[shardingsphere] branch master updated: Add update converter test (#28218)

[duanzhengqiang]

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 e32370e3102 Add update converter test (#28218)
e32370e3102 is described below

commit e32370e3102f9864dd5ae1dba3ec094e12a45332
Author: niu niu <zihaoa...@aliyun.com>
AuthorDate: Tue Aug 22 16:53:43 2023 +0800

    Add update converter test (#28218)
    
    * Add update converter test
    
    * Change test case
---
 .../src/test/resources/converter/update.xml        | 40 ++++++++++++++++++++++
 1 file changed, 40 insertions(+)

diff --git a/test/it/optimizer/src/test/resources/converter/update.xml 
b/test/it/optimizer/src/test/resources/converter/update.xml
index d91fdd9054b..3588c00b9de 100644
--- a/test/it/optimizer/src/test/resources/converter/update.xml
+++ b/test/it/optimizer/src/test/resources/converter/update.xml
@@ -21,4 +21,44 @@
     <test-cases sql-case-id="update_without_alias" expected-sql="UPDATE 
`t_order` SET `status` = ? WHERE `order_id` = ? AND `user_id` = ?" 
db-types="MySQL" sql-case-types="PLACEHOLDER" />
     <test-cases sql-case-id="update_without_alias" expected-sql="UPDATE 
&quot;t_order&quot; SET &quot;status&quot; = 'update' WHERE 
&quot;order_id&quot; = 1 AND &quot;user_id&quot; = 1" db-types="PostgreSQL, 
openGauss, Oracle" sql-case-types="LITERAL" />
     <test-cases sql-case-id="update_without_alias" expected-sql="UPDATE 
&quot;t_order&quot; SET &quot;status&quot; = ? WHERE &quot;order_id&quot; = ? 
AND &quot;user_id&quot; = ?" db-types="PostgreSQL, openGauss, Oracle" 
sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_alias" expected-sql="UPDATE `t_order` 
AS `o` SET `o`.`status` = 'update' WHERE `o`.`order_id` = 1 AND `o`.`user_id` = 
1" db-types="MySQL" sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_alias" expected-sql="UPDATE `t_order` 
AS `o` SET `o`.`status` = ? WHERE `o`.`order_id` = ? AND `o`.`user_id` = ?" 
db-types="MySQL" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_alias" expected-sql="UPDATE 
&quot;t_order&quot; AS &quot;o&quot; SET &quot;o&quot;.&quot;status&quot; = 
'update' WHERE &quot;o&quot;.&quot;order_id&quot; = 1 AND 
&quot;o&quot;.&quot;user_id&quot; = 1" db-types="openGauss" 
sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_alias" expected-sql="UPDATE 
&quot;t_order&quot; AS &quot;o&quot; SET &quot;o&quot;.&quot;status&quot; = ? 
WHERE &quot;o&quot;.&quot;order_id&quot; = ? AND 
&quot;o&quot;.&quot;user_id&quot; = ?" db-types="openGauss" 
sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_unicode_escape_alias" 
expected-sql="UPDATE &quot;t_order&quot; AS &quot;u&quot; SET 
&quot;status&quot; = 'update' WHERE &quot;u&quot;.&quot;order_id&quot; = 1 AND 
&quot;u&quot;.&quot;user_id&quot; = 1" db-types="PostgreSQL, openGauss" 
sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_unicode_escape_alias" 
expected-sql="UPDATE &quot;t_order&quot; AS &quot;u&quot; SET 
&quot;status&quot; = ? WHERE &quot;u&quot;.&quot;order_id&quot; = ? AND 
&quot;u&quot;.&quot;user_id&quot; = ?" db-types="PostgreSQL, openGauss" 
sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_without_condition" expected-sql="UPDATE 
`t_order` AS `o` SET `o`.`status` = 'finished'" db-types="MySQL" />
+    <test-cases sql-case-id="update_with_extra_keywords" expected-sql="UPDATE 
`t_order` SET `status` = 'update' WHERE `order_id` = 1 AND `user_id` = 1" 
db-types="MySQL" sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_extra_keywords" expected-sql="UPDATE 
`t_order` SET `status` = ? WHERE `order_id` = ? AND `user_id` = ?" 
db-types="MySQL" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_special_character" 
expected-sql="UPDATE `t_order` SET `status` = 'update' WHERE `order_id` = 1 AND 
`user_id` = 1" db-types="MySQL" sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_special_character" 
expected-sql="UPDATE `t_order` SET `status` = ? WHERE `order_id` = ? AND 
`user_id` = ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_without_parameters" expected-sql="UPDATE 
`t_order` SET `status` = 'update' WHERE `order_id` = 1000 AND `user_id` = 10" 
db-types="MySQL" />
+    <test-cases sql-case-id="update_without_parameters" expected-sql="UPDATE 
&quot;t_order&quot; SET &quot;status&quot; = 'update' WHERE 
&quot;order_id&quot; = 1000 AND &quot;user_id&quot; = 10" db-types="PostgreSQL, 
openGauss, Oracle" />
+    <test-cases sql-case-id="update_with_or" expected-sql="UPDATE `t_order` 
SET `status` = 'update' WHERE (`order_id` = 1000 OR `order_id` = 0) AND 
`user_id` = 10" db-types="MySQL" sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_or" expected-sql="UPDATE `t_order` 
SET `status` = 'update' WHERE (`order_id` = ? OR `order_id` = ?) AND `user_id` 
= ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_or" expected-sql="UPDATE 
&quot;t_order&quot; SET &quot;status&quot; = 'update' WHERE 
(&quot;order_id&quot; = 1000 OR &quot;order_id&quot; = 0) AND 
&quot;user_id&quot; = 10" db-types="PostgreSQL, openGauss, Oracle" 
sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_or" expected-sql="UPDATE 
&quot;t_order&quot; SET &quot;status&quot; = 'update' WHERE 
(&quot;order_id&quot; = ? OR &quot;order_id&quot; = ?) AND &quot;user_id&quot; 
= ?" db-types="PostgreSQL, openGauss, Oracle" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_set_calculation" expected-sql="UPDATE 
`t_order` SET `status` = `status` - 1 WHERE `order_id` = 2 AND `user_id` = 3" 
db-types="MySQL" sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_set_calculation" expected-sql="UPDATE 
`t_order` SET `status` = `status` - ? WHERE `order_id` = ? AND `user_id` = ?" 
db-types="MySQL" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_set_calculation" expected-sql="UPDATE 
&quot;t_order&quot; SET &quot;status&quot; = &quot;status&quot; - 1 WHERE 
&quot;order_id&quot; = 2 AND &quot;user_id&quot; = 3" db-types="PostgreSQL, 
openGauss, Oracle" sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_set_calculation" expected-sql="UPDATE 
&quot;t_order&quot; SET &quot;status&quot; = &quot;status&quot; - ? WHERE 
&quot;order_id&quot; = ? AND &quot;user_id&quot; = ?" db-types="PostgreSQL, 
openGauss, Oracle" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_where_calculation" 
expected-sql="UPDATE `t_order` SET `status` = 1 WHERE `order_id` = `order_id` - 
2 AND `user_id` = 3" db-types="MySQL" sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_where_calculation" 
expected-sql="UPDATE `t_order` SET `status` = ? WHERE `order_id` = `order_id` - 
? AND `user_id` = ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_where_calculation" 
expected-sql="UPDATE &quot;t_order&quot; SET &quot;status&quot; = 1 WHERE 
&quot;order_id&quot; = &quot;order_id&quot; - 2 AND &quot;user_id&quot; = 3" 
db-types="PostgreSQL, openGauss, Oracle" sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_where_calculation" 
expected-sql="UPDATE &quot;t_order&quot; SET &quot;status&quot; = ? WHERE 
&quot;order_id&quot; = &quot;order_id&quot; - ? AND &quot;user_id&quot; = ?" 
db-types="PostgreSQL, openGauss, Oracle" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_case_when" expected-sql="UPDATE 
`stock_freeze_detail` SET `row_status` = CASE WHEN `id` = 3 THEN 2 WHEN `id` = 
4 THEN 2 WHEN `id` = 10 THEN 2 ELSE 'NULL' END, `update_user` = CASE WHEN `id` 
= 3 THEN 'll' WHEN `id` = 4 THEN 'll' WHEN `id` = 10 THEN 'll' ELSE 'NULL' END, 
`update_time` = CASE WHEN `id` = 3 THEN '2020-08-10T17:15:25.979+0800' ELSE 
'NULL' END WHERE `tenant_id` = 'jd'" db-types="MySQL" sql-case-types="LITERAL" 
/>
+    <test-cases sql-case-id="update_with_case_when" expected-sql="UPDATE 
`stock_freeze_detail` SET `row_status` = CASE WHEN `id` = ? THEN ? WHEN `id` = 
? THEN ? WHEN `id` = ? THEN ? ELSE 'NULL' END, `update_user` = CASE WHEN `id` = 
? THEN ? WHEN `id` = ? THEN ? WHEN `id` = ? THEN ? ELSE 'NULL' END, 
`update_time` = CASE WHEN `id` = ? THEN ? ELSE 'NULL' END WHERE `tenant_id` = 
?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_order_by_row_count" 
expected-sql="UPDATE `t_order` SET `status` = 'update' WHERE `order_id` = 1 AND 
`user_id` = 1 ORDER BY `order_id` LIMIT 10" db-types="MySQL" 
sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_order_by_row_count" 
expected-sql="UPDATE `t_order` SET `status` = ? WHERE `order_id` = ? AND 
`user_id` = ? ORDER BY `order_id` LIMIT ?" db-types="MySQL" 
sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_number" expected-sql="UPDATE 
&quot;t_order&quot; SET &quot;order_id&quot; = 1 WHERE &quot;user_id&quot; = 1" 
db-types="PostgreSQL,openGauss" sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_number" expected-sql="UPDATE 
&quot;t_order&quot; SET &quot;order_id&quot; = ? WHERE &quot;user_id&quot; = ?" 
db-types="PostgreSQL,openGauss" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_set_null" expected-sql="UPDATE 
&quot;employees&quot; SET &quot;commission_pct&quot; = NULL WHERE 
&quot;job_id&quot; = 'SH_CLERK'" db-types="Oracle" />
+    <test-cases sql-case-id="update_with_set_subquery" expected-sql="UPDATE 
&quot;employees&quot; &quot;a&quot; SET &quot;department_id&quot; = (SELECT 
&quot;department_id&quot; FROM &quot;departments&quot; WHERE 
&quot;location_id&quot; = '2100')" db-types="Oracle" />
+    <test-cases sql-case-id="update_with_multiple_set" expected-sql="UPDATE 
&quot;employees&quot; SET &quot;job_id&quot; = 'SA_MAN', &quot;salary&quot; = 
1000, &quot;department_id&quot; = 120 WHERE &quot;last_name&quot; = 'Douglas 
Grant'" db-types="Oracle" />
+    <test-cases sql-case-id="update_with_force_index" expected-sql="UPDATE 
`t_order` SET `status` = 'update' WHERE `order_id` = 1" db-types="MySQL" 
sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_force_index" expected-sql="UPDATE 
`t_order` SET `status` = ? WHERE `order_id` = ?" db-types="MySQL" 
sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="update_with_subquery_using_interval" 
expected-sql="UPDATE &quot;employees&quot; &quot;a&quot; SET &quot;salary&quot; 
= (SELECT &quot;salary&quot; FROM &quot;employees&quot; WHERE 
&quot;last_name&quot; = 'Chung') WHERE &quot;last_name&quot; = 'Chung'" 
db-types="Oracle" />
+    <test-cases sql-case-id="update_with_translate_function" 
expected-sql="UPDATE &quot;translate_tab&quot; SET &quot;char_col&quot; = 
TRANSLATE(&quot;nchar_col&quot; USING 'CHAR_CS')" db-types="Oracle" />
+    <test-cases sql-case-id="update_with_dot_column_name" expected-sql="UPDATE 
&quot;employees&quot; SET &quot;salary&quot; = &quot;salary&quot; + 10 WHERE 
&quot;employee_id&quot; BETWEEN ASYMMETRIC 1 AND 10" db-types="Oracle" 
sql-case-types="LITERAL" />
+    <test-cases sql-case-id="update_with_dot_column_name" expected-sql="UPDATE 
&quot;employees&quot; SET &quot;salary&quot; = &quot;salary&quot; + ? WHERE 
&quot;employee_id&quot; BETWEEN ASYMMETRIC ? AND ?" db-types="Oracle" 
sql-case-types="PLACEHOLDER" />
 </sql-node-converter-test-cases>