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
commit e3b8b629f89ce5ffd25b4ef713258d83afd683cc Author: Julian Hyde <[email protected]> AuthorDate: Wed Dec 9 16:59:38 2020 -0800 [CALCITE-4433] Add UNPIVOT operator to SQL --- core/src/main/codegen/default_config.fmpp | 2 + core/src/main/codegen/templates/Parser.jj | 59 ++++ .../apache/calcite/runtime/CalciteResource.java | 12 + .../main/java/org/apache/calcite/sql/SqlKind.java | 3 + .../main/java/org/apache/calcite/sql/SqlPivot.java | 6 +- .../java/org/apache/calcite/sql/SqlUnpivot.java | 179 ++++++++++ .../apache/calcite/sql/validate/ScopeChild.java | 7 +- .../calcite/sql/validate/SqlValidatorImpl.java | 195 ++++++++++- .../calcite/sql/validate/UnpivotNamespace.java | 46 +++ .../apache/calcite/sql/validate/UnpivotScope.java | 49 +++ .../apache/calcite/sql2rel/SqlToRelConverter.java | 63 +++- .../java/org/apache/calcite/tools/RelBuilder.java | 170 ++++++++- .../main/java/org/apache/calcite/util/Util.java | 57 ++- .../calcite/runtime/CalciteResource.properties | 4 + .../apache/calcite/sql/parser/SqlParserTest.java | 19 + .../org/apache/calcite/test/RelBuilderTest.java | 46 +++ .../apache/calcite/test/SqlToRelConverterTest.java | 8 + .../org/apache/calcite/test/SqlValidatorTest.java | 94 +++++ .../java/org/apache/calcite/util/UtilTest.java | 12 +- .../apache/calcite/test/SqlToRelConverterTest.xml | 16 + core/src/test/resources/sql/pivot.iq | 386 +++++++++++++++++++++ site/_docs/algebra.md | 1 + site/_docs/reference.md | 18 + 23 files changed, 1414 insertions(+), 38 deletions(-) diff --git a/core/src/main/codegen/default_config.fmpp b/core/src/main/codegen/default_config.fmpp index ad3b724..00f89ed 100644 --- a/core/src/main/codegen/default_config.fmpp +++ b/core/src/main/codegen/default_config.fmpp @@ -133,6 +133,7 @@ parser: { "IMMEDIATE" "IMMEDIATELY" "IMPLEMENTATION" + "INCLUDE" "INCLUDING" "INCREMENT" "INITIALLY" @@ -327,6 +328,7 @@ parser: { "UNCOMMITTED" "UNCONDITIONAL" "UNDER" + "UNPIVOT" "UNNAMED" "USAGE" "USER_DEFINED_TYPE_CATALOG" diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index e6405e8..85f2921 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -98,6 +98,7 @@ import org.apache.calcite.sql.SqlTimeLiteral; import org.apache.calcite.sql.SqlTimestampLiteral; import org.apache.calcite.sql.SqlTypeNameSpec; import org.apache.calcite.sql.SqlUnnestOperator; +import org.apache.calcite.sql.SqlUnpivot; import org.apache.calcite.sql.SqlUpdate; import org.apache.calcite.sql.SqlUserDefinedTypeNameSpec; import org.apache.calcite.sql.SqlUtil; @@ -2177,9 +2178,14 @@ SqlNode TableRef2(boolean lateral) : tableRef = ExtendedTableRef() ) [ + LOOKAHEAD(2) tableRef = Pivot(tableRef) ] [ + LOOKAHEAD(2) + tableRef = Unpivot(tableRef) + ] + [ [ <AS> ] alias = SimpleIdentifier() [ columnAliasList = ParenthesizedSimpleIdentifierList() ] { @@ -2882,6 +2888,57 @@ void PivotValue(List<SqlNode> list) : ) } +/** Parses an UNPIVOT clause following a table expression. */ +SqlNode Unpivot(SqlNode tableRef) : +{ + final Span s; + final boolean includeNulls; + final SqlNodeList measureList; + final SqlNodeList axisList; + final Span s2; + final List<SqlNode> values = new ArrayList<SqlNode>(); + final SqlNodeList inList; +} +{ + <UNPIVOT> { s = span(); } + ( + <INCLUDE> <NULLS> { includeNulls = true; } + | <EXCLUDE> <NULLS> { includeNulls = false; } + | { includeNulls = false; } + ) + <LPAREN> + measureList = SimpleIdentifierOrList() + <FOR> axisList = SimpleIdentifierOrList() + <IN> + <LPAREN> { s2 = span(); } + UnpivotValue(values) ( <COMMA> UnpivotValue(values) )* + <RPAREN> + { inList = new SqlNodeList(values, s2.end(this)); } + <RPAREN> { + return new SqlUnpivot(s.end(this), tableRef, includeNulls, measureList, + axisList, inList); + } +} + +void UnpivotValue(List<SqlNode> list) : +{ + final SqlNodeList columnList; + final SqlNode values; +} +{ + columnList = SimpleIdentifierOrList() + ( + <AS> values = RowConstructor() { + final SqlNodeList valueList = SqlParserUtil.stripRow(values); + list.add( + SqlStdOperatorTable.AS.createCall(Span.of(columnList).end(this), + columnList, valueList)); + } + | + { list.add(columnList); } + ) +} + /** * Parses a MATCH_RECOGNIZE clause following a table expression. */ @@ -7395,6 +7452,7 @@ SqlPostfixOperator PostfixRowOperator() : | < IMPLEMENTATION: "IMPLEMENTATION" > | < IMPORT: "IMPORT" > | < IN: "IN" > +| < INCLUDE: "INCLUDE" > | < INCLUDING: "INCLUDING" > | < INCREMENT: "INCREMENT" > | < INDICATOR: "INDICATOR" > @@ -7785,6 +7843,7 @@ SqlPostfixOperator PostfixRowOperator() : | < UNION: "UNION" > | < UNIQUE: "UNIQUE" > | < UNKNOWN: "UNKNOWN" > +| < UNPIVOT: "UNPIVOT" > | < UNNAMED: "UNNAMED" > | < UNNEST: "UNNEST" > | < UPDATE: "UPDATE" > { beforeTableName(); } diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java index 8501ee2..895022e 100644 --- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java +++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java @@ -731,6 +731,18 @@ public interface CalciteResource { @BaseMessage("Value count in PIVOT ({0,number,#}) must match number of FOR columns ({1,number,#})") ExInst<SqlValidatorException> pivotValueArityMismatch(int valueCount, int forCount); + @BaseMessage("Duplicate column name ''{0}'' in UNPIVOT") + ExInst<SqlValidatorException> unpivotDuplicate(String columnName); + + @BaseMessage("Value count in UNPIVOT ({0,number,#}) must match number of FOR columns ({1,number,#})") + ExInst<SqlValidatorException> unpivotValueArityMismatch(int valueCount, int forCount); + + @BaseMessage("In UNPIVOT, cannot derive type for measure ''{0}'' because source columns have different data types") + ExInst<SqlValidatorException> unpivotCannotDeriveMeasureType(String measureName); + + @BaseMessage("In UNPIVOT, cannot derive type for axis ''{0}''") + ExInst<SqlValidatorException> unpivotCannotDeriveAxisType(String axisName); + @BaseMessage("Pattern variable ''{0}'' has already been defined") ExInst<SqlValidatorException> patternVarAlreadyDefined(String varName); diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java index d737fb4..aa18bfe 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -221,6 +221,9 @@ public enum SqlKind { /** PIVOT clause. */ PIVOT, + /** UNPIVOT clause. */ + UNPIVOT, + /** MATCH_RECOGNIZE clause. */ MATCH_RECOGNIZE, diff --git a/core/src/main/java/org/apache/calcite/sql/SqlPivot.java b/core/src/main/java/org/apache/calcite/sql/SqlPivot.java index 90aa8dc..72122b2 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlPivot.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlPivot.java @@ -102,7 +102,7 @@ public class SqlPivot extends SqlCall { writer.endList(frame); } - private static SqlNodeList stripList(SqlNodeList list) { + static SqlNodeList stripList(SqlNodeList list) { return list.stream().map(SqlPivot::strip) .collect(SqlNode.toList(list.pos)); } @@ -152,7 +152,7 @@ public class SqlPivot extends SqlCall { } } - private static String pivotAlias(SqlNode node) { + static String pivotAlias(SqlNode node) { if (node instanceof SqlNodeList) { return ((SqlNodeList) node).stream() .map(SqlPivot::pivotAlias).collect(Collectors.joining("_")); @@ -161,7 +161,7 @@ public class SqlPivot extends SqlCall { } /** Converts a SqlNodeList to a list, and other nodes to a singleton list. */ - private static SqlNodeList toNodes(SqlNode node) { + static SqlNodeList toNodes(SqlNode node) { if (node instanceof SqlNodeList) { return (SqlNodeList) node; } else { diff --git a/core/src/main/java/org/apache/calcite/sql/SqlUnpivot.java b/core/src/main/java/org/apache/calcite/sql/SqlUnpivot.java new file mode 100644 index 0000000..81ddeaf --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql/SqlUnpivot.java @@ -0,0 +1,179 @@ +/* + * 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. + */ +package org.apache.calcite.sql; + +import org.apache.calcite.sql.parser.SqlParserPos; +import org.apache.calcite.sql.util.SqlBasicVisitor; +import org.apache.calcite.sql.util.SqlVisitor; +import org.apache.calcite.util.ImmutableNullableList; +import org.apache.calcite.util.Util; + +import org.checkerframework.checker.nullness.qual.Nullable; + +import java.util.HashSet; +import java.util.List; +import java.util.Objects; +import java.util.Set; +import java.util.function.BiConsumer; +import java.util.function.Consumer; + +/** + * Parse tree node that represents UNPIVOT applied to a table reference + * (or sub-query). + * + * <p>Syntax: + * <blockquote><pre>{@code + * SELECT * + * FROM query + * UNPIVOT [ { INCLUDE | EXCLUDE } NULLS ] ( + * columns FOR columns IN ( columns [ AS values ], ...)) + * + * where: + * + * columns: column + * | '(' column, ... ')' + * values: value + * | '(' value, ... ')' + * }</pre></blockquote> + */ +public class SqlUnpivot extends SqlCall { + + public SqlNode query; + public final boolean includeNulls; + public final SqlNodeList measureList; + public final SqlNodeList axisList; + public final SqlNodeList inList; + + static final Operator OPERATOR = new Operator(SqlKind.UNPIVOT); + + //~ Constructors ----------------------------------------------------------- + + public SqlUnpivot(SqlParserPos pos, SqlNode query, boolean includeNulls, + SqlNodeList measureList, SqlNodeList axisList, SqlNodeList inList) { + super(pos); + this.query = Objects.requireNonNull(query); + this.includeNulls = includeNulls; + this.measureList = Objects.requireNonNull(measureList); + this.axisList = Objects.requireNonNull(axisList); + this.inList = Objects.requireNonNull(inList); + } + + //~ Methods ---------------------------------------------------------------- + + @Override public SqlOperator getOperator() { + return OPERATOR; + } + + @Override public List<SqlNode> getOperandList() { + return ImmutableNullableList.of(query, measureList, axisList, inList); + } + + @SuppressWarnings("nullness") + @Override public void setOperand(int i, @Nullable SqlNode operand) { + // Only 'query' is mutable. (It is required for validation.) + switch (i) { + case 0: + query = operand; + break; + default: + super.setOperand(i, operand); + } + } + + @Override public void unparse(SqlWriter writer, int leftPrec, int rightPrec) { + query.unparse(writer, leftPrec, 0); + writer.keyword("UNPIVOT"); + writer.keyword(includeNulls ? "INCLUDE NULLS" : "EXCLUDE NULLS"); + final SqlWriter.Frame frame = writer.startList("(", ")"); + // force parentheses if there is more than one foo + final int leftPrec1 = measureList.size() > 1 ? 1 : 0; + measureList.unparse(writer, leftPrec1, 0); + writer.sep("FOR"); + // force parentheses if there is more than one axis + final int leftPrec2 = axisList.size() > 1 ? 1 : 0; + axisList.unparse(writer, leftPrec2, 0); + writer.sep("IN"); + writer.list(SqlWriter.FrameTypeEnum.PARENTHESES, SqlWriter.COMMA, + SqlPivot.stripList(inList)); + writer.endList(frame); + } + + /** Returns the measure list as SqlIdentifiers. */ + @SuppressWarnings({"unchecked", "rawtypes"}) + public void forEachMeasure(Consumer<SqlIdentifier> consumer) { + ((List<SqlIdentifier>) (List) measureList).forEach(consumer); + } + + /** Returns contents of the IN clause {@code (nodeList, valueList)} pairs. + * {@code valueList} is null if the entry has no {@code AS} clause. */ + public void forEachNameValues( + BiConsumer<SqlNodeList, @Nullable SqlNodeList> consumer) { + for (SqlNode node : inList) { + switch (node.getKind()) { + case AS: + final SqlCall call = (SqlCall) node; + assert call.getOperandList().size() == 2; + final SqlNodeList nodeList = call.operand(0); + final SqlNodeList valueList = call.operand(1); + consumer.accept(nodeList, valueList); + break; + default: + final SqlNodeList nodeList2 = (SqlNodeList) node; + consumer.accept(nodeList2, null); + } + } + } + + /** Returns the set of columns that are referenced in the {@code FOR} + * clause. All columns that are not used will be part of the returned row. */ + public Set<String> usedColumnNames() { + final Set<String> columnNames = new HashSet<>(); + final SqlVisitor<Void> nameCollector = new SqlBasicVisitor<Void>() { + @Override public Void visit(SqlIdentifier id) { + columnNames.add(Util.last(id.names)); + return super.visit(id); + } + }; + forEachNameValues((aliasList, valueList) -> + aliasList.accept(nameCollector)); + return columnNames; + } + + /** Computes an alias. In the query fragment + * <blockquote> + * {@code UNPIVOT ... FOR ... IN ((c1, c2) AS 'c1_c2', (c3, c4))} + * </blockquote> + * note that {@code (c3, c4)} has no {@code AS}. The computed alias is + * 'C3_C4'. */ + public static String aliasValue(SqlNodeList aliasList) { + final StringBuilder b = new StringBuilder(); + aliasList.forEach(alias -> { + if (b.length() > 0) { + b.append('_'); + } + b.append(Util.last(((SqlIdentifier) alias).names)); + }); + return b.toString(); + } + + /** Unpivot operator. */ + static class Operator extends SqlSpecialOperator { + Operator(SqlKind kind) { + super(kind.name(), kind); + } + } +} diff --git a/core/src/main/java/org/apache/calcite/sql/validate/ScopeChild.java b/core/src/main/java/org/apache/calcite/sql/validate/ScopeChild.java index 9186daf..fc2401e 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/ScopeChild.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/ScopeChild.java @@ -30,7 +30,7 @@ class ScopeChild { /** Creates a ScopeChild. * * @param ordinal Ordinal of child within parent scope - * @param name Table alias (may be null) + * @param name Table alias * @param namespace Namespace of child * @param nullable Whether fields of the child are nullable when seen from the * parent, due to outer joins @@ -42,4 +42,9 @@ class ScopeChild { this.namespace = namespace; this.nullable = nullable; } + + @Override public String toString() { + return ordinal + ": " + name + ": " + namespace + + (nullable ? " (nullable)" : ""); + } } 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 256e7ec..eb9af34 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 @@ -17,7 +17,6 @@ package org.apache.calcite.sql.validate; import org.apache.calcite.linq4j.Ord; -import org.apache.calcite.linq4j.function.Function2; import org.apache.calcite.linq4j.function.Functions; import org.apache.calcite.plan.RelOptTable; import org.apache.calcite.plan.RelOptUtil; @@ -73,6 +72,7 @@ import org.apache.calcite.sql.SqlSelectKeyword; import org.apache.calcite.sql.SqlSnapshot; import org.apache.calcite.sql.SqlSyntax; import org.apache.calcite.sql.SqlTableFunction; +import org.apache.calcite.sql.SqlUnpivot; import org.apache.calcite.sql.SqlUnresolvedFunction; import org.apache.calcite.sql.SqlUpdate; import org.apache.calcite.sql.SqlUtil; @@ -139,6 +139,7 @@ import java.util.Locale; import java.util.Map; import java.util.Objects; import java.util.Set; +import java.util.function.BiFunction; import java.util.function.Supplier; import java.util.function.UnaryOperator; import java.util.stream.Collectors; @@ -2129,30 +2130,59 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { private void registerPivot( SqlValidatorScope parentScope, SqlValidatorScope usingScope, - SqlPivot call, + SqlPivot pivot, SqlNode enclosingNode, @Nullable String alias, boolean forceNullable) { final PivotNamespace namespace = - createPivotNameSpace(call, enclosingNode); + createPivotNameSpace(pivot, enclosingNode); registerNamespace(usingScope, alias, namespace, forceNullable); final SqlValidatorScope scope = - new PivotScope(parentScope, call); + new PivotScope(parentScope, pivot); + scopes.put(pivot, scope); + + // parse input query + SqlNode expr = pivot.query; + SqlNode newExpr = registerFrom(parentScope, scope, true, expr, + expr, null, null, forceNullable, false); + if (expr != newExpr) { + pivot.setOperand(0, newExpr); + } + } + + protected PivotNamespace createPivotNameSpace(SqlPivot call, + SqlNode enclosingNode) { + return new PivotNamespace(this, call, enclosingNode); + } + + private void registerUnpivot( + SqlValidatorScope parentScope, + SqlValidatorScope usingScope, + SqlUnpivot call, + SqlNode enclosingNode, + @Nullable String alias, + boolean forceNullable) { + final UnpivotNamespace namespace = + createUnpivotNameSpace(call, enclosingNode); + registerNamespace(usingScope, alias, namespace, forceNullable); + + final SqlValidatorScope scope = + new UnpivotScope(parentScope, call); scopes.put(call, scope); // parse input query SqlNode expr = call.query; - SqlNode newExpr = registerFrom(scope, scope, true, expr, + SqlNode newExpr = registerFrom(parentScope, scope, true, expr, expr, null, null, forceNullable, false); if (expr != newExpr) { call.setOperand(0, newExpr); } } - protected PivotNamespace createPivotNameSpace(SqlPivot call, + protected UnpivotNamespace createUnpivotNameSpace(SqlUnpivot call, SqlNode enclosingNode) { - return new PivotNamespace(this, call, enclosingNode); + return new UnpivotNamespace(this, call, enclosingNode); } /** @@ -2249,6 +2279,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { case OTHER_FUNCTION: case COLLECTION_TABLE: case PIVOT: + case UNPIVOT: case MATCH_RECOGNIZE: // give this anonymous construct a name since later @@ -2336,6 +2367,11 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { alias, forceNullable); return node; + case UNPIVOT: + registerUnpivot(parentScope, usingScope, (SqlUnpivot) node, enclosingNode, + alias, forceNullable); + return node; + case TABLESAMPLE: call = (SqlCall) node; expr = call.operand(0); @@ -5209,7 +5245,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { * The exception is determined when the function is applied. */ class ValidationErrorFunction - implements Function2<SqlNode, Resources.ExInst<SqlValidatorException>, + implements BiFunction<SqlNode, Resources.ExInst<SqlValidatorException>, CalciteContextException> { @Override public CalciteContextException apply( SqlNode v0, Resources.ExInst<SqlValidatorException> v1) { @@ -5573,6 +5609,14 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { mr.setOperand(SqlMatchRecognize.OPERAND_PATTERN_DEFINES, list); } + /** Returns the alias of a "expr AS alias" expression. */ + private static String alias(SqlNode item) { + assert item instanceof SqlCall; + assert item.getKind() == SqlKind.AS; + final SqlIdentifier identifier = ((SqlCall) item).operand(1); + return identifier.getSimple(); + } + public void validatePivot(SqlPivot pivot) { final PivotScope scope = (PivotScope) requireNonNull(getJoinScope(pivot), () -> "joinScope for " + pivot); @@ -5650,12 +5694,135 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { ns.setType(rowType); } - /** Returns the alias of a "expr AS alias" expression. */ - private static String alias(SqlNode item) { - assert item instanceof SqlCall; - assert item.getKind() == SqlKind.AS; - final SqlIdentifier identifier = ((SqlCall) item).operand(1); - return identifier.getSimple(); + public void validateUnpivot(SqlUnpivot unpivot) { + final UnpivotScope scope = + (UnpivotScope) requireNonNull(getJoinScope(unpivot), () -> + "scope for " + unpivot); + + final UnpivotNamespace ns = + getNamespaceOrThrow(unpivot).unwrap(UnpivotNamespace.class); + assert ns.rowType == null; + + // Given + // query UNPIVOT ((measure1, ..., measureM) + // FOR (axis1, ..., axisN) + // IN ((c11, ..., c1M) AS (value11, ..., value1N), + // (c21, ..., c2M) AS (value21, ..., value2N), ...) + // the type is + // k1, ... kN, axis1, ..., axisN, measure1, ..., measureM + // where k1, ... kN are columns that are not referenced as an argument to + // an aggregate or as an axis. + + // First, And make sure that each + final int measureCount = unpivot.measureList.size(); + final int axisCount = unpivot.axisList.size(); + unpivot.forEachNameValues((nodeList, valueList) -> { + // Make sure that each (ci1, ... ciM) list has the same arity as + // (measure1, ..., measureM). + if (nodeList.size() != measureCount) { + throw newValidationError(nodeList, + RESOURCE.unpivotValueArityMismatch(nodeList.size(), + measureCount)); + } + + // Make sure that each (vi1, ... viN) list has the same arity as + // (axis1, ..., axisN). + if (valueList != null && valueList.size() != axisCount) { + throw newValidationError(valueList, + RESOURCE.unpivotValueArityMismatch(valueList.size(), + axisCount)); + } + + // Make sure that each IN expression is a valid column from the input. + nodeList.forEach(node -> deriveType(scope, node)); + }); + + // What columns from the input are not referenced by a column in the IN + // list? + final SqlValidatorNamespace inputNs = + Objects.requireNonNull(getNamespace(unpivot.query)); + final Set<String> unusedColumnNames = + catalogReader.nameMatcher().createSet(); + unusedColumnNames.addAll(inputNs.getRowType().getFieldNames()); + unusedColumnNames.removeAll(unpivot.usedColumnNames()); + + // What columns will be present in the output row type? + final Set<String> columnNames = catalogReader.nameMatcher().createSet(); + columnNames.addAll(unusedColumnNames); + + // Gather the name and type of each measure. + final List<Pair<String, RelDataType>> measureNameTypes = new ArrayList<>(); + Ord.forEach(unpivot.measureList, (measure, i) -> { + final String measureName = ((SqlIdentifier) measure).getSimple(); + final List<RelDataType> types = new ArrayList<>(); + final List<SqlNode> nodes = new ArrayList<>(); + unpivot.forEachNameValues((nodeList, valueList) -> { + final SqlNode alias = nodeList.get(i); + nodes.add(alias); + types.add(deriveType(scope, alias)); + }); + final RelDataType type0 = typeFactory.leastRestrictive(types); + if (type0 == null) { + throw newValidationError(nodes.get(0), + RESOURCE.unpivotCannotDeriveMeasureType(measureName)); + } + final RelDataType type = + typeFactory.createTypeWithNullability(type0, + unpivot.includeNulls || unpivot.measureList.size() > 1); + setValidatedNodeType(measure, type); + if (!columnNames.add(measureName)) { + throw newValidationError(measure, + RESOURCE.unpivotDuplicate(measureName)); + } + measureNameTypes.add(Pair.of(measureName, type)); + }); + + // Gather the name and type of each axis. + // Consider + // FOR (job, deptno) + // IN (a AS ('CLERK', 10), + // b AS ('ANALYST', 20)) + // There are two axes, (job, deptno), and so each value list ('CLERK', 10), + // ('ANALYST', 20) must have arity two. + // + // The type of 'job' is derived as the least restrictive type of the values + // ('CLERK', 'ANALYST'), namely VARCHAR(7). The derived type of 'deptno' is + // the type of values (10, 20), namely INTEGER. + final List<Pair<String, RelDataType>> axisNameTypes = new ArrayList<>(); + Ord.forEach(unpivot.axisList, (axis, i) -> { + final String axisName = ((SqlIdentifier) axis).getSimple(); + final List<RelDataType> types = new ArrayList<>(); + unpivot.forEachNameValues((aliasList, valueList) -> + types.add( + valueList == null + ? typeFactory.createSqlType(SqlTypeName.VARCHAR, + SqlUnpivot.aliasValue(aliasList).length()) + : deriveType(scope, valueList.get(i)))); + final RelDataType type = typeFactory.leastRestrictive(types); + if (type == null) { + throw newValidationError(axis, + RESOURCE.unpivotCannotDeriveAxisType(axisName)); + } + setValidatedNodeType(axis, type); + if (!columnNames.add(axisName)) { + throw newValidationError(axis, RESOURCE.unpivotDuplicate(axisName)); + } + axisNameTypes.add(Pair.of(axisName, type)); + }); + + // Columns that have been seen as arguments to aggregates or as axes + // do not appear in the output. + final RelDataTypeFactory.Builder typeBuilder = typeFactory.builder(); + scope.getChild().getRowType().getFieldList().forEach(field -> { + if (unusedColumnNames.contains(field.getName())) { + typeBuilder.add(field); + } + }); + typeBuilder.addAll(axisNameTypes); + typeBuilder.addAll(measureNameTypes); + + final RelDataType rowType = typeBuilder.build(); + ns.setType(rowType); } /** Checks that all pattern variables within a function are the same, diff --git a/core/src/main/java/org/apache/calcite/sql/validate/UnpivotNamespace.java b/core/src/main/java/org/apache/calcite/sql/validate/UnpivotNamespace.java new file mode 100644 index 0000000..5c69961 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql/validate/UnpivotNamespace.java @@ -0,0 +1,46 @@ +/* + * 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. + */ +package org.apache.calcite.sql.validate; + +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.sql.SqlNode; +import org.apache.calcite.sql.SqlUnpivot; + +import static java.util.Objects.requireNonNull; + +/** + * Namespace for an {@code UNPIVOT} clause. + */ +public class UnpivotNamespace extends AbstractNamespace { + private final SqlUnpivot unpivot; + + /** Creates an UnpivotNamespace. */ + protected UnpivotNamespace(SqlValidatorImpl validator, SqlUnpivot unpivot, + SqlNode enclosingNode) { + super(validator, enclosingNode); + this.unpivot = unpivot; + } + + @Override public RelDataType validateImpl(RelDataType targetRowType) { + validator.validateUnpivot(unpivot); + return requireNonNull(rowType, "rowType"); + } + + @Override public SqlUnpivot getNode() { + return unpivot; + } +} diff --git a/core/src/main/java/org/apache/calcite/sql/validate/UnpivotScope.java b/core/src/main/java/org/apache/calcite/sql/validate/UnpivotScope.java new file mode 100644 index 0000000..f44f14b --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql/validate/UnpivotScope.java @@ -0,0 +1,49 @@ +/* + * 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. + */ +package org.apache.calcite.sql.validate; + +import org.apache.calcite.sql.SqlUnpivot; + +import static java.util.Objects.requireNonNull; + +/** + * Scope for expressions in an {@code UNPIVOT} clause. + */ +public class UnpivotScope extends ListScope { + + //~ Instance fields --------------------------------------------- + private final SqlUnpivot unpivot; + + /** Creates an UnpivotScope. */ + public UnpivotScope(SqlValidatorScope parent, SqlUnpivot unpivot) { + super(parent); + this.unpivot = unpivot; + } + + /** By analogy with + * {@link ListScope#getChildren()}, but this + * scope only has one namespace, and it is anonymous. */ + public SqlValidatorNamespace getChild() { + return requireNonNull( + validator.getNamespace(unpivot.query), + () -> "namespace for unpivot.query " + unpivot.query); + } + + @Override public SqlUnpivot getNode() { + return unpivot; + } +} diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java index f8f72e7..c099f45 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -127,6 +127,7 @@ import org.apache.calcite.sql.SqlSelectKeyword; import org.apache.calcite.sql.SqlSetOperator; import org.apache.calcite.sql.SqlSnapshot; import org.apache.calcite.sql.SqlUnnestOperator; +import org.apache.calcite.sql.SqlUnpivot; import org.apache.calcite.sql.SqlUpdate; import org.apache.calcite.sql.SqlUtil; import org.apache.calcite.sql.SqlValuesOperator; @@ -1769,11 +1770,12 @@ public class SqlToRelConverter { // don't use LogicalValues for those return null; } + return convertLiteral((SqlLiteral) sqlNode, bb, type); + } - RexNode literalExpr = - exprConverter.convertLiteral( - bb, - (SqlLiteral) sqlNode); + private RexLiteral convertLiteral(SqlLiteral sqlLiteral, + Blackboard bb, RelDataType type) { + RexNode literalExpr = exprConverter.convertLiteral(bb, sqlLiteral); if (!(literalExpr instanceof RexLiteral)) { assert literalExpr.isA(SqlKind.CAST); @@ -2142,13 +2144,11 @@ public class SqlToRelConverter { convertPivot(bb, (SqlPivot) from); return; + case UNPIVOT: + convertUnpivot(bb, (SqlUnpivot) from); + return; + case WITH_ITEM: -/* - final SqlWithItem withItem = (SqlWithItem) from; // TODO: revert? - final List<String> fieldNames2 = - ImmutableList.copyOf(SqlIdentifier.simpleNames(withItem.columnList)); - convertFrom(bb, withItem.query, fieldNames2); -*/ convertFrom(bb, ((SqlWithItem) from).query); return; @@ -2522,6 +2522,49 @@ public class SqlToRelConverter { bb.setRoot(rel, true); } + protected void convertUnpivot(Blackboard bb, SqlUnpivot unpivot) { + final SqlValidatorScope scope = validator().getJoinScope(unpivot); + + final Blackboard unpivotBb = createBlackboard(scope, null, false); + + // Convert input + convertFrom(unpivotBb, unpivot.query); + final RelNode input = unpivotBb.root(); + relBuilder.push(input); + + final List<String> measureNames = unpivot.measureList.stream() + .map(node -> ((SqlIdentifier) node).getSimple()) + .collect(Util.toImmutableList()); + final List<String> axisNames = unpivot.axisList.stream() + .map(node -> ((SqlIdentifier) node).getSimple()) + .collect(Util.toImmutableList()); + final ImmutableList.Builder<Pair<List<RexLiteral>, List<RexNode>>> axisMap = + ImmutableList.builder(); + unpivot.forEachNameValues((nodeList, valueList) -> { + if (valueList == null) { + valueList = new SqlNodeList( + Collections.nCopies(axisNames.size(), + SqlLiteral.createCharString(SqlUnpivot.aliasValue(nodeList), + SqlParserPos.ZERO)), + SqlParserPos.ZERO); + } + final List<RexLiteral> literals = new ArrayList<>(); + Pair.forEach(valueList, unpivot.axisList, (value, axis) -> { + final RelDataType type = validator().getValidatedNodeType(axis); + literals.add(convertLiteral((SqlLiteral) value, bb, type)); + }); + final List<RexNode> nodes = nodeList.stream() + .map(unpivotBb::convertExpression) + .collect(Util.toImmutableList()); + axisMap.add(Pair.of(literals, nodes)); + }); + relBuilder.unpivot(unpivot.includeNulls, measureNames, axisNames, + axisMap.build()); + relBuilder.convert(getNamespace(unpivot).getRowType(), false); + + bb.setRoot(relBuilder.build(), true); + } + private void convertIdentifier(Blackboard bb, SqlIdentifier id, @Nullable SqlNodeList extendedColumns, @Nullable SqlNodeList tableHints) { final SqlValidatorNamespace fromNamespace = getNamespace(id).resolve(); diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java index a09a809..50b6bdf 100644 --- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java +++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java @@ -119,6 +119,8 @@ import java.math.BigDecimal; import java.util.AbstractList; import java.util.ArrayDeque; import java.util.ArrayList; +import java.util.Arrays; +import java.util.BitSet; import java.util.Collections; import java.util.Deque; import java.util.HashMap; @@ -133,6 +135,7 @@ import java.util.TreeSet; import java.util.function.Function; import java.util.function.UnaryOperator; import java.util.stream.Collectors; +import java.util.stream.StreamSupport; import static org.apache.calcite.linq4j.Nullness.castNonNull; import static org.apache.calcite.sql.SqlKind.UNION; @@ -2544,24 +2547,32 @@ public class RelBuilder { } final ImmutableList<ImmutableList<RexLiteral>> tupleList = tupleList(fieldNames.length, values); + assert tupleList.size() == rowCount; + final List<String> fieldNameList = + Util.transformIndexed(Arrays.asList(fieldNames), (name, i) -> + name != null ? name : "expr$" + i); + return values(tupleList, fieldNameList); + } + + private RelBuilder values(List<? extends List<RexLiteral>> tupleList, + List<String> fieldNames) { final RelDataTypeFactory typeFactory = cluster.getTypeFactory(); final RelDataTypeFactory.Builder builder = typeFactory.builder(); - for (final Ord<@Nullable String> fieldName : Ord.zip(fieldNames)) { - final String name = - fieldName.e != null ? fieldName.e : "expr$" + fieldName.i; + Ord.forEach(fieldNames, (fieldName, i) -> { final RelDataType type = typeFactory.leastRestrictive( new AbstractList<RelDataType>() { @Override public RelDataType get(int index) { - return tupleList.get(index).get(fieldName.i).getType(); + return tupleList.get(index).get(i).getType(); } @Override public int size() { - return rowCount; + return tupleList.size(); } }); - assert type != null : "can't infer type for field " + fieldName.i + ", " + fieldName.e; - builder.add(name, type); - } + assert type != null + : "can't infer type for field " + i + ", " + fieldName; + builder.add(fieldName, type); + }); final RelDataType rowType = builder.build(); return values(tupleList, rowType); } @@ -3001,6 +3012,149 @@ public class RelBuilder { } /** + * Creates an Unpivot. + * + * <p>To achieve the same effect as the SQL + * + * <blockquote><pre>{@code + * SELECT * + * FROM (SELECT deptno, job, sal, comm FROM emp) + * UNPIVOT INCLUDE NULLS (remuneration + * FOR remuneration_type IN (comm AS 'commission', + * sal AS 'salary')) + * }</pre></blockquote> + * + * <p>use the builder as follows: + * + * <blockquote><pre>{@code + * RelBuilder b; + * b.scan("EMP"); + * final List<String> measureNames = Arrays.asList("REMUNERATION"); + * final List<String> axisNames = Arrays.asList("REMUNERATION_TYPE"); + * final Map<List<RexLiteral>, List<RexNode>> axisMap = + * ImmutableMap.<List<RexLiteral>, List<RexNode>>builder() + * .put(Arrays.asList(b.literal("commission")), + * Arrays.asList(b.field("COMM"))) + * .put(Arrays.asList(b.literal("salary")), + * Arrays.asList(b.field("SAL"))) + * .build(); + * b.unpivot(false, measureNames, axisNames, axisMap); + * }</pre></blockquote> + * + * <p>The query generates two columns: {@code remuneration_type} (an axis + * column) and {@code remuneration} (a measure column). Axis columns contain + * values to indicate the source of the row (in this case, {@code 'salary'} + * if the row came from the {@code sal} column, and {@code 'commission'} + * if the row came from the {@code comm} column). + * + * @param includeNulls Whether to include NULL values in the output + * @param measureNames Names of columns to be generated to hold pivoted + * measures + * @param axisNames Names of columns to be generated to hold qualifying values + * @param axisMap Mapping from the columns that hold measures to the values + * that the axis columns will hold in the generated rows + * @return This RelBuilder + */ + public RelBuilder unpivot(boolean includeNulls, + Iterable<String> measureNames, Iterable<String> axisNames, + Iterable<? extends Map.Entry<? extends List<? extends RexLiteral>, + ? extends List<? extends RexNode>>> axisMap) { + // Make immutable copies of all arguments. + final List<String> measureNameList = ImmutableList.copyOf(measureNames); + final List<String> axisNameList = ImmutableList.copyOf(axisNames); + final List<Pair<List<RexLiteral>, List<RexNode>>> map = + StreamSupport.stream(axisMap.spliterator(), false) + .map(pair -> + Pair.<List<RexLiteral>, List<RexNode>>of( + ImmutableList.<RexLiteral>copyOf(pair.getKey()), + ImmutableList.<RexNode>copyOf(pair.getValue()))) + .collect(Util.toImmutableList()); + + // Check that counts match. + Pair.forEach(map, (valueList, inputMeasureList) -> { + if (inputMeasureList.size() != measureNameList.size()) { + throw new IllegalArgumentException("Number of measures (" + + inputMeasureList.size() + ") must match number of measure names (" + + measureNameList.size() + ")"); + } + if (valueList.size() != axisNameList.size()) { + throw new IllegalArgumentException("Number of axis values (" + + valueList.size() + ") match match number of axis names (" + + axisNameList.size() + ")"); + } + }); + + final RelDataType leftRowType = peek().getRowType(); + final BitSet usedFields = new BitSet(); + Pair.forEach(map, (aliases, nodes) -> + nodes.forEach(node -> { + if (node instanceof RexInputRef) { + usedFields.set(((RexInputRef) node).getIndex()); + } + })); + + // Create "VALUES (('commission'), ('salary')) AS t (remuneration_type)" + values(ImmutableList.copyOf(Pair.left(map)), axisNameList); + + join(JoinRelType.INNER); + + final ImmutableBitSet unusedFields = + ImmutableBitSet.range(leftRowType.getFieldCount()) + .except(ImmutableBitSet.fromBitSet(usedFields)); + final List<RexNode> projects = new ArrayList<>(fields(unusedFields)); + Ord.forEach(axisNameList, (dimensionName, d) -> + projects.add( + alias(field(leftRowType.getFieldCount() + d), + dimensionName))); + + final List<RexNode> conditions = new ArrayList<>(); + Ord.forEach(measureNameList, (measureName, m) -> { + final List<RexNode> caseOperands = new ArrayList<>(); + Pair.forEach(map, (literals, nodes) -> { + Ord.forEach(literals, (literal, d) -> + conditions.add( + call(SqlStdOperatorTable.EQUALS, + field(leftRowType.getFieldCount() + d), literal))); + caseOperands.add(and(conditions)); + conditions.clear(); + caseOperands.add(nodes.get(m)); + }); + caseOperands.add(literal(null)); + projects.add( + alias(call(SqlStdOperatorTable.CASE, caseOperands), + measureName)); + }); + project(projects); + + if (!includeNulls) { + // Add 'WHERE m1 IS NOT NULL OR m2 IS NOT NULL' + final BitSet notNullFields = new BitSet(); + Ord.forEach(measureNameList, (measureName, m) -> { + final int f = unusedFields.cardinality() + axisNameList.size() + m; + conditions.add(isNotNull(field(f))); + notNullFields.set(f); + }); + filter(or(conditions)); + if (measureNameList.size() == 1) { + // If there is one field, EXCLUDE NULLS will have converted it to NOT + // NULL. + final RelDataTypeFactory.Builder builder = getTypeFactory().builder(); + peek().getRowType().getFieldList().forEach(field -> { + final RelDataType type = field.getType(); + builder.add(field.getName(), + notNullFields.get(field.getIndex()) + ? getTypeFactory().createTypeWithNullability(type, false) + : type); + }); + convert(builder.build(), false); + } + conditions.clear(); + } + + return this; + } + + /** * Attaches an array of hints to the stack top relational expression. * * <p>The redundant hints would be eliminated. diff --git a/core/src/main/java/org/apache/calcite/util/Util.java b/core/src/main/java/org/apache/calcite/util/Util.java index 8eb4cb2..be3a4b0 100644 --- a/core/src/main/java/org/apache/calcite/util/Util.java +++ b/core/src/main/java/org/apache/calcite/util/Util.java @@ -100,6 +100,7 @@ import java.util.RandomAccess; import java.util.Set; import java.util.StringTokenizer; import java.util.TimeZone; +import java.util.function.BiFunction; import java.util.function.Consumer; import java.util.function.Function; import java.util.function.ObjIntConsumer; @@ -2601,7 +2602,18 @@ public class Util { } } - /** Transforms a iterator, applying a function to each element. */ + /** Transforms a list, applying a function to each element, also passing in + * the element's index in the list. */ + public static <F, T> List<T> transformIndexed(List<? extends F> list, + BiFunction<? super F, Integer, ? extends T> function) { + if (list instanceof RandomAccess) { + return new RandomAccessTransformingIndexedList<>(list, function); + } else { + return new TransformingIndexedList<>(list, function); + } + } + + /** Transforms an iterable, applying a function to each element. */ @API(since = "1.27", status = API.Status.EXPERIMENTAL) public static <F, T> Iterable<T> transform(Iterable<? extends F> iterable, java.util.function.Function<? super F, ? extends T> function) { @@ -2778,6 +2790,49 @@ public class Util { } } + /** List that returns the same number of elements as a backing list, + * applying a transformation function to each one. + * + * @param <F> Element type of backing list + * @param <T> Element type of this list + */ + private static class TransformingIndexedList<F, T> extends AbstractList<T> { + private final BiFunction<? super F, Integer, ? extends T> function; + private final List<? extends F> list; + + TransformingIndexedList(List<? extends F> list, + BiFunction<? super F, Integer, ? extends T> function) { + this.function = function; + this.list = list; + } + + @Override public T get(int i) { + return function.apply(list.get(i), i); + } + + @Override public int size() { + return list.size(); + } + + @Override public Iterator<T> iterator() { + return listIterator(); + } + } + + /** Extension to {@link TransformingIndexedList} that implements + * {@link RandomAccess}. + * + * @param <F> Element type of backing list + * @param <T> Element type of this list + */ + private static class RandomAccessTransformingIndexedList<F, T> + extends TransformingIndexedList<F, T> implements RandomAccess { + RandomAccessTransformingIndexedList(List<? extends F> list, + BiFunction<? super F, Integer, ? extends T> function) { + super(list, function); + } + } + /** Iterator that applies a predicate to each element. * * @param <T> Element type */ diff --git a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties index e62b1555..7adbce3 100644 --- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties +++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties @@ -239,6 +239,10 @@ GroupFunctionMustAppearInGroupByClause=Group function ''{0}'' can only appear in AuxiliaryWithoutMatchingGroupCall=Call to auxiliary group function ''{0}'' must have matching call to group function ''{1}'' in GROUP BY clause PivotAggMalformed=Measure expression in PIVOT must use aggregate function PivotValueArityMismatch=Value count in PIVOT ({0,number,#}) must match number of FOR columns ({1,number,#}) +UnpivotDuplicate=Duplicate column name ''{0}'' in UNPIVOT +UnpivotValueArityMismatch=Value count in UNPIVOT ({0,number,#}) must match number of FOR columns ({1,number,#}) +UnpivotCannotDeriveMeasureType=In UNPIVOT, cannot derive type for measure ''{0}'' because source columns have different data types +UnpivotCannotDeriveAxisType=In UNPIVOT, cannot derive type for axis ''{0}'' PatternVarAlreadyDefined=Pattern variable ''{0}'' has already been defined PatternPrevFunctionInMeasure=Cannot use PREV/NEXT in MEASURE ''{0}'' PatternPrevFunctionOrder=Cannot nest PREV/NEXT under LAST/FIRST ''{0}'' diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java index ac8932c..4e89586 100644 --- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java +++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java @@ -8061,6 +8061,25 @@ public class SqlParserTest { sql(sql).ok(expected); } + @Test void testUnpivot() { + final String sql = "SELECT *\n" + + "FROM emp_pivoted\n" + + "UNPIVOT (\n" + + " (sum_sal, count_star)\n" + + " FOR (job, deptno)\n" + + " IN ((c10_ss, c10_c) AS ('CLERK', 10),\n" + + " (c20_ss, c20_c) AS ('CLERK', 20),\n" + + " (a20_ss, a20_c) AS ('ANALYST', 20)))"; + final String expected = "SELECT *\n" + + "FROM `EMP_PIVOTED` " + + "UNPIVOT EXCLUDE NULLS ((`SUM_SAL`, `COUNT_STAR`)" + + " FOR (`JOB`, `DEPTNO`)" + + " IN ((`C10_SS`, `C10_C`) AS ('CLERK', 10)," + + " (`C20_SS`, `C20_C`) AS ('CLERK', 20)," + + " (`A20_SS`, `A20_C`) AS ('ANALYST', 20)))"; + sql(sql).ok(expected); + } + @Test void testMatchRecognize1() { final String sql = "select *\n" + " from t match_recognize\n" diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java index faab1be..6bb8e96 100644 --- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java @@ -63,6 +63,7 @@ import org.apache.calcite.tools.RelRunner; import org.apache.calcite.tools.RelRunners; import org.apache.calcite.util.Holder; import org.apache.calcite.util.ImmutableBitSet; +import org.apache.calcite.util.Pair; import org.apache.calcite.util.TimestampString; import org.apache.calcite.util.Util; import org.apache.calcite.util.mapping.Mappings; @@ -3312,6 +3313,51 @@ public class RelBuilderTest { assertThat(f.apply(createBuilder()), hasTree(expected)); } + @Test void testUnpivot() { + // Equivalent SQL: + // SELECT * + // FROM (SELECT deptno, job, sal, comm FROM emp) + // UNPIVOT INCLUDE NULLS (remuneration + // FOR remuneration_type IN (comm AS 'commission', + // sal AS 'salary')) + // + // translates to + // SELECT e.deptno, e.job, + // CASE t.remuneration_type + // WHEN 'commission' THEN comm + // ELSE sal + // END AS remuneration + // FROM emp + // CROSS JOIN VALUES ('commission', 'salary') AS t (remuneration_type) + // + final BiFunction<RelBuilder, Boolean, RelNode> f = (b, includeNulls) -> + b.scan("EMP") + .unpivot(includeNulls, ImmutableList.of("REMUNERATION"), + ImmutableList.of("REMUNERATION_TYPE"), + Pair.zip( + Arrays.asList(ImmutableList.of(b.literal("commission")), + ImmutableList.of(b.literal("salary"))), + Arrays.asList(ImmutableList.of(b.field("COMM")), + ImmutableList.of(b.field("SAL"))))) + .build(); + final String expectedIncludeNulls = "" + + "LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], " + + "HIREDATE=[$4], DEPTNO=[$7], REMUNERATION_TYPE=[$8], " + + "REMUNERATION=[CASE(=($8, 'commission'), $6, =($8, 'salary'), $5, " + + "null:NULL)])\n" + + " LogicalJoin(condition=[true], joinType=[inner])\n" + + " LogicalTableScan(table=[[scott, EMP]])\n" + + " LogicalValues(tuples=[[{ 'commission' }, { 'salary' }]])\n"; + final String expectedExcludeNulls = "" + + "LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], " + + "HIREDATE=[$4], DEPTNO=[$5], REMUNERATION_TYPE=[$6], " + + "REMUNERATION=[CAST($7):DECIMAL(7, 2) NOT NULL])\n" + + " LogicalFilter(condition=[IS NOT NULL($7)])\n" + + " " + expectedIncludeNulls.replace("\n ", "\n "); + assertThat(f.apply(createBuilder(), true), hasTree(expectedIncludeNulls)); + assertThat(f.apply(createBuilder(), false), hasTree(expectedExcludeNulls)); + } + @Test void testMatchRecognize() { // Equivalent SQL: // SELECT * 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 97f2377..1f7f1a8 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -3492,6 +3492,14 @@ class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).ok(); } + @Test void testUnpivot() { + final String sql = "SELECT * FROM emp\n" + + "UNPIVOT INCLUDE NULLS (remuneration\n" + + " FOR remuneration_type IN (comm AS 'commission',\n" + + " sal as 'salary'))"; + sql(sql).ok(); + } + @Test void testMatchRecognize1() { final String sql = "select *\n" + " from emp match_recognize\n" 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 e3bdc83..43f8139 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -1898,6 +1898,100 @@ public class SqlValidatorTest extends SqlValidatorTestCase { + "FOR columns \\(2\\)"); } + @Test void testUnpivot() { + final String sql = "SELECT * FROM emp\n" + + "UNPIVOT (remuneration\n" + + " FOR remuneration_type IN (comm AS 'commission',\n" + + " sal as 'salary'))"; + sql(sql).type("RecordType(INTEGER NOT NULL EMPNO," + + " VARCHAR(20) NOT NULL ENAME, VARCHAR(10) NOT NULL JOB, INTEGER MGR," + + " TIMESTAMP(0) NOT NULL HIREDATE, INTEGER NOT NULL DEPTNO," + + " BOOLEAN NOT NULL SLACKER, CHAR(10) NOT NULL REMUNERATION_TYPE," + + " INTEGER NOT NULL REMUNERATION) NOT NULL"); + } + + @Test void testUnpivotInvalidColumn() { + final String sql = "SELECT * FROM emp\n" + + "UNPIVOT (remuneration\n" + + " FOR remuneration_type IN (comm AS 'commission',\n" + + " ^unknownCol^ as 'salary'))"; + sql(sql).fails("Column 'UNKNOWNCOL' not found in any table"); + } + + @Test void testUnpivotCannotDeriveMeasureType() { + final String sql = "SELECT * FROM emp\n" + + "UNPIVOT (remuneration\n" + + " FOR remuneration_type IN (^comm^ AS 'commission',\n" + + " ename as 'salary'))"; + sql(sql).fails("In UNPIVOT, cannot derive type for measure 'REMUNERATION'" + + " because source columns have different data types"); + } + + @Test void testUnpivotValueMismatch() { + final String sql = "SELECT * FROM emp\n" + + "UNPIVOT (remuneration\n" + + " FOR remuneration_type IN (comm AS 'commission',\n" + + " sal AS ^('salary', 1)^))"; + String expected = "Value count in UNPIVOT \\(2\\) must match " + + "number of FOR columns \\(1\\)"; + sql(sql).fails(expected); + } + + @Test void testUnpivotDuplicateName() { + final String sql = "SELECT * FROM emp\n" + + "UNPIVOT ((remuneration, ^remuneration^)\n" + + " FOR remuneration_type\n" + + " IN ((comm, comm) AS 'commission',\n" + + " (sal, sal) AS 'salary'))"; + sql(sql).fails("Duplicate column name 'REMUNERATION' in UNPIVOT"); + } + + @Test void testUnpivotDuplicateName2() { + final String sql = "SELECT * FROM emp\n" + + "UNPIVOT (remuneration\n" + + " FOR ^remuneration^ IN (comm AS 'commission',\n" + + " sal AS 'salary'))"; + sql(sql).fails("Duplicate column name 'REMUNERATION' in UNPIVOT"); + } + + @Test void testUnpivotDuplicateName3() { + final String sql = "SELECT * FROM emp\n" + + "UNPIVOT (remuneration\n" + + " FOR ^deptno^ IN (comm AS 'commission',\n" + + " sal AS 'salary'))"; + sql(sql).fails("Duplicate column name 'DEPTNO' in UNPIVOT"); + } + + @Test void testUnpivotMissingAs() { + final String sql = "SELECT *\n" + + "FROM (\n" + + " SELECT *\n" + + " FROM (VALUES (0, 1, 2, 3, 4),\n" + + " (10, 11, 12, 13, 14))\n" + + " AS t (c0, c1, c2, c3, c4))\n" + + "UNPIVOT ((m0, m1, m2)\n" + + " FOR (a0, a1)\n" + + " IN ((c1, c2, c3) AS ('col1','col2'),\n" + + " (c2, c3, c4)))"; + sql(sql).type("RecordType(INTEGER NOT NULL C0, VARCHAR(8) NOT NULL A0," + + " VARCHAR(8) NOT NULL A1, INTEGER M0, INTEGER M1," + + " INTEGER M2) NOT NULL"); + } + + @Test void testUnpivotMissingAs2() { + final String sql = "SELECT *\n" + + "FROM (\n" + + " SELECT *\n" + + " FROM (VALUES (0, 1, 2, 3, 4),\n" + + " (10, 11, 12, 13, 14))\n" + + " AS t (c0, c1, c2, c3, c4))\n" + + "UNPIVOT ((m0, m1, m2)\n" + + " FOR (^a0^, a1)\n" + + " IN ((c1, c2, c3) AS (6, true),\n" + + " (c2, c3, c4)))"; + sql(sql).fails("In UNPIVOT, cannot derive type for axis 'A0'"); + } + @Test void testMatchRecognizeWithDistinctAggregation() { final String sql = "SELECT *\n" + "FROM emp\n" diff --git a/core/src/test/java/org/apache/calcite/util/UtilTest.java b/core/src/test/java/org/apache/calcite/util/UtilTest.java index 7b1644a..c0453c3 100644 --- a/core/src/test/java/org/apache/calcite/util/UtilTest.java +++ b/core/src/test/java/org/apache/calcite/util/UtilTest.java @@ -95,6 +95,7 @@ import java.util.TimeZone; import java.util.TreeSet; import java.util.concurrent.atomic.AtomicInteger; import java.util.function.BiConsumer; +import java.util.function.BiFunction; import java.util.function.Function; import java.util.function.ObjIntConsumer; import java.util.function.Predicate; @@ -102,6 +103,7 @@ import java.util.function.UnaryOperator; import static org.apache.calcite.test.Matchers.isLinux; +import static org.hamcrest.CoreMatchers.allOf; import static org.hamcrest.CoreMatchers.equalTo; import static org.hamcrest.CoreMatchers.instanceOf; import static org.hamcrest.CoreMatchers.is; @@ -2761,7 +2763,8 @@ class UtilTest { assertThat(incTripleFn.apply(2), is(9)); } - /** Tests {@link Util#transform(List, java.util.function.Function)}. */ + /** Tests {@link Util#transform(List, java.util.function.Function)} + * and {@link Util#transformIndexed(List, BiFunction)}. */ @Test void testTransform() { final List<String> beatles = Arrays.asList("John", "Paul", "George", "Ringo"); @@ -2776,6 +2779,13 @@ class UtilTest { final List<String> beatles2 = new LinkedList<>(beatles); assertThat(Util.transform(beatles2, String::length), not(instanceOf(RandomAccess.class))); + + assertThat(Util.transformIndexed(beatles, (s, i) -> i + ": " + s), + allOf(is(Arrays.asList("0: John", "1: Paul", "2: George", "3: Ringo")), + instanceOf(RandomAccess.class))); + assertThat(Util.transformIndexed(beatles2, (s, i) -> i + ": " + s), + allOf(is(Arrays.asList("0: John", "1: Paul", "2: George", "3: Ringo")), + not(instanceOf(RandomAccess.class)))); } /** Tests {@link Util#filter(Iterable, java.util.function.Predicate)}. */ 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 135a8fe..7c754b0 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -6799,6 +6799,22 @@ LogicalProject(EXPR$0=[IS JSON VALUE($1)], EXPR$1=[IS JSON VALUE($1)], EXPR$2=[I ]]> </Resource> </TestCase> + <TestCase name="testUnpivot"> + <Resource name="sql"> + <![CDATA[SELECT * FROM emp +UNPIVOT INCLUDE NULLS (remuneration + FOR remuneration_type IN (comm AS 'commission', + sal as 'salary'))]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], DEPTNO=[$7], SLACKER=[$8], REMUNERATION_TYPE=[$9], REMUNERATION=[CASE(=($9, 'commission'), $6, =($9, 'salary '), $5, null:NULL)]) + LogicalJoin(condition=[true], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalValues(tuples=[[{ 'commission' }, { 'salary ' }]]) +]]> + </Resource> + </TestCase> <TestCase name="testWithinGroup1"> <Resource name="sql"> <. | `distinct()` | Creates an [Aggregate]({{ site.apiRoot }}/org/apache/calcite/rel/core/Aggregate.html) that eliminates duplicate records. | `pivot(groupKey, aggCalls, axes, values)` | Adds a pivot operation, implemented by generating an [Aggregate]({{ site.apiRoot }}/org/apache/calcite/rel/core/Aggregate.html) with a column for each combination of measures and values +| `unpivot(includeNulls, measureNames, axisNames, axisMap)` | Adds an unpivot operation, implemented by generating a [Join]({{ site.apiRoot }}/org/apache/calcite/rel/core/Join.html) to a [Values]({{ site.apiRoot }}/org/apache/calcite/rel/core/Values.html) that converts each row to several rows | `sort(fieldOrdinal...)`<br/>`sort(expr...)`<br/>`sort(exprList)` | Creates a [Sort]({{ site.apiRoot }}/org/apache/calcite/rel/core/Sort.html).<br/><br/>In the first form, field ordinals are 0-based, and a negative ordinal indicates descending; for example, -2 means field 1 descending.<br/><br/>In the other forms, you can wrap expressions in `as`, `nullsFirst` or `nullsLast`. | `sortLimit(offset, fetch, expr...)`<br/>`sortLimit(offset, fetch, exprList)` | Creates a [Sort]({{ site.apiRoot }}/org/apache/calcite/rel/core/Sort.html) with offset and limit. | `limit(offset, fetch)` | Creates a [Sort]({{ site.apiRoot }}/org/apache/calcite/rel/core/Sort.html) that does not sort, only applies with offset and limit. diff --git a/site/_docs/reference.md b/site/_docs/reference.md index fa9504f..bba8db9 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -229,6 +229,7 @@ tableReference: tablePrimary [ FOR SYSTEM_TIME AS OF expression ] [ pivot ] + [ unpivot ] [ matchRecognize ] [ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ] @@ -285,6 +286,23 @@ pivotList: pivotExpr: exprOrList [ [ AS ] alias ] +unpivot: + UNPIVOT [ INCLUDING NULLS | EXCLUDING NULLS ] '(' + unpivotMeasureList + FOR unpivotAxisList + IN '(' unpivotValue [, unpivotValue ]* ')' + ')' + +unpivotMeasureList: + columnOrList + +unpivotAxisList: + columnOrList + +unpivotValue: + column [ AS literal ] + | '(' column [, column ]* ')' [ AS '(' literal [, literal ]* ')' ] + values: VALUES expression [, expression ]*
