##############################################
# Test instant ADD COLUMN for REDUNDANT format
##############################################
#
# Scenario 1:
# Create a small table, and add all kinds of new columns and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	NULL
INSERT INTO t1(a, c1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 1;
c1
1
ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL DEFAULT 10;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
name	default_value
c2	8000000a
INSERT INTO t1(a, c2) VALUES(0, 11);
SELECT count(*) = max(a) FROM t1 WHERE c2 = 10;
count(*) = max(a)
1
SELECT c2 FROM t1 WHERE c2 = 11;
c2
11
ALTER TABLE t1 ADD COLUMN d1 BIGINT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
name	default_value
d1	NULL
INSERT INTO t1(a, d1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
count(*) = max(a)
1
SELECT d1 FROM t1 WHERE d1 = 1;
d1
1
ALTER TABLE t1 ADD COLUMN d2 BIGINT NOT NULL DEFAULT 1234567890;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd2' AND has_default = 1;
name	default_value
d2	80000000499602d2
INSERT INTO t1(a, d2) VALUES(0, 1234);
SELECT count(*) = max(a) FROM t1 WHERE d2 = 1234567890;
count(*) = max(a)
1
SELECT d2 FROM t1 WHERE d2 = 1234;
d2
1234
ALTER TABLE t1 ADD COLUMN e1 SMALLINT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
name	default_value
e1	NULL
INSERT INTO t1(a, e1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
count(*) = max(a)
1
SELECT e1 FROM t1 WHERE e1 = 1;
e1
1
ALTER TABLE t1 ADD COLUMN e2 SMALLINT NOT NULL DEFAULT 10;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e2' AND has_default = 1;
name	default_value
e2	800a
INSERT INTO t1(a, e2) VALUES(0, 11);
SELECT count(*) = max(a) FROM t1 WHERE e2 = 10;
count(*) = max(a)
1
SELECT e2 FROM t1 WHERE e2 = 11;
e2
11
ALTER TABLE t1 ADD COLUMN f1 TINYINT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
name	default_value
f1	NULL
INSERT INTO t1(a, f1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
count(*) = max(a)
1
SELECT f1 FROM t1 WHERE f1 = 1;
f1
1
ALTER TABLE t1 ADD COLUMN f2 TINYINT NOT NULL DEFAULT 123;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f2' AND has_default = 1;
name	default_value
f2	fb
INSERT INTO t1(a, f2) VALUES(0, 12);
SELECT count(*) = max(a) FROM t1 WHERE f2 = 123;
count(*) = max(a)
1
SELECT f2 FROM t1 WHERE f2 = 12;
f2
12
ALTER TABLE t1 ADD COLUMN g1 MEDIUMINT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
name	default_value
g1	NULL
INSERT INTO t1(a, g1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
count(*) = max(a)
1
SELECT g1 FROM t1 WHERE g1 = 1;
g1
1
ALTER TABLE t1 ADD COLUMN g2 MEDIUMINT NOT NULL DEFAULT 12345;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g2' AND has_default = 1;
name	default_value
g2	803039
INSERT INTO t1(a, g2) VALUES(0, 1234);
SELECT count(*) = max(a) FROM t1 WHERE g2 = 12345;
count(*) = max(a)
1
SELECT g2 FROM t1 WHERE g2 = 1234;
g2
1234
ALTER TABLE t1 ADD COLUMN h1 FLOAT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
name	default_value
h1	NULL
INSERT INTO t1(a, h1) VALUES(0, 1.0);
SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
count(*) = max(a)
1
SELECT h1 FROM t1 WHERE h1 = 1;
h1
1
ALTER TABLE t1 ADD COLUMN h2 FLOAT NOT NULL DEFAULT 12.34;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h2' AND has_default = 1;
name	default_value
h2	a4704541
INSERT INTO t1(a, h2) VALUES(0, 1.234);
SELECT count(*) = max(a) FROM t1 WHERE h2 = 12.34;
count(*) = max(a)
NULL
SELECT h2 FROM t1 WHERE h2 = 1.234;
h2
ALTER TABLE t1 ADD COLUMN i1 DECIMAL(5, 2), ADD COLUMN i2 double, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'i1' OR name = 'i2') AND has_default = 1;
name	default_value
i1	NULL
i2	NULL
INSERT INTO t1(a, i1, i2) VALUES(0, 10.10, 20.20);
SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE i2 IS NULL;
count(*) = max(a)
1
SELECT i1 FROM t1 WHERE i1 = 10.10;
i1
10.10
SELECT i2 FROM t1 WHERE i2 = 20.20;
i2
20.2
ALTER TABLE t1 ADD COLUMN j1 DECIMAL(5, 2) NOT NULL DEFAULT 100.00, ADD COLUMN j2 double NOT NULL DEFAULT 1000.5678;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'j1' OR name = 'j2') AND has_default = 1;
name	default_value
j1	806400
j2	5af5b9da8a448f40
INSERT INTO t1(a, j1, j2) VALUES(0, 90.90, 1000.1234);
SELECT count(*) = max(a) FROM t1 WHERE j1 = 100.00;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE j2 = 1000.5678;
count(*) = max(a)
1
SELECT j1 FROM t1 WHERE j1 = 90.90;
j1
90.90
SELECT j2 FROM t1 WHERE j2 = 1000.1234;
j2
1000.1234
ALTER TABLE t1 ADD COLUMN k1 BIT(8), ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k1' AND has_default = 1;
name	default_value
k1	NULL
INSERT INTO t1(a, k1) VALUES(0, b'010101');
SELECT count(*) = max(a) FROM t1 WHERE k1 IS NULL;
count(*) = max(a)
1
SELECT hex(k1) FROM t1 WHERE k1 = b'010101';
hex(k1)
15
ALTER TABLE t1 ADD COLUMN k2 BIT(8) NOT NULL DEFAULT b'101010';
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k2' AND has_default = 1;
name	default_value
k2	2a
INSERT INTO t1(a, k2) VALUES(0, b'110011');
SELECT count(*) = max(a) FROM t1 WHERE k2 = b'101010';
count(*) = max(a)
1
SELECT hex(k2) FROM t1 WHERE k2 = b'110011';
hex(k2)
33
ALTER TABLE t1 ADD COLUMN l1 CHAR(50), ADD COLUMN l2 VARCHAR(100), ALGORITHM=DEFAULT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'l1' OR name = 'l2') AND has_default = 1;
name	default_value
l1	NULL
l2	NULL
INSERT INTO t1(a, l1, l2) VALUES(0, 'ABCD EFGH', 'abcdefg hijklmn ');
SELECT count(*) = max(a) FROM t1 WHERE l2 IS NULL;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE l1 IS NULL;
count(*) = max(a)
1
SELECT l1 FROM t1 WHERE l1 = 'ABCD EFGH';
l1
ABCD EFGH
SELECT l2 FROM t1 WHERE l2 = 'abcdefg hijklmn ';
l2
abcdefg hijklmn 
ALTER TABLE t1 ADD COLUMN m1 CHAR(50) default 'The fox jumps over', ADD COLUMN m2 VARCHAR(50) DEFAULT 'The fox jumps over the lazy dog.';
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'm1' OR name = 'm2') AND has_default = 1;
name	default_value
m1	54686520666f78206a756d7073206f7665722020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
m2	54686520666f78206a756d7073206f76657220746865206c617a7920646f672e
INSERT INTO t1(a, m1, m2) VALUES(0, 'over the lazy dog', 'The lazy dog jumps over the fox.');
SELECT count(*) = max(a) FROM t1 WHERE m1 = 'The fox jumps over';
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE m2 like 'The fox jumps%';
count(*) = max(a)
1
SELECT m1 FROM t1 WHERE m1 = 'over the lazy dog';
m1
over the lazy dog
SELECT m2 FROM t1 WHERE m2 like '%the fox.';
m2
The lazy dog jumps over the fox.
ALTER TABLE t1 ADD COLUMN n1 BINARY(10), ADD COLUMN n2 VARBINARY(10), ALGORITHM=DEFAULT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'n1' OR name = 'n2') AND has_default = 1;
name	default_value
n1	NULL
n2	NULL
INSERT INTO t1(a, n1, n2) VALUES(0, 0x010203040506070809, 0x102030405060708090);
SELECT count(*) = max(a) FROM t1 WHERE n1 IS NULL;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE n2 IS NULL;
count(*) = max(a)
1
SELECT hex(n1) FROM t1 WHERE n1 = 0x01020304050607080900;
hex(n1)
01020304050607080900
SELECT hex(n2) FROM t1 WHERE n2 = 0x102030405060708090;
hex(n2)
102030405060708090
ALTER TABLE t1 ADD COLUMN o1 BINARY(10) DEFAULT 0x11223344, ADD COLUMN o2 VARBINARY(10) DEFAULT 0x55667788;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'o1' OR name = 'o2') AND has_default = 1;
name	default_value
o1	11223344000000000000
o2	55667788
INSERT INTO t1(a, o1, o2) VALUES(0, 0x44332211, 0x88776655);
SELECT count(*) = max(a) FROM t1 WHERE o1 = 0x11223344000000000000;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE o2 = 0x55667788;
count(*) = max(a)
1
SELECT hex(o1) FROM t1 WHERE o1 = 0x44332211000000000000;
hex(o1)
44332211000000000000
SELECT hex(o2) FROM t1 WHERE o2 = 0x88776655;
hex(o2)
88776655
ALTER TABLE t1 ADD COLUMN p1 DATETIME, ALGORITHM=DEFAULT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p1' AND has_default = 1;
name	default_value
p1	NULL
INSERT INTO t1(a, p1) VALUES(0, '2017-12-31 00:00:00');
SELECT count(*) = max(a) FROM t1 WHERE p1 IS NULL;
count(*) = max(a)
1
SELECT p1 FROM t1 WHERE p1 = '2017-12-31 00:00:00';
p1
2017-12-31 00:00:00
ALTER TABLE t1 ADD COLUMN p2 DATETIME NOT NULL DEFAULT '2017-12-31 01:02:03';
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p2' AND has_default = 1;
name	default_value
p2	999e7e1083
SELECT count(*) = max(a) FROM t1 GROUP BY p2;
count(*) = max(a)
1
INSERT INTO t1(a, p2) VALUES(0, now());
SELECT count(*) FROM t1 GROUP BY p2;
count(*)
26
1
ALTER TABLE t1 ADD COLUMN q1 ENUM ('value1','value2','value3'), ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'q1' AND has_default = 1;
name	default_value
q1	NULL
INSERT INTO t1(a, q1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE q1 IS NULL;
count(*) = max(a)
1
SELECT q1 FROM t1 WHERE q1 = 1;
q1
value1
ALTER TABLE t1 ADD COLUMN r1 SET ('a','b','c'), ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'r1' AND has_default = 1;
name	default_value
r1	NULL
INSERT INTO t1(a, r1) VALUES(0, 'a');
SELECT count(*) = max(a) FROM t1 WHERE r1 IS NULL;
count(*) = max(a)
1
SELECT r1 FROM t1 WHERE r1 = 'a';
r1
a
ALTER TABLE t1 ADD COLUMN s1 BLOB, ADD COLUMN s2 TEXT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 's1' OR name = 's2') AND has_default = 1;
name	default_value
s1	NULL
s2	NULL
INSERT INTO t1(a, s1, s2) VALUES(0, 0x0102030405, 'abcd qwerty');
SELECT count(*) = max(a) FROM t1 WHERE s1 IS NULL;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE s2 IS NULL;
count(*) = max(a)
1
SELECT hex(s1) FROM t1 WHERE s1 = 0x0102030405;
hex(s1)
0102030405
SELECT s2 FROM t1 WHERE s2 = 'abcd qwerty';
s2
abcd qwerty
ALTER TABLE t1 ADD COLUMN u1 BLOB NOT NULL, ADD COLUMN u2 TEXT NOT NULL;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'u1' OR name = 'u2') AND has_default = 1;
name	default_value
u1	
u2	
INSERT INTO t1(a, u1, u2) VALUES(0, 0x0102030405, 'abcd qwerty');
SELECT count(*) = max(a) FROM t1 WHERE u1 = '';
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE u2 = '';
count(*) = max(a)
1
SELECT hex(u1) FROM t1 WHERE u1 = 0x0102030405;
hex(u1)
0102030405
SELECT u2 FROM t1 WHERE u2 = 'abcd qwerty';
u2
abcd qwerty
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) NOT NULL DEFAULT '10',
  `d1` bigint(20) DEFAULT NULL,
  `d2` bigint(20) NOT NULL DEFAULT '1234567890',
  `e1` smallint(6) DEFAULT NULL,
  `e2` smallint(6) NOT NULL DEFAULT '10',
  `f1` tinyint(4) DEFAULT NULL,
  `f2` tinyint(4) NOT NULL DEFAULT '123',
  `g1` mediumint(9) DEFAULT NULL,
  `g2` mediumint(9) NOT NULL DEFAULT '12345',
  `h1` float DEFAULT NULL,
  `h2` float NOT NULL DEFAULT '12.34',
  `i1` decimal(5,2) DEFAULT NULL,
  `i2` double DEFAULT NULL,
  `j1` decimal(5,2) NOT NULL DEFAULT '100.00',
  `j2` double NOT NULL DEFAULT '1000.5678',
  `k1` bit(8) DEFAULT NULL,
  `k2` bit(8) NOT NULL DEFAULT b'101010',
  `l1` char(50) DEFAULT NULL,
  `l2` varchar(100) DEFAULT NULL,
  `m1` char(50) DEFAULT 'The fox jumps over',
  `m2` varchar(50) DEFAULT 'The fox jumps over the lazy dog.',
  `n1` binary(10) DEFAULT NULL,
  `n2` varbinary(10) DEFAULT NULL,
  `o1` binary(10) DEFAULT '"3D\0\0\0\0\0\0',
  `o2` varbinary(10) DEFAULT 'Ufw?',
  `p1` datetime DEFAULT NULL,
  `p2` datetime NOT NULL DEFAULT '2017-12-31 01:02:03',
  `q1` enum('value1','value2','value3') DEFAULT NULL,
  `r1` set('a','b','c') DEFAULT NULL,
  `s1` blob,
  `s2` text,
  `u1` blob NOT NULL,
  `u2` text NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
