[IGNITE-18667] sql. left outer join with correlated subquery fails

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Fix Version/s: 3.0
• Component/s: sql
• Labels: calcite2-required calcite3-required ignite-3
• Epic Link: Improve SQL quality in AI3. Part1
• Assignee: Unassigned
• Reporter: Maksim Zhuravkov
• Created: 30/Jan/23 12:25
• Updated: 04/Sep/24 16:20

Description

The following queries must work but the fail:

# left outer join on arbitrary correlated subquery
statement ok
SELECT * FROM integers s1 LEFT OUTER JOIN integers s2 ON (SELECT CASE WHEN s1.i+s2.i>10 THEN TRUE ELSE FALSE END) ORDER BY s1.i;

Expected:

i	i
1	null
2	null
3	null
null	null

Actual:

Caused by: java.lang.AssertionError: All correlation variables should resolve to the same namespace. Prev ns=org.apache.calcite.sql.validate.IdentifierNamespace@7f74c84c, new ns=org.apache.calcite.sql.validate.IdentifierNamespace@11b2d99c
	at org.apache.calcite.sql2rel.SqlToRelConverter.getCorrelationUse(SqlToRelConverter.java:3006)
	at org.apache.calcite.sql2rel.SqlToRelConverter.createJoin(SqlToRelConverter.java:2916)
	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.register(SqlToRelConverter.java:4865)
	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.reRegister(SqlToRelConverter.java:4930)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3253)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3173)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2392)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2280)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:694)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:675)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3737)
	at org.apache.ignite.internal.sql.engine.prepare.IgniteSqlToRelConvertor.convertQueryRecursive(IgniteSqlToRelConvertor.java:68)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:595)

Issue Links

• incorporates: IGNITE-15594 Calcite. ArrayIndexOutOfBoundsException with left outer join on subquery.