[CALCITE-559] Correlated scalar subquery in WHERE gives error

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.7.0
• Assignee: Julian Hyde
• Reporter: Jinfeng Ni
• Created: 16/Jan/15 02:05
• Updated: 22/Mar/16 01:18
• Resolved: 26/Jan/16 03:29

Description

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

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)

2. As a Junit testcase:

  @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);
  }

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 .....

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.

Issue Links

• depends upon: CALCITE-1045 Decorrelate sub-queries in Project and Join
• duplicates: CALCITE-864 Correlation variable has incorrect row type if it is populated by right side of a Join