You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Gavin Ray <ra...@gmail.com> on 2022/02/14 15:24:18 UTC
"Unable to implement EnumerableNestedLoopJoin in SELECT(a, b, ARRAY(c, d, ARRAY(e)))"
The following query seems to work perfectly:
SELECT
"houses"."id",
"houses"."name",
"houses"."address",
ARRAY(
SELECT
"users"."id",
"users"."name"
FROM
"users"
WHERE
"users"."house_id" = "houses"."id"
) AS "users"
FROM
"houses";
However, if I modify the inner ARRAY() query for "users" to be:
SELECT
"users"."id",
"users"."name",
ARRAY(
SELECT
"todos"."id",
"todos"."description"
FROM
"todos"
WHERE
"todos"."user_id" = "users"."id"
) AS "todos"
FROM
"users"
===================================================
Unable to implement EnumerableNestedLoopJoin(condition=[true],
joinType=[inner]): rowcount = 22500.0, cumulative cost = {227855.0 rows,
18242.0 cpu, 0.0 io}, id = 12787
EnumerableTableScan(table=[[example, houses]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12718
EnumerableCollect(field=[x]): rowcount = 225.0, cumulative cost = {2755.0
rows, 18141.0 cpu, 0.0 io}, id = 12785
EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}], todos=[$t3]):
rowcount = 225.0, cumulative cost = {2530.0 rows, 17916.0 cpu, 0.0 io}, id
= 12793
EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{0}]): rowcount = 225.0, cumulative cost = {2305.0 rows,
16341.0 cpu, 0.0 io}, id = 12781
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor0], expr#4=[$t3.id],
expr#5=[=($t2, $t4)], proj#0..2=[{exprs}], $condition=[$t5]): rowcount =
15.0, cumulative cost = {115.0 rows, 1101.0 cpu, 0.0 io}, id = 12789
EnumerableTableScan(table=[[example, users]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12722
EnumerableCollect(field=[x]): rowcount = 15.0, cumulative cost =
{130.0 rows, 1016.0 cpu, 0.0 io}, id = 12779
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor1], expr#4=[$
t3.id], expr#5=[=($t1, $t4)], id=[$t0], description=[$t2],
$condition=[$t5]): rowcount = 15.0, cumulative cost = {115.0 rows, 1001.0
cpu, 0.0 io}, id = 12797
EnumerableTableScan(table=[[example, todos]]): rowcount =
100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12728
(state=,code=0)
Re: "Unable to implement EnumerableNestedLoopJoin in SELECT(a, b, ARRAY(c, d, ARRAY(e)))"
Posted by Gavin Ray <ra...@gmail.com>.
Ahh wow, thank you Stamatis. Great to know there is something that has been
done in the past
I suppose I could try to build from this PR and see if it works
And about the API's, I assume you mean the "GenerateCorrelate" and
"CreateEnricher" methods the author introduced there
On Mon, Feb 21, 2022 at 8:05 AM Stamatis Zampetakis <za...@gmail.com>
wrote:
> Hey Gavin,
>
> I think you are bumping into a missing feature and most likely addressed by
> [1].
>
> The approach in [1] is rather good but I had some doubts about a few new
> APIs that were introduced which made me a bit cautious about merging this
> to master. I would definitely like to find some time to review this again.
>
> Best,
> Stamatis
>
> [1] https://github.com/apache/calcite/pull/2116
>
> On Sat, Feb 19, 2022 at 6:17 PM Gavin Ray <ra...@gmail.com> wrote:
>
> > Digging into this more to try to better understand Calcite and hopefully
> > make
> > progress,it seems like the query breaks here:
> >
> >
> >
> https://github.com/apache/calcite/blob/5b2de4ef5c9447bc9f7aff98dd049bd32af5c53d/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1450-L1454
> >
> > @Override protected Context getAliasContext(RexCorrelVariable
> variable)
> > {
> > return requireNonNull(
> > correlTableMap.get(variable.id),
> > () -> "variable " + variable.id + " is not found");
> > }
> >
> > Unfortunately, this feature (at least I think so?) is the barrier to
> > me being able to make efficient cross-datasource queries that return the
> > right
> > data shape for GraphQL responses.
> >
> > My current duct-tape hack is to split the query into query-per-join
> which I
> > assume defeats most of Calcite's optimization and planning abilities =(
> >
> > It's not much, but I'm also willing to offer $250 if anyone could help me
> > fix
> > this or figure out an alternative solution.
> >
> > On Mon, Feb 14, 2022 at 4:26 PM Gavin Ray <ra...@gmail.com> wrote:
> >
> > > Apologies for the slow reply Ruben, I appreciate your help.
> > > The full stack trace (I was prototyping in sqlline) seems to be more
> > > helpful:
> > >
> > > Here is what seems to be the most useful bits:
> > > ======================================
> > > java.sql.SQLException: Error while preparing plan
> > > [EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
> > > requiredColumns=[{0}])
> > > JdbcToEnumerableConverter
> > >
> > > Caused by: java.lang.IllegalStateException: Unable to implement
> > > EnumerableCorrelate
> > > Suppressed: java.lang.NullPointerException: variable $cor0 is not
> > found
> > > at java.base/java.util.Objects.requireNonNull(Objects.java:334)
> > > at
> > >
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$BaseContext.getAliasContext(SqlImplementor.java:1429)
> > > at
> > >
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:628)
> > > ....
> > > at
> > >
> >
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:427)
> > >
> > > And here is the entire thing:
> > > ======================================
> > > java.sql.SQLException: Error while preparing plan
> > > [EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
> > > requiredColumns=[{0}])
> > > JdbcToEnumerableConverter
> > > JdbcTableScan(table=[[hsql, PUBLIC, houses]])
> > > EnumerableCollect(field=[EXPR$0])
> > > EnumerableProject(id=[$0], name=[$1], todos=[$3])
> > > EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
> > > requiredColumns=[{0}])
> > > JdbcToEnumerableConverter
> > > JdbcFilter(condition=[=($2, $cor0.id)])
> > > JdbcTableScan(table=[[hsql, PUBLIC, users]])
> > > EnumerableCollect(field=[EXPR$0])
> > > JdbcToEnumerableConverter
> > > JdbcProject(id=[$0], description=[$2])
> > > JdbcFilter(condition=[=($1, $cor1.id)])
> > > JdbcTableScan(table=[[hsql, PUBLIC, todos]])
> > > ]
> > > at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> > > at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> > > at
> > >
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:239)
> > > at
> > >
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.access$100(CalciteConnectionImpl.java:101)
> > > at
> > >
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl$2.prepareStatement(CalciteConnectionImpl.java:188)
> > > at CalciteSchemaManager.executeQuery(CalciteSchemaManager.kt:209)
> > > at CalciteSchemaManager.executeQuery(CalciteSchemaManager.kt:213)
> > > at ForeignKeyTest.throwawayTest(ForeignKeyTest.kt:265)
> > >
> > > Caused by: java.lang.IllegalStateException: Unable to implement
> > > EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
> > > requiredColumns=[{0}]): rowcount = 22500.0, cumulative cost = {318610.0
> > > rows, 562611.0 cpu, 0.0 io}, id = 315
> > > JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = {110.0
> > > rows, 111.0 cpu, 0.0 io}, id = 293
> > > JdbcTableScan(table=[[hsql, PUBLIC, houses]]): rowcount = 100.0,
> > > cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 15
> > > EnumerableCollect(field=[EXPR$0]): rowcount = 225.0, cumulative cost
> =
> > > {2959.0 rows, 5625.0 cpu, 0.0 io}, id = 313
> > > EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}],
> > > todos=[$t3]): rowcount = 225.0, cumulative cost = {2734.0 rows, 5400.0
> > cpu,
> > > 0.0 io}, id = 317
> > > EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
> > > requiredColumns=[{0}]): rowcount = 225.0, cumulative cost = {2509.0
> rows,
> > > 3825.0 cpu, 0.0 io}, id = 309
> > > JdbcToEnumerableConverter: rowcount = 15.0, cumulative cost =
> > > {116.5 rows, 202.5 cpu, 0.0 io}, id = 298
> > > JdbcFilter(condition=[=($2, $cor0.id)]): rowcount = 15.0,
> > > cumulative cost = {115.0 rows, 201.0 cpu, 0.0 io}, id = 296
> > > JdbcTableScan(table=[[hsql, PUBLIC, users]]): rowcount =
> > > 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 16
> > > EnumerableCollect(field=[EXPR$0]): rowcount = 15.0, cumulative
> > > cost = {143.5 rows, 241.5 cpu, 0.0 io}, id = 307
> > > JdbcToEnumerableConverter: rowcount = 15.0, cumulative cost =
> > > {128.5 rows, 226.5 cpu, 0.0 io}, id = 305
> > > JdbcProject(id=[$0], description=[$2]): rowcount = 15.0,
> > > cumulative cost = {127.0 rows, 225.0 cpu, 0.0 io}, id = 303
> > > JdbcFilter(condition=[=($1, $cor1.id)]): rowcount =
> 15.0,
> > > cumulative cost = {115.0 rows, 201.0 cpu, 0.0 io}, id = 301
> > > JdbcTableScan(table=[[hsql, PUBLIC, todos]]): rowcount
> =
> > > 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 18
> > >
> > > at
> > >
> >
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114)
> > > at
> > >
> >
> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:114)
> > > at
> > >
> >
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1130)
> > > at
> > >
> >
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1032)
> > > at
> > >
> >
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:988)
> > > at
> > >
> >
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:668)
> > > at
> > >
> >
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:513)
> > > at
> > >
> >
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:483)
> > > at
> > >
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:249)
> > > at
> > >
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:229)
> > > ... 88 more
> > > Suppressed: java.lang.NullPointerException: variable $cor0 is not found
> > > at java.base/java.util.Objects.requireNonNull(Objects.java:334)
> > > at
> > >
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$BaseContext.getAliasContext(SqlImplementor.java:1429)
> > > at
> > >
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:628)
> > > at
> > >
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:1069)
> > > at
> > >
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.callToSql(SqlImplementor.java:776)
> > > at
> > >
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:750)
> > > at
> > >
> >
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:427)
> > > at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
> > > Method)
> > > at
> > >
> >
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
> > > at
> > >
> >
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> > > at java.base/java.lang.reflect.Method.invoke(Method.java:568)
> > > at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
> > > 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:201)
> > > at
> > >
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:189)
> > > at
> > >
> >
> org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.generateSql(JdbcToEnumerableConverter.java:351)
> > > at
> > >
> >
> org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.implement(JdbcToEnumerableConverter.java:107)
> > > at
> > >
> >
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
> > > at
> > >
> >
> org.apache.calcite.adapter.enumerable.EnumerableCorrelate.implement(EnumerableCorrelate.java:113)
> > > at
> > >
> >
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
> > > at
> > >
> >
> org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:118)
> > > at
> > >
> >
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
> > > at
> > >
> >
> org.apache.calcite.adapter.enumerable.EnumerableCollect.implement(EnumerableCollect.java:81)
> > > at
> > >
> >
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
> > > at
> > >
> >
> org.apache.calcite.adapter.enumerable.EnumerableCorrelate.implement(EnumerableCorrelate.java:139)
> > > at
> > >
> >
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111)
> > > ... 97 more
> > >
> > > On Mon, Feb 14, 2022 at 1:14 PM Ruben Q L <ru...@gmail.com> wrote:
> > >
> > >> Hello Gavin,
> > >>
> > >> what's the full stack trace that you get?
> > >>
> > >> On Mon, Feb 14, 2022 at 3:24 PM Gavin Ray <ra...@gmail.com>
> > wrote:
> > >>
> > >> > The following query seems to work perfectly:
> > >> >
> > >> > SELECT
> > >> > "houses"."id",
> > >> > "houses"."name",
> > >> > "houses"."address",
> > >> > ARRAY(
> > >> > SELECT
> > >> > "users"."id",
> > >> > "users"."name"
> > >> > FROM
> > >> > "users"
> > >> > WHERE
> > >> > "users"."house_id" = "houses"."id"
> > >> > ) AS "users"
> > >> > FROM
> > >> > "houses";
> > >> >
> > >> > However, if I modify the inner ARRAY() query for "users" to be:
> > >> >
> > >> > SELECT
> > >> > "users"."id",
> > >> > "users"."name",
> > >> > ARRAY(
> > >> > SELECT
> > >> > "todos"."id",
> > >> > "todos"."description"
> > >> > FROM
> > >> > "todos"
> > >> > WHERE
> > >> > "todos"."user_id" = "users"."id"
> > >> > ) AS "todos"
> > >> > FROM
> > >> > "users"
> > >> >
> > >> > ===================================================
> > >> >
> > >> > Unable to implement EnumerableNestedLoopJoin(condition=[true],
> > >> > joinType=[inner]): rowcount = 22500.0, cumulative cost = {227855.0
> > rows,
> > >> > 18242.0 cpu, 0.0 io}, id = 12787
> > >> > EnumerableTableScan(table=[[example, houses]]): rowcount = 100.0,
> > >> > cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12718
> > >> > EnumerableCollect(field=[x]): rowcount = 225.0, cumulative cost =
> > >> {2755.0
> > >> > rows, 18141.0 cpu, 0.0 io}, id = 12785
> > >> > EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}],
> > >> todos=[$t3]):
> > >> > rowcount = 225.0, cumulative cost = {2530.0 rows, 17916.0 cpu, 0.0
> > io},
> > >> id
> > >> > = 12793
> > >> > EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
> > >> > requiredColumns=[{0}]): rowcount = 225.0, cumulative cost = {2305.0
> > >> rows,
> > >> > 16341.0 cpu, 0.0 io}, id = 12781
> > >> > EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor0],
> expr#4=[$
> > >> > t3.id],
> > >> > expr#5=[=($t2, $t4)], proj#0..2=[{exprs}], $condition=[$t5]):
> > rowcount =
> > >> > 15.0, cumulative cost = {115.0 rows, 1101.0 cpu, 0.0 io}, id = 12789
> > >> > EnumerableTableScan(table=[[example, users]]): rowcount =
> > >> 100.0,
> > >> > cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12722
> > >> > EnumerableCollect(field=[x]): rowcount = 15.0, cumulative
> > cost =
> > >> > {130.0 rows, 1016.0 cpu, 0.0 io}, id = 12779
> > >> > EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor1],
> > expr#4=[$
> > >> > t3.id], expr#5=[=($t1, $t4)], id=[$t0], description=[$t2],
> > >> > $condition=[$t5]): rowcount = 15.0, cumulative cost = {115.0 rows,
> > >> 1001.0
> > >> > cpu, 0.0 io}, id = 12797
> > >> > EnumerableTableScan(table=[[example, todos]]): rowcount
> =
> > >> > 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12728
> > >> > (state=,code=0)
> > >> >
> > >>
> > >
> >
>
Re: "Unable to implement EnumerableNestedLoopJoin in SELECT(a, b, ARRAY(c, d, ARRAY(e)))"
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hey Gavin,
I think you are bumping into a missing feature and most likely addressed by
[1].
The approach in [1] is rather good but I had some doubts about a few new
APIs that were introduced which made me a bit cautious about merging this
to master. I would definitely like to find some time to review this again.
Best,
Stamatis
[1] https://github.com/apache/calcite/pull/2116
On Sat, Feb 19, 2022 at 6:17 PM Gavin Ray <ra...@gmail.com> wrote:
> Digging into this more to try to better understand Calcite and hopefully
> make
> progress,it seems like the query breaks here:
>
>
> https://github.com/apache/calcite/blob/5b2de4ef5c9447bc9f7aff98dd049bd32af5c53d/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1450-L1454
>
> @Override protected Context getAliasContext(RexCorrelVariable variable)
> {
> return requireNonNull(
> correlTableMap.get(variable.id),
> () -> "variable " + variable.id + " is not found");
> }
>
> Unfortunately, this feature (at least I think so?) is the barrier to
> me being able to make efficient cross-datasource queries that return the
> right
> data shape for GraphQL responses.
>
> My current duct-tape hack is to split the query into query-per-join which I
> assume defeats most of Calcite's optimization and planning abilities =(
>
> It's not much, but I'm also willing to offer $250 if anyone could help me
> fix
> this or figure out an alternative solution.
>
> On Mon, Feb 14, 2022 at 4:26 PM Gavin Ray <ra...@gmail.com> wrote:
>
> > Apologies for the slow reply Ruben, I appreciate your help.
> > The full stack trace (I was prototyping in sqlline) seems to be more
> > helpful:
> >
> > Here is what seems to be the most useful bits:
> > ======================================
> > java.sql.SQLException: Error while preparing plan
> > [EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
> > requiredColumns=[{0}])
> > JdbcToEnumerableConverter
> >
> > Caused by: java.lang.IllegalStateException: Unable to implement
> > EnumerableCorrelate
> > Suppressed: java.lang.NullPointerException: variable $cor0 is not
> found
> > at java.base/java.util.Objects.requireNonNull(Objects.java:334)
> > at
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$BaseContext.getAliasContext(SqlImplementor.java:1429)
> > at
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:628)
> > ....
> > at
> >
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:427)
> >
> > And here is the entire thing:
> > ======================================
> > java.sql.SQLException: Error while preparing plan
> > [EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
> > requiredColumns=[{0}])
> > JdbcToEnumerableConverter
> > JdbcTableScan(table=[[hsql, PUBLIC, houses]])
> > EnumerableCollect(field=[EXPR$0])
> > EnumerableProject(id=[$0], name=[$1], todos=[$3])
> > EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
> > requiredColumns=[{0}])
> > JdbcToEnumerableConverter
> > JdbcFilter(condition=[=($2, $cor0.id)])
> > JdbcTableScan(table=[[hsql, PUBLIC, users]])
> > EnumerableCollect(field=[EXPR$0])
> > JdbcToEnumerableConverter
> > JdbcProject(id=[$0], description=[$2])
> > JdbcFilter(condition=[=($1, $cor1.id)])
> > JdbcTableScan(table=[[hsql, PUBLIC, todos]])
> > ]
> > at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> > at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> > at
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:239)
> > at
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.access$100(CalciteConnectionImpl.java:101)
> > at
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl$2.prepareStatement(CalciteConnectionImpl.java:188)
> > at CalciteSchemaManager.executeQuery(CalciteSchemaManager.kt:209)
> > at CalciteSchemaManager.executeQuery(CalciteSchemaManager.kt:213)
> > at ForeignKeyTest.throwawayTest(ForeignKeyTest.kt:265)
> >
> > Caused by: java.lang.IllegalStateException: Unable to implement
> > EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
> > requiredColumns=[{0}]): rowcount = 22500.0, cumulative cost = {318610.0
> > rows, 562611.0 cpu, 0.0 io}, id = 315
> > JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = {110.0
> > rows, 111.0 cpu, 0.0 io}, id = 293
> > JdbcTableScan(table=[[hsql, PUBLIC, houses]]): rowcount = 100.0,
> > cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 15
> > EnumerableCollect(field=[EXPR$0]): rowcount = 225.0, cumulative cost =
> > {2959.0 rows, 5625.0 cpu, 0.0 io}, id = 313
> > EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}],
> > todos=[$t3]): rowcount = 225.0, cumulative cost = {2734.0 rows, 5400.0
> cpu,
> > 0.0 io}, id = 317
> > EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
> > requiredColumns=[{0}]): rowcount = 225.0, cumulative cost = {2509.0 rows,
> > 3825.0 cpu, 0.0 io}, id = 309
> > JdbcToEnumerableConverter: rowcount = 15.0, cumulative cost =
> > {116.5 rows, 202.5 cpu, 0.0 io}, id = 298
> > JdbcFilter(condition=[=($2, $cor0.id)]): rowcount = 15.0,
> > cumulative cost = {115.0 rows, 201.0 cpu, 0.0 io}, id = 296
> > JdbcTableScan(table=[[hsql, PUBLIC, users]]): rowcount =
> > 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 16
> > EnumerableCollect(field=[EXPR$0]): rowcount = 15.0, cumulative
> > cost = {143.5 rows, 241.5 cpu, 0.0 io}, id = 307
> > JdbcToEnumerableConverter: rowcount = 15.0, cumulative cost =
> > {128.5 rows, 226.5 cpu, 0.0 io}, id = 305
> > JdbcProject(id=[$0], description=[$2]): rowcount = 15.0,
> > cumulative cost = {127.0 rows, 225.0 cpu, 0.0 io}, id = 303
> > JdbcFilter(condition=[=($1, $cor1.id)]): rowcount = 15.0,
> > cumulative cost = {115.0 rows, 201.0 cpu, 0.0 io}, id = 301
> > JdbcTableScan(table=[[hsql, PUBLIC, todos]]): rowcount =
> > 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 18
> >
> > at
> >
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114)
> > at
> >
> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:114)
> > at
> >
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1130)
> > at
> >
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1032)
> > at
> >
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:988)
> > at
> >
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:668)
> > at
> >
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:513)
> > at
> >
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:483)
> > at
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:249)
> > at
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:229)
> > ... 88 more
> > Suppressed: java.lang.NullPointerException: variable $cor0 is not found
> > at java.base/java.util.Objects.requireNonNull(Objects.java:334)
> > at
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$BaseContext.getAliasContext(SqlImplementor.java:1429)
> > at
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:628)
> > at
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:1069)
> > at
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.callToSql(SqlImplementor.java:776)
> > at
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:750)
> > at
> >
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:427)
> > at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
> > Method)
> > at
> >
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
> > at
> >
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> > at java.base/java.lang.reflect.Method.invoke(Method.java:568)
> > at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
> > 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:201)
> > at
> >
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:189)
> > at
> >
> org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.generateSql(JdbcToEnumerableConverter.java:351)
> > at
> >
> org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.implement(JdbcToEnumerableConverter.java:107)
> > at
> >
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
> > at
> >
> org.apache.calcite.adapter.enumerable.EnumerableCorrelate.implement(EnumerableCorrelate.java:113)
> > at
> >
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
> > at
> >
> org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:118)
> > at
> >
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
> > at
> >
> org.apache.calcite.adapter.enumerable.EnumerableCollect.implement(EnumerableCollect.java:81)
> > at
> >
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
> > at
> >
> org.apache.calcite.adapter.enumerable.EnumerableCorrelate.implement(EnumerableCorrelate.java:139)
> > at
> >
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111)
> > ... 97 more
> >
> > On Mon, Feb 14, 2022 at 1:14 PM Ruben Q L <ru...@gmail.com> wrote:
> >
> >> Hello Gavin,
> >>
> >> what's the full stack trace that you get?
> >>
> >> On Mon, Feb 14, 2022 at 3:24 PM Gavin Ray <ra...@gmail.com>
> wrote:
> >>
> >> > The following query seems to work perfectly:
> >> >
> >> > SELECT
> >> > "houses"."id",
> >> > "houses"."name",
> >> > "houses"."address",
> >> > ARRAY(
> >> > SELECT
> >> > "users"."id",
> >> > "users"."name"
> >> > FROM
> >> > "users"
> >> > WHERE
> >> > "users"."house_id" = "houses"."id"
> >> > ) AS "users"
> >> > FROM
> >> > "houses";
> >> >
> >> > However, if I modify the inner ARRAY() query for "users" to be:
> >> >
> >> > SELECT
> >> > "users"."id",
> >> > "users"."name",
> >> > ARRAY(
> >> > SELECT
> >> > "todos"."id",
> >> > "todos"."description"
> >> > FROM
> >> > "todos"
> >> > WHERE
> >> > "todos"."user_id" = "users"."id"
> >> > ) AS "todos"
> >> > FROM
> >> > "users"
> >> >
> >> > ===================================================
> >> >
> >> > Unable to implement EnumerableNestedLoopJoin(condition=[true],
> >> > joinType=[inner]): rowcount = 22500.0, cumulative cost = {227855.0
> rows,
> >> > 18242.0 cpu, 0.0 io}, id = 12787
> >> > EnumerableTableScan(table=[[example, houses]]): rowcount = 100.0,
> >> > cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12718
> >> > EnumerableCollect(field=[x]): rowcount = 225.0, cumulative cost =
> >> {2755.0
> >> > rows, 18141.0 cpu, 0.0 io}, id = 12785
> >> > EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}],
> >> todos=[$t3]):
> >> > rowcount = 225.0, cumulative cost = {2530.0 rows, 17916.0 cpu, 0.0
> io},
> >> id
> >> > = 12793
> >> > EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
> >> > requiredColumns=[{0}]): rowcount = 225.0, cumulative cost = {2305.0
> >> rows,
> >> > 16341.0 cpu, 0.0 io}, id = 12781
> >> > EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor0], expr#4=[$
> >> > t3.id],
> >> > expr#5=[=($t2, $t4)], proj#0..2=[{exprs}], $condition=[$t5]):
> rowcount =
> >> > 15.0, cumulative cost = {115.0 rows, 1101.0 cpu, 0.0 io}, id = 12789
> >> > EnumerableTableScan(table=[[example, users]]): rowcount =
> >> 100.0,
> >> > cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12722
> >> > EnumerableCollect(field=[x]): rowcount = 15.0, cumulative
> cost =
> >> > {130.0 rows, 1016.0 cpu, 0.0 io}, id = 12779
> >> > EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor1],
> expr#4=[$
> >> > t3.id], expr#5=[=($t1, $t4)], id=[$t0], description=[$t2],
> >> > $condition=[$t5]): rowcount = 15.0, cumulative cost = {115.0 rows,
> >> 1001.0
> >> > cpu, 0.0 io}, id = 12797
> >> > EnumerableTableScan(table=[[example, todos]]): rowcount =
> >> > 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12728
> >> > (state=,code=0)
> >> >
> >>
> >
>
Re: "Unable to implement EnumerableNestedLoopJoin in SELECT(a, b, ARRAY(c, d, ARRAY(e)))"
Posted by Gavin Ray <ra...@gmail.com>.
Digging into this more to try to better understand Calcite and hopefully
make
progress,it seems like the query breaks here:
https://github.com/apache/calcite/blob/5b2de4ef5c9447bc9f7aff98dd049bd32af5c53d/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1450-L1454
@Override protected Context getAliasContext(RexCorrelVariable variable)
{
return requireNonNull(
correlTableMap.get(variable.id),
() -> "variable " + variable.id + " is not found");
}
Unfortunately, this feature (at least I think so?) is the barrier to
me being able to make efficient cross-datasource queries that return the
right
data shape for GraphQL responses.
My current duct-tape hack is to split the query into query-per-join which I
assume defeats most of Calcite's optimization and planning abilities =(
It's not much, but I'm also willing to offer $250 if anyone could help me
fix
this or figure out an alternative solution.
On Mon, Feb 14, 2022 at 4:26 PM Gavin Ray <ra...@gmail.com> wrote:
> Apologies for the slow reply Ruben, I appreciate your help.
> The full stack trace (I was prototyping in sqlline) seems to be more
> helpful:
>
> Here is what seems to be the most useful bits:
> ======================================
> java.sql.SQLException: Error while preparing plan
> [EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{0}])
> JdbcToEnumerableConverter
>
> Caused by: java.lang.IllegalStateException: Unable to implement
> EnumerableCorrelate
> Suppressed: java.lang.NullPointerException: variable $cor0 is not found
> at java.base/java.util.Objects.requireNonNull(Objects.java:334)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$BaseContext.getAliasContext(SqlImplementor.java:1429)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:628)
> ....
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:427)
>
> And here is the entire thing:
> ======================================
> java.sql.SQLException: Error while preparing plan
> [EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{0}])
> JdbcToEnumerableConverter
> JdbcTableScan(table=[[hsql, PUBLIC, houses]])
> EnumerableCollect(field=[EXPR$0])
> EnumerableProject(id=[$0], name=[$1], todos=[$3])
> EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
> requiredColumns=[{0}])
> JdbcToEnumerableConverter
> JdbcFilter(condition=[=($2, $cor0.id)])
> JdbcTableScan(table=[[hsql, PUBLIC, users]])
> EnumerableCollect(field=[EXPR$0])
> JdbcToEnumerableConverter
> JdbcProject(id=[$0], description=[$2])
> JdbcFilter(condition=[=($1, $cor1.id)])
> JdbcTableScan(table=[[hsql, PUBLIC, todos]])
> ]
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:239)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.access$100(CalciteConnectionImpl.java:101)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl$2.prepareStatement(CalciteConnectionImpl.java:188)
> at CalciteSchemaManager.executeQuery(CalciteSchemaManager.kt:209)
> at CalciteSchemaManager.executeQuery(CalciteSchemaManager.kt:213)
> at ForeignKeyTest.throwawayTest(ForeignKeyTest.kt:265)
>
> Caused by: java.lang.IllegalStateException: Unable to implement
> EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{0}]): rowcount = 22500.0, cumulative cost = {318610.0
> rows, 562611.0 cpu, 0.0 io}, id = 315
> JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = {110.0
> rows, 111.0 cpu, 0.0 io}, id = 293
> JdbcTableScan(table=[[hsql, PUBLIC, houses]]): rowcount = 100.0,
> cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 15
> EnumerableCollect(field=[EXPR$0]): rowcount = 225.0, cumulative cost =
> {2959.0 rows, 5625.0 cpu, 0.0 io}, id = 313
> EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}],
> todos=[$t3]): rowcount = 225.0, cumulative cost = {2734.0 rows, 5400.0 cpu,
> 0.0 io}, id = 317
> EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
> requiredColumns=[{0}]): rowcount = 225.0, cumulative cost = {2509.0 rows,
> 3825.0 cpu, 0.0 io}, id = 309
> JdbcToEnumerableConverter: rowcount = 15.0, cumulative cost =
> {116.5 rows, 202.5 cpu, 0.0 io}, id = 298
> JdbcFilter(condition=[=($2, $cor0.id)]): rowcount = 15.0,
> cumulative cost = {115.0 rows, 201.0 cpu, 0.0 io}, id = 296
> JdbcTableScan(table=[[hsql, PUBLIC, users]]): rowcount =
> 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 16
> EnumerableCollect(field=[EXPR$0]): rowcount = 15.0, cumulative
> cost = {143.5 rows, 241.5 cpu, 0.0 io}, id = 307
> JdbcToEnumerableConverter: rowcount = 15.0, cumulative cost =
> {128.5 rows, 226.5 cpu, 0.0 io}, id = 305
> JdbcProject(id=[$0], description=[$2]): rowcount = 15.0,
> cumulative cost = {127.0 rows, 225.0 cpu, 0.0 io}, id = 303
> JdbcFilter(condition=[=($1, $cor1.id)]): rowcount = 15.0,
> cumulative cost = {115.0 rows, 201.0 cpu, 0.0 io}, id = 301
> JdbcTableScan(table=[[hsql, PUBLIC, todos]]): rowcount =
> 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 18
>
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114)
> at
> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:114)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1130)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1032)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:988)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:668)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:513)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:483)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:249)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:229)
> ... 88 more
> Suppressed: java.lang.NullPointerException: variable $cor0 is not found
> at java.base/java.util.Objects.requireNonNull(Objects.java:334)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$BaseContext.getAliasContext(SqlImplementor.java:1429)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:628)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:1069)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.callToSql(SqlImplementor.java:776)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:750)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:427)
> at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
> Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:568)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
> 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:201)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:189)
> at
> org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.generateSql(JdbcToEnumerableConverter.java:351)
> at
> org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.implement(JdbcToEnumerableConverter.java:107)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
> at
> org.apache.calcite.adapter.enumerable.EnumerableCorrelate.implement(EnumerableCorrelate.java:113)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
> at
> org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:118)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
> at
> org.apache.calcite.adapter.enumerable.EnumerableCollect.implement(EnumerableCollect.java:81)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
> at
> org.apache.calcite.adapter.enumerable.EnumerableCorrelate.implement(EnumerableCorrelate.java:139)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111)
> ... 97 more
>
> On Mon, Feb 14, 2022 at 1:14 PM Ruben Q L <ru...@gmail.com> wrote:
>
>> Hello Gavin,
>>
>> what's the full stack trace that you get?
>>
>> On Mon, Feb 14, 2022 at 3:24 PM Gavin Ray <ra...@gmail.com> wrote:
>>
>> > The following query seems to work perfectly:
>> >
>> > SELECT
>> > "houses"."id",
>> > "houses"."name",
>> > "houses"."address",
>> > ARRAY(
>> > SELECT
>> > "users"."id",
>> > "users"."name"
>> > FROM
>> > "users"
>> > WHERE
>> > "users"."house_id" = "houses"."id"
>> > ) AS "users"
>> > FROM
>> > "houses";
>> >
>> > However, if I modify the inner ARRAY() query for "users" to be:
>> >
>> > SELECT
>> > "users"."id",
>> > "users"."name",
>> > ARRAY(
>> > SELECT
>> > "todos"."id",
>> > "todos"."description"
>> > FROM
>> > "todos"
>> > WHERE
>> > "todos"."user_id" = "users"."id"
>> > ) AS "todos"
>> > FROM
>> > "users"
>> >
>> > ===================================================
>> >
>> > Unable to implement EnumerableNestedLoopJoin(condition=[true],
>> > joinType=[inner]): rowcount = 22500.0, cumulative cost = {227855.0 rows,
>> > 18242.0 cpu, 0.0 io}, id = 12787
>> > EnumerableTableScan(table=[[example, houses]]): rowcount = 100.0,
>> > cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12718
>> > EnumerableCollect(field=[x]): rowcount = 225.0, cumulative cost =
>> {2755.0
>> > rows, 18141.0 cpu, 0.0 io}, id = 12785
>> > EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}],
>> todos=[$t3]):
>> > rowcount = 225.0, cumulative cost = {2530.0 rows, 17916.0 cpu, 0.0 io},
>> id
>> > = 12793
>> > EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
>> > requiredColumns=[{0}]): rowcount = 225.0, cumulative cost = {2305.0
>> rows,
>> > 16341.0 cpu, 0.0 io}, id = 12781
>> > EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor0], expr#4=[$
>> > t3.id],
>> > expr#5=[=($t2, $t4)], proj#0..2=[{exprs}], $condition=[$t5]): rowcount =
>> > 15.0, cumulative cost = {115.0 rows, 1101.0 cpu, 0.0 io}, id = 12789
>> > EnumerableTableScan(table=[[example, users]]): rowcount =
>> 100.0,
>> > cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12722
>> > EnumerableCollect(field=[x]): rowcount = 15.0, cumulative cost =
>> > {130.0 rows, 1016.0 cpu, 0.0 io}, id = 12779
>> > EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor1], expr#4=[$
>> > t3.id], expr#5=[=($t1, $t4)], id=[$t0], description=[$t2],
>> > $condition=[$t5]): rowcount = 15.0, cumulative cost = {115.0 rows,
>> 1001.0
>> > cpu, 0.0 io}, id = 12797
>> > EnumerableTableScan(table=[[example, todos]]): rowcount =
>> > 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12728
>> > (state=,code=0)
>> >
>>
>
Re: "Unable to implement EnumerableNestedLoopJoin in SELECT(a, b, ARRAY(c, d, ARRAY(e)))"
Posted by Gavin Ray <ra...@gmail.com>.
Apologies for the slow reply Ruben, I appreciate your help.
The full stack trace (I was prototyping in sqlline) seems to be more
helpful:
Here is what seems to be the most useful bits:
======================================
java.sql.SQLException: Error while preparing plan
[EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
JdbcToEnumerableConverter
Caused by: java.lang.IllegalStateException: Unable to implement
EnumerableCorrelate
Suppressed: java.lang.NullPointerException: variable $cor0 is not found
at java.base/java.util.Objects.requireNonNull(Objects.java:334)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$BaseContext.getAliasContext(SqlImplementor.java:1429)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:628)
....
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:427)
And here is the entire thing:
======================================
java.sql.SQLException: Error while preparing plan
[EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
JdbcToEnumerableConverter
JdbcTableScan(table=[[hsql, PUBLIC, houses]])
EnumerableCollect(field=[EXPR$0])
EnumerableProject(id=[$0], name=[$1], todos=[$3])
EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{0}])
JdbcToEnumerableConverter
JdbcFilter(condition=[=($2, $cor0.id)])
JdbcTableScan(table=[[hsql, PUBLIC, users]])
EnumerableCollect(field=[EXPR$0])
JdbcToEnumerableConverter
JdbcProject(id=[$0], description=[$2])
JdbcFilter(condition=[=($1, $cor1.id)])
JdbcTableScan(table=[[hsql, PUBLIC, todos]])
]
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:239)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.access$100(CalciteConnectionImpl.java:101)
at
org.apache.calcite.jdbc.CalciteConnectionImpl$2.prepareStatement(CalciteConnectionImpl.java:188)
at CalciteSchemaManager.executeQuery(CalciteSchemaManager.kt:209)
at CalciteSchemaManager.executeQuery(CalciteSchemaManager.kt:213)
at ForeignKeyTest.throwawayTest(ForeignKeyTest.kt:265)
Caused by: java.lang.IllegalStateException: Unable to implement
EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}]): rowcount = 22500.0, cumulative cost = {318610.0
rows, 562611.0 cpu, 0.0 io}, id = 315
JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = {110.0
rows, 111.0 cpu, 0.0 io}, id = 293
JdbcTableScan(table=[[hsql, PUBLIC, houses]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 15
EnumerableCollect(field=[EXPR$0]): rowcount = 225.0, cumulative cost =
{2959.0 rows, 5625.0 cpu, 0.0 io}, id = 313
EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}], todos=[$t3]):
rowcount = 225.0, cumulative cost = {2734.0 rows, 5400.0 cpu, 0.0 io}, id =
317
EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{0}]): rowcount = 225.0, cumulative cost = {2509.0 rows,
3825.0 cpu, 0.0 io}, id = 309
JdbcToEnumerableConverter: rowcount = 15.0, cumulative cost =
{116.5 rows, 202.5 cpu, 0.0 io}, id = 298
JdbcFilter(condition=[=($2, $cor0.id)]): rowcount = 15.0,
cumulative cost = {115.0 rows, 201.0 cpu, 0.0 io}, id = 296
JdbcTableScan(table=[[hsql, PUBLIC, users]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 16
EnumerableCollect(field=[EXPR$0]): rowcount = 15.0, cumulative cost
= {143.5 rows, 241.5 cpu, 0.0 io}, id = 307
JdbcToEnumerableConverter: rowcount = 15.0, cumulative cost =
{128.5 rows, 226.5 cpu, 0.0 io}, id = 305
JdbcProject(id=[$0], description=[$2]): rowcount = 15.0,
cumulative cost = {127.0 rows, 225.0 cpu, 0.0 io}, id = 303
JdbcFilter(condition=[=($1, $cor1.id)]): rowcount = 15.0,
cumulative cost = {115.0 rows, 201.0 cpu, 0.0 io}, id = 301
JdbcTableScan(table=[[hsql, PUBLIC, todos]]): rowcount =
100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 18
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114)
at
org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:114)
at
org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1130)
at
org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1032)
at
org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:988)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:668)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:513)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:483)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:249)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:229)
... 88 more
Suppressed: java.lang.NullPointerException: variable $cor0 is not found
at java.base/java.util.Objects.requireNonNull(Objects.java:334)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$BaseContext.getAliasContext(SqlImplementor.java:1429)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:628)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:1069)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.callToSql(SqlImplementor.java:776)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:750)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:427)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
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:201)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:189)
at
org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.generateSql(JdbcToEnumerableConverter.java:351)
at
org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.implement(JdbcToEnumerableConverter.java:107)
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
at
org.apache.calcite.adapter.enumerable.EnumerableCorrelate.implement(EnumerableCorrelate.java:113)
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
at
org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:118)
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
at
org.apache.calcite.adapter.enumerable.EnumerableCollect.implement(EnumerableCollect.java:81)
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104)
at
org.apache.calcite.adapter.enumerable.EnumerableCorrelate.implement(EnumerableCorrelate.java:139)
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111)
... 97 more
On Mon, Feb 14, 2022 at 1:14 PM Ruben Q L <ru...@gmail.com> wrote:
> Hello Gavin,
>
> what's the full stack trace that you get?
>
> On Mon, Feb 14, 2022 at 3:24 PM Gavin Ray <ra...@gmail.com> wrote:
>
> > The following query seems to work perfectly:
> >
> > SELECT
> > "houses"."id",
> > "houses"."name",
> > "houses"."address",
> > ARRAY(
> > SELECT
> > "users"."id",
> > "users"."name"
> > FROM
> > "users"
> > WHERE
> > "users"."house_id" = "houses"."id"
> > ) AS "users"
> > FROM
> > "houses";
> >
> > However, if I modify the inner ARRAY() query for "users" to be:
> >
> > SELECT
> > "users"."id",
> > "users"."name",
> > ARRAY(
> > SELECT
> > "todos"."id",
> > "todos"."description"
> > FROM
> > "todos"
> > WHERE
> > "todos"."user_id" = "users"."id"
> > ) AS "todos"
> > FROM
> > "users"
> >
> > ===================================================
> >
> > Unable to implement EnumerableNestedLoopJoin(condition=[true],
> > joinType=[inner]): rowcount = 22500.0, cumulative cost = {227855.0 rows,
> > 18242.0 cpu, 0.0 io}, id = 12787
> > EnumerableTableScan(table=[[example, houses]]): rowcount = 100.0,
> > cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12718
> > EnumerableCollect(field=[x]): rowcount = 225.0, cumulative cost =
> {2755.0
> > rows, 18141.0 cpu, 0.0 io}, id = 12785
> > EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}],
> todos=[$t3]):
> > rowcount = 225.0, cumulative cost = {2530.0 rows, 17916.0 cpu, 0.0 io},
> id
> > = 12793
> > EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
> > requiredColumns=[{0}]): rowcount = 225.0, cumulative cost = {2305.0 rows,
> > 16341.0 cpu, 0.0 io}, id = 12781
> > EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor0], expr#4=[$
> > t3.id],
> > expr#5=[=($t2, $t4)], proj#0..2=[{exprs}], $condition=[$t5]): rowcount =
> > 15.0, cumulative cost = {115.0 rows, 1101.0 cpu, 0.0 io}, id = 12789
> > EnumerableTableScan(table=[[example, users]]): rowcount =
> 100.0,
> > cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12722
> > EnumerableCollect(field=[x]): rowcount = 15.0, cumulative cost =
> > {130.0 rows, 1016.0 cpu, 0.0 io}, id = 12779
> > EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor1], expr#4=[$
> > t3.id], expr#5=[=($t1, $t4)], id=[$t0], description=[$t2],
> > $condition=[$t5]): rowcount = 15.0, cumulative cost = {115.0 rows, 1001.0
> > cpu, 0.0 io}, id = 12797
> > EnumerableTableScan(table=[[example, todos]]): rowcount =
> > 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12728
> > (state=,code=0)
> >
>
Re: "Unable to implement EnumerableNestedLoopJoin in SELECT(a, b, ARRAY(c, d, ARRAY(e)))"
Posted by Ruben Q L <ru...@gmail.com>.
Hello Gavin,
what's the full stack trace that you get?
On Mon, Feb 14, 2022 at 3:24 PM Gavin Ray <ra...@gmail.com> wrote:
> The following query seems to work perfectly:
>
> SELECT
> "houses"."id",
> "houses"."name",
> "houses"."address",
> ARRAY(
> SELECT
> "users"."id",
> "users"."name"
> FROM
> "users"
> WHERE
> "users"."house_id" = "houses"."id"
> ) AS "users"
> FROM
> "houses";
>
> However, if I modify the inner ARRAY() query for "users" to be:
>
> SELECT
> "users"."id",
> "users"."name",
> ARRAY(
> SELECT
> "todos"."id",
> "todos"."description"
> FROM
> "todos"
> WHERE
> "todos"."user_id" = "users"."id"
> ) AS "todos"
> FROM
> "users"
>
> ===================================================
>
> Unable to implement EnumerableNestedLoopJoin(condition=[true],
> joinType=[inner]): rowcount = 22500.0, cumulative cost = {227855.0 rows,
> 18242.0 cpu, 0.0 io}, id = 12787
> EnumerableTableScan(table=[[example, houses]]): rowcount = 100.0,
> cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12718
> EnumerableCollect(field=[x]): rowcount = 225.0, cumulative cost = {2755.0
> rows, 18141.0 cpu, 0.0 io}, id = 12785
> EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}], todos=[$t3]):
> rowcount = 225.0, cumulative cost = {2530.0 rows, 17916.0 cpu, 0.0 io}, id
> = 12793
> EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
> requiredColumns=[{0}]): rowcount = 225.0, cumulative cost = {2305.0 rows,
> 16341.0 cpu, 0.0 io}, id = 12781
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor0], expr#4=[$
> t3.id],
> expr#5=[=($t2, $t4)], proj#0..2=[{exprs}], $condition=[$t5]): rowcount =
> 15.0, cumulative cost = {115.0 rows, 1101.0 cpu, 0.0 io}, id = 12789
> EnumerableTableScan(table=[[example, users]]): rowcount = 100.0,
> cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12722
> EnumerableCollect(field=[x]): rowcount = 15.0, cumulative cost =
> {130.0 rows, 1016.0 cpu, 0.0 io}, id = 12779
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor1], expr#4=[$
> t3.id], expr#5=[=($t1, $t4)], id=[$t0], description=[$t2],
> $condition=[$t5]): rowcount = 15.0, cumulative cost = {115.0 rows, 1001.0
> cpu, 0.0 io}, id = 12797
> EnumerableTableScan(table=[[example, todos]]): rowcount =
> 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12728
> (state=,code=0)
>