[
https://issues.apache.org/jira/browse/CALCITE-559?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14280884#comment-14280884
]
Vladimir Sitnikov commented on CALCITE-559:
-------------------------------------------
{quote} Performance-wise, is it optimal to duplicate those n table joins in the
subquery? {quote}
If we "duplicate E+D", we might scan just a subset of E2 since we know the set
of potential IDs to use in E2.
The query you suggest makes sense as well. For instance, in case when all the
tables are big and we do not want to recompute the result.
It would be better if Calcite could use cost-based de-correlation.
The problem is it is not implemented.
> Correlated subquery will hit exception in Calcite
> -------------------------------------------------
>
> Key: CALCITE-559
> URL: https://issues.apache.org/jira/browse/CALCITE-559
> Project: Calcite
> Issue Type: Bug
> Reporter: Jinfeng Ni
> Assignee: Julian Hyde
>
> Calcite will throw exception, for a query with a correlated subquery. I
> tried both sqlline and as a Junit test case inJDBCTest. Both of them hit
> exception:
> 1. sqlline
> {code}
> sqlline> !connect
> jdbc:calcite:model=core/src/test/resources/hsqldb-foodmart-lattice-model.json
> admin admin
> 0: jdbc:calcite:model=core/src/test/resources> select e."employee_id",
> d."department_id" from "foodmart"."employee" e, "foodmart"."department" d
> where e."department_id" = d."department_id" and e."salary" > (select
> avg(e2."salary") from "foodmart"."employee" e2 where e2."store_id" =
> e."store_id");
> Error: exception while executing query: while executing SQL [SELECT "$f0"
> FROM (SELECT "employee"."store_id" AS "$f0"
> FROM "foodmart"."department"
> INNER JOIN "foodmart"."employee") AS "t"
> GROUP BY "$f0"] (state=,code=0)
> {code}
> 2. As a Junit testcase:
> {code}
> @Test public void testJoinCorreScalarSubQ()
> throws ClassNotFoundException, SQLException {
> CalciteAssert.that()
> .with(CalciteAssert.Config.FOODMART_CLONE)
> .query("select e.\"employee_id\", d.\"department_id\" "
> + " from \"employee\" e, \"department\" d "
> + " where e.\"department_id\" = d.\"department_id\" and "
> + " e.\"salary\" > (select avg(e2.\"salary\") "
> + " from \"employee\" e2 "
> + " where e2.\"store_id\" = e.\"store_id\")"
> ).returnsCount(0);
> }
> {code}
> Caused by: java.lang.RuntimeException: With materializationsEnabled=false,
> limit=0
> at
> org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:461)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1159)
> ... 29 more
> Caused by: java.lang.AssertionError: Internal error: Error while applying
> rule EnumerableJoinRule, args
> [rel#160:LogicalJoin.NONE.[](left=rel#75:Subset#0.ENUMERABLE.[],right=rel#93:Subset#9.NONE.[],condition=AND(=($6,
> $17), >($11, $18)),joinType=inner)]
> at org.apache.calcite.util.Util.newInternal(Util.java:739)
> at
> org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:251)
> at ...
> ... 30 more
> Caused by: java.lang.AssertionError: Internal error: Error occurred while
> applying rule EnumerableJoinRule
> at org.apache.calcite.util.Util.newInternal(Util.java:739)
> at ... 44 more
> Caused by: java.lang.AssertionError: type mismatch:
> ref:
> DECIMAL(10, 4)
> input:
> DECIMAL(10, 4) NOT NULL
> at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:1608)
> at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:120)
> at .....
> {code}
> The sqlline run seems to hit exception during enumerable execution, while the
> Junit run seems to hit exception in planning phase. Probably, that's because
> sqlline by default does not turn on assertion check.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
