[
https://issues.apache.org/jira/browse/CALCITE-559?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14280644#comment-14280644
]
Vladimir Sitnikov commented on CALCITE-559:
-------------------------------------------
Plan is as follows (one more case to make de-correlation rule-based):
{noformat}
LogicalProject(employee_id=[$0], department_id=[$17])
LogicalFilter(condition=[AND(=($7, $17), >($11, $19))])
LogicalProject(employee_id=[$0], full_name=[$1], first_name=[$2],
last_name=[$3], position_id=[$4], position_title=[$5], store_id=[$6],
department_id=[$7], birth_date=[$8], hire_date=[$9], end_date=[$10],
salary=[$11], supervisor_id=[$12], education_level=[$13], marital_status=[$14],
gender=[$15], management_role=[$16], department_id0=[$17],
department_description=[$18], EXPR$0=[$20])
LogicalJoin(condition=[=($6, $19)], joinType=[left])
LogicalJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[foodmart2, employee]])
EnumerableTableScan(table=[[foodmart2, department]])
LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])
LogicalProject($f0=[$1], salary=[$0])
LogicalProject(salary=[$11], $f0=[$17])
LogicalFilter(condition=[=($6, $17)])
LogicalJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[foodmart2, employee]])
LogicalAggregate(group=[{0}])
LogicalProject($f0=[$6])
LogicalJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[foodmart2, employee]])
EnumerableTableScan(table=[[foodmart2, department]])
{noformat}
{{$19}} is nullable (I believe it is correct), however join thinks the input is
not nullable (I believe it is not correct)
> 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)
