1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
CREATE PROCEDURE add_columns(table_name varchar(50), algorithm varchar(10))
BEGIN
DECLARE i INT DEFAULT 1;
SET @sql_text = CONCAT('ALTER TABLE ', table_name, ' ');
while (i < 1012) do
SET @sql_text = CONCAT(@sql_text, ' ADD COLUMN ', CONCAT('col_', i), ' INTEGER NULL DEFAULT NULL,');
set i = i + 1;
end while;
SET @sql_text = CONCAT(@sql_text, 'ALGORITHM=', algorithm);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END|
CREATE PROCEDURE create_tables()
BEGIN
# Create a non-partitioned table
CREATE TABLE `test`.`TEST_TABLE` (
`TIMESTAMP` datetime NOT NULL,
`INTERVALL` int NOT NULL,
`IDENT_KEY` int NOT NULL,
`FIELD_1` double DEFAULT NULL,
`FIELD_2` double DEFAULT NULL,
`FIELD_3` double DEFAULT NULL
);
# Create a partitioned table
CREATE TABLE `test`.`TEST_TABLE_PARTITIONED` (
`TIMESTAMP` datetime NOT NULL,
`INTERVALL` int NOT NULL,
`IDENT_KEY` int NOT NULL,
`FIELD_1` double DEFAULT NULL,
`FIELD_2` double DEFAULT NULL,
`FIELD_3` double DEFAULT NULL
)
PARTITION BY RANGE COLUMNS(`TIMESTAMP`)
(
PARTITION p20210321 VALUES LESS THAN ('2021-03-22'),
PARTITION p20210322 VALUES LESS THAN ('2021-03-23'),
PARTITION p20210323 VALUES LESS THAN MAXVALUE
);
END |
call create_tables();
call add_columns('TEST_TABLE', 'INPLACE');
call add_columns('TEST_TABLE_PARTITIONED', 'INPLACE');
ALTER TABLE TEST_TABLE ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Too many columns. Try ALGORITHM=COPY.
ALTER TABLE TEST_TABLE_PARTITIONED ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Too many columns. Try ALGORITHM=COPY.
TRUNCATE TABLE TEST_TABLE;
TRUNCATE TABLE TEST_TABLE_PARTITIONED;
DROP TABLE TEST_TABLE;
DROP TABLE TEST_TABLE_PARTITIONED;
call create_tables();
call add_columns('TEST_TABLE', 'INSTANT');
call add_columns('TEST_TABLE_PARTITIONED', 'INSTANT');
ALTER TABLE TEST_TABLE ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=INSTANT;
ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Too many columns. Try ALGORITHM=COPY/INPLACE.
ALTER TABLE TEST_TABLE_PARTITIONED ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=INSTANT;
ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Too many columns. Try ALGORITHM=COPY/INPLACE.
TRUNCATE TABLE TEST_TABLE;
TRUNCATE TABLE TEST_TABLE_PARTITIONED;
DROP TABLE TEST_TABLE;
DROP TABLE TEST_TABLE_PARTITIONED;
call create_tables();
call add_columns('TEST_TABLE', 'COPY');
call add_columns('TEST_TABLE_PARTITIONED', 'COPY');
ALTER TABLE TEST_TABLE ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=COPY;
ERROR HY000: Too many columns
ALTER TABLE TEST_TABLE_PARTITIONED ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=COPY;
ERROR HY000: Too many columns
TRUNCATE TABLE TEST_TABLE;
TRUNCATE TABLE TEST_TABLE_PARTITIONED;
DROP TABLE TEST_TABLE;
DROP TABLE TEST_TABLE_PARTITIONED;
call create_tables();
call add_columns('TEST_TABLE', 'DEFAULT');
call add_columns('TEST_TABLE_PARTITIONED', 'DEFAULT');
ALTER TABLE TEST_TABLE ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=DEFAULT;
ERROR HY000: Too many columns
ALTER TABLE TEST_TABLE_PARTITIONED ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=DEFAULT;
ERROR HY000: Too many columns
TRUNCATE TABLE TEST_TABLE;
TRUNCATE TABLE TEST_TABLE_PARTITIONED;
DROP TABLE TEST_TABLE;
DROP TABLE TEST_TABLE_PARTITIONED;
DROP PROCEDURE create_tables;
DROP PROCEDURE add_columns;
|
