This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 500272d33ab Refactor pivot and unpivot clause handling to support
multiple column… (#35586)
500272d33ab is described below
commit 500272d33ab9a34524e7a55bb09d91bdfb712eb1
Author: Cong Hu <iamhuc...@gmail.com>
AuthorDate: Thu Jun 5 11:31:16 2025 +0800
Refactor pivot and unpivot clause handling to support multiple column…
(#35586)
* Refactor pivot and unpivot clause handling to support multiple column
names.
* Add release note.
---
RELEASE-NOTES.md | 1 +
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 6 +--
.../statement/type/OracleDMLStatementVisitor.java | 12 ++---
.../core/segment/generic/PivotSegment.java | 14 +++---
.../resources/case/dml/select-special-function.xml | 58 +++++++++++++++-------
.../sql/supported/dml/select-special-function.xml | 3 +-
6 files changed, 57 insertions(+), 37 deletions(-)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index c9464bcf7b2..098578d0fbd 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -61,6 +61,7 @@
1. JDBC: Support set datasource properties type with java.time.Duration -
[#35241](https://github.com/apache/shardingsphere/pull/35241)
1. Proxy: Fix `show processlist` not wait for all nodes -
[#35348](https://github.com/apache/shardingsphere/pull/35348)
1. Proxy: Fix NoSuchElementException exception when execute MySQL SHOW
VARIABLES without current database -
[#35550](https://github.com/apache/shardingsphere/pull/35550)
+1. Parser: Refactor pivot and unpivot clause handling to support multiple
column names - [35586](https://github.com/apache/shardingsphere/pull/35586)
### Change Logs
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
index 59969afcd49..13d20929af4 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
@@ -520,7 +520,7 @@ pivotClause
;
pivotForClause
- : FOR (columnName | columnNames)
+ : FOR columnNames
;
pivotInClause
@@ -534,7 +534,7 @@ pivotInClauseExpr
;
unpivotClause
- : UNPIVOT ((INCLUDE | EXCLUDE) NULLS)? LP_ (columnName | columnNames)
pivotForClause unpivotInClause RP_
+ : UNPIVOT ((INCLUDE | EXCLUDE) NULLS)? LP_ columnNames pivotForClause
unpivotInClause RP_
;
unpivotInClause
@@ -542,7 +542,7 @@ unpivotInClause
;
unpivotInClauseExpr
- : (columnName | columnNames) (AS (literals | LP_ literals (COMMA_
literals)* RP_))?
+ : columnNames (AS (literals | LP_ literals (COMMA_ literals)* RP_))?
;
sampleClause
diff --git
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
index 5ae50e06c18..fbdab5e52f7 100644
---
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
+++
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
@@ -474,7 +474,6 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
@Override
public ASTNode visitPivotClause(final PivotClauseContext ctx) {
- ColumnSegment pivotForColumn = (ColumnSegment)
visitColumnName(ctx.pivotForClause().columnName());
Collection<ColumnSegment> pivotInColumns = new LinkedList<>();
if (null != ctx.pivotInClause()) {
ctx.pivotInClause().pivotInClauseExpr().forEach(each -> {
@@ -484,19 +483,18 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
pivotInColumns.add(columnSegment);
});
}
- return new PivotSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), pivotForColumn, pivotInColumns);
+ return new PivotSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), ((CollectionValue<ColumnSegment>)
visit(ctx.pivotForClause().columnNames())).getValue(), pivotInColumns);
}
@Override
public ASTNode visitUnpivotClause(final UnpivotClauseContext ctx) {
- ColumnSegment unpivotColumn = (ColumnSegment)
visitColumnName(ctx.columnName());
- ColumnSegment unpivotForColumn = (ColumnSegment)
visitColumnName(ctx.pivotForClause().columnName());
Collection<ColumnSegment> unpivotInColumns = new LinkedList<>();
if (null != ctx.unpivotInClause()) {
- ctx.unpivotInClause().unpivotInClauseExpr().forEach(each ->
unpivotInColumns.add((ColumnSegment) visit(each.columnName())));
+ ctx.unpivotInClause().unpivotInClauseExpr().forEach(each ->
unpivotInColumns.addAll(((CollectionValue<ColumnSegment>)
visit(ctx.columnNames())).getValue()));
}
- PivotSegment result = new PivotSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), unpivotForColumn, unpivotInColumns, true);
- result.setUnpivotColumn(unpivotColumn);
+ PivotSegment result = new PivotSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), ((CollectionValue<ColumnSegment>)
visit(ctx.pivotForClause().columnNames())).getValue(),
+ unpivotInColumns, true);
+ result.setUnpivotColumns(((CollectionValue<ColumnSegment>)
visit(ctx.columnNames())).getValue());
return result;
}
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/generic/PivotSegment.java
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/generic/PivotSegment.java
index 649e010c1a6..8b2f8f06bbf 100644
---
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/generic/PivotSegment.java
+++
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/generic/PivotSegment.java
@@ -37,19 +37,19 @@ public final class PivotSegment implements SQLSegment {
private final int stopIndex;
- private final ColumnSegment pivotForColumn;
+ private final Collection<ColumnSegment> pivotForColumns;
private final Collection<ColumnSegment> pivotInColumns;
private final boolean isUnPivot;
@Setter
- private ColumnSegment unpivotColumn;
+ private Collection<ColumnSegment> unpivotColumns;
- public PivotSegment(final int startIndex, final int stopIndex, final
ColumnSegment pivotForColumn, final Collection<ColumnSegment> pivotInColumns) {
+ public PivotSegment(final int startIndex, final int stopIndex, final
Collection<ColumnSegment> pivotForColumns, final Collection<ColumnSegment>
pivotInColumns) {
this.startIndex = startIndex;
this.stopIndex = stopIndex;
- this.pivotForColumn = pivotForColumn;
+ this.pivotForColumns = pivotForColumns;
this.pivotInColumns = pivotInColumns;
isUnPivot = false;
}
@@ -61,9 +61,9 @@ public final class PivotSegment implements SQLSegment {
*/
public Collection<ColumnSegment> getPivotColumns() {
Collection<ColumnSegment> result = new HashSet<>(pivotInColumns);
- result.add(pivotForColumn);
- if (null != unpivotColumn) {
- result.add(unpivotColumn);
+ result.addAll(pivotForColumns);
+ if (null != unpivotColumns) {
+ result.addAll(unpivotColumns);
}
return result;
}
diff --git
a/test/it/parser/src/main/resources/case/dml/select-special-function.xml
b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
index 545700756ab..9ddab502ac4 100644
--- a/test/it/parser/src/main/resources/case/dml/select-special-function.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
@@ -695,6 +695,26 @@
</from>
</select>
+ <select sql-case-id="select_unpivot">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7"/>
+ </projections>
+ <from>
+ <subquery-table start-index="14" stop-index="34" >
+ <subquery>
+ <select>
+ <projections start-index="22" stop-index="22">
+ <shorthand-projection start-index="22"
stop-index="22"/>
+ </projections>
+ <from start-index="29" stop-index="33">
+ <simple-table name="sales" start-index="29"
stop-index="33"/>
+ </from>
+ </select>
+ </subquery>
+ </subquery-table>
+ </from>
+ </select>
+
<select sql-case-id="select_string_split_function">
<from>
<join-table join-type="CROSS">
@@ -818,7 +838,7 @@
</function-table>
</from>
</select>
-
+
<select sql-case-id="select_json_object_simple_key_value">
<projections start-index="7" stop-index="43">
<expression-projection start-index="7" stop-index="43"
text="JSON_OBJECT('name':'value', 'type':1)">
@@ -849,7 +869,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_nest_json_object">
<projections start-index="7" stop-index="78">
<expression-projection text="JSON_OBJECT('name':'value',
'type':JSON_OBJECT('type_id':1, 'name':'a'))" start-index="7" stop-index="78">
@@ -901,7 +921,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_json_object_with_json_array">
<projections start-index="7" stop-index="58">
<expression-projection text="JSON_OBJECT('name':'value',
'type':JSON_ARRAY(1, 2))" start-index="7" stop-index="58">
@@ -1205,7 +1225,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_json_object_absent_not_null">
<projections start-index="7" stop-index="61">
<expression-projection text="JSON_OBJECT('name':'value',
'type':NULL ABSENT ON NULL)" start-index="7" stop-index="61">
@@ -1239,7 +1259,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_json_object_with_subquery">
<projections start-index="7" stop-index="84">
<expression-projection text="JSON_OBJECT('user_name':USER_NAME(),
@id_key:@id_value, 'sid':(SELECT @@SPID))" start-index="7" stop-index="84">
@@ -1286,7 +1306,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_dm_exec_sessions_with_json_object_function">
<projections start-index="7" stop-index="109">
<column-projection name="session_id" start-index="7"
stop-index="18">
@@ -1356,7 +1376,7 @@
</expr>
</where>
</select>
-
+
<select sql-case-id="select_first_last_value_function">
<projections start-index="7" stop-index="493">
<column-projection name="BusinessEntityID" start-index="7"
stop-index="22" />
@@ -1464,7 +1484,7 @@
<column-item name="Quarter" order-direction="ASC"
start-index="638" stop-index="644" />
</order-by>
</select>
-
+
<select sql-case-id="select_approx_percentile_cont_function">
<projections start-index="7" stop-index="148">
<column-projection start-index="7" stop-index="12" name="DeptId" />
@@ -1500,7 +1520,7 @@
<column-item name="DeptId" order-direction="ASC" start-index="176"
stop-index="181" />
</group-by>
</select>
-
+
<select sql-case-id="select_wm_concat_function_with_schema">
<projections start-index="7" stop-index="49">
<expression-projection start-index="7" stop-index="49"
text="TO_CHAR(WMSYS.WM_CONCAT(DISTINCT o.status))">
@@ -1611,7 +1631,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_bin">
<projections start-index="7" stop-index="13">
<expression-projection start-index="7" stop-index="13"
text="BIN(12)">
@@ -1625,7 +1645,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_bin_uuid">
<projections start-index="7" stop-index="70">
<expression-projection start-index="7" stop-index="70"
text="BIN_TO_UUID(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'))">
@@ -1643,7 +1663,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_bit_length">
<projections start-index="7" stop-index="19">
<expression-projection start-index="7" stop-index="19"
text="BIT_LENGTH(1)">
@@ -1657,7 +1677,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_bit_count">
<projections start-index="7" stop-index="18">
<expression-projection start-index="7" stop-index="18"
text="BIT_COUNT(1)">
@@ -1671,7 +1691,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_ceil">
<projections start-index="7" stop-index="15">
<expression-projection start-index="7" stop-index="15"
text="CEIL(1.1)">
@@ -1699,7 +1719,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_with_uuid_function">
<projections start-index="7" stop-index="12">
<expression-projection text="UUID()" start-index="7"
stop-index="12">
@@ -1788,7 +1808,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_with_variance_function">
<from>
<simple-table name="sales_view" start-index="29" stop-index="40"
alias="s" />
@@ -4887,7 +4907,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_lower_function">
<projections start-index="7" stop-index="28">
<expression-projection start-index="7" stop-index="28"
text="LOWER('QUADRATICALLY')">
@@ -4901,7 +4921,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_length">
<projections start-index="7" stop-index="20">
<expression-projection start-index="7" stop-index="20"
text="LENGTH('TEXT')">
@@ -4915,7 +4935,7 @@
</expression-projection>
</projections>
</select>
-
+
<select sql-case-id="select_locate">
<projections start-index="7" stop-index="31">
<expression-projection start-index="7" stop-index="31"
text="LOCATE('bar','foobarbar')">
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
index 261edbf18b1..b8d1fe8437e 100644
---
a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
+++
b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
@@ -48,7 +48,8 @@
<sql-case id="select_mod_function" value="SELECT MOD(order_id, 1) from
t_order" db-types="PostgreSQL,openGauss" />
<sql-case id="select_sys_xml_agg" value="SELECT
SYS_XMLAGG(SYS_XMLGEN(last_name)) XMLAGG FROM employees WHERE last_name LIKE
'R%' ORDER BY xmlagg;" db-types="Oracle" />
<sql-case id="select_set_function" value="SELECT customer_id,
SET(cust_address_ntab) address FROM customers_demo ORDER BY customer_id;"
db-types="Oracle" />
- <sql-case id="select_pivot" value="SELECT * FROM (SELECT * FROM sales)
PIVOT (SUM(amount) FOR month IN ('Jan', 'Feb'))" db-types="Oracle"/>
+ <sql-case id="select_pivot" value="SELECT * FROM (SELECT * FROM sales)
PIVOT (SUM(amount) FOR (year, month) IN ((2018, 'Jan'), (2018, 'Feb')))"
db-types="Oracle"/>
+ <sql-case id="select_unpivot" value="SELECT * FROM (SELECT * FROM sales)
UNPIVOT ((cert_type, cert_number) FOR cert_source IN ((primary_cert_type,
primary_cert_no), (alt_cert_type1, alt_cert_no1), (alt_cert_type2,
alt_cert_no2)))" db-types="Oracle"/>
<sql-case id="select_string_split_function" value="SELECT value as tag,
COUNT(*) AS [number_of_articles] FROM Product CROSS APPLY STRING_SPLIT(Tags,
',') GROUP BY value HAVING COUNT(*) > 2 ORDER BY COUNT(*) DESC"
db-types="SQLServer"/>
<sql-case id="select_from_open_json_function" value="SELECT * FROM
OPENJSON(@array) WITH ( month VARCHAR(3), temp int, month_id tinyint
'$.sql:identity()') as months" db-types="SQLServer"/>
<sql-case id="select_from_open_json_function_with_path" value="SELECT
[key], value FROM OPENJSON(@json,'$.path.to."sub-object"')"
db-types="SQLServer"/>
