This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new c2527cc [CALCITE-4305] Implicit column alias for single-column
VALUES, and UNNEST of ARRAY and MULTISET constructors
c2527cc is described below
commit c2527ccf440f7750bfbabd2063c402440c5b32a0
Author: Julian Hyde <[email protected]>
AuthorDate: Fri Oct 23 23:48:32 2020 -0700
[CALCITE-4305] Implicit column alias for single-column VALUES, and UNNEST
of ARRAY and MULTISET constructors
---
.../calcite/sql/validate/AliasNamespace.java | 76 ++++---
.../calcite/sql/validate/SqlValidatorImpl.java | 8 +-
.../org/apache/calcite/test/SqlValidatorTest.java | 36 +++-
core/src/test/resources/sql/unnest.iq | 226 +++++++++++++++++++++
4 files changed, 318 insertions(+), 28 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/AliasNamespace.java
b/core/src/main/java/org/apache/calcite/sql/validate/AliasNamespace.java
index 38222d7..fc29736 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/AliasNamespace.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/AliasNamespace.java
@@ -25,9 +25,9 @@ import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Util;
-import java.util.ArrayList;
import java.util.List;
import static org.apache.calcite.util.Static.RESOURCE;
@@ -60,42 +60,66 @@ public class AliasNamespace extends AbstractNamespace {
super(validator, enclosingNode);
this.call = call;
assert call.getOperator() == SqlStdOperatorTable.AS;
+ assert call.operandCount() >= 2;
}
//~ Methods ----------------------------------------------------------------
+ @Override public boolean supportsModality(SqlModality modality) {
+ final List<SqlNode> operands = call.getOperandList();
+ final SqlValidatorNamespace childNs =
+ validator.getNamespace(operands.get(0));
+ return childNs.supportsModality(modality);
+ }
+
@Override protected RelDataType validateImpl(RelDataType targetRowType) {
- final List<String> nameList = new ArrayList<>();
final List<SqlNode> operands = call.getOperandList();
final SqlValidatorNamespace childNs =
validator.getNamespace(operands.get(0));
final RelDataType rowType = childNs.getRowTypeSansSystemColumns();
- final List<SqlNode> columnNames = Util.skip(operands, 2);
- for (final SqlNode operand : columnNames) {
- String name = ((SqlIdentifier) operand).getSimple();
- if (nameList.contains(name)) {
- throw validator.newValidationError(operand,
- RESOURCE.aliasListDuplicate(name));
+ final RelDataType aliasedType;
+ if (operands.size() == 2) {
+ // Alias is 'AS t' (no column list).
+ // If the sub-query is UNNEST or VALUES,
+ // and the sub-query has one column,
+ // then the namespace's sole column is named after the alias.
+ if (rowType.getFieldCount() == 1) {
+ aliasedType = validator.getTypeFactory().builder()
+ .kind(rowType.getStructKind())
+ .add(((SqlIdentifier) operands.get(1)).getSimple(),
+ rowType.getFieldList().get(0).getType())
+ .build();
+ } else {
+ aliasedType = rowType;
}
- nameList.add(name);
- }
- if (nameList.size() != rowType.getFieldCount()) {
- // Position error over all column names
- final SqlNode node = operands.size() == 3
- ? operands.get(2)
- : new SqlNodeList(columnNames, SqlParserPos.sum(columnNames));
- throw validator.newValidationError(node,
- RESOURCE.aliasListDegree(rowType.getFieldCount(), getString(rowType),
- nameList.size()));
- }
- final List<RelDataType> typeList = new ArrayList<>();
- for (RelDataTypeField field : rowType.getFieldList()) {
- typeList.add(field.getType());
+ } else {
+ // Alias is 'AS t (c0, ..., cN)'
+ final List<SqlNode> columnNames = Util.skip(operands, 2);
+ final List<String> nameList =
+ Util.transform(columnNames, operand ->
+ ((SqlIdentifier) operand).getSimple());
+ final int i = Util.firstDuplicate(nameList);
+ if (i >= 0) {
+ final SqlIdentifier id = (SqlIdentifier) columnNames.get(i);
+ throw validator.newValidationError(id,
+ RESOURCE.aliasListDuplicate(id.getSimple()));
+ }
+ if (columnNames.size() != rowType.getFieldCount()) {
+ // Position error over all column names
+ final SqlNode node = operands.size() == 3
+ ? operands.get(2)
+ : new SqlNodeList(columnNames, SqlParserPos.sum(columnNames));
+ throw validator.newValidationError(node,
+ RESOURCE.aliasListDegree(rowType.getFieldCount(),
+ getString(rowType), columnNames.size()));
+ }
+ aliasedType = validator.getTypeFactory().builder()
+ .addAll(
+ Util.transform(rowType.getFieldList(), f ->
+ Pair.of(nameList.get(f.getIndex()), f.getType())))
+ .kind(rowType.getStructKind())
+ .build();
}
- final RelDataType aliasedType =
validator.getTypeFactory().createStructType(
- rowType.getStructKind(),
- typeList,
- nameList);
// As per suggestion in CALCITE-4085, JavaType has its special nullability
handling.
if (rowType instanceof RelDataTypeFactoryImpl.JavaType) {
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 ba35baf..1c823d1 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
@@ -2186,8 +2186,14 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
if (alias == null) {
alias = call.operand(1).toString();
}
- final boolean needAlias = call.operandCount() > 2;
expr = call.operand(0);
+ final boolean needAlias = call.operandCount() > 2
+ || expr.getKind() == SqlKind.VALUES
+ || expr.getKind() == SqlKind.UNNEST
+ && (((SqlCall) expr).operand(0).getKind()
+ == SqlKind.ARRAY_VALUE_CONSTRUCTOR
+ || ((SqlCall) expr).operand(0).getKind()
+ == SqlKind.MULTISET_VALUE_CONSTRUCTOR);
newExpr =
registerFrom(
parentScope,
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 57b80a4..beb29f8 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -7989,6 +7989,38 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
.type("RecordType(INTEGER NOT NULL KEY, INTEGER NOT NULL VALUE) NOT
NULL");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-4305";>[CALCITE-4305]
+ * Implicit column alias for single-column UNNEST</a>. */
+ @Test void testUnnestAlias() {
+ final String expectedType = "RecordType(CHAR(6) NOT NULL FRUIT) NOT NULL";
+
+ // When UNNEST produces a single column, and you use an alias for the
+ // relation, that alias becomes the name of the column.
+ sql("select fruit.* from UNNEST(array ['apple', 'banana']) as fruit")
+ .type(expectedType);
+
+ // The magic doesn't happen if the query is not an UNNEST.
+ // In this case, the query is a SELECT.
+ sql("SELECT fruit.*\n"
+ + "FROM (\n"
+ + " SELECT * FROM UNNEST(array ['apple', 'banana']) as x) as fruit")
+ .type("RecordType(CHAR(6) NOT NULL X) NOT NULL");
+
+ // The magic doesn't happen if the UNNEST yields more than one column.
+ sql("select * from UNNEST(array [('apple', 1), ('banana', 2)]) as fruit")
+ .type("RecordType(CHAR(6) NOT NULL EXPR$0, INTEGER NOT NULL EXPR$1) "
+ + "NOT NULL");
+
+ // VALUES gets the same treatment as ARRAY. (Unlike PostgreSQL.)
+ sql("select * from (values ('apple'), ('banana')) as fruit")
+ .type("RecordType(CHAR(6) NOT NULL FRUIT) NOT NULL");
+
+ // UNNEST MULTISET gets the same treatment as UNNEST ARRAY.
+ sql("select * from unnest(multiset [1, 2, 1]) as f")
+ .type("RecordType(INTEGER NOT NULL F) NOT NULL");
+ }
+
@Test void testCorrelationJoin() {
sql("select *,"
+ " multiset(select * from emp where deptno=dept.deptno) "
@@ -10336,7 +10368,9 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
}
@Test void testStreamValues() {
- sql("select stream * from (^values 1^) as e")
+ sql("select stream * from (^values 1) as e^")
+ .fails(cannotConvertToStream("E"));
+ sql("select stream * from (^values 1) as e (c)^")
.fails(cannotConvertToStream("E"));
sql("select stream orderId from orders\n"
+ "union all\n"
diff --git a/core/src/test/resources/sql/unnest.iq
b/core/src/test/resources/sql/unnest.iq
new file mode 100644
index 0000000..77e1598
--- /dev/null
+++ b/core/src/test/resources/sql/unnest.iq
@@ -0,0 +1,226 @@
+# unnest.iq - Tests for UNNEST
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+!use scott
+!set outputformat mysql
+
+select *
+from unnest(array [(1, 'a'), (2, 'b')]) as t (x, y);
++---+---+
+| X | Y |
++---+---+
+| 1 | a |
+| 2 | b |
++---+---+
+(2 rows)
+
+!ok
+
+select *
+from UNNEST(array ['apple', 'banana']) as fruit (fruit);
++--------+
+| FRUIT |
++--------+
+| apple |
+| banana |
++--------+
+(2 rows)
+
+!ok
+
+# When UNNEST produces a single column, and you use an alias for the
+# relation, that alias becomes the name of the column.
+select *
+from UNNEST(array ['apple', 'banana']) as fruit;
++--------+
+| FRUIT |
++--------+
+| apple |
+| banana |
++--------+
+(2 rows)
+
+!ok
+
+select fruit.*
+from UNNEST(array ['apple', 'banana']) as fruit;
++--------+
+| FRUIT |
++--------+
+| apple |
+| banana |
++--------+
+(2 rows)
+
+!ok
+
+select fruit
+from UNNEST(array ['apple', 'banana']) as fruit;
++--------+
+| FRUIT |
++--------+
+| apple |
+| banana |
++--------+
+(2 rows)
+
+!ok
+
+select fruit.fruit
+from UNNEST(array ['apple', 'banana']) as fruit;
++--------+
+| FRUIT |
++--------+
+| apple |
+| banana |
++--------+
+(2 rows)
+
+!ok
+
+# As previous, but quoted
+select *
+from UNNEST(array ['apple', 'banana']) as "fruit";
++--------+
+| fruit |
++--------+
+| apple |
+| banana |
++--------+
+(2 rows)
+
+!ok
+
+# If UNNEST is not the direct child of the AS, aliasing doesn't happen.
+SELECT fruit.*
+FROM (
+ SELECT *
+ FROM UNNEST(array ['apple', 'banana']) as x) as fruit;
++--------+
+| X |
++--------+
+| apple |
+| banana |
++--------+
+(2 rows)
+
+!ok
+
+# If UNNEST applies to a value that is not an array constructor,
+# aliasing doesn't happen. Thus the last column is 'EXPR$0', not 'z'.
+SELECT *
+FROM (
+ SELECT x, collect(y) as ys
+ FROM (VALUES (1, 1), (2, 2), (1, 3)) AS t (x, y)
+ GROUP BY x) AS u,
+ UNNEST(u.ys) AS z;
++---+--------+--------+
+| X | YS | EXPR$0 |
++---+--------+--------+
+| 1 | [1, 3] | 1 |
+| 1 | [1, 3] | 3 |
+| 2 | [2] | 2 |
++---+--------+--------+
+(3 rows)
+
+!ok
+
+# If VALUES is not the direct child of the AS, aliasing doesn't happen.
+SELECT fruit.*
+FROM (
+ SELECT *
+ FROM (VALUES 'apple', 'banana') as x) as fruit;
++--------+
+| X |
++--------+
+| apple |
+| banana |
++--------+
+(2 rows)
+
+!ok
+
+# If UNNEST yields more than one column, the aliasing doesn't happen.
+select *
+from UNNEST(array [('apple', 1), ('banana', 2)]) as fruit;
++--------+--------+
+| EXPR$0 | EXPR$1 |
++--------+--------+
+| apple | 1 |
+| banana | 2 |
++--------+--------+
+(2 rows)
+
+!ok
+
+# MULTISET gets the same treatment as UNNEST.
+select *
+from unnest(multiset [1, 2, 1]) as v;
++---+
+| V |
++---+
+| 1 |
+| 1 |
+| 2 |
++---+
+(3 rows)
+
+!ok
+
+# VALUES gets the same treatment as UNNEST.
+#
+# This goes beyond PostgreSQL. In PostgreSQL, the following
+# query yields a column called "column1", not "fruit":
+# select * from (values (1), (2)) as fruit
+#
+select *
+from (values ('apple'), ('banana')) as fruit;
++--------+
+| FRUIT |
++--------+
+| apple |
+| banana |
++--------+
+(2 rows)
+
+!ok
+
+select upper(f)
+from (values 'apple', 'banana', 'pear') as f;
++--------+
+| EXPR$0 |
++--------+
+| APPLE |
+| BANANA |
+| PEAR |
++--------+
+(3 rows)
+
+!ok
+
+SELECT AVG(x) as "avg"
+FROM UNNEST(array [0, 2, 4, 4, 5]) as x;
++-----+
+| avg |
++-----+
+| 3 |
++-----+
+(1 row)
+
+!ok
+
+# End unnest.iq
