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 <rube...@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 <ray.gavi...@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)
> >
>
