You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by GitBox <gi...@apache.org> on 2022/07/29 23:01:01 UTC

[GitHub] [calcite] GavinRay97 commented on pull request #2116: [CALCITE-4188] Support EnumerableBatchNestedLoopJoin inJdbcToEnumerab…

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-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org