[
https://issues.apache.org/jira/browse/CALCITE-7883?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
krooswu reassigned CALCITE-7883:
--------------------------------
Assignee: krooswu
> RelToSqlConverter strips outer qualifier in visitAntiOrSemiJoin, so
> INTERSECT/EXCEPT results compare against the wrong column
> -----------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7883
> URL: https://issues.apache.org/jira/browse/CALCITE-7883
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.42.0
> Environment: macOS / Linux, JDK 17/21, Calcite 1.42.0 and main branch
> Reporter: Cheng Wang
> Assignee: krooswu
> Priority: Major
>
> h3. Description
> When CoreRules.INTERSECT_TO_SEMI_JOIN or CoreRules.MINUS_TO_ANTI_JOIN rewrite
> a set operation into a semi/anti join and RelToSqlConverter unparses it,
> visitAntiOrSemiJoin (RelToSqlConverter.java:200-232) runs
> AliasReplacementShuttle (RelToSqlConverter.java:387-392) over the join
> condition.
> The shuttle strips the outer table alias ("t") from the correlated
> references, so an unqualified "DEPTNO" inside the generated EXISTS / NOT
> EXISTS resolves to the inner relation ("t0"."DEPTNO") instead of the outer
> one. The resulting SQL compares each outer column with the wrong inner
> column, and the enclosing GROUP BY does not repair it.
> h3. Reproducer (Calcite 1.42.0 and current main)
> Schema and data:
> * EMP(EMPNO, ENAME, JOB, MGR, SAL, COMM, DEPTNO) with rows:
> (1, 'ALICE', 'CLERK', null, 100, null, 10),
> (2, 'ALICE', 'ANALYST', 1, 150, 0, 10),
> (3, 'CAROL', 'MGR', null, 200, null, 20),
> (4, 'DAVE', null, 3, 300, 50, null)
> * DEPT(DEPTNO, NAME) with rows:
> (10, 'ACC'), (10, 'ACC_DUP'), (20, 'RES'), (null, 'NULL_DEPT')
> Queries:
> {code:sql}
> -- INTERSECT case
> SELECT DEPTNO, MGR FROM EMP INTERSECT SELECT DEPTNO, DEPTNO FROM DEPT;
> -- EXCEPT case
> SELECT DEPTNO, MGR FROM EMP EXCEPT SELECT DEPTNO, DEPTNO FROM DEPT;
> {code}
> Apply CoreRules.INTERSECT_TO_SEMI_JOIN / CoreRules.MINUS_TO_ANTI_JOIN, then
> unparse using RelToSqlConverter.
> h3. Expected vs Actual
> * INTERSECT:
> Original RelNode result: [] (correct)
> Generated SQL round-trip result: [[10, null], [20, null]] (WRONG:
> unqualified "DEPTNO" binds to "t0"."DEPTNO")
> * EXCEPT:
> Original RelNode result: [[10, 1], [10, null], [20, null], [null, 3]]
> (correct)
> Generated SQL round-trip result: [[10, 1], [null, 3]] (WRONG: rows silently
> dropped)
> Generated SQL for INTERSECT:
> {code:sql}
> SELECT "DEPTNO", "MGR"
> FROM "EMP" AS "t"
> WHERE EXISTS (SELECT 1
> FROM (SELECT "DEPTNO", "DEPTNO" AS "DEPTNO0" FROM "DEPT") AS "t0"
> WHERE "DEPTNO" IS NOT DISTINCT FROM "t0"."DEPTNO"
> AND "MGR" IS NOT DISTINCT FROM "t0"."DEPTNO0")
> GROUP BY "DEPTNO", "MGR"
> {code}
> h3. Root Cause
> AliasReplacementShuttle replaces qualified references with unqualified
> aliases from the inner subquery (RelToSqlConverter.java:387-392).
> visitAntiOrSemiJoin emits the condition without re-qualifying the outer side,
> causing variable capture.
> This is distinct from closed issues CALCITE-5394 / CALCITE-5395 /
> CALCITE-6804 (which covered SELECT * and corrupted NOT EXISTS condition for a
> subquery alias, not qualifier stripping in set-op rewrites).
> h3. Suggested Fix
> Do not strip a qualifier that becomes ambiguous inside the generated
> subquery; retain the outer alias (e.g. "t"."DEPTNO") or assign the inner
> relation fresh aliases before running AliasReplacementShuttle.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
