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 b72a1a8ca82 Add test for insert converter. (#28449)
b72a1a8ca82 is described below
commit b72a1a8ca82538fafc7c710b8ce3557f5e6bab6e
Author: boyjoy1127 <[email protected]>
AuthorDate: Mon Sep 18 10:59:16 2023 +0800
Add test for insert converter. (#28449)
* feat: add insert converter.
* feat: add insert converter.
* feat: add insert converter.
* test: add test for insert converter.
---
.../statement/insert/InsertStatementConverter.java | 8 ++--
.../src/test/resources/converter/insert.xml | 55 ++++++++++++++++++++++
2 files changed, 60 insertions(+), 3 deletions(-)
diff --git
a/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/statement/insert/InsertStatementConverter.java
b/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/statement/insert/InsertStatementConverter.java
index e10d342e593..2a2cdc88129 100644
---
a/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/statement/insert/InsertStatementConverter.java
+++
b/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/statement/insert/InsertStatementConverter.java
@@ -68,8 +68,8 @@ public final class InsertStatementConverter implements
SQLStatementConverter<Ins
} else {
source = convertValues(insertStatement.getValues());
}
- SqlNodeList columns = convertColumn(insertStatement.getColumns());
- return new SqlInsert(SqlParserPos.ZERO, keywords, table, source,
columns);
+ SqlNodeList columnList = convertColumn(insertStatement.getColumns());
+ return new SqlInsert(SqlParserPos.ZERO, keywords, table, source,
columnList);
}
private SqlNode convertSelect(final SubquerySegment subquerySegment) {
@@ -87,7 +87,9 @@ public final class InsertStatementConverter implements
SQLStatementConverter<Ins
private SqlNode convertValues(final Collection<InsertValuesSegment>
insertValuesSegments) {
List<SqlNode> values = new ArrayList<>();
for (InsertValuesSegment each : insertValuesSegments) {
- values.add(convertExpression(each.getValues().get(0)));
+ for (ExpressionSegment value : each.getValues()) {
+ values.add(convertExpression(value));
+ }
}
List<SqlNode> operands = new ArrayList<>();
operands.add(new SqlBasicCall(new SqlRowOperator("ROW"), values,
SqlParserPos.ZERO));
diff --git a/test/it/optimizer/src/test/resources/converter/insert.xml
b/test/it/optimizer/src/test/resources/converter/insert.xml
index 7ed3870d811..65ec6621645 100644
--- a/test/it/optimizer/src/test/resources/converter/insert.xml
+++ b/test/it/optimizer/src/test/resources/converter/insert.xml
@@ -18,4 +18,59 @@
<sql-node-converter-test-cases>
<test-cases sql-case-id="insert_into_values" expected-sql="INSERT INTO
`test_nested` VALUES (1)" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_all_placeholders"
expected-sql="INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES
(?, ?, ?)" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_now_function" expected-sql="INSERT
INTO `t_order_item` (`item_id`, `order_id`, `user_id`, `status`,
`creation_date`) VALUES (?, ?, ?, 'insert', `now`())" db-types="MySQL"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_without_parameters" expected-sql="INSERT
INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (1, 1, 'insert')"
db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_special_characters"
expected-sql="INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES
(1, 1, 'insert')" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_special_syntax" expected-sql="INSERT
INTO "t_order" ("order_id", "user_id",
"status") VALUES (1, 1, 'insert')" db-types="PostgreSQL,openGauss"
sql-case-types="LITERAL" />
+ <test-cases
sql-case-id="insert_with_all_placeholders_for_table_identifier"
expected-sql="INSERT INTO "t_order"
("t_order"."order_id",
"t_order"."user_id",
"t_order"."status") VALUES (?, ?, ?)"
db-types="PostgreSQL,openGauss,Oracle" sql-case-types="PLACEHOLDER" />
+ <test-cases
sql-case-id="insert_with_all_placeholders_for_table_identifier"
expected-sql="INSERT INTO `t_order` (`t_order`.`order_id`, `t_order`.`user_id`,
`t_order`.`status`) VALUES (?, ?, ?)" db-types="MySQL"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_without_columns_with_all_placeholders"
expected-sql="INSERT INTO `t_order` VALUES (?, ?, ?)" db-types="MySQL"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_without_columns_with_all_placeholders"
expected-sql="INSERT INTO "t_order" VALUES (?, ?, ?)"
db-types="PostgreSQL,openGauss,Oracle" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_partial_placeholders"
expected-sql="INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES
(?, ?, 'insert')" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_partial_placeholders"
expected-sql="INSERT INTO "t_order" ("order_id",
"user_id", "status") VALUES (?, ?, 'insert')"
db-types="PostgreSQL,Oracle" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_generate_key_column"
expected-sql="INSERT INTO "t_order_item" ("item_id",
"order_id", "user_id", "status",
"creation_date") VALUES (?, ?, ?, 'insert', '2017-08-08')"
db-types="openGauss" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_without_generate_key_column"
expected-sql="INSERT INTO `t_order_item` (`order_id`, `user_id`, `status`,
`creation_date`) VALUES (?, ?, 'insert', '2017-08-08')" db-types="MySQL"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_without_generate_key_column"
expected-sql="INSERT INTO "t_order_item" ("order_id",
"user_id", "status", "creation_date") VALUES (?,
?, 'insert', '2017-08-08')" db-types="PostgreSQL,openGauss,Oracle"
sql-case-types="PLACEHOLDER" />
+ <test-cases
sql-case-id="insert_without_columns_and_with_generate_key_column"
expected-sql="INSERT INTO `t_order_item` VALUES (?, ?, ?, 'insert',
'2017-08-08')" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases
sql-case-id="insert_without_columns_and_with_generate_key_column"
expected-sql="INSERT INTO "t_order_item" VALUES (?, ?, ?, 'insert',
'2017-08-08')" db-types="PostgreSQL,openGauss,Oracle"
sql-case-types="PLACEHOLDER" />
+ <test-cases
sql-case-id="insert_without_columns_and_without_generate_key_column"
expected-sql="INSERT INTO `t_order_item` VALUES (?, ?, 'insert', '2017-08-08')"
db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases
sql-case-id="insert_without_columns_and_without_generate_key_column"
expected-sql="INSERT INTO "t_order_item" VALUES (?, ?, 'insert',
'2017-08-08')" db-types="PostgreSQL,openGauss,Oracle"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_batch" expected-sql="INSERT INTO
`t_order` (`order_id`, `user_id`, `status`) VALUES (?, ?, ?, ?, ?, ?)"
db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_batch" expected-sql="INSERT INTO
"t_order" ("order_id", "user_id",
"status") VALUES (?, ?, ?, ?, ?, ?)" db-types="PostgreSQL,openGauss"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_batch_and_irregular_parameters"
expected-sql="INSERT INTO "t_order" ("order_id",
"user_id", "status") VALUES (?, 1, 'insert', ?, ?, ?)"
db-types="PostgreSQL,openGauss" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_batch_and_irregular_parameters"
expected-sql="INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES
(?, 1, 'insert', ?, ?, ?)" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_batch_and_composite_expression"
expected-sql="INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES
(?, ?, `SUBSTR`(?, 1), ?, ?, `SUBSTR`(?, 1))" db-types="H2,MySQL"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_batch_and_with_generate_key_column"
expected-sql="INSERT INTO `t_order_item` (`item_id`, `order_id`, `user_id`,
`status`, `creation_date`) VALUES (?, ?, ?, 'insert', '2017-08-08', ?, ?, ?,
'insert', '2017-08-08')" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_batch_and_with_generate_key_column"
expected-sql="INSERT INTO "t_order_item" ("item_id",
"order_id", "user_id", "status",
"creation_date") VALUES (?, ?, ?, 'insert', '2017-08-08', ?, ?, ?,
'insert', '2017-08-08')" db-types="SQLServer, PostgreSQL,openGauss"
sql-case-types="PLACEHOLDER" />
+ <test-cases
sql-case-id="insert_with_batch_and_without_generate_key_column"
expected-sql="INSERT INTO `t_order_item` (`order_id`, `user_id`, `status`,
`creation_date`) VALUES (?, ?, 'insert', '2017-08-08', ?, ?, 'insert',
'2017-08-08')" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases
sql-case-id="insert_with_batch_and_without_generate_key_column"
expected-sql="INSERT INTO "t_order_item" ("order_id",
"user_id", "status", "creation_date") VALUES (?,
?, 'insert', '2017-08-08', ?, ?, 'insert', '2017-08-08')" db-types="SQLServer,
PostgreSQL,openGauss" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_multiple_values" expected-sql="INSERT
INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (1, 1, 'insert', 2, 2,
'insert2')" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_one_auto_increment_column"
expected-sql="INSERT INTO `t_auto_increment_table` VALUES ()" db-types="MySQL"
sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_double_value" expected-sql="INSERT
INTO `t_double_test` (`col1`) VALUES (1.22)" db-types="MySQL"
sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_null_value" expected-sql="INSERT INTO
`t_null_value_test` (`col1`) VALUES (NULL)" db-types="MySQL"
sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_function" expected-sql="INSERT INTO
`t_order` (`present_date`, `order_id`, `user_id`) VALUES (`curdate`(), ?, ?)"
db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_unix_timestamp_function"
expected-sql="INSERT INTO `t_order` (`status`, `order_id`, `user_id`) VALUES
(`unix_timestamp`(?), ?, ?)" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_str_to_date" expected-sql="INSERT
INTO `t_order` (`present_date`, `order_id`, `user_id`) VALUES (`str_to_date`(?,
'%Y-%m-%d'), ?, ?)" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_str_date_add" expected-sql="INSERT
INTO `t_order` (`present_date`, `order_id`, `user_id`) VALUES
(`date_add`(`now`(), `interval`(?, 'second')), ?, ?)" db-types="MySQL"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_select_with_all_columns"
expected-sql="INSERT INTO "t_order" ("order_id",
"user_id", "status") SELECT "order_id",
"user_id", "status" FROM "t_order" WHERE
"order_id" = ?" db-types="PostgreSQL,openGauss"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_select_with_all_columns"
expected-sql="INSERT INTO `t_order` (`order_id`, `user_id`, `status`) SELECT
`order_id`, `user_id`, `status` FROM `t_order` WHERE `order_id` = ?"
db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_select_without_columns"
expected-sql="INSERT INTO `t_order` SELECT `order_id`, `user_id`, `status` FROM
`t_order` WHERE `order_id` = ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_select_without_columns"
expected-sql="INSERT INTO "t_order" SELECT "order_id",
"user_id", "status" FROM "t_order" WHERE
"order_id" = ?" db-types="PostgreSQL,openGauss"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_select_with_generate_key_column"
expected-sql="INSERT INTO `t_order_item` (`item_id`, `order_id`, `user_id`,
`status`, `creation_date`) SELECT `item_id`, `order_id`, `user_id`, 'insert',
'2017-08-08' FROM `t_order_item` WHERE `item_id` = ?" db-types="MySQL"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_select_without_generate_key_column"
expected-sql="INSERT INTO `t_order_item` (`order_id`, `user_id`, `status`,
`creation_date`) SELECT `order_id`, `user_id`, 'insert', '2017-08-08' FROM
`t_order_item` WHERE `order_id` = ?" db-types="MySQL"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_emoji_value" expected-sql="INSERT
INTO `t_emoji_test` (`col1`) VALUES (u&'test\d83d\de00')" db-types="MySQL"
sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_without_into_keyword" expected-sql="INSERT
INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (?, ?, ?)"
db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_uuid_column" expected-sql="INSERT
INTO "t_order" ("id", "uuid") VALUES (?, ?)"
db-types="PostgreSQL,openGauss" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_without_columns" expected-sql="INSERT INTO
"departments" VALUES (280, 'Recreation', 121, 1700)"
db-types="Oracle" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_select_subquery" expected-sql="INSERT
INTO "bonuses" SELECT "employee_id", "salary" *
1.1 FROM "employees" WHERE "commission_pct" > 0.25"
db-types="Oracle" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_rank_column" expected-sql="INSERT
INTO "sales" ("rank") VALUES (1)" db-types="Oracle"
sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_schema" expected-sql="INSERT INTO
`db1`.`t_order` VALUES (1, 2, 3)" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_schema" expected-sql="INSERT INTO
"db1"."t_order" VALUES (1, 2, 3)"
db-types="PostgreSQL,openGauss,Oracle" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_negative_value" expected-sql="INSERT
INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (?, ?, ?)"
db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_with_negative_value" expected-sql="INSERT
INTO "t_order" ("order_id", "user_id",
"status") VALUES (?, ?, ?)" db-types="PostgreSQL,openGauss,Oracle"
sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="insert_datetime_literals" expected-sql="INSERT
INTO "date_tab" VALUES ('1999-12-01 10:00:00', '1999-12-01 10:00:00',
'1999-12-01 10:00:00')" db-types="Oracle" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_content_keyword" expected-sql="INSERT
INTO "SYS_MQ_MSG" ("ID", "CONTENT") VALUES (1,
'test')" db-types="Oracle" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_connect_by_and_prior"
expected-sql="INSERT INTO "t" ("c1", "c2",
"c3", "c4", "c5") SELECT "c1",
"c2", "regexp_substr"("c3", '[^,]+', 1,
"l") "c3", "c4", "c5" FROM
"t" WHERE "id" = 1" db-types="Oracle"
sql-case-types="LITERAL" />
+ <test-cases sql-case-id="insert_with_national_character_set"
expected-sql="INSERT INTO "customers" VALUES (1000,
"TO_NCHAR"('John Smith'), '''500 Oracle Parkway',
"sysdate")" db-types="Oracle" sql-case-types="LITERAL" />
</sql-node-converter-test-cases>
