You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by st...@apache.org on 2026/05/15 09:02:17 UTC
(impala) 02/02: IMPALA-2083: Add PIVOT clause
This is an automated email from the ASF dual-hosted git repository.
asf-gitbox-commits pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/impala.git
commit 56be75218a7dd11a45ad231208a3972cb4b479ac
Author: Xuebin Su <xs...@cloudera.com>
AuthorDate: Wed Apr 1 11:32:48 2026 +0800
IMPALA-2083: Add PIVOT clause
This patch adds support for the PIVOT clause by adding a new subclass
of `TableRef` called `PivotTableRef`. The concepts and the examples are
explained in `PivotTableRef.java`.
The changes are mostly in the frontend:
- A new keyword `PIVOT` is added.
- In the parsing phase, a PIVOT clause will be parsed into a
`PivotTableRef`.
- Then, in the analysis phase, the `PivotTableRef` will be rewritten to
an `InlineViewRef` to a nested subquery, in which
- First, the source table will be aggregated with the aggregate
expressions specified in the PIVOT clause;
- Then, each aggregated column will be split into multiple columns
with the `aggif()` function, one for each value in the PIVOT clause.
No new plan node or new execution node is added.
Since it is now possible that the predicate in the `aggif()` function
references a nullable slot, this patch removes the restriction that the
predicate must not be NULL.
Limitations:
- Multiple header columns in the PIVOT clause is not supported.
- Specifying a nested field as the header column is not supported.
- Specifying constant expressions other than literals in the header
value list is not supported.
- Using the PIVOT clause in statements other than SELECT is not
supported.
Testing:
- Added E2E tests in test_pivot_clause.py
- Added FE tests to ensure that predicates can be pushed down to the
SCAN nodes.
Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Reviewed-on: http://gerrit.cloudera.org:8080/24105
Reviewed-by: Quanlong Huang <hu...@gmail.com>
Tested-by: Impala Public Jenkins <im...@cloudera.com>
---
be/src/exprs/aggregate-functions-ir.cc | 10 +-
be/src/exprs/aggregate-functions.h | 2 +-
fe/src/main/cup/sql-parser.cup | 15 +-
.../apache/impala/analysis/AnalysisContext.java | 8 +
.../java/org/apache/impala/analysis/Analyzer.java | 5 +-
.../org/apache/impala/analysis/FromClause.java | 5 +-
.../org/apache/impala/analysis/PivotTableRef.java | 347 ++++++++++++++++
.../org/apache/impala/analysis/StmtRewriter.java | 21 +
.../java/org/apache/impala/analysis/TableRef.java | 6 +-
.../java/org/apache/impala/catalog/BuiltinsDb.java | 2 +-
fe/src/main/jflex/sql-scanner.flex | 1 +
.../apache/impala/analysis/AnalyzeStmtsTest.java | 67 ++++
.../org/apache/impala/analysis/ParserTest.java | 14 +-
.../org/apache/impala/planner/PlannerTest.java | 7 +
.../queries/PlannerTest/pivot-clause.test | 230 +++++++++++
.../queries/QueryTest/pivot-clause.test | 434 +++++++++++++++++++++
.../ranger_column_masking_and_row_filtering.test | 9 +
tests/query_test/test_pivot_clause.py | 36 ++
18 files changed, 1194 insertions(+), 25 deletions(-)
diff --git a/be/src/exprs/aggregate-functions-ir.cc b/be/src/exprs/aggregate-functions-ir.cc
index c9e4b0439..48e703306 100644
--- a/be/src/exprs/aggregate-functions-ir.cc
+++ b/be/src/exprs/aggregate-functions-ir.cc
@@ -3284,26 +3284,24 @@ BigIntVal AggregateFunctions::SampledNdvFinalize(FunctionContext* ctx,
template <typename T>
void AggregateFunctions::AggIfUpdate(
FunctionContext* ctx, const BooleanVal& cond, const T& src, T* dst) {
- DCHECK(!cond.is_null);
- if (cond.val) *dst = src;
+ if (!cond.is_null && cond.val) *dst = src;
}
template <>
void AggregateFunctions::AggIfUpdate(
FunctionContext* ctx, const BooleanVal& cond, const StringVal& src, StringVal* dst) {
- DCHECK(!cond.is_null);
- if (cond.val) CopyStringVal(ctx, src, dst);
+ if (!cond.is_null && cond.val) CopyStringVal(ctx, src, dst);
}
template <typename T>
void AggregateFunctions::AggIfMerge(FunctionContext*, const T& src, T* dst) {
- *dst = src;
+ if (!src.is_null) *dst = src;
}
template <>
void AggregateFunctions::AggIfMerge(
FunctionContext* ctx, const StringVal& src, StringVal* dst) {
- CopyStringVal(ctx, src, dst);
+ if (!src.is_null) CopyStringVal(ctx, src, dst);
}
template <typename T>
diff --git a/be/src/exprs/aggregate-functions.h b/be/src/exprs/aggregate-functions.h
index 13aadb8e9..f3c99e25e 100644
--- a/be/src/exprs/aggregate-functions.h
+++ b/be/src/exprs/aggregate-functions.h
@@ -396,7 +396,7 @@ class AggregateFunctions {
/// The AGGIF(predicate, expr) function returns 'expr' if 'predicate' is true.
/// It is expected that 'predicate' only returns true for a single row per group.
- /// The predicate must not evaluate to NULL.
+ /// It skips the current row if 'predicate' is null.
template <typename T>
static void AggIfUpdate(FunctionContext*, const BooleanVal& cond, const T& src, T* dst);
template <typename T>
diff --git a/fe/src/main/cup/sql-parser.cup b/fe/src/main/cup/sql-parser.cup
index 21d64cc0d..1fbf1fb7a 100755
--- a/fe/src/main/cup/sql-parser.cup
+++ b/fe/src/main/cup/sql-parser.cup
@@ -340,9 +340,9 @@ terminal
KW_MATCHED, KW_MERGE, KW_MERGE_FN, KW_METADATA, KW_MINUS, KW_NON, KW_NORELY, KW_NOT,
KW_NOVALIDATE, KW_NULL, KW_NULLS, KW_OF, KW_OFFSET, KW_ON, KW_OPTIMIZE, KW_OR,
KW_ORC, KW_ORDER, KW_OUTER,
- KW_OVER, KW_OVERWRITE, KW_PARQUET, KW_PARQUETFILE, KW_PARTITION, KW_PARTITIONED,
- KW_PARTITIONS, KW_PRECEDING, KW_PREPARE_FN, KW_PAIMON, KW_PRIMARY, KW_PRODUCED, KW_PURGE, KW_QUERY,
- KW_RANGE, KW_RCFILE, KW_RECOVER, KW_REFERENCES, KW_REFRESH, KW_REGEXP, KW_RELY,
+ KW_OVER, KW_OVERWRITE, KW_PARQUET, KW_PARQUETFILE, KW_PARTITION, KW_PARTITIONED, KW_PARTITIONS,
+ KW_PIVOT, KW_PRECEDING, KW_PREPARE_FN, KW_PAIMON, KW_PRIMARY, KW_PRODUCED, KW_PURGE,
+ KW_QUERY, KW_RANGE, KW_RCFILE, KW_RECOVER, KW_REFERENCES, KW_REFRESH, KW_REGEXP, KW_RELY,
KW_RENAME, KW_REPEATABLE, KW_REPLACE, KW_REPLICATION, KW_RESTRICT, KW_RETURNS,
KW_REVOKE, KW_RIGHT, KW_RLIKE, KW_ROLE, KW_ROLES, KW_ROLLUP, KW_ROW, KW_ROWS, KW_RWSTORAGE,
KW_SCHEMA, KW_SCHEMAS, KW_SELECT, KW_SELECTIVITY, KW_SEMI, KW_SEQUENCEFILE, KW_SERDEPROPERTIES,
@@ -3559,6 +3559,13 @@ table_ref ::=
RESULT = new UnpivotTableRef(
path, alias, data_column, header_column, unpivot_column_list);
:}
+ | dotted_path:path KW_PIVOT LPAREN select_list:agg_list KW_FOR
+ ident_or_unreserved:header_column KW_IN
+ LPAREN select_list:header_value_list RPAREN RPAREN alias_clause:alias
+ {:
+ RESULT = new PivotTableRef(
+ path, alias, agg_list, header_column, header_value_list);
+ :}
;
unnest_table_ref ::=
@@ -4803,6 +4810,8 @@ word ::=
{: RESULT = r.toString(); :}
| KW_PARTITIONS:r
{: RESULT = r.toString(); :}
+ | KW_PIVOT:r
+ {: RESULT = r.toString(); :}
| KW_PRECEDING:r
{: RESULT = r.toString(); :}
| KW_PREPARE_FN:r
diff --git a/fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java b/fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
index 12c84f1cb..32f8bf566 100644
--- a/fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
+++ b/fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
@@ -626,6 +626,14 @@ public class AnalysisContext {
new StmtRewriter.ZippingUnnestRewriter().rewrite(stmt_);
shouldReAnalyze = true;
}
+ if (stmt_ instanceof SelectStmt) {
+ StmtRewriter.PivotClauseRewriter pivotClauseRewriter =
+ new StmtRewriter.PivotClauseRewriter();
+ pivotClauseRewriter.rewrite(stmt_);
+ if (pivotClauseRewriter.changed()) {
+ shouldReAnalyze = true;
+ }
+ }
if (!shouldReAnalyze) {
return new AnalysisResult(parsedStmt_, analyzer_);
}
diff --git a/fe/src/main/java/org/apache/impala/analysis/Analyzer.java b/fe/src/main/java/org/apache/impala/analysis/Analyzer.java
index 5198d3aa2..905554586 100644
--- a/fe/src/main/java/org/apache/impala/analysis/Analyzer.java
+++ b/fe/src/main/java/org/apache/impala/analysis/Analyzer.java
@@ -44,14 +44,12 @@ import javax.annotation.Nullable;
import org.apache.commons.lang3.StringUtils;
import org.apache.impala.analysis.Path.PathType;
import org.apache.impala.analysis.StmtMetadataLoader.StmtTableCache;
-import org.apache.impala.authorization.AuthorizationChecker;
import org.apache.impala.authorization.AuthorizationConfig;
import org.apache.impala.authorization.AuthorizationContext;
import org.apache.impala.authorization.AuthorizationFactory;
import org.apache.impala.authorization.Privilege;
import org.apache.impala.authorization.PrivilegeRequest;
import org.apache.impala.authorization.PrivilegeRequestBuilder;
-import org.apache.impala.authorization.TableMask;
import org.apache.impala.authorization.User;
import org.apache.impala.catalog.Column;
import org.apache.impala.catalog.DatabaseNotFoundException;
@@ -68,7 +66,6 @@ import org.apache.impala.catalog.FeTable;
import org.apache.impala.catalog.FeView;
import org.apache.impala.catalog.IcebergTimeTravelTable;
import org.apache.impala.catalog.KuduTable;
-import org.apache.impala.catalog.MaterializedViewHdfsTable;
import org.apache.impala.catalog.ScalarType;
import org.apache.impala.catalog.StructField;
import org.apache.impala.catalog.StructType;
@@ -1741,7 +1738,7 @@ public class Analyzer {
result = createAndRegisterSlotDesc(slotPath);
}
TableRef tableRef = tableRefMap_.get(result.getParent().getId());
- if (tableRef instanceof UnpivotTableRef) {
+ if (tableRef instanceof UnpivotTableRef || tableRef instanceof PivotTableRef) {
Preconditions.checkState(tupleStack_.isEmpty());
tableRef.notifySlotRefRegistered(this, result);
}
diff --git a/fe/src/main/java/org/apache/impala/analysis/FromClause.java b/fe/src/main/java/org/apache/impala/analysis/FromClause.java
index 426ba0d84..341385d4e 100644
--- a/fe/src/main/java/org/apache/impala/analysis/FromClause.java
+++ b/fe/src/main/java/org/apache/impala/analysis/FromClause.java
@@ -193,8 +193,9 @@ public class FromClause extends StmtNode implements Iterable<TableRef> {
public void reset() {
for (int i = 0; i < size(); ++i) {
TableRef origTblRef = get(i);
- if (origTblRef instanceof UnpivotTableRef) {
- // UnpivotTableRef.clone() creates an unanalyzed clone and can be used for reset.
+ if (origTblRef instanceof UnpivotTableRef || origTblRef instanceof PivotTableRef) {
+ // For UnpivotTableRef and PivotTableRef, clone() creates an unanalyzed clone and
+ // can be used for reset.
set(i, origTblRef.clone());
continue;
}
diff --git a/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java b/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
new file mode 100644
index 000000000..f31dc4d68
--- /dev/null
+++ b/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
@@ -0,0 +1,347 @@
+// 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.impala.analysis;
+
+import java.util.ArrayList;
+import java.util.HashSet;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.UUID;
+
+import org.apache.impala.analysis.BinaryPredicate.Operator;
+import org.apache.impala.analysis.GroupByClause.GroupingSetsType;
+import org.apache.impala.catalog.Column;
+import org.apache.impala.catalog.StructField;
+import org.apache.impala.catalog.StructType;
+import org.apache.impala.common.AnalysisException;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+
+/**
+ * A PivotTableRef is created when there is a PIVOT clause in the FROM clause of a query.
+ * A PIVOT clause following a TableRef can be used to split an aggregated column into
+ * multiple columns.
+ *
+ * As an example, for the following query:
+ *
+ * SELECT * FROM s PIVOT ( count(a) for b in (1 AS v1, 2 AS v2) ) AS t;
+ *
+ * The PIVOT clause splits the aggregated column count(a) into two columns:
+ * - column v1 contains the aggregation results when b = 1, and
+ * - column v2 contains the aggregation results when b = 2.
+ *
+ * The result table t contains
+ * - columns v1 and v2 created from the split, and
+ * - all the columns in the source table s except column b.
+ *
+ * Column b in this example is called the "header column" since its values are used for
+ * the header of the result table to identify the columns created from the split.
+ *
+ * In the analysis phase, a `PivotTableRef` will be rewritten to an `InlineViewRef` to a
+ * nested subquery with the `toInlineViewRef()` method.
+ */
+public class PivotTableRef extends TableRef {
+ private final SelectList aggList_; // read-only
+ private final String headerColumn_; // read-only
+ private final SelectList headerValueList_; // read-only
+
+ private final boolean resolvedSourceTableRef_;
+ private TableRef sourceTableRef_ = null;
+
+ // Use LinkedHashMaps here to keep the order of the exprs stable since it determines
+ // the order of slots in the output tuple of the aggregation.
+
+ // Maps each alias to the Agg Expr in the PIVOT clause.
+ private final Map<String, FunctionCallExpr> analyzedAggList_ = new LinkedHashMap<>();
+
+ // Maps each alias to the Literal Expr in the PIVOT clause.
+ private final Map<String, LiteralExpr> headerValueMap_ = new LinkedHashMap<>();
+
+ // Maps each column name to the SlotDescriptor in the TupleDescriptor of the source
+ // table of the PIVOT clause.
+ private final Map<String, SlotDescriptor> groupingSlotMap_ = new LinkedHashMap<>();
+
+ PivotTableRef(List<String> path, String alias,
+ SelectList aggList, String headerColumn, SelectList headerValueList) {
+ super(path, alias);
+ resolvedSourceTableRef_ = false;
+ aggList_ = aggList;
+ headerColumn_ = headerColumn;
+ headerValueList_ = headerValueList;
+ }
+
+ PivotTableRef(TableRef sourceTableRef, String alias,
+ SelectList aggList, String headerColumn, SelectList headerValueList) {
+ super(sourceTableRef.getPath(), alias);
+ resolvedSourceTableRef_ = true;
+ sourceTableRef_ = sourceTableRef;
+ aggList_ = aggList;
+ headerColumn_ = headerColumn;
+ headerValueList_ = headerValueList;
+ }
+
+ // Creates an unanalyzed clone.
+ // We cannot create a full clone since a SlotDescriptor cannot be cloned without an
+ // Analyzer.
+ @Override
+ protected PivotTableRef clone() {
+ if (!resolvedSourceTableRef_) {
+ return new PivotTableRef(
+ new ArrayList<>(rawPath_),
+ getUniqueAlias(),
+ aggList_.clone(),
+ headerColumn_,
+ headerValueList_.clone()
+ );
+ }
+ TableRef sourceTableRefClone = sourceTableRef_.clone();
+ sourceTableRefClone.reset();
+ return new PivotTableRef(
+ sourceTableRefClone,
+ getUniqueAlias(),
+ aggList_.clone(),
+ headerColumn_,
+ headerValueList_.clone()
+ );
+ }
+
+ @Override
+ public TupleDescriptor createTupleDescriptor(Analyzer analyzer)
+ throws AnalysisException {
+ Preconditions.checkNotNull(sourceTableRef_);
+
+ TupleDescriptor result = analyzer.getDescTbl().createTupleDescriptor(
+ getClass().getSimpleName() + " " + getUniqueAlias());
+ result.setIsMaterialized(false);
+ result.setHidden(true);
+ List<StructField> fields = new ArrayList<>();
+ Set<String> uniqueColAliases = Sets.newHashSetWithExpectedSize(
+ sourceTableRef_.getDesc().getType().getFields().size() +
+ (analyzedAggList_.size() * headerValueMap_.size()));
+ for (StructField field : sourceTableRef_.getDesc().getType().getFields()) {
+ if (field.getName().equals(headerColumn_)) continue;
+ if (!uniqueColAliases.add(field.getName())) {
+ throw new AnalysisException("Duplicate column name: " + field.getName());
+ }
+ fields.add(new StructField(field.getName(), field.getType()));
+ }
+ Preconditions.checkState(analyzedAggList_.size() >= 1);
+ for (String alias : headerValueMap_.keySet()) {
+ for (Map.Entry<String, FunctionCallExpr> entry : analyzedAggList_.entrySet()) {
+ String fieldName = analyzedAggList_.size() == 1 ?
+ alias : alias + "_" + entry.getKey();
+ if (!uniqueColAliases.add(fieldName)) {
+ throw new AnalysisException("Duplicate column name: " + fieldName);
+ }
+ fields.add(new StructField(fieldName, entry.getValue().getType()));
+ }
+ }
+ result.setType(new StructType(fields));
+ return result;
+ }
+
+ private void createHeaderValueMap(Analyzer analyzer) throws AnalysisException {
+ HashSet<LiteralExpr> headerValueSet = new HashSet<>();
+ for (SelectListItem item : headerValueList_.getItems()) {
+ if (!(item.getExpr() instanceof LiteralExpr)) {
+ throw new AnalysisException(
+ "Only literals are supported in the header value list of the PIVOT clause");
+ }
+ LiteralExpr e = (LiteralExpr)item.getExpr();
+ e.analyze(analyzer);
+ String alias = item.getAlias();
+ if (alias == null) {
+ alias = e.getStringValue();
+ }
+ if (headerValueMap_.put(alias, e) != null) {
+ throw new AnalysisException(
+ "Duplicate alias for header values in the PIVOT clause: " + alias);
+ }
+ if (!headerValueSet.add(e)) {
+ throw new AnalysisException(
+ "Duplicate value in the PIVOT clause: " + e.getStringValue());
+ }
+ }
+ }
+
+ private void analyzeAggList(Analyzer analyzer) throws AnalysisException {
+ for (SelectListItem item : aggList_.getItems()) {
+ // Use clone() to keep aggList_ read-only.
+ Expr e = item.getExpr().clone();
+ e.analyze(analyzer);
+ if (!e.isAggregate()) {
+ throw new AnalysisException(
+ "The function called in the PIVOT clause should be an aggregate");
+ }
+ if (item.getAlias() == null && aggList_.getItems().size() > 1) {
+ throw new AnalysisException(
+ "Aliases are required when multiple aggregate expressions are specified " +
+ "in the PIVOT clause");
+ }
+ if (analyzedAggList_.put(item.getAlias(), (FunctionCallExpr)e) != null) {
+ throw new AnalysisException(
+ "Duplicate alias for aggregations in the PIVOT clause: " + item.getAlias());
+ }
+ }
+ }
+
+ @Override
+ public void analyze(Analyzer analyzer) throws AnalysisException {
+ if (isAnalyzed_) return;
+ isAnalyzed_ = true;
+
+ sourceTableRef_ = analyzer.resolveTableRef(new TableRef(rawPath_, null));
+ sourceTableRef_.analyze(analyzer);
+ registerSourceTableSlotRef(analyzer, Lists.newArrayList(headerColumn_));
+ createHeaderValueMap(analyzer);
+ analyzeAggList(analyzer);
+ // The source table is not visible outside the PIVOT clause when resolving a Path.
+ sourceTableRef_.setHidden(true);
+ desc_ = analyzer.registerTableRef(this);
+ }
+
+ private SlotDescriptor registerSourceTableSlotRef(
+ Analyzer analyzer, List<String> rawPath) throws AnalysisException {
+ String columnName = rawPath.get(0);
+ Path sourceTableSlotPath = new Path(sourceTableRef_.getDesc(), rawPath);
+ if (!sourceTableSlotPath.resolve()) return null;
+ SlotDescriptor sourceTableSlot = analyzer.registerSlotRef(sourceTableSlotPath);
+ if (sourceTableSlot.getType().isComplexType()) {
+ // Throw an exception since GROUP BY expression cannot be used on complex types.
+ throw new AnalysisException(
+ "Referencing complex columns in the source table of the PIVOT clause " +
+ "is not supported");
+ }
+ sourceTableRef_.registerColumn(new Column(
+ String.join(".", rawPath), sourceTableSlot.getType(), -1));
+ groupingSlotMap_.put(columnName, sourceTableSlot);
+ return sourceTableSlot;
+ }
+
+ @Override
+ void notifySlotRefRegistered(Analyzer analyzer, SlotDescriptor slot)
+ throws AnalysisException {
+ List<String> rawPath = slot.getPath().getRawPath();
+ registerSourceTableSlotRef(analyzer, rawPath);
+ }
+
+ @Override
+ public String tableRefToSql(ToSqlOptions options) {
+ List<String> headerValueList = new ArrayList<>();
+ for (SelectListItem item : headerValueList_.getItems()) {
+ headerValueList.add(item.toSql());
+ }
+ List<String> aggList = new ArrayList<>();
+ for (SelectListItem item : aggList_.getItems()) {
+ aggList.add(item.toSql());
+ }
+ return String.format("%s PIVOT (%s FOR %s IN (%s)) %s",
+ resolvedSourceTableRef_ ? sourceTableRef_.toSql() : String.join(".", getPath()),
+ String.join(", ", aggList),
+ headerColumn_,
+ String.join(", ", headerValueList),
+ getUniqueAlias());
+ }
+
+ TableRef getSourceTableRef() { return sourceTableRef_; }
+
+ InlineViewRef toInlineViewRef(TableRef sourceTableRef) {
+ List<Expr> groupingExprs = new ArrayList<>();
+ List<SelectListItem> multiAggSelectList = new ArrayList<>();
+ int headerSlotRefIndex = -1;
+ int slotIdx = 0;
+ for (Map.Entry<String, SlotDescriptor> entry : groupingSlotMap_.entrySet()) {
+ Expr e = new SlotRef(entry.getValue().getPath().getRawPath());
+ groupingExprs.add(e);
+ multiAggSelectList.add(new SelectListItem(e, null));
+ String columnName = entry.getKey();
+ if (columnName.equals(headerColumn_)) {
+ Preconditions.checkState(headerSlotRefIndex == -1);
+ headerSlotRefIndex = slotIdx;
+ }
+ ++slotIdx;
+ }
+ Preconditions.checkState(headerSlotRefIndex >= 0);
+ String multiAggInlineViewRefAlias = getUniqueAlias() + "_" +
+ UUID.randomUUID().toString().replace("-", "_");
+ // singleAggAlias is used only when there is only one Agg Expr and its alias is not
+ // specified.
+ String singleAggAlias = multiAggInlineViewRefAlias + "_agg";
+ for (SelectListItem aggItem : aggList_.getItems()) {
+ // Use clone() to keep aggList_ read-only.
+ multiAggSelectList.add(aggItem.getAlias() != null ?
+ aggItem.clone() :
+ new SelectListItem(aggItem.getExpr().clone(), singleAggAlias));
+ }
+ if (sourceTableRef == null) {
+ sourceTableRef_.setHidden(false);
+ sourceTableRef = sourceTableRef_;
+ }
+ SelectStmt multiAggStmt = new SelectStmt(new SelectList(multiAggSelectList),
+ new FromClause(Lists.newArrayList(sourceTableRef)), null,
+ new GroupByClause(groupingExprs, GroupingSetsType.NONE), null, null, null);
+
+ InlineViewRef multiAggInlineViewRef = new InlineViewRef(
+ multiAggInlineViewRefAlias, multiAggStmt, (TableSampleClause) null);
+
+ List<Expr> transposeGroupingExprs = Expr.cloneList(groupingExprs);
+ transposeGroupingExprs.remove(headerSlotRefIndex);
+ List<SelectListItem> transposeSelectList = new ArrayList<>();
+ for (Expr e : transposeGroupingExprs) {
+ transposeSelectList.add(new SelectListItem(e, null));
+ }
+ SlotRef headerColumnSlotRef = new SlotRef(Lists.newArrayList(headerColumn_));
+ for (Map.Entry<String, LiteralExpr> entry : headerValueMap_.entrySet()) {
+ for (SelectListItem aggItem : aggList_.getItems()) {
+ String aggAlias =
+ aggItem.getAlias() == null ?singleAggAlias : aggItem.getAlias();
+ // clone() is needed to keep the headerValueMap read-only.
+ Expr cond = new BinaryPredicate(
+ Operator.EQ, headerColumnSlotRef, entry.getValue().clone());
+ FunctionCallExpr transAggExpr = new FunctionCallExpr("aggif",
+ Lists.newArrayList(cond, new SlotRef(Lists.newArrayList(aggAlias))));
+ String alias = entry.getKey();
+ if (aggList_.getItems().size() > 1) {
+ alias = alias + "_" + aggAlias;
+ }
+ transposeSelectList.add(new SelectListItem(transAggExpr, alias));
+ }
+ }
+ GroupByClause transposeGroupByClause = transposeGroupingExprs.isEmpty() ? null :
+ new GroupByClause(transposeGroupingExprs, GroupingSetsType.NONE);
+ SelectStmt transposeSelectStmt = new SelectStmt(new SelectList(transposeSelectList),
+ new FromClause(Lists.newArrayList(multiAggInlineViewRef)), null,
+ transposeGroupByClause, null, null, null);
+ return new InlineViewRef(
+ getUniqueAlias(), transposeSelectStmt, (TableSampleClause) null);
+ }
+
+ @Override
+ public TableRef applyTableMask(Analyzer analyzer) throws AnalysisException {
+ TableRef sourceTableRefClone = sourceTableRef_.clone();
+ sourceTableRefClone.setHidden(false);
+ TableRef maskedSourceTableRef = sourceTableRefClone.applyTableMask(analyzer);
+ InlineViewRef result = toInlineViewRef(maskedSourceTableRef);
+ result.setIsTableMaskingView();
+ return result;
+ }
+}
diff --git a/fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java b/fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java
index 2ec24786e..2b052b107 100644
--- a/fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java
+++ b/fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java
@@ -98,6 +98,9 @@ public class StmtRewriter {
protected void rewriteSelectStatement(SelectStmt stmt, Analyzer analyzer)
throws AnalysisException {
for (TableRef tblRef : stmt.fromClause_) {
+ if (tblRef instanceof PivotTableRef) {
+ tblRef = ((PivotTableRef)tblRef).getSourceTableRef();
+ }
if (!(tblRef instanceof InlineViewRef)) continue;
InlineViewRef inlineViewRef = (InlineViewRef) tblRef;
rewriteQueryStatement(inlineViewRef.getViewStmt(), inlineViewRef.getAnalyzer());
@@ -1880,4 +1883,22 @@ public class StmtRewriter {
for (TableRef tblRef : unnestTableRefs) stmt.fromClause_.add(tblRef);
}
}
+
+ static class PivotClauseRewriter extends StmtRewriter {
+ private boolean changed_ = false;
+
+ @Override
+ protected void rewriteSelectStmtHook(SelectStmt stmt, Analyzer analyzer)
+ throws AnalysisException {
+ for (int i = 0; i < stmt.fromClause_.getTableRefs().size(); ++i) {
+ if (stmt.fromClause_.get(i) instanceof PivotTableRef) {
+ PivotTableRef pivotTableRef = (PivotTableRef)stmt.fromClause_.get(i);
+ stmt.fromClause_.set(i, pivotTableRef.toInlineViewRef(null));
+ changed_ = true;
+ }
+ }
+ }
+
+ boolean changed() { return changed_; }
+ }
}
diff --git a/fe/src/main/java/org/apache/impala/analysis/TableRef.java b/fe/src/main/java/org/apache/impala/analysis/TableRef.java
index 1e1ed7f9f..815b11e67 100644
--- a/fe/src/main/java/org/apache/impala/analysis/TableRef.java
+++ b/fe/src/main/java/org/apache/impala/analysis/TableRef.java
@@ -29,7 +29,6 @@ import java.util.Map;
import java.util.Set;
import org.apache.impala.authorization.AuthorizationChecker;
-import org.apache.impala.authorization.AuthorizationContext;
import org.apache.impala.authorization.Privilege;
import org.apache.impala.authorization.TableMask;
import org.apache.impala.catalog.Column;
@@ -882,11 +881,16 @@ public class TableRef extends StmtNode {
return selectedDataFilesWithoutDeletesForOptimize_;
}
+ // This method is called after the slot descriptor of this TableRef is registered in
+ // Analyzer.registerSlotRef(). It is designed to be overridden by the subclasses.
void notifySlotRefRegistered(Analyzer analyzer, SlotDescriptor slot)
throws AnalysisException {
// Do nothing
}
+ // This method is called after the slot descriptor of the TableRef is set to
+ // materialized in Analyzer.materializeSlots(). It is designed to be overridden by the
+ // subclasses.
void notifySlotsMaterialized(Analyzer analyzer, Expr expr) {
// Do nothing
}
diff --git a/fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java b/fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
index 69b3b6109..2b8801277 100644
--- a/fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
+++ b/fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
@@ -1210,7 +1210,7 @@ public class BuiltinsDb extends Db {
db.addBuiltin(AggregateFunction.createBuiltin(db, "aggif",
Lists.newArrayList(ScalarType.BOOLEAN, t), t, t,
- initNull,
+ t.isStringType() ? initNullString : initNull,
prefix + AGGIF_UPDATE_SYMBOL.get(t),
prefix + AGGIF_MERGE_SYMBOL.get(t),
null,
diff --git a/fe/src/main/jflex/sql-scanner.flex b/fe/src/main/jflex/sql-scanner.flex
index bfa4e1a2f..e3a623921 100644
--- a/fe/src/main/jflex/sql-scanner.flex
+++ b/fe/src/main/jflex/sql-scanner.flex
@@ -222,6 +222,7 @@ import org.apache.impala.thrift.TReservedWordsVersion;
keywordMap.put("partition", SqlParserSymbols.KW_PARTITION);
keywordMap.put("partitioned", SqlParserSymbols.KW_PARTITIONED);
keywordMap.put("partitions", SqlParserSymbols.KW_PARTITIONS);
+ keywordMap.put("pivot", SqlParserSymbols.KW_PIVOT);
keywordMap.put("preceding", SqlParserSymbols.KW_PRECEDING);
keywordMap.put("prepare_fn", SqlParserSymbols.KW_PREPARE_FN);
keywordMap.put("primary", SqlParserSymbols.KW_PRIMARY);
diff --git a/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java b/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
index e151b2661..a17b79b6c 100644
--- a/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
+++ b/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
@@ -450,6 +450,73 @@ public class AnalyzeStmtsTest extends AnalyzerTest {
);
}
+ public void TestPivotClause() {
+ AnalysisError(
+ "select month from functional_parquet.alltypestiny pivot (" +
+ " count(month) for month in (1 as v1)) as t;",
+ "Could not resolve column/field reference: 'month'"
+ );
+ AnalysisError(
+ "select year, v1 from functional_parquet.alltypestiny pivot (" +
+ " sleep(1) for month in (1 as v1, 2 as v2)) as t;",
+ "The function called in the PIVOT clause should be an aggregate"
+ );
+ AnalysisError(
+ "select year, v1 from functional_parquet.alltypestiny pivot (" +
+ " min(month) for month in (1 as v1, 2 as v1)) as t;",
+ "Duplicate alias for header values in the PIVOT clause: v1"
+ );
+ AnalysisError(
+ "select year, v1 from functional_parquet.alltypestiny pivot (" +
+ " min(month) for month in (1 as v1, 1 as v2)) as t;",
+ "Duplicate value in the PIVOT clause: 1"
+ );
+ AnalysisError(
+ "select year from functional_parquet.alltypestiny pivot (" +
+ " min(month) for month in (1 as year)) as t;",
+ "Duplicate column name: year"
+ );
+ AnalysisError(
+ "with t1 (a, b) as (select 1, 'a')" +
+ "select * from t1 pivot (count(*) for b in ('a')) as t;",
+ "Duplicate column name: a"
+ );
+ AnalysisError(
+ "select year from functional_parquet.alltypestiny pivot (" +
+ " min(month) for month in (1, 2 as `1`)) as t;",
+ "Duplicate alias in the PIVOT clause: 1"
+ );
+ AnalysisError(
+ "select alltypes from functional_parquet.complextypes_structs pivot (" +
+ " count(*) for id in (1)) as t;",
+ "Referencing complex columns in the source table of the PIVOT clause " +
+ "is not supported"
+ );
+ AnalysisError(
+ "select year, v1 from functional_parquet.alltypestiny pivot (" +
+ " min(month), max(month) for month in (1 as v1, 2 as v2)) as t;",
+ "Aliases are required when multiple aggregate expressions are specified " +
+ "in the PIVOT clause"
+ );
+ AnalysisError(
+ "SELECT v1, v2 FROM functional.alltypes PIVOT ( " +
+ " count(id) for month in (int_col AS v1, 2 AS v2) ) AS t;",
+ "Only literals are suppoted in the header value list of the PIVOT clause"
+ );
+ AnalysisError(
+ "with t1 (v2_min, b) as (select 1, 2) " +
+ "select * from t1 pivot (" +
+ " min(b) as min, max(b) as max for b in (2 as v2, 4 as v4)) as t;",
+ "Duplicate column name: v2_min"
+ );
+ AnalysisError(
+ "with t1 (a, b) as (select 1, 2) " +
+ "select * from t1 pivot (" +
+ " min(b) as min, max(b) as min for b in (2 as v2, 4 as v4)) as t;",
+ "Duplicate alias for aggregations in the PIVOT clause: min"
+ );
+ }
+
/**
* Helper function that returns a list of integers used to improve readability
* in the path-related tests below.
diff --git a/fe/src/test/java/org/apache/impala/analysis/ParserTest.java b/fe/src/test/java/org/apache/impala/analysis/ParserTest.java
index 451c9f083..eefa6c110 100644
--- a/fe/src/test/java/org/apache/impala/analysis/ParserTest.java
+++ b/fe/src/test/java/org/apache/impala/analysis/ParserTest.java
@@ -3777,7 +3777,7 @@ public class ParserTest extends FrontendTestBase {
"select c, b, c where a = 5\n" +
" ^\n" +
"Encountered: WHERE\n" +
- "Expected: AND, AS, BETWEEN, DIV, EXCEPT, FROM, ILIKE, IN, INTERSECT, " +
+ "Expected: AND, AS, BETWEEN, DIV, EXCEPT, FOR, FROM, ILIKE, IN, INTERSECT, " +
"IREGEXP, IS, LIKE, LIMIT, ||, MINUS, NOT, OR, ORDER, REGEXP, RLIKE, UNION, " +
"COMMA, IDENTIFIER\n" +
"\n" +
@@ -3858,9 +3858,9 @@ public class ParserTest extends FrontendTestBase {
"... b, c,c,c,c,c,c,c,c,c,a a a,c,c,c,c,c,c,c,cd,c,d,d,,c,...\n" +
" ^\n" +
"Encountered: IDENTIFIER\n" +
- "Expected: CROSS, EXCEPT, FROM, FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, " +
- "LEFT, LIMIT, MINUS, OFFSET, ON, ORDER, RIGHT, STRAIGHT_JOIN, TABLESAMPLE, " +
- "UNION, USING, WHERE, COMMA\n");
+ "Expected: CROSS, EXCEPT, FOR, FROM, FULL, GROUP, HAVING, INNER, INTERSECT, " +
+ "JOIN, LEFT, LIMIT, MINUS, OFFSET, ON, ORDER, RIGHT, STRAIGHT_JOIN, " +
+ "TABLESAMPLE, UNION, USING, WHERE, COMMA\n");
// Long line: error close to the start
ParserError("select a a a, b, c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,cd,c,d,d,,c, from t",
@@ -3868,9 +3868,9 @@ public class ParserTest extends FrontendTestBase {
"select a a a, b, c,c,c,c,c,c,c,c,c,c,c,...\n" +
" ^\n" +
"Encountered: IDENTIFIER\n" +
- "Expected: CROSS, EXCEPT, FROM, FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, " +
- "LEFT, LIMIT, MINUS, OFFSET, ON, ORDER, RIGHT, STRAIGHT_JOIN, TABLESAMPLE, " +
- "UNION, USING, WHERE, COMMA\n");
+ "Expected: CROSS, EXCEPT, FOR, FROM, FULL, GROUP, HAVING, INNER, INTERSECT, " +
+ "JOIN, LEFT, LIMIT, MINUS, OFFSET, ON, ORDER, RIGHT, STRAIGHT_JOIN, " +
+ "TABLESAMPLE, UNION, USING, WHERE, COMMA\n");
// Long line: error close to the end
ParserError("select a, b, c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,cd,c,d,d, ,c, from t",
diff --git a/fe/src/test/java/org/apache/impala/planner/PlannerTest.java b/fe/src/test/java/org/apache/impala/planner/PlannerTest.java
index 86c7338bd..7eb4f0e97 100644
--- a/fe/src/test/java/org/apache/impala/planner/PlannerTest.java
+++ b/fe/src/test/java/org/apache/impala/planner/PlannerTest.java
@@ -216,6 +216,13 @@ public class PlannerTest extends PlannerTestBase {
runPlannerTestFile("grouping-sets");
}
+ @Test
+ public void testPivotClause() {
+ TQueryOptions options = defaultQueryOptions();
+ runPlannerTestFile("pivot-clause", options,
+ ImmutableSet.of(PlannerTestOption.VALIDATE_CARDINALITY));
+ }
+
@Test
public void testAnalyticFns() {
runPlannerTestFile("analytic-fns",
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test b/testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test
new file mode 100644
index 000000000..2b4721aed
--- /dev/null
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test
@@ -0,0 +1,230 @@
+# The child node of the AGGREGATE node is a SCAN node.
+# The predicate should be pushed down to the SCAN node.
+select year, v1, v2
+from functional_parquet.alltypestiny pivot (
+ min(month) as result for month in (1 as v1, 2 as v2)) as t
+where year = 2009;
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+07:EXCHANGE [UNPARTITIONED]
+|
+06:AGGREGATE [FINALIZE]
+| output: aggif:merge(`month` = 1, result), aggif:merge(`month` = 2, result)
+| group by: `year`
+| row-size=12B cardinality=1
+|
+05:EXCHANGE [HASH(`year`)]
+|
+02:AGGREGATE [STREAMING]
+| output: aggif(`month` = 1, min(`month`)), aggif(`month` = 2, min(`month`))
+| group by: `year`
+| row-size=12B cardinality=3
+|
+04:AGGREGATE [FINALIZE]
+| output: min:merge(`month`)
+| group by: `month`, `year`
+| row-size=12B cardinality=4
+|
+03:EXCHANGE [HASH(`month`,`year`)]
+|
+01:AGGREGATE [STREAMING]
+| output: min(`month`)
+| group by: `month`, `year`
+| row-size=12B cardinality=4
+|
+00:SCAN HDFS [functional_parquet.alltypestiny]
+ partition predicates: functional_parquet.alltypestiny.year = 2009
+ HDFS partitions=4/4 files=4 size=11.92KB
+ partition key scan
+ row-size=8B cardinality=4
+====
+# The child node of the AGGREGATE node is a SCAN node.
+# The predicate should be evaluated in the AGGREGATE node.
+select year, v1, v2
+from functional_parquet.alltypestiny pivot (
+ min(month) as result for month in (1 as v1, 2 as v2)) as t
+where v1 = 1;
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+07:EXCHANGE [UNPARTITIONED]
+|
+06:AGGREGATE [FINALIZE]
+| output: aggif:merge(`month` = 1, result), aggif:merge(`month` = 2, result)
+| group by: `year`
+| having: aggif(`month` = 1, result) = 1
+| row-size=12B cardinality=1
+|
+05:EXCHANGE [HASH(`year`)]
+|
+02:AGGREGATE [STREAMING]
+| output: aggif(`month` = 1, min(`month`)), aggif(`month` = 2, min(`month`))
+| group by: `year`
+| row-size=12B cardinality=3
+|
+04:AGGREGATE [FINALIZE]
+| output: min:merge(`month`)
+| group by: `month`, `year`
+| row-size=12B cardinality=4
+|
+03:EXCHANGE [HASH(`month`,`year`)]
+|
+01:AGGREGATE [STREAMING]
+| output: min(`month`)
+| group by: `month`, `year`
+| row-size=12B cardinality=4
+|
+00:SCAN HDFS [functional_parquet.alltypestiny]
+ HDFS partitions=4/4 files=4 size=11.92KB
+ partition key scan
+ row-size=8B cardinality=4
+====
+# The output of the AGGREGATE node is consumed by a JOIN node.
+with t1 as (
+ select year, v1, v2
+ from functional_parquet.alltypestiny pivot (
+ min(month) as result for month in (1 as v1, 2 as v2)) as t
+)
+select t1.year, v1, v2 from t1
+join functional_parquet.alltypestiny as t2 on t1.year = t2.year;
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+10:EXCHANGE [UNPARTITIONED]
+|
+04:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: t2.`year` = `year`
+| runtime filters: RF000 <- `year`, RF001 <- `year`
+| row-size=16B cardinality=758
+|
+|--09:EXCHANGE [BROADCAST]
+| |
+| 08:AGGREGATE [FINALIZE]
+| | output: aggif:merge(`month` = 1, result), aggif:merge(`month` = 2, result)
+| | group by: `year`
+| | row-size=12B cardinality=1
+| |
+| 07:EXCHANGE [HASH(`year`)]
+| |
+| 02:AGGREGATE [STREAMING]
+| | output: aggif(`month` = 1, min(`month`)), aggif(`month` = 2, min(`month`))
+| | group by: `year`
+| | row-size=12B cardinality=3
+| |
+| 06:AGGREGATE [FINALIZE]
+| | output: min:merge(`month`)
+| | group by: `month`, `year`
+| | row-size=12B cardinality=4
+| |
+| 05:EXCHANGE [HASH(`month`,`year`)]
+| |
+| 01:AGGREGATE [STREAMING]
+| | output: min(`month`)
+| | group by: `month`, `year`
+| | row-size=12B cardinality=4
+| |
+| 00:SCAN HDFS [functional_parquet.alltypestiny]
+| HDFS partitions=4/4 files=4 size=11.92KB
+| partition key scan
+| row-size=8B cardinality=4
+|
+03:SCAN HDFS [functional_parquet.alltypestiny t2]
+ HDFS partitions=4/4 files=4 size=11.92KB
+ runtime filters: RF001 -> t2.`year`, RF000 -> t2.`year`
+ row-size=4B cardinality=758
+====
+# The output of the AGGREGATE node is consumed by a JOIN node on a column
+# created from the split.
+with t1 as (
+ select year, v1, v2
+ from functional_parquet.alltypestiny pivot (
+ min(month) as result for month in (1 as v1, 2 as v2)) as t
+)
+select t1.year, v1, v2 from t1
+join functional_parquet.alltypestiny as t2 on t1.v2 = t2.month;
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+10:EXCHANGE [UNPARTITIONED]
+|
+04:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: t2.`month` = aggif(`month` = 2, result)
+| runtime filters: RF000 <- aggif(`month` = 2, result), RF001 <- aggif(`month` = 2, result)
+| row-size=16B cardinality=758
+|
+|--09:EXCHANGE [BROADCAST]
+| |
+| 08:AGGREGATE [FINALIZE]
+| | output: aggif:merge(`month` = 1, result), aggif:merge(`month` = 2, result)
+| | group by: `year`
+| | row-size=12B cardinality=1
+| |
+| 07:EXCHANGE [HASH(`year`)]
+| |
+| 02:AGGREGATE [STREAMING]
+| | output: aggif(`month` = 1, min(`month`)), aggif(`month` = 2, min(`month`))
+| | group by: `year`
+| | row-size=12B cardinality=3
+| |
+| 06:AGGREGATE [FINALIZE]
+| | output: min:merge(`month`)
+| | group by: `month`, `year`
+| | row-size=12B cardinality=4
+| |
+| 05:EXCHANGE [HASH(`month`,`year`)]
+| |
+| 01:AGGREGATE [STREAMING]
+| | output: min(`month`)
+| | group by: `month`, `year`
+| | row-size=12B cardinality=4
+| |
+| 00:SCAN HDFS [functional_parquet.alltypestiny]
+| HDFS partitions=4/4 files=4 size=11.92KB
+| partition key scan
+| row-size=8B cardinality=4
+|
+03:SCAN HDFS [functional_parquet.alltypestiny t2]
+ HDFS partitions=4/4 files=4 size=11.92KB
+ runtime filters: RF001 -> t2.`month`, RF000 -> t2.`month`
+ row-size=4B cardinality=758
+====
+# Aggregates with multiple DISTINCTs in the PIVOT clause.
+with t1 (a, b, c) as (
+ values (1, 2, 3), (1, 2, 3), (1, 2, 4)
+)
+select v2_count_a, v2_count_c from t1 pivot (
+ count(distinct a) as count_a, count(distinct c) as count_c for b in (2 as v2)
+) as t;
+---- DISTRIBUTEDPLAN
+PLAN-ROOT SINK
+|
+04:AGGREGATE [FINALIZE]
+| output: aggif(CASE valid_tid(3,5) WHEN 3 THEN b WHEN 5 THEN b END = 2, aggif(valid_tid(3,5) = 3, count(a))), aggif(CASE valid_tid(3,5) WHEN 3 THEN b WHEN 5 THEN b END = 2, aggif(valid_tid(3,5) = 5, count(c)))
+| row-size=16B cardinality=1
+|
+03:AGGREGATE [FINALIZE]
+| output: aggif(valid_tid(3,5) = 3, count(a)), aggif(valid_tid(3,5) = 5, count(c))
+| group by: CASE valid_tid(3,5) WHEN 3 THEN b WHEN 5 THEN b END
+| row-size=17B cardinality=3
+|
+02:AGGREGATE [FINALIZE]
+| Class 0
+| output: count(a)
+| group by: b
+| Class 1
+| output: count(c)
+| group by: b
+| row-size=18B cardinality=6
+|
+01:AGGREGATE
+| Class 0
+| group by: 2, 1
+| Class 1
+| group by: 2, 3
+| row-size=4B cardinality=6
+|
+00:UNION
+ constant-operands=3
+ row-size=3B cardinality=3
+====
diff --git a/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test b/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
new file mode 100644
index 000000000..1acc8c485
--- /dev/null
+++ b/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
@@ -0,0 +1,434 @@
+====
+---- QUERY
+# Each result column of PIVOT contains the minimum id in the month.
+select year, v1, v2, v3, v4
+from functional_parquet.alltypestiny pivot (
+ min(id) for month in (1 as v1, 2 as v2, 3 as v3, 4 as v4)) as t;
+---- TYPES
+INT,INT,INT,INT,INT
+---- RESULTS
+2009,0,2,4,6
+====
+---- QUERY
+# The child node of the AGGREGATE node is a SCAN node.
+# min(month) is used to easily check if the value of `month` for the result
+# column matches the value in the PIVOT clause.
+select year, v1, v2, v3, v4
+from functional_parquet.alltypestiny pivot (
+ min(month) for month in (1 as v1, 2 as v2, 3 as v3, 4 as v4)) as t;
+---- TYPES
+INT,INT,INT,INT,INT
+---- RESULTS
+2009,1,2,3,4
+====
+---- QUERY
+# Uses DISTINCT aggregation in the PIVOT clause.
+select year, v1, v2, v3, v4
+from functional_parquet.alltypestiny pivot (
+ count(distinct month) for month in (1 as v1, 2 as v2, 3 as v3, 4 as v4)) as t;
+---- TYPES
+INT,BIGINT,BIGINT,BIGINT,BIGINT
+---- RESULTS
+2009,1,1,1,1
+====
+---- QUERY
+select year, v1
+from functional_parquet.alltypestiny pivot (
+ count(*) for month in (1 as v1, 2 as v2)) as t;
+---- TYPES
+INT,BIGINT
+---- RESULTS
+2009,2
+====
+---- QUERY
+select year, v1
+from functional_parquet.alltypestiny pivot (
+ count(1) for month in (1 as v1, 2 as v2)) as t;
+---- TYPES
+INT,BIGINT
+---- RESULTS
+2009,2
+====
+---- QUERY
+# The parent node of the AGGREGATE node is a SORT node.
+select v1, v2, v3, v4
+from functional_parquet.alltypestiny pivot (
+ min(month) for month in (1 as v1, 2 as v2, 3 as v3, 4 as v4)
+) as t
+order by year;
+---- TYPES
+INT,INT,INT,INT
+---- RESULTS
+1,2,3,4
+====
+---- QUERY
+# The child node of the AGGRTEGATE node is a UNION node.
+with t1 (a, b) as (
+ values (1, 2), (3, 4)
+)
+select a from t1 pivot (
+ min(b) for b in (2 as v2, 4 as v4)
+) as t;
+---- TYPES
+TINYINT
+---- RESULTS
+3
+1
+====
+---- QUERY
+with t1 (a, b) as (
+ values (1, 2), (3, 4)
+)
+select a, v2, v4 from t1 pivot (
+ min(b) for b in (2 as v2, 4 as v4)
+) as t;
+---- TYPES
+TINYINT,TINYINT,TINYINT
+---- RESULTS
+3,NULL,4
+1,2,NULL
+====
+---- QUERY
+# The type of the pivot values is different from the type of the pivot column.
+with t1 (a, b) as (
+ values (1, CAST(NULL AS INT))
+)
+select a, v2, v4 from t1 pivot (
+ min(b) for b in (2 as v2, 4 as v4)
+) as t;
+---- TYPES
+TINYINT,INT,INT
+---- RESULTS
+1,NULL,NULL
+====
+---- QUERY
+# The pivot column only contains NULLs.
+with t1 (a, b) as (
+ values (1, CAST(NULL AS STRING))
+)
+select a, v2, v4, v2 IS NULL, v4 IS NULL from t1 pivot (
+ min(b) for b in ('2' as v2, '4' as v4)
+) as t;
+---- TYPES
+TINYINT,STRING,STRING,BOOLEAN,BOOLEAN
+---- RESULTS
+1,'NULL','NULL',true,true
+====
+---- QUERY
+# The child node of the AGGREGATE node is an inner JOIN node.
+with t1 (a, b) as (
+ values (1, 2), (3, 4)
+), t2 as (
+ select t11.a as a1, t11.b as b1, t12.a as a2, t12.b as b2
+ from t1 as t11 join t1 as t12
+)
+select a1, v2, v4 from t2 pivot (
+ count(b2) for b2 in (2 as v2, 4 as v4)
+) as t;
+---- TYPES
+TINYINT,BIGINT,BIGINT
+---- RESULTS
+1,1,1
+3,1,1
+====
+---- QUERY
+# The child node of the AGGREGATE node is an outer JOIN node.
+with t1 (a, b) as (
+ values (1, 2), (3, 4)
+), t2 (a, b) as (
+ values (5, 6), (7, 8)
+), t3 as (
+ select t1.a as a1, t1.b as b1, t2.a as a2, t2.b as b2
+ from t1 left outer join t2 on t1.a = t2.a
+)
+select a1, v2, v4 from t3 pivot (
+ count(b2) for b2 in (2 as v2, 4 as v4)
+) as t;
+---- TYPES
+TINYINT,BIGINT,BIGINT
+---- RESULTS
+1,NULL,NULL
+3,NULL,NULL
+====
+---- QUERY
+# The PIVOT clause is in an inline view.
+with t1 (a, b) as (
+ values (1, 2), (3, 4)
+), t2 as (
+ select a, v2, v4 from t1 pivot (
+ min(b) for b in (2 as v2, 4 as v4)
+ ) as t
+)
+select * from t2;
+---- TYPES
+TINYINT,TINYINT,TINYINT
+---- RESULTS
+3,NULL,4
+1,2,NULL
+====
+---- QUERY
+# PIVOT on a local view.
+select year, m5, m6 from functional.alltypes_view pivot (
+ count(*) for month in (5 as m5, 6 as m6)
+) as t;
+---- TYPES
+INT,BIGINT,BIGINT
+---- RESULTS
+2009,310,300
+2010,310,300
+====
+---- QUERY
+# Selects * from the PIVOT table
+select * from functional_parquet.alltypestiny pivot (
+ min(month) for month in (1 as v1, 2 as v2, 3 as v3, 4 as v4)) as t
+order by id;
+---- TYPES
+INT,INT,BOOLEAN,TINYINT,SMALLINT,INT,BIGINT,FLOAT,DOUBLE,STRING,STRING,TIMESTAMP,INT,INT,INT,INT
+---- RESULTS
+2009,0,true,0,0,0,0,0.0,0.0,'01/01/09','0',2009-01-01 00:00:00,1,NULL,NULL,NULL
+2009,1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,1,NULL,NULL,NULL
+2009,2,true,0,0,0,0,0.0,0.0,'02/01/09','0',2009-02-01 00:00:00,NULL,2,NULL,NULL
+2009,3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,NULL,2,NULL,NULL
+2009,4,true,0,0,0,0,0.0,0.0,'03/01/09','0',2009-03-01 00:00:00,NULL,NULL,3,NULL
+2009,5,false,1,1,1,10,1.100000023841858,10.1,'03/01/09','1',2009-03-01 00:01:00,NULL,NULL,3,NULL
+2009,6,true,0,0,0,0,0.0,0.0,'04/01/09','0',2009-04-01 00:00:00,NULL,NULL,NULL,4
+2009,7,false,1,1,1,10,1.100000023841858,10.1,'04/01/09','1',2009-04-01 00:01:00,NULL,NULL,NULL,4
+====
+---- QUERY
+# Evaluates a predicate in the AGGREGATE node.
+with t1 (a, b) as (
+ values (1, 2), (3, 4)
+)
+select v2, v4 from t1 pivot (
+ min(b) for b in (2 as v2, 4 as v4)
+) as t
+where a = 1;
+---- TYPES
+TINYINT,TINYINT
+---- RESULTS
+2,NULL
+====
+---- QUERY
+# The predicate should be pushed down to the SCAN node.
+select v1, v2, v3, v4 from functional_parquet.alltypestiny pivot (
+ min(month) for month in (1 as v1, 2 as v2, 3 as v3, 4 as v4)) as t
+where id = 1;
+---- TYPES
+INT,INT,INT,INT
+---- RESULTS
+1,NULL,NULL,NULL
+====
+---- QUERY
+# The child of the AGGREGATE node is an ANALYTIC node.
+with t1 as (
+ select id, row_number() over (order by id) as b
+ from functional_parquet.alltypestiny
+)
+select * from t1 pivot (
+ min(b) for b in (
+ 1 as v1, 2 as v2, 3 as v3, 4 as v4, 5 as v5, 6 as v6, 7 as v7, 8 as v8)
+) as t
+order by id;
+---- TYPES
+INT,BIGINT,BIGINT,BIGINT,BIGINT,BIGINT,BIGINT,BIGINT,BIGINT
+---- RESULTS
+0,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL
+1,NULL,2,NULL,NULL,NULL,NULL,NULL,NULL
+2,NULL,NULL,3,NULL,NULL,NULL,NULL,NULL
+3,NULL,NULL,NULL,4,NULL,NULL,NULL,NULL
+4,NULL,NULL,NULL,NULL,5,NULL,NULL,NULL
+5,NULL,NULL,NULL,NULL,NULL,6,NULL,NULL
+6,NULL,NULL,NULL,NULL,NULL,NULL,7,NULL
+7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,8
+====
+---- QUERY
+# The AGGREGATE node reads from an TOP-N node.
+with t1 as (
+ select month
+ from functional_parquet.alltypestiny
+ order by year limit 8
+)
+select v1, v2, v3, v4 from t1 pivot (
+ count(month) for month in (1 as v1, 2 as v2, 3 as v3, 4 as v4)) as t;
+---- TYPES
+BIGINT,BIGINT,BIGINT,BIGINT
+---- RESULTS
+2,2,2,2
+====
+---- QUERY
+# The output of the AGGREGATE node is consumed by a JOIN node.
+with t1 as (
+ select year, v1, v2
+ from functional_parquet.alltypestiny pivot (
+ min(month) for month in (1 as v1, 2 as v2)) as t
+)
+select t1.year, v1, v2
+from t1 join functional_parquet.alltypestiny as t2 on t1.year = t2.year;
+---- TYPES
+INT,INT,INT
+---- RESULTS
+2009,1,2
+2009,1,2
+2009,1,2
+2009,1,2
+2009,1,2
+2009,1,2
+2009,1,2
+2009,1,2
+====
+---- QUERY
+# The output of the AGGREGATE node is consumed by a JOIN node on a column
+# created from the split.
+with t1 as (
+ select year, v1, v2
+ from functional_parquet.alltypestiny pivot (
+ min(month) for month in (1 as v1, 2 as v2)) as t
+)
+select t1.year, v1, v2
+from t1 join functional_parquet.alltypestiny as t2 on t1.v2 = t2.month;
+---- TYPES
+INT,INT,INT
+---- RESULTS
+2009,1,2
+2009,1,2
+====
+---- QUERY
+# The child of the AGGREGATE NODE is a UNION node for a constant SELECT.
+# Aliases in the PIVOT clause should be optional.
+with t1 (a, b) as (
+ select 1, 2
+), t2 (a, b, c) as (
+ select * from t1 pivot (min(b) for b in (1, 2)) as t
+)
+select * from t2;
+---- LABELS
+a,b,c
+---- TYPES
+TINYINT,TINYINT,TINYINT
+---- RESULTS
+1,NULL,2
+====
+---- QUERY
+# The source table is a collection column.
+select * from functional_parquet.complextypestbl.int_map pivot (
+ min(value) for key in ('k1', 'k2', 'k3')
+) as t;
+---- TYPES
+INT,INT,INT,INT
+---- RESULTS
+1,1,NULL,NULL
+-1,-1,NULL,NULL
+NULL,NULL,NULL,NULL
+2,2,NULL,NULL
+100,NULL,100,NULL
+====
+---- QUERY
+# The PIVOT clause contains multiple aggregate function calls.
+with t1 (a, b) as (
+ values (1, 2), (3, 4)
+)
+select * from t1 pivot (
+ min(b) as min, max(b) as max for b in (2 as v2, 4 as v4)
+) as t;
+---- LABELS
+a,v2_min,v2_max,v4_min,v4_max
+---- TYPES
+TINYINT,TINYINT,TINYINT,TINYINT,TINYINT
+---- RESULTS
+3,NULL,NULL,4,4
+1,2,2,NULL,NULL
+====
+---- QUERY
+# Aggregates with multiple DISTINCTs in the PIVOT clause.
+with t1 (a, b, c) as (
+ values (1, 2, 3), (1, 2, 3), (1, 2, 4)
+)
+select v2_count_a, v2_count_c from t1 pivot (
+ count(distinct a) as count_a, count(distinct c) as count_c for b in (2 as v2)
+) as t;
+---- TYPES
+BIGINT,BIGINT
+---- RESULTS
+1,2
+====
+---- QUERY
+# The aggregate function has multiple parameters.
+with t1 (a, b) as (
+ values (1, 'a'), (1, 'b')
+)
+select v1 from t1 pivot (
+ group_concat(b, ',') for a in (1 as v1)
+) as t;
+---- TYPES
+STRING
+---- RESULTS
+'a,b'
+====
+---- QUERY
+# The query contains a HAVING clause.
+select year, m5, m6
+from functional.alltypes pivot(
+ max(id) for month in (5 as m5, 6 as m6)
+) as t
+having m5 > 2000;
+---- TYPES
+INT,INT,INT
+---- RESULTS
+2010,5159,5459
+====
+---- QUERY
+# Nested PIVOT operation.
+with t1 as (
+ select year, v1, v2
+ from functional_parquet.alltypestiny pivot (
+ min(id) for month in (1 as v1, 2 as v2)) as t
+)
+select * from t1 pivot (
+ min(v1) for year in (2009 as y2009)) as t2;
+---- LABELS
+v1,v2,y2009
+---- TYPES
+INT,INT,INT
+---- RESULTS
+0,2,0
+====
+---- QUERY
+# The PIVOT clause is in a Set Operation statement.
+with t as (
+ select year, v1, v2
+ from functional_parquet.alltypestiny pivot (
+ min(month) for month in (1 as v1, 2 as v2)) as t
+ intersect
+ select year, v1, v2
+ from functional_parquet.alltypestiny pivot (
+ min(month) for month in (1 as v1, 2 as v2)) as t2
+)
+select * from t;
+---- LABELS
+year,v1,v2
+---- TYPES
+INT,INT,INT
+---- RESULTS
+2009,1,2
+====
+---- QUERY
+# The PIVOT clause is in a CREATE TABLE AS statement.
+create table test_pivot_ctas as
+ select year, v1, v2, v3, v4
+ from functional_parquet.alltypestiny pivot (
+ min(month) for month in (1 as v1, 2 as v2, 3 as v3, 4 as v4)) as t;
+---- CATCH
+NotImplementedException: Planning not implemented for table ref class: class org.apache.impala.analysis.PivotTableRef
+====
+---- QUERY
+create table test_pivot_insert (
+ v1 int,
+ v2 int
+) stored as parquet;
+insert into test_pivot_insert
+ select v1, v2
+ from functional_parquet.alltypestiny pivot (
+ min(month) for month in (1 as v1, 2 as v2)) as t;
+---- CATCH
+NotImplementedException: Planning not implemented for table ref class: class org.apache.impala.analysis.PivotTableRef
+====
diff --git a/testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking_and_row_filtering.test b/testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking_and_row_filtering.test
index c2f957bc6..993966085 100644
--- a/testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking_and_row_filtering.test
+++ b/testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking_and_row_filtering.test
@@ -188,3 +188,12 @@ STRING,INT
'v1',103
'v1',106
====
+---- QUERY
+select v1, v2, v3, v4
+from functional.alltypestiny pivot (
+ min(id) for month in (1 as v1, 2 as v2, 3 as v3, 4 as v4)) as t;
+---- TYPES
+INT,INT,INT,INT
+---- RESULTS
+100,103,NULL,106
+====
diff --git a/tests/query_test/test_pivot_clause.py b/tests/query_test/test_pivot_clause.py
new file mode 100644
index 000000000..432cc772a
--- /dev/null
+++ b/tests/query_test/test_pivot_clause.py
@@ -0,0 +1,36 @@
+# 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.
+
+# Tests the PIVOT clause.
+
+from tests.common.impala_test_suite import ImpalaTestSuite
+from tests.common.test_dimensions import (
+ create_single_exec_option_dimension,
+ create_parquet_dimension,
+ default_client_protocol_dimension,
+)
+
+
+class TestPivotClause(ImpalaTestSuite):
+ @classmethod
+ def add_test_dimensions(cls):
+ cls.ImpalaTestMatrix.add_dimension(default_client_protocol_dimension())
+ cls.ImpalaTestMatrix.add_dimension(create_parquet_dimension(cls.get_workload()))
+ cls.ImpalaTestMatrix.add_dimension(create_single_exec_option_dimension())
+
+ def test_pivot_clause(self, unique_database, vector):
+ self.run_test_case('QueryTest/pivot-clause', vector, unique_database)