GavinRay97 commented on PR #2116: URL: https://github.com/apache/calcite/pull/2116#issuecomment-1200013442
This is a super valuable PR, just wanted to bring it up again and also give some feedback on it: I rebased this locally off of main today and built it, it works except in certain odd circumstances. For instance, here's the following query being run: ```java String query = "SELECT dname, MULTISET(" + " SELECT ename, MULTISET(" + " SELECT 1 FROM (VALUES (10), (20), (30), (40)) AS T(d_no) WHERE t.d_no IN (10, 20)" + " ) FROM emp AS e WHERE deptno = dept.deptno" + ") FROM dept"; ``` ![image](https://user-images.githubusercontent.com/26604994/181855336-0d721529-f2a6-489a-bfad-515193770bef.png) It also worked with: ```sql String query2 = "select\n" + " \"first_name\",\n" + " multiset(\n" + " select \"store_name\", multiset(\n" + " select \"fname\"\n" + " from \"customer\"\n" + " where \"customer\".\"customer_region_id\" <> 0 \n" + " ) \n" + " from \"store\"\n" + " where \"store_id\" = \"employee\".\"store_id\"\n" + " )\n" + "from \"employee\"\n" + "limit 2"; ``` What I have noticed though is that if the multiset returns an empty relation in a correlated subquery, you get an error about `$cor0` variable being null. To trigger this, you can change the `where customer.customer_region_id` clause to be: ```java where "customer"."customer_region_id" = "store"."region_id" ``` Running that will give you: ```java Correlation variable $cor0 should be defined java.lang.AssertionError: Correlation variable $cor0 should be defined at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.getCorrelVariableGetter(EnumerableRelImplementor.java:472) at org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.generateCorrelate(JdbcToEnumerableConverter.java:351) at org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.implement(JdbcToEnumerableConverter.java:188) 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.EnumerableNestedLoopJoin.implement(EnumerableNestedLoopJoin.java:160) at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104) at org.apache.calcite.adapter.enumerable.EnumerableLimit.implement(EnumerableLimit.java:98) 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.implementRoot(EnumerableRelImplementor.java:111) at org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:114) at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1131) at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:324) ``` And there is one more error which is less understandable to me, you can produce this one by modifying the `emps deps` query to: ```java String query = "SELECT dname, MULTISET(" + " SELECT ename, MULTISET(" + " SELECT * FROM dept AS d WHERE d.deptno = e.deptno" + " ) FROM emp AS e WHERE deptno = dept.deptno" + ") FROM dept"; ``` ```sql java.sql.SQLException: Error while executing SQL: Index 7 out of bounds for length 3 ... Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 7 out of bounds for length 3 at com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:77) at org.apache.calcite.rel.core.TableScan.project(TableScan.java:164) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:1282) ... 66 more ``` I would offer to fix these and push this PR through to finished, but I still don't really have a clue about how Calcite works under the hood and that fancy relational algebra stuff =/ Hope this info is useful at least! -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org