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)
