You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2021/04/13 03:58:10 UTC
[shardingsphere] branch master updated: fix wrong table rewrite in
UPDATE statement SET clause (#10056)
This is an automated email from the ASF dual-hosted git repository.
panjuan 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 2c17070 fix wrong table rewrite in UPDATE statement SET clause (#10056)
2c17070 is described below
commit 2c1707039bc16db676f6be54e791af04d4dde41b
Author: Zhengqiang Duan <st...@gmail.com>
AuthorDate: Tue Apr 13 11:57:44 2021 +0800
fix wrong table rewrite in UPDATE statement SET clause (#10056)
* fix wrong table rewrite in UPDATE statement SET clause
* improve unit test for update statement
---
.../src/test/resources/sharding/update.xml | 28 +++++++++++++++++++---
.../statement/dml/UpdateStatementContextTest.java | 3 +++
.../sql/common/extractor/TableExtractor.java | 1 +
3 files changed, 29 insertions(+), 3 deletions(-)
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/update.xml b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/update.xml
index 3cde18e..16a077d 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/update.xml
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/update.xml
@@ -21,21 +21,43 @@
<input sql="UPDATE t_account SET status = ? WHERE account_id = ?" parameters="'OK', 100" />
<output sql="UPDATE t_account_0 SET status = ? WHERE account_id = ?" parameters="'OK', 100" />
</rewrite-assertion>
+
+ <rewrite-assertion id="update_with_sharding_value_with_table_for_parameters">
+ <input sql="UPDATE t_account SET t_account.status = ? WHERE t_account.account_id = ?" parameters="'OK', 100" />
+ <output sql="UPDATE t_account_0 SET t_account_0.status = ? WHERE t_account_0.account_id = ?" parameters="'OK', 100" />
+ </rewrite-assertion>
<rewrite-assertion id="update_with_sharding_value_for_literals">
<input sql="UPDATE t_account SET status = 'OK' WHERE account_id = 100" />
<output sql="UPDATE t_account_0 SET status = 'OK' WHERE account_id = 100" />
</rewrite-assertion>
+
+ <rewrite-assertion id="update_with_sharding_value_with_table_for_literals">
+ <input sql="UPDATE t_account SET t_account.status = 'OK' WHERE t_account.account_id = 100" />
+ <output sql="UPDATE t_account_0 SET t_account_0.status = 'OK' WHERE t_account_0.account_id = 100" />
+ </rewrite-assertion>
<rewrite-assertion id="update_without_sharding_value_for_parameters">
<input sql="UPDATE t_account SET status = ? WHERE amount = ?" parameters="'OK', 1000" />
<output sql="UPDATE t_account_0 SET status = ? WHERE amount = ?" parameters="'OK', 1000" />
<output sql="UPDATE t_account_1 SET status = ? WHERE amount = ?" parameters="'OK', 1000" />
</rewrite-assertion>
+
+ <rewrite-assertion id="update_without_sharding_value_with_table_for_parameters">
+ <input sql="UPDATE t_account SET t_account.status = ? WHERE t_account.amount = ?" parameters="'OK', 1000" />
+ <output sql="UPDATE t_account_0 SET t_account_0.status = ? WHERE t_account_0.amount = ?" parameters="'OK', 1000" />
+ <output sql="UPDATE t_account_1 SET t_account_1.status = ? WHERE t_account_1.amount = ?" parameters="'OK', 1000" />
+ </rewrite-assertion>
<rewrite-assertion id="update_without_sharding_value_for_literals">
- <input sql="UPDATE t_account SET status = 'OK' WHERE amount = 10" parameters="'OK', 1000" />
- <output sql="UPDATE t_account_0 SET status = 'OK' WHERE amount = 10" parameters="'OK', 1000" />
- <output sql="UPDATE t_account_1 SET status = 'OK' WHERE amount = 10" parameters="'OK', 1000" />
+ <input sql="UPDATE t_account SET status = 'OK' WHERE amount = 10" />
+ <output sql="UPDATE t_account_0 SET status = 'OK' WHERE amount = 10" />
+ <output sql="UPDATE t_account_1 SET status = 'OK' WHERE amount = 10" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="update_without_sharding_value_with_table_for_literals">
+ <input sql="UPDATE t_account SET t_account.status = 'OK' WHERE t_account.amount = 10" />
+ <output sql="UPDATE t_account_0 SET t_account_0.status = 'OK' WHERE t_account_0.amount = 10" />
+ <output sql="UPDATE t_account_1 SET t_account_1.status = 'OK' WHERE t_account_1.amount = 10" />
</rewrite-assertion>
</rewrite-assertions>
diff --git a/shardingsphere-infra/shardingsphere-infra-binder/src/test/java/org/apache/shardingsphere/infra/binder/statement/dml/UpdateStatementContextTest.java b/shardingsphere-infra/shardingsphere-infra-binder/src/test/java/org/apache/shardingsphere/infra/binder/statement/dml/UpdateStatementContextTest.java
index af678dd..e8978dc 100644
--- a/shardingsphere-infra/shardingsphere-infra-binder/src/test/java/org/apache/shardingsphere/infra/binder/statement/dml/UpdateStatementContextTest.java
+++ b/shardingsphere-infra/shardingsphere-infra-binder/src/test/java/org/apache/shardingsphere/infra/binder/statement/dml/UpdateStatementContextTest.java
@@ -17,6 +17,7 @@
package org.apache.shardingsphere.infra.binder.statement.dml;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.SetAssignmentSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BinaryOperationExpression;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.WhereSegment;
@@ -32,6 +33,7 @@ import org.mockito.Mock;
import org.mockito.junit.MockitoJUnitRunner;
import java.util.Arrays;
+import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import java.util.Optional;
@@ -66,6 +68,7 @@ public final class UpdateStatementContextTest {
UpdateStatement updateStatement = new MySQLUpdateStatement();
updateStatement.setWhere(whereSegment);
updateStatement.setTableSegment(joinTableSegment);
+ updateStatement.setSetAssignment(new SetAssignmentSegment(0, 0, Collections.emptyList()));
UpdateStatementContext actual = new UpdateStatementContext(updateStatement);
assertThat(actual.getTablesContext().getTables().stream().map(a -> a.getTableName().getIdentifier().getValue()).collect(Collectors.toList()), is(Arrays.asList("tbl_1", "tbl_2")));
assertThat(actual.getWhere(), is(Optional.of(whereSegment)));
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/extractor/TableExtractor.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/extractor/TableExtractor.java
index 4c572b0..a79086d 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/extractor/TableExtractor.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/extractor/TableExtractor.java
@@ -233,6 +233,7 @@ public final class TableExtractor {
*/
public void extractTablesFromUpdate(final UpdateStatement updateStatement) {
extractTablesFromTableSegment(updateStatement.getTableSegment());
+ updateStatement.getSetAssignment().getAssignments().forEach(each -> extractTablesFromExpression(each.getColumn()));
if (updateStatement.getWhere().isPresent()) {
extractTablesFromExpression(updateStatement.getWhere().get().getExpr());
}