This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang 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 ac4bfc2d34f Support oracle keep clause parsing (#27420)
ac4bfc2d34f is described below
commit ac4bfc2d34f270154a06fc3867167269604118b9
Author: ZhangCheng <[email protected]>
AuthorDate: Mon Jul 24 17:22:30 2023 +0800
Support oracle keep clause parsing (#27420)
* Support oracle keep clause parsing
* Support oracle keep clause parsing
---
.../oracle/src/main/antlr4/imports/oracle/BaseRule.g4 | 6 +++++-
test/it/parser/src/main/resources/case/dml/select.xml | 14 ++++++++++++++
.../parser/src/main/resources/sql/supported/dml/select.xml | 1 +
3 files changed, 20 insertions(+), 1 deletion(-)
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index f1e55d54017..c3b84a416c6 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -685,7 +685,11 @@ functionCall
;
aggregationFunction
- : aggregationFunctionName LP_ (((DISTINCT | ALL)? expr (COMMA_ expr)*) |
ASTERISK_) (COMMA_ stringLiterals)? listaggOverflowClause? RP_ (WITHIN GROUP
LP_ orderByClause RP_)? overClause? overClause?
+ : aggregationFunctionName LP_ (((DISTINCT | ALL)? expr (COMMA_ expr)*) |
ASTERISK_) (COMMA_ stringLiterals)? listaggOverflowClause? RP_ (WITHIN GROUP
LP_ orderByClause RP_)? keepClause? overClause? overClause?
+ ;
+
+keepClause
+ : KEEP LP_ DENSE_RANK (FIRST | LAST) orderByClause RP_ overClause?
;
aggregationFunctionName
diff --git a/test/it/parser/src/main/resources/case/dml/select.xml
b/test/it/parser/src/main/resources/case/dml/select.xml
index 1e2041309a2..f39fd794adb 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -5726,4 +5726,18 @@
<column-item name="department_id" order-direction="ASC"
start-index="189" stop-index="201" literal-start-index="189"
literal-stop-index="201" />
</group-by>
</select>
+
+ <select sql-case-id="select_with_keep_clause">
+ <projections start-index="7" stop-index="215" literal-start-index="7"
literal-stop-index="215">
+ <column-projection name="salary" start-index="7" stop-index="12"
literal-start-index="7" literal-stop-index="12" />
+ <aggregation-projection type="MIN" expression="MIN(salary) KEEP
(DENSE_RANK FIRST ORDER BY commission_pct) OVER (PARTITION BY department_id)"
alias="Worst" start-index="14" stop-index="106" literal-start-index="14"
literal-stop-index="106" />
+ <aggregation-projection type="MAX" expression="MAX(salary) KEEP
(DENSE_RANK LAST ORDER BY commission_pct) OVER (PARTITION BY department_id)"
alias="Best" start-index="117" stop-index="208" literal-start-index="117"
literal-stop-index="208" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="222" stop-index="230"
literal-start-index="222" literal-stop-index="230" />
+ </from>
+ <order-by>
+ <column-item name="department_id" order-direction="ASC"
start-index="241" stop-index="253" literal-start-index="241"
literal-stop-index="253" />
+ </order-by>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
index 12a934ea074..34338406191 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
@@ -193,4 +193,5 @@
<sql-case id="select_with_connect_by_root" value="SELECT CONNECT_BY_ROOT
last_name 'Manager' FROM employees CONNECT BY PRIOR employee_id = manager_id"
db-types="Oracle" />
<sql-case id="select_with_ntile_function" value="SELECT NTILE(4) OVER
(ORDER BY salary DESC) AS quartile FROM employees WHERE department_id = 100
ORDER BY last_name" db-types="Oracle" />
<sql-case id="select_with_percentile_functions" value="SELECT
department_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) 'Median
cont', PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) 'Median disc'
FROM employees GROUP BY department_id" db-types="Oracle" />
+ <sql-case id="select_with_keep_clause" value="SELECT salary,MIN(salary)
KEEP (DENSE_RANK FIRST ORDER BY commission_pct) OVER (PARTITION BY
department_id) 'Worst', MAX(salary) KEEP (DENSE_RANK LAST ORDER BY
commission_pct) OVER (PARTITION BY department_id) 'Best' FROM employees ORDER
BY department_id" db-types="Oracle" />
</sql-cases>
