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 <[email protected]>
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>
