[ https://issues.apache.org/jira/browse/CALCITE-685?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17461060#comment-17461060 ]
Vladimir Steshin commented on CALCITE-685: ------------------------------------------ [~julianhyde], I meant 1.29. Thanks. The bug is actual. Noted: Fails: {code:sql} select e.department_id, ( select 1 from employee e2 where e.department_id = e2.department_id ) from employee e group by e.department_id {code} Works (fails with some kind of correct error 'more than one value in agg SINGLE_VALUE'): {code:sql} select e.department_id, ( select 1 from employee e2 where e.department_id = e2.department_id ) from employee e with plan PLAN=EnumerableCalc(expr#0..3=[{inputs}], department_id=[$t1], EXPR$1=[$t3]) EnumerableMergeJoin(condition=[=($1, $2)], joinType=[left]) EnumerableSort(sort0=[$1], dir0=[ASC]) EnumerableCalc(expr#0..16=[{inputs}], employee_id=[$t0], department_id=[$t7]) EnumerableTableScan(table=[[foodmart2, employee]]) EnumerableSort(sort0=[$0], dir0=[ASC]) EnumerableAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)]) EnumerableCalc(expr#0..16=[{inputs}], expr#17=[1], department_id=[$t7], EXPR$0=[$t17]) EnumerableTableScan(table=[[foodmart2, employee]]) {code} > Correlated scalar sub-query in SELECT clause throws > --------------------------------------------------- > > Key: CALCITE-685 > URL: https://issues.apache.org/jira/browse/CALCITE-685 > Project: Calcite > Issue Type: Bug > Reporter: Jinfeng Ni > Priority: Major > Labels: sub-query > > For the following query, where a correlated scalar subquery is put in select > list, Calcite will hit NPE in RelDecorrelator. > {code} > 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; > {code} > {code} > 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) > ..... > {code} > Here is the unit test case I used to re-produce this problem in JdbcTest.java > {code} > @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); > } > {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)