You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by tu...@apache.org on 2022/11/18 00:53:29 UTC
[shardingsphere] branch master updated: Update Oracle SQL XMLAGG function parse (#22246)
This is an automated email from the ASF dual-hosted git repository.
tuichenchuxin 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 83cad1c8962 Update Oracle SQL XMLAGG function parse (#22246)
83cad1c8962 is described below
commit 83cad1c89621509ad36fbb5fce7f251254b419ec
Author: Zichao <57...@users.noreply.github.com>
AuthorDate: Fri Nov 18 13:53:14 2022 +1300
Update Oracle SQL XMLAGG function parse (#22246)
* Update Oracle SQL XMLAGG function parse
* Update Oracle SQL XMLAGG function parse
* Update Oracle SQL XMLAGG function parse
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 12 +++++++++--
.../src/main/antlr4/imports/oracle/Keyword.g4 | 8 ++++++++
.../statement/impl/OracleStatementSQLVisitor.java | 15 ++++++++++++++
test/parser/src/main/resources/case/dml/select.xml | 24 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 1 +
5 files changed, 58 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 04649c2092b..a2edf47f055 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
@@ -163,7 +163,7 @@ unreservedWord
| HOST | PORT | EVERY | MINUTES | HOURS | NORELOCATE | SAVE | DISCARD | APPLICATION | INSTALL
| MINIMUM | VERSION | UNINSTALL | COMPATIBILITY | MATERIALIZE | SUBTYPE | RECORD | CONSTANT | CURSOR
| OTHERS | EXCEPTION | CPU_PER_SESSION | CONNECT_TIME | LOGICAL_READS_PER_SESSION | PRIVATE_SGA | PERCENT_RANK | ROWID
- | LPAD | ZONE | SESSIONTIMEZONE | TO_CHAR
+ | LPAD | ZONE | SESSIONTIMEZONE | TO_CHAR | XMLELEMENT
;
schemaName
@@ -509,7 +509,7 @@ simpleExpr
;
functionCall
- : aggregationFunction | analyticFunction | specialFunction | regularFunction
+ : aggregationFunction | analyticFunction | specialFunction | regularFunction | xmlFunction
;
aggregationFunction
@@ -1613,3 +1613,11 @@ maxNumberOfSnapshots
datetimeExpr
: AT (LOCAL | TIME ZONE expr)
;
+
+xmlFunction
+ : xmlAggFunction
+ ;
+
+xmlAggFunction
+ : XMLAGG LP_ expr orderByClause? RP_
+ ;
diff --git a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Keyword.g4 b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Keyword.g4
index c859b27efe3..da6119fb3c7 100644
--- a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Keyword.g4
+++ b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Keyword.g4
@@ -667,3 +667,11 @@ DBTIMEZONE
TO_CHAR
: T O UL_ C H A R
;
+
+XMLELEMENT
+ : X M L E L E M E N T
+ ;
+
+XMLAGG
+ : X M L A G G
+ ;
diff --git a/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java b/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
index 2ad1ef49f8b..c855bec527c 100644
--- a/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
+++ b/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
@@ -68,6 +68,8 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.TypeNa
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.UnreservedWordContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ViewNameContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DatetimeExprContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.XmlFunctionContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.XmlAggFunctionContext;
import org.apache.shardingsphere.sql.parser.sql.common.constant.AggregationType;
import org.apache.shardingsphere.sql.parser.sql.common.constant.OrderDirection;
import org.apache.shardingsphere.sql.parser.sql.common.constant.ParameterMarkerType;
@@ -503,6 +505,9 @@ public abstract class OracleStatementSQLVisitor extends OracleStatementBaseVisit
if (null != ctx.regularFunction()) {
return visit(ctx.regularFunction());
}
+ if (null != ctx.xmlFunction()) {
+ return visit(ctx.xmlFunction());
+ }
throw new IllegalStateException("FunctionCallContext must have aggregationFunction, regularFunction, analyticFunction or specialFunction.");
}
@@ -537,6 +542,16 @@ public abstract class OracleStatementSQLVisitor extends OracleStatementBaseVisit
return result;
}
+ @Override
+ public ASTNode visitXmlFunction(final XmlFunctionContext ctx) {
+ return visit(ctx.xmlAggFunction());
+ }
+
+ @Override
+ public ASTNode visitXmlAggFunction(final XmlAggFunctionContext ctx) {
+ return new FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), ctx.XMLAGG().getText(), getOriginalText(ctx));
+ }
+
private Collection<ExpressionSegment> getExpressions(final AggregationFunctionContext ctx) {
return null == ctx.expr() ? Collections.emptyList() : ctx.expr().stream().map(each -> (ExpressionSegment) visit(each)).collect(Collectors.toList());
}
diff --git a/test/parser/src/main/resources/case/dml/select.xml b/test/parser/src/main/resources/case/dml/select.xml
index 8387e26359e..90fb75ce23f 100644
--- a/test/parser/src/main/resources/case/dml/select.xml
+++ b/test/parser/src/main/resources/case/dml/select.xml
@@ -4634,4 +4634,28 @@
<column-item name="ts_col" start-index="85" stop-index="90" />
</order-by>
</select>
+
+ <select sql-case-id="select_xmlelement_xmlagg_function">
+ <projections start-index="7" stop-index="124">
+ <expression-projection text="XMLELEMENT('Department', XMLAGG(XMLELEMENT('Employee', e.job_id||' '||e.last_name) ORDER BY last_name))" alias="Dept_list" start-index="7" stop-index="124" />
+ </projections>
+ <from>
+ <simple-table name="employees" alias="e" start-index="131" stop-index="141" />
+ </from>
+ <where start-index="143" stop-index="168">
+ <expr>
+ <binary-operation-expression start-index="149" stop-index="168">
+ <left>
+ <column name="department_id" start-index="149" stop-index="163" >
+ <owner name="e" start-index="149" stop-index="149" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="30" start-index="167" stop-index="168" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </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 2df4099f308..3d700968dc9 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select.xml
@@ -144,4 +144,5 @@
<sql-case id="select_linear_regression_function" value="SELECT job_id, employee_id ID, salary, REGR_SLOPE(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id) slope, REGR_INTERCEPT(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id) intcpt, REGR_R2(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id) rsqr, REGR_COUNT(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id) count, REGR_AVGX(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id) avgx, REGR_AVGY(SYSDATE-hire_date, salar [...]
<sql-case id="select_lpad_function" value="SELECT LPAD('Page 1',15,'*.') 'LPAD example' FROM DUAL;" db-types="Oracle" />
<sql-case id="select_to_char_function" value="SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS ts_col FROM date_tab ORDER BY ts_col;" />
+ <sql-case id="select_xmlelement_xmlagg_function" value="SELECT XMLELEMENT('Department', XMLAGG(XMLELEMENT('Employee', e.job_id||' '||e.last_name) ORDER BY last_name)) as 'Dept_list' FROM employees e WHERE e.department_id = 30;" db-types="Oracle" />
</sql-cases>