This is an automated email from the ASF dual-hosted git repository.
zhaojinchao 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 769b1f5a365 Optimize Oracle support select pivot. (#28337)
769b1f5a365 is described below
commit 769b1f5a36505a48a266ca601483b1273701e8ea
Author: Cong Hu <[email protected]>
AuthorDate: Mon Sep 4 11:33:11 2023 +0800
Optimize Oracle support select pivot. (#28337)
* Optimize Oracle support select pivot.
* Optimize Oracle support select pivot.
---
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 2 +-
.../resources/case/dml/select-special-function.xml | 20 ++++++++++++++++++++
.../sql/supported/dml/select-special-function.xml | 1 +
3 files changed, 22 insertions(+), 1 deletion(-)
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 17342ab5f54..6d20e9d578c 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
@@ -136,7 +136,7 @@ select
;
selectSubquery
- : (queryBlock | selectCombineClause | parenthesisSelectSubquery)
orderByClause? rowLimitingClause
+ : (queryBlock | selectCombineClause | parenthesisSelectSubquery)
pivotClause? orderByClause? rowLimitingClause
;
selectCombineClause
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 89d44df472d..96647f9d6fe 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
@@ -567,4 +567,24 @@
<column-item name="customer_id" order-direction="ASC"
start-index="80" stop-index="90" literal-start-index="80"
literal-stop-index="90" />
</order-by>
</select>
+
+ <select sql-case-id="select_pivot">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7"/>
+ </projections>
+ <from>
+ <subquery-table>
+ <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>
</sql-parser-test-cases>
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 5f8e8b70518..33706e16d67 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
@@ -43,4 +43,5 @@
<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-cases>
