This is an automated email from the ASF dual-hosted git repository.
kxiao pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new 42d480699d3 [test](regression) Add more alter stmt regression case
(#26988) (#27193)
42d480699d3 is described below
commit 42d480699d3fdc2674af9883dac85c31d9cc6780
Author: Lei Zhang <[email protected]>
AuthorDate: Fri Nov 17 21:54:58 2023 +0800
[test](regression) Add more alter stmt regression case (#26988) (#27193)
---
.../data/schema_change_p0/test_alter_parition.out | 6 +
.../test_alter_table_add_columns.out | 40 ++++
.../test_alter_table_drop_column.out | 41 ++++
.../test_alter_table_modify_column.out | 29 +++
.../schema_change_p0/test_alter_table_replace.out | 26 +++
.../schema_change_p0/test_alter_parition.groovy | 65 ++++++
.../test_alter_table_add_columns.groovy | 88 +++++++++
.../test_alter_table_drop_column.groovy | 176 +++++++++++++++++
.../test_alter_table_modify_column.groovy | 219 +++++++++++++++++++++
.../test_alter_table_replace.groovy | 104 ++++++++++
10 files changed, 794 insertions(+)
diff --git a/regression-test/data/schema_change_p0/test_alter_parition.out
b/regression-test/data/schema_change_p0/test_alter_parition.out
new file mode 100644
index 00000000000..cad31310640
--- /dev/null
+++ b/regression-test/data/schema_change_p0/test_alter_parition.out
@@ -0,0 +1,6 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+1 2017-01-01 Beijing 10 1 1 30 20 \N
\N
+2 2017-02-01 Beijing 10 1 1 31 19 \N
\N
+3 2017-03-01 Beijing 10 1 1 31 21 \N
\N
+
diff --git
a/regression-test/data/schema_change_p0/test_alter_table_add_columns.out
b/regression-test/data/schema_change_p0/test_alter_table_add_columns.out
new file mode 100644
index 00000000000..809993434ad
--- /dev/null
+++ b/regression-test/data/schema_change_p0/test_alter_table_add_columns.out
@@ -0,0 +1,40 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+
+-- !order --
+1 1 xxx 1 1 2
+2 2 xxx 2 1 2
+3 3 xxx 3 1 2
+
+-- !order --
+1 1 xxx 1 1 2
+2 2 xxx 2 1 2
+3 3 xxx 3 1 2
+4 4 yyy 4 4 4
+5 5 yyy 5 5 5
+6 6 yyy 6 6 6
+
+-- !sql --
+siteid INT Yes true 10
+citycode SMALLINT Yes true \N
+username VARCHAR(32) Yes true test
+new_k1 INT Yes true 1
+new_k2 INT Yes true 2
+pv BIGINT Yes false 0 SUM
+new_v1 INT Yes false 1 MAX
+new_v2 INT Yes false 2 MAX
+
+-- !order --
+1 1 xxx 1 2 1 1 2
+2 2 xxx 1 2 2 1 2
+3 3 xxx 1 2 3 1 2
+4 4 yyy 1 2 4 4 4
+5 5 yyy 1 2 5 5 5
+6 6 yyy 1 2 6 6 6
+7 7 zzz 7 7 7 7 7
+8 8 zzz 8 8 8 8 8
+9 9 zzz 9 9 9 9 9
+
diff --git
a/regression-test/data/schema_change_p0/test_alter_table_drop_column.out
b/regression-test/data/schema_change_p0/test_alter_table_drop_column.out
new file mode 100644
index 00000000000..4da60943997
--- /dev/null
+++ b/regression-test/data/schema_change_p0/test_alter_table_drop_column.out
@@ -0,0 +1,41 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+
diff --git
a/regression-test/data/schema_change_p0/test_alter_table_modify_column.out
b/regression-test/data/schema_change_p0/test_alter_table_modify_column.out
new file mode 100644
index 00000000000..947717f4adf
--- /dev/null
+++ b/regression-test/data/schema_change_p0/test_alter_table_modify_column.out
@@ -0,0 +1,29 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+
+-- !order --
+xxx 1 1 1
+xxx 2 2 2
+xxx 3 3 3
+yyy 4 4 4
+
+-- !order --
+1 1 xxx 1
+2 2 xxx 2
+3 3 xxx 3
+4 4 yyy 4
+5 5 zzz 5
+
diff --git a/regression-test/data/schema_change_p0/test_alter_table_replace.out
b/regression-test/data/schema_change_p0/test_alter_table_replace.out
new file mode 100644
index 00000000000..40698fdd537
--- /dev/null
+++ b/regression-test/data/schema_change_p0/test_alter_table_replace.out
@@ -0,0 +1,26 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+1 2017-01-01 Beijing 10 1 1 30 20 \N
\N
+2 2017-02-01 Beijing 10 1 1 31 19 \N
\N
+3 2017-03-01 Beijing 10 1 1 31 21 \N
\N
+
+-- !select --
+4 2017-01-01 Beijing 10 1 1 30 20 \N
\N
+5 2017-02-01 Beijing 10 1 1 31 19 \N
\N
+6 2017-03-01 Beijing 10 1 1 31 21 \N
\N
+
+-- !select --
+4 2017-01-01 Beijing 10 1 1 30 20 \N
\N
+5 2017-02-01 Beijing 10 1 1 31 19 \N
\N
+6 2017-03-01 Beijing 10 1 1 31 21 \N
\N
+
+-- !select --
+1 2017-01-01 Beijing 10 1 1 30 20 \N
\N
+2 2017-02-01 Beijing 10 1 1 31 19 \N
\N
+3 2017-03-01 Beijing 10 1 1 31 21 \N
\N
+
+-- !select --
+1 2017-01-01 Beijing 10 1 1 30 20 \N
\N
+2 2017-02-01 Beijing 10 1 1 31 19 \N
\N
+3 2017-03-01 Beijing 10 1 1 31 21 \N
\N
+
diff --git a/regression-test/suites/schema_change_p0/test_alter_parition.groovy
b/regression-test/suites/schema_change_p0/test_alter_parition.groovy
new file mode 100644
index 00000000000..98439c007e3
--- /dev/null
+++ b/regression-test/suites/schema_change_p0/test_alter_parition.groovy
@@ -0,0 +1,65 @@
+// 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_alter_partition") {
+ def tbName = "test_alter_partition"
+
+ sql "DROP TABLE IF EXISTS ${tbName} FORCE"
+ sql """
+ CREATE TABLE ${tbName} (
+ `user_id` LARGEINT NOT NULL COMMENT "用户id",
+ `date` DATE NOT NULL COMMENT "数据灌入日期时间",
+ `city` VARCHAR(20) COMMENT "用户所在城市",
+ `age` SMALLINT COMMENT "用户年龄",
+ `sex` TINYINT COMMENT "用户性别",
+
+ `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
+ `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
+ `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间",
+ `hll_col` HLL HLL_UNION NOT NULL COMMENT "HLL列",
+ `bitmap_col` Bitmap BITMAP_UNION NOT NULL COMMENT "bitmap列")
+ AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
+ PARTITION BY RANGE(`date`)
+ (
+ PARTITION `p201701` VALUES LESS THAN ("2017-02-01"),
+ PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),
+ PARTITION `p201703` VALUES LESS THAN ("2017-04-01")
+ )
+ DISTRIBUTED BY HASH(`user_id`)
+ BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ ;
+ """
+
+ sql """ INSERT INTO ${tbName} VALUES
+ (1, '2017-01-01', 'Beijing', 10, 1, 1, 30, 20, hll_hash(1),
to_bitmap(1)),
+ (2, '2017-02-01', 'Beijing', 10, 1, 1, 31, 19, hll_hash(2),
to_bitmap(2)),
+ (3, '2017-03-01', 'Beijing', 10, 1, 1, 31, 21, hll_hash(2),
to_bitmap(2))
+ """
+
+ qt_select """ select * from ${tbName} order by user_id"""
+
+ // modify in_memory property
+ // https://github.com/apache/doris/pull/18731
+ test {
+ sql """ALTER TABLE ${tbName} MODIFY PARTITION p201701 SET ("in_memory"
= "true");"""
+ exception "Not support set 'in_memory'='true' now!"
+ }
+ sql "DROP TABLE IF EXISTS ${tbName} FORCE"
+}
\ No newline at end of file
diff --git
a/regression-test/suites/schema_change_p0/test_alter_table_add_columns.groovy
b/regression-test/suites/schema_change_p0/test_alter_table_add_columns.groovy
new file mode 100644
index 00000000000..33484f95bf8
--- /dev/null
+++
b/regression-test/suites/schema_change_p0/test_alter_table_add_columns.groovy
@@ -0,0 +1,88 @@
+// 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_alter_table_add_columns") {
+ def tbName = "test_alter_table_add_columns"
+
+ sql "DROP TABLE IF EXISTS ${tbName} FORCE"
+ sql """
+ CREATE TABLE `${tbName}`
+ (
+ `siteid` INT DEFAULT '10',
+ `citycode` SMALLINT,
+ `username` VARCHAR(32) DEFAULT 'test',
+ `pv` BIGINT SUM DEFAULT '0'
+ )
+ AGGREGATE KEY(`siteid`, `citycode`, `username`)
+ DISTRIBUTED BY HASH(siteid) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """ INSERT INTO ${tbName} VALUES
+ (1, 1, "xxx", 1),
+ (2, 2, "xxx", 2),
+ (3, 3, "xxx", 3);
+ """
+
+ qt_order """ select * from ${tbName} order by siteid"""
+
+ // Add two value column light schema change is true
+ sql """ alter table ${tbName} ADD COLUMN (new_v1 INT MAX default "1" ,
new_v2 INT MAX default "2");"""
+
+ qt_order """ select * from ${tbName} order by siteid"""
+
+ sql """ INSERT INTO ${tbName} VALUES
+ (4, 4, "yyy", 4, 4, 4),
+ (5, 5, "yyy", 5, 5, 5),
+ (6, 6, "yyy", 6, 6, 6);
+ """
+
+ qt_order """ select * from ${tbName} order by siteid"""
+
+ // Add one value column light schema change is false
+ sleep(1000)
+ sql """ alter table ${tbName} ADD COLUMN (new_k1 INT DEFAULT '1', new_k2
INT DEFAULT '2');"""
+ def waitSchemaChangeJob = { String tableName /* param */ ->
+ int tryTimes = 30
+ while (tryTimes-- > 0) {
+ def jobResult = sql """SHOW ALTER TABLE COLUMN WHERE
IndexName='${tableName}' ORDER BY createtime DESC LIMIT 1 """
+ def jobState = jobResult[0][9].toString()
+ if ('cancelled'.equalsIgnoreCase(jobState)) {
+ logger.info("jobResult:{}", jobResult)
+ throw new IllegalStateException("${tableName}'s job has been
cancelled")
+ }
+ if ('finished'.equalsIgnoreCase(jobState)) {
+ logger.info("jobResult:{}", jobResult)
+ break
+ }
+ sleep(10000)
+ }
+ }
+
+ waitSchemaChangeJob(tbName)
+ qt_sql """ DESC ${tbName}"""
+
+ sql """ INSERT INTO ${tbName} VALUES
+ (7, 7, "zzz", 7, 7, 7, 7, 7),
+ (8, 8, "zzz", 8, 8, 8, 8, 8),
+ (9, 9, "zzz", 9, 9, 9, 9, 9);
+ """
+ qt_order """ select * from ${tbName} order by siteid"""
+ sql "DROP TABLE IF EXISTS ${tbName} FORCE"
+}
\ No newline at end of file
diff --git
a/regression-test/suites/schema_change_p0/test_alter_table_drop_column.groovy
b/regression-test/suites/schema_change_p0/test_alter_table_drop_column.groovy
new file mode 100644
index 00000000000..433cbadcddf
--- /dev/null
+++
b/regression-test/suites/schema_change_p0/test_alter_table_drop_column.groovy
@@ -0,0 +1,176 @@
+// 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_alter_table_drop_column") {
+ // unique model table
+ def uniqueTableName = "test_alter_table_drop_column_unique"
+ def uniqueTableRollupName = "test_alter_table_drop_column_rollup_unique"
+
+ sql "DROP TABLE IF EXISTS ${uniqueTableName} FORCE"
+ sql """
+ CREATE TABLE `${uniqueTableName}`
+ (
+ `siteid` INT DEFAULT '10',
+ `citycode` SMALLINT,
+ `username` VARCHAR(32) DEFAULT 'test',
+ `pv` BIGINT DEFAULT '0'
+ )
+ UNIQUE KEY(`siteid`, `citycode`, `username`)
+ DISTRIBUTED BY HASH(siteid) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1",
+ "bloom_filter_columns" = "pv"
+ );
+ """
+
+ sql "ALTER TABLE ${uniqueTableName} ADD ROLLUP
${uniqueTableRollupName}(`citycode`,`siteid`,`username`,`pv`);"
+ def waitRollupJob = { String tableName /* param */ ->
+ int tryTimes = 30
+ while (tryTimes-- > 0) {
+ def jobResult = sql """SHOW ALTER TABLE ROLLUP WHERE
TableName='${tableName}' ORDER BY CreateTime DESC LIMIT 1"""
+ def jobState = jobResult[0][8].toString()
+ if ('cancelled'.equalsIgnoreCase(jobState)) {
+ logger.info("jobResult:{}", jobResult)
+ throw new IllegalStateException("${tableName}'s job has been
cancelled")
+ }
+ if ('finished'.equalsIgnoreCase(jobState)) {
+ logger.info("jobResult:{}", jobResult)
+ return;
+ }
+ sleep(10000)
+ }
+ assertTrue(false)
+ }
+
+ waitRollupJob(uniqueTableName)
+
+ sql """ INSERT INTO ${uniqueTableName} VALUES
+ (1, 1, "xxx", 1),
+ (2, 2, "xxx", 2),
+ (3, 3, "xxx", 3);
+ """
+
+ qt_order """ select * from ${uniqueTableName} order by siteid"""
+ qt_order """ select * from ${uniqueTableName} order by citycode"""
+
+ test {
+ sql """ alter table ${uniqueTableName} drop COLUMN siteid;"""
+ // check exception message contains
+ exception "Can not drop key column in Unique data model table"
+ }
+
+ def waitSchemaChangeJob = { String tableName /* param */ ->
+ int tryTimes = 30
+ while (tryTimes-- > 0) {
+ def jobResult = sql """SHOW ALTER TABLE COLUMN WHERE
IndexName='${tableName}' ORDER BY createtime DESC LIMIT 1 """
+ def jobState = jobResult[0][9].toString()
+ if ('cancelled'.equalsIgnoreCase(jobState)) {
+ logger.info("jobResult:{}", jobResult)
+ throw new IllegalStateException("${tableName}'s job has been
cancelled")
+ }
+ if ('finished'.equalsIgnoreCase(jobState)) {
+ logger.info("jobResult:{}", jobResult)
+ return
+ }
+ sleep(10000)
+ }
+ assertTrue(false)
+ }
+
+ sql """ alter table ${uniqueTableName} drop COLUMN pv from
${uniqueTableRollupName};"""
+ waitSchemaChangeJob(uniqueTableName)
+
+ qt_order """ select * from ${uniqueTableName} order by siteid"""
+ qt_order """ select * from ${uniqueTableName} order by citycode"""
+
+ sql "DROP TABLE IF EXISTS ${uniqueTableName} FORCE"
+
+ // aggregage model table
+ def aggTableName = "test_alter_table_drop_column_agg"
+ def aggTableRollupName = "test_alter_table_drop_column_rollup_agg"
+
+ sql "DROP TABLE IF EXISTS ${aggTableName} FORCE"
+ sql """
+ CREATE TABLE `${aggTableName}`
+ (
+ `siteid` INT DEFAULT '10',
+ `citycode` SMALLINT,
+ `username` VARCHAR(32) DEFAULT 'test',
+ `pv` BIGINT REPLACE DEFAULT '0'
+ )
+ AGGREGATE KEY(`siteid`, `citycode`, `username`)
+ DISTRIBUTED BY HASH(siteid) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+ sql "ALTER TABLE ${aggTableName} ADD ROLLUP
${aggTableRollupName}(`citycode`,`siteid`,`username`,`pv`);"
+ waitRollupJob(aggTableName)
+ sql """ INSERT INTO ${aggTableName} VALUES
+ (1, 1, "xxx", 1),
+ (2, 2, "xxx", 2),
+ (3, 3, "xxx", 3);
+ """
+
+ qt_order """ select * from ${aggTableName} order by siteid"""
+ qt_order """ select * from ${aggTableName} order by citycode"""
+
+ test {
+ sql """ alter table ${aggTableName} drop COLUMN citycode from
${aggTableRollupName};"""
+ // check exception message contains
+ exception "Can not drop key column when rollup has value column with
REPLACE aggregation method"
+ }
+
+ sql """ alter table ${aggTableName} drop COLUMN pv from
${aggTableRollupName};"""
+ waitSchemaChangeJob(aggTableName)
+
+ qt_order """ select * from ${aggTableName} order by siteid"""
+ qt_order """ select * from ${aggTableName} order by citycode"""
+
+ test {
+ sql """ alter table ${aggTableName} drop COLUMN pv from
${aggTableRollupName};"""
+ // check exception message contains
+ exception "Column does not exists"
+ }
+
+ // duplicate model table
+ def dupTableName = "test_alter_table_drop_column_dup"
+
+ sql "DROP TABLE IF EXISTS ${dupTableName} FORCE"
+ sql """
+ CREATE TABLE `${dupTableName}`
+ (
+ `siteid` INT DEFAULT '10',
+ `citycode` SMALLINT,
+ `username` VARCHAR(32) DEFAULT 'test',
+ `pv` BIGINT SUM DEFAULT '0'
+ )
+ DUPLICATE KEY(`siteid`, `citycode`, `username`)
+ DISTRIBUTED BY HASH(siteid) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ test {
+ sql """alter table ${dupTableName} drop COLUMN siteid;"""
+ // check exception message contains
+ exception "Distribution column[siteid] cannot be dropped"
+ }
+
+ sql "DROP TABLE IF EXISTS ${dupTableName} FORCE"
+}
\ No newline at end of file
diff --git
a/regression-test/suites/schema_change_p0/test_alter_table_modify_column.groovy
b/regression-test/suites/schema_change_p0/test_alter_table_modify_column.groovy
new file mode 100644
index 00000000000..8df8e911229
--- /dev/null
+++
b/regression-test/suites/schema_change_p0/test_alter_table_modify_column.groovy
@@ -0,0 +1,219 @@
+// 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_alter_table_modify_column") {
+ def waitRollupJob = { String tableName /* param */ ->
+ int tryTimes = 30
+ while (tryTimes-- > 0) {
+ def jobResult = sql """SHOW ALTER TABLE ROLLUP WHERE
TableName='${tableName}' ORDER BY CreateTime DESC LIMIT 1"""
+ def jobState = jobResult[0][8].toString()
+ if ('cancelled'.equalsIgnoreCase(jobState)) {
+ logger.info("jobResult:{}", jobResult)
+ throw new IllegalStateException("${tableName}'s job has been
cancelled")
+ }
+ if ('finished'.equalsIgnoreCase(jobState)) {
+ logger.info("jobResult:{}", jobResult)
+ return;
+ }
+ sleep(10000)
+ }
+ assertTrue(false)
+ }
+
+ def waitSchemaChangeJob = { String tableName /* param */ ->
+ int tryTimes = 30
+ while (tryTimes-- > 0) {
+ def jobResult = sql """SHOW ALTER TABLE COLUMN WHERE
IndexName='${tableName}' ORDER BY createtime DESC LIMIT 1 """
+ def jobState = jobResult[0][9].toString()
+ if ('cancelled'.equalsIgnoreCase(jobState)) {
+ logger.info("jobResult:{}", jobResult)
+ throw new IllegalStateException("${tableName}'s job has been
cancelled")
+ }
+ if ('finished'.equalsIgnoreCase(jobState)) {
+ logger.info("jobResult:{}", jobResult)
+ return
+ }
+ sleep(10000)
+ }
+ assertTrue(false)
+ }
+
+ // unique model table
+ def uniqueTableName = "test_alter_table_modify_column_unique"
+
+ sql "DROP TABLE IF EXISTS ${uniqueTableName} FORCE;"
+ sql """
+ CREATE TABLE `${uniqueTableName}`
+ (
+ `siteid` INT DEFAULT '10',
+ `citycode` SMALLINT,
+ `username` VARCHAR(32) DEFAULT 'test',
+ `pv` BIGINT DEFAULT '0'
+ )
+ UNIQUE KEY(`siteid`, `citycode`, `username`)
+ DISTRIBUTED BY HASH(siteid) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """ INSERT INTO ${uniqueTableName} VALUES
+ (1, 1, "xxx", 1),
+ (2, 2, "xxx", 2),
+ (3, 3, "xxx", 3);
+ """
+
+ qt_order """select * from ${uniqueTableName} order by siteid"""
+
+ test {
+ sql """alter table ${uniqueTableName} modify COLUMN siteid INT SUM
DEFAULT '0';"""
+ // check exception message contains
+ exception "Can not assign aggregation method on column in Unique data
model table"
+ }
+
+ sql "DROP TABLE IF EXISTS ${uniqueTableName} FORCE"
+
+ // aggregate model table
+ def aggTableName = "test_alter_table_modify_column_agg"
+
+ sql "DROP TABLE IF EXISTS ${aggTableName} FORCE"
+ sql """
+ CREATE TABLE `${aggTableName}`
+ (
+ `siteid` INT DEFAULT '10',
+ `citycode` SMALLINT,
+ `username` VARCHAR(32) DEFAULT 'test',
+ `pv` BIGINT SUM DEFAULT '0'
+ )
+ AGGREGATE KEY(`siteid`, `citycode`, `username`)
+ DISTRIBUTED BY HASH(siteid) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """ INSERT INTO ${aggTableName} VALUES
+ (1, 1, "xxx", 1),
+ (2, 2, "xxx", 2),
+ (3, 3, "xxx", 3);
+ """
+
+ qt_order """select * from ${aggTableName} order by siteid"""
+
+ test {
+ sql """alter table ${aggTableName} modify COLUMN siteid INT key SUM
DEFAULT '0';"""
+ // check exception message contains
+ exception "Key column can not set aggregation type"
+ }
+
+ test {
+ sql """alter table ${aggTableName} modify COLUMN pv BIGINT DEFAULT
'0';"""
+ // check exception message contains
+ exception "Can not change aggregation typ"
+ }
+
+ sql "DROP TABLE IF EXISTS ${aggTableName} FORCE"
+
+ // duplicate model table
+ def dupTableName = "test_alter_table_modify_column_dup"
+ def dupTableRollupName = "test_alter_table_modify_column_dup_rollup"
+
+ sql "DROP TABLE IF EXISTS ${dupTableName} FORCE"
+ sql """
+ CREATE TABLE `${dupTableName}`
+ (
+ `citycode` SMALLINT DEFAULT '10',
+ `siteid` INT DEFAULT '10',
+ `username` VARCHAR(32) DEFAULT 'test',
+ `pv` BIGINT SUM DEFAULT '0'
+ )
+ DUPLICATE KEY(`siteid`, `citycode`, `username`)
+ DISTRIBUTED BY HASH(siteid) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql "ALTER TABLE ${dupTableName} ADD ROLLUP
${dupTableRollupName}(`siteid`,`citycode`,`username`,`pv`);"
+ waitRollupJob(dupTableName)
+
+ sql """ INSERT INTO ${dupTableName} VALUES
+ (1, 1, "xxx", 1),
+ (2, 2, "xxx", 2),
+ (3, 3, "xxx", 3);
+ """
+
+ qt_order """select * from ${dupTableName} order by siteid"""
+
+ test {
+ sql """alter table ${dupTableName} modify COLUMN siteid INT SUM
DEFAULT '0';"""
+ // check exception message contains
+ exception "Can not assign aggregation method on column in Duplicate
data model table"
+ }
+
+ test {
+ sql """alter table ${dupTableName} modify COLUMN siteid BIGINT from
not_exist_rollup;"""
+ // check exception message contains
+ exception "Index[not_exist_rollup] does not exist in table"
+ }
+
+ test {
+ sql """alter table ${dupTableName} modify COLUMN not_exist_column
BIGINT;"""
+ // check exception message contains
+ exception "Column[not_exist_column] does not exists"
+ }
+
+ test {
+ sql """alter table ${dupTableName} modify COLUMN not_exist_column
BIGINT from ${dupTableRollupName};"""
+ // check exception message contains
+ exception "Do not need to specify index name when just modifying
column type"
+ }
+
+ test {
+ sql """alter table ${dupTableName} modify COLUMN siteid BIGINT after
not_exist_column;"""
+ // check exception message contains
+ exception "Column[not_exist_column] does not exists"
+ }
+
+ test {
+ sql """alter table ${dupTableName} modify COLUMN citycode BIGINT
DEFAULT '10' first;"""
+ // check exception message contains
+ exception "Invalid column order. value should be after key"
+ }
+
+ test {
+ sql """alter table ${dupTableName} modify COLUMN siteid BIGINT key
DEFAULT '10' first;"""
+ // check exception message contains
+ exception "Can not modify distribution column"
+ }
+
+ sql """alter table ${dupTableName} modify COLUMN username VARCHAR(32) key
DEFAULT 'test' first;"""
+ waitSchemaChangeJob(dupTableName)
+
+ sql """ INSERT INTO ${dupTableName} VALUES
+ ("yyy", 4, 4, 4)
+ """
+ qt_order """select * from ${dupTableName} order by siteid"""
+
+ sql """alter table ${dupTableName} order by(citycode, siteid, username,
pv);"""
+ waitSchemaChangeJob(dupTableName)
+ sql """ INSERT INTO ${dupTableName} VALUES
+ (5, 5, "zzz", 5)
+ """
+ qt_order """select * from ${dupTableName} order by siteid"""
+ sql "DROP TABLE IF EXISTS ${dupTableName} FORCE"
+}
\ No newline at end of file
diff --git
a/regression-test/suites/schema_change_p0/test_alter_table_replace.groovy
b/regression-test/suites/schema_change_p0/test_alter_table_replace.groovy
new file mode 100644
index 00000000000..b07a54c5288
--- /dev/null
+++ b/regression-test/suites/schema_change_p0/test_alter_table_replace.groovy
@@ -0,0 +1,104 @@
+// 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_alter_table_replace") {
+ def tbNameA = "test_alter_table_replace_a"
+ def tbNameB = "test_alter_table_replace_b"
+
+ sql "DROP TABLE IF EXISTS ${tbNameA}"
+ sql "DROP TABLE IF EXISTS ${tbNameB}"
+ sql """
+ CREATE TABLE ${tbNameA} (
+ `user_id` LARGEINT NOT NULL COMMENT "用户id",
+ `date` DATE NOT NULL COMMENT "数据灌入日期时间",
+ `city` VARCHAR(20) COMMENT "用户所在城市",
+ `age` SMALLINT COMMENT "用户年龄",
+ `sex` TINYINT COMMENT "用户性别",
+
+ `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
+ `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
+ `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间",
+ `hll_col` HLL HLL_UNION NOT NULL COMMENT "HLL列",
+ `bitmap_col` Bitmap BITMAP_UNION NOT NULL COMMENT "bitmap列")
+ AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
+ PARTITION BY RANGE(`date`)
+ (
+ PARTITION `p201701` VALUES LESS THAN ("2017-02-01"),
+ PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),
+ PARTITION `p201703` VALUES LESS THAN ("2017-04-01")
+ )
+ DISTRIBUTED BY HASH(`user_id`)
+ BUCKETS 1
+ PROPERTIES ( "replication_num" = "1");
+ """
+
+ sql """
+ CREATE TABLE ${tbNameB} (
+ `user_id` LARGEINT NOT NULL COMMENT "用户id",
+ `date` DATE NOT NULL COMMENT "数据灌入日期时间",
+ `city` VARCHAR(20) COMMENT "用户所在城市",
+ `age` SMALLINT COMMENT "用户年龄",
+ `sex` TINYINT COMMENT "用户性别",
+
+ `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
+ `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
+ `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间",
+ `hll_col` HLL HLL_UNION NOT NULL COMMENT "HLL列",
+ `bitmap_col` Bitmap BITMAP_UNION NOT NULL COMMENT "bitmap列")
+ AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
+ PARTITION BY RANGE(`date`)
+ (
+ PARTITION `p201701` VALUES LESS THAN ("2017-02-01"),
+ PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),
+ PARTITION `p201703` VALUES LESS THAN ("2017-04-01")
+ )
+ DISTRIBUTED BY HASH(`user_id`)
+ BUCKETS 1
+ PROPERTIES ( "replication_num" = "1");
+ """
+
+ sql """ INSERT INTO ${tbNameA} VALUES
+ (1, '2017-01-01', 'Beijing', 10, 1, 1, 30, 20, hll_hash(1),
to_bitmap(1)),
+ (2, '2017-02-01', 'Beijing', 10, 1, 1, 31, 19, hll_hash(2),
to_bitmap(2)),
+ (3, '2017-03-01', 'Beijing', 10, 1, 1, 31, 21, hll_hash(2),
to_bitmap(2))
+ """
+
+ sql """ INSERT INTO ${tbNameB} VALUES
+ (4, '2017-01-01', 'Beijing', 10, 1, 1, 30, 20, hll_hash(1),
to_bitmap(1)),
+ (5, '2017-02-01', 'Beijing', 10, 1, 1, 31, 19, hll_hash(2),
to_bitmap(2)),
+ (6, '2017-03-01', 'Beijing', 10, 1, 1, 31, 21, hll_hash(2),
to_bitmap(2))
+ """
+
+ qt_select """ select * from ${tbNameA} order by user_id"""
+ qt_select """ select * from ${tbNameB} order by user_id"""
+
+ sql """ALTER TABLE ${tbNameA} REPLACE WITH TABLE ${tbNameB}
PROPERTIES('swap' = 'true');"""
+
+ qt_select """ select * from ${tbNameA} order by user_id"""
+ qt_select """ select * from ${tbNameB} order by user_id"""
+
+ sql """ALTER TABLE ${tbNameA} REPLACE WITH TABLE ${tbNameB}
PROPERTIES('swap' = 'false');"""
+ qt_select """ select * from ${tbNameA} order by user_id"""
+ test {
+ sql """ select * from ${tbNameB} order by user_id"""
+ // check exception message contains
+ exception "Unknown table '${tbNameB}'"
+ }
+
+ sql "DROP TABLE IF EXISTS ${tbNameA} FORCE;"
+ sql "DROP TABLE IF EXISTS ${tbNameB} FORCE;"
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
