[jira] [Created] (FLINK-36662) Incorrect constant pull up when group keys in aggregate are NULL

["lincoln lee (Jira)" <ji...@apache.org>]

lincoln lee created FLINK-36662:
-----------------------------------

             Summary: 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: 2.0-preview, 1.20.0
            Reporter: lincoln lee
            Assignee: lincoln lee
             Fix For: 2.0.0


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.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)