You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@impala.apache.org by "Xuebin Su (Code Review)" <ge...@cloudera.org> on 2026/03/17 08:52:00 UTC

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Xuebin Su has uploaded this change for review. ( http://gerrit.cloudera.org:8080/24105


Change subject: IMPALA-2083: Add PIVOT clause
......................................................................

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 `UnpivotTableRef.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 translated
  into a `MultiAggregateInfo`.
- Finally, in the planning phase, `AggregationNode`s will be created
  from the `MultiAggregateInfo`.

No new plan node or new execution node is added.

Testing:
- Added E2E tests in test_pivot_clause.py
- Added FE tests to ensure that predicates can be pushed down to the
  SCAN nodes and the result table of PIVOT can be broadcast for JOIN.

Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
---
M be/src/exprs/aggregate-functions-ir.cc
M fe/src/main/cup/sql-parser.cup
M fe/src/main/java/org/apache/impala/analysis/AggregateInfo.java
M fe/src/main/java/org/apache/impala/analysis/Analyzer.java
M fe/src/main/java/org/apache/impala/analysis/ExprSubstitutionMap.java
M fe/src/main/java/org/apache/impala/analysis/FromClause.java
M fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java
A fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
M fe/src/main/java/org/apache/impala/analysis/QueryStmt.java
M fe/src/main/java/org/apache/impala/analysis/SelectStmt.java
M fe/src/main/java/org/apache/impala/analysis/SetOperationStmt.java
M fe/src/main/java/org/apache/impala/analysis/TableRef.java
M fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
M fe/src/main/java/org/apache/impala/planner/AggregationNode.java
M fe/src/main/java/org/apache/impala/planner/SingleNodePlanner.java
M fe/src/main/jflex/sql-scanner.flex
M fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
M fe/src/test/java/org/apache/impala/planner/PlannerTest.java
A testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test
A testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
A tests/query_test/test_pivot_clause.py
21 files changed, 1,062 insertions(+), 52 deletions(-)



  git pull ssh://gerrit.cloudera.org:29418/Impala-ASF refs/changes/05/24105/2
-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 2
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 14: Verified+1


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 14
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Fri, 15 May 2026 08:53:50 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Xuebin Su (Code Review)" <ge...@cloudera.org>.
Xuebin Su has uploaded a new patch set (#9). ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................

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.

Limitations:
- Multiple header columns in the PIVOT clause 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
---
M be/src/exprs/aggregate-functions-ir.cc
M fe/src/main/cup/sql-parser.cup
M fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
M fe/src/main/java/org/apache/impala/analysis/Analyzer.java
M fe/src/main/java/org/apache/impala/analysis/FromClause.java
M fe/src/main/java/org/apache/impala/analysis/InlineViewRef.java
A fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
M fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java
M fe/src/main/java/org/apache/impala/analysis/TableRef.java
M fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
M fe/src/main/jflex/sql-scanner.flex
M fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
M fe/src/test/java/org/apache/impala/planner/PlannerTest.java
A testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test
A testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
M testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking_and_row_filtering.test
A tests/query_test/test_pivot_clause.py
17 files changed, 1,154 insertions(+), 89 deletions(-)


  git pull ssh://gerrit.cloudera.org:29418/Impala-ASF refs/changes/05/24105/9
-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: newpatchset
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 9
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Xuebin Su (Code Review)" <ge...@cloudera.org>.
Xuebin Su has uploaded a new patch set (#6). ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................

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.

Limitations:
- Multiple header columns in the PIVOT clause 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
---
M be/src/exprs/aggregate-functions-ir.cc
M fe/src/main/cup/sql-parser.cup
M fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
M fe/src/main/java/org/apache/impala/analysis/Analyzer.java
M fe/src/main/java/org/apache/impala/analysis/FromClause.java
M fe/src/main/java/org/apache/impala/analysis/InlineViewRef.java
A fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
M fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java
M fe/src/main/java/org/apache/impala/analysis/TableRef.java
M fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
M fe/src/main/jflex/sql-scanner.flex
M fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
M fe/src/test/java/org/apache/impala/planner/PlannerTest.java
A testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test
A testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
M testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking_and_row_filtering.test
A tests/query_test/test_pivot_clause.py
17 files changed, 1,144 insertions(+), 88 deletions(-)


  git pull ssh://gerrit.cloudera.org:29418/Impala-ASF refs/changes/05/24105/6
-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: newpatchset
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 6
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 11:

Build Successful 

https://jenkins.impala.io/job/gerrit-code-review-checks/22424/ : Initial code review checks passed. Use gerrit-verify-dryrun-external or gerrit-verify-dryrun to run full precommit tests.


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 11
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Thu, 14 May 2026 10:06:20 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 9:

Build Successful 

https://jenkins.impala.io/job/gerrit-code-review-checks/22400/ : Initial code review checks passed. Use gerrit-verify-dryrun-external or gerrit-verify-dryrun to run full precommit tests.


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 9
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Wed, 13 May 2026 03:01:21 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Xuebin Su (Code Review)" <ge...@cloudera.org>.
Xuebin Su has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 5:

(15 comments)

> Patch Set 3:
> 
> (16 comments)

Thanks for your review!

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/cup/sql-parser.cup
File fe/src/main/cup/sql-parser.cup:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/cup/sql-parser.cup@3525
PS3, Line 3525: ident_or_unreserved
> I think we need dotted_path for header_column to support nested fields, e.g
Thanks! I think nested fields can be extracted in a subquery.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/cup/sql-parser.cup@3524
PS3, Line 3524:   | 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
> I think this doesn't support multiple aggregates or multiple pivot columns.
Thanks! Added "Limitations" section in the commit message.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/AggregateInfo.java
File fe/src/main/java/org/apache/impala/analysis/AggregateInfo.java:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/AggregateInfo.java@110
PS3, Line 110:  */
> nit: use 4 spaces indent
Thanks!


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/AggregateInfo.java@156
PS3, Line 156:       }
> nit: our code style prefers putting the parameters in less lines.
Thanks!


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/FromClause.java
File fe/src/main/java/org/apache/impala/analysis/FromClause.java:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/FromClause.java@197
PS3, Line 197:         // PivotTableRef.clone() creates an unanalyzed clone and can be used for reset.
> nit: can we add a comment for using clone() for reset?
Thanks! Added.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java
File fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java@592
PS3, Line 592:    */
> nit: not used
Thanks!


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java@598
PS3, Line 598:       // we use a bigint type for the grouping_id later, so we allow up to
> I think it won't be hard to support pivoting on multiple columns. We just n
Thanks! I agree.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
File fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@62
PS3, Line 62: public class PivotTableRef extends TableRef {
> nit: please add a comment about using LinkedHashMap to perserve the order o
Thanks! Changed.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@96
PS3, Line 96: 
> Aggregation functions could have more than one parameter, e.g., group_conca
Thanks! Changed to cloning the whole list.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@147
PS3, Line 147:       }
> It's not guaranteed that these are literals. I think exprs that have consta
Thanks! I think they are supported in the new Patch Set.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@206
PS3, Line 206:     List<String> rawPath = slot.getPath().getRawPath();
> nit: add a @Override annotation
Thanks! Added


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@209
PS3, Line 209: 
> This might loss the field names for nested columns. I see an exception that
Thanks! Fixed.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@258
PS3, Line 258:     InlineViewRef multiAggInlineViewRef = new InlineViewRef(
> Using ClassCastException for type checking is generally discouraged in Java
Thanks!


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@300
PS3, Line 300: 
> nit: Unnecessary 'toString()' call
Thanks! Removed.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
File fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java@455
PS3, Line 455:     );
> Let's add another test for "month in (1, 2 as `1`)"
Thanks! Added.



-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 5
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Wed, 01 Apr 2026 09:11:30 +0000
Gerrit-HasComments: Yes

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 5:

Build Failed 

https://jenkins.impala.io/job/gerrit-code-review-checks/22061/ : Initial code review checks failed. See linked job for details on the failure.


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 5
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Wed, 01 Apr 2026 09:14:55 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Xuebin Su (Code Review)" <ge...@cloudera.org>.
Xuebin Su has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 6:

(1 comment)

> Patch Set 3:
> 
> (16 comments)

Thanks for your reviews!

http://gerrit.cloudera.org:8080/#/c/24105/3/testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking.test
File testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking.test:

http://gerrit.cloudera.org:8080/#/c/24105/3/testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking.test@994
PS3, Line 994: ====
> Let's add some tests in ranger_column_masking.test and ranger_row_filtering
Thanks! Added.



-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 6
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Mon, 27 Apr 2026 07:24:17 +0000
Gerrit-HasComments: Yes

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 6: Verified-1

Build failed: https://jenkins.impala.io/job/gerrit-verify-dryrun/13389/


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 6
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Mon, 27 Apr 2026 09:07:32 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Quanlong Huang (Code Review)" <ge...@cloudera.org>.
Quanlong Huang has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 9:

(9 comments)

I still need some more time to look into the details. Left some comments for discussion first.

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/cup/sql-parser.cup
File fe/src/main/cup/sql-parser.cup:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/cup/sql-parser.cup@3525
PS3, Line 3525: ident_or_unreserved
> Thanks! I think nested fields can be extracted in a subquery.
If we don't support this, let's mention it in the commit message.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
File fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java@625
PS9, Line 625:         if (stmt_ instanceof SelectStmt) {
It'd be more tidy to use a method like requiresSubqueryRewrite(). Then we can also remove the changed_ flag used here.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
File fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@96
PS3, Line 96:   // Creates an unanalyzed clone.
> Thanks! Changed to cloning the whole list.
Can we add a test for this?


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@147
PS3, Line 147:           throw new AnalysisException("Duplicate column name: " + fieldName);
> Thanks! I think they are supported in the new Patch Set.
The new patch set still cast the Expr to LiteralExpr which doesn't work for FunctionalCallExpr.

It'd be nice to support statements like

 SELECT v1, v2 FROM functional.alltypes PIVOT ( count(id) for month in (month(now()) AS m1, month(now())+1 AS m2) ) AS t;

month(now()) is evaluated to the current month.

This needs a better error message instead of hitting ClassCastException:

 SELECT v1, v2 FROM functional.alltypes PIVOT ( count(id) for month in (int_col AS v1, 2 AS v2) ) AS t;


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
File fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@21
PS9, Line 21: import java.util.HashMap;
nit: unused import


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@73
PS9, Line 73:   private final Map<String, FunctionCallExpr> analyzedAggList_ = new LinkedHashMap<>();
            :   private final Map<String, LiteralExpr> headerValueMap_ = new LinkedHashMap<>();
            :   private final Map<String, SlotDescriptor> groupingSlotMap_ = new LinkedHashMap<>();
nit: Let's add a comment mentioning what are the keys of these maps, e.g. analyzedAggList_ maps alias to the analyzed agg exprs.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@277
PS9, Line 277: uniqueAggAlias
nit: at first glance, it seems all items use the same alias. But later I realized we require writing alias for multiple aggs on L184. Maybe it's more clear by adding a comment above 'uniqueAggAlias' mentioning it's only used for single agg scenario.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@291
PS9, Line 291:     Expr headerSlotRef = transposeGroupingExprs.get(headerSlotRefIndex);
nit: not used


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@298
PS9, Line 298:     for (SelectListItem aggItem : aggList_.getItems()) {
             :       String aggAlias = aggItem.getAlias() == null ? uniqueAggAlias : aggItem.getAlias();
             :       for (Map.Entry<String, LiteralExpr> entry : headerValueMap_.entrySet()) {
Oracle iterates the header values before the agg alias: https://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#sthref1224
Same for SparkSQL: https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-pivot.html

It'd be better to be consistent with them.

SELECT * FROM person
    PIVOT (
        SUM(age) AS a, AVG(class) AS c
        FOR name IN ('John' AS john, 'Mike' AS mike)
    );
+------+-----------+---------+---------+---------+---------+
|  id  |  address  | john_a  | john_c  | mike_a  | mike_c  |
+------+-----------+---------+---------+---------+---------+
| 200  | Street 2  | NULL    | NULL    | NULL    | NULL    |
| 100  | Street 1  | 30      | 1.0     | NULL    | NULL    |
| 300  | Street 3  | NULL    | NULL    | 80      | 3.0     |
| 400  | Street 4  | NULL    | NULL    | NULL    | NULL    |
+------+-----------+---------+---------+---------+---------+



-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 9
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Wed, 13 May 2026 09:29:20 +0000
Gerrit-HasComments: Yes

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 6:

Build failed: https://jenkins.impala.io/job/gerrit-verify-dryrun/13390/


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 6
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Mon, 27 Apr 2026 10:03:33 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Xuebin Su (Code Review)" <ge...@cloudera.org>.
Xuebin Su has uploaded a new patch set (#14). ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................

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
---
M be/src/exprs/aggregate-functions-ir.cc
M be/src/exprs/aggregate-functions.h
M fe/src/main/cup/sql-parser.cup
M fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
M fe/src/main/java/org/apache/impala/analysis/Analyzer.java
M fe/src/main/java/org/apache/impala/analysis/FromClause.java
A fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
M fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java
M fe/src/main/java/org/apache/impala/analysis/TableRef.java
M fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
M fe/src/main/jflex/sql-scanner.flex
M fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
M fe/src/test/java/org/apache/impala/analysis/ParserTest.java
M fe/src/test/java/org/apache/impala/planner/PlannerTest.java
A testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test
A testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
M testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking_and_row_filtering.test
A tests/query_test/test_pivot_clause.py
18 files changed, 1,194 insertions(+), 25 deletions(-)


  git pull ssh://gerrit.cloudera.org:29418/Impala-ASF refs/changes/05/24105/14
-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: newpatchset
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 14
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Xuebin Su (Code Review)" <ge...@cloudera.org>.
Xuebin Su has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 14:

(2 comments)

> Patch Set 9:
> 
> (7 comments)
> 
> I've done the second round of review. The rewrite solution LGTM. I think we just need to fix some special cases like nested PIVOT and add more tests like DMLs. If some special cases are not supported, we need better error messages. 
> 
> Thanks for this hard work!

Thanks for your reviews!

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
File fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java@625
PS9, Line 625:         if (requiresZippingUnnestRewrite()) {
> This also skips DMLs like CreateTableAsSelect, INSERT. I think we should be
Thanks! Added.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
File fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@216
PS9, Line 216: 
> Just tried nested PIVOT, it doesn't work:
Thanks! Fixed.



-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 14
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Fri, 15 May 2026 04:25:31 +0000
Gerrit-HasComments: Yes

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 12: Verified+1


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 12
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Thu, 14 May 2026 16:05:18 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 3:

Build Successful 

https://jenkins.impala.io/job/gerrit-code-review-checks/21956/ : Initial code review checks passed. Use gerrit-verify-dryrun-external or gerrit-verify-dryrun to run full precommit tests.


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 3
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Comment-Date: Tue, 17 Mar 2026 09:38:44 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 7:

Build Successful 

https://jenkins.impala.io/job/gerrit-code-review-checks/22344/ : Initial code review checks passed. Use gerrit-verify-dryrun-external or gerrit-verify-dryrun to run full precommit tests.


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 7
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Thu, 07 May 2026 02:07:08 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 9: Verified-1

Build failed: https://jenkins.impala.io/job/gerrit-verify-dryrun/13467/


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 9
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Wed, 13 May 2026 09:57:24 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Quanlong Huang (Code Review)" <ge...@cloudera.org>.
Quanlong Huang has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 9:

(9 comments)

http://gerrit.cloudera.org:8080/#/c/24105/9/be/src/exprs/aggregate-functions-ir.cc
File be/src/exprs/aggregate-functions-ir.cc:

http://gerrit.cloudera.org:8080/#/c/24105/9/be/src/exprs/aggregate-functions-ir.cc@3287
PS9, Line 3287:   if (!cond.is_null && cond.val) *dst = src;
nit: Please mention this change in the commit message, e.g. when will cond.is_null be true and why we need it for this patch.


http://gerrit.cloudera.org:8080/#/c/24105/9/be/src/exprs/aggregate-functions.h
File be/src/exprs/aggregate-functions.h:

http://gerrit.cloudera.org:8080/#/c/24105/9/be/src/exprs/aggregate-functions.h@399
PS9, Line 399:   /// The predicate must not evaluate to NULL.
nit: We should remove this if it's allowed now.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
File fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java@625
PS9, Line 625:         if (stmt_ instanceof SelectStmt) {
> It'd be more tidy to use a method like requiresSubqueryRewrite(). Then we c
This also skips DMLs like CreateTableAsSelect, INSERT. I think we should be able to support them. If not, let's add an error message.

Please also add tests for DMLs that use PIVOT.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/Analyzer.java
File fe/src/main/java/org/apache/impala/analysis/Analyzer.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/Analyzer.java@47
PS9, Line 47: import org.apache.impala.authorization.AuthorizationChecker;
nit: this and TableMask, MaterializedViewHdfsTable become unused now. Need to remove the imports as well.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
File fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@216
PS9, Line 216: sourceTableSlotPath
Just curious, is it possible that sourceTableRef_ is also a PivotTableRef? Do we have a test case for that.

Another question is should we require 'analyzer' here is the same that we used in analyze() ?


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/TableRef.java
File fe/src/main/java/org/apache/impala/analysis/TableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/TableRef.java@32
PS9, Line 32: import org.apache.impala.authorization.AuthorizationContext;
nit: unused import


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/TableRef.java@45
PS9, Line 45: import org.apache.impala.planner.PlanNode;
nit: unused import


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/TableRef.java@885
PS9, Line 885:   void notifySlotRefRegistered(Analyzer analyzer, SlotDescriptor slot)
nit: might worth a comment here explaining its usage.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/TableRef.java@915
PS9, Line 915:   public TableRef applyTableMask(Analyzer analyzer) throws AnalysisException {
Do you want to implement this for InlineViewRef and CollectionTableRef? Otherwise, it seems we don't need to move it from Analyzer to here. If we do need this, please mention this refactor in the commit message.



-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 9
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Thu, 14 May 2026 04:07:47 +0000
Gerrit-HasComments: Yes

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 14:

Build Successful 

https://jenkins.impala.io/job/gerrit-code-review-checks/22442/ : Initial code review checks passed. Use gerrit-verify-dryrun-external or gerrit-verify-dryrun to run full precommit tests.


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 14
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Fri, 15 May 2026 04:44:59 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Xuebin Su (Code Review)" <ge...@cloudera.org>.
Xuebin Su has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 12:

(19 comments)

> Patch Set 9:
> 
> (7 comments)
> 
> I've done the second round of review. The rewrite solution LGTM. I think we just need to fix some special cases like nested PIVOT and add more tests like DMLs. If some special cases are not supported, we need better error messages. 
> 
> Thanks for this hard work!

Thanks for your reviews!

http://gerrit.cloudera.org:8080/#/c/24105/9/be/src/exprs/aggregate-functions.h
File be/src/exprs/aggregate-functions.h:

http://gerrit.cloudera.org:8080/#/c/24105/9/be/src/exprs/aggregate-functions.h@399
PS9, Line 399:   /// It skips the current row if 'predicate' is null.
> nit: We should remove this if it's allowed now.
Thanks! Removed.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/Analyzer.java
File fe/src/main/java/org/apache/impala/analysis/Analyzer.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/Analyzer.java@47
PS9, Line 47: import org.apache.impala.authorization.AuthorizationConfig;
> nit: this and TableMask, MaterializedViewHdfsTable become unused now. Need 
Thanks! Removed.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
File fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@96
PS3, Line 96:     sourceTableRef_ = sourceTableRef;
> Can we add a test for this?
Thanks! Added.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@147
PS3, Line 147:     Preconditions.checkState(analyzedAggList_.size() >= 1);
> The new patch set still cast the Expr to LiteralExpr which doesn't work for
Thanks! Added an item in the commit message and a test case.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
File fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@21
PS9, Line 21: import java.util.HashSet;
> nit: unused import
Thanks! Removed.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@73
PS9, Line 73:   // Maps each alias to the Agg Expr in the PIVOT clause.
            :   private final Map<String, FunctionCallExpr> analyzedAggList_ = new LinkedHashMap<>();
            : 
> nit: Let's add a comment mentioning what are the keys of these maps, e.g. a
Thanks! Added.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@277
PS9, Line 277: 
> nit: at first glance, it seems all items use the same alias. But later I re
Thanks! Added.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@291
PS9, Line 291:           new SelectListItem(aggItem.getExpr().clone(), singleAggAlias));
> nit: not used
Thanks! Removed.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@298
PS9, Line 298:         new FromClause(Lists.newArrayList(sourceTableRef)), null,
             :         new GroupByClause(groupingExprs, GroupingSetsType.NONE), null, null, null);
             : 
> Oracle iterates the header values before the agg alias: https://docs.oracle
Thanks! Changed.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/TableRef.java
File fe/src/main/java/org/apache/impala/analysis/TableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/TableRef.java@32
PS9, Line 32: import org.apache.impala.authorization.Privilege;
> nit: unused import
Thanks! Removed.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/TableRef.java@45
PS9, Line 45: import org.apache.impala.rewrite.ExprRewriter;
> nit: unused import
Thanks! Removed.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/TableRef.java@885
PS9, Line 885:   // Analyzer.registerSlotRef(). It is designed to be overridden by the subclasses.
> nit: might worth a comment here explaining its usage.
Thanks! Added.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/TableRef.java@915
PS9, Line 915:     other.aliases_ = aliases_;
> Do you want to implement this for InlineViewRef and CollectionTableRef? Oth
Thanks! This refactor is mentioned in https://gerrit.cloudera.org/c/23922/ .


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
File fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java@450
PS9, Line 450:     );
> Let's also test multiple aggs in this scenario.
Thanks! Added.


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java@467
PS9, Line 467:         "Duplicate alias for header values in the PIVOT clause: v1"
> Let's add a test for "Duplicate alias for aggregation in the PIVOT clause".
Thanks! Added.


http://gerrit.cloudera.org:8080/#/c/24105/9/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
File testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test:

http://gerrit.cloudera.org:8080/#/c/24105/9/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test@6
PS9, Line 6: min(id) for month in
> Lots of tests use the header column in agg, e.g. "min(month) for month", wh
Thanks! Added a test case.


http://gerrit.cloudera.org:8080/#/c/24105/9/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test@21
PS9, Line 21: ---- RESULTS
> Should be
Thanks! Changed.


http://gerrit.cloudera.org:8080/#/c/24105/9/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test@155
PS9, Line 155: with t1 (a, b) as (
> The above tests use inline views. Let's add some tests for local views like
Thanks! Added.


http://gerrit.cloudera.org:8080/#/c/24105/9/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test@329
PS9, Line 329: )
> Let's test the HAVING clause, e.g.
Thanks! Added.



-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 12
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Thu, 14 May 2026 11:39:41 +0000
Gerrit-HasComments: Yes

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Quanlong Huang (Code Review)" <ge...@cloudera.org>.
Quanlong Huang has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 9:

(7 comments)

I've done the second round of review. The rewrite solution LGTM. I think we just need to fix some special cases like nested PIVOT and add more tests like DMLs. If some special cases are not supported, we need better error messages. 

Thanks for this hard work!

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
File fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@216
PS9, Line 216: sourceTableSlotPath
> Just curious, is it possible that sourceTableRef_ is also a PivotTableRef? 
Just tried nested PIVOT, it doesn't work:

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;
RuntimeException: java.lang.IllegalStateException
CAUSED BY: IllegalStateException: null


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
File fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java:

http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java@450
PS9, Line 450:     );
Let's also test multiple aggs in this scenario.

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"
);


http://gerrit.cloudera.org:8080/#/c/24105/9/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java@467
PS9, Line 467:     );
Let's add a test for "Duplicate alias for aggregation in the PIVOT clause".


http://gerrit.cloudera.org:8080/#/c/24105/9/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
File testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test:

http://gerrit.cloudera.org:8080/#/c/24105/9/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test@6
PS9, Line 6: min(month) for month
Lots of tests use the header column in agg, e.g. "min(month) for month", which is not that easy to understand the purpose for people that are new to this feature. It'd be better to use non-header column in agg, e.g. min(id), so the agg function actaully aggregates multiple values.


http://gerrit.cloudera.org:8080/#/c/24105/9/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test@21
PS9, Line 21: ==== QUERY
Should be
 
====
---- Query


http://gerrit.cloudera.org:8080/#/c/24105/9/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test@155
PS9, Line 155: ====
The above tests use inline views. Let's add some tests for local views like functional.alltypes_view. E.g.

 select year, m5, m6 from functional.alltypes_view pivot( count(id) for month in (5 as m5, 6 as m6)) t;


http://gerrit.cloudera.org:8080/#/c/24105/9/testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test@329
PS9, Line 329: ====
Let's test the HAVING clause, e.g.

select year, m5, m6
from functional.alltypes pivot(
  max(id) for month in (5 as m5, 6 as m6)) t
having m5 > 2000;



-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 9
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Thu, 14 May 2026 07:11:55 +0000
Gerrit-HasComments: Yes

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 14:

Build started: https://jenkins.impala.io/job/gerrit-verify-dryrun/13497/ DRY_RUN=true


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 14
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Fri, 15 May 2026 04:28:38 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Quanlong Huang (Code Review)" <ge...@cloudera.org>.
Quanlong Huang has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 14: Code-Review+2


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 14
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Fri, 15 May 2026 07:56:50 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Xuebin Su (Code Review)" <ge...@cloudera.org>.
Xuebin Su has uploaded a new patch set (#3). ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................

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 `UnpivotTableRef.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 translated
  into a `MultiAggregateInfo`.
- Finally, in the planning phase, `AggregationNode`s will be created
  from the `MultiAggregateInfo`.

No new plan node or new execution node is added.

Testing:
- Added E2E tests in test_pivot_clause.py
- Added FE tests to ensure that predicates can be pushed down to the
  SCAN nodes and the result table of PIVOT can be broadcast for JOIN.

Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
---
M be/src/exprs/aggregate-functions-ir.cc
M fe/src/main/cup/sql-parser.cup
M fe/src/main/java/org/apache/impala/analysis/AggregateInfo.java
M fe/src/main/java/org/apache/impala/analysis/Analyzer.java
M fe/src/main/java/org/apache/impala/analysis/ExprSubstitutionMap.java
M fe/src/main/java/org/apache/impala/analysis/FromClause.java
M fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java
A fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
M fe/src/main/java/org/apache/impala/analysis/QueryStmt.java
M fe/src/main/java/org/apache/impala/analysis/SelectStmt.java
M fe/src/main/java/org/apache/impala/analysis/SetOperationStmt.java
M fe/src/main/java/org/apache/impala/analysis/TableRef.java
M fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
M fe/src/main/java/org/apache/impala/planner/AggregationNode.java
M fe/src/main/java/org/apache/impala/planner/SingleNodePlanner.java
M fe/src/main/jflex/sql-scanner.flex
M fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
M fe/src/test/java/org/apache/impala/planner/PlannerTest.java
A testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test
A testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
A tests/query_test/test_pivot_clause.py
21 files changed, 1,064 insertions(+), 52 deletions(-)


  git pull ssh://gerrit.cloudera.org:29418/Impala-ASF refs/changes/05/24105/3
-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: newpatchset
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 3
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 2:

Build Successful 

https://jenkins.impala.io/job/gerrit-code-review-checks/21954/ : Initial code review checks passed. Use gerrit-verify-dryrun-external or gerrit-verify-dryrun to run full precommit tests.


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 2
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Comment-Date: Tue, 17 Mar 2026 09:22:02 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Quanlong Huang (Code Review)" <ge...@cloudera.org>.
Quanlong Huang has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 3:

(16 comments)

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/cup/sql-parser.cup
File fe/src/main/cup/sql-parser.cup:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/cup/sql-parser.cup@3525
PS3, Line 3525: ident_or_unreserved
I think we need dotted_path for header_column to support nested fields, e.g.,

  select * from functional_parquet.complextypes_structs
  pivot (count(*) for alltypes.ti in (90, 100, 123, 127));


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/cup/sql-parser.cup@3524
PS3, Line 3524:   | dotted_path:path KW_PIVOT LPAREN function_call_expr:aggregation KW_FOR
              :     ident_or_unreserved:header_column KW_IN
              :     LPAREN select_list:header_value_list RPAREN RPAREN alias_clause:alias
I think this doesn't support multiple aggregates or multiple pivot columns.
https://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#sthref1224
https://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#sthref1223

Let's mention this in the commit message.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/AggregateInfo.java
File fe/src/main/java/org/apache/impala/analysis/AggregateInfo.java:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/AggregateInfo.java@110
PS3, Line 110:       
nit: use 4 spaces indent


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/AggregateInfo.java@156
PS3, Line 156:       boolean isForPivoting) throws AnalysisException {
nit: our code style prefers putting the parameters in less lines.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/FromClause.java
File fe/src/main/java/org/apache/impala/analysis/FromClause.java:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/FromClause.java@197
PS3, Line 197:         set(i, origTblRef.clone());
nit: can we add a comment for using clone() for reset?


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java
File fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java@592
PS3, Line 592:     int numAggs = aggTuple.getSlots().size() - numGroupingExprs;
nit: not used


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java@598
PS3, Line 598:           Operator.EQ, headerSlotRef, e.getValue().clone());
I think it won't be hard to support pivoting on multiple columns. We just need CompoundPredicates to connect all the EQ predicates.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
File fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@62
PS3, Line 62:   private final LinkedHashMap<String, LiteralExpr> headerValueMap_ =
nit: please add a comment about using LinkedHashMap to perserve the order of header values.
nit: We prefer declaring the variable using interface type Map, e.g., in Analyzer:

  public final Map<ExprId, Expr> conjuncts = new LinkedHashMap<>();


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@96
PS3, Line 96:       Expr aggArg = aggExpr.getParams().exprs().get(0);
Aggregation functions could have more than one parameter, e.g., group_concat(col, separator). We need to clone all the params.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@147
PS3, Line 147:       LiteralExpr e = (LiteralExpr)item.getExpr();
It's not guaranteed that these are literals. I think exprs that have constant results also worth to support, e.g., month(now()), 1 + 1, etc. For non-constant exprs, we should throw AnalysisExceptions.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@206
PS3, Line 206:   void notifySlotRefRegistered(Analyzer analyzer, SlotDescriptor slot)
nit: add a @Override annotation


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@209
PS3, Line 209:     String columnName = rawPath.get(0);
This might loss the field names for nested columns. I see an exception that might due to this:

  set EXPAND_COMPLEX_TYPES=true;
  select * from functional_parquet.complextypes_structs
  pivot (count(*) for str in ('first item', 'second item')) t;

