Skip to content

Commit 42d4806

Browse files
[test](regression) Add more alter stmt regression case (apache#26988) (apache#27193)
1 parent b8ed0a3 commit 42d4806

10 files changed

Lines changed: 794 additions & 0 deletions
Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !select --
3+
1 2017-01-01 Beijing 10 1 1 30 20 \N \N
4+
2 2017-02-01 Beijing 10 1 1 31 19 \N \N
5+
3 2017-03-01 Beijing 10 1 1 31 21 \N \N
6+
Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !order --
3+
1 1 xxx 1
4+
2 2 xxx 2
5+
3 3 xxx 3
6+
7+
-- !order --
8+
1 1 xxx 1 1 2
9+
2 2 xxx 2 1 2
10+
3 3 xxx 3 1 2
11+
12+
-- !order --
13+
1 1 xxx 1 1 2
14+
2 2 xxx 2 1 2
15+
3 3 xxx 3 1 2
16+
4 4 yyy 4 4 4
17+
5 5 yyy 5 5 5
18+
6 6 yyy 6 6 6
19+
20+
-- !sql --
21+
siteid INT Yes true 10
22+
citycode SMALLINT Yes true \N
23+
username VARCHAR(32) Yes true test
24+
new_k1 INT Yes true 1
25+
new_k2 INT Yes true 2
26+
pv BIGINT Yes false 0 SUM
27+
new_v1 INT Yes false 1 MAX
28+
new_v2 INT Yes false 2 MAX
29+
30+
-- !order --
31+
1 1 xxx 1 2 1 1 2
32+
2 2 xxx 1 2 2 1 2
33+
3 3 xxx 1 2 3 1 2
34+
4 4 yyy 1 2 4 4 4
35+
5 5 yyy 1 2 5 5 5
36+
6 6 yyy 1 2 6 6 6
37+
7 7 zzz 7 7 7 7 7
38+
8 8 zzz 8 8 8 8 8
39+
9 9 zzz 9 9 9 9 9
40+
Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !order --
3+
1 1 xxx 1
4+
2 2 xxx 2
5+
3 3 xxx 3
6+
7+
-- !order --
8+
1 1 xxx 1
9+
2 2 xxx 2
10+
3 3 xxx 3
11+
12+
-- !order --
13+
1 1 xxx 1
14+
2 2 xxx 2
15+
3 3 xxx 3
16+
17+
-- !order --
18+
1 1 xxx 1
19+
2 2 xxx 2
20+
3 3 xxx 3
21+
22+
-- !order --
23+
1 1 xxx 1
24+
2 2 xxx 2
25+
3 3 xxx 3
26+
27+
-- !order --
28+
1 1 xxx 1
29+
2 2 xxx 2
30+
3 3 xxx 3
31+
32+
-- !order --
33+
1 1 xxx 1
34+
2 2 xxx 2
35+
3 3 xxx 3
36+
37+
-- !order --
38+
1 1 xxx 1
39+
2 2 xxx 2
40+
3 3 xxx 3
41+
Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !order --
3+
1 1 xxx 1
4+
2 2 xxx 2
5+
3 3 xxx 3
6+
7+
-- !order --
8+
1 1 xxx 1
9+
2 2 xxx 2
10+
3 3 xxx 3
11+
12+
-- !order --
13+
1 1 xxx 1
14+
2 2 xxx 2
15+
3 3 xxx 3
16+
17+
-- !order --
18+
xxx 1 1 1
19+
xxx 2 2 2
20+
xxx 3 3 3
21+
yyy 4 4 4
22+
23+
-- !order --
24+
1 1 xxx 1
25+
2 2 xxx 2
26+
3 3 xxx 3
27+
4 4 yyy 4
28+
5 5 zzz 5
29+
Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !select --
3+
1 2017-01-01 Beijing 10 1 1 30 20 \N \N
4+
2 2017-02-01 Beijing 10 1 1 31 19 \N \N
5+
3 2017-03-01 Beijing 10 1 1 31 21 \N \N
6+
7+
-- !select --
8+
4 2017-01-01 Beijing 10 1 1 30 20 \N \N
9+
5 2017-02-01 Beijing 10 1 1 31 19 \N \N
10+
6 2017-03-01 Beijing 10 1 1 31 21 \N \N
11+
12+
-- !select --
13+
4 2017-01-01 Beijing 10 1 1 30 20 \N \N
14+
5 2017-02-01 Beijing 10 1 1 31 19 \N \N
15+
6 2017-03-01 Beijing 10 1 1 31 21 \N \N
16+
17+
-- !select --
18+
1 2017-01-01 Beijing 10 1 1 30 20 \N \N
19+
2 2017-02-01 Beijing 10 1 1 31 19 \N \N
20+
3 2017-03-01 Beijing 10 1 1 31 21 \N \N
21+
22+
-- !select --
23+
1 2017-01-01 Beijing 10 1 1 30 20 \N \N
24+
2 2017-02-01 Beijing 10 1 1 31 19 \N \N
25+
3 2017-03-01 Beijing 10 1 1 31 21 \N \N
26+
Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,65 @@
1+
// Licensed to the Apache Software Foundation (ASF) under one
2+
// or more contributor license agreements. See the NOTICE file
3+
// distributed with this work for additional information
4+
// regarding copyright ownership. The ASF licenses this file
5+
// to you under the Apache License, Version 2.0 (the
6+
// "License"); you may not use this file except in compliance
7+
// with the License. You may obtain a copy of the License at
8+
//
9+
// http://www.apache.org/licenses/LICENSE-2.0
10+
//
11+
// Unless required by applicable law or agreed to in writing,
12+
// software distributed under the License is distributed on an
13+
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14+
// KIND, either express or implied. See the License for the
15+
// specific language governing permissions and limitations
16+
// under the License.
17+
18+
suite("test_alter_partition") {
19+
def tbName = "test_alter_partition"
20+
21+
sql "DROP TABLE IF EXISTS ${tbName} FORCE"
22+
sql """
23+
CREATE TABLE ${tbName} (
24+
`user_id` LARGEINT NOT NULL COMMENT "用户id",
25+
`date` DATE NOT NULL COMMENT "数据灌入日期时间",
26+
`city` VARCHAR(20) COMMENT "用户所在城市",
27+
`age` SMALLINT COMMENT "用户年龄",
28+
`sex` TINYINT COMMENT "用户性别",
29+
30+
`cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
31+
`max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
32+
`min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间",
33+
`hll_col` HLL HLL_UNION NOT NULL COMMENT "HLL列",
34+
`bitmap_col` Bitmap BITMAP_UNION NOT NULL COMMENT "bitmap列")
35+
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
36+
PARTITION BY RANGE(`date`)
37+
(
38+
PARTITION `p201701` VALUES LESS THAN ("2017-02-01"),
39+
PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),
40+
PARTITION `p201703` VALUES LESS THAN ("2017-04-01")
41+
)
42+
DISTRIBUTED BY HASH(`user_id`)
43+
BUCKETS 1
44+
PROPERTIES (
45+
"replication_num" = "1"
46+
);
47+
;
48+
"""
49+
50+
sql """ INSERT INTO ${tbName} VALUES
51+
(1, '2017-01-01', 'Beijing', 10, 1, 1, 30, 20, hll_hash(1), to_bitmap(1)),
52+
(2, '2017-02-01', 'Beijing', 10, 1, 1, 31, 19, hll_hash(2), to_bitmap(2)),
53+
(3, '2017-03-01', 'Beijing', 10, 1, 1, 31, 21, hll_hash(2), to_bitmap(2))
54+
"""
55+
56+
qt_select """ select * from ${tbName} order by user_id"""
57+
58+
// modify in_memory property
59+
// https://github.com/apache/doris/pull/18731
60+
test {
61+
sql """ALTER TABLE ${tbName} MODIFY PARTITION p201701 SET ("in_memory" = "true");"""
62+
exception "Not support set 'in_memory'='true' now!"
63+
}
64+
sql "DROP TABLE IF EXISTS ${tbName} FORCE"
65+
}
Lines changed: 88 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,88 @@
1+
// Licensed to the Apache Software Foundation (ASF) under one
2+
// or more contributor license agreements. See the NOTICE file
3+
// distributed with this work for additional information
4+
// regarding copyright ownership. The ASF licenses this file
5+
// to you under the Apache License, Version 2.0 (the
6+
// "License"); you may not use this file except in compliance
7+
// with the License. You may obtain a copy of the License at
8+
//
9+
// http://www.apache.org/licenses/LICENSE-2.0
10+
//
11+
// Unless required by applicable law or agreed to in writing,
12+
// software distributed under the License is distributed on an
13+
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14+
// KIND, either express or implied. See the License for the
15+
// specific language governing permissions and limitations
16+
// under the License.
17+
18+
suite("test_alter_table_add_columns") {
19+
def tbName = "test_alter_table_add_columns"
20+
21+
sql "DROP TABLE IF EXISTS ${tbName} FORCE"
22+
sql """
23+
CREATE TABLE `${tbName}`
24+
(
25+
`siteid` INT DEFAULT '10',
26+
`citycode` SMALLINT,
27+
`username` VARCHAR(32) DEFAULT 'test',
28+
`pv` BIGINT SUM DEFAULT '0'
29+
)
30+
AGGREGATE KEY(`siteid`, `citycode`, `username`)
31+
DISTRIBUTED BY HASH(siteid) BUCKETS 1
32+
PROPERTIES (
33+
"replication_num" = "1"
34+
);
35+
"""
36+
37+
sql """ INSERT INTO ${tbName} VALUES
38+
(1, 1, "xxx", 1),
39+
(2, 2, "xxx", 2),
40+
(3, 3, "xxx", 3);
41+
"""
42+
43+
qt_order """ select * from ${tbName} order by siteid"""
44+
45+
// Add two value column light schema change is true
46+
sql """ alter table ${tbName} ADD COLUMN (new_v1 INT MAX default "1" , new_v2 INT MAX default "2");"""
47+
48+
qt_order """ select * from ${tbName} order by siteid"""
49+
50+
sql """ INSERT INTO ${tbName} VALUES
51+
(4, 4, "yyy", 4, 4, 4),
52+
(5, 5, "yyy", 5, 5, 5),
53+
(6, 6, "yyy", 6, 6, 6);
54+
"""
55+
56+
qt_order """ select * from ${tbName} order by siteid"""
57+
58+
// Add one value column light schema change is false
59+
sleep(1000)
60+
sql """ alter table ${tbName} ADD COLUMN (new_k1 INT DEFAULT '1', new_k2 INT DEFAULT '2');"""
61+
def waitSchemaChangeJob = { String tableName /* param */ ->
62+
int tryTimes = 30
63+
while (tryTimes-- > 0) {
64+
def jobResult = sql """SHOW ALTER TABLE COLUMN WHERE IndexName='${tableName}' ORDER BY createtime DESC LIMIT 1 """
65+
def jobState = jobResult[0][9].toString()
66+
if ('cancelled'.equalsIgnoreCase(jobState)) {
67+
logger.info("jobResult:{}", jobResult)
68+
throw new IllegalStateException("${tableName}'s job has been cancelled")
69+
}
70+
if ('finished'.equalsIgnoreCase(jobState)) {
71+
logger.info("jobResult:{}", jobResult)
72+
break
73+
}
74+
sleep(10000)
75+
}
76+
}
77+
78+
waitSchemaChangeJob(tbName)
79+
qt_sql """ DESC ${tbName}"""
80+
81+
sql """ INSERT INTO ${tbName} VALUES
82+
(7, 7, "zzz", 7, 7, 7, 7, 7),
83+
(8, 8, "zzz", 8, 8, 8, 8, 8),
84+
(9, 9, "zzz", 9, 9, 9, 9, 9);
85+
"""
86+
qt_order """ select * from ${tbName} order by siteid"""
87+
sql "DROP TABLE IF EXISTS ${tbName} FORCE"
88+
}

0 commit comments

Comments
 (0)