You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2024/03/07 08:19:48 UTC
(doris) 15/38: [regression test] Test the unique model by modify a value type from TINYINT to other type (#31682)
This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
commit 1d9e9fc88439538ee27a960962873d31a7aaa09d
Author: kkop <45...@users.noreply.github.com>
AuthorDate: Wed Mar 6 20:55:40 2024 +0800
[regression test] Test the unique model by modify a value type from TINYINT to other type (#31682)
---
.../test_unique_model_schema_value_change.out | 80 +++++++++++
.../test_unique_model_schema_value_change.groovy | 155 +++++++++++++++++++++
2 files changed, 235 insertions(+)
diff --git a/regression-test/data/schema_change_p0/test_unique_model_schema_value_change.out b/regression-test/data/schema_change_p0/test_unique_model_schema_value_change.out
index 7a089233c2f..c8d4de184ea 100644
--- a/regression-test/data/schema_change_p0/test_unique_model_schema_value_change.out
+++ b/regression-test/data/schema_change_p0/test_unique_model_schema_value_change.out
@@ -139,3 +139,83 @@
789012345 Grace \N Xian 29 0 13333333333 No. 222 Street, Xian 2022-07-07T22:00
123456689 Alice {"k1":"v31","k2":300} Yaan 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+-- ! --
+123456789 Alice 1 Beijing 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+234567890 Bob 1 Shanghai 30 1 13998765432 No. 456 Street, Shanghai 2022-02-02T12:00
+345678901 Carol 1 Guangzhou 28 0 13724681357 No. 789 Street, Guangzhou 2022-03-03T14:00
+456789012 Dave 1 Shenzhen 35 1 13680864279 No. 987 Street, Shenzhen 2022-04-04T16:00
+567890123 Eve 0 Chengdu 27 0 13572468091 No. 654 Street, Chengdu 2022-05-05T18:00
+678901234 Frank 0 Hangzhou 32 1 13467985213 No. 321 Street, Hangzhou 2022-06-06T20:00
+789012345 Grace 1 Xian 29 0 13333333333 No. 222 Street, Xian 2022-07-07T22:00
+123456689 Alice 1 Yaan 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+
+-- ! --
+123456789 Alice 1 Beijing 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+234567890 Bob 1 Shanghai 30 1 13998765432 No. 456 Street, Shanghai 2022-02-02T12:00
+345678901 Carol 1 Guangzhou 28 0 13724681357 No. 789 Street, Guangzhou 2022-03-03T14:00
+456789012 Dave 1 Shenzhen 35 1 13680864279 No. 987 Street, Shenzhen 2022-04-04T16:00
+567890123 Eve 0 Chengdu 27 0 13572468091 No. 654 Street, Chengdu 2022-05-05T18:00
+678901234 Frank 0 Hangzhou 32 1 13467985213 No. 321 Street, Hangzhou 2022-06-06T20:00
+789012345 Grace 1 Xian 29 0 13333333333 No. 222 Street, Xian 2022-07-07T22:00
+123456689 Alice 1 Yaan 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+
+-- ! --
+123456789 Alice 1 Beijing 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+234567890 Bob 1 Shanghai 30 1 13998765432 No. 456 Street, Shanghai 2022-02-02T12:00
+345678901 Carol 1 Guangzhou 28 0 13724681357 No. 789 Street, Guangzhou 2022-03-03T14:00
+456789012 Dave 1 Shenzhen 35 1 13680864279 No. 987 Street, Shenzhen 2022-04-04T16:00
+567890123 Eve 0 Chengdu 27 0 13572468091 No. 654 Street, Chengdu 2022-05-05T18:00
+678901234 Frank 0 Hangzhou 32 1 13467985213 No. 321 Street, Hangzhou 2022-06-06T20:00
+789012345 Grace 1 Xian 29 0 13333333333 No. 222 Street, Xian 2022-07-07T22:00
+123456689 Alice 1 Yaan 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+
+-- ! --
+123456789 Alice 1 Beijing 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+234567890 Bob 1 Shanghai 30 1 13998765432 No. 456 Street, Shanghai 2022-02-02T12:00
+345678901 Carol 1 Guangzhou 28 0 13724681357 No. 789 Street, Guangzhou 2022-03-03T14:00
+456789012 Dave 1 Shenzhen 35 1 13680864279 No. 987 Street, Shenzhen 2022-04-04T16:00
+567890123 Eve 0 Chengdu 27 0 13572468091 No. 654 Street, Chengdu 2022-05-05T18:00
+678901234 Frank 0 Hangzhou 32 1 13467985213 No. 321 Street, Hangzhou 2022-06-06T20:00
+789012345 Grace 1 Xian 29 0 13333333333 No. 222 Street, Xian 2022-07-07T22:00
+123456689 Alice 1 Yaan 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+
+-- ! --
+123456789 Alice 1.0 Beijing 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+234567890 Bob 1.0 Shanghai 30 1 13998765432 No. 456 Street, Shanghai 2022-02-02T12:00
+345678901 Carol 1.0 Guangzhou 28 0 13724681357 No. 789 Street, Guangzhou 2022-03-03T14:00
+456789012 Dave 1.0 Shenzhen 35 1 13680864279 No. 987 Street, Shenzhen 2022-04-04T16:00
+567890123 Eve 0.0 Chengdu 27 0 13572468091 No. 654 Street, Chengdu 2022-05-05T18:00
+678901234 Frank 0.0 Hangzhou 32 1 13467985213 No. 321 Street, Hangzhou 2022-06-06T20:00
+789012345 Grace 1.0 Xian 29 0 13333333333 No. 222 Street, Xian 2022-07-07T22:00
+123456689 Alice 1.2 Yaan 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+
+-- ! --
+123456789 Alice 1.0 Beijing 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+234567890 Bob 1.0 Shanghai 30 1 13998765432 No. 456 Street, Shanghai 2022-02-02T12:00
+345678901 Carol 1.0 Guangzhou 28 0 13724681357 No. 789 Street, Guangzhou 2022-03-03T14:00
+456789012 Dave 1.0 Shenzhen 35 1 13680864279 No. 987 Street, Shenzhen 2022-04-04T16:00
+567890123 Eve 0.0 Chengdu 27 0 13572468091 No. 654 Street, Chengdu 2022-05-05T18:00
+678901234 Frank 0.0 Hangzhou 32 1 13467985213 No. 321 Street, Hangzhou 2022-06-06T20:00
+789012345 Grace 1.0 Xian 29 0 13333333333 No. 222 Street, Xian 2022-07-07T22:00
+123456689 Alice 1.23 Yaan 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+
+-- ! --
+123456789 Alice 1 Beijing 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+234567890 Bob 1 Shanghai 30 1 13998765432 No. 456 Street, Shanghai 2022-02-02T12:00
+345678901 Carol 1 Guangzhou 28 0 13724681357 No. 789 Street, Guangzhou 2022-03-03T14:00
+456789012 Dave 1 Shenzhen 35 1 13680864279 No. 987 Street, Shenzhen 2022-04-04T16:00
+567890123 Eve 0 Chengdu 27 0 13572468091 No. 654 Street, Chengdu 2022-05-05T18:00
+678901234 Frank 0 Hangzhou 32 1 13467985213 No. 321 Street, Hangzhou 2022-06-06T20:00
+789012345 Grace 1 Xian 29 0 13333333333 No. 222 Street, Xian 2022-07-07T22:00
+123456689 Alice asd Yaan 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+
+-- ! --
+123456789 Alice 1 Beijing 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+234567890 Bob 1 Shanghai 30 1 13998765432 No. 456 Street, Shanghai 2022-02-02T12:00
+345678901 Carol 1 Guangzhou 28 0 13724681357 No. 789 Street, Guangzhou 2022-03-03T14:00
+456789012 Dave 1 Shenzhen 35 1 13680864279 No. 987 Street, Shenzhen 2022-04-04T16:00
+567890123 Eve 0 Chengdu 27 0 13572468091 No. 654 Street, Chengdu 2022-05-05T18:00
+678901234 Frank 0 Hangzhou 32 1 13467985213 No. 321 Street, Hangzhou 2022-06-06T20:00
+789012345 Grace 1 Xian 29 0 13333333333 No. 222 Street, Xian 2022-07-07T22:00
+123456689 Alice asd Yaan 25 0 13812345678 No. 123 Street, Beijing 2022-01-01T10:00
+
diff --git a/regression-test/suites/schema_change_p0/test_unique_model_schema_value_change.groovy b/regression-test/suites/schema_change_p0/test_unique_model_schema_value_change.groovy
index a8ef1265ab0..c573624b26f 100644
--- a/regression-test/suites/schema_change_p0/test_unique_model_schema_value_change.groovy
+++ b/regression-test/suites/schema_change_p0/test_unique_model_schema_value_change.groovy
@@ -411,4 +411,159 @@ suite("test_unique_model_schema_value_change","p0") {
sql getTableStatusSql
time 60
}, insertSql, true,"${tbName}")*/
+
+
+
+ /**
+ * Test the unique model by modify a value type from TINYINT to other type
+ */
+
+ initTable = " CREATE TABLE IF NOT EXISTS ${tbName}\n" +
+ " (\n" +
+ " `user_id` LARGEINT NOT NULL COMMENT \"用户id\",\n" +
+ " `username` VARCHAR(50) NOT NULL COMMENT \"用户昵称\",\n" +
+ " `is_student` TINYINT COMMENT \"是否是学生\",\n" +
+ " `city` VARCHAR(20) COMMENT \"用户所在城市\",\n" +
+ " `age` SMALLINT COMMENT \"用户年龄\",\n" +
+ " `sex` TINYINT COMMENT \"用户性别\",\n" +
+ " `phone` LARGEINT COMMENT \"用户电话\",\n" +
+ " `address` VARCHAR(500) COMMENT \"用户地址\",\n" +
+ " `register_time` DATETIME COMMENT \"用户注册时间\"\n" +
+ " )\n" +
+ " UNIQUE KEY(`user_id`, `username`)\n" +
+ " DISTRIBUTED BY HASH(`user_id`) BUCKETS 1\n" +
+ " PROPERTIES (\n" +
+ " \"replication_allocation\" = \"tag.location.default: 1\",\n" +
+ " \"enable_unique_key_merge_on_write\" = \"true\"\n" +
+ " );"
+
+ initTableData = "insert into ${tbName} values(123456789, 'Alice', 1, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 1, 'Shanghai', 30, 1, 13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 1, 'Guangzhou', 28, 0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 1, 'Shenzhen', 35, 1, 13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 0, 'Chengdu', 27, 0, 13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 0, 'Hangzhou', 32, 1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (789012345, 'Grace', 1, 'Xian', 29, 0, 13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+
+ //Test the unique model by modify a value type from TINYINT to BOOLEAN
+ //TODO java.sql.SQLException: errCode = 2, detailMessage = Can not change TINYINT to BOOLEAN
+/* sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student BOOLEAN DEFAULT "true" """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', false, 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName}")*/
+
+
+
+ //Test the unique model by modify a value type from TINYINT to SMALLINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student SMALLINT """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 1, 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName}")
+
+
+ //Test the unique model by modify a value type from TINYINT to INT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student INT """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 1, 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName}")
+
+
+ //Test the unique model by modify a value type from TINYINT to BIGINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student BIGINT """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 1, 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName}")
+
+ //Test the unique model by modify a value type from TINYINT to LARGEINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student LARGEINT """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 1, 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName}")
+
+
+ //Test the unique model by modify a value type from TINYINT to FLOAT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student FLOAT """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 1.2, 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName}")
+
+ //Test the unique model by modify a value type from TINYINT to DOUBLE
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student DOUBLE """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 1.23, 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName}")
+
+
+ //TODO Test the unique model by modify a value type from TINYINT to DECIMAL32
+ //java.sql.SQLException: errCode = 2, detailMessage = Can not change TINYINT to DECIMAL32
+/* sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student DECIMAL32 """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 1.23, 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName}")*/
+
+ //TODO Test the unique model by modify a value type from TINYINT to CHAR
+ //java.sql.SQLException: errCode = 2, detailMessage = Can not change TINYINT to CHAR
+/* sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student CHAR(15) """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 'asd', 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName}")*/
+
+
+
+ //Test the unique model by modify a value type from TINYINT to VARCHAR
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student VARCHAR(100) """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 'asd', 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName}")
+
+
+ //Test the unique model by modify a value type from TINYINT to STRING
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student STRING """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 'asd', 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName}")
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org