Major Description
EXCEPT might be merged by the rule even when we should not, mingling the different branches.
For instance, given the following query:
select * from ( select name, deptno from ( select name, deptno, count(1) from dept group by name, deptno except all select name, deptno, 1 from dept ) subq except all select name, deptno from ( select name, deptno, 1 from dept except all select name, deptno, count(1) from dept group by name, deptno ) subq2 ) a except all select name, deptno from dept ) aa
The plan produced by the rule is:
LogicalMinus(all=[true])
LogicalProject(NAME=[$0], DEPTNO=[$1])
LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()])
LogicalProject(NAME=[$1], DEPTNO=[$0], $f2=[1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(NAME=[$0], DEPTNO=[$1])
LogicalProject(NAME=[$1], DEPTNO=[$0], EXPR$2=[1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(NAME=[$0], DEPTNO=[$1])
LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()])
LogicalProject(NAME=[$1], DEPTNO=[$0], $f2=[1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(NAME=[$0], DEPTNO=[$1])
LogicalProject(NAME=[$1], DEPTNO=[$0], EXPR$2=[1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(NAME=[$1], DEPTNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])