This is an automated email from the ASF dual-hosted git repository.
zhangliang 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 0916dd63ab7 Add SQL parser test cases for Oracle (#37854)
0916dd63ab7 is described below
commit 0916dd63ab7e535df7e13885e666daaab7a0fbcd
Author: Liang Zhang <[email protected]>
AuthorDate: Mon Jan 26 23:43:20 2026 +0800
Add SQL parser test cases for Oracle (#37854)
---
.../parser/src/main/resources/case/dml/insert.xml | 53 ++++++++++++++++++++++
.../parser/src/main/resources/case/dml/merge.xml | 48 ++++++++++++++++++++
.../src/main/resources/case/dml/select-lock.xml | 13 ++++++
.../parser/src/main/resources/case/dml/select.xml | 11 +++++
.../parser/src/main/resources/case/dml/update.xml | 25 ++++++++++
.../main/resources/sql/supported/dml/insert.xml | 1 +
.../src/main/resources/sql/supported/dml/merge.xml | 1 +
.../resources/sql/supported/dml/select-lock.xml | 1 +
.../main/resources/sql/supported/dml/select.xml | 1 +
.../main/resources/sql/supported/dml/update.xml | 1 +
10 files changed, 155 insertions(+)
diff --git a/test/it/parser/src/main/resources/case/dml/insert.xml
b/test/it/parser/src/main/resources/case/dml/insert.xml
index 82394816bcd..44a4909818d 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -2478,6 +2478,59 @@
</select>
</insert>
+ <insert sql-case-id="insert_first_with_conditional_when_else">
+ <multi-table-insert-type value="FIRST" />
+ <multi-table-conditional-into start-index="7" stop-index="121">
+ <conditional-into-when-then start-index="13" stop-index="79">
+ <when start-index="13" stop-index="35">
+ <binary-operation-expression start-index="18"
stop-index="35">
+ <left>
+ <column name="order_total" start-index="18"
stop-index="28" />
+ </left>
+ <operator><=</operator>
+ <right>
+ <literal-expression value="100" start-index="33"
stop-index="35" />
+ </right>
+ </binary-operation-expression>
+ </when>
+ <then start-index="42" stop-index="79">
+ <insert-statement>
+ <table name="small_orders" start-index="47"
stop-index="58" />
+ <columns start-index="59" stop-index="59" />
+ <values start-index="60" stop-index="79">
+ <value>
+ <assignment-value>
+ <column name="order_total"
start-index="68" stop-index="78" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert-statement>
+ </then>
+ </conditional-into-when-then>
+ <conditional-into-else start-index="81" stop-index="121">
+ <insert-statement>
+ <table name="big_orders" start-index="91" stop-index="100"
/>
+ <columns start-index="101" stop-index="101" />
+ <values start-index="102" stop-index="121">
+ <value>
+ <assignment-value>
+ <column name="order_total" start-index="110"
stop-index="120" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert-statement>
+ </conditional-into-else>
+ </multi-table-conditional-into>
+ <select>
+ <projections start-index="130" stop-index="140">
+ <column-projection name="order_total" start-index="130"
stop-index="140" />
+ </projections>
+ <from start-index="142" stop-index="152">
+ <simple-table name="orders" start-index="147" stop-index="152"
/>
+ </from>
+ </select>
+ </insert>
+
<insert sql-case-id="insert_with_rank_column">
<table name="sales" start-index="12" stop-index="16" />
<columns start-index="18" stop-index="23">
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 83619753d29..09554e2906b 100644
--- a/test/it/parser/src/main/resources/case/dml/merge.xml
+++ b/test/it/parser/src/main/resources/case/dml/merge.xml
@@ -456,6 +456,54 @@
</set>
</update>
</merge>
+
+ <merge sql-case-id="merge_insert_then_update">
+ <target>
+ <simple-table alias="D" name="bonuses" start-index="11"
stop-index="19" />
+ </target>
+ <source>
+ <simple-table alias="S" name="employees" start-index="27"
stop-index="37" />
+ </source>
+ <expr>
+ <binary-operation-expression start-index="43" stop-index="53">
+ <left>
+ <column name="id" start-index="43" stop-index="46">
+ <owner name="D" start-index="43" stop-index="43" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="id" start-index="50" stop-index="53">
+ <owner name="S" start-index="50" stop-index="50" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ <insert>
+ <columns start-index="85" stop-index="88">
+ <column name="id" start-index="86" stop-index="87" />
+ </columns>
+ <values start-index="90" stop-index="102">
+ <column name="id" start-index="98" stop-index="101">
+ <owner name="S" start-index="98" stop-index="98" />
+ </column>
+ </values>
+ </insert>
+ <update>
+ <set start-index="133" stop-index="143">
+ <assignment start-index="133" stop-index="143">
+ <column name="id" start-index="133" stop-index="136">
+ <owner name="D" start-index="133" stop-index="133" />
+ </column>
+ <assignment-value>
+ <column name="id" start-index="140" stop-index="143">
+ <owner name="S" start-index="140" stop-index="140"
/>
+ </column>
+ </assignment-value>
+ </assignment>
+ </set>
+ </update>
+ </merge>
<merge sql-case-id="merge_into_select">
<target>
<subquery-table alias="D" start-index="11" stop-index="60">
diff --git a/test/it/parser/src/main/resources/case/dml/select-lock.xml
b/test/it/parser/src/main/resources/case/dml/select-lock.xml
index 29c1618dd21..30a3d377787 100644
--- a/test/it/parser/src/main/resources/case/dml/select-lock.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-lock.xml
@@ -193,6 +193,19 @@
</lock>
</select>
+ <select sql-case-id="select_lock_with_for_update_of_column_skip_locked">
+ <from start-index="14" stop-index="22">
+ <simple-table name="employees" start-index="14" stop-index="22" />
+ </from>
+ <projections distinct-row="false" start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <lock start-index="24" stop-index="65">
+ <table name="employees" start-index="38" stop-index="46" />
+ <column name="salary" start-index="48" stop-index="53" />
+ </lock>
+ </select>
+
<select sql-case-id="select_lock_with_of_multi_tables" parameters="1">
<where start-index="44" stop-index="151">
<expr>
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 aec75c1ac51..91a2037fb87 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -7025,6 +7025,17 @@
</order-by>
</select>
+ <select sql-case-id="select_with_db_link">
+ <from>
+ <simple-table name="employees" start-index="14" stop-index="25">
+ <owner name="hr" start-index="14" stop-index="15" />
+ </simple-table>
+ </from>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ </select>
+
<select sql-case-id="select_with_trim_function_multi">
<projections start-index="7" stop-index="203" literal-start-index="7"
literal-stop-index="203">
<expression-projection text="TRIM(' derby ')" start-index="7"
stop-index="22" literal-start-index="7" literal-stop-index="22">
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 62ce5c243fc..3596d81e62d 100644
--- a/test/it/parser/src/main/resources/case/dml/update.xml
+++ b/test/it/parser/src/main/resources/case/dml/update.xml
@@ -115,6 +115,31 @@
</set>
</update>
+ <update sql-case-id="update_with_subquery_target_oracle">
+ <table start-index="7" stop-index="31">
+ <subquery-table start-index="7" stop-index="31">
+ <subquery>
+ <select>
+ <projections start-index="15" stop-index="15">
+ <shorthand-projection start-index="15"
stop-index="15" />
+ </projections>
+ <from start-index="17" stop-index="30">
+ <simple-table name="employees" start-index="22"
stop-index="30" />
+ </from>
+ </select>
+ </subquery>
+ </subquery-table>
+ </table>
+ <set start-index="33" stop-index="46">
+ <assignment start-index="36" stop-index="46">
+ <column name="salary" start-index="37" stop-index="42" />
+ <assignment-value>
+ <literal-expression value="1" start-index="46"
stop-index="46" />
+ </assignment-value>
+ </assignment>
+ </set>
+ </update>
+
<update sql-case-id="update_with_alias" parameters="'update', 1, 1">
<table start-index="7" stop-index="18" >
<simple-table name="t_order" alias="o" start-index="7"
stop-index="18" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
index 0367520dac8..13763c0f3be 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
@@ -93,6 +93,7 @@
<sql-case id="insert_with_multitable_element" value="INSERT ALL INTO sales
(prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id,
weekly_start_date, sales_sun) INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, sales_mon, weekly_start_date+1) SELECT
product_id, customer_id, weekly_start_date, sales_sun, sales_mon FROM
sales_input_table" db-types="Oracle" />
<sql-case id="insert_all_with_multi_table_with_conditional_when"
value="INSERT ALL WHEN order_total <= 100000 THEN INTO small_orders WHEN
order_total > 1000000 AND order_total <= 200000 THEN INTO medium_orders WHEN
order_total > 200000 THEN INTO large_orders SELECT order_id, order_total,
sales_rep_id, customer_id FROM orders" db-types="Oracle" />
<sql-case
id="insert_all_with_multi_table_with_conditional_when_with_conditional_else"
value="INSERT ALL WHEN order_total <= 100000 THEN INTO small_orders WHEN
order_total > 100000 AND order_total <= 200000 THEN INTO medium_orders ELSE
INTO large_orders SELECT order_id, order_total, sales_rep_id, customer_id FROM
orders" db-types="Oracle" />
+ <sql-case id="insert_first_with_conditional_when_else" value="INSERT FIRST
WHEN order_total <= 100 THEN INTO small_orders VALUES (order_total) ELSE
INTO big_orders VALUES (order_total) SELECT order_total FROM orders"
db-types="Oracle" />
<sql-case id="insert_with_rank_column" value="INSERT INTO sales (rank)
VALUES (1)" db-types="Oracle" />
<sql-case id="insert_with_schema" value="INSERT INTO db1.t_order VALUES
(1,2,3)" />
<sql-case id="insert_on_duplicate_key_update_nothing" value="INSERT INTO
t_order (order_id, user_id, status) VALUES (1, 1, 'insert') ON DUPLICATE KEY
UPDATE NOTHING" db-types="openGauss" />
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 97c49d13e16..21441ea58f9 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
@@ -34,6 +34,7 @@
SET merchant_id =
?,
remark =
?,
status =
?;" db-types="Oracle" />
+ <sql-case id="merge_insert_then_update" value="MERGE INTO bonuses D USING
employees S ON (D.id = S.id) WHEN NOT MATCHED THEN INSERT (id) VALUES (S.id)
WHEN MATCHED THEN UPDATE SET D.id = S.id" 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-case id="merge_output_with_aaterisk" value="MERGE INTO people_target
pt USING people_source ps ON (pt.person_id = ps.person_id) WHEN MATCHED THEN
UPDATE SET first_name = ps.first_name, pt.last_name = ps.last_name, pt.title =
ps.title OUTPUT deleted.*, $action, inserted.*" db-types="SQLServer" />
<sql-case id="merge_update" value="MERGE INTO employees AS T USING
employee_updates AS S ON T.id = S.id WHEN MATCHED THEN UPDATE SET T.salary =
S.new_salary;" db-types="Hive" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-lock.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-lock.xml
index aeae9b44eeb..dc8146c0706 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-lock.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-lock.xml
@@ -26,5 +26,6 @@
<sql-case id="select_lock_with_of_multi_tables" value="SELECT * FROM
t_order, t_order_item, t_user WHERE t_order.order_id = t_order_item.order_id
AND t_order.user_id = t_user.user_id AND t_order.order_id = ? FOR UPDATE OF
t_order, t_order_item FOR SHARE OF t_user" db-types="MySQL" />
<sql-case id="select_lock_with_for_update_column" value="SELECT order_id
FROM t_order FOR UPDATE OF order_id" db-types="Oracle" />
<sql-case id="select_lock_with_for_update_table_column" value="SELECT
order_id FROM t_order FOR UPDATE OF t_order.order_id" db-types="Oracle" />
+ <sql-case id="select_lock_with_for_update_of_column_skip_locked"
value="SELECT * FROM employees FOR UPDATE OF employees.salary SKIP LOCKED"
db-types="Oracle" />
<sql-case id="select_lock_with_union_all" value="SELECT order_id FROM
t_order FOR UPDATE UNION ALL SELECT order_id FROM t_order2 FOR UPDATE"
db-types="MySQL" />
</sql-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 b629758d760..d43cee267ce 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
@@ -260,6 +260,7 @@
<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-case id="select_with_corr_function" value="SELECT employee_id,
CORR(SYSDATE - hire_date, salary) FROM employees WHERE department_id in (50,
80) ORDER BY employee_id" db-types="Oracle" />
+ <sql-case id="select_with_db_link" value="SELECT * FROM
hr.employees@remote" db-types="Oracle" />
<sql-case id="select_with_trim_function_multi" value="select TRIM(' derby
'), TRIM(BOTH ' ' FROM ' derby '), TRIM(TRAILING ' ' FROM ' derby '),
TRIM(cast (null as char(1)) FROM ' derby '), TRIM(' ' FROM cast(null as
varchar(30))), TRIM('y' FROM ' derby') FROM employees" db-types="Oracle" />
<sql-case id="select_with_trim_function_simple" value="SELECT TRIM( '.,! '
FROM ' # test .') AS Result" db-types="SQLServer" />
<sql-case id="select_with_trim_function_leading" value="SELECT
TRIM(LEADING '.,! ' FROM ' .# test .') AS Result"
db-types="SQLServer,Presto" />
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 831c525d87f..d791ce4c3ce 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
@@ -49,6 +49,7 @@
<sql-case id="update_with_translate_function" value="UPDATE translate_tab
SET char_col = TRANSLATE (nchar_col USING CHAR_CS);" db-types="Oracle"/>
<sql-case id="update_with_dot_column_name" value="UPDATE employees SET
salary =.salary + 10 WHERE employee_id BETWEEN 1 and 10; " db-types="Oracle" />
<sql-case id="update_with_set_value_clause" value="UPDATE ot1 SET
VALUE(ot1.x) = t1(20) WHERE VALUE(ot1.x) = t1(10);" db-types="Oracle" />
+ <sql-case id="update_with_subquery_target_oracle" value="UPDATE (SELECT *
FROM employees) SET salary = 1" db-types="Oracle" />
<sql-case id="update_with_open_row_set_function" value="UPDATE T SET
XmlCol = ( SELECT * FROM OPENROWSET(BULK 'C:\SampleFolder\SampleData3.txt',
SINGLE_BLOB) AS x) WHERE IntCol = 1" db-types="SQLServer"/>
<sql-case id="update_with_point_type" value="UPDATE dbo.Cities SET
Location.SetXY(23.5, 23.5) WHERE Name = 'Anchorage'" db-types="SQLServer"/>
<sql-case id="update_with_table_hint" value="UPDATE Production.Product
WITH (TABLOCK) SET ListPrice = ListPrice * 1.10 WHERE ProductNumber LIKE
'BK-%'" db-types="SQLServer"/>
