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 fa90956b4da Support parsing Oracle UPDATE (#27465)
fa90956b4da is described below
commit fa90956b4da71b25d708e40762a3e581f975a4d2
Author: Swapnil Patil <[email protected]>
AuthorDate: Sun Jul 30 01:01:47 2023 -0700
Support parsing Oracle UPDATE (#27465)
---
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 6 ++-
.../parser/src/main/resources/case/dml/update.xml | 49 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/update.xml | 1 +
3 files changed, 55 insertions(+), 1 deletion(-)
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
index 7a55bebe744..8ded7cb819f 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
@@ -464,7 +464,11 @@ queryTableExprClause
flashbackQueryClause
: VERSIONS (BETWEEN (SCN | TIMESTAMP) | PERIOD FOR validTimeColumn
BETWEEN) (expr | MINVALUE) AND (expr | MAXVALUE)
- | AS OF ((SCN | TIMESTAMP) expr | PERIOD FOR validTimeColumn expr)
+ | AS OF ((SCN | TIMESTAMP) (expr | intervalExprClause) | PERIOD FOR
validTimeColumn expr)
+ ;
+
+intervalExprClause
+ : LP_ SYSTIMESTAMP (PLUS_ | MINUS_) INTERVAL (INTEGER_ | STRING_) (HOUR
| MINUTE | SECOND) RP_
;
queryTableExpr
diff --git a/test/it/parser/src/main/resources/case/dml/update.xml
b/test/it/parser/src/main/resources/case/dml/update.xml
index 41948346ae1..91fc1903627 100644
--- a/test/it/parser/src/main/resources/case/dml/update.xml
+++ b/test/it/parser/src/main/resources/case/dml/update.xml
@@ -933,6 +933,55 @@
</set>
</update>
+ <update sql-case-id="update_with_subquery_using_interval">
+ <table start-index="7" stop-index="17">
+ <simple-table name="employees" alias="a" start-index="7"
stop-index="17" />
+ </table>
+ <set start-index="19" stop-index="140">
+ <assignment start-index="23" stop-index="140">
+ <column name="salary" start-index="23" stop-index="28" />
+ <assignment-value>
+ <subquery start-index="32" stop-index="140">
+ <select>
+ <from start-index="52" stop-index="60">
+ <simple-table name="employees"
start-index="52" stop-index="60" />
+ </from>
+ <projections start-index="40" stop-index="45">
+ <column-projection name="salary"
start-index="40" stop-index="45" />
+ </projections>
+ <where start-index="115" stop-index="139">
+ <expr>
+ <binary-operation-expression
start-index="121" stop-index="139">
+ <left>
+ <column name="last_name"
start-index="121" stop-index="129" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="Chung"
start-index="133" stop-index="139" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </assignment-value>
+ </assignment>
+ </set>
+ <where start-index="142" stop-index="166">
+ <expr>
+ <binary-operation-expression start-index="148"
stop-index="166">
+ <left>
+ <column name="last_name" start-index="148"
stop-index="156" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="Chung" start-index="160"
stop-index="166" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </update>
+
<update sql-case-id="update_with_multiple_set">
<table start-index="7" stop-index="15">
<simple-table name="employees" start-index="7" stop-index="15" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/update.xml
b/test/it/parser/src/main/resources/sql/supported/dml/update.xml
index 9797cc3f6ed..0dda3cd1a94 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/update.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/update.xml
@@ -43,4 +43,5 @@
<sql-case id="update_with_set_value" value="UPDATE people_demo1 p SET
VALUE(p) = (SELECT VALUE(q) FROM people_demo2 q WHERE p.department_id =
q.department_id) WHERE p.department_id = 10" db-types="Oracle" />
<sql-case id="update_with_multi_columns" value="UPDATE employees a SET
department_id = (SELECT department_id FROM departments WHERE location_id =
'2100'), (salary, commission_pct) = (SELECT 1.1*AVG(salary),
1.5*AVG(commission_pct) FROM employees b WHERE a.department_id =
b.department_id)" db-types="Oracle" />
<sql-case id="update_with_force_index" value="UPDATE t_order FORCE INDEX
(PRIMARY) SET status = ? WHERE order_id = ?" db-types="MySQL" />
+ <sql-case id="update_with_subquery_using_interval" value="UPDATE employees
a SET salary = (SELECT salary FROM employees AS OF TIMESTAMP (SYSTIMESTAMP -
INTERVAL '2' MINUTE) WHERE last_name = 'Chung') WHERE last_name = 'Chung'"
db-types="Oracle" />
</sql-cases>
