[
https://issues.apache.org/jira/browse/CALCITE-7822?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7822:
------------------------------------
Labels: pull-request-available (was: )
> AssertionError "type mismatch" in SqlToRelConverter for OVER () in a GROUP BY
> query
> -----------------------------------------------------------------------------------
>
> Key: CALCITE-7822
> URL: https://issues.apache.org/jira/browse/CALCITE-7822
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.42.0
> Reporter: Vladimir Sitnikov
> Priority: Major
> Labels: pull-request-available
>
> With assertions enabled, a GROUP BY query over {{scott.EMP}} with a window
> aggregate over an empty {{OVER ()}} fails in {{SqlToRelConverter}}. Calcite's
> own test suite runs with assertions enabled, and this test fails there:
> {code:java}
> @Test void testEmptyOverInAggregateQuery() {
> CalciteAssert.that()
> .with(CalciteAssert.Config.SCOTT)
> .query("select deptno, count(*) over () as c from emp group by deptno")
> .returnsUnordered("DEPTNO=10; C=3",
> "DEPTNO=20; C=3",
> "DEPTNO=30; C=3");
> }
> {code}
> {noformat}
> java.lang.AssertionError: type mismatch:
> ref:
> SMALLINT NOT NULL
> input:
> TINYINT
> at org.apache.calcite.util.Litmus.lambda$static$0(Litmus.java:31)
> at
> org.apache.calcite.plan.RelOptUtil.eqUpToNullability(RelOptUtil.java:2279)
> at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:131)
> at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:62)
> at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:125)
> at org.apache.calcite.rex.RexVisitorImpl.visitOver(RexVisitorImpl.java:61)
> at org.apache.calcite.rex.RexOver.accept(RexOver.java:156)
> at org.apache.calcite.rel.core.Project.isValid(Project.java:260)
> at org.apache.calcite.rel.core.Project.<init>(Project.java:106)
> at
> org.apache.calcite.rel.logical.LogicalProject.<init>(LogicalProject.java:75)
> at
> org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:168)
> at
> org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:144)
> at
> org.apache.calcite.rel.core.RelFactories$ProjectFactoryImpl.createProject(RelFactories.java:217)
> at org.apache.calcite.tools.RelBuilder.project_(RelBuilder.java:2267)
> at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:2042)
> at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:2025)
> at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1997)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.createAggImpl(SqlToRelConverter.java:4100)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertAgg(SqlToRelConverter.java:3899)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList(SqlToRelConverter.java:5182)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:825)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:751)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:4340)
> ...
> {noformat}
> h3. Expected
> The query returns three rows: {{DEPTNO=10; C=3}}, {{DEPTNO=20; C=3}},
> {{DEPTNO=30; C=3}}. The same query returns exactly these rows with assertions
> disabled ({{-da}}), and the ROWS form of the window, {{count(*) over (rows
> between unbounded preceding and unbounded following)}}, returns them with
> assertions enabled. The failing check is Calcite's own row-type validation in
> {{Project.isValid}}.
> h3. With assertions disabled
> The query returns the expected rows, but the plan sorts the window by the
> first column of the aggregate, {{DEPTNO}}, although the window has no ORDER
> BY:
> {noformat}
> EnumerableWindow(window#0=[window(order by [0] range between UNBOUNDED
> PRECEDING and UNBOUNDED FOLLOWING aggs [COUNT()])])
> EnumerableAggregate(group=[{7}])
> EnumerableTableScan(table=[[scott, EMP]])
> {noformat}
> h3. Which queries fail
> Each query below ran on the scott schema with assertions enabled:
> {noformat}
> select deptno, count(*) over () from emp group by deptno
> -> AssertionError: type mismatch: ref: SMALLINT NOT NULL input: TINYINT
> select deptno, count(*) over (range between unbounded preceding and unbounded
> following) from emp group by deptno
> -> AssertionError: type mismatch: ref: SMALLINT NOT NULL input: TINYINT
> select job, max(max(sal)) over () from emp group by job
> -> AssertionError: type mismatch: ref: SMALLINT NOT NULL input: VARCHAR(9)
> select deptno, count(*) over (rows between unbounded preceding and unbounded
> following) from emp group by deptno
> -> 20,3; 10,3; 30,3
> select deptno, count(*) over (order by deptno) from emp group by deptno
> -> 10,1; 20,2; 30,3
> select deptno, count(*) over () from (select deptno from emp) group by deptno
> -> 20,3; 10,3; 30,3
> select empno, count(*) over () from emp where empno < 7600 group by empno
> -> 7369,4; 7499,4; 7521,4; 7566,4
> select deptno, count(*) over () from emp where empno < 7600
> -> 20,4; 30,4; 30,4; 20,4
> {noformat}
> The same shape passes on the {{hr}} schema: {{select "deptno", count(*) over
> () from "hr"."emps" group by "deptno"}} returns {{20,4; 10,4}}.
> h3. Versions
> Reproduced on release 1.42.0 (tag {{calcite-1.42.0}}) and on {{main}} at
> commit {{afdea2ec79}}, with the JUnit 5 test suite of the {{core}} module
> (assertions enabled).
> h3. Possible cause (a guess; the report above does not depend on it)
> For a RANGE window without ORDER BY, {{SqlToRelConverter.convertOver}} takes
> an implicit sort key from {{bb.scope.getOrderList()}}.
> {{SelectScope.getOrderList()}} returns the first monotonic expression of the
> FROM clause, which for {{scott.EMP}} would be {{EMPNO}} ({{SMALLINT NOT
> NULL}}). In a GROUP BY query the window is computed on top of the Aggregate,
> so the key becomes field 0 of the Aggregate, which is {{DEPTNO}}
> ({{TINYINT}}). This fits the table above: a ROWS frame, an explicit ORDER BY,
> a sub-query in FROM, and GROUP BY EMPNO each avoid the error.
> h3. Existing reports
> I searched CALCITE for "type mismatch" together with "over ()". The nearest
> issue is CALCITE-6772, which fails with "Conversion to relational algebra
> failed to preserve datatypes" (a nullability difference) for LAG over an
> aggregate with PARTITION BY and ORDER BY. This report has neither in the
> window, and the error is different.
> Found while reviewing CALCITE-7744
> (https://github.com/apache/calcite/pull/5225); it is unrelated to that issue.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
