[jira] [Commented] (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=17707696#comment-17707696 ] 

Dmitry Sysolyatin commented on CALCITE-5590:
--------------------------------------------

My previous fix, which I implemented in CALCITE-5209, was incorrect. It did not account for cases where a SubQuery is part of the GROUP BY field, rather than the entire field.

A more appropriate fix might look like: [https://github.com/dssysolyatin/calcite/commit/a1e5774f91697299061713cb86104e976e7fbfe7|https://github.com/dssysolyatin/calcite/commit/a1e5774f91697299061713cb86104e976e7fbfe7.]

The cause of CALCITE-5209 is that SqlToRelConverter replaces SubQueries within the selectList [1], followed by replacing expressions in the selectList with references to fields in the GROUP BY [2][3]. 

I modified the process so that Calcite first tries to find SubQueries in the selectList without converting them. Then, it attempts to replace expressions inside the selectList with references to fields in GROUP BY, and only after that, it substitutes the SubQuery.

However, some tests, like RelOptRulesTest, are failing now because the plan is different and uses an unusual "offset(x)" in the plan.

[~libenchao] Can you take a look at the commit? If it is acceptable, I will try to wrap up the fix next Saturday if nobody addresses it during the week. Thanks!

[1] https://github.com/dssysolyatin/calcite/commit/a1e5774f91697299061713cb86104e976e7fbfe7#diff-5d7c774cd39d94b119eedcf3d361e72406c7864e916736c533bbe8f893ca72bcL3510
[2] https://github.com/dssysolyatin/calcite/commit/a1e5774f91697299061713cb86104e976e7fbfe7#diff-5d7c774cd39d94b119eedcf3d361e72406c7864e916736c533bbe8f893ca72bcR3520
[3] https://github.com/dssysolyatin/calcite/commit/a1e5774f91697299061713cb86104e976e7fbfe7#diff-5d7c774cd39d94b119eedcf3d361e72406c7864e916736c533bbe8f893ca72bcR5313

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