[
https://issues.apache.org/jira/browse/CALCITE-5134?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17532268#comment-17532268
]
Benchao Li commented on CALCITE-5134:
-------------------------------------
the pr is ready for review: https://github.com/apache/calcite/pull/2791
> 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
>
> {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)
