[CALCITE-5134] Queries with subquery inside select list does not work if subquery uses table from left join

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.31.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Benchao Li
• Reporter: Dmitry Sysolyatin
• Created: 05/May/22 07:31
• Updated: 03/Aug/22 16:21
• Resolved: 17/Jun/22 07:28

Description

!use bookstore
SELECT array(SELECT lau."books")
FROM "bookstore"."authors" au
LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name");

Exception:

> java.lang.AssertionError: Conversion to relational algebra failed to preserve datatypes:
> validated type:
> RecordType(RecordType(RecordType(JavaType(class java.lang.String) title, JavaType(int) NOT NULL publishYear, RecordType(JavaType(int) NOT NULL pageNo, JavaType(class java.lang.String) contentType) NOT NULL ARRAY NOT NULL pages) NOT NULL ARRAY books) NOT NULL ARRAY NOT NULL EXPR$0) NOT NULL
> converted type:
> RecordType(RecordType(RecordType(JavaType(class java.lang.String) title, JavaType(int) NOT NULL publishYear, RecordType(JavaType(int) NOT NULL pageNo, JavaType(class java.lang.String) contentType) NOT NULL ARRAY NOT NULL pages) NOT NULL ARRAY NOT NULL books) NOT NULL ARRAY NOT NULL EXPR$0) NOT NULL
> rel:
> LogicalProject(EXPR$0=[ARRAY({
> LogicalProject(books=[$cor0.books0])
>   LogicalValues(tuples=[[{ 0 }]])
> })])
>   LogicalJoin(condition=[=($9, $4)], joinType=[left])
>     LogicalProject(aid=[$0], name=[$1], birthPlace=[$2], books=[$3], name0=[CAST($1):VARCHAR])
>       LogicalTableScan(table=[[bookstore, authors]])
>     LogicalProject(aid=[$0], name=[$1], birthPlace=[$2], books=[$3], name0=[CAST($1):VARCHAR])
>       LogicalTableScan(table=[[bookstore, authors]])
> 

!use bookstore
SELECT array(SELECT lau."name" || 'test')
FROM "bookstore"."authors" au
LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name");

Exception:

> java.sql.SQLException: Error while executing SQL "SELECT array(SELECT lau."name" || 'test')
> FROM "bookstore"."authors" au
> LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name")": field ordinal [5] out of range; input fields are: [name0, name00]

SELECT array(SELECT * FROM UNNEST(lau."books"))
FROM "bookstore"."authors" au
LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name");

Exception:

Suppressed: java.lang.IndexOutOfBoundsException: Index 7 out of bounds for length 2

Initially, I faced this issue when tried to execute the following query and got NPE exception: 

SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoids, c.relispartition, 
pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', '), c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident
                     FROM pg_catalog.pg_class c
                     LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
                     WHERE c.oid = '1';

I started to break down this query into pieces and managed to reduce it to queries described above.

Issue Links

• links to: GitHub Pull Request #2791