[CALCITE-3399] Field-pruning for set operators (except UNION ALL) changes query semantics

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.26.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Jin Xing
• Reporter: Jin Xing
• Created: 11/Oct/19 05:23
• Updated: 02/Feb/21 22:13
• Resolved: 04/Sep/20 09:48

Description

RelFieldTrimmer#trimFields provides functionality to trim fields for  UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL;

But UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL works by checking duplication. Column pruning on inputs of SetOp might lead to different semantics.

 

Take below example for illustration

emp0:
name, deptno
 "A",   0
 "B",   1

emp1:
name, deptno
 "C",   0
 "D",   2

select deptno from
(select name, deptno from emp0
intersect
select name, deptno from emp1)

Run above Sql on emp0 and emp1, result is not empty if trim fields on inputs of INTERSECT, but result is empty if DO NOT trim fields on inputs of INTERSECT

Issue Links

• is duplicated by: CALCITE-2260 RelFieldTrimmer incorrectly trims fields when trimming a distinct UNION
• is duplicated by: CALCITE-4133 Shouldn't trim fields when it's under Union
• links to: GitHub Pull Request #1495
• links to: GitHub Pull Request #2125