[IGNITE-15566] Calcite. Join with correlated subqueries returns wrong result.

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Component/s: sql
• Labels: calcite
• Assignee: Aleksey Plekhanov
• Reporter: Evgeny Stanilovsky
• Created: 23/Sep/21 08:52
• Updated: 17/May/22 09:33
• Resolved: 24/Jan/22 14:17

Description

Example from ScriptRunner :

query IIT
SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<>test2.c) AND NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<test2.c);
----
12	2	b

query IIT
SELECT * FROM test WHERE NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<test2.c AND test2.a=12) AND NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b>test2.c AND test2.a=12) ORDER BY a;
----
11	1	a
13	3	c

/join/inner/test_eq_ineq_join.test[_ignore]

Issue Links

• is part of: IGNITE-12248 Apache Calcite based query execution engine
• links to: GitHub Pull Request #9720