This is an automated email from the ASF dual-hosted git repository. rubenql pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push: new 44082a08a1 [CALCITE-5045] Alias within GroupingSets throws type mis-match exception [CALCITE-5145] CASE statement within GROUPING SETS throws type mis-match exception 44082a08a1 is described below commit 44082a08a11a7a62a73fda7bf4e5f423130aabc5 Author: hannerwang <hannerw...@tencent.com> AuthorDate: Fri Jun 24 11:38:44 2022 +0800 [CALCITE-5045] Alias within GroupingSets throws type mis-match exception [CALCITE-5145] CASE statement within GROUPING SETS throws type mis-match exception Co-authored-by: yingyuwang <yingyuw...@users.noreply.github.com> --- .../calcite/sql/validate/SqlValidatorImpl.java | 9 +++- .../apache/calcite/test/SqlToRelConverterTest.java | 48 +++++++++++++++++++++ .../apache/calcite/test/SqlToRelConverterTest.xml | 50 ++++++++++++++++++++++ 3 files changed, 106 insertions(+), 1 deletion(-) diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java index 19ef6aadf2..a5df51b875 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java @@ -460,7 +460,14 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { if (expanded != null) { inferUnknownTypes(targetType, scope, expanded); } - final RelDataType type = deriveType(selectScope, expanded); + RelDataType type = deriveType(selectScope, expanded); + // Re-derive SELECT ITEM's data type that may be nullable in AggregatingSelectScope when it + // appears in advanced grouping elements such as CUBE, ROLLUP , GROUPING SETS. + // For example, SELECT CASE WHEN c = 1 THEN '1' ELSE '23' END AS x FROM t GROUP BY CUBE(x), + // the 'x' should be nullable even if x's literal values are not null. + if (selectScope instanceof AggregatingSelectScope) { + type = requireNonNull(selectScope.nullifyType(stripAs(expanded), type)); + } setValidatedNodeType(expanded, type); fields.add(Pair.of(alias, type)); return false; diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java index d554a2a41e..a7e4c63643 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -4545,4 +4545,52 @@ class SqlToRelConverterTest extends SqlToRelTestBase { + "ROLLUP (deptno, job)"; sql(sql).ok(); } + + /** + * Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-5045">[CALCITE-5045] + * Alias within GroupingSets throws type mis-match exception</a>. + */ + @Test void testAliasWithinGroupingSets() { + final String sql = "SELECT empno / 2 AS x\n" + + "FROM emp\n" + + "GROUP BY ROLLUP(x)"; + sql(sql) + .withConformance(SqlConformanceEnum.LENIENT) + .ok(); + } + + /** + * Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-5145">[CALCITE-5145] + * CASE statement within GROUPING SETS throws type mis-match exception</a>. + */ + @Test public void testCaseAliasWithinGroupingSets() { + sql("SELECT empno,\n" + + "CASE\n" + + "WHEN ename in ('Fred','Eric') THEN 'CEO'\n" + + "ELSE 'Other'\n" + + "END AS derived_col\n" + + "FROM emp\n" + + "GROUP BY GROUPING SETS ((empno, derived_col),(empno))") + .withConformance(SqlConformanceEnum.LENIENT).ok(); + } + + /** + * Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-5145">[CALCITE-5145] + * CASE statement within GROUPING SETS throws type mis-match exception</a>. + */ + @Test void testCaseWithinGroupingSets() { + String sql = "SELECT empno,\n" + + "CASE WHEN ename IN ('Fred','Eric') THEN 'Manager' ELSE 'Other' END\n" + + "FROM emp\n" + + "GROUP BY GROUPING SETS (\n" + + "(empno, CASE WHEN ename IN ('Fred','Eric') THEN 'Manager' ELSE 'Other' END),\n" + + "(empno)\n" + + ")"; + sql(sql) + .withConformance(SqlConformanceEnum.LENIENT) + .ok(); + } } diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml index 85d87445e6..521ff3f251 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -315,6 +315,20 @@ from dept_nested_expanded as d, UNNEST(d.employees) as t(employee)]]> </Resource> </TestCase> + <TestCase name="testAliasWithinGroupingSets"> + <Resource name="sql"> + <![CDATA[SELECT empno / 2 AS x +FROM emp +GROUP BY ROLLUP(x)]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalAggregate(group=[{0}], groups=[[{0}, {}]]) + LogicalProject(X=[/($0, 2)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testAllValueList"> <Resource name="sql"> <![CDATA[select empno from emp where deptno > all (10, 20)]]> @@ -421,6 +435,42 @@ LogicalValues(tuples=[[{ 1 }]]) <![CDATA[values (case 'a' when 'a' then 1 end)]]> </Resource> </TestCase> + <TestCase name="testCaseAliasWithinGroupingSets"> + <Resource name="sql"> + <![CDATA[SELECT empno, +CASE +WHEN ename in ('Fred','Eric') THEN 'CEO' +ELSE 'Other' +END AS derived_col +FROM emp +GROUP BY GROUPING SETS ((empno, derived_col),(empno))]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]]) + LogicalProject(EMPNO=[$0], DERIVED_COL=[CASE(SEARCH($1, Sarg['Eric', 'Fred']:CHAR(4)), 'CEO ', 'Other')]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testCaseWithinGroupingSets"> + <Resource name="sql"> + <![CDATA[SELECT empno, +CASE WHEN ename IN ('Fred','Eric') THEN 'Manager' ELSE 'Other' END +FROM emp +GROUP BY GROUPING SETS ( +(empno, CASE WHEN ename IN ('Fred','Eric') THEN 'Manager' ELSE 'Other' END), +(empno) +)]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]]) + LogicalProject(EMPNO=[$0], EXPR$1=[CASE(SEARCH($1, Sarg['Eric', 'Fred']:CHAR(4)), 'Manager', 'Other ')]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testCharLength"> <Resource name="plan"> <![CDATA[