[
https://issues.apache.org/jira/browse/CALCITE-7655?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
zzwqqq updated CALCITE-7655:
----------------------------
Summary: RelToSqlConverter generates invalid SQL when grouping by a window
function result (was: RelToSqlConverter generates invalid SQL when grouping by
a window function)
> RelToSqlConverter generates invalid SQL when grouping by a window function
> result
> ---------------------------------------------------------------------------------
>
> Key: CALCITE-7655
> URL: https://issues.apache.org/jira/browse/CALCITE-7655
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: zzwqqq
> Priority: Major
>
> 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)
