1#
2# Scenario 1:
3# Create a small table with row_format compressed and adding instant column should fail
4#
5CREATE TABLE t1 (a INT, b INT) ROW_FORMAT=COMPRESSED;
6INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
7ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 5, ALGORITHM=INSTANT;
8ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
9DROP TABLE t1;
10#
11# Scenario 2:
12# Create a small table and adding instant column between two columns should fail
13#
14CREATE TABLE t1 (a INT, b INT);
15INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
16ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 5 AFTER a, ALGORITHM=INSTANT;
17ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
18ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 5 FIRST, ALGORITHM=INSTANT;
19ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
20ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 5 AFTER b, ALGORITHM=INSTANT;
21ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 6 AFTER c1, ALGORITHM=INPLACE;
22ALTER TABLE t1 ADD COLUMN d INT GENERATED ALWAYS AS(a * b) AFTER a, ALGORITHM=INSTANT;
23SHOW CREATE TABLE t1;
24Table	Create Table
25t1	CREATE TABLE `t1` (
26  `a` int DEFAULT NULL,
27  `d` int GENERATED ALWAYS AS ((`a` * `b`)) VIRTUAL,
28  `b` int DEFAULT NULL,
29  `c1` int DEFAULT '5',
30  `c2` int DEFAULT '6'
31) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
32DROP TABLE t1;
33#
34# Scenario 3:
35# Create a small table and adding spatial types with not null should fail
36#
37CREATE TABLE t1 (a INT, b INT);
38INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
39ALTER TABLE t1 ADD COLUMN c1 POINT NOT NULL, ALGORITHM = INSTANT;
40ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
41ALTER TABLE t1 ADD COLUMN d1 LINESTRING  NOT NULL, ALGORITHM = INSTANT;
42ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
43ALTER TABLE t1 ADD COLUMN e1 POLYGON  NOT NULL, ALGORITHM = INSTANT;
44ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
45ALTER TABLE t1 ADD COLUMN f1 MULTIPOINT  NOT NULL, ALGORITHM = INSTANT;
46ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
47ALTER TABLE t1 ADD COLUMN g1 MULTILINESTRING  NOT NULL, ALGORITHM = INSTANT;
48ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
49ALTER TABLE t1 ADD COLUMN h1 MULTIPOLYGON  NOT NULL, ALGORITHM = INSTANT;
50ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
51ALTER TABLE t1 ADD COLUMN i1 GEOMETRYCOLLECTION  NOT NULL, ALGORITHM = INSTANT;
52ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
53ALTER TABLE t1 ADD COLUMN j1 GEOMETRY  NOT NULL, ALGORITHM = INSTANT;
54ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
55DROP TABLE t1;
56#
57# Scenario 4:
58# Create a temporary table and adding instant column should fail
59#
60CREATE TEMPORARY TABLE tmp1(a int);
61ALTER TABLE tmp1 ADD COLUMN b int, ALGORITHM=INSTANT;
62ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY.
63DROP TABLE tmp1;
64#
65# Scenario 5:
66# trying to add instant columns for data-dictionary tables should fail
67#
68ALTER TABLE mysql.plugin ADD COLUMN c1 INT, ALGORITHM=INSTANT;
69ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
70ALTER TABLE mysql.user ADD COLUMN c1 INT, ALGORITHM=INSTANT;
71ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
72