bugfix: fix missing table alias for on update column of image SQL (#6075

)
apache · Nov 26, 2023 · 7894473 · 7894473
1 parent 960c298
commit 7894473
Show file tree

Hide file tree

Showing 4 changed files with 38 additions and 9 deletions.
diff --git a/changes/en-us/2.x.md b/changes/en-us/2.x.md
@@ -6,7 +6,7 @@ Add changes here for all PR submitted to the 2.x branch.
 - [[#PR_NO](https://github.com/seata/seata/pull/PR_NO)] A brief and accurate description of PR
 
 ### bugfix:
-- [[#PR_NO](https://github.com/seata/seata/pull/PR_NO)] A brief and accurate description of PR
+- [[#6075](https://github.com/seata/seata/pull/6075)] fix missing table alias for on update column of image SQL
 
 ### optimize:
 - [[#6031](https://github.com/seata/seata/pull/6031)] add a check for the existence of the undolog table
@@ -21,8 +21,8 @@ Thanks to these contributors for their code commits. Please report an unintended
 
 <!-- Please make sure your Github ID is in the list below -->
 - [slievrly](https://github.com/slievrly)
+- [ptyin](https://github.com/ptyin)
 - [laywin](https://github.com/laywin)
 - [imcmai](https://github.com/imcmai)
 
-
 Also, we receive many valuable issues, questions and advices from our community. Thanks for you all.
diff --git a/changes/zh-cn/2.x.md b/changes/zh-cn/2.x.md
@@ -6,7 +6,7 @@
 - [[#PR_NO](https://github.com/seata/seata/pull/PR_NO)] 准确简要的PR描述
 
 ### bugfix:
-- [[#PR_NO](https://github.com/seata/seata/pull/PR_NO)] 准确简要的PR描述
+- [[#6075](https://github.com/seata/seata/pull/6075)] 修复镜像SQL对于on update列没有添加表别名的问题
 
 ### optimize:
 - [[#6031](https://github.com/seata/seata/pull/6031)] 添加undo_log表的存在性校验
@@ -21,7 +21,9 @@
 
 <!-- 请确保您的 GitHub ID 在以下列表中 -->
 - [slievrly](https://github.com/slievrly)
+- [ptyin](https://github.com/ptyin)
 - [laywin](https://github.com/laywin)
 - [imcmai](https://github.com/imcmai)
 
+
 同时，我们收到了社区反馈的很多有价值的issue和建议，非常感谢大家。
diff --git a/rm-datasource/src/main/java/io/seata/rm/datasource/exec/BaseTransactionalExecutor.java b/rm-datasource/src/main/java/io/seata/rm/datasource/exec/BaseTransactionalExecutor.java
@@ -24,6 +24,7 @@
 import java.util.Map;
 import java.util.StringJoiner;
 import java.util.stream.Collectors;
+import java.util.stream.Stream;
 
 import io.seata.common.DefaultValues;
 import io.seata.common.exception.ShouldNeverHappenException;
@@ -535,13 +536,22 @@ protected List<String> getNeedColumns(String table, String tableAlias, List<Stri
 
             // The on update xxx columns will be auto update by db, so it's also the actually updated columns
             List<String> onUpdateColumns = tableMeta.getOnUpdateColumnsOnlyName();
+            if (StringUtils.isNotBlank(tableAlias)) {
+                onUpdateColumns = onUpdateColumns.stream()
+                        .map(onUpdateColumn -> getColumnNameWithTablePrefix(table, tableAlias, onUpdateColumn))
+                        .collect(Collectors.toList());
+            }
             onUpdateColumns.removeAll(unescapeColumns);
             needUpdateColumns.addAll(onUpdateColumns.stream()
                 .map(onUpdateColumn -> ColumnUtils.addEscape(onUpdateColumn, getDbType(), tableMeta))
                 .collect(Collectors.toList()));
         } else {
-            needUpdateColumns.addAll(tableMeta.getAllColumns().keySet().stream()
-                .map(columnName -> ColumnUtils.addEscape(columnName, getDbType(), tableMeta)).collect(Collectors.toList()));
+            Stream<String> allColumns = tableMeta.getAllColumns().keySet().stream();
+            if (StringUtils.isNotBlank(tableAlias)) {
+                allColumns = allColumns.map(columnName -> getColumnNameWithTablePrefix(table, tableAlias, columnName));
+            }
+            allColumns = allColumns.map(columnName -> ColumnUtils.addEscape(columnName, getDbType(), tableMeta));
+            allColumns.forEach(needUpdateColumns::add);
         }
         return needUpdateColumns;
     }

diff --git a/rm-datasource/src/test/java/io/seata/rm/datasource/exec/UpdateExecutorTest.java b/rm-datasource/src/test/java/io/seata/rm/datasource/exec/UpdateExecutorTest.java
@@ -18,6 +18,7 @@
 import java.lang.reflect.Field;
 import java.sql.SQLException;
 import java.sql.Types;
+import java.util.ArrayList;
 import java.util.List;
 
 import com.alibaba.druid.mock.MockStatement;
@@ -58,21 +59,25 @@ public class UpdateExecutorTest {
 
     @BeforeAll
     public static void init() {
-        List<String> returnValueColumnLabels = Lists.newArrayList("id", "name", "all");
+        List<String> returnValueColumnLabels = Lists.newArrayList("id", "name", "all", "updated");
         Object[][] returnValue = new Object[][] {
-            new Object[] {1, "Tom", "keyword"},
-            new Object[] {2, "Jack", "keyword"},
+            new Object[] {1, "Tom", "keyword", 0},
+            new Object[] {2, "Jack", "keyword", 0},
         };
         Object[][] columnMetas = new Object[][] {
             new Object[] {"", "", "table_update_executor_test", "id", Types.INTEGER, "INTEGER", 64, 0, 10, 1, "", "", 0, 0, 64, 1, "NO", "YES"},
             new Object[] {"", "", "table_update_executor_test", "name", Types.VARCHAR, "VARCHAR", 64, 0, 10, 0, "", "", 0, 0, 64, 2, "YES", "NO"},
             new Object[] {"", "", "table_update_executor_test", "ALL", Types.VARCHAR, "VARCHAR", 64, 0, 10, 0, "", "", 0, 0, 64, 2, "YES", "NO"},
+            new Object[] {"", "", "table_update_executor_test", "updated", Types.INTEGER, "INTEGER", 64, 0, 10, 0, "", "", 0, 0, 64, 2, "YES", "NO"},
         };
         Object[][] indexMetas = new Object[][] {
             new Object[] {"PRIMARY", "id", false, "", 3, 1, "A", 34},
         };
+        Object[][] onUpdateColumnsReturnValue = new Object[][] {
+            new Object[]{0, "updated", Types.INTEGER, "INTEGER", 64, 10, 0, 0}
+        };
 
-        MockDriver mockDriver = new MockDriver(returnValueColumnLabels, returnValue, columnMetas, indexMetas);
+        MockDriver mockDriver = new MockDriver(returnValueColumnLabels, returnValue, columnMetas, indexMetas, null, onUpdateColumnsReturnValue);
         DruidDataSource dataSource = new DruidDataSource();
         dataSource.setUrl("jdbc:mock:xxx");
         dataSource.setDriver(mockDriver);
@@ -108,6 +113,18 @@ public void testBeforeImage() throws SQLException {
         Assertions.assertNotNull(updateExecutor.beforeImage());
     }
 
+    @Test
+    public void testBeforeImageWithTableAlias() throws SQLException {
+        Assertions.assertNotNull(updateExecutor.beforeImage());
+
+        String sql = "update table_update_executor_test t set t.name = 'WILL' where t.id = 1";
+        List<SQLStatement> asts = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
+        MySQLUpdateRecognizer recognizer = new MySQLUpdateRecognizer(sql, asts.get(0));
+        updateExecutor = new UpdateExecutor(statementProxy, (statement, args) -> null, recognizer);
+        String builtSql = updateExecutor.buildBeforeImageSQL(updateExecutor.getTableMeta(), new ArrayList<>());
+        Assertions.assertTrue(builtSql.contains("t.updated"));
+    }
+
     @Test
     public void testKeyword() throws SQLException {
         String sql = "update table_update_executor_test set `all` = '1234' where id = 1";