#
# Scenario 2:
# Create a small table, add some columns instantly, along with
# virtual columns
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL, ADD COLUMN d INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(a, b, c) VALUES(0, 6, 20);
SELECT * FROM t1;
a	b	c	d
1	1	0	2
2	2	0	4
3	3	0	6
4	4	0	8
5	5	0	10
6	6	20	12
ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'ABCD EFGH', ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(e));
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(a, c, e) VALUES(0, 20, 'Hello'), (0, 20, 'World'), (0, 20, 'Hello World');
SELECT * FROM t1;
a	b	c	d	e	f
1	1	0	2	ABCD EFGH	9
2	2	0	4	ABCD EFGH	9
3	3	0	6	ABCD EFGH	9
4	4	0	8	ABCD EFGH	9
5	5	0	10	ABCD EFGH	9
6	6	20	12	ABCD EFGH	9
7	NULL	20	NULL	Hello	5
8	NULL	20	NULL	World	5
9	NULL	20	NULL	Hello World	11
ALTER TABLE t1 ADD COLUMN g VARCHAR(100) GENERATED ALWAYS AS (e), ADD COLUMN h BIGINT DEFAULT 10000, ADD COLUMN i BIGINT GENERATED ALWAYS AS (h * 2 + b);
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c	d	e	f	g	h	i
1	1	0	2	ABCD EFGH	9	ABCD EFGH	10000	20001
2	2	0	4	ABCD EFGH	9	ABCD EFGH	10000	20002
3	3	0	6	ABCD EFGH	9	ABCD EFGH	10000	20003
4	4	0	8	ABCD EFGH	9	ABCD EFGH	10000	20004
5	5	0	10	ABCD EFGH	9	ABCD EFGH	10000	20005
6	6	20	12	ABCD EFGH	9	ABCD EFGH	10000	20006
7	NULL	20	NULL	Hello	5	Hello	10000	NULL
8	NULL	20	NULL	World	5	World	10000	NULL
9	NULL	20	NULL	Hello World	11	Hello World	10000	NULL
INSERT INTO t1(a, b, c, h) VALUES(0, 7, 40, 2000), (0, 7, 40, 20000);
SELECT * FROM t1;
a	b	c	d	e	f	g	h	i
1	1	0	2	ABCD EFGH	9	ABCD EFGH	10000	20001
2	2	0	4	ABCD EFGH	9	ABCD EFGH	10000	20002
3	3	0	6	ABCD EFGH	9	ABCD EFGH	10000	20003
4	4	0	8	ABCD EFGH	9	ABCD EFGH	10000	20004
5	5	0	10	ABCD EFGH	9	ABCD EFGH	10000	20005
6	6	20	12	ABCD EFGH	9	ABCD EFGH	10000	20006
7	NULL	20	NULL	Hello	5	Hello	10000	NULL
8	NULL	20	NULL	World	5	World	10000	NULL
9	NULL	20	NULL	Hello World	11	Hello World	10000	NULL
10	7	40	14	ABCD EFGH	9	ABCD EFGH	2000	4007
11	7	40	14	ABCD EFGH	9	ABCD EFGH	20000	40007
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) GENERATED ALWAYS AS ((`b` * 2)) VIRTUAL,
  `e` varchar(100) DEFAULT 'ABCD EFGH',
  `f` int(11) GENERATED ALWAYS AS (length(`e`)) VIRTUAL,
  `g` varchar(100) GENERATED ALWAYS AS (`e`) VIRTUAL,
  `h` bigint(20) DEFAULT '10000',
  `i` bigint(20) GENERATED ALWAYS AS (((`h` * 2) + `b`)) VIRTUAL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
#
# Scenario 3:
# Create a small table, add some columns instantly, then change
# their default values, check original default values are correct
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
INSERT INTO t1(a, b, c, e) VALUES(0, 6, 200, 'Good day'), (0, 7, 300, 'Good DAY');
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND has_default = 1;
default_value
80000064
ALTER TABLE t1 ALTER COLUMN c SET DEFAULT 500;
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND
has_default = 1;
default_value
80000064
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
INSERT INTO t1(a, b) VALUES(0, 8), (0, 9);
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
8	8	500	1008	Hello world
9	9	500	1009	Hello world
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
default_value
48656c6c6f20776f726c64
ALTER TABLE t1 ALTER COLUMN e SET DEFAULT 'HELLO MySQL!';
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
default_value
48656c6c6f20776f726c64
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
8	8	500	1008	Hello world
9	9	500	1009	Hello world
INSERT INTO t1(a, b) VALUES(0, 10), (0, 20);
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
8	8	500	1008	Hello world
9	9	500	1009	Hello world
10	10	500	1010	HELLO MySQL!
11	20	500	1020	HELLO MySQL!
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '500',
  `d` int(11) GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
  `e` varchar(100) DEFAULT 'HELLO MySQL!',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
#
# Scenario 4:
# Create a small table, add some columns instantly, then do DML
# on the table
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
UPDATE t1 SET c = 200 WHERE a > 3;
SELECT distinct(c) FROM t1;
c
100
200
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
DELETE FROM t1 WHERE c = 100;
UPDATE t1 SET c = 300;
SELECT distinct(c) FROM t1;
c
300
SELECT count(*) FROM t1;
count(*)
16
ALTER TABLE t1 ADD COLUMN t DATETIME DEFAULT CURRENT_TIMESTAMP;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
UPDATE t1 SET e = 'Hello MySQL' WHERE a > 10;
UPDATE t1 SET e = 'Hello MySQL!!' WHERE a > 20;
SELECT distinct(e) FROM t1;
e
Hello world
Hello MySQL
Hello MySQL!!
UPDATE t1 SET c = 500 WHERE e LIKE '%world%';
SELECT c, e FROM t1 GROUP BY c, e;
c	e
500	Hello world
300	Hello MySQL
300	Hello MySQL!!
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
UPDATE t1 SET t = CURRENT_TIMESTAMP WHERE a < 50;
SELECT count(t) FROM t1 GROUP BY t;
count(t)
16
16
DELETE FROM t1 WHERE a < 50;
SELECT count(t) FROM t1 GROUP BY t;
count(t)
16
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '100',
  `d` int(11) GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
  `e` varchar(100) DEFAULT 'Hello world',
  `t` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
#
# Scenario 5:
# Create a small table, add some columns instantly, then do DDL
# to build indexes
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD KEY(c);
EXPLAIN SELECT c FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	c	#	NULL	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`c` AS `c` from `test`.`t1`
SELECT c FROM t1 WHERE c != 100;
c
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
UPDATE t1 SET e = 'Hello MySQL' WHERE a > 30;
ALTER TABLE t1 ADD KEY(e);
EXPLAIN SELECT e FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	e	#	NULL	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`e` AS `e` from `test`.`t1`
SELECT count(e) FROM t1 WHERE e LIKE '%MySQL%';
count(e)
17
SELECT count(e) FROM t1 WHERE e LIKE '%world%';
count(e)
23
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a, c);
SELECT a, c, e FROM t1 WHERE a > 25 AND a < 40;
a	c	e
28	100	Hello world
29	100	Hello world
30	100	Hello world
31	100	Hello MySQL
32	100	Hello MySQL
33	100	Hello MySQL
34	100	Hello MySQL
35	100	Hello MySQL
36	100	Hello MySQL
37	100	Hello MySQL
38	100	Hello MySQL
39	100	Hello MySQL
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '100',
  `d` int(11) GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
  `e` varchar(100) DEFAULT 'Hello world',
  PRIMARY KEY (`a`,`c`),
  KEY `c` (`c`),
  KEY `e` (`e`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
#
# Scenario 6:
# Create a small table, add some columns instantly, then do DML
# on the table, and some simple rollback
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
START TRANSACTION;
INSERT INTO t1(a, b) VALUES(0, 6);
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	100	206	Hello world
ROLLBACK;
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
START TRANSACTION;
UPDATE t1 SET c = 500 WHERE a = 1;
UPDATE t1 SET b = 1000 WHERE a = 2;
SELECT a, b, c FROM t1 WHERE a = 1 OR a = 2;
a	b	c
1	1	500
2	1000	100
ROLLBACK;
SELECT a, b, c FROM t1;
a	b	c
1	1	100
2	2	100
3	3	100
4	4	100
5	5	100
START TRANSACTION;
DELETE FROM t1 WHERE a < 5;
INSERT INTO t1(a, b) VALUES(0, 6);
SELECT * FROM t1;
a	b	c	d	e
5	5	100	205	Hello world
7	6	100	206	Hello world
ROLLBACK;
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '100',
  `d` int(11) GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
  `e` varchar(100) DEFAULT 'Hello world',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
SELECT id, length(c1), length(c2), length(c3) FROM t1;
id	length(c1)	length(c2)	length(c3)
1	4000	4000	1
ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd';
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
id	length(c1)	length(c2)	length(c3)	length(c4)
1	4000	4000	1	500
START TRANSACTION;
UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
ROLLBACK;
SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
id	length(c1)	length(c2)	length(c3)	length(c4)
1	4000	4000	1	500
START TRANSACTION;
UPDATE t1 SET c4 = 'x' WHERE id = 1;
ROLLBACK;
SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
id	length(c1)	length(c2)	length(c3)	length(c4)
1	4000	4000	1	500
DROP TABLE t1;
#
# Scenario 7:
# Confirm some ADD COLUMN are instant, some are not
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100 AFTER b;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
Table ID differed
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
Expect 0
0
ALTER TABLE t1 ADD COLUMN e INT NOT NULL DEFAULT 100, ADD KEY(e);
Table ID differed
count(*) = 1
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
Expect 0
0
ALTER TABLE t1 ADD COLUMN f INT NOT NULL DEFAULT 100, FORCE;
Table ID differed
count(*) = 1
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
Expect 0
0
ALTER TABLE t1 ADD COLUMN g INT NOT NULL DEFAULT 100, ALGORITHM=INPLACE;
Table ID differed
count(*) = 1
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
Expect 0
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `d` int(11) NOT NULL DEFAULT '100',
  `c` int(11) NOT NULL DEFAULT '100',
  `e` int(11) NOT NULL DEFAULT '100',
  `f` int(11) NOT NULL DEFAULT '100',
  `g` int(11) NOT NULL DEFAULT '100',
  PRIMARY KEY (`a`),
  KEY `e` (`e`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c TEXT, FULLTEXT(c)) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1, 'Hello'), (0, 2, 'HELLO'), (0, 3, 'World'), (0, 4, 'Hello world'), (0, 5, 'HELLO WORLD');
ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
Table ID differed
count(*) = 1
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
#
# Scenario 8:
# Check FK constraints on instantly added columns
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
CREATE TABLE t2(a INT NOT NULL PRIMARY KEY, b INT, c INT, KEY(c));
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
INSERT INTO t2 VALUES(1, 2, 3), (2, 3, 4), (3, 4, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 3;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
UPDATE t1 SET c = 4 WHERE a = 2;
UPDATE t1 SET c = 5 WHERE a = 3;
ALTER TABLE t1 ADD KEY(c);
ALTER TABLE t2 ADD FOREIGN KEY (c) REFERENCES t1(c) ON UPDATE CASCADE, ALGORITHM = COPY;
UPDATE t1 SET c = 10 WHERE a = 1;
SELECT c FROM t2;
c
4
5
10
UPDATE t1 SET c = 10;
SELECT c FROM t2;
c
10
10
10
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '3',
  PRIMARY KEY (`a`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t2;
DROP TABLE t1;
#
# Scenario 9:
# Instant ADD COLUMN on partitioned table, only simple test here
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT PARTITION BY HASH(a) PARTITIONS 3;;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c
3	3	5
6	6	5
1	1	5
4	4	5
7	7	5
2	2	5
5	5	5
8	8	5
INSERT INTO t1 VALUES(0, 9, 10), (0, 10, 20);
SELECT * FROM t1 WHERE b > 8;
a	b	c
9	9	10
10	10	20
UPDATE t1 SET c = 8 WHERE a = 1 OR a = 3 OR a = 5 OR a = 7;
SELECT * FROM t1;
a	b	c
3	3	8
6	6	5
9	9	10
1	1	8
4	4	5
7	7	8
10	10	20
2	2	5
5	5	8
8	8	5
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '5',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 3 */
DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT) ROW_FORMAT=REDUNDANT PARTITION BY HASH(a) PARTITIONS 2;;
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 CHANGE COLUMN c c1 INT;
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
#
# Scenario 10:
# EXCHANGE PARTITION is not allowed if either is instant
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT NOT NULL DEFAULT 5) ROW_FORMAT=REDUNDANT PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30));;
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
ERROR HY000: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
ALTER TABLE t2 ADD COLUMN d INT;
ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=INPLACE;
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
ERROR HY000: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
OPTIMIZE TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	optimize	note	Table does not support optimize, doing recreate + analyze instead
test.t2	optimize	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '5',
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '5',
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
/*!50100 PARTITION BY RANGE (`a`)
(PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (30) ENGINE = InnoDB) */
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
DROP TABLE t1, t2;
#
# Scenario 11:
# PRIMARY KEY with more than one column, at least to verify it works with REDUDANT
#
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b)) ROW_FORMAT=REDUNDANT;;
INSERT INTO t1 VALUES(0, 1), (1, 2), (2, 3), (3, 4);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c
0	1	5
1	2	5
2	3	5
3	4	5
UPDATE t1 SET c = b WHERE b <= 2;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c
0	1	1
1	2	2
2	3	5
3	4	5
DROP TABLE t1;
#
# Scenario 12:
# Mix ALTER PARTITION and ALTER TABLE ... INPLACE. This is to check if first partition is not
# instant after ALTER PARTITION, will the metadata be copied correctly
#
CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ENGINE = InnoDB PARTITION BY RANGE(col1 * 2) ( PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256) , PARTITION p2 VALUES LESS THAN (384) , PARTITION p3 VALUES LESS THAN MAXVALUE);;
INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500), ADD COLUMN col6 TEXT;
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 4` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 4
4
ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128));
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10));
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table);
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD KEY idx3(col3);
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
col1	col2	col3	col4	col5	col6
1	2	3	abcdefg	NULL	NULL
100	200	300	qwerty	NULL	NULL
200	300	400	asdfg	NULL	NULL
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT PARTITION BY HASH(a) PARTITIONS 3;;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
ALTER TABLE t1 ADD PARTITION PARTITIONS 10;
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 0
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD KEY(b);
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 0
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c
1	1	5
2	2	5
3	3	5
4	4	5
5	5	5
6	6	5
7	7	5
8	8	5
DROP TABLE t1;
#
# Scenario 13:
# Create a table with a two level clustered index, do instant ADD COLUMN, then the non-leaf node
# should be parsed correctly
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, d INT NOT NULL, b BLOB NOT NULL, c VARCHAR(87), INDEX(d), INDEX(a ASC), PRIMARY KEY (a, d DESC, c DESC)) ROW_FORMAT=REDUNDANT PARTITION BY LINEAR KEY(c) PARTITIONS 9;;
INSERT INTO t1(d, b, c) VALUES(1, 2, 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(2, 3, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(3, 4, 'bbbbbbbbbbaaaaaaaaaahhhhhhhhhhccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(4, 5, 'eeeeeeeeeehhhhhhhhhhbbbbbbbbbbccccccccccddddddddddaaaaaaaaaaffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(5, 6, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbddddddddddcccccccccceeeeeeeeeeffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(6, 7, 'cccccccccchhhhhhhhhhbbbbbbbbbbaaaaaaaaaaddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
ALTER TABLE t1 ADD COLUMN nc086 BIGINT NOT NULL FIRST, ALGORITHM=INPLACE, LOCK=DEFAULT;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD COLUMN nc082 TINYTEXT;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT COUNT(*) FROM t1;
COUNT(*)
384
DROP TABLE t1;
#
# Scenario 14:
# Create a small table, and add GIS kinds of new columns and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 POINT, ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	NULL
INSERT INTO t1(a, c1) VALUES(0, ST_PointFromText('POINT(10 10)'));
SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN d1 LINESTRING, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
name	default_value
d1	NULL
INSERT INTO t1(a, d1) VALUES(0, ST_LineFromText('LINESTRING(0 0,0 10,10 0)'));
SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN e1 POLYGON, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
name	default_value
e1	NULL
INSERT INTO t1(a, e1) VALUES(0, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'));
SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN f1 MULTIPOINT, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
name	default_value
f1	NULL
INSERT INTO t1(a, f1) VALUES(0, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)'));
SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN g1 MULTILINESTRING, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
name	default_value
g1	NULL
INSERT INTO t1(a, g1) VALUES(0, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'));
SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN h1 MULTIPOLYGON, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
name	default_value
h1	NULL
INSERT INTO t1(a, h1) VALUES(0, ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'));
SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN i1 GEOMETRYCOLLECTION, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'i1' AND has_default = 1;
name	default_value
i1	NULL
INSERT INTO t1(a, i1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN j1 GEOMETRY, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'j1' AND has_default = 1;
name	default_value
j1	NULL
INSERT INTO t1(a, j1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
SELECT count(*) = max(a) FROM t1 WHERE j1 IS NULL;
count(*) = max(a)
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` point DEFAULT NULL,
  `d1` linestring DEFAULT NULL,
  `e1` polygon DEFAULT NULL,
  `f1` multipoint DEFAULT NULL,
  `g1` multilinestring DEFAULT NULL,
  `h1` multipolygon DEFAULT NULL,
  `i1` geomcollection DEFAULT NULL,
  `j1` geometry DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
