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 1434d3983bbe6f5b8e266c35e2a0081028b3a292
Author: kkop <45192870+cjj2...@users.noreply.github.com>
AuthorDate: Tue Mar 5 18:58:58 2024 +0800
[enhancement](test) Test the unique model by modify a key type from TINYINT
to other type (#31713)
---
.../test_unique_model_schema_key_change.out | 50 +++++++
.../test_unique_model_schema_key_change.groovy | 154 +++++++++++++++++++++
2 files changed, 204 insertions(+)
diff --git
a/regression-test/data/schema_change_p0/test_unique_model_schema_key_change.out
b/regression-test/data/schema_change_p0/test_unique_model_schema_key_change.out
index 2419cdd18de..994426576ad 100644
---
a/regression-test/data/schema_change_p0/test_unique_model_schema_key_change.out
+++
b/regression-test/data/schema_change_p0/test_unique_model_schema_key_change.out
@@ -109,3 +109,53 @@
789012345 Grace F Xian 29 0 13333333333 No. 222
Street, Xian 2022-07-07T22:00
123456689 Alice T 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 2 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 2 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 3 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 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_key_change.groovy
b/regression-test/suites/schema_change_p0/test_unique_model_schema_key_change.groovy
index 4f59aead784..c3f54fd04e4 100644
---
a/regression-test/suites/schema_change_p0/test_unique_model_schema_key_change.groovy
+++
b/regression-test/suites/schema_change_p0/test_unique_model_schema_key_change.groovy
@@ -389,6 +389,160 @@ suite("test_unique_model_schema_key_change","p0") {
time 60
}, insertSql, true,"${tbName}")*/
+ /**
+ * Test the unique model by modify a key 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`, `is_student`)\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 key 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 key """
+ 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}")*/
+
+
+
+ //TODO Test the unique model by modify a key type from TINYINT to
SMALLINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student SMALLINT key """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 2, '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 key type from TINYINT to INT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student INT key """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 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 key type from TINYINT to BIGINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student BIGINT key """
+ insertSql = "insert into ${tbName} values(123456689, 'Alice', 3, '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 key type from TINYINT to
LARGEINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student LARGEINT key """
+ 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}")
+
+
+ //TODO Test the unique model by modify a key type from TINYINT to FLOAT
+ //java.sql.SQLException: errCode = 2, detailMessage = Float or double can
not used as a key, use decimal instead.
+/* sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student FLOAT key """
+ 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}")*/
+
+ //TODO Test the unique model by modify a key type from TINYINT to DOUBLE
+ //java.sql.SQLException: errCode = 2, detailMessage = Float or double can
not used as a key, use decimal instead.
+/* sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student DOUBLE key """
+ 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 key type from TINYINT to DECIMAL
+ //java.sql.SQLException: errCode = 2, detailMessage = Can not change
TINYINT to DECIMAL32
+/* sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student DECIMAL key """
+ 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 key 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) key """
+ 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 key type from TINYINT to VARCHAR
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student VARCHAR(100) key
"""
+ 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 key type from TINYINT to STRING
+ //java.sql.SQLException: errCode = 2, detailMessage = String Type should
not be used in key column[is_student].
+/* sql initTable
+ sql initTableData
+ sql """ alter table ${tbName} MODIFY column is_student STRING key """
+ 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-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
