[
https://issues.apache.org/jira/browse/FLINK-36662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
lincoln lee updated FLINK-36662:
--------------------------------
Description:
Currently, for the following case:
{code:java}
val groupingSetsQuery =
"""
|SELECT
| case
| when g1 = 1 then 'aaa'
| when g2 = 1 then 'bbb'
| end as gt,
| b, c,
| AVG(a) AS a
|FROM (select *, 1 g1, 1 g2 from MyTable) t
| GROUP BY GROUPING SETS ((g1, b), (g2, b, c))
""".stripMargin
util.verifyExecPlan(groupingSetsQuery)
{code}
we'll get a wrong plan:
{code:java}
Calc(select=[CAST('aaa' AS CHAR(3)) AS gt, b, c, a])
+- GroupAggregate(groupBy=[g1, b, g2, c, $e], select=[g1, b, g2, c, $e, AVG(a)
AS a])
+- Exchange(distribution=[hash[g1, b, g2, c, $e]])
+- Expand(projects=[{g1, b, null AS g2, null AS c, a, 3 AS $e}, {null AS
g1, b, g2, c, a, 8 AS $e}])
+- Calc(select=[1 AS g1, b, 1 AS g2, c, a])
+- LegacyTableSourceScan(table=[[default_catalog, default_database,
MyTable, source: [TestTableSource(a, b, c)]]], fields=[a, b, c])
{code}
the expected right plan:
{code:java}
Calc(select=[CASE((g1 = 1), 'aaa', (g2 = 1), 'bbb', null:CHAR(3)) AS gt, b, c,
a])
+- GroupAggregate(groupBy=[g1, b, g2, c, $e], select=[g1, b, g2, c, $e, AVG(a)
AS a])
+- Exchange(distribution=[hash[g1, b, g2, c, $e]])
+- Expand(projects=[\{g1, b, null AS g2, null AS c, a, 3 AS $e}, \{null AS g1,
b, g2, c, a, 8 AS $e}])
+- Calc(select=[1 AS g1, b, 1 AS g2, c, a])
+- LegacyTableSourceScan(table=[[default_catalog, default_database, MyTable,
source: [TestTableSource(a, b, c)]]], fields=[a, b, c])
{code}
This is the issue which CALCITE-6317 addressed. Before upgraded corresponding
calcite version(1.37), we can have the fix that copy the related
`RelMdPredicates` to flink and remove it after calcite version upgrading done.
was:
Currently, for the following case:
{code}
val groupingSetsQuery =
"""
|SELECT
| case
| when g1 = 1 then 'aaa'
| when g2 = 1 then 'bbb'
| end as gt,
| b, c,
| AVG(a) AS a
|FROM (select *, 1 g1, 1 g2 from MyTable) t
| GROUP BY GROUPING SETS ((g1, b), (g2, b, c))
""".stripMargin
util.verifyExecPlan(groupingSetsQuery)
{code}
we'll get a wrong plan:
{code}
Calc(select=[CAST('aaa' CHAR(3)) AS gt, b, c, a])
+- GroupAggregate(groupBy=[g1, b, g2, c, $e], select=[g1, b, g2, c, $e, AVG(a)
AS a])
+- Exchange(distribution=[hash[g1, b, g2, c, $e]])
+- Expand(projects=[\{g1, b, null AS g2, null AS c, a, 3 AS $e}, \{null AS g1,
b, g2, c, a, 8 AS $e}])
+- Calc(select=[1 AS g1, b, 1 AS g2, c, a])
+- LegacyTableSourceScan(table=[[default_catalog, default_database, MyTable,
source: [TestTableSource(a, b, c)]]], fields=[a, b, c])
{code}
the expected right plan:
{code}
Calc(select=[CASE((g1 = 1), 'aaa', (g2 = 1), 'bbb', null:CHAR(3)) AS gt, b, c,
a])
+- GroupAggregate(groupBy=[g1, b, g2, c, $e], select=[g1, b, g2, c, $e, AVG(a)
AS a])
+- Exchange(distribution=[hash[g1, b, g2, c, $e]])
+- Expand(projects=[\{g1, b, null AS g2, null AS c, a, 3 AS $e}, \{null AS g1,
b, g2, c, a, 8 AS $e}])
+- Calc(select=[1 AS g1, b, 1 AS g2, c, a])
+- LegacyTableSourceScan(table=[[default_catalog, default_database, MyTable,
source: [TestTableSource(a, b, c)]]], fields=[a, b, c])
{code}
This is the issue which CALCITE-6317 addressed. Before upgraded corresponding
calcite version(1.37), we can have the fix that copy the related
`RelMdPredicates` to flink and remove it after calcite version upgrading done.
> Incorrect constant pull up when group keys in aggregate are NULL
> ----------------------------------------------------------------
>
> Key: FLINK-36662
> URL: https://issues.apache.org/jira/browse/FLINK-36662
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Affects Versions: 1.20.0, 2.0-preview
> Reporter: lincoln lee
> Assignee: lincoln lee
> Priority: Major
> Fix For: 2.0.0
>
>
> Currently, for the following case:
> {code:java}
> val groupingSetsQuery =
> """
> |SELECT
> | case
> | when g1 = 1 then 'aaa'
> | when g2 = 1 then 'bbb'
> | end as gt,
> | b, c,
> | AVG(a) AS a
> |FROM (select *, 1 g1, 1 g2 from MyTable) t
> | GROUP BY GROUPING SETS ((g1, b), (g2, b, c))
> """.stripMargin
> util.verifyExecPlan(groupingSetsQuery)
> {code}
> we'll get a wrong plan:
> {code:java}
> Calc(select=[CAST('aaa' AS CHAR(3)) AS gt, b, c, a])
> +- GroupAggregate(groupBy=[g1, b, g2, c, $e], select=[g1, b, g2, c, $e,
> AVG(a) AS a])
> +- Exchange(distribution=[hash[g1, b, g2, c, $e]])
> +- Expand(projects=[{g1, b, null AS g2, null AS c, a, 3 AS $e}, {null
> AS g1, b, g2, c, a, 8 AS $e}])
> +- Calc(select=[1 AS g1, b, 1 AS g2, c, a])
> +- LegacyTableSourceScan(table=[[default_catalog,
> default_database, MyTable, source: [TestTableSource(a, b, c)]]], fields=[a,
> b, c])
> {code}
> the expected right plan:
> {code:java}
> Calc(select=[CASE((g1 = 1), 'aaa', (g2 = 1), 'bbb', null:CHAR(3)) AS gt, b,
> c, a])
> +- GroupAggregate(groupBy=[g1, b, g2, c, $e], select=[g1, b, g2, c, $e,
> AVG(a) AS a])
> +- Exchange(distribution=[hash[g1, b, g2, c, $e]])
> +- Expand(projects=[\{g1, b, null AS g2, null AS c, a, 3 AS $e}, \{null AS
> g1, b, g2, c, a, 8 AS $e}])
> +- Calc(select=[1 AS g1, b, 1 AS g2, c, a])
> +- LegacyTableSourceScan(table=[[default_catalog, default_database, MyTable,
> source: [TestTableSource(a, b, c)]]], fields=[a, b, c])
> {code}
> This is the issue which CALCITE-6317 addressed. Before upgraded corresponding
> calcite version(1.37), we can have the fix that copy the related
> `RelMdPredicates` to flink and remove it after calcite version upgrading done.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