#
# Scenario 15:
# Create a small table, and add JSON columns and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 JSON, ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	NULL
INSERT INTO t1(a, c1) VALUES(0, '{"key1": "value1", "key2": "value2"}');
SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
count(*) = max(a)
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` json DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
#
# Scenario 16:
# Create a small table, and add INSTANT columns and verify with trigger
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
INSERT INTO t2 VALUES(0,6);
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
DROP TRIGGER t1_ai;
ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
c1	63636363
CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW
INSERT INTO t1(a,c1) VALUES(0,'eeee');
INSERT INTO t2(a, c1) VALUES(0, 'dddd');
SELECT count(*) = max(a) FROM t2 WHERE c1='cccc';
count(*) = max(a)
1
SELECT c1 FROM t2 WHERE c1 = 'dddd';
c1
dddd
DROP TRIGGER t2_ai;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'cccc',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1,t2;
#
# Scenario 17:
# Create a small table, and add INSTANT columns and verify with storedprocedure
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
CREATE PROCEDURE p1() INSERT INTO t1(a,c1) VALUES(0, 'bbbb');
CALL p1();
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
DROP PROCEDURE p1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
#
# Scenario 18:
# Create a small table, and add INSTANT columns and verify with view
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
CREATE VIEW v1 AS SELECT * FROM t1;
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
SELECT * FROM v1;
a	b	c1
1	1	aaaa
2	2	aaaa
3	3	aaaa
4	4	aaaa
5	5	aaaa
6	NULL	bbbb
DROP VIEW v1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
#
# Scenario 19:
# Create a small table, and add INSTANT columns and drop it and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
ALTER TABLE t1 DROP COLUMN c1;
Table ID differed
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
#
# Scenario 20:
# Create a small table, and add INSTANT columns and rename table
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 RENAME t2;
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
INSERT INTO t2(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t2 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t2 WHERE c1 = 'bbbb';
c1
bbbb
CHECK TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	check	status	OK
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t2;
#
# Scenario 21:
# Create a small table, and add INSTANT columns and change its data type INSTANTly won't work
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
ALTER TABLE t1 CHANGE c1 c2 CHAR(10) NOT NULL DEFAULT 'cccc';
Table ID differed
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c2` char(10) NOT NULL DEFAULT 'cccc',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
#
# Scenario 22:
# Create a small table, and add INSTANT columns and create hash,btree multi column index and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
CREATE INDEX id1 ON t1(c1) USING BTREE;
CREATE INDEX id2 ON t1(c1) USING HASH;
Warnings:
Note	3502	This storage engine does not support the HASH index algorithm, storage engine default was used instead.
Warning	1831	Duplicate index 'id2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
DROP INDEX id1 ON t1;
DROP INDEX id2 ON t1;
ALTER TABLE t1 ADD COLUMN c2 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
name	default_value
c2	63636363
CREATE INDEX id1 ON t1(c1 ASC,c2 DESC) USING BTREE;
INSERT INTO t1(a, c2) VALUES(0, 'dddd');
SELECT count(*) = max(a) FROM t1 WHERE c1='cccc';
count(*) = max(a)
NULL
SELECT c1 FROM t1 WHERE c1 = 'dddd';
c1
ALTER TABLE t1 RENAME INDEX id1 TO id2;
DROP INDEX id2 ON t1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  `c2` varchar(10) NOT NULL DEFAULT 'cccc',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
#
# Scenario 23:
# Create a small table, and add INSTANT columns and perform table join operation
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
INSERT INTO t1(a, c1) VALUES(0, 'cccc');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'cccc';
c1
cccc
ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'bbbb', ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
c1	62626262
INSERT INTO t2(a, c1) VALUES(0, 'cccc');
SELECT count(*) = max(a) FROM t2 WHERE c1='bbbb';
count(*) = max(a)
1
SELECT c1 FROM t2 WHERE c1 = 'cccc';
c1
cccc
SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;
a	b	c1	a	b	c1
6	NULL	cccc	6	NULL	cccc
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'bbbb',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
DROP TABLE t2;
#
# Scenario 24:
# Create a small table, and add stored and(or) virtual columns
# after last stored column in the table
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT, d INT GENERATED ALWAYS AS (b + c) VIRTUAL, e INT GENERATED ALWAYS AS (b * c) VIRTUAL) ROW_FORMAT=REDUNDANT;
INSERT INTO t1(a, b, c) VALUES(0, 1, 2), (0, 2, 3), (0, 3, 4), (0, 4, 5), (0, 5, 6);
ALTER TABLE t1 ADD COLUMN h INT NOT NULL AFTER c;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(a, b, c, h) VALUES(0, 6, 20, 40);
SELECT * FROM t1;
a	b	c	h	d	e
1	1	2	0	3	2
2	2	3	0	5	6
3	3	4	0	7	12
4	4	5	0	9	20
5	5	6	0	11	30
6	6	20	40	26	120
ALTER TABLE t1 ADD COLUMN i VARCHAR(100) DEFAULT 'ABCD EFGH' AFTER h, ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(i)) AFTER i;
Table id did not change
SELECT 3 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(a, b, c, h, i) VALUES(0, 20, 30, 50, 'qwerty');
SELECT * FROM t1;
a	b	c	h	i	f	d	e
1	1	2	0	ABCD EFGH	9	3	2
2	2	3	0	ABCD EFGH	9	5	6
3	3	4	0	ABCD EFGH	9	7	12
4	4	5	0	ABCD EFGH	9	9	20
5	5	6	0	ABCD EFGH	9	11	30
6	6	20	40	ABCD EFGH	9	26	120
7	20	30	50	qwerty	6	50	600
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `h` int(11) NOT NULL,
  `i` varchar(100) DEFAULT 'ABCD EFGH',
  `f` int(11) GENERATED ALWAYS AS (length(`i`)) VIRTUAL,
  `d` int(11) GENERATED ALWAYS AS ((`b` + `c`)) VIRTUAL,
  `e` int(11) GENERATED ALWAYS AS ((`b` * `c`)) VIRTUAL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
