This is an automated email from the ASF dual-hosted git repository.
dataroaring pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 7118d7c512e [enhancement](regression-test) dup modify case (#37111)
7118d7c512e is described below
commit 7118d7c512eeb78d85e0480c8c0f8e58f8382f83
Author: kkop <[email protected]>
AuthorDate: Thu Jul 4 09:06:09 2024 +0800
[enhancement](regression-test) dup modify case (#37111)
---
.../test_dup_schema_value_modify.out | 42 ++
.../test_dup_schema_value_modify.groovy | 567 +++++++++++++++++++++
2 files changed, 609 insertions(+)
diff --git
a/regression-test/data/schema_change_p0/test_dup_schema_value_modify.out
b/regression-test/data/schema_change_p0/test_dup_schema_value_modify.out
new file mode 100644
index 00000000000..b68ee8081d5
--- /dev/null
+++ b/regression-test/data/schema_change_p0/test_dup_schema_value_modify.out
@@ -0,0 +1,42 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- ! --
+123456789 Alice 1 Beijing 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
+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
+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
+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
+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
+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
+789012345 Grace 1 Xian 29 0 13333333333 No. 222
Street, Xian 2022-07-07T22:00
+993456689 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
+123456789 Alice 1 Beijing 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
+234567890 Bob 1 Shanghai 30 1 13998765432
No. 456 Street, Shanghai 2022-02-02T12: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
+345678901 Carol 1 Guangzhou 28 0 13724681357
No. 789 Street, Guangzhou 2022-03-03T14: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
+456789012 Dave 1 Shenzhen 35 1 13680864279
No. 987 Street, Shenzhen 2022-04-04T16: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
+567890123 Eve 0 Chengdu 27 0 13572468091 No. 654
Street, Chengdu 2022-05-05T18: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
+678901234 Frank 0 Hangzhou 32 1 13467985213
No. 321 Street, Hangzhou 2022-06-06T20: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
+789012345 Grace 1 Xian 29 0 13333333333 No. 222
Street, Xian 2022-07-07T22:00
+789012345 Grace 1 Xian 29 0 13333333333 No. 222
Street, Xian 2022-07-07T22:00
+993456689 Alice asd Yaan 25 0 13812345678 No. 123
Street, Beijing 2022-01-01T10:00
+
diff --git
a/regression-test/suites/schema_change_p0/test_dup_schema_value_modify.groovy
b/regression-test/suites/schema_change_p0/test_dup_schema_value_modify.groovy
new file mode 100644
index 00000000000..daffea3b1f4
--- /dev/null
+++
b/regression-test/suites/schema_change_p0/test_dup_schema_value_modify.groovy
@@ -0,0 +1,567 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_dup_schema_value_modify","p0") {
+ def tbName1 = "dup_model_value_change0"
+ def tbName2 = "dup_model_value_change_0"
+ //Test the dup model by adding a value column
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+ def getTableStatusSql = " SHOW ALTER TABLE COLUMN WHERE
IndexName='${tbName1}' ORDER BY createtime DESC LIMIT 1 "
+ def errorMessage=""
+ /**
+ * Test the dup model by modify a value type
+ */
+ def initTable2 = ""
+ def initTableData2 = ""
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+ def initTable = " CREATE TABLE IF NOT EXISTS ${tbName1}\n" +
+ " (\n" +
+ " `user_id` LARGEINT NOT NULL COMMENT \"用户id\",\n" +
+ " `username` VARCHAR(50) NOT NULL COMMENT
\"用户昵称\",\n" +
+ " `is_teacher` BOOLEAN 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" +
+ " DUPLICATE KEY(`user_id`, `username`)\n" +
+ " DISTRIBUTED BY HASH(`user_id`) BUCKETS 1\n" +
+ " PROPERTIES (\n" +
+ " \"replication_allocation\" = \"tag.location.default:
1\"\n" +
+ " );"
+
+ def initTableData = "insert into ${tbName1} values(123456789, 'Alice', 0,
'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01
10:00:00')," +
+ " (234567890, 'Bob', 0, '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', 0, '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', 1, 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (789012345, 'Grace', 0, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+
+ //TODO Test the dup model by modify a value type from BOOLEAN to TINYINT
+ errorMessage="errCode = 2, detailMessage = Can not change BOOLEAN to
TINYINT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_teacher TINYINT
DEFAULT "0" """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1,
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+ },errorMessage)
+
+
+ //TODO Test the dup model by modify a value type from BOOLEAN to SMALLINT
+ errorMessage="errCode = 2, detailMessage = Can not change BOOLEAN to
SMALLINT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_teacher SMALLINT
DEFAULT "0" """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1,
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+ },errorMessage)
+
+
+ //TODO Test the dup model by modify a value type from BOOLEAN to INT
+ errorMessage="errCode = 2, detailMessage = Can not change BOOLEAN to INT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_teacher INT
DEFAULT "0" """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1,
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+ },errorMessage)
+
+
+
+ //TODO Test the dup model by modify a value type from BOOLEAN to BIGINT
+ errorMessage="errCode = 2, detailMessage = Can not change BOOLEAN to
BIGINT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_teacher BIGINT
DEFAULT "0" """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1,
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+ },errorMessage)
+
+
+
+ //TODO Test the dup model by modify a value type from BOOLEAN to FLOAT
+ errorMessage="errCode = 2, detailMessage = Can not change BOOLEAN to
FLOAT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_teacher FLOAT
DEFAULT "0" """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1.0,
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+ },errorMessage)
+
+
+ //TODO Test the dup model by modify a value type from BOOLEAN to DOUBLE
+ errorMessage="errCode = 2, detailMessage = Can not change BOOLEAN to
DOUBLE"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_teacher DOUBLE
DEFAULT "0" """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1.0,
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+ },errorMessage)
+
+
+ //TODO Test the dup model by modify a value type from BOOLEAN to DECIMAL
+ errorMessage="errCode = 2, detailMessage = Can not change BOOLEAN to
DECIMAL32"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_teacher DECIMAL
DEFAULT "0" """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1.0,
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+ },errorMessage)
+
+
+ //TODO Test the dup model by modify a value type from BOOLEAN to CHAR
+ errorMessage="errCode = 2, detailMessage = Can not change BOOLEAN to CHAR"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_teacher CHAR
DEFAULT "0" """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', '1',
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+ },errorMessage)
+
+
+ //TODO Test the dup model by modify a value type from BOOLEAN to STRING
+ errorMessage="errCode = 2, detailMessage = Can not change BOOLEAN to
STRING"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_teacher STRING
DEFAULT "0" """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', '1',
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+ },errorMessage)
+
+
+ //TODO Test the dup model by modify a value type from BOOLEAN to VARCHAR
+ errorMessage="errCode = 2, detailMessage = Can not change BOOLEAN to
VARCHAR"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_teacher
VARCHAR(32) DEFAULT "0" """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', '1',
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+ },errorMessage)
+
+
+
+
+ /**
+ * Test the dup model by modify a value type from TINYINT to other type
+ */
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+ initTable = " CREATE TABLE IF NOT EXISTS ${tbName1}\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" +
+ " DUPLICATE KEY(`user_id`, `username`)\n" +
+ " DISTRIBUTED BY HASH(`user_id`) BUCKETS 1\n" +
+ " PROPERTIES (\n" +
+ " \"replication_allocation\" = \"tag.location.default:
1\"\n" +
+ " );"
+
+ initTableData = "insert into ${tbName1} 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');"
+
+ //TODO Test the dup model by modify a value type from TINYINT to BOOLEAN
+ errorMessage="errCode = 2, detailMessage = Can not change TINYINT to
BOOLEAN"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_student BOOLEAN
DEFAULT "true" """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice',
false, 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01
10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+ },errorMessage)
+
+
+
+
+ //TODO Data doubling Test the dup model by modify a value type from
TINYINT to SMALLINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_student SMALLINT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1, 'Yaan',
25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+
+
+
+
+ // Test the dup model by modify a value type from TINYINT to INT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_student INT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1, 'Yaan',
25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, false,"${tbName1}")
+
+
+ sql """ DROP TABLE IF EXISTS ${tbName2} """
+ initTable2 = " CREATE TABLE IF NOT EXISTS ${tbName2}\n" +
+ " (\n" +
+ " `user_id` LARGEINT NOT NULL COMMENT \"用户id\",\n" +
+ " `username` VARCHAR(50) NOT NULL COMMENT
\"用户昵称\",\n" +
+ " `is_student` INT 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" +
+ " DUPLICATE KEY(`user_id`, `username`)\n" +
+ " DISTRIBUTED BY HASH(`user_id`) BUCKETS 1\n" +
+ " PROPERTIES (\n" +
+ " \"replication_allocation\" = \"tag.location.default:
1\"\n" +
+ " );"
+
+ initTableData2 = "insert into ${tbName2} 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')," +
+ " (993456689, 'Alice', 1, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}","${tbName2}","is_student")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+
+ //TODO Data doubling Test the dup model by modify a value type from
TINYINT to BIGINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_student BIGINT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1, 'Yaan',
25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, false,"${tbName1}")
+
+ sql """ DROP TABLE IF EXISTS ${tbName2} """
+ initTable2 = " CREATE TABLE IF NOT EXISTS ${tbName2}\n" +
+ " (\n" +
+ " `user_id` LARGEINT NOT NULL COMMENT \"用户id\",\n" +
+ " `username` VARCHAR(50) NOT NULL COMMENT
\"用户昵称\",\n" +
+ " `is_student` BIGINT 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" +
+ " DUPLICATE KEY(`user_id`, `username`)\n" +
+ " DISTRIBUTED BY HASH(`user_id`) BUCKETS 1\n" +
+ " PROPERTIES (\n" +
+ " \"replication_allocation\" = \"tag.location.default:
1\"\n" +
+ " );"
+
+ initTableData2 = "insert into ${tbName2} 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')," +
+ " (993456689, 'Alice', 1, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}","${tbName2}","is_student")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+
+
+ //Test the dup model by modify a value type from TINYINT to LARGEINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_student LARGEINT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1, 'Yaan',
25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, false,"${tbName1}")
+
+ sql """ DROP TABLE IF EXISTS ${tbName2} """
+ initTable2 = " CREATE TABLE IF NOT EXISTS ${tbName2}\n" +
+ " (\n" +
+ " `user_id` LARGEINT NOT NULL COMMENT \"用户id\",\n" +
+ " `username` VARCHAR(50) NOT NULL COMMENT
\"用户昵称\",\n" +
+ " `is_student` LARGEINT 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" +
+ " DUPLICATE KEY(`user_id`, `username`)\n" +
+ " DISTRIBUTED BY HASH(`user_id`) BUCKETS 1\n" +
+ " PROPERTIES (\n" +
+ " \"replication_allocation\" = \"tag.location.default:
1\"\n" +
+ " );"
+
+ initTableData2 = "insert into ${tbName2} 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')," +
+ " (993456689, 'Alice', 1, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}","${tbName2}","is_student")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+ //Test the dup model by modify a value type from TINYINT to FLOAT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_student FLOAT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1.2,
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, false,"${tbName1}")
+
+ sql """ DROP TABLE IF EXISTS ${tbName2} """
+ initTable2 = " CREATE TABLE IF NOT EXISTS ${tbName2}\n" +
+ " (\n" +
+ " `user_id` LARGEINT NOT NULL COMMENT \"用户id\",\n" +
+ " `username` VARCHAR(50) NOT NULL COMMENT
\"用户昵称\",\n" +
+ " `is_student` FLOAT 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" +
+ " DUPLICATE KEY(`user_id`, `username`)\n" +
+ " DISTRIBUTED BY HASH(`user_id`) BUCKETS 1\n" +
+ " PROPERTIES (\n" +
+ " \"replication_allocation\" = \"tag.location.default:
1\"\n" +
+ " );"
+
+ initTableData2 = "insert into ${tbName2} 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')," +
+ " (993456689, 'Alice', 1.2, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}","${tbName2}","is_student")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+ //Test the dup model by modify a value type from TINYINT to DOUBLE
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_student DOUBLE """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1.23,
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, false,"${tbName1}")
+
+ sql """ DROP TABLE IF EXISTS ${tbName2} """
+ initTable2 = " CREATE TABLE IF NOT EXISTS ${tbName2}\n" +
+ " (\n" +
+ " `user_id` LARGEINT NOT NULL COMMENT \"用户id\",\n" +
+ " `username` VARCHAR(50) NOT NULL COMMENT
\"用户昵称\",\n" +
+ " `is_student` DOUBLE 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" +
+ " DUPLICATE KEY(`user_id`, `username`)\n" +
+ " DISTRIBUTED BY HASH(`user_id`) BUCKETS 1\n" +
+ " PROPERTIES (\n" +
+ " \"replication_allocation\" = \"tag.location.default:
1\"\n" +
+ " );"
+
+ initTableData2 = "insert into ${tbName2} 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')," +
+ " (993456689, 'Alice', 1.23, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}","${tbName2}","is_student")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+ //TODO Test the dup model by modify a value type from TINYINT to
DECIMAL32
+ errorMessage="errCode = 2, detailMessage = Can not change TINYINT to
DECIMAL32"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_student DECIMAL
"""
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 1.23,
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+
+ },errorMessage)
+
+ //TODO Test the dup model by modify a value type from TINYINT to CHAR
+ errorMessage="errCode = 2, detailMessage = Can not change TINYINT to CHAR"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_student CHAR(15)
"""
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice',
'asd', 'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01
10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+ },errorMessage)
+
+
+
+
+ //TODO Data doubling Test Test the dup model by modify a value type from
TINYINT to VARCHAR
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_student VARCHAR(100)
"""
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 'asd',
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, true,"${tbName1}")
+
+
+
+
+
+ //Test the dup model by modify a value type from TINYINT to STRING
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column is_student STRING """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 'asd',
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, false,"${tbName1}")
+
+ sql """ DROP TABLE IF EXISTS ${tbName2} """
+ initTable2 = " CREATE TABLE IF NOT EXISTS ${tbName2}\n" +
+ " (\n" +
+ " `user_id` LARGEINT NOT NULL COMMENT \"用户id\",\n" +
+ " `username` VARCHAR(50) NOT NULL COMMENT
\"用户昵称\",\n" +
+ " `is_student` STRING 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" +
+ " DUPLICATE KEY(`user_id`, `username`)\n" +
+ " DISTRIBUTED BY HASH(`user_id`) BUCKETS 1\n" +
+ " PROPERTIES (\n" +
+ " \"replication_allocation\" = \"tag.location.default:
1\"\n" +
+ " );"
+
+ initTableData2 = "insert into ${tbName2} 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')," +
+ " (993456689, 'Alice', 'asd', 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}","${tbName2}","is_student")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+
+
+
+
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
