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)