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";
```
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