DROP TABLE t1;
############################################
# Test instant ADD COLUMN for DYNAMIC format
############################################
#
# Scenario 1:
# Create a small table, and add all kinds of new columns and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	NULL
INSERT INTO t1(a, c1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 1;
c1
1
ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL DEFAULT 10;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
name	default_value
c2	8000000a
INSERT INTO t1(a, c2) VALUES(0, 11);
SELECT count(*) = max(a) FROM t1 WHERE c2 = 10;
count(*) = max(a)
1
SELECT c2 FROM t1 WHERE c2 = 11;
c2
11
ALTER TABLE t1 ADD COLUMN d1 BIGINT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
name	default_value
d1	NULL
INSERT INTO t1(a, d1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
count(*) = max(a)
1
SELECT d1 FROM t1 WHERE d1 = 1;
d1
1
ALTER TABLE t1 ADD COLUMN d2 BIGINT NOT NULL DEFAULT 1234567890;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd2' AND has_default = 1;
name	default_value
d2	80000000499602d2
INSERT INTO t1(a, d2) VALUES(0, 1234);
SELECT count(*) = max(a) FROM t1 WHERE d2 = 1234567890;
count(*) = max(a)
1
SELECT d2 FROM t1 WHERE d2 = 1234;
d2
1234
ALTER TABLE t1 ADD COLUMN e1 SMALLINT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
name	default_value
e1	NULL
INSERT INTO t1(a, e1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
count(*) = max(a)
1
SELECT e1 FROM t1 WHERE e1 = 1;
e1
1
ALTER TABLE t1 ADD COLUMN e2 SMALLINT NOT NULL DEFAULT 10;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e2' AND has_default = 1;
name	default_value
e2	800a
INSERT INTO t1(a, e2) VALUES(0, 11);
SELECT count(*) = max(a) FROM t1 WHERE e2 = 10;
count(*) = max(a)
1
SELECT e2 FROM t1 WHERE e2 = 11;
e2
11
ALTER TABLE t1 ADD COLUMN f1 TINYINT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
name	default_value
f1	NULL
INSERT INTO t1(a, f1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
count(*) = max(a)
1
SELECT f1 FROM t1 WHERE f1 = 1;
f1
1
ALTER TABLE t1 ADD COLUMN f2 TINYINT NOT NULL DEFAULT 123;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f2' AND has_default = 1;
name	default_value
f2	fb
INSERT INTO t1(a, f2) VALUES(0, 12);
SELECT count(*) = max(a) FROM t1 WHERE f2 = 123;
count(*) = max(a)
1
SELECT f2 FROM t1 WHERE f2 = 12;
f2
12
ALTER TABLE t1 ADD COLUMN g1 MEDIUMINT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
name	default_value
g1	NULL
INSERT INTO t1(a, g1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
count(*) = max(a)
1
SELECT g1 FROM t1 WHERE g1 = 1;
g1
1
ALTER TABLE t1 ADD COLUMN g2 MEDIUMINT NOT NULL DEFAULT 12345;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g2' AND has_default = 1;
name	default_value
g2	803039
INSERT INTO t1(a, g2) VALUES(0, 1234);
SELECT count(*) = max(a) FROM t1 WHERE g2 = 12345;
count(*) = max(a)
1
SELECT g2 FROM t1 WHERE g2 = 1234;
g2
1234
ALTER TABLE t1 ADD COLUMN h1 FLOAT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
name	default_value
h1	NULL
INSERT INTO t1(a, h1) VALUES(0, 1.0);
SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
count(*) = max(a)
1
SELECT h1 FROM t1 WHERE h1 = 1;
h1
1
ALTER TABLE t1 ADD COLUMN h2 FLOAT NOT NULL DEFAULT 12.34;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h2' AND has_default = 1;
name	default_value
h2	a4704541
INSERT INTO t1(a, h2) VALUES(0, 1.234);
SELECT count(*) = max(a) FROM t1 WHERE h2 = 12.34;
count(*) = max(a)
NULL
SELECT h2 FROM t1 WHERE h2 = 1.234;
h2
ALTER TABLE t1 ADD COLUMN i1 DECIMAL(5, 2), ADD COLUMN i2 double, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'i1' OR name = 'i2') AND has_default = 1;
name	default_value
i1	NULL
i2	NULL
INSERT INTO t1(a, i1, i2) VALUES(0, 10.10, 20.20);
SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE i2 IS NULL;
count(*) = max(a)
1
SELECT i1 FROM t1 WHERE i1 = 10.10;
i1
10.10
SELECT i2 FROM t1 WHERE i2 = 20.20;
i2
20.2
ALTER TABLE t1 ADD COLUMN j1 DECIMAL(5, 2) NOT NULL DEFAULT 100.00, ADD COLUMN j2 double NOT NULL DEFAULT 1000.5678;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'j1' OR name = 'j2') AND has_default = 1;
name	default_value
j1	806400
j2	5af5b9da8a448f40
INSERT INTO t1(a, j1, j2) VALUES(0, 90.90, 1000.1234);
SELECT count(*) = max(a) FROM t1 WHERE j1 = 100.00;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE j2 = 1000.5678;
count(*) = max(a)
1
SELECT j1 FROM t1 WHERE j1 = 90.90;
j1
90.90
SELECT j2 FROM t1 WHERE j2 = 1000.1234;
j2
1000.1234
ALTER TABLE t1 ADD COLUMN k1 BIT(8), ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k1' AND has_default = 1;
name	default_value
k1	NULL
INSERT INTO t1(a, k1) VALUES(0, b'010101');
SELECT count(*) = max(a) FROM t1 WHERE k1 IS NULL;
count(*) = max(a)
1
SELECT hex(k1) FROM t1 WHERE k1 = b'010101';
hex(k1)
15
ALTER TABLE t1 ADD COLUMN k2 BIT(8) NOT NULL DEFAULT b'101010';
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k2' AND has_default = 1;
name	default_value
k2	2a
INSERT INTO t1(a, k2) VALUES(0, b'110011');
SELECT count(*) = max(a) FROM t1 WHERE k2 = b'101010';
count(*) = max(a)
1
SELECT hex(k2) FROM t1 WHERE k2 = b'110011';
hex(k2)
33
ALTER TABLE t1 ADD COLUMN l1 CHAR(50), ADD COLUMN l2 VARCHAR(100), ALGORITHM=DEFAULT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'l1' OR name = 'l2') AND has_default = 1;
name	default_value
l1	NULL
l2	NULL
INSERT INTO t1(a, l1, l2) VALUES(0, 'ABCD EFGH', 'abcdefg hijklmn ');
SELECT count(*) = max(a) FROM t1 WHERE l2 IS NULL;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE l1 IS NULL;
count(*) = max(a)
1
SELECT l1 FROM t1 WHERE l1 = 'ABCD EFGH';
l1
ABCD EFGH
SELECT l2 FROM t1 WHERE l2 = 'abcdefg hijklmn ';
l2
abcdefg hijklmn 
ALTER TABLE t1 ADD COLUMN m1 CHAR(50) default 'The fox jumps over', ADD COLUMN m2 VARCHAR(50) DEFAULT 'The fox jumps over the lazy dog.';
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'm1' OR name = 'm2') AND has_default = 1;
name	default_value
m1	54686520666f78206a756d7073206f7665722020202020202020202020202020202020202020202020202020202020202020
m2	54686520666f78206a756d7073206f76657220746865206c617a7920646f672e
INSERT INTO t1(a, m1, m2) VALUES(0, 'over the lazy dog', 'The lazy dog jumps over the fox.');
SELECT count(*) = max(a) FROM t1 WHERE m1 = 'The fox jumps over';
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE m2 like 'The fox jumps%';
count(*) = max(a)
1
SELECT m1 FROM t1 WHERE m1 = 'over the lazy dog';
m1
over the lazy dog
SELECT m2 FROM t1 WHERE m2 like '%the fox.';
m2
The lazy dog jumps over the fox.
ALTER TABLE t1 ADD COLUMN n1 BINARY(10), ADD COLUMN n2 VARBINARY(10), ALGORITHM=DEFAULT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'n1' OR name = 'n2') AND has_default = 1;
name	default_value
n1	NULL
n2	NULL
INSERT INTO t1(a, n1, n2) VALUES(0, 0x010203040506070809, 0x102030405060708090);
SELECT count(*) = max(a) FROM t1 WHERE n1 IS NULL;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE n2 IS NULL;
count(*) = max(a)
1
SELECT hex(n1) FROM t1 WHERE n1 = 0x01020304050607080900;
hex(n1)
01020304050607080900
SELECT hex(n2) FROM t1 WHERE n2 = 0x102030405060708090;
hex(n2)
102030405060708090
ALTER TABLE t1 ADD COLUMN o1 BINARY(10) DEFAULT 0x11223344, ADD COLUMN o2 VARBINARY(10) DEFAULT 0x55667788;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'o1' OR name = 'o2') AND has_default = 1;
name	default_value
o1	11223344000000000000
o2	55667788
INSERT INTO t1(a, o1, o2) VALUES(0, 0x44332211, 0x88776655);
SELECT count(*) = max(a) FROM t1 WHERE o1 = 0x11223344000000000000;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE o2 = 0x55667788;
count(*) = max(a)
1
SELECT hex(o1) FROM t1 WHERE o1 = 0x44332211000000000000;
hex(o1)
44332211000000000000
SELECT hex(o2) FROM t1 WHERE o2 = 0x88776655;
hex(o2)
88776655
ALTER TABLE t1 ADD COLUMN p1 DATETIME, ALGORITHM=DEFAULT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p1' AND has_default = 1;
name	default_value
p1	NULL
INSERT INTO t1(a, p1) VALUES(0, '2017-12-31 00:00:00');
SELECT count(*) = max(a) FROM t1 WHERE p1 IS NULL;
count(*) = max(a)
1
SELECT p1 FROM t1 WHERE p1 = '2017-12-31 00:00:00';
p1
2017-12-31 00:00:00
ALTER TABLE t1 ADD COLUMN p2 DATETIME NOT NULL DEFAULT '2017-12-31 01:02:03';
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p2' AND has_default = 1;
name	default_value
p2	999e7e1083
SELECT count(*) = max(a) FROM t1 GROUP BY p2;
count(*) = max(a)
1
INSERT INTO t1(a, p2) VALUES(0, now());
SELECT count(*) FROM t1 GROUP BY p2;
count(*)
26
1
ALTER TABLE t1 ADD COLUMN q1 ENUM ('value1','value2','value3'), ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'q1' AND has_default = 1;
name	default_value
q1	NULL
INSERT INTO t1(a, q1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE q1 IS NULL;
count(*) = max(a)
1
SELECT q1 FROM t1 WHERE q1 = 1;
q1
value1
ALTER TABLE t1 ADD COLUMN r1 SET ('a','b','c'), ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'r1' AND has_default = 1;
name	default_value
r1	NULL
INSERT INTO t1(a, r1) VALUES(0, 'a');
SELECT count(*) = max(a) FROM t1 WHERE r1 IS NULL;
count(*) = max(a)
1
SELECT r1 FROM t1 WHERE r1 = 'a';
r1
a
ALTER TABLE t1 ADD COLUMN s1 BLOB, ADD COLUMN s2 TEXT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 's1' OR name = 's2') AND has_default = 1;
name	default_value
s1	NULL
s2	NULL
INSERT INTO t1(a, s1, s2) VALUES(0, 0x0102030405, 'abcd qwerty');
SELECT count(*) = max(a) FROM t1 WHERE s1 IS NULL;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE s2 IS NULL;
count(*) = max(a)
1
SELECT hex(s1) FROM t1 WHERE s1 = 0x0102030405;
hex(s1)
0102030405
SELECT s2 FROM t1 WHERE s2 = 'abcd qwerty';
s2
abcd qwerty
ALTER TABLE t1 ADD COLUMN u1 BLOB NOT NULL, ADD COLUMN u2 TEXT NOT NULL;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'u1' OR name = 'u2') AND has_default = 1;
name	default_value
u1	
u2	
INSERT INTO t1(a, u1, u2) VALUES(0, 0x0102030405, 'abcd qwerty');
SELECT count(*) = max(a) FROM t1 WHERE u1 = '';
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE u2 = '';
count(*) = max(a)
1
SELECT hex(u1) FROM t1 WHERE u1 = 0x0102030405;
hex(u1)
0102030405
SELECT u2 FROM t1 WHERE u2 = 'abcd qwerty';
u2
abcd qwerty
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) NOT NULL DEFAULT '10',
  `d1` bigint(20) DEFAULT NULL,
  `d2` bigint(20) NOT NULL DEFAULT '1234567890',
  `e1` smallint(6) DEFAULT NULL,
  `e2` smallint(6) NOT NULL DEFAULT '10',
  `f1` tinyint(4) DEFAULT NULL,
  `f2` tinyint(4) NOT NULL DEFAULT '123',
  `g1` mediumint(9) DEFAULT NULL,
  `g2` mediumint(9) NOT NULL DEFAULT '12345',
  `h1` float DEFAULT NULL,
  `h2` float NOT NULL DEFAULT '12.34',
  `i1` decimal(5,2) DEFAULT NULL,
  `i2` double DEFAULT NULL,
  `j1` decimal(5,2) NOT NULL DEFAULT '100.00',
  `j2` double NOT NULL DEFAULT '1000.5678',
  `k1` bit(8) DEFAULT NULL,
  `k2` bit(8) NOT NULL DEFAULT b'101010',
  `l1` char(50) DEFAULT NULL,
  `l2` varchar(100) DEFAULT NULL,
  `m1` char(50) DEFAULT 'The fox jumps over',
  `m2` varchar(50) DEFAULT 'The fox jumps over the lazy dog.',
  `n1` binary(10) DEFAULT NULL,
  `n2` varbinary(10) DEFAULT NULL,
  `o1` binary(10) DEFAULT '"3D\0\0\0\0\0\0',
  `o2` varbinary(10) DEFAULT 'Ufw?',
  `p1` datetime DEFAULT NULL,
  `p2` datetime NOT NULL DEFAULT '2017-12-31 01:02:03',
  `q1` enum('value1','value2','value3') DEFAULT NULL,
  `r1` set('a','b','c') DEFAULT NULL,
  `s1` blob,
  `s2` text,
  `u1` blob NOT NULL,
  `u2` text NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
#
# Scenario 2:
# Create a small table, add some columns instantly, along with
# virtual columns
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL, ADD COLUMN d INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(a, b, c) VALUES(0, 6, 20);
SELECT * FROM t1;
a	b	c	d
1	1	0	2
2	2	0	4
3	3	0	6
4	4	0	8
5	5	0	10
6	6	20	12
ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'ABCD EFGH', ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(e));
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(a, c, e) VALUES(0, 20, 'Hello'), (0, 20, 'World'), (0, 20, 'Hello World');
SELECT * FROM t1;
a	b	c	d	e	f
1	1	0	2	ABCD EFGH	9
2	2	0	4	ABCD EFGH	9
3	3	0	6	ABCD EFGH	9
4	4	0	8	ABCD EFGH	9
5	5	0	10	ABCD EFGH	9
6	6	20	12	ABCD EFGH	9
7	NULL	20	NULL	Hello	5
8	NULL	20	NULL	World	5
9	NULL	20	NULL	Hello World	11
ALTER TABLE t1 ADD COLUMN g VARCHAR(100) GENERATED ALWAYS AS (e), ADD COLUMN h BIGINT DEFAULT 10000, ADD COLUMN i BIGINT GENERATED ALWAYS AS (h * 2 + b);
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c	d	e	f	g	h	i
1	1	0	2	ABCD EFGH	9	ABCD EFGH	10000	20001
2	2	0	4	ABCD EFGH	9	ABCD EFGH	10000	20002
3	3	0	6	ABCD EFGH	9	ABCD EFGH	10000	20003
4	4	0	8	ABCD EFGH	9	ABCD EFGH	10000	20004
5	5	0	10	ABCD EFGH	9	ABCD EFGH	10000	20005
6	6	20	12	ABCD EFGH	9	ABCD EFGH	10000	20006
7	NULL	20	NULL	Hello	5	Hello	10000	NULL
8	NULL	20	NULL	World	5	World	10000	NULL
9	NULL	20	NULL	Hello World	11	Hello World	10000	NULL
INSERT INTO t1(a, b, c, h) VALUES(0, 7, 40, 2000), (0, 7, 40, 20000);
SELECT * FROM t1;
a	b	c	d	e	f	g	h	i
1	1	0	2	ABCD EFGH	9	ABCD EFGH	10000	20001
2	2	0	4	ABCD EFGH	9	ABCD EFGH	10000	20002
3	3	0	6	ABCD EFGH	9	ABCD EFGH	10000	20003
4	4	0	8	ABCD EFGH	9	ABCD EFGH	10000	20004
5	5	0	10	ABCD EFGH	9	ABCD EFGH	10000	20005
6	6	20	12	ABCD EFGH	9	ABCD EFGH	10000	20006
7	NULL	20	NULL	Hello	5	Hello	10000	NULL
8	NULL	20	NULL	World	5	World	10000	NULL
9	NULL	20	NULL	Hello World	11	Hello World	10000	NULL
10	7	40	14	ABCD EFGH	9	ABCD EFGH	2000	4007
11	7	40	14	ABCD EFGH	9	ABCD EFGH	20000	40007
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) GENERATED ALWAYS AS ((`b` * 2)) VIRTUAL,
  `e` varchar(100) DEFAULT 'ABCD EFGH',
  `f` int(11) GENERATED ALWAYS AS (length(`e`)) VIRTUAL,
  `g` varchar(100) GENERATED ALWAYS AS (`e`) VIRTUAL,
  `h` bigint(20) DEFAULT '10000',
  `i` bigint(20) GENERATED ALWAYS AS (((`h` * 2) + `b`)) VIRTUAL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
#
# Scenario 3:
# Create a small table, add some columns instantly, then change
# their default values, check original default values are correct
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
INSERT INTO t1(a, b, c, e) VALUES(0, 6, 200, 'Good day'), (0, 7, 300, 'Good DAY');
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND has_default = 1;
default_value
80000064
ALTER TABLE t1 ALTER COLUMN c SET DEFAULT 500;
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND
has_default = 1;
default_value
80000064
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
INSERT INTO t1(a, b) VALUES(0, 8), (0, 9);
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
8	8	500	1008	Hello world
9	9	500	1009	Hello world
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
default_value
48656c6c6f20776f726c64
ALTER TABLE t1 ALTER COLUMN e SET DEFAULT 'HELLO MySQL!';
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
default_value
48656c6c6f20776f726c64
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
8	8	500	1008	Hello world
9	9	500	1009	Hello world
INSERT INTO t1(a, b) VALUES(0, 10), (0, 20);
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
8	8	500	1008	Hello world
9	9	500	1009	Hello world
10	10	500	1010	HELLO MySQL!
11	20	500	1020	HELLO MySQL!
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '500',
  `d` int(11) GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
  `e` varchar(100) DEFAULT 'HELLO MySQL!',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
#
# Scenario 4:
# Create a small table, add some columns instantly, then do DML
# on the table
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
UPDATE t1 SET c = 200 WHERE a > 3;
SELECT distinct(c) FROM t1;
c
100
200
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
DELETE FROM t1 WHERE c = 100;
UPDATE t1 SET c = 300;
SELECT distinct(c) FROM t1;
c
300
SELECT count(*) FROM t1;
count(*)
16
ALTER TABLE t1 ADD COLUMN t DATETIME DEFAULT CURRENT_TIMESTAMP;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
UPDATE t1 SET e = 'Hello MySQL' WHERE a > 10;
UPDATE t1 SET e = 'Hello MySQL!!' WHERE a > 20;
SELECT distinct(e) FROM t1;
e
Hello world
Hello MySQL
Hello MySQL!!
UPDATE t1 SET c = 500 WHERE e LIKE '%world%';
SELECT c, e FROM t1 GROUP BY c, e;
c	e
500	Hello world
300	Hello MySQL
300	Hello MySQL!!
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
UPDATE t1 SET t = CURRENT_TIMESTAMP WHERE a < 50;
SELECT count(t) FROM t1 GROUP BY t;
count(t)
16
16
DELETE FROM t1 WHERE a < 50;
SELECT count(t) FROM t1 GROUP BY t;
count(t)
16
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '100',
  `d` int(11) GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
  `e` varchar(100) DEFAULT 'Hello world',
  `t` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
