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 5f6b3db5cee Support oracle merge statement parse when write not
matched first (#30452)
5f6b3db5cee is described below
commit 5f6b3db5cee846aa3d5d70ed4aa0f06493345471
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Mon Mar 11 18:19:48 2024 +0800
Support oracle merge statement parse when write not matched first (#30452)
---
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 2 +-
.../parser/src/main/resources/case/dml/merge.xml | 113 +++++++++++++++++++++
.../src/main/resources/sql/supported/dml/merge.xml | 12 +++
3 files changed, 126 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 c94493dceb9..486c9aefd51 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
@@ -707,7 +707,7 @@ rowLimitingClause
;
merge
- : MERGE hint? intoClause usingClause mergeUpdateClause? mergeInsertClause?
errorLoggingClause?
+ : MERGE hint? intoClause usingClause (mergeUpdateClause?
mergeInsertClause? | mergeInsertClause? mergeUpdateClause?) errorLoggingClause?
;
hint
diff --git a/test/it/parser/src/main/resources/case/dml/merge.xml
b/test/it/parser/src/main/resources/case/dml/merge.xml
index 809c3366df6..5502075af7a 100644
--- a/test/it/parser/src/main/resources/case/dml/merge.xml
+++ b/test/it/parser/src/main/resources/case/dml/merge.xml
@@ -333,6 +333,119 @@
</where>
</insert>
</merge>
+ <merge sql-case-id="merge_insert_and_update_table">
+ <target>
+ <simple-table alias="t1" name="t_order" start-index="11"
stop-index="20" />
+ </target>
+ <source>
+ <subquery-table alias="t2">
+ <subquery>
+ <select>
+ <projections start-index="92" stop-index="116">
+ <expression-projection text="1" start-index="92"
stop-index="102" alias="userId">
+ <literal-expression value="1" start-index="92"
stop-index="92" />
+ </expression-projection>
+ <expression-projection text="1" start-index="105"
stop-index="116" alias="orderId">
+ <literal-expression value="1"
start-index="105" stop-index="105" />
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="DUAL" start-index="186"
stop-index="189" />
+ </from>
+ </select>
+ </subquery>
+ </subquery-table>
+ </source>
+ <expr>
+ <binary-operation-expression start-index="255" stop-index="305">
+ <left>
+ <binary-operation-expression start-index="255"
stop-index="276">
+ <left>
+ <column name="user_id" start-index="255"
stop-index="264">
+ <owner name="t1" start-index="255"
stop-index="256" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="userId" start-index="268"
stop-index="276">
+ <owner name="t2" start-index="268"
stop-index="269" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>AND</operator>
+ <right>
+ <binary-operation-expression start-index="282"
stop-index="305">
+ <left>
+ <column name="order_id" start-index="282"
stop-index="292">
+ <owner name="t1" start-index="282"
stop-index="283" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="orderId" start-index="296"
stop-index="305">
+ <owner name="t2" start-index="296"
stop-index="297" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ <update>
+ <set start-index="824" stop-index="1000">
+ <assignment>
+ <columns name="merchant_id" start-index="824"
stop-index="834" />
+ <assignment-value>
+ <literal-expression value="1" start-index="838"
stop-index="838" />
+ </assignment-value>
+ </assignment>
+ <assignment>
+ <columns name="remark" start-index="905" stop-index="910"
/>
+ <assignment-value>
+ <literal-expression value="1" start-index="919"
stop-index="919" />
+ </assignment-value>
+ </assignment>
+ <assignment>
+ <columns name="status" start-index="986" stop-index="991"
/>
+ <assignment-value>
+ <literal-expression value="1" start-index="1000"
stop-index="1000" />
+ </assignment-value>
+ </assignment>
+ </set>
+ </update>
+ <insert>
+ <columns start-index="453" stop-index="515">
+ <column name="order_id" start-index="454" stop-index="461" />
+ <column name="user_id" start-index="464" stop-index="470" />
+ <column name="status" start-index="473" stop-index="478" />
+ <column name="merchant_id" start-index="481" stop-index="491"
/>
+ <column name="remark" start-index="494" stop-index="499" />
+ <column name="creation_date" start-index="502"
stop-index="514" />
+ </columns>
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="1" start-index="585"
stop-index="585" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="1" start-index="588"
stop-index="588" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="1" start-index="591"
stop-index="591" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="1" start-index="594"
stop-index="594" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="1" start-index="597"
stop-index="597" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="DATE '2017-08-08'"
start-index="600" stop-index="616" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+ </merge>
<merge sql-case-id="merge_into_select">
<target>
<subquery-table alias="D">
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/merge.xml
b/test/it/parser/src/main/resources/sql/supported/dml/merge.xml
index de66626b021..933b4f74d6a 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/merge.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/merge.xml
@@ -22,5 +22,17 @@
<sql-case id="merge_update_table" value="MERGE INTO people_target pt USING
people_source ps ON (pt.person_id = ps.person_id) WHEN MATCHED THEN UPDATE SET
pt.first_name = ps.first_name, pt.last_name = ps.last_name, pt.title =
ps.title" db-types="Oracle" />
<sql-case id="merge_update_table_with_delete" value="MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees WHERE
department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN
UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE (S.salary > 8000)"
db-types="Oracle" />
<sql-case id="merge_update_and_insert_table" value="MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM hr.employees WHERE
department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED
THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE (S.salary =
8000) WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES
(S.employee_id, S.salary*.01) WHERE (S.salary <= 8000);"
db-types="Oracle" />
+ <sql-case id="merge_insert_and_update_table" value="MERGE INTO t_order t1
+ USING (SELECT 1 AS
userId, 1 AS orderId
+ FROM DUAL) t2
+ ON (t1.user_id =
t2.userId AND t1.order_id = t2.orderId)
+ WHEN NOT MATCHED THEN
+ INSERT (order_id,
user_id, status, merchant_id, remark, creation_date)
+ VALUES (1, 1, 1,
1, 1, DATE '2017-08-08')
+ WHEN MATCHED THEN
+ UPDATE
+ SET merchant_id =
1,
+ remark =
1,
+ status =
1;" db-types="Oracle" />
<sql-case id="merge_into_select" value="MERGE INTO (SELECT * FROM bonuses
WHERE department_id = 80) D USING (SELECT employee_id, salary, department_id
FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE
(S.salary > 8000)" db-types="Oracle" />
</sql-cases>
