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/10 09:48:07 UTC
[shardingsphere] branch master updated: Update Oracle DML SLELECT PERCENT_RANK statement parse (#22042)
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 e9c7eebab90 Update Oracle DML SLELECT PERCENT_RANK statement parse (#22042)
e9c7eebab90 is described below
commit e9c7eebab9022460e26399b4412f717bed906e12
Author: Zichao <57...@users.noreply.github.com>
AuthorDate: Thu Nov 10 22:48:00 2022 +1300
Update Oracle DML SLELECT PERCENT_RANK statement parse (#22042)
* Update Oracle DML SLELECT PERCENT_RANK statement parse
* Update Oracle DML SLELECT PERCENT_RANK statement parse
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 8 +++---
.../main/antlr4/imports/oracle/OracleKeyword.g4 | 4 +++
.../statement/impl/OracleStatementSQLVisitor.java | 2 +-
test/parser/src/main/resources/case/dml/select.xml | 30 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 2 ++
5 files changed, 42 insertions(+), 4 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 0e946906938..a97a4b7f6d3 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
@@ -162,7 +162,7 @@ unreservedWord
| ENFORCED | TRUSTED | ID | SYNCHRONOUS | ASYNCHRONOUS | REPEAT | FEATURE | STATEMENT | CLAUSE | UNPLUG
| 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
+ | OTHERS | EXCEPTION | CPU_PER_SESSION | CONNECT_TIME | LOGICAL_READS_PER_SESSION | PRIVATE_SGA | PERCENT_RANK
;
schemaName
@@ -441,6 +441,8 @@ expr
| PRIOR expr
| LP_ expr RP_
| booleanPrimary
+ | aggregationFunction
+ | analyticFunction
;
andOperator
@@ -509,11 +511,11 @@ functionCall
;
aggregationFunction
- : aggregationFunctionName LP_ (((DISTINCT | ALL)? expr) | ASTERISK_) (COMMA_ stringLiterals)? listaggOverflowClause? RP_ (WITHIN GROUP LP_ orderByClause RP_)? (OVER LP_ analyticClause RP_)? (OVER LP_ analyticClause RP_)?
+ : aggregationFunctionName LP_ (((DISTINCT | ALL)? expr (COMMA_ 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 | LISTAGG
+ : MAX | MIN | SUM | COUNT | AVG | GROUPING | LISTAGG | PERCENT_RANK
;
listaggOverflowClause
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 e06b235406d..a081263007a 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
@@ -3088,6 +3088,10 @@ PRIVATE_SGA
: P R I V A T E UL_ S G A
;
+PERCENT_RANK
+ : P E R C E N T UL_ R A N K
+ ;
+
LISTAGG
: L I S T 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 5ca12e13415..2768051943a 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
@@ -519,7 +519,7 @@ public abstract class OracleStatementSQLVisitor extends OracleStatementBaseVisit
}
private Collection<ExpressionSegment> getExpressions(final AggregationFunctionContext ctx) {
- return null == ctx.expr() ? Collections.emptyList() : Collections.singletonList((ExpressionSegment) visit(ctx.expr()));
+ return null == ctx.expr() ? Collections.emptyList() : ctx.expr().stream().map(each -> (ExpressionSegment) visit(each)).collect(Collectors.toList());
}
private String getDistinctExpression(final AggregationFunctionContext ctx) {
diff --git a/test/parser/src/main/resources/case/dml/select.xml b/test/parser/src/main/resources/case/dml/select.xml
index c0a722b91a5..e46461f7241 100644
--- a/test/parser/src/main/resources/case/dml/select.xml
+++ b/test/parser/src/main/resources/case/dml/select.xml
@@ -4445,4 +4445,34 @@
<simple-table name="t_product_category" start-index="108" stop-index="127" alias="c" />
</from>
</select>
+
+ <select sql-case-id="select_aggregate_percent_rank">
+ <projections start-index="7" stop-index="92">
+ <expression-projection text="PERCENT_RANK(15000, .05) WITHIN GROUP (ORDER BY salary, commission_pct)" alias="Percent-Rank" start-index="7" stop-index="92" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="99" stop-index="107" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_analytic_percent_rank">
+ <projections start-index="7" stop-index="115">
+ <column-projection name="department_id" start-index="7" stop-index="19" />
+ <column-projection name="last_name" start-index="22" stop-index="30" />
+ <column-projection name="salary" start-index="33" stop-index="38" />
+ <expression-projection text="PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)" start-index="41" stop-index="115" alias="pr">
+ <expr>
+ <function function-name="PERCENT_RANK" start-index="41" stop-index="109" text="PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)"/>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="122" stop-index="130" />
+ </from>
+ <order-by>
+ <column-item name="pr" start-index="141" stop-index="142" />
+ <column-item name="salary" start-index="145" stop-index="150" />
+ <column-item name="last_name" start-index="153" stop-index="161" />
+ </order-by>
+ </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 43dc5a1d46e..f1c2d785518 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select.xml
@@ -134,4 +134,6 @@
<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-case id="select_aggregate_percent_rank" value="SELECT PERCENT_RANK(15000, .05) WITHIN GROUP (ORDER BY salary, commission_pct) 'Percent-Rank' FROM employees;" db-types="Oracle" />
+ <sql-case id="select_analytic_percent_rank" value="SELECT department_id, last_name, salary, PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr FROM employees ORDER BY pr, salary, last_name;" db-types="Oracle" />
</sql-cases>