#
# Scenario 5:
# Create a small table, add some columns instantly, then do DDL
# to build indexes
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD KEY(c);
EXPLAIN SELECT c FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	c	#	NULL	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`c` AS `c` from `test`.`t1`
SELECT c FROM t1 WHERE c != 100;
c
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
UPDATE t1 SET e = 'Hello MySQL' WHERE a > 30;
ALTER TABLE t1 ADD KEY(e);
EXPLAIN SELECT e FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	e	#	NULL	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`e` AS `e` from `test`.`t1`
SELECT count(e) FROM t1 WHERE e LIKE '%MySQL%';
count(e)
17
SELECT count(e) FROM t1 WHERE e LIKE '%world%';
count(e)
23
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a, c);
SELECT a, c, e FROM t1 WHERE a > 25 AND a < 40;
a	c	e
28	100	Hello world
29	100	Hello world
30	100	Hello world
31	100	Hello MySQL
32	100	Hello MySQL
33	100	Hello MySQL
34	100	Hello MySQL
35	100	Hello MySQL
36	100	Hello MySQL
37	100	Hello MySQL
38	100	Hello MySQL
39	100	Hello MySQL
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '100',
  `d` int(11) GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
  `e` varchar(100) DEFAULT 'Hello world',
  PRIMARY KEY (`a`,`c`),
  KEY `c` (`c`),
  KEY `e` (`e`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
#
# Scenario 6:
# Create a small table, add some columns instantly, then do DML
# on the table, and some simple rollback
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
START TRANSACTION;
INSERT INTO t1(a, b) VALUES(0, 6);
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	100	206	Hello world
ROLLBACK;
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
START TRANSACTION;
UPDATE t1 SET c = 500 WHERE a = 1;
UPDATE t1 SET b = 1000 WHERE a = 2;
SELECT a, b, c FROM t1 WHERE a = 1 OR a = 2;
a	b	c
1	1	500
2	1000	100
ROLLBACK;
SELECT a, b, c FROM t1;
a	b	c
1	1	100
2	2	100
3	3	100
4	4	100
5	5	100
START TRANSACTION;
DELETE FROM t1 WHERE a < 5;
INSERT INTO t1(a, b) VALUES(0, 6);
SELECT * FROM t1;
a	b	c	d	e
5	5	100	205	Hello world
7	6	100	206	Hello world
ROLLBACK;
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '100',
  `d` int(11) GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
  `e` varchar(100) DEFAULT 'Hello world',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
SELECT id, length(c1), length(c2), length(c3) FROM t1;
id	length(c1)	length(c2)	length(c3)
1	4000	4000	1
ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd';
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
id	length(c1)	length(c2)	length(c3)	length(c4)
1	4000	4000	1	500
START TRANSACTION;
UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
ROLLBACK;
SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
id	length(c1)	length(c2)	length(c3)	length(c4)
1	4000	4000	1	500
START TRANSACTION;
UPDATE t1 SET c4 = 'x' WHERE id = 1;
ROLLBACK;
SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
id	length(c1)	length(c2)	length(c3)	length(c4)
1	4000	4000	1	500
DROP TABLE t1;
#
# Scenario 7:
# Confirm some ADD COLUMN are instant, some are not
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100 AFTER b;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
Table ID differed
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
Expect 0
0
ALTER TABLE t1 ADD COLUMN e INT NOT NULL DEFAULT 100, ADD KEY(e);
Table ID differed
count(*) = 1
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
Expect 0
0
ALTER TABLE t1 ADD COLUMN f INT NOT NULL DEFAULT 100, FORCE;
Table ID differed
count(*) = 1
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
Expect 0
0
ALTER TABLE t1 ADD COLUMN g INT NOT NULL DEFAULT 100, ALGORITHM=INPLACE;
Table ID differed
count(*) = 1
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
Expect 0
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `d` int(11) NOT NULL DEFAULT '100',
  `c` int(11) NOT NULL DEFAULT '100',
  `e` int(11) NOT NULL DEFAULT '100',
  `f` int(11) NOT NULL DEFAULT '100',
  `g` int(11) NOT NULL DEFAULT '100',
  PRIMARY KEY (`a`),
  KEY `e` (`e`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c TEXT, FULLTEXT(c)) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1, 'Hello'), (0, 2, 'HELLO'), (0, 3, 'World'), (0, 4, 'Hello world'), (0, 5, 'HELLO WORLD');
ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
Table ID differed
count(*) = 1
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
#
# Scenario 8:
# Check FK constraints on instantly added columns
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
CREATE TABLE t2(a INT NOT NULL PRIMARY KEY, b INT, c INT, KEY(c));
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
INSERT INTO t2 VALUES(1, 2, 3), (2, 3, 4), (3, 4, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 3;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
UPDATE t1 SET c = 4 WHERE a = 2;
UPDATE t1 SET c = 5 WHERE a = 3;
ALTER TABLE t1 ADD KEY(c);
ALTER TABLE t2 ADD FOREIGN KEY (c) REFERENCES t1(c) ON UPDATE CASCADE, ALGORITHM = COPY;
UPDATE t1 SET c = 10 WHERE a = 1;
SELECT c FROM t2;
c
4
5
10
UPDATE t1 SET c = 10;
SELECT c FROM t2;
c
10
10
10
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '3',
  PRIMARY KEY (`a`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t2;
DROP TABLE t1;
#
# Scenario 9:
# Instant ADD COLUMN on partitioned table, only simple test here
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC PARTITION BY HASH(a) PARTITIONS 3;;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c
3	3	5
6	6	5
1	1	5
4	4	5
7	7	5
2	2	5
5	5	5
8	8	5
INSERT INTO t1 VALUES(0, 9, 10), (0, 10, 20);
SELECT * FROM t1 WHERE b > 8;
a	b	c
9	9	10
10	10	20
UPDATE t1 SET c = 8 WHERE a = 1 OR a = 3 OR a = 5 OR a = 7;
SELECT * FROM t1;
a	b	c
3	3	8
6	6	5
9	9	10
1	1	8
4	4	5
7	7	8
10	10	20
2	2	5
5	5	8
8	8	5
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '5',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 3 */
DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT) ROW_FORMAT=DYNAMIC PARTITION BY HASH(a) PARTITIONS 2;;
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 CHANGE COLUMN c c1 INT;
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
#
# Scenario 10:
# EXCHANGE PARTITION is not allowed if either is instant
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT NOT NULL DEFAULT 5) ROW_FORMAT=DYNAMIC PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30));;
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
ERROR HY000: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
ALTER TABLE t2 ADD COLUMN d INT;
ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=INPLACE;
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
ERROR HY000: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
OPTIMIZE TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	optimize	note	Table does not support optimize, doing recreate + analyze instead
test.t2	optimize	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '5',
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '5',
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY RANGE (`a`)
(PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (30) ENGINE = InnoDB) */
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
DROP TABLE t1, t2;
#
# Scenario 11:
# PRIMARY KEY with more than one column, at least to verify it works with REDUDANT
#
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b)) ROW_FORMAT=DYNAMIC;;
INSERT INTO t1 VALUES(0, 1), (1, 2), (2, 3), (3, 4);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c
0	1	5
1	2	5
2	3	5
3	4	5
UPDATE t1 SET c = b WHERE b <= 2;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c
0	1	1
1	2	2
2	3	5
3	4	5
DROP TABLE t1;
#
# Scenario 12:
# Mix ALTER PARTITION and ALTER TABLE ... INPLACE. This is to check if first partition is not
# instant after ALTER PARTITION, will the metadata be copied correctly
#
CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ENGINE = InnoDB PARTITION BY RANGE(col1 * 2) ( PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256) , PARTITION p2 VALUES LESS THAN (384) , PARTITION p3 VALUES LESS THAN MAXVALUE);;
INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500), ADD COLUMN col6 TEXT;
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 4` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 4
4
ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128));
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10));
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table);
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD KEY idx3(col3);
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
col1	col2	col3	col4	col5	col6
1	2	3	abcdefg	NULL	NULL
100	200	300	qwerty	NULL	NULL
200	300	400	asdfg	NULL	NULL
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC PARTITION BY HASH(a) PARTITIONS 3;;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
ALTER TABLE t1 ADD PARTITION PARTITIONS 10;
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 0
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD KEY(b);
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 0
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c
1	1	5
2	2	5
3	3	5
4	4	5
5	5	5
6	6	5
7	7	5
8	8	5
DROP TABLE t1;
#
# Scenario 13:
# Create a table with a two level clustered index, do instant ADD COLUMN, then the non-leaf node
# should be parsed correctly
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, d INT NOT NULL, b BLOB NOT NULL, c VARCHAR(87), INDEX(d), INDEX(a ASC), PRIMARY KEY (a, d DESC, c DESC)) ROW_FORMAT=DYNAMIC PARTITION BY LINEAR KEY(c) PARTITIONS 9;;
INSERT INTO t1(d, b, c) VALUES(1, 2, 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(2, 3, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(3, 4, 'bbbbbbbbbbaaaaaaaaaahhhhhhhhhhccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(4, 5, 'eeeeeeeeeehhhhhhhhhhbbbbbbbbbbccccccccccddddddddddaaaaaaaaaaffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(5, 6, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbddddddddddcccccccccceeeeeeeeeeffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(6, 7, 'cccccccccchhhhhhhhhhbbbbbbbbbbaaaaaaaaaaddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
ALTER TABLE t1 ADD COLUMN nc086 BIGINT NOT NULL FIRST, ALGORITHM=INPLACE, LOCK=DEFAULT;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD COLUMN nc082 TINYTEXT;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT COUNT(*) FROM t1;
COUNT(*)
384
DROP TABLE t1;
#
# Scenario 14:
# Create a small table, and add GIS kinds of new columns and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 POINT, ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	NULL
INSERT INTO t1(a, c1) VALUES(0, ST_PointFromText('POINT(10 10)'));
SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN d1 LINESTRING, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
name	default_value
d1	NULL
INSERT INTO t1(a, d1) VALUES(0, ST_LineFromText('LINESTRING(0 0,0 10,10 0)'));
SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN e1 POLYGON, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
name	default_value
e1	NULL
INSERT INTO t1(a, e1) VALUES(0, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'));
SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN f1 MULTIPOINT, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
name	default_value
f1	NULL
INSERT INTO t1(a, f1) VALUES(0, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)'));
SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN g1 MULTILINESTRING, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
name	default_value
g1	NULL
INSERT INTO t1(a, g1) VALUES(0, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'));
SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN h1 MULTIPOLYGON, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
name	default_value
h1	NULL
INSERT INTO t1(a, h1) VALUES(0, ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'));
SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN i1 GEOMETRYCOLLECTION, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'i1' AND has_default = 1;
name	default_value
i1	NULL
INSERT INTO t1(a, i1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN j1 GEOMETRY, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'j1' AND has_default = 1;
name	default_value
j1	NULL
INSERT INTO t1(a, j1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
SELECT count(*) = max(a) FROM t1 WHERE j1 IS NULL;
count(*) = max(a)
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` point DEFAULT NULL,
  `d1` linestring DEFAULT NULL,
  `e1` polygon DEFAULT NULL,
  `f1` multipoint DEFAULT NULL,
  `g1` multilinestring DEFAULT NULL,
  `h1` multipolygon DEFAULT NULL,
  `i1` geomcollection DEFAULT NULL,
  `j1` geometry DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
#
# Scenario 15:
# Create a small table, and add JSON columns and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 JSON, ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	NULL
INSERT INTO t1(a, c1) VALUES(0, '{"key1": "value1", "key2": "value2"}');
SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
count(*) = max(a)
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` json DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
#
# Scenario 16:
# Create a small table, and add INSTANT columns and verify with trigger
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
INSERT INTO t2 VALUES(0,6);
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
DROP TRIGGER t1_ai;
ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
c1	63636363
CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW
INSERT INTO t1(a,c1) VALUES(0,'eeee');
INSERT INTO t2(a, c1) VALUES(0, 'dddd');
SELECT count(*) = max(a) FROM t2 WHERE c1='cccc';
count(*) = max(a)
1
SELECT c1 FROM t2 WHERE c1 = 'dddd';
c1
dddd
DROP TRIGGER t2_ai;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'cccc',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1,t2;
#
# Scenario 17:
# Create a small table, and add INSTANT columns and verify with storedprocedure
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
CREATE PROCEDURE p1() INSERT INTO t1(a,c1) VALUES(0, 'bbbb');
CALL p1();
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
DROP PROCEDURE p1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
#
# Scenario 18:
# Create a small table, and add INSTANT columns and verify with view
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
CREATE VIEW v1 AS SELECT * FROM t1;
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
SELECT * FROM v1;
a	b	c1
1	1	aaaa
2	2	aaaa
3	3	aaaa
4	4	aaaa
5	5	aaaa
6	NULL	bbbb
DROP VIEW v1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
#
# Scenario 19:
# Create a small table, and add INSTANT columns and drop it and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
ALTER TABLE t1 DROP COLUMN c1;
Table ID differed
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
#
# Scenario 20:
# Create a small table, and add INSTANT columns and rename table
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 RENAME t2;
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
INSERT INTO t2(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t2 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t2 WHERE c1 = 'bbbb';
c1
bbbb
CHECK TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	check	status	OK
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t2;
#
# Scenario 21:
# Create a small table, and add INSTANT columns and change its data type INSTANTly won't work
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
ALTER TABLE t1 CHANGE c1 c2 CHAR(10) NOT NULL DEFAULT 'cccc';
Table ID differed
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c2` char(10) NOT NULL DEFAULT 'cccc',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
#
# Scenario 22:
# Create a small table, and add INSTANT columns and create hash,btree multi column index and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
CREATE INDEX id1 ON t1(c1) USING BTREE;
CREATE INDEX id2 ON t1(c1) USING HASH;
Warnings:
Note	3502	This storage engine does not support the HASH index algorithm, storage engine default was used instead.
Warning	1831	Duplicate index 'id2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
DROP INDEX id1 ON t1;
DROP INDEX id2 ON t1;
ALTER TABLE t1 ADD COLUMN c2 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
name	default_value
c2	63636363
CREATE INDEX id1 ON t1(c1 ASC,c2 DESC) USING BTREE;
INSERT INTO t1(a, c2) VALUES(0, 'dddd');
SELECT count(*) = max(a) FROM t1 WHERE c1='cccc';
count(*) = max(a)
NULL
SELECT c1 FROM t1 WHERE c1 = 'dddd';
c1
ALTER TABLE t1 RENAME INDEX id1 TO id2;
DROP INDEX id2 ON t1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  `c2` varchar(10) NOT NULL DEFAULT 'cccc',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
#
# Scenario 23:
# Create a small table, and add INSTANT columns and perform table join operation
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
INSERT INTO t1(a, c1) VALUES(0, 'cccc');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'cccc';
c1
cccc
ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'bbbb', ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
c1	62626262
INSERT INTO t2(a, c1) VALUES(0, 'cccc');
SELECT count(*) = max(a) FROM t2 WHERE c1='bbbb';
count(*) = max(a)
1
SELECT c1 FROM t2 WHERE c1 = 'cccc';
c1
cccc
SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;
a	b	c1	a	b	c1
6	NULL	cccc	6	NULL	cccc
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'bbbb',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
DROP TABLE t2;
#
# Scenario 24:
# Create a small table, and add stored and(or) virtual columns
# after last stored column in the table
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT, d INT GENERATED ALWAYS AS (b + c) VIRTUAL, e INT GENERATED ALWAYS AS (b * c) VIRTUAL) ROW_FORMAT=DYNAMIC;
INSERT INTO t1(a, b, c) VALUES(0, 1, 2), (0, 2, 3), (0, 3, 4), (0, 4, 5), (0, 5, 6);
ALTER TABLE t1 ADD COLUMN h INT NOT NULL AFTER c;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(a, b, c, h) VALUES(0, 6, 20, 40);
SELECT * FROM t1;
a	b	c	h	d	e
1	1	2	0	3	2
2	2	3	0	5	6
3	3	4	0	7	12
4	4	5	0	9	20
5	5	6	0	11	30
6	6	20	40	26	120
ALTER TABLE t1 ADD COLUMN i VARCHAR(100) DEFAULT 'ABCD EFGH' AFTER h, ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(i)) AFTER i;
Table id did not change
SELECT 3 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(a, b, c, h, i) VALUES(0, 20, 30, 50, 'qwerty');
SELECT * FROM t1;
a	b	c	h	i	f	d	e
1	1	2	0	ABCD EFGH	9	3	2
2	2	3	0	ABCD EFGH	9	5	6
3	3	4	0	ABCD EFGH	9	7	12
4	4	5	0	ABCD EFGH	9	9	20
5	5	6	0	ABCD EFGH	9	11	30
6	6	20	40	ABCD EFGH	9	26	120
7	20	30	50	qwerty	6	50	600
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `h` int(11) NOT NULL,
  `i` varchar(100) DEFAULT 'ABCD EFGH',
  `f` int(11) GENERATED ALWAYS AS (length(`i`)) VIRTUAL,
  `d` int(11) GENERATED ALWAYS AS ((`b` + `c`)) VIRTUAL,
  `e` int(11) GENERATED ALWAYS AS ((`b` * `c`)) VIRTUAL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