I20260324 10:25:42.164049 271305 jni-util.cc:335] 9046ddbdbe70501a:97c1513a00000000] java.lang.RuntimeException: java.lang.IllegalStateException
        at org.apache.impala.service.Frontend.getTExecRequestWithFallback(Frontend.java:2459)
        at org.apache.impala.service.Frontend.createExecRequest(Frontend.java:2126)
        at org.apache.impala.service.JniFrontend.createExecRequest(JniFrontend.java:175)
Caused by: java.lang.IllegalStateException
        at com.google.common.base.Preconditions.checkState(Preconditions.java:496)
        at org.apache.impala.analysis.SlotRef.addStructChildrenAsSlotRefs(SlotRef.java:264)
        at org.apache.impala.analysis.SlotRef.<init>(SlotRef.java:93)
        at org.apache.impala.analysis.AggregateInfoBase.createTupleDesc(AggregateInfoBase.java:135)
        at org.apache.impala.analysis.AggregateInfoBase.createTupleDescs(AggregateInfoBase.java:101)
        at org.apache.impala.analysis.AggregateInfo.create(AggregateInfo.java:173)
        at org.apache.impala.analysis.AggregateInfo.create(AggregateInfo.java:202)
        at org.apache.impala.analysis.MultiAggregateInfo.groupAggExprs(MultiAggregateInfo.java:318)
        at org.apache.impala.analysis.MultiAggregateInfo.analyzeForPivoting(MultiAggregateInfo.java:559)
        at org.apache.impala.analysis.PivotTableRef.createMultiAggregateInfo(PivotTableRef.java:251)
        at org.apache.impala.analysis.SelectStmt$SelectAnalyzer.combineBaseTableSmaps(SelectStmt.java:841)
        at org.apache.impala.analysis.SelectStmt$SelectAnalyzer.analyze(SelectStmt.java:369)
        at org.apache.impala.analysis.SelectStmt.analyze(SelectStmt.java:277)
        at org.apache.impala.analysis.AnalysisContext$AnalysisDriverImpl.analyze(AnalysisContext.java:565)
        at org.apache.impala.analysis.AnalysisContext.analyzeAndAuthorize(AnalysisContext.java:496)
        at org.apache.impala.service.Frontend.doCreateExecRequest(Frontend.java:3033)
        at org.apache.impala.service.Frontend.getTExecRequest(Frontend.java:2590)
        at org.apache.impala.service.Frontend.getTExecRequestWithFallback(Frontend.java:2436)
        ... 2 more


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@258
PS3, Line 258:     } catch (ClassCastException e) {
Using ClassCastException for type checking is generally discouraged in Java. Please use instanceof.


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@300
PS3, Line 300: .toString()
nit: Unnecessary 'toString()' call


http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
File fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java:

http://gerrit.cloudera.org:8080/#/c/24105/3/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java@455
PS3, Line 455:     );
Let's add another test for "month in (1, 2 as `1`)"


