[CALCITE-1852] Fix for UnionMergeRule to deal correctly with EXCEPT

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.13.0
• Component/s: core
• Assignee: Jesús Camacho Rodríguez
• Reporter: Jesús Camacho Rodríguez
• Created: 20/Jun/17 09:56
• Updated: 27/Feb/24 22:24
• Resolved: 20/Jun/17 10:11

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]])