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 <zabe...@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 <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) > > >> > > > >> > > > > > >
