You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2025/03/01 04:08:44 UTC
(shardingsphere) branch master updated: Support With Clause in Delete Statement in MySQL. (#34817)
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 53f2f69dc75 Support With Clause in Delete Statement in MySQL. (#34817)
53f2f69dc75 is described below
commit 53f2f69dc758d959814b53facccf4069a0093c3e
Author: Yash-cor <ya...@coriolis.co.in>
AuthorDate: Sat Mar 1 09:38:38 2025 +0530
Support With Clause in Delete Statement in MySQL. (#34817)
* Support With Segment in Delete Statement in MySQL.
* Update Release Notes.
---
RELEASE-NOTES.md | 1 +
.../src/main/antlr4/imports/mysql/DMLStatement.g4 | 2 +-
.../visitor/statement/MySQLStatementVisitor.java | 3 +++
.../statement/mysql/dml/MySQLDeleteStatement.java | 8 ++++++
.../parser/src/main/resources/case/dml/delete.xml | 31 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/delete.xml | 1 +
6 files changed, 45 insertions(+), 1 deletion(-)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index 5aa1b15adcd..1e49cbeb665 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -23,6 +23,7 @@
1. SQL Parser: Support MySQL SELECT CAST AS YEAR statement parse - [#34638](https://github.com/apache/shardingsphere/pull/34638)
1. SQL Parser: Support MySQL SELECT MAX(ALL expr) statement parse - [#34639](https://github.com/apache/shardingsphere/pull/34639)
1. SQL Parser: Support MySQL INSERT with GEOMCOLLECTION function parse - [#34654](https://github.com/apache/shardingsphere/pull/34654)
+1. SQL Parser: Support MySQL DELETE with statement parse - [#34817](https://github.com/apache/shardingsphere/pull/34817)
### Bug Fixes
diff --git a/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4 b/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4
index c1d11cb372e..c4554e00a59 100644
--- a/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4
+++ b/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4
@@ -105,7 +105,7 @@ blobValue
;
delete
- : DELETE deleteSpecification (singleTableClause | multipleTablesClause) whereClause? orderByClause? limitClause? returningClause?
+ : withClause? DELETE deleteSpecification (singleTableClause | multipleTablesClause) whereClause? orderByClause? limitClause? returningClause?
;
deleteSpecification
diff --git a/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java b/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
index 97acc56b503..d098d9757cc 100644
--- a/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
+++ b/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
@@ -1649,6 +1649,9 @@ public abstract class MySQLStatementVisitor extends MySQLStatementBaseVisitor<AS
if (null != ctx.returningClause()) {
result.setReturningSegment((ReturningSegment) visit(ctx.returningClause()));
}
+ if (null != ctx.withClause()) {
+ result.setWithSegment((WithSegment) visit(ctx.withClause()));
+ }
return result;
}
diff --git a/parser/sql/statement/type/mysql/src/main/java/org/apache/shardingsphere/sql/parser/statement/mysql/dml/MySQLDeleteStatement.java b/parser/sql/statement/type/mysql/src/main/java/org/apache/shardingsphere/sql/parser/statement/mysql/dml/MySQLDeleteStatement.java
index 510f38682c6..35bd0b8c22a 100644
--- a/parser/sql/statement/type/mysql/src/main/java/org/apache/shardingsphere/sql/parser/statement/mysql/dml/MySQLDeleteStatement.java
+++ b/parser/sql/statement/type/mysql/src/main/java/org/apache/shardingsphere/sql/parser/statement/mysql/dml/MySQLDeleteStatement.java
@@ -21,6 +21,7 @@ import lombok.Setter;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.ReturningSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.OrderBySegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.pagination.limit.LimitSegment;
+import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.WithSegment;
import org.apache.shardingsphere.sql.parser.statement.core.statement.dml.DeleteStatement;
import org.apache.shardingsphere.sql.parser.statement.mysql.MySQLStatement;
@@ -38,6 +39,8 @@ public final class MySQLDeleteStatement extends DeleteStatement implements MySQL
private ReturningSegment returningSegment;
+ private WithSegment withSegment;
+
@Override
public Optional<OrderBySegment> getOrderBy() {
return Optional.ofNullable(orderBy);
@@ -52,4 +55,9 @@ public final class MySQLDeleteStatement extends DeleteStatement implements MySQL
public Optional<ReturningSegment> getReturningSegment() {
return Optional.ofNullable(returningSegment);
}
+
+ @Override
+ public Optional<WithSegment> getWithSegment() {
+ return Optional.ofNullable(withSegment);
+ }
}
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 6cf5229359e..01fc19d2a14 100644
--- a/test/it/parser/src/main/resources/case/dml/delete.xml
+++ b/test/it/parser/src/main/resources/case/dml/delete.xml
@@ -393,6 +393,37 @@
</where>
</delete>
+ <delete sql-case-id="delete_using_with_clause">
+ <with start-index="0" stop-index="63">
+ <common-table-expression name = "cte" start-index="5" stop-index="63">
+ <subquery-expression start-index="5" stop-index="63">
+ <select>
+ <from>
+ <simple-table name="t_order" start-index="34" stop-index="40" />
+ </from>
+ <projections start-index="20" stop-index="27">
+ <column-projection name="order_id" start-index="20" stop-index="27" />
+ </projections>
+ <where start-index="42" stop-index="62">
+ <expr>
+ <binary-operation-expression start-index="48" stop-index="62">
+ <left>
+ <column name="order_id" start-index="48" stop-index="55"/>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1000" start-index="59" stop-index="62"/>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ </with>
+ <table name="t_order" start-index="77" stop-index="83"/>
+ </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 db5e92fdfe2..a98fa1fd402 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
@@ -30,6 +30,7 @@
<sql-case id="delete_with_top" value="DELETE TOP(10) FROM t_order WHERE order_id = ?" db-types="SQLServer" />
<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_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" />
<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" />