Vladimir Sitnikov created CALCITE-7822:
------------------------------------------
Summary: 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
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)
