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 859d70f80b6 [enhancement](regression-test) dup modify case (#37137)
859d70f80b6 is described below
commit 859d70f80b683b82db3276001a8442bb11668cba
Author: kkop <[email protected]>
AuthorDate: Thu Jul 4 09:05:46 2024 +0800
[enhancement](regression-test) dup modify case (#37137)
---
.../test_dup_schema_value_modify1.out | 0
.../test_dup_schema_value_modify1.groovy | 1512 ++++++++++++++++++++
2 files changed, 1512 insertions(+)
diff --git
a/regression-test/data/schema_change_p0/test_dup_schema_value_modify1.out
b/regression-test/data/schema_change_p0/test_dup_schema_value_modify1.out
new file mode 100644
index 00000000000..e69de29bb2d
diff --git
a/regression-test/suites/schema_change_p0/test_dup_schema_value_modify1.groovy
b/regression-test/suites/schema_change_p0/test_dup_schema_value_modify1.groovy
new file mode 100644
index 00000000000..235cf80833f
--- /dev/null
+++
b/regression-test/suites/schema_change_p0/test_dup_schema_value_modify1.groovy
@@ -0,0 +1,1512 @@
+// 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_modify1", "p0") {
+ def tbName1 = "unique_model_value_change1"
+ def tbName2 = "unique_model_value_change_1"
+ def on_write = getRandomBoolean()
+ println String.format("current enable_unique_key_merge_on_write is : %s ",
on_write)
+ //Test the unique 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 unique 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" +
+ " UNIQUE 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');"
+
+
+ /**
+ * Test the unique model by modify a value type from SMALLINT 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" +
+ " `car_number` SMALLINT 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" +
+ " );"
+
+ initTableData = "insert into ${tbName1} values(123456789, 'Alice', 13243,
'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01
10:00:00')," +
+ " (234567890, 'Bob', 13445, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 15768, 'Guangzhou', 28, 0,
13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 14243, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 10768, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 14325, 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (789012345, 'Grace', 15686, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+
+ //TODO Test the unique model by modify a value type from SMALLINT to
BOOLEAN
+ errorMessage = "errCode = 2, detailMessage = Can not change SMALLINT to
BOOLEAN"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column car_number BOOLEAN """
+ 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 Test the unique model by modify a value type from SMALLINT to
TINYINT
+ errorMessage = "errCode = 2, detailMessage = Can not change SMALLINT to
TINYINT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column car_number TINYINT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 2,
'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 unique model by modify a value type from SMALLINT to INT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column car_number INT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 3, '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" +
+ " `car_number` 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', 13243,
'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01
10:00:00')," +
+ " (234567890, 'Bob', 13445, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 15768, 'Guangzhou', 28, 0,
13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 14243, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 10768, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 14325, 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 3, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 15686, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "car_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //Test the unique model by modify a value type from SMALLINT to BIGINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column car_number BIGINT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 4, '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" +
+ " `car_number` 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', 13243,
'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01
10:00:00')," +
+ " (234567890, 'Bob', 13445, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 15768, 'Guangzhou', 28, 0,
13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 14243, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 10768, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 14325, 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 4, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 15686, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "car_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //Test the unique model by modify a value type from SMALLINT to LARGEINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column car_number LARGEINT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 5, '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" +
+ " `car_number` 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', 13243,
'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01
10:00:00')," +
+ " (234567890, 'Bob', 13445, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 15768, 'Guangzhou', 28, 0,
13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 14243, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 10768, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 14325, 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 5, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 15686, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "car_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column car_number 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" +
+ " `car_number` 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', 13243,
'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01
10:00:00')," +
+ " (234567890, 'Bob', 13445, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 15768, 'Guangzhou', 28, 0,
13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 14243, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 10768, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 14325, 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 1.2, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 15686, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "car_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column car_number 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" +
+ " `car_number` 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', 13243,
'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01
10:00:00')," +
+ " (234567890, 'Bob', 13445, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 15768, 'Guangzhou', 28, 0,
13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 14243, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 10768, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 14325, 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 1.23, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 15686, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "car_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //TODO Test the unique model by modify a value type from SMALLINT to
DECIMAL
+ errorMessage = "errCode = 2, detailMessage = Can not change SMALLINT to
DECIMAL32"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column car_number 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 unique model by modify a value type from SMALLINT to CHAR
+ errorMessage = "errCode = 2, detailMessage = Can not change SMALLINT to
CHAR"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column car_number 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)
+
+
+ //Test the unique model by modify a value type from SMALLINT to VARCHAR
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column car_number 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, 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" +
+ " `car_number` VARCHAR(100) 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', 13243,
'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01
10:00:00')," +
+ " (234567890, 'Bob', 13445, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 15768, 'Guangzhou', 28, 0,
13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 14243, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 10768, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 14325, 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 'asd', 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 15686, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "car_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //Test the unique model by modify a value type from SMALLINT to STRING
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column car_number 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" +
+ " `car_number` 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', 13243,
'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01
10:00:00')," +
+ " (234567890, 'Bob', 13445, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 15768, 'Guangzhou', 28, 0,
13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 14243, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 10768, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 14325, 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 'asd', 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 15686, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "car_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ /**
+ * Test the unique model by modify a value type from INT 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" +
+ " `sn_number` INT COMMENT \"sn卡\",\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" +
+ " );"
+
+ initTableData = "insert into ${tbName1} values(123456789, 'Alice',
2147483641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 214748364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 2147483441, 'Guangzhou', 28,
0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 2147483141, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 2127483141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 2124483141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (789012345, 'Grace', 2123483141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+
+ //TODO Test the unique model by modify a value type from INT to BOOLEAN
+ errorMessage = "errCode = 2, detailMessage = Can not change INT to BOOLEAN"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column sn_number BOOLEAN """
+ 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 Test the unique model by modify a value type from INT to TINYINT
+ errorMessage = "errCode = 2, detailMessage = Can not change INT to TINYINT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column sn_number TINYINT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 2,
'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 unique model by modify a value type from INT to SMALLINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column sn_number INT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 3, '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" +
+ " `sn_number` 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',
2147483641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 214748364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 2147483441, 'Guangzhou', 28,
0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 2147483141, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 2127483141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 2124483141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 3, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 2123483141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "sn_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //Test the unique model by modify a value type from INT to BIGINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column sn_number BIGINT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 4, '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" +
+ " `sn_number` 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',
2147483641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 214748364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 2147483441, 'Guangzhou', 28,
0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 2147483141, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 2127483141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 2124483141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 4, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 2123483141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "sn_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //Test the unique model by modify a value type from INT to LARGEINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column sn_number LARGEINT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 5, '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" +
+ " `sn_number` 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',
2147483641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 214748364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 2147483441, 'Guangzhou', 28,
0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 2147483141, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 2127483141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 2124483141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 5, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 2123483141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "sn_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //Test the unique model by modify a value type from INT to FLOAT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column sn_number 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" +
+ " `sn_number` 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',
2147483641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 214748364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 2147483441, 'Guangzhou', 28,
0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 2147483141, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 2127483141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 2124483141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 1.2, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 2123483141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "sn_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //Test the unique model by modify a value type from INT to DOUBLE
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column sn_number 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" +
+ " `sn_number` 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',
2147483641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 214748364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 2147483441, 'Guangzhou', 28,
0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 2147483141, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 2127483141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 2124483141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 1.23, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 2123483141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "sn_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //TODO Test the unique model by modify a value type from INT to DECIMAL
+ errorMessage = "errCode = 2, detailMessage = Can not change INT to
DECIMAL128"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column sn_number DECIMAL(38,0)
"""
+ 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 unique model by modify a value type from INT to CHAR
+ errorMessage = "errCode = 2, detailMessage = Can not change INT to CHAR"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column sn_number 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)
+
+
+ //Test the unique model by modify a value type from INT to VARCHAR
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column sn_number 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, 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" +
+ " `sn_number` VARCHAR(100) 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',
2147483641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 214748364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 2147483441, 'Guangzhou', 28,
0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 2147483141, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 2127483141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 2124483141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 'asd', 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 2123483141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "sn_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //Test the unique model by modify a value type from INT to VARCHAR
+ errorMessage = "errCode = 2, detailMessage = Can not change from wider
type INT to narrower type VARCHAR(2)"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column sn_number VARCHAR(2)
"""
+ 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)
+
+ //Test the unique model by modify a value type from INT to STRING
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column sn_number 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" +
+ " `sn_number` 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',
2147483641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 214748364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 2147483441, 'Guangzhou', 28,
0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 2147483141, 'Shenzhen', 35, 1,
13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 2127483141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 2124483141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 'asd', 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 2123483141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "sn_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ /**
+ * Test the unique model by modify a value type from BIGINT 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" +
+ " `fan_number` BIGINT COMMENT \"fan序列号\",\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" +
+ " );"
+
+ initTableData = "insert into ${tbName1} values(123456789, 'Alice',
21474832641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 21474348364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 214742383441, 'Guangzhou',
28, 0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 21474283141, 'Shenzhen', 35,
1, 13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 21274863141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 21244883141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (789012345, 'Grace', 21234683141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+
+ //TODO Test the unique model by modify a value type from BIGINT to BOOLEAN
+ errorMessage = "errCode = 2, detailMessage = Can not change BIGINT to
BOOLEAN"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column fan_number BOOLEAN """
+ 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 Test the unique model by modify a value type from BIGINT to
TINYINT
+ errorMessage = "errCode = 2, detailMessage = Can not change BIGINT to
TINYINT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column fan_number TINYINT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 2,
'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 unique model by modify a value type from BIGINT to SMALLINT
+ errorMessage = "errCode = 2, detailMessage = Can not change BIGINT to
SMALLINT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column fan_number SMALLINT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 3,
'Yaan', 25, 0, 13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00'); "
+ waitForSchemaChangeDone({
+ sql getTableStatusSql
+ time 60
+ }, insertSql, false, "${tbName1}")
+ }, errorMessage)
+
+
+ //Test the unique model by modify a value type from BIGINT to INT
+ errorMessage = "errCode = 2, detailMessage = Can not change BIGINT to INT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column fan_number INT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 4,
'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 unique model by modify a value type from BIGINT to LARGEINT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column fan_number LARGEINT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 5, '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" +
+ " `fan_number` 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',
21474832641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 21474348364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 214742383441, 'Guangzhou',
28, 0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 21474283141, 'Shenzhen', 35,
1, 13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 21274863141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 21244883141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (678901234, 'Frank', 5, 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (789012345, 'Grace', 21234683141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "fan_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //Test the unique model by modify a value type from BIGINT to FLOAT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column fan_number 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" +
+ " `fan_number` 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',
21474832641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 21474348364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 214742383441, 'Guangzhou',
28, 0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 21474283141, 'Shenzhen', 35,
1, 13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 21274863141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 21244883141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (678901234, 'Frank', 1.2, 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (789012345, 'Grace', 21234683141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "fan_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //Test the unique model by modify a value type from BIGINT to DOUBLE
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column fan_number 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" +
+ " `fan_number` 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',
21474832641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 21474348364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 214742383441, 'Guangzhou',
28, 0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 21474283141, 'Shenzhen', 35,
1, 13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 21274863141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 21244883141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (678901234, 'Frank', 1.23, 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (789012345, 'Grace', 21234683141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "fan_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //TODO Test the unique model by modify a value type from BIGINT to DECIMAL
+ errorMessage = "errCode = 2, detailMessage = Can not change BIGINT to
DECIMAL128"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column fan_number
DECIMAL(38,0) """
+ 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 unique model by modify a value type from BIGINT to CHAR
+ errorMessage = "errCode = 2, detailMessage = Can not change BIGINT to CHAR"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column fan_number 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)
+
+
+ //Test the unique model by modify a value type from BIGINT to VARCHAR
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column fan_number 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, 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" +
+ " `fan_number` VARCHAR(100) 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',
21474832641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 21474348364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 214742383441, 'Guangzhou',
28, 0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 21474283141, 'Shenzhen', 35,
1, 13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 21274863141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 21244883141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (678901234, 'Frank', 'asd', 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (789012345, 'Grace', 21234683141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "fan_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //Test the unique model by modify a value type from BIGINT to VARCHAR(2)
+ errorMessage = "errCode = 2, detailMessage = Can not change from wider
type BIGINT to narrower type VARCHAR(2)"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column fan_number VARCHAR(2)
"""
+ 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)
+
+
+ //Test the unique model by modify a value type from BIGINT to STRING
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column fan_number 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" +
+ " `fan_number` 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',
21474832641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 21474348364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 214742383441, 'Guangzhou',
28, 0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 21474283141, 'Shenzhen', 35,
1, 13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 21274863141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 21244883141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (678901234, 'Frank', 'asd', 'Hangzhou', 32, 1,
13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (789012345, 'Grace', 21234683141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}", "${tbName2}", "fan_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ /**
+ * Test the unique model by modify a value type from LARGEINT 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" +
+ " `item_number` LARGEINT COMMENT \"item序列号\",\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" +
+ " );"
+
+ initTableData = "insert into ${tbName1} values(123456789, 'Alice',
21474832641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 21474348364, 'Shanghai', 30,
1, 13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 214742383441, 'Guangzhou',
28, 0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 21474283141, 'Shenzhen', 35,
1, 13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 21274863141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 21244883141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (789012345, 'Grace', 21234683141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+
+ //TODO Test the unique model by modify a value type from LARGEINT to
BOOLEAN
+ errorMessage = "errCode = 2, detailMessage = Can not change LARGEINT to
BOOLEAN"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column item_number BOOLEAN
"""
+ 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 Test the unique model by modify a value type from LARGEINT to
TINYINT
+ errorMessage = "errCode = 2, detailMessage = Can not change LARGEINT to
TINYINT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column item_number TINYINT
"""
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 2,
'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 unique model by modify a value type from LARGEINT to
SMALLINT
+ errorMessage = "errCode = 2, detailMessage = Can not change LARGEINT to
SMALLINT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column item_number SMALLINT
"""
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 3,
'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 unique model by modify a value type from LARGEINT to INT
+ errorMessage = "errCode = 2, detailMessage = Can not change LARGEINT to
INT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column item_number INT """
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 4,
'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 unique model by modify a value type from LARGEINT to
BIGINT
+ errorMessage="errCode = 2, detailMessage = Can not change LARGEINT to
BIGINT"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column item_number BIGINT
"""
+ insertSql = "insert into ${tbName1} values(993456689, 'Alice', 5,
'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 unique model by modify a value type from LARGEINT to FLOAT
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column item_number 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" +
+ " `item_number` 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',
21474832641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 21474348364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 214742383441, 'Guangzhou',
28, 0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 21474283141, 'Shenzhen', 35,
1, 13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 21274863141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 21244883141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 1.2, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 21234683141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}","${tbName2}","item_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //Test the unique model by modify a value type from LARGEINT to DOUBLE
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column item_number 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" +
+ " `item_number` 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',
21474832641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 21474348364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 214742383441, 'Guangzhou',
28, 0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 21474283141, 'Shenzhen', 35,
1, 13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 21274863141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 21244883141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 1.23, 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 21234683141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}","${tbName2}","item_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+ //TODO Test the unique model by modify a value type from LARGEINT to
DECIMAL
+ errorMessage = "errCode = 2, detailMessage = Can not change LARGEINT to
DECIMAL128"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column item_number
DECIMAL(38,0) """
+ 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 unique model by modify a value type from LARGEINT to
CHAR
+ errorMessage = "errCode = 2, detailMessage = Can not change LARGEINT to
CHAR"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column item_number 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)
+
+
+ //Test the unique model by modify a value type from LARGEINT to VARCHAR
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column item_number 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, 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" +
+ " `item_number` VARCHAR(100) 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',
21474832641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 21474348364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 214742383441, 'Guangzhou',
28, 0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 21474283141, 'Shenzhen', 35,
1, 13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 21274863141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 21244883141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 'asd', 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 21234683141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}","${tbName2}","item_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+
+
+ //Test the unique model by modify a value type from LARGEINT to
VARCHAR(2)
+ errorMessage="errCode = 2, detailMessage = Can not change from wider type
LARGEINT to narrower type VARCHAR(2)"
+ expectException({
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column item_number
VARCHAR(2) """
+ 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)
+
+
+ //Test the unique model by modify a value type from LARGEINT to STRING
+ sql initTable
+ sql initTableData
+ sql """ alter table ${tbName1} MODIFY column item_number 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" +
+ " `item_number` 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',
21474832641, 'Beijing', 25, 0, 13812345678, 'No. 123 Street, Beijing',
'2022-01-01 10:00:00')," +
+ " (234567890, 'Bob', 21474348364, 'Shanghai', 30, 1,
13998765432, 'No. 456 Street, Shanghai', '2022-02-02 12:00:00')," +
+ " (345678901, 'Carol', 214742383441, 'Guangzhou',
28, 0, 13724681357, 'No. 789 Street, Guangzhou', '2022-03-03 14:00:00')," +
+ " (456789012, 'Dave', 21474283141, 'Shenzhen', 35,
1, 13680864279, 'No. 987 Street, Shenzhen', '2022-04-04 16:00:00')," +
+ " (567890123, 'Eve', 21274863141, 'Chengdu', 27, 0,
13572468091, 'No. 654 Street, Chengdu', '2022-05-05 18:00:00')," +
+ " (678901234, 'Frank', 21244883141, 'Hangzhou', 32,
1, 13467985213, 'No. 321 Street, Hangzhou', '2022-06-06 20:00:00')," +
+ " (993456689, 'Alice', 'asd', 'Yaan', 25, 0,
13812345678, 'No. 123 Street, Beijing', '2022-01-01 10:00:00')," +
+ " (789012345, 'Grace', 21234683141, 'Xian', 29, 0,
13333333333, 'No. 222 Street, Xian', '2022-07-07 22:00:00');"
+ sql initTable2
+ sql initTableData2
+ checkTableData("${tbName1}","${tbName2}","item_number")
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
