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