Cheng Wang created CALCITE-7883:
-----------------------------------
Summary: 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
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)
