You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2022/10/28 06:27:27 UTC
[shardingsphere] branch master updated: Support oracle listagg function and start with connect by claus parse (#21820)
This is an automated email from the ASF dual-hosted git repository.
panjuan 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 2b1a94b94b4 Support oracle listagg function and start with connect by claus parse (#21820)
2b1a94b94b4 is described below
commit 2b1a94b94b4c3727af233375b25feae3548bd58c
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Fri Oct 28 14:27:20 2022 +0800
Support oracle listagg function and start with connect by claus parse (#21820)
---
.../dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4 | 9 +++++++--
.../oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4 | 4 ++++
test/parser/src/main/resources/case/dml/select.xml | 9 +++++++++
test/parser/src/main/resources/sql/supported/dml/select.xml | 1 +
4 files changed, 21 insertions(+), 2 deletions(-)
diff --git a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4 b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index 0f9c5692ddb..0e946906938 100644
--- a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -438,6 +438,7 @@ expr
: expr andOperator expr
| expr orOperator expr
| notOperator expr
+ | PRIOR expr
| LP_ expr RP_
| booleanPrimary
;
@@ -508,11 +509,15 @@ functionCall
;
aggregationFunction
- : aggregationFunctionName LP_ (((DISTINCT | ALL)? expr) | ASTERISK_) RP_ (OVER LP_ analyticClause RP_)?
+ : aggregationFunctionName LP_ (((DISTINCT | ALL)? expr) | ASTERISK_) (COMMA_ stringLiterals)? listaggOverflowClause? RP_ (WITHIN GROUP LP_ orderByClause RP_)? (OVER LP_ analyticClause RP_)? (OVER LP_ analyticClause RP_)?
;
aggregationFunctionName
- : MAX | MIN | SUM | COUNT | AVG | GROUPING
+ : MAX | MIN | SUM | COUNT | AVG | GROUPING | LISTAGG
+ ;
+
+listaggOverflowClause
+ : ON OVERFLOW (ERROR | (TRUNCATE stringLiterals? ((WITH | WITHOUT) COUNT)?))
;
analyticClause
diff --git a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4 b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
index f94c362fafb..e06b235406d 100644
--- a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
+++ b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
@@ -3087,3 +3087,7 @@ LOGICAL_READS_PER_SESSION
PRIVATE_SGA
: P R I V A T E UL_ S G A
;
+
+LISTAGG
+ : L I S T A G G
+ ;
diff --git a/test/parser/src/main/resources/case/dml/select.xml b/test/parser/src/main/resources/case/dml/select.xml
index 19b5eb84af6..c0a722b91a5 100644
--- a/test/parser/src/main/resources/case/dml/select.xml
+++ b/test/parser/src/main/resources/case/dml/select.xml
@@ -4436,4 +4436,13 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_with_listagg_function_start_with_connect_by">
+ <projections start-index="7" stop-index="101">
+ <expression-projection text="LISTAGG(c.category_name, '/') WITHIN GROUP (ORDER BY LENGTH (c."level") DESC)" start-index="7" stop-index="101" alias="category_level" />
+ </projections>
+ <from>
+ <simple-table name="t_product_category" start-index="108" stop-index="127" alias="c" />
+ </from>
+ </select>
</sql-parser-test-cases>
diff --git a/test/parser/src/main/resources/sql/supported/dml/select.xml b/test/parser/src/main/resources/sql/supported/dml/select.xml
index 5cd7fbcb8e0..43dc5a1d46e 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select.xml
@@ -133,4 +133,5 @@
<sql-case id="select_with_xml_default_namespaces_clause" value="WITH XMLNAMESPACES ('uri1' AS ns1, 'uri2' AS ns2, DEFAULT 'uri2') SELECT order_id AS 'ns1:order_id', status AS 'ns1:status' FROM t_order FOR XML RAW ('ns1:order'), ELEMENTS XSINIL" db-types="SQLServer" />
<sql-case id="select_with_mysql_main_and_utc_data_and_so_on" value="SELECT MYSQL_MAIN,UTC_DATE,UTC_TIME,UTC_TIMESTAMP FROM test" db-types="MySQL" />
<sql-case id="select_with_analytic_function" value="SELECT order_id, ROW_NUMBER () OVER (PARTITION BY user_id ORDER BY order_id DESC) AS row_number FROM t_order WHERE order_id = ?" db-types="Oracle" />
+ <sql-case id="select_with_listagg_function_start_with_connect_by" value="SELECT LISTAGG(c.category_name, '/') WITHIN GROUP (ORDER BY LENGTH (c."level") DESC) AS category_level FROM t_product_category c START WITH c.category_id = 1 CONNECT BY PRIOR c.parent_id = c.category_id" db-types="Oracle" />
</sql-cases>