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