DROP TABLE t1;
############################################
# Test instant ADD COLUMN for COMPACT format
############################################
#
# Scenario 1:
# Create a small table, and add all kinds of new columns and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	NULL
INSERT INTO t1(a, c1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 1;
c1
1
ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL DEFAULT 10;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
name	default_value
c2	8000000a
INSERT INTO t1(a, c2) VALUES(0, 11);
SELECT count(*) = max(a) FROM t1 WHERE c2 = 10;
count(*) = max(a)
1
SELECT c2 FROM t1 WHERE c2 = 11;
c2
11
ALTER TABLE t1 ADD COLUMN d1 BIGINT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
name	default_value
d1	NULL
INSERT INTO t1(a, d1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
count(*) = max(a)
1
SELECT d1 FROM t1 WHERE d1 = 1;
d1
1
ALTER TABLE t1 ADD COLUMN d2 BIGINT NOT NULL DEFAULT 1234567890;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd2' AND has_default = 1;
name	default_value
d2	80000000499602d2
INSERT INTO t1(a, d2) VALUES(0, 1234);
SELECT count(*) = max(a) FROM t1 WHERE d2 = 1234567890;
count(*) = max(a)
1
SELECT d2 FROM t1 WHERE d2 = 1234;
d2
1234
ALTER TABLE t1 ADD COLUMN e1 SMALLINT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
name	default_value
e1	NULL
INSERT INTO t1(a, e1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
count(*) = max(a)
1
SELECT e1 FROM t1 WHERE e1 = 1;
e1
1
ALTER TABLE t1 ADD COLUMN e2 SMALLINT NOT NULL DEFAULT 10;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e2' AND has_default = 1;
name	default_value
e2	800a
INSERT INTO t1(a, e2) VALUES(0, 11);
SELECT count(*) = max(a) FROM t1 WHERE e2 = 10;
count(*) = max(a)
1
SELECT e2 FROM t1 WHERE e2 = 11;
e2
11
ALTER TABLE t1 ADD COLUMN f1 TINYINT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
name	default_value
f1	NULL
INSERT INTO t1(a, f1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
count(*) = max(a)
1
SELECT f1 FROM t1 WHERE f1 = 1;
f1
1
ALTER TABLE t1 ADD COLUMN f2 TINYINT NOT NULL DEFAULT 123;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f2' AND has_default = 1;
name	default_value
f2	fb
INSERT INTO t1(a, f2) VALUES(0, 12);
SELECT count(*) = max(a) FROM t1 WHERE f2 = 123;
count(*) = max(a)
1
SELECT f2 FROM t1 WHERE f2 = 12;
f2
12
ALTER TABLE t1 ADD COLUMN g1 MEDIUMINT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
name	default_value
g1	NULL
INSERT INTO t1(a, g1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
count(*) = max(a)
1
SELECT g1 FROM t1 WHERE g1 = 1;
g1
1
ALTER TABLE t1 ADD COLUMN g2 MEDIUMINT NOT NULL DEFAULT 12345;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g2' AND has_default = 1;
name	default_value
g2	803039
INSERT INTO t1(a, g2) VALUES(0, 1234);
SELECT count(*) = max(a) FROM t1 WHERE g2 = 12345;
count(*) = max(a)
1
SELECT g2 FROM t1 WHERE g2 = 1234;
g2
1234
ALTER TABLE t1 ADD COLUMN h1 FLOAT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
name	default_value
h1	NULL
INSERT INTO t1(a, h1) VALUES(0, 1.0);
SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
count(*) = max(a)
1
SELECT h1 FROM t1 WHERE h1 = 1;
h1
1
ALTER TABLE t1 ADD COLUMN h2 FLOAT NOT NULL DEFAULT 12.34;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h2' AND has_default = 1;
name	default_value
h2	a4704541
INSERT INTO t1(a, h2) VALUES(0, 1.234);
SELECT count(*) = max(a) FROM t1 WHERE h2 = 12.34;
count(*) = max(a)
NULL
SELECT h2 FROM t1 WHERE h2 = 1.234;
h2
ALTER TABLE t1 ADD COLUMN i1 DECIMAL(5, 2), ADD COLUMN i2 double, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'i1' OR name = 'i2') AND has_default = 1;
name	default_value
i1	NULL
i2	NULL
INSERT INTO t1(a, i1, i2) VALUES(0, 10.10, 20.20);
SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE i2 IS NULL;
count(*) = max(a)
1
SELECT i1 FROM t1 WHERE i1 = 10.10;
i1
10.10
SELECT i2 FROM t1 WHERE i2 = 20.20;
i2
20.2
ALTER TABLE t1 ADD COLUMN j1 DECIMAL(5, 2) NOT NULL DEFAULT 100.00, ADD COLUMN j2 double NOT NULL DEFAULT 1000.5678;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'j1' OR name = 'j2') AND has_default = 1;
name	default_value
j1	806400
j2	5af5b9da8a448f40
INSERT INTO t1(a, j1, j2) VALUES(0, 90.90, 1000.1234);
SELECT count(*) = max(a) FROM t1 WHERE j1 = 100.00;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE j2 = 1000.5678;
count(*) = max(a)
1
SELECT j1 FROM t1 WHERE j1 = 90.90;
j1
90.90
SELECT j2 FROM t1 WHERE j2 = 1000.1234;
j2
1000.1234
ALTER TABLE t1 ADD COLUMN k1 BIT(8), ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k1' AND has_default = 1;
name	default_value
k1	NULL
INSERT INTO t1(a, k1) VALUES(0, b'010101');
SELECT count(*) = max(a) FROM t1 WHERE k1 IS NULL;
count(*) = max(a)
1
SELECT hex(k1) FROM t1 WHERE k1 = b'010101';
hex(k1)
15
ALTER TABLE t1 ADD COLUMN k2 BIT(8) NOT NULL DEFAULT b'101010';
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k2' AND has_default = 1;
name	default_value
k2	2a
INSERT INTO t1(a, k2) VALUES(0, b'110011');
SELECT count(*) = max(a) FROM t1 WHERE k2 = b'101010';
count(*) = max(a)
1
SELECT hex(k2) FROM t1 WHERE k2 = b'110011';
hex(k2)
33
ALTER TABLE t1 ADD COLUMN l1 CHAR(50), ADD COLUMN l2 VARCHAR(100), ALGORITHM=DEFAULT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'l1' OR name = 'l2') AND has_default = 1;
name	default_value
l1	NULL
l2	NULL
INSERT INTO t1(a, l1, l2) VALUES(0, 'ABCD EFGH', 'abcdefg hijklmn ');
SELECT count(*) = max(a) FROM t1 WHERE l2 IS NULL;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE l1 IS NULL;
count(*) = max(a)
1
SELECT l1 FROM t1 WHERE l1 = 'ABCD EFGH';
l1
ABCD EFGH
SELECT l2 FROM t1 WHERE l2 = 'abcdefg hijklmn ';
l2
abcdefg hijklmn 
ALTER TABLE t1 ADD COLUMN m1 CHAR(50) default 'The fox jumps over', ADD COLUMN m2 VARCHAR(50) DEFAULT 'The fox jumps over the lazy dog.';
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'm1' OR name = 'm2') AND has_default = 1;
name	default_value
m1	54686520666f78206a756d7073206f7665722020202020202020202020202020202020202020202020202020202020202020
m2	54686520666f78206a756d7073206f76657220746865206c617a7920646f672e
INSERT INTO t1(a, m1, m2) VALUES(0, 'over the lazy dog', 'The lazy dog jumps over the fox.');
SELECT count(*) = max(a) FROM t1 WHERE m1 = 'The fox jumps over';
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE m2 like 'The fox jumps%';
count(*) = max(a)
1
SELECT m1 FROM t1 WHERE m1 = 'over the lazy dog';
m1
over the lazy dog
SELECT m2 FROM t1 WHERE m2 like '%the fox.';
m2
The lazy dog jumps over the fox.
ALTER TABLE t1 ADD COLUMN n1 BINARY(10), ADD COLUMN n2 VARBINARY(10), ALGORITHM=DEFAULT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'n1' OR name = 'n2') AND has_default = 1;
name	default_value
n1	NULL
n2	NULL
INSERT INTO t1(a, n1, n2) VALUES(0, 0x010203040506070809, 0x102030405060708090);
SELECT count(*) = max(a) FROM t1 WHERE n1 IS NULL;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE n2 IS NULL;
count(*) = max(a)
1
SELECT hex(n1) FROM t1 WHERE n1 = 0x01020304050607080900;
hex(n1)
01020304050607080900
SELECT hex(n2) FROM t1 WHERE n2 = 0x102030405060708090;
hex(n2)
102030405060708090
ALTER TABLE t1 ADD COLUMN o1 BINARY(10) DEFAULT 0x11223344, ADD COLUMN o2 VARBINARY(10) DEFAULT 0x55667788;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'o1' OR name = 'o2') AND has_default = 1;
name	default_value
o1	11223344000000000000
o2	55667788
INSERT INTO t1(a, o1, o2) VALUES(0, 0x44332211, 0x88776655);
SELECT count(*) = max(a) FROM t1 WHERE o1 = 0x11223344000000000000;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE o2 = 0x55667788;
count(*) = max(a)
1
SELECT hex(o1) FROM t1 WHERE o1 = 0x44332211000000000000;
hex(o1)
44332211000000000000
SELECT hex(o2) FROM t1 WHERE o2 = 0x88776655;
hex(o2)
88776655
ALTER TABLE t1 ADD COLUMN p1 DATETIME, ALGORITHM=DEFAULT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p1' AND has_default = 1;
name	default_value
p1	NULL
INSERT INTO t1(a, p1) VALUES(0, '2017-12-31 00:00:00');
SELECT count(*) = max(a) FROM t1 WHERE p1 IS NULL;
count(*) = max(a)
1
SELECT p1 FROM t1 WHERE p1 = '2017-12-31 00:00:00';
p1
2017-12-31 00:00:00
ALTER TABLE t1 ADD COLUMN p2 DATETIME NOT NULL DEFAULT '2017-12-31 01:02:03';
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p2' AND has_default = 1;
name	default_value
p2	999e7e1083
SELECT count(*) = max(a) FROM t1 GROUP BY p2;
count(*) = max(a)
1
INSERT INTO t1(a, p2) VALUES(0, now());
SELECT count(*) FROM t1 GROUP BY p2;
count(*)
26
1
ALTER TABLE t1 ADD COLUMN q1 ENUM ('value1','value2','value3'), ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'q1' AND has_default = 1;
name	default_value
q1	NULL
INSERT INTO t1(a, q1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE q1 IS NULL;
count(*) = max(a)
1
SELECT q1 FROM t1 WHERE q1 = 1;
q1
value1
ALTER TABLE t1 ADD COLUMN r1 SET ('a','b','c'), ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'r1' AND has_default = 1;
name	default_value
r1	NULL
INSERT INTO t1(a, r1) VALUES(0, 'a');
SELECT count(*) = max(a) FROM t1 WHERE r1 IS NULL;
count(*) = max(a)
1
SELECT r1 FROM t1 WHERE r1 = 'a';
r1
a
ALTER TABLE t1 ADD COLUMN s1 BLOB, ADD COLUMN s2 TEXT, ALGORITHM=INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 's1' OR name = 's2') AND has_default = 1;
name	default_value
s1	NULL
s2	NULL
INSERT INTO t1(a, s1, s2) VALUES(0, 0x0102030405, 'abcd qwerty');
SELECT count(*) = max(a) FROM t1 WHERE s1 IS NULL;
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE s2 IS NULL;
count(*) = max(a)
1
SELECT hex(s1) FROM t1 WHERE s1 = 0x0102030405;
hex(s1)
0102030405
SELECT s2 FROM t1 WHERE s2 = 'abcd qwerty';
s2
abcd qwerty
ALTER TABLE t1 ADD COLUMN u1 BLOB NOT NULL, ADD COLUMN u2 TEXT NOT NULL;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'u1' OR name = 'u2') AND has_default = 1;
name	default_value
u1	
u2	
INSERT INTO t1(a, u1, u2) VALUES(0, 0x0102030405, 'abcd qwerty');
SELECT count(*) = max(a) FROM t1 WHERE u1 = '';
count(*) = max(a)
1
SELECT count(*) = max(a) FROM t1 WHERE u2 = '';
count(*) = max(a)
1
SELECT hex(u1) FROM t1 WHERE u1 = 0x0102030405;
hex(u1)
0102030405
SELECT u2 FROM t1 WHERE u2 = 'abcd qwerty';
u2
abcd qwerty
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) NOT NULL DEFAULT '10',
  `d1` bigint(20) DEFAULT NULL,
  `d2` bigint(20) NOT NULL DEFAULT '1234567890',
  `e1` smallint(6) DEFAULT NULL,
  `e2` smallint(6) NOT NULL DEFAULT '10',
  `f1` tinyint(4) DEFAULT NULL,
  `f2` tinyint(4) NOT NULL DEFAULT '123',
  `g1` mediumint(9) DEFAULT NULL,
  `g2` mediumint(9) NOT NULL DEFAULT '12345',
  `h1` float DEFAULT NULL,
  `h2` float NOT NULL DEFAULT '12.34',
  `i1` decimal(5,2) DEFAULT NULL,
  `i2` double DEFAULT NULL,
  `j1` decimal(5,2) NOT NULL DEFAULT '100.00',
  `j2` double NOT NULL DEFAULT '1000.5678',
  `k1` bit(8) DEFAULT NULL,
  `k2` bit(8) NOT NULL DEFAULT b'101010',
  `l1` char(50) DEFAULT NULL,
  `l2` varchar(100) DEFAULT NULL,
  `m1` char(50) DEFAULT 'The fox jumps over',
  `m2` varchar(50) DEFAULT 'The fox jumps over the lazy dog.',
  `n1` binary(10) DEFAULT NULL,
  `n2` varbinary(10) DEFAULT NULL,
  `o1` binary(10) DEFAULT '"3D\0\0\0\0\0\0',
  `o2` varbinary(10) DEFAULT 'Ufw?',
  `p1` datetime DEFAULT NULL,
  `p2` datetime NOT NULL DEFAULT '2017-12-31 01:02:03',
  `q1` enum('value1','value2','value3') DEFAULT NULL,
  `r1` set('a','b','c') DEFAULT NULL,
  `s1` blob,
  `s2` text,
  `u1` blob NOT NULL,
  `u2` text NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Scenario 2:
# Create a small table, add some columns instantly, along with
# virtual columns
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL, ADD COLUMN d INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(a, b, c) VALUES(0, 6, 20);
SELECT * FROM t1;
a	b	c	d
1	1	0	2
2	2	0	4
3	3	0	6
4	4	0	8
5	5	0	10
6	6	20	12
ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'ABCD EFGH', ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(e));
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(a, c, e) VALUES(0, 20, 'Hello'), (0, 20, 'World'), (0, 20, 'Hello World');
SELECT * FROM t1;
a	b	c	d	e	f
1	1	0	2	ABCD EFGH	9
2	2	0	4	ABCD EFGH	9
3	3	0	6	ABCD EFGH	9
4	4	0	8	ABCD EFGH	9
5	5	0	10	ABCD EFGH	9
6	6	20	12	ABCD EFGH	9
7	NULL	20	NULL	Hello	5
8	NULL	20	NULL	World	5
9	NULL	20	NULL	Hello World	11
ALTER TABLE t1 ADD COLUMN g VARCHAR(100) GENERATED ALWAYS AS (e), ADD COLUMN h BIGINT DEFAULT 10000, ADD COLUMN i BIGINT GENERATED ALWAYS AS (h * 2 + b);
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c	d	e	f	g	h	i
1	1	0	2	ABCD EFGH	9	ABCD EFGH	10000	20001
2	2	0	4	ABCD EFGH	9	ABCD EFGH	10000	20002
3	3	0	6	ABCD EFGH	9	ABCD EFGH	10000	20003
4	4	0	8	ABCD EFGH	9	ABCD EFGH	10000	20004
5	5	0	10	ABCD EFGH	9	ABCD EFGH	10000	20005
6	6	20	12	ABCD EFGH	9	ABCD EFGH	10000	20006
7	NULL	20	NULL	Hello	5	Hello	10000	NULL
8	NULL	20	NULL	World	5	World	10000	NULL
9	NULL	20	NULL	Hello World	11	Hello World	10000	NULL
INSERT INTO t1(a, b, c, h) VALUES(0, 7, 40, 2000), (0, 7, 40, 20000);
SELECT * FROM t1;
a	b	c	d	e	f	g	h	i
1	1	0	2	ABCD EFGH	9	ABCD EFGH	10000	20001
2	2	0	4	ABCD EFGH	9	ABCD EFGH	10000	20002
3	3	0	6	ABCD EFGH	9	ABCD EFGH	10000	20003
4	4	0	8	ABCD EFGH	9	ABCD EFGH	10000	20004
5	5	0	10	ABCD EFGH	9	ABCD EFGH	10000	20005
6	6	20	12	ABCD EFGH	9	ABCD EFGH	10000	20006
7	NULL	20	NULL	Hello	5	Hello	10000	NULL
8	NULL	20	NULL	World	5	World	10000	NULL
9	NULL	20	NULL	Hello World	11	Hello World	10000	NULL
10	7	40	14	ABCD EFGH	9	ABCD EFGH	2000	4007
11	7	40	14	ABCD EFGH	9	ABCD EFGH	20000	40007
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) GENERATED ALWAYS AS ((`b` * 2)) VIRTUAL,
  `e` varchar(100) DEFAULT 'ABCD EFGH',
  `f` int(11) GENERATED ALWAYS AS (length(`e`)) VIRTUAL,
  `g` varchar(100) GENERATED ALWAYS AS (`e`) VIRTUAL,
  `h` bigint(20) DEFAULT '10000',
  `i` bigint(20) GENERATED ALWAYS AS (((`h` * 2) + `b`)) VIRTUAL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Scenario 3:
# Create a small table, add some columns instantly, then change
# their default values, check original default values are correct
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
INSERT INTO t1(a, b, c, e) VALUES(0, 6, 200, 'Good day'), (0, 7, 300, 'Good DAY');
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND has_default = 1;
default_value
80000064
ALTER TABLE t1 ALTER COLUMN c SET DEFAULT 500;
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND
has_default = 1;
default_value
80000064
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
INSERT INTO t1(a, b) VALUES(0, 8), (0, 9);
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
8	8	500	1008	Hello world
9	9	500	1009	Hello world
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
default_value
48656c6c6f20776f726c64
ALTER TABLE t1 ALTER COLUMN e SET DEFAULT 'HELLO MySQL!';
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
default_value
48656c6c6f20776f726c64
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
8	8	500	1008	Hello world
9	9	500	1009	Hello world
INSERT INTO t1(a, b) VALUES(0, 10), (0, 20);
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	200	406	Good day
7	7	300	607	Good DAY
8	8	500	1008	Hello world
9	9	500	1009	Hello world
10	10	500	1010	HELLO MySQL!
11	20	500	1020	HELLO MySQL!
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '500',
  `d` int(11) GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
  `e` varchar(100) DEFAULT 'HELLO MySQL!',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Scenario 4:
# Create a small table, add some columns instantly, then do DML
# on the table
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
UPDATE t1 SET c = 200 WHERE a > 3;
SELECT distinct(c) FROM t1;
c
100
200
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
DELETE FROM t1 WHERE c = 100;
UPDATE t1 SET c = 300;
SELECT distinct(c) FROM t1;
c
300
SELECT count(*) FROM t1;
count(*)
16
ALTER TABLE t1 ADD COLUMN t DATETIME DEFAULT CURRENT_TIMESTAMP;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
UPDATE t1 SET e = 'Hello MySQL' WHERE a > 10;
UPDATE t1 SET e = 'Hello MySQL!!' WHERE a > 20;
SELECT distinct(e) FROM t1;
e
Hello world
Hello MySQL
Hello MySQL!!
UPDATE t1 SET c = 500 WHERE e LIKE '%world%';
SELECT c, e FROM t1 GROUP BY c, e;
c	e
500	Hello world
300	Hello MySQL
300	Hello MySQL!!
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
UPDATE t1 SET t = CURRENT_TIMESTAMP WHERE a < 50;
SELECT count(t) FROM t1 GROUP BY t;
count(t)
16
16
DELETE FROM t1 WHERE a < 50;
SELECT count(t) FROM t1 GROUP BY t;
count(t)
16
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '100',
  `d` int(11) GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
  `e` varchar(100) DEFAULT 'Hello world',
  `t` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Scenario 5:
# Create a small table, add some columns instantly, then do DDL
# to build indexes
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD KEY(c);
EXPLAIN SELECT c FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	c	#	NULL	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`c` AS `c` from `test`.`t1`
SELECT c FROM t1 WHERE c != 100;
c
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
UPDATE t1 SET e = 'Hello MySQL' WHERE a > 30;
ALTER TABLE t1 ADD KEY(e);
EXPLAIN SELECT e FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	e	#	NULL	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`e` AS `e` from `test`.`t1`
SELECT count(e) FROM t1 WHERE e LIKE '%MySQL%';
count(e)
17
SELECT count(e) FROM t1 WHERE e LIKE '%world%';
count(e)
23
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a, c);
SELECT a, c, e FROM t1 WHERE a > 25 AND a < 40;
a	c	e
28	100	Hello world
29	100	Hello world
30	100	Hello world
31	100	Hello MySQL
32	100	Hello MySQL
33	100	Hello MySQL
34	100	Hello MySQL
35	100	Hello MySQL
36	100	Hello MySQL
37	100	Hello MySQL
38	100	Hello MySQL
39	100	Hello MySQL
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '100',
  `d` int(11) GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
  `e` varchar(100) DEFAULT 'Hello world',
  PRIMARY KEY (`a`,`c`),
  KEY `c` (`c`),
  KEY `e` (`e`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Scenario 6:
# Create a small table, add some columns instantly, then do DML
# on the table, and some simple rollback
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
START TRANSACTION;
INSERT INTO t1(a, b) VALUES(0, 6);
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
6	6	100	206	Hello world
ROLLBACK;
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
START TRANSACTION;
UPDATE t1 SET c = 500 WHERE a = 1;
UPDATE t1 SET b = 1000 WHERE a = 2;
SELECT a, b, c FROM t1 WHERE a = 1 OR a = 2;
a	b	c
1	1	500
2	1000	100
ROLLBACK;
SELECT a, b, c FROM t1;
a	b	c
1	1	100
2	2	100
3	3	100
4	4	100
5	5	100
START TRANSACTION;
DELETE FROM t1 WHERE a < 5;
INSERT INTO t1(a, b) VALUES(0, 6);
SELECT * FROM t1;
a	b	c	d	e
5	5	100	205	Hello world
7	6	100	206	Hello world
ROLLBACK;
SELECT * FROM t1;
a	b	c	d	e
1	1	100	201	Hello world
2	2	100	202	Hello world
3	3	100	203	Hello world
4	4	100	204	Hello world
5	5	100	205	Hello world
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '100',
  `d` int(11) GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
  `e` varchar(100) DEFAULT 'Hello world',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
SELECT id, length(c1), length(c2), length(c3) FROM t1;
id	length(c1)	length(c2)	length(c3)
1	4000	4000	1
ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd';
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
id	length(c1)	length(c2)	length(c3)	length(c4)
1	4000	4000	1	500
START TRANSACTION;
UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
ROLLBACK;
SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
id	length(c1)	length(c2)	length(c3)	length(c4)
1	4000	4000	1	500
START TRANSACTION;
UPDATE t1 SET c4 = 'x' WHERE id = 1;
ROLLBACK;
SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
id	length(c1)	length(c2)	length(c3)	length(c4)
1	4000	4000	1	500
DROP TABLE t1;
#
# Scenario 7:
# Confirm some ADD COLUMN are instant, some are not
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100 AFTER b;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
Table ID differed
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
Expect 0
0
ALTER TABLE t1 ADD COLUMN e INT NOT NULL DEFAULT 100, ADD KEY(e);
Table ID differed
count(*) = 1
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
Expect 0
0
ALTER TABLE t1 ADD COLUMN f INT NOT NULL DEFAULT 100, FORCE;
Table ID differed
count(*) = 1
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
Expect 0
0
ALTER TABLE t1 ADD COLUMN g INT NOT NULL DEFAULT 100, ALGORITHM=INPLACE;
Table ID differed
count(*) = 1
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
Expect 0
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `d` int(11) NOT NULL DEFAULT '100',
  `c` int(11) NOT NULL DEFAULT '100',
  `e` int(11) NOT NULL DEFAULT '100',
  `f` int(11) NOT NULL DEFAULT '100',
  `g` int(11) NOT NULL DEFAULT '100',
  PRIMARY KEY (`a`),
  KEY `e` (`e`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c TEXT, FULLTEXT(c)) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1, 'Hello'), (0, 2, 'HELLO'), (0, 3, 'World'), (0, 4, 'Hello world'), (0, 5, 'HELLO WORLD');
ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
Table ID differed
count(*) = 1
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
#
# Scenario 8:
# Check FK constraints on instantly added columns
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
CREATE TABLE t2(a INT NOT NULL PRIMARY KEY, b INT, c INT, KEY(c));
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
INSERT INTO t2 VALUES(1, 2, 3), (2, 3, 4), (3, 4, 5);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 3;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
UPDATE t1 SET c = 4 WHERE a = 2;
UPDATE t1 SET c = 5 WHERE a = 3;
ALTER TABLE t1 ADD KEY(c);
ALTER TABLE t2 ADD FOREIGN KEY (c) REFERENCES t1(c) ON UPDATE CASCADE, ALGORITHM = COPY;
UPDATE t1 SET c = 10 WHERE a = 1;
SELECT c FROM t2;
c
4
5
10
UPDATE t1 SET c = 10;
SELECT c FROM t2;
c
10
10
10
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '3',
  PRIMARY KEY (`a`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t2;
DROP TABLE t1;
#
# Scenario 9:
# Instant ADD COLUMN on partitioned table, only simple test here
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT PARTITION BY HASH(a) PARTITIONS 3;;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c
3	3	5
6	6	5
1	1	5
4	4	5
7	7	5
2	2	5
5	5	5
8	8	5
INSERT INTO t1 VALUES(0, 9, 10), (0, 10, 20);
SELECT * FROM t1 WHERE b > 8;
a	b	c
9	9	10
10	10	20
UPDATE t1 SET c = 8 WHERE a = 1 OR a = 3 OR a = 5 OR a = 7;
SELECT * FROM t1;
a	b	c
3	3	8
6	6	5
9	9	10
1	1	8
4	4	5
7	7	8
10	10	20
2	2	5
5	5	8
8	8	5
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '5',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 3 */
DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT) ROW_FORMAT=COMPACT PARTITION BY HASH(a) PARTITIONS 2;;
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 CHANGE COLUMN c c1 INT;
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
#
# Scenario 10:
# EXCHANGE PARTITION is not allowed if either is instant
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT NOT NULL DEFAULT 5) ROW_FORMAT=COMPACT PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30));;
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
ERROR HY000: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
ALTER TABLE t2 ADD COLUMN d INT;
ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=INPLACE;
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
ERROR HY000: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
OPTIMIZE TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	optimize	note	Table does not support optimize, doing recreate + analyze instead
test.t2	optimize	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '5',
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) NOT NULL DEFAULT '5',
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
/*!50100 PARTITION BY RANGE (`a`)
(PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (30) ENGINE = InnoDB) */
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
DROP TABLE t1, t2;
#
# Scenario 11:
# PRIMARY KEY with more than one column, at least to verify it works with REDUDANT
#
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b)) ROW_FORMAT=COMPACT;;
INSERT INTO t1 VALUES(0, 1), (1, 2), (2, 3), (3, 4);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c
0	1	5
1	2	5
2	3	5
3	4	5
UPDATE t1 SET c = b WHERE b <= 2;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c
0	1	1
1	2	2
2	3	5
3	4	5
DROP TABLE t1;
#
# Scenario 12:
# Mix ALTER PARTITION and ALTER TABLE ... INPLACE. This is to check if first partition is not
# instant after ALTER PARTITION, will the metadata be copied correctly
#
CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ENGINE = InnoDB PARTITION BY RANGE(col1 * 2) ( PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256) , PARTITION p2 VALUES LESS THAN (384) , PARTITION p3 VALUES LESS THAN MAXVALUE);;
INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500), ADD COLUMN col6 TEXT;
Table id did not change
count(*) = 2
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 4` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 4
4
ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128));
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10));
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table);
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD KEY idx3(col3);
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
col1	col2	col3	col4	col5	col6
1	2	3	abcdefg	NULL	NULL
100	200	300	qwerty	NULL	NULL
200	300	400	asdfg	NULL	NULL
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT PARTITION BY HASH(a) PARTITIONS 3;;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 3
3
ALTER TABLE t1 ADD PARTITION PARTITIONS 10;
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 0
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD KEY(b);
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
Expect 0
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
a	b	c
1	1	5
2	2	5
3	3	5
4	4	5
5	5	5
6	6	5
7	7	5
8	8	5
DROP TABLE t1;
#
# Scenario 13:
# Create a table with a two level clustered index, do instant ADD COLUMN, then the non-leaf node
# should be parsed correctly
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, d INT NOT NULL, b BLOB NOT NULL, c VARCHAR(87), INDEX(d), INDEX(a ASC), PRIMARY KEY (a, d DESC, c DESC)) ROW_FORMAT=COMPACT PARTITION BY LINEAR KEY(c) PARTITIONS 9;;
INSERT INTO t1(d, b, c) VALUES(1, 2, 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(2, 3, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(3, 4, 'bbbbbbbbbbaaaaaaaaaahhhhhhhhhhccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(4, 5, 'eeeeeeeeeehhhhhhhhhhbbbbbbbbbbccccccccccddddddddddaaaaaaaaaaffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(5, 6, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbddddddddddcccccccccceeeeeeeeeeffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) VALUES(6, 7, 'cccccccccchhhhhhhhhhbbbbbbbbbbaaaaaaaaaaddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
ALTER TABLE t1 ADD COLUMN nc086 BIGINT NOT NULL FIRST, ALGORITHM=INPLACE, LOCK=DEFAULT;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 ADD COLUMN nc082 TINYTEXT;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT COUNT(*) FROM t1;
COUNT(*)
384
DROP TABLE t1;
#
# Scenario 14:
# Create a small table, and add GIS kinds of new columns and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 POINT, ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	NULL
INSERT INTO t1(a, c1) VALUES(0, ST_PointFromText('POINT(10 10)'));
SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN d1 LINESTRING, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
name	default_value
d1	NULL
INSERT INTO t1(a, d1) VALUES(0, ST_LineFromText('LINESTRING(0 0,0 10,10 0)'));
SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN e1 POLYGON, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
name	default_value
e1	NULL
INSERT INTO t1(a, e1) VALUES(0, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'));
SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN f1 MULTIPOINT, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
name	default_value
f1	NULL
INSERT INTO t1(a, f1) VALUES(0, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)'));
SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN g1 MULTILINESTRING, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
name	default_value
g1	NULL
INSERT INTO t1(a, g1) VALUES(0, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'));
SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN h1 MULTIPOLYGON, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
name	default_value
h1	NULL
INSERT INTO t1(a, h1) VALUES(0, ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'));
SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN i1 GEOMETRYCOLLECTION, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'i1' AND has_default = 1;
name	default_value
i1	NULL
INSERT INTO t1(a, i1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
count(*) = max(a)
1
ALTER TABLE t1 ADD COLUMN j1 GEOMETRY, ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'j1' AND has_default = 1;
name	default_value
j1	NULL
INSERT INTO t1(a, j1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
SELECT count(*) = max(a) FROM t1 WHERE j1 IS NULL;
count(*) = max(a)
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` point DEFAULT NULL,
  `d1` linestring DEFAULT NULL,
  `e1` polygon DEFAULT NULL,
  `f1` multipoint DEFAULT NULL,
  `g1` multilinestring DEFAULT NULL,
  `h1` multipolygon DEFAULT NULL,
  `i1` geomcollection DEFAULT NULL,
  `j1` geometry DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Scenario 15:
