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

["Dmitry Sysolyatin (Jira)" <ji...@apache.org>]

Dmitry Sysolyatin created CALCITE-5134:
------------------------------------------

             Summary: Queries with subquery inside select list does not work if subquery uses table from left join
                 Key: CALCITE-5134
                 URL: https://issues.apache.org/jira/browse/CALCITE-5134
             Project: Calcite
          Issue Type: Bug
          Components: core
            Reporter: Dmitry Sysolyatin


{code:java}
!use bookstore
SELECT array(SELECT lau."books")
FROM "bookstore"."authors" au
LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name");
{code}
Exception:
{code:java}
> 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]])
> 
{code}
{code:java}
!use bookstore
SELECT array(SELECT lau."name" || 'test')
FROM "bookstore"."authors" au
LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name");
{code}
Exception:
{code:java}
> 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]
{code}
{code:java}
SELECT array(SELECT * FROM UNNEST(lau."books"))
FROM "bookstore"."authors" au
LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name");
{code}
Exception:
{code:java}
Suppressed: java.lang.IndexOutOfBoundsException: Index 7 out of bounds for length 2
{code}


Initially, I faced this issue when tried to execute the following query and got NPE exception: 
{code:java}
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';
{code}
I started to break down this query into pieces and managed to reduce it to queries described above.



--
This message was sent by Atlassian Jira
(v8.20.7#820007)