[jira] [Comment Edited] (CALCITE-5590) NullPointerException when converting 'in' expression that is used inside select list and group by

["Dmitry Sysolyatin (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5590?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17707123#comment-17707123 ] 

Dmitry Sysolyatin edited comment on CALCITE-5590 at 3/31/23 5:29 AM:
---------------------------------------------------------------------

[~libenchao] It is related to CALCITE-5209. If I comment the following code:
{code:java}
// Reference comparing does not work in case when select list has column which refers
        // to the column inside `GROUP BY` clause.
        // For example: SELECT deptno IN (1,2) FROM emp.deptno GROUP BY deptno IN (1,2);
        if (exprClause == SqlImplementor.Clause.SELECT
            && subQuery.clause == SqlImplementor.Clause.GROUP_BY
            && expr.equalsDeep(subQuery.node, Litmus.IGNORE)) {
          return subQuery;
        }
{code}
The result will be (But it is not correct result as well)
> COL
> null
> null
> null

I will try to look deeper tomorrow


was (Author: dmsysolyatin):
[~libenchao] Yes, it is related to CALCITE-5209. If I comment the following code:
{code:java}
// Reference comparing does not work in case when select list has column which refers
        // to the column inside `GROUP BY` clause.
        // For example: SELECT deptno IN (1,2) FROM emp.deptno GROUP BY deptno IN (1,2);
        if (exprClause == SqlImplementor.Clause.SELECT
            && subQuery.clause == SqlImplementor.Clause.GROUP_BY
            && expr.equalsDeep(subQuery.node, Litmus.IGNORE)) {
          return subQuery;
        }
{code}
The result will be (But it is not correct result as well)
> COL
> null
> null
> null

I will try to look deeper tomorrow

> NullPointerException when converting  'in' expression that is used inside select list and group by
> --------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-5590
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5590
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.33.0
>            Reporter: Jiajun Xie
>            Priority: Major
>
> In CALCITE-5209, we changed sub-query handle way.
> It can solve some problems, but it also introduces some new problems.
> Here is a simple case to reproduce, it can work before 1.33.0
> {code:java}
> @Test void testGroupByCaseWhen() {
> final String sql = "select case when deptno in (1) then 1 end as col\n"
> + "from emp\n"
> + "group by deptno, case when deptno in (1) then 1 else 0 end";
> sql(sql).ok();
> } {code}
> {code:java}
> while converting CASE WHEN `EMP`.`DEPTNO` IN (1) THEN 1 ELSE NULL END
> java.lang.RuntimeException: while converting CASE WHEN `EMP`.`DEPTNO` IN (1) THEN 1 ELSE NULL END
> 	at org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$0(ReflectiveConvertletTable.java:86)
> 	at org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:63)
> 	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5591)
> 	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4875)
> 	at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
> 	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5454)
> 	at org.apache.calcite.sql2rel.StandardConvertletTable.lambda$new$9(StandardConvertletTable.java:205)
> 	at org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:63)
> 	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5591)
> 	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4875)
> 	at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
> 	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5454)
> 	at org.apache.calcite.sql2rel.SqlToRelConverter.createAggImpl(SqlToRelConverter.java:3316)
> 	at org.apache.calcite.sql2rel.SqlToRelConverter.convertAgg(SqlToRelConverter.java:3158)
> 	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:784)
> 	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:682)
> 	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3680)
> 	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:597)
> 	at org.apache.calcite.test.SqlToRelTestBase$TesterImpl.convertSqlToRel(SqlToRelTestBase.java:639)
> 	at org.apache.calcite.test.SqlToRelTestBase$TesterImpl.assertConvertsTo(SqlToRelTestBase.java:758)
> 	at org.apache.calcite.test.SqlToRelConverterTest$Sql.convertsTo(SqlToRelConverterTest.java:4502)
> 	at org.apache.calcite.test.SqlToRelConverterTest$Sql.ok(SqlToRelConverterTest.java:4494)
> 	at org.apache.calcite.test.SqlToRelConverterTest.testGroupByCaseWhen(SqlToRelConverterTest.java:4028){code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)