zzwqqq created CALCITE-7655:
-------------------------------
Summary: RelToSqlConverter generates invalid SQL when grouping by
a window function
Key: CALCITE-7655
URL: https://issues.apache.org/jira/browse/CALCITE-7655
Project: Calcite
Issue Type: Bug
Components: core
Reporter: zzwqqq
RelToSqlConverter removes a subquery, leaving window function in GROUP BY.
Reproduction: add this test to RelToSqlConverterTest
{code:java}
@Test void testGroupByWindowFunction() {
final String query = "SELECT \"EMPNO\", \"row_number\", COUNT(*) AS \"c\"\n"
+ "FROM (\n"
+ " SELECT \"EMPNO\",\n"
+ " ROW_NUMBER() OVER (ORDER BY \"EMPNO\" NULLS FIRST) AS
\"row_number\"\n"
+ " FROM \"EMP\") AS \"t\"\n"
+ "GROUP BY \"EMPNO\", \"row_number\"";
final String expected = "SELECT `EMPNO`, `row_number`, COUNT(*) AS `c`\n"
+ "FROM (SELECT `EMPNO`, ROW_NUMBER() OVER (ORDER BY `EMPNO`) AS
`row_number`\n"
+ "FROM `SCOTT`.`EMP`) AS `t`\n"
+ "GROUP BY `EMPNO`, `row_number`";
sql(query)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
.withMysql().ok(expected);
} {code}
Actual sql:
{code:sql}
SELECT `EMPNO`,
ROW_NUMBER() OVER (ORDER BY `EMPNO`) AS `row_number`,
COUNT(*) AS `c`
FROM `SCOTT`.`EMP`
GROUP BY `EMPNO`, ROW_NUMBER() OVER (ORDER BY `EMPNO`)
{code}
I tested the generated SQL on MySQL, PostgreSQL, and Oracle. It fails on all
three databases because window functions are not allowed in a GROUP BY clause.
[https://onecompiler.com/mysql/44v6rpxxz]
[https://onecompiler.com/postgresql/44v6rqs8u]
[https://onecompiler.com/oracle/44v6rrjcf]
Expected sql:
{code:sql}
SELECT `EMPNO`, `row_number`, COUNT(*) AS `c`
FROM (
SELECT
`EMPNO`,
ROW_NUMBER() OVER (ORDER BY `EMPNO`) AS `row_number`
FROM `SCOTT`.`EMP`
) AS `t`
GROUP BY `EMPNO`, `row_number`
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