http://gerrit.cloudera.org:8080/#/c/24105/3/testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking.test
File testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking.test:

http://gerrit.cloudera.org:8080/#/c/24105/3/testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking.test@994
PS3, Line 994: ====
Let's add some tests in ranger_column_masking.test and ranger_row_filtering.test to make sure this feature works with Ranger column-masking/row-filtering.



-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 3
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Comment-Date: Tue, 24 Mar 2026 05:51:57 +0000
Gerrit-HasComments: Yes

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 6:

Build Failed 

https://jenkins.impala.io/job/gerrit-code-review-checks/22245/ : Initial code review checks failed. See linked job for details on the failure.


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 6
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Mon, 27 Apr 2026 07:52:03 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 12:

Build Successful 

https://jenkins.impala.io/job/gerrit-code-review-checks/22425/ : Initial code review checks passed. Use gerrit-verify-dryrun-external or gerrit-verify-dryrun to run full precommit tests.


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 12
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Thu, 14 May 2026 10:15:07 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Xuebin Su (Code Review)" <ge...@cloudera.org>.
Xuebin Su has uploaded a new patch set (#5). ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................

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.

Limitations:
- Specifying multiple header columns in the PIVOT clause is not
  supported in this patch.

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
---
M be/src/exprs/aggregate-functions-ir.cc
M fe/src/main/cup/sql-parser.cup
M fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
M fe/src/main/java/org/apache/impala/analysis/Analyzer.java
M fe/src/main/java/org/apache/impala/analysis/FromClause.java
A fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
M fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java
M fe/src/main/java/org/apache/impala/analysis/TableRef.java
M fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
M fe/src/main/jflex/sql-scanner.flex
M fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
M fe/src/test/java/org/apache/impala/planner/PlannerTest.java
A testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test
A testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
A tests/query_test/test_pivot_clause.py
15 files changed, 996 insertions(+), 11 deletions(-)


  git pull ssh://gerrit.cloudera.org:29418/Impala-ASF refs/changes/05/24105/5
-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: newpatchset
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 5
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 2:

(3 comments)

gerrit-auto-critic failed. You can reproduce it locally using command:

  python3 bin/jenkins/critique-gerrit-review.py --dryrun

To run it, you might need a virtual env with Python3's venv installed.

http://gerrit.cloudera.org:8080/#/c/24105/2/fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java
File fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java:

http://gerrit.cloudera.org:8080/#/c/24105/2/fe/src/main/java/org/apache/impala/analysis/MultiAggregateInfo.java@561
PS2, Line 561:         createTransposeAggInfoForPivoting(analyzer, pivotHeaderSlotRefIndex_, headerValueMap);
line too long (94 > 90)


http://gerrit.cloudera.org:8080/#/c/24105/2/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
File fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/2/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@183
PS2, Line 183:             "The argument of the aggregate in the PIVOT clause should be a path or a literal");
line too long (95 > 90)