# Create a small table, and add JSON columns and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 JSON, ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	NULL
INSERT INTO t1(a, c1) VALUES(0, '{"key1": "value1", "key2": "value2"}');
SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
count(*) = max(a)
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` json DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Scenario 16:
# Create a small table, and add INSTANT columns and verify with trigger
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
INSERT INTO t2 VALUES(0,6);
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
DROP TRIGGER t1_ai;
ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
c1	63636363
CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW
INSERT INTO t1(a,c1) VALUES(0,'eeee');
INSERT INTO t2(a, c1) VALUES(0, 'dddd');
SELECT count(*) = max(a) FROM t2 WHERE c1='cccc';
count(*) = max(a)
1
SELECT c1 FROM t2 WHERE c1 = 'dddd';
c1
dddd
DROP TRIGGER t2_ai;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'cccc',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1,t2;
#
# Scenario 17:
# Create a small table, and add INSTANT columns and verify with storedprocedure
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
CREATE PROCEDURE p1() INSERT INTO t1(a,c1) VALUES(0, 'bbbb');
CALL p1();
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
DROP PROCEDURE p1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Scenario 18:
# Create a small table, and add INSTANT columns and verify with view
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
CREATE VIEW v1 AS SELECT * FROM t1;
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
SELECT * FROM v1;
a	b	c1
1	1	aaaa
2	2	aaaa
3	3	aaaa
4	4	aaaa
5	5	aaaa
6	NULL	bbbb
DROP VIEW v1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Scenario 19:
# Create a small table, and add INSTANT columns and drop it and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
ALTER TABLE t1 DROP COLUMN c1;
Table ID differed
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Scenario 20:
# Create a small table, and add INSTANT columns and rename table
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 RENAME t2;
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
INSERT INTO t2(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t2 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t2 WHERE c1 = 'bbbb';
c1
bbbb
CHECK TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	check	status	OK
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t2;
#
# Scenario 21:
# Create a small table, and add INSTANT columns and change its data type INSTANTly won't work
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
ALTER TABLE t1 CHANGE c1 c2 CHAR(10) NOT NULL DEFAULT 'cccc';
Table ID differed
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
0
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c2` char(10) NOT NULL DEFAULT 'cccc',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Scenario 22:
# Create a small table, and add INSTANT columns and create hash,btree multi column index and verify
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
CREATE INDEX id1 ON t1(c1) USING BTREE;
CREATE INDEX id2 ON t1(c1) USING HASH;
Warnings:
Note	3502	This storage engine does not support the HASH index algorithm, storage engine default was used instead.
Warning	1831	Duplicate index 'id2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
c1
bbbb
DROP INDEX id1 ON t1;
DROP INDEX id2 ON t1;
ALTER TABLE t1 ADD COLUMN c2 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
name	default_value
c2	63636363
CREATE INDEX id1 ON t1(c1 ASC,c2 DESC) USING BTREE;
INSERT INTO t1(a, c2) VALUES(0, 'dddd');
SELECT count(*) = max(a) FROM t1 WHERE c1='cccc';
count(*) = max(a)
NULL
SELECT c1 FROM t1 WHERE c1 = 'dddd';
c1
ALTER TABLE t1 RENAME INDEX id1 TO id2;
DROP INDEX id2 ON t1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  `c2` varchar(10) NOT NULL DEFAULT 'cccc',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Scenario 23:
# Create a small table, and add INSTANT columns and perform table join operation
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
INSERT INTO t1(a, c1) VALUES(0, 'cccc');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
count(*) = max(a)
1
SELECT c1 FROM t1 WHERE c1 = 'cccc';
c1
cccc
ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'bbbb', ALGORITHM = INSTANT;
Table id did not change
SELECT 2 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
name	default_value
c1	61616161
c1	62626262
INSERT INTO t2(a, c1) VALUES(0, 'cccc');
SELECT count(*) = max(a) FROM t2 WHERE c1='bbbb';
count(*) = max(a)
1
SELECT c1 FROM t2 WHERE c1 = 'cccc';
c1
cccc
SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;
a	b	c1	a	b	c1
6	NULL	cccc	6	NULL	cccc
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c1` varchar(10) NOT NULL DEFAULT 'bbbb',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
DROP TABLE t2;
#
# Scenario 24:
# Create a small table, and add stored and(or) virtual columns
# after last stored column in the table
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT, d INT GENERATED ALWAYS AS (b + c) VIRTUAL, e INT GENERATED ALWAYS AS (b * c) VIRTUAL) ROW_FORMAT=COMPACT;
INSERT INTO t1(a, b, c) VALUES(0, 1, 2), (0, 2, 3), (0, 3, 4), (0, 4, 5), (0, 5, 6);
ALTER TABLE t1 ADD COLUMN h INT NOT NULL AFTER c;
Table id did not change
count(*) = 1
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(a, b, c, h) VALUES(0, 6, 20, 40);
SELECT * FROM t1;
a	b	c	h	d	e
1	1	2	0	3	2
2	2	3	0	5	6
3	3	4	0	7	12
4	4	5	0	9	20
5	5	6	0	11	30
6	6	20	40	26	120
ALTER TABLE t1 ADD COLUMN i VARCHAR(100) DEFAULT 'ABCD EFGH' AFTER h, ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(i)) AFTER i;
Table id did not change
SELECT 3 = instant_cols AS `Instant columns equal` FROM information_schema.innodb_tables WHERE name like '%t1%';
Instant columns equal
1
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1(a, b, c, h, i) VALUES(0, 20, 30, 50, 'qwerty');
SELECT * FROM t1;
a	b	c	h	i	f	d	e
1	1	2	0	ABCD EFGH	9	3	2
2	2	3	0	ABCD EFGH	9	5	6
3	3	4	0	ABCD EFGH	9	7	12
4	4	5	0	ABCD EFGH	9	9	20
5	5	6	0	ABCD EFGH	9	11	30
6	6	20	40	ABCD EFGH	9	26	120
7	20	30	50	qwerty	6	50	600
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `h` int(11) NOT NULL,
  `i` varchar(100) DEFAULT 'ABCD EFGH',
  `f` int(11) GENERATED ALWAYS AS (length(`i`)) VIRTUAL,
  `d` int(11) GENERATED ALWAYS AS ((`b` + `c`)) VIRTUAL,
  `e` int(11) GENERATED ALWAYS AS ((`b` * `c`)) VIRTUAL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
DROP TABLE t1;
#
# Test cases which don't care about row format start here
#
#
# Bug #28040201	ADDING A NEW COLUMN TO A TABLE HAVING A GENRATED COLUMN
# CRASHES THE SERVER
#
CREATE TABLE t1 (a TEXT GENERATED ALWAYS AS (lpad(1, 1, 1)) VIRTUAL);
ALTER TABLE t1 ADD COLUMN (b INT);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` text GENERATED ALWAYS AS (lpad(1,1,1)) VIRTUAL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
#
# BUG#27784462 - DD::COLUMN::SE_PRIVATE_DATA[TABLE_ID] IS NOT MAINTAINED PROPERLY
#
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT NOT NULL DEFAULT 5, e INT GENERATED ALWAYS AS ((c + 2)) VIRTUAL) PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30));
ALTER TABLE t1 REORGANIZE PARTITION p1 INTO (PARTITION p1_a VALUES LESS THAN (5), PARTITION p1_b VALUES LESS THAN (10));
ALTER TABLE t1 ADD COLUMN d INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL, ALGORITHM = INSTANT;
DROP TABLE t1;
#
# Check adding a stored columns(along with virtual columns)
# after the last stored column of a table with functional index
# should be fine
#
CREATE TABLE t1 (col1 INT, col2 INT AS (col1 + col1), INDEX (col2));
INSERT INTO t1(col1) VALUES(10), (20), (30), (40);
ALTER TABLE t1 ADD COLUMN new_col INT AFTER col1, ALGORITHM=INSTANT;
SELECT * FROM t1;
col1	new_col	col2
10	NULL	20
20	NULL	40
30	NULL	60
40	NULL	80
ALTER TABLE t1 ADD COLUMN new_col_1 INT AFTER new_col, ADD COLUMN new_col_2 INT GENERATED ALWAYS AS (new_col * 3), ALGORITHM=INSTANT;
INSERT INTO t1(col1, new_col, new_col_1) VALUES(100, 200, 300);
SELECT * FROM t1;
col1	new_col	new_col_1	col2	new_col_2
10	NULL	NULL	20	NULL
20	NULL	NULL	40	NULL
30	NULL	NULL	60	NULL
40	NULL	NULL	80	NULL
100	200	300	200	600
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `col1` int(11) DEFAULT NULL,
  `new_col` int(11) DEFAULT NULL,
  `new_col_1` int(11) DEFAULT NULL,
  `col2` int(11) GENERATED ALWAYS AS ((`col1` + `col1`)) VIRTUAL,
  `new_col_2` int(11) GENERATED ALWAYS AS ((`new_col` * 3)) VIRTUAL,
  KEY `col2` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;