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

["Benchao Li (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-5134?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Benchao Li updated CALCITE-5134:
--------------------------------
    Fix Version/s: 1.31.0

> 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
>            Assignee: Benchao Li
>            Priority: Major
>             Fix For: 1.31.0
>
>
> {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)