http://gerrit.cloudera.org:8080/#/c/24105/2/tests/query_test/test_pivot_clause.py
File tests/query_test/test_pivot_clause.py:

http://gerrit.cloudera.org:8080/#/c/24105/2/tests/query_test/test_pivot_clause.py@27
PS2, Line 27: class TestPivotClause(ImpalaTestSuite):
flake8: E302 expected 2 blank lines, found 1



-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 2
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Comment-Date: Tue, 17 Mar 2026 08:54:06 +0000
Gerrit-HasComments: Yes

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 9:

Build started: https://jenkins.impala.io/job/gerrit-verify-dryrun/13467/ DRY_RUN=true


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 9
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Wed, 13 May 2026 05:32:17 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Xuebin Su (Code Review)" <ge...@cloudera.org>.
Xuebin Su has uploaded a new patch set (#11). ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................

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.

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
---
M be/src/exprs/aggregate-functions-ir.cc
M be/src/exprs/aggregate-functions.h
M fe/src/main/cup/sql-parser.cup
M fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
M fe/src/main/java/org/apache/impala/analysis/Analyzer.java
M fe/src/main/java/org/apache/impala/analysis/FromClause.java
A fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
M fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java
M fe/src/main/java/org/apache/impala/analysis/TableRef.java
M fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
M fe/src/main/jflex/sql-scanner.flex
M fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
M fe/src/test/java/org/apache/impala/analysis/ParserTest.java
M fe/src/test/java/org/apache/impala/planner/PlannerTest.java
A testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test
A testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
M testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking_and_row_filtering.test
A tests/query_test/test_pivot_clause.py
18 files changed, 1,132 insertions(+), 25 deletions(-)


  git pull ssh://gerrit.cloudera.org:29418/Impala-ASF refs/changes/05/24105/11
-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: newpatchset
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 11
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 11:

(1 comment)

gerrit-auto-critic failed. You can reproduce it locally using command:

  python3 bin/jenkins/critique-gerrit-review.py --dryrun

To run it, you might need a virtual env with Python3's venv installed.

http://gerrit.cloudera.org:8080/#/c/24105/11/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
File fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java:

http://gerrit.cloudera.org:8080/#/c/24105/11/fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java@313
PS11, Line 313:         String aggAlias = aggItem.getAlias() == null ? singleAggAlias : aggItem.getAlias();
line too long (91 > 90)



-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 11
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Thu, 14 May 2026 09:47:00 +0000
Gerrit-HasComments: Yes

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Xuebin Su (Code Review)" <ge...@cloudera.org>.
Xuebin Su has uploaded a new patch set (#7). ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................

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.

Limitations:
- Multiple header columns in the PIVOT clause 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
---
M be/src/exprs/aggregate-functions-ir.cc
M fe/src/main/cup/sql-parser.cup
M fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
M fe/src/main/java/org/apache/impala/analysis/Analyzer.java
M fe/src/main/java/org/apache/impala/analysis/FromClause.java
M fe/src/main/java/org/apache/impala/analysis/InlineViewRef.java
A fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
M fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java
M fe/src/main/java/org/apache/impala/analysis/TableRef.java
M fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
M fe/src/main/jflex/sql-scanner.flex
M fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
M fe/src/test/java/org/apache/impala/planner/PlannerTest.java
A testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test
A testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
M testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking_and_row_filtering.test
A tests/query_test/test_pivot_clause.py
17 files changed, 1,144 insertions(+), 88 deletions(-)


  git pull ssh://gerrit.cloudera.org:29418/Impala-ASF refs/changes/05/24105/7
-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: newpatchset
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 7
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 6:

Build started: https://jenkins.impala.io/job/gerrit-verify-dryrun/13389/ DRY_RUN=true


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 6
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Mon, 27 Apr 2026 07:52:01 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 6:

Build started: https://jenkins.impala.io/job/gerrit-verify-dryrun/13390/ DRY_RUN=true


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 6
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Mon, 27 Apr 2026 08:09:24 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Impala Public Jenkins (Code Review)" <ge...@cloudera.org>.
Impala Public Jenkins has posted comments on this change. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................


Patch Set 12:

Build started: https://jenkins.impala.io/job/gerrit-verify-dryrun/13488/ DRY_RUN=true


-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: comment
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 12
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>
Gerrit-Comment-Date: Thu, 14 May 2026 11:44:36 +0000
Gerrit-HasComments: No

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Xuebin Su (Code Review)" <ge...@cloudera.org>.
Xuebin Su has uploaded a new patch set (#12). ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................

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.

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
---
M be/src/exprs/aggregate-functions-ir.cc
M be/src/exprs/aggregate-functions.h
M fe/src/main/cup/sql-parser.cup
M fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
M fe/src/main/java/org/apache/impala/analysis/Analyzer.java
M fe/src/main/java/org/apache/impala/analysis/FromClause.java
A fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
M fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java
M fe/src/main/java/org/apache/impala/analysis/TableRef.java
M fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
M fe/src/main/jflex/sql-scanner.flex
M fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
M fe/src/test/java/org/apache/impala/analysis/ParserTest.java
M fe/src/test/java/org/apache/impala/planner/PlannerTest.java
A testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test
A testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
M testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking_and_row_filtering.test
A tests/query_test/test_pivot_clause.py
18 files changed, 1,133 insertions(+), 25 deletions(-)


  git pull ssh://gerrit.cloudera.org:29418/Impala-ASF refs/changes/05/24105/12
-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: newpatchset
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 12
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>

[Impala-ASF-CR] IMPALA-2083: Add PIVOT clause

Posted by "Quanlong Huang (Code Review)" <ge...@cloudera.org>.
Quanlong Huang has submitted this change and it was merged. ( http://gerrit.cloudera.org:8080/24105 )

Change subject: IMPALA-2083: Add PIVOT clause
......................................................................

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>
---
M be/src/exprs/aggregate-functions-ir.cc
M be/src/exprs/aggregate-functions.h
M fe/src/main/cup/sql-parser.cup
M fe/src/main/java/org/apache/impala/analysis/AnalysisContext.java
M fe/src/main/java/org/apache/impala/analysis/Analyzer.java
M fe/src/main/java/org/apache/impala/analysis/FromClause.java
A fe/src/main/java/org/apache/impala/analysis/PivotTableRef.java
M fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java
M fe/src/main/java/org/apache/impala/analysis/TableRef.java
M fe/src/main/java/org/apache/impala/catalog/BuiltinsDb.java
M fe/src/main/jflex/sql-scanner.flex
M fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
M fe/src/test/java/org/apache/impala/analysis/ParserTest.java
M fe/src/test/java/org/apache/impala/planner/PlannerTest.java
A testdata/workloads/functional-planner/queries/PlannerTest/pivot-clause.test
A testdata/workloads/functional-query/queries/QueryTest/pivot-clause.test
M testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking_and_row_filtering.test
A tests/query_test/test_pivot_clause.py
18 files changed, 1,194 insertions(+), 25 deletions(-)

Approvals:
  Quanlong Huang: Looks good to me, approved
  Impala Public Jenkins: Verified

-- 
To view, visit http://gerrit.cloudera.org:8080/24105
To unsubscribe, visit http://gerrit.cloudera.org:8080/settings

Gerrit-Project: Impala-ASF
Gerrit-Branch: master
Gerrit-MessageType: merged
Gerrit-Change-Id: Ib799b772be18d2a3db8983b24e1068e7dfdf1ca9
Gerrit-Change-Number: 24105
Gerrit-PatchSet: 15
Gerrit-Owner: Xuebin Su <xs...@cloudera.com>
Gerrit-Reviewer: Impala Public Jenkins <im...@cloudera.com>
Gerrit-Reviewer: Quanlong Huang <hu...@gmail.com>
Gerrit-Reviewer: Xuebin Su <xs...@cloudera.com>