This is an automated email from the ASF dual-hosted git repository.
strongduanmu 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 a5e7dd66f79 Add delete multi tables with CTE SQL parser case (#38728)
a5e7dd66f79 is described below
commit a5e7dd66f79fea97a147ad698af0c2eb15b38b39
Author: ZhangCheng <[email protected]>
AuthorDate: Wed May 27 15:31:08 2026 +0800
Add delete multi tables with CTE SQL parser case (#38728)
---
.../parser/src/main/resources/case/dml/delete.xml | 59 +++++++++++++++++++++-
.../main/resources/sql/supported/dml/delete.xml | 1 +
2 files changed, 59 insertions(+), 1 deletion(-)
diff --git a/test/it/parser/src/main/resources/case/dml/delete.xml
b/test/it/parser/src/main/resources/case/dml/delete.xml
index b3ceee99e2d..d4cdffc705f 100644
--- a/test/it/parser/src/main/resources/case/dml/delete.xml
+++ b/test/it/parser/src/main/resources/case/dml/delete.xml
@@ -423,7 +423,64 @@
</with>
<table name="t_order" start-index="77" stop-index="83"/>
</delete>
-
+
+ <delete sql-case-id="delete_multi_tables_with_with_clause"
parameters="1900">
+ <with start-index="0" stop-index="70" literal-stop-index="73">
+ <common-table-expression name="target_orders" start-index="5"
stop-index="70" literal-stop-index="73">
+ <subquery-expression start-index="5" stop-index="70"
literal-stop-index="73">
+ <select>
+ <from>
+ <simple-table name="t_order" start-index="44"
stop-index="50" />
+ </from>
+ <projections start-index="30" stop-index="37">
+ <column-projection name="order_id"
start-index="30" stop-index="37" />
+ </projections>
+ <where start-index="52" stop-index="69"
literal-stop-index="72">
+ <expr>
+ <binary-operation-expression start-index="58"
stop-index="69" literal-stop-index="72">
+ <left>
+ <column name="order_id"
start-index="58" stop-index="65" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1900"
start-index="69" stop-index="72" />
+ <parameter-marker-expression
parameter-index="0" start-index="69" stop-index="69" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ </with>
+ <table name="o" start-index="79" stop-index="79"
literal-start-index="82" literal-stop-index="82" />
+ <table name="i" start-index="82" stop-index="82"
literal-start-index="85" literal-stop-index="85" />
+ <where start-index="152" stop-index="207" literal-start-index="155"
literal-stop-index="210">
+ <expr>
+ <in-expression start-index="158" stop-index="207"
literal-start-index="161" literal-stop-index="210">
+ <not>false</not>
+ <left>
+ <column name="order_id" start-index="158"
stop-index="167" literal-start-index="161" literal-stop-index="170">
+ <owner name="o" start-index="158" stop-index="158"
literal-start-index="161" literal-stop-index="161" />
+ </column>
+ </left>
+ <right>
+ <subquery start-index="172" stop-index="207"
literal-start-index="175" literal-stop-index="210">
+ <select>
+ <from>
+ <simple-table name="target_orders"
start-index="194" stop-index="206" literal-start-index="197"
literal-stop-index="209" />
+ </from>
+ <projections start-index="180"
stop-index="187" literal-start-index="183" literal-stop-index="190">
+ <column-projection name="order_id"
start-index="180" stop-index="187" literal-start-index="183"
literal-stop-index="190" />
+ </projections>
+ </select>
+ </subquery>
+ </right>
+ </in-expression>
+ </expr>
+ </where>
+ </delete>
+
<delete sql-case-id="delete_without_columns_with_with_clause">
<with start-index="0" stop-index="50">
<common-table-expression name="cte" start-index="5"
stop-index="50">
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/delete.xml
b/test/it/parser/src/main/resources/sql/supported/dml/delete.xml
index 9170cdc65f2..a16a743f21d 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/delete.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/delete.xml
@@ -31,6 +31,7 @@
<sql-case id="delete_with_top_percent" value="DELETE TOP(10) PERCENT FROM
t_order WHERE order_id = ?" db-types="SQLServer" />
<sql-case id="delete_with_with_clause" value="WITH cte (order_id, user_id)
AS (SELECT order_id, user_id FROM t_order) DELETE t_order FROM cte WHERE
t_order.order_id = cte.order_id" db-types="SQLServer" />
<sql-case id="delete_using_with_clause" value="WITH cte AS (SELECT
order_id FROM t_order WHERE order_id = 1000) DELETE FROM t_order USING t_order
JOIN cte ON t_order.order_id = cte.order_id" db-types="MySQL" />
+ <sql-case id="delete_multi_tables_with_with_clause" value="WITH
target_orders AS (SELECT order_id FROM t_order WHERE order_id = ?) DELETE o, i
FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE
o.order_id IN (SELECT order_id FROM target_orders)" db-types="MySQL" />
<sql-case id="delete_without_columns_with_with_clause" value="WITH cte AS
(SELECT order_id, user_id FROM t_order) DELETE t_order FROM cte WHERE
t_order.order_id = cte.order_id" db-types="SQLServer" />
<sql-case id="delete_multi_tables" value="DELETE t_order, t_order_item
from t_order, t_order_item where t_order.order_id = t_order_item.order_id and
t_order.status = ?" db-types="MySQL,Presto,Doris,Hive" />
<sql-case id="delete_multi_tables_with_using" value="DELETE from t_order,
t_order_item using t_order left join t_order_item on t_order.order_id =
t_order_item.order_id where t_order.status = ?" db-types="MySQL,Doris" />
