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 <[email protected]>
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 <[email protected]>
---
.../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">
<