[CALCITE-685] Correlated scalar sub-query in SELECT clause throws

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: sub-query
• Assignee: Unassigned
• Reporter: Jinfeng Ni
• Created: 17/Apr/15 00:11
• Updated: 21/Aug/24 19:03

Description

For the following query, where a correlated scalar subquery is put in select list, Calcite will hit NPE in RelDecorrelator.

select e.department_id, sum(e.employee_id),
       ( select sum(e2.employee_id)
         from  employee e2
         where e.department_id = e2.department_id
       )
from employee e
group by e.department_id;

Caused by: java.lang.NullPointerException
	at org.apache.calcite.sql2rel.RelDecorrelator.createValueGenerator(RelDecorrelator.java:733)
	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateInputWithValueGenerator(RelDecorrelator.java:842)
	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:902)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at org.apache.calcite.util.ReflectUtil.invokeVisitorInternal(ReflectUtil.java:256)
	at org.apache.calcite.util.ReflectUtil.invokeVisitor(ReflectUtil.java:213)
	at org.apache.calcite.util.ReflectUtil$1.invokeVisitor(ReflectUtil.java:476)
	at org.apache.calcite.sql2rel.RelDecorrelator$DecorrelateRelVisitor.visit(RelDecorrelator.java:1420)
	at org.apache.calcite.rel.SingleRel.childrenAccept(SingleRel.java:72)
.....

Here is the unit test case I used to re-produce this problem in JdbcTest.java

  @Test public void testCorreScalarSubQInSelect()
      throws ClassNotFoundException, SQLException {
    String query = "select e.department_id, sum(e.employee_id),\n"
        + "       ( select sum(e2.employee_id)\n"
        + "         from  employee e2\n"
        + "         where e.department_id = e2.department_id\n"
        + "       )\n"
        + " from employee e\n"
        + " group by e.department_id\n";
    CalciteAssert.that()
        .with(CalciteAssert.Config.FOODMART_CLONE)
        .with(Lex.JAVA)
        .query(query)
        .returnsCount(0);
  }

Issue Links

• is related to: CALCITE-2136 SubQueryRemoveRule.Project generated error plan
• is related to: CALCITE-4945 Runtime compilation fails on subquery.
• is related to: IGNITE-16000 Calcite engine. Assertion error on SubQueryRemoveRule for correlated queries
• is related to: IGNITE-16147 Compilation failes on subquery
• relates to: CALCITE-1541 Update RelDecorrelator to work on Filter, Join, Project etc