Jia-Xuan Liu created CALCITE-5643:
-------------------------------------
Summary: Query with CROSS JOIN UNNEST(array) executed by BigQuery
adaptor isn't supported
Key: CALCITE-5643
URL: https://issues.apache.org/jira/browse/CALCITE-5643
Project: Calcite
Issue Type: Bug
Affects Versions: 1.34.0
Reporter: Jia-Xuan Liu
Given a query like
{code:sql}
WITH
tmp1 (custkey, name, orders) AS (
SELECT
c_custkey
, c_name
, array_agg(o_orderkey ORDER BY o_orderkey ASC) orders
FROM
(bq.customer
LEFT JOIN bq.orders ON (o_custkey = c_custkey))
GROUP BY c_custkey, c_name
)
SELECT
t.custkey
, t.name
, array_agg(o.o_orderstatus ORDER BY o.o_orderkey ASC)
FROM
((tmp1 t
CROSS JOIN UNNEST(t.orders) u (uc))
LEFT JOIN bq.orders o ON (u.uc = o.o_orderkey))
GROUP BY t.custkey, t.name
{code}
`bq` is a JdbcSchema connected with BigQuery.
It fails with exception:
{code:java}
Caused by: java.lang.IllegalStateException: Unable to implement
EnumerableAggregate(group=[{0, 1}], EXPR$2=[ARRAY_AGG($5) WITHIN GROUP ([4])]):
rowcount = 251.2225, cumulative cost = {7580.5628125 rows, 32182.2752199209
cpu, 0.0 io}, id = 394
EnumerableMergeJoin(condition=[=($3, $4)], joinType=[left]): rowcount =
2512.225, cumulative cost = {7297.9375 rows, 32182.2752199209 cpu, 0.0 io}, id
= 392
EnumerableSort(sort0=[$3], dir0=[ASC]): rowcount = 158.5, cumulative cost =
{4247.2125 rows, 23621.968885142334 cpu, 0.0 io}, id = 383
EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{2}]): rowcount = 158.5, cumulative cost = {4088.7125 rows,
1140.15 cpu, 0.0 io}, id = 381
EnumerableAggregate(group=[{0, 1}], orders=[ARRAY_AGG($2) WITHIN GROUP
([2])]): rowcount = 158.5, cumulative cost = {2281.8125 rows, 680.5 cpu, 0.0
io}, id = 372
JdbcToEnumerableConverter: rowcount = 1585.0, cumulative cost =
{2103.5 rows, 680.5 cpu, 0.0 io}, id = 370
JdbcJoin(condition=[=($3, $0)], joinType=[left]): rowcount =
1585.0, cumulative cost = {1945.0 rows, 522.0 cpu, 0.0 io}, id = 368
JdbcProject(c_custkey=[$0], c_name=[$1]): rowcount = 100.0,
cumulative cost = {180.0 rows, 261.0 cpu, 0.0 io}, id = 363
JdbcTableScan(table=[[bq, customer]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 1
JdbcProject(o_orderkey=[$0], o_custkey=[$1]): rowcount = 100.0,
cumulative cost = {180.0 rows, 261.0 cpu, 0.0 io}, id = 366
JdbcTableScan(table=[[bq, orders]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 3
EnumerableUncollect: rowcount = 1.0, cumulative cost =
{2.9000000000000004 rows, 2.9000000000000004 cpu, 0.0 io}, id = 379
JdbcToEnumerableConverter: rowcount = 1.0, cumulative cost =
{1.9000000000000001 rows, 1.9000000000000001 cpu, 0.0 io}, id = 377
JdbcProject(orders=[$cor0.orders]): rowcount = 1.0, cumulative cost
= {1.8 rows, 1.8 cpu, 0.0 io}, id = 375
JdbcValues(tuples=[[{ 0 }]]): rowcount = 1.0, cumulative cost =
{1.0 rows, 1.0 cpu, 0.0 io}, id = 263
JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = {280.0 rows,
8560.306334778565 cpu, 0.0 io}, id = 390
JdbcSort(sort0=[$0], dir0=[ASC]): rowcount = 100.0, cumulative cost =
{270.0 rows, 8550.306334778565 cpu, 0.0 io}, id = 388
JdbcProject(o_orderkey=[$0], o_orderstatus=[$2]): rowcount = 100.0,
cumulative cost = {180.0 rows, 261.0 cpu, 0.0 io}, id = 386
JdbcTableScan(table=[[bq, orders]]): rowcount = 100.0, cumulative
cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 3
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:117)
at
org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:112)
at
org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1159)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:324)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:665)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:621)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
... 2 more
Suppressed: java.lang.UnsupportedOperationException
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.getAliasContext(SqlImplementor.java:987)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:657)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:454)
at
java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
at java.base/java.lang.reflect.Method.invoke(Method.java:577)
at
org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:139)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:147)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:216)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:204)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitRoot(SqlImplementor.java:180)
at
org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.generateSql(JdbcToEnumerableConverter.java:364)
at
org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.implement(JdbcToEnumerableConverter.java:108)
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:107)
at
org.apache.calcite.adapter.enumerable.EnumerableUncollect.implement(EnumerableUncollect.java:84)
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:107)
at
org.apache.calcite.adapter.enumerable.EnumerableCorrelate.implement(EnumerableCorrelate.java:140)
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:107)
at
org.apache.calcite.adapter.enumerable.EnumerableSort.implement(EnumerableSort.java:74)
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:107)
at
org.apache.calcite.adapter.enumerable.EnumerableMergeJoin.implement(EnumerableMergeJoin.java:412)
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:107)
at
org.apache.calcite.adapter.enumerable.EnumerableAggregate.implement(EnumerableAggregate.java:105)
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114)
... 13 more
Suppressed: java.lang.Throwable: Error while converting RelNode
to SqlNode:
JdbcProject(orders=[$cor0.orders])
JdbcValues(tuples=[[{ 0 }]])
at
org.apache.calcite.util.Util.throwAsRuntime(Util.java:973)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitRoot(SqlImplementor.java:182)
... 26 more
{code}
I used JDBC adaptor to connect with BigQuery (Simba BigQuery JDBC). Created the
Calcite connection with `jdbc:calcite:fun=bigquery`.
I also tried a similar SQL without BigQuery table, as followed
{code:sql}
with Book as (
SELECT *
FROM
(
VALUES
ROW (1, 'book1', 1)
, ROW (2, 'book2', 2)
, ROW (3, 'book3', 1)
) Book (bookId, name, authorId)
),
People as (
SELECT *
FROM (
VALUES
ROW (1, 'user1', array[1,3]),
ROW (2, 'user2', array[2])
) People (userId, name, books)
)
select b.bookId, b.name from People p
cross join unnest(p.books) u(uc)
left join Book b on u.uc = b.bookId
{code}
It works fine.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
