[IGNITE-15594] Calcite. ArrayIndexOutOfBoundsException with left outer join on subquery.

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql
• Labels: calcite calcite2-required calcite3-required ignite-3
• Assignee: Unassigned
• Reporter: Evgeny Stanilovsky
• Created: 24/Sep/21 11:41
• Updated: 17/Oct/23 07:52

Description

# left outer join on subquery only involving RHS works
query TT
SELECT * FROM integers s1 LEFT OUTER JOIN integers s2 ON s1.i=s2.i AND (SELECT CASE WHEN s2.i>2 THEN TRUE ELSE FALSE END) ORDER BY s1.i NULLS FIRST;
----
NULL	NULL
1	NULL
2	NULL
3	3

statement ok
CREATE TABLE tbl(a TINYINT, b SMALLINT, c INTEGER, d BIGINT, e VARCHAR, f DATE, g TIMESTAMP)

statement ok
INSERT INTO tbl VALUES (1, 2, 3, 4, '5', DATE '1992-01-01', TIMESTAMP '1992-01-01 00:00:00')

query TTTTTTTTTTTTTT
SELECT * FROM tbl t1 LEFT JOIN tbl t2 ON (SELECT t2.a)<100
----
1	2	3	4	5	1992-01-01	1992-01-01 00:00:00	1	2	3	4	5	1992-01-01	1992-01-01 00:00:0

/subquery/scalar/test_complex_correlated_subquery.test[_ignore]
/subquery/scalar/test_complex_nested_correlated_subquery.test[_ignore]

[2021-09-24 14:35:07,214][WARN ][calciteQry-#147%srv1%][org.apache.ignite.internal.processors.query.calcite.exec.QueryTaskExecutorImplc{1}] Uncaught exception
class org.apache.ignite.IgniteException: Unexpected exception
	at org.apache.ignite.internal.processors.query.calcite.exec.ExecutionContext.lambda$execute$0(ExecutionContext.java:309)
	at org.apache.ignite.internal.processors.query.calcite.exec.QueryTaskExecutorImpl.lambda$execute$0(QueryTaskExecutorImpl.java:68)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.ArrayIndexOutOfBoundsException: 5
	at org.apache.ignite.internal.processors.query.calcite.exec.ArrayRowHandler.get(ArrayRowHandler.java:36)
	at org.apache.ignite.internal.processors.query.calcite.exec.ArrayRowHandler.get(ArrayRowHandler.java:27)
	at SC.execute(Unknown Source)

Issue Links

• is part of: IGNITE-18667 sql. left outer join with correlated subquery fails
• is part of: IGNITE-12248 Apache Calcite based query execution engine