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 1c56aad2fc7 add test cases (#28289)
1c56aad2fc7 is described below
commit 1c56aad2fc7ef49d1506de326fad19388c46f5a1
Author: Yunbo Ni <[email protected]>
AuthorDate: Mon Sep 4 17:49:29 2023 +0800
add test cases (#28289)
* feat: case expression test case
* feat: collate test cases
* feat: trim test cases
* feat: spatial function test case
* fix: function name test case
* feat: date format function test case
---------
Co-authored-by: Zhengqiang Duan <[email protected]>
---
test/it/optimizer/src/test/resources/converter/select-expression.xml | 3 +++
.../optimizer/src/test/resources/converter/select-special-function.xml | 2 ++
test/it/optimizer/src/test/resources/converter/select.xml | 3 +++
3 files changed, 8 insertions(+)
diff --git
a/test/it/optimizer/src/test/resources/converter/select-expression.xml
b/test/it/optimizer/src/test/resources/converter/select-expression.xml
index ed2fcc3affa..c414191201e 100644
--- a/test/it/optimizer/src/test/resources/converter/select-expression.xml
+++ b/test/it/optimizer/src/test/resources/converter/select-expression.xml
@@ -49,6 +49,9 @@
<test-cases sql-case-id="select_where_with_predicate_with_in_subquery"
expected-sql="SELECT * FROM "t_order" WHERE
"t_order"."order_id" NOT IN (SELECT "order_id"
FROM "t_order_item" WHERE "status" > 1)"
db-types="PostgreSQL, openGauss" sql-case-types="LITERAL" />
<test-cases sql-case-id="select_where_with_predicate_with_in_subquery"
expected-sql="SELECT * FROM "t_order" WHERE
"t_order"."order_id" NOT IN (SELECT "order_id"
FROM "t_order_item" WHERE "status" > ?)"
db-types="PostgreSQL, openGauss" sql-case-types="PLACEHOLDER" />
<test-cases sql-case-id="select_where_with_simple_expr_with_collate"
expected-sql="SELECT * FROM `t_order` WHERE `order_id` COLLATE
'utf8mb4_0900_ai_ci'" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_with_case_expression" expected-sql="SELECT
`t`.*, `o`.`item_id` AS `item_id`, CASE WHEN `t`.`status` = 'init' THEN
u&'\5df2\542f\7528' WHEN `t`.`status` = 'failed' THEN
u&'\5df2\505c\7528' ELSE 'NULL' END AS `stateName` FROM `t_order` AS `t`
LEFT JOIN `t_order_item` AS `o` ON `o`.`order_id` = `t`.`order_id` WHERE
`t`.`order_id` = 1000 LIMIT 1" db-types="MySQL,H2" />
+ <test-cases sql-case-id="select_with_collate_with_marker"
expected-sql="SELECT * FROM `t_order` WHERE `order_id` COLLATE ?"
db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_with_collate_with_marker"
expected-sql="SELECT * FROM `t_order` WHERE `order_id` COLLATE 'latin1_bin'"
db-types="MySQL" sql-case-types="LITERAL" />
<test-cases sql-case-id="select_where_with_simple_expr_with_row"
expected-sql="SELECT * FROM `t_order` WHERE (?, `now`()) = (`order_id`,
`status`)" db-types="MySQL" sql-case-types="PLACEHOLDER" />
<test-cases sql-case-id="select_where_with_simple_expr_with_row"
expected-sql="SELECT * FROM `t_order` WHERE ('abc', `now`()) = (`order_id`,
`status`)" db-types="MySQL" sql-case-types="LITERAL" />
<test-cases sql-case-id="select_where_with_simple_expr_with_not"
expected-sql="SELECT * FROM `t_order` WHERE ! `t_order`.`order_id`"
db-types="MySQL" />
diff --git
a/test/it/optimizer/src/test/resources/converter/select-special-function.xml
b/test/it/optimizer/src/test/resources/converter/select-special-function.xml
index ed4357793f0..38f417e54f2 100644
--- a/test/it/optimizer/src/test/resources/converter/select-special-function.xml
+++ b/test/it/optimizer/src/test/resources/converter/select-special-function.xml
@@ -18,5 +18,7 @@
<sql-node-converter-test-cases>
<test-cases sql-case-id="select_extract_function" expected-sql="SELECT
EXTRACT("YEAR" FROM CAST('2001-02-16 20:38:40' AS TIMESTAMP))"
db-types="PostgreSQL,openGauss" />
+ <test-cases sql-case-id="select_with_trim_expr" expected-sql="SELECT
TRIM(BOTH '#' FROM `name`) FROM `t_order`" db-types="MySQL" />
+ <test-cases sql-case-id="select_with_trim_expr_from_expr"
expected-sql="SELECT TRIM(BOTH `remove_name` FROM `name`) FROM `t_order`"
db-types="MySQL" />
<test-cases sql-case-id="select_window_function" expected-sql="SELECT
`order_id`, ROW_NUMBER() OVER () FROM `t_order`" db-types="MySQL" />
</sql-node-converter-test-cases>
diff --git a/test/it/optimizer/src/test/resources/converter/select.xml
b/test/it/optimizer/src/test/resources/converter/select.xml
index a17523123ca..e16ff8d319e 100644
--- a/test/it/optimizer/src/test/resources/converter/select.xml
+++ b/test/it/optimizer/src/test/resources/converter/select.xml
@@ -20,6 +20,9 @@
<test-cases sql-case-id="select_string_constant_type_cast"
expected-sql="SELECT CAST('1' AS INTEGER), CAST('2' AS DECIMAL)"
dbtypes="openGauss,PostgreSQL" />
<test-cases
sql-case-id="select_with_database_name_and_schema_name_in_table"
expected-sql="SELECT "order_id" FROM
"sharding_db"."public"."t_order" WHERE
"user_id" = ? AND "order_id" = ?"
db-types="PostgreSQL,openGauss" sql-case-types="PLACEHOLDER" />
<test-cases
sql-case-id="select_with_database_name_and_schema_name_in_table"
expected-sql="SELECT "order_id" FROM
"sharding_db"."public"."t_order" WHERE
"user_id" = 1 AND "order_id" = 1"
db-types="PostgreSQL,openGauss" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_with_spatial_function"
expected-sql="SELECT * FROM `t_order` WHERE
`ST_DISTANCE_SPHERE`(`POINT`(113.358772, 23.1273723), `POINT`(`user_id`,
`order_id`)) <> 0" db-types="MySQL" />
+ <test-cases sql-case-id="select_with_function_name" expected-sql="SELECT
CURRENT_TIMESTAMP" db-types="MySQL" />
+ <test-cases sql-case-id="select_with_date_format_function"
expected-sql="SELECT * FROM `t_order` WHERE `DATE_FORMAT`(CURRENT_DATE,
'%Y-%m-%d') = '2019-12-18'" db-types="MySQL" />
<test-cases sql-case-id="select_with_schema_name_in_shorthand_projection"
expected-sql="SELECT `sharding_db`.`t_order`.* FROM `t_order` WHERE `user_id` =
? AND `order_id` = ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
<test-cases sql-case-id="select_with_schema_name_in_shorthand_projection"
expected-sql="SELECT `sharding_db`.`t_order`.* FROM `t_order` WHERE `user_id` =
1 AND `order_id` = 1" db-types="MySQL" sql-case-types="LITERAL" />
<test-cases sql-case-id="select_with_schema_name_in_column_projection"
expected-sql="SELECT `sharding_db`.`t_order`.`order_id` FROM `t_order` WHERE
`user_id` = ? AND `order_id` = ?" db-types="MySQL" sql-case-types="PLACEHOLDER"
/>
