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 <[email protected]> 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 <[email protected]> wrote: > >> Hello Gavin, >> >> what's the full stack trace that you get? >> >> On Mon, Feb 14, 2022 at 3:24 PM Gavin Ray <[email protected]> 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) >> > >> >
