--echo #
--echo # Scenario 1:
--echo # Create a small table, and add all kinds of new columns and verify
--echo #

--disable_query_log
call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Cannot add field.*");
--enable_query_log
SET @saved_innodb_strict_mode=@@SESSION.innodb_strict_mode;

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);


# [1] ADD COLUMN INT
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;

INSERT INTO t1(a, c1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
SELECT c1 FROM t1 WHERE c1 = 1;

# [2] ADD COLUMN INT DEFAULT
let $new_cols = 2;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL DEFAULT 10;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;

INSERT INTO t1(a, c2) VALUES(0, 11);
SELECT count(*) = max(a) FROM t1 WHERE c2 = 10;
SELECT c2 FROM t1 WHERE c2 = 11;


# [3] ADD COLUMN BIGINT
let $new_cols = 3;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN d1 BIGINT, ALGORITHM=INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;

INSERT INTO t1(a, d1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
SELECT d1 FROM t1 WHERE d1 = 1;

# [4] ADD COLUMN BIGINT DEFAULT
let $new_cols = 4;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN d2 BIGINT NOT NULL DEFAULT 1234567890;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd2' AND has_default = 1;

INSERT INTO t1(a, d2) VALUES(0, 1234);
SELECT count(*) = max(a) FROM t1 WHERE d2 = 1234567890;
SELECT d2 FROM t1 WHERE d2 = 1234;


# [5] ADD COLUMN SMALLINT
let $new_cols = 5;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN e1 SMALLINT, ALGORITHM=INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;

INSERT INTO t1(a, e1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
SELECT e1 FROM t1 WHERE e1 = 1;

# [6] ADD COLUMN SMALLINT DEFAULT
let $new_cols = 6;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN e2 SMALLINT NOT NULL DEFAULT 10;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e2' AND has_default = 1;

INSERT INTO t1(a, e2) VALUES(0, 11);
SELECT count(*) = max(a) FROM t1 WHERE e2 = 10;
SELECT e2 FROM t1 WHERE e2 = 11;


# [7] ADD COLUMN TINYINT
let $new_cols = 7;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN f1 TINYINT, ALGORITHM=INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;

INSERT INTO t1(a, f1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
SELECT f1 FROM t1 WHERE f1 = 1;

# [8] ADD COLUMN TINYINT DEFAULT
let $new_cols = 8;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN f2 TINYINT NOT NULL DEFAULT 123;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f2' AND has_default = 1;

INSERT INTO t1(a, f2) VALUES(0, 12);
SELECT count(*) = max(a) FROM t1 WHERE f2 = 123;
SELECT f2 FROM t1 WHERE f2 = 12;


# [9] ADD COLUMN MEDIUMINT
let $new_cols = 9;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN g1 MEDIUMINT, ALGORITHM=INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;

INSERT INTO t1(a, g1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
SELECT g1 FROM t1 WHERE g1 = 1;

# ADD COLUMN MEDIUMINT DEFAULT
let $new_cols = 10;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN g2 MEDIUMINT NOT NULL DEFAULT 12345;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g2' AND has_default = 1;

INSERT INTO t1(a, g2) VALUES(0, 1234);
SELECT count(*) = max(a) FROM t1 WHERE g2 = 12345;
SELECT g2 FROM t1 WHERE g2 = 1234;


# ADD COLUMN FLOAT
let $new_cols = 11;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN h1 FLOAT, ALGORITHM=INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;

INSERT INTO t1(a, h1) VALUES(0, 1.0);
SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
SELECT h1 FROM t1 WHERE h1 = 1;

# ADD COLUMN FLOAT DEFAULT
let $new_cols = 12;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN h2 FLOAT NOT NULL DEFAULT 12.34;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h2' AND has_default = 1;

INSERT INTO t1(a, h2) VALUES(0, 1.234);
SELECT count(*) = max(a) FROM t1 WHERE h2 = 12.34;
SELECT h2 FROM t1 WHERE h2 = 1.234;


# ADD COLUMN DECIMAL & DOUBLE
let $new_cols = 14;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN i1 DECIMAL(5, 2), ADD COLUMN i2 double, ALGORITHM=INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'i1' OR name = 'i2') AND has_default = 1;

INSERT INTO t1(a, i1, i2) VALUES(0, 10.10, 20.20);
SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
SELECT count(*) = max(a) FROM t1 WHERE i2 IS NULL;
SELECT i1 FROM t1 WHERE i1 = 10.10;
SELECT i2 FROM t1 WHERE i2 = 20.20;

# ADD COLUMN DECIMAL & DOUBLE DEFAULT
let $new_cols = 16;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN j1 DECIMAL(5, 2) NOT NULL DEFAULT 100.00, ADD COLUMN j2 double NOT NULL DEFAULT 1000.5678;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'j1' OR name = 'j2') AND has_default = 1;

INSERT INTO t1(a, j1, j2) VALUES(0, 90.90, 1000.1234);
SELECT count(*) = max(a) FROM t1 WHERE j1 = 100.00;
SELECT count(*) = max(a) FROM t1 WHERE j2 = 1000.5678;
SELECT j1 FROM t1 WHERE j1 = 90.90;
SELECT j2 FROM t1 WHERE j2 = 1000.1234;



# ADD COLUMN BIT
let $new_cols = 17;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN k1 BIT(8), ALGORITHM=INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k1' AND has_default = 1;

INSERT INTO t1(a, k1) VALUES(0, b'010101');
SELECT count(*) = max(a) FROM t1 WHERE k1 IS NULL;
SELECT hex(k1) FROM t1 WHERE k1 = b'010101';

# ADD COLUMN BIT DEFAULT
let $new_cols = 18;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN k2 BIT(8) NOT NULL DEFAULT b'101010';

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k2' AND has_default = 1;

INSERT INTO t1(a, k2) VALUES(0, b'110011');
SELECT count(*) = max(a) FROM t1 WHERE k2 = b'101010';
SELECT hex(k2) FROM t1 WHERE k2 = b'110011';



# ADD COLUMN CHAR & VARCHAR
let $new_cols = 20;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN l1 CHAR(50), ADD COLUMN l2 VARCHAR(100), ALGORITHM=DEFAULT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'l1' OR name = 'l2') AND has_default = 1;

INSERT INTO t1(a, l1, l2) VALUES(0, 'ABCD EFGH', 'abcdefg hijklmn ');
SELECT count(*) = max(a) FROM t1 WHERE l2 IS NULL;
SELECT count(*) = max(a) FROM t1 WHERE l1 IS NULL;
SELECT l1 FROM t1 WHERE l1 = 'ABCD EFGH';
SELECT l2 FROM t1 WHERE l2 = 'abcdefg hijklmn ';


# ADD COLUMN CHAR & VARCHAR DEFAULT
let $new_cols = 22;
let $instant_add_column = 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.';

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'm1' OR name = 'm2') AND has_default = 1;

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';
SELECT count(*) = max(a) FROM t1 WHERE m2 like 'The fox jumps%';
SELECT m1 FROM t1 WHERE m1 = 'over the lazy dog';
SELECT m2 FROM t1 WHERE m2 like '%the fox.';



# ADD COLUMN BINARY & VARBINARY
let $new_cols = 24;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN n1 BINARY(10), ADD COLUMN n2 VARBINARY(10), ALGORITHM=DEFAULT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'n1' OR name = 'n2') AND has_default = 1;

INSERT INTO t1(a, n1, n2) VALUES(0, 0x010203040506070809, 0x102030405060708090);
SELECT count(*) = max(a) FROM t1 WHERE n1 IS NULL;
SELECT count(*) = max(a) FROM t1 WHERE n2 IS NULL;
SELECT hex(n1) FROM t1 WHERE n1 = 0x01020304050607080900;
SELECT hex(n2) FROM t1 WHERE n2 = 0x102030405060708090;



# ADD COLUMN BINARY & VARBINARY DEFAULT
let $new_cols = 26;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN o1 BINARY(10) DEFAULT 0x11223344, ADD COLUMN o2 VARBINARY(10) DEFAULT 0x55667788;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'o1' OR name = 'o2') AND has_default = 1;

INSERT INTO t1(a, o1, o2) VALUES(0, 0x44332211, 0x88776655);
SELECT count(*) = max(a) FROM t1 WHERE o1 = 0x11223344000000000000;
SELECT count(*) = max(a) FROM t1 WHERE o2 = 0x55667788;
SELECT hex(o1) FROM t1 WHERE o1 = 0x44332211000000000000;
SELECT hex(o2) FROM t1 WHERE o2 = 0x88776655;



# ADD COLUMN DATETIME
let $new_cols = 27;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN p1 DATETIME, ALGORITHM=DEFAULT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p1' AND has_default = 1;

INSERT INTO t1(a, p1) VALUES(0, '2017-12-31 00:00:00');
SELECT count(*) = max(a) FROM t1 WHERE p1 IS NULL;
SELECT p1 FROM t1 WHERE p1 = '2017-12-31 00:00:00';

# ADD COLUMN DATETIME DEFAULT
let $new_cols = 28;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN p2 DATETIME NOT NULL DEFAULT '2017-12-31 01:02:03';

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p2' AND has_default = 1;

sleep 1;
SELECT count(*) = max(a) FROM t1 GROUP BY p2;
INSERT INTO t1(a, p2) VALUES(0, now());
SELECT count(*) FROM t1 GROUP BY p2;



# ADD COLUMN ENUM
let $new_cols = 29;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN q1 ENUM ('value1','value2','value3'), ALGORITHM=INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'q1' AND has_default = 1;

INSERT INTO t1(a, q1) VALUES(0, 1);
SELECT count(*) = max(a) FROM t1 WHERE q1 IS NULL;
SELECT q1 FROM t1 WHERE q1 = 1;


# ADD COLUMN SET
let $new_cols = 30;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN r1 SET ('a','b','c'), ALGORITHM=INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'r1' AND has_default = 1;

INSERT INTO t1(a, r1) VALUES(0, 'a');
SELECT count(*) = max(a) FROM t1 WHERE r1 IS NULL;
SELECT r1 FROM t1 WHERE r1 = 'a';


# ADD COLUMN BLOB & TEXT
let $new_cols = 32;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN s1 BLOB, ADD COLUMN s2 TEXT, ALGORITHM=INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 's1' OR name = 's2') AND has_default = 1;

INSERT INTO t1(a, s1, s2) VALUES(0, 0x0102030405, 'abcd qwerty');
SELECT count(*) = max(a) FROM t1 WHERE s1 IS NULL;
SELECT count(*) = max(a) FROM t1 WHERE s2 IS NULL;
SELECT hex(s1) FROM t1 WHERE s1 = 0x0102030405;
SELECT s2 FROM t1 WHERE s2 = 'abcd qwerty';

# ADD COLUMN BLOB & TEXT NOT NULL
let $new_cols = 34;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN u1 BLOB NOT NULL, ADD COLUMN u2 TEXT NOT NULL;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'u1' OR name = 'u2') AND has_default = 1;

INSERT INTO t1(a, u1, u2) VALUES(0, 0x0102030405, 'abcd qwerty');
SELECT count(*) = max(a) FROM t1 WHERE u1 = '';
SELECT count(*) = max(a) FROM t1 WHERE u2 = '';
SELECT hex(u1) FROM t1 WHERE u1 = 0x0102030405;
SELECT u2 FROM t1 WHERE u2 = 'abcd qwerty';


CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;



--echo #
--echo # Scenario 2:
--echo # Create a small table, add some columns instantly, along with
--echo # virtual columns
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);


let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL, ADD COLUMN d INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

INSERT INTO t1(a, b, c) VALUES(0, 6, 20);
--sorted_result
SELECT * FROM t1;


let $new_cols = 2;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'ABCD EFGH', ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(e));

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

INSERT INTO t1(a, c, e) VALUES(0, 20, 'Hello'), (0, 20, 'World'), (0, 20, 'Hello World');
--sorted_result
SELECT * FROM t1;


let $new_cols = 3;
let $instant_add_column = 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);

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

--sorted_result
SELECT * FROM t1;
INSERT INTO t1(a, b, c, h) VALUES(0, 7, 40, 2000), (0, 7, 40, 20000);
--sorted_result
SELECT * FROM t1;


CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;



--echo #
--echo # Scenario 3:
--echo # Create a small table, add some columns instantly, then change
--echo # their default values, check original default values are correct
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);


let $new_cols = 2;
let $instant_add_column = 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';

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

--sorted_result
SELECT * FROM t1;
INSERT INTO t1(a, b, c, e) VALUES(0, 6, 200, 'Good day'), (0, 7, 300, 'Good DAY');
--sorted_result
SELECT * FROM t1;

SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND has_default = 1;
ALTER TABLE t1 ALTER COLUMN c SET DEFAULT 500;
# This should not change
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND
 has_default = 1;

--sorted_result
SELECT * FROM t1;
INSERT INTO t1(a, b) VALUES(0, 8), (0, 9);
--sorted_result
SELECT * FROM t1;

SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
ALTER TABLE t1 ALTER COLUMN e SET DEFAULT 'HELLO MySQL!';
# This should not change
SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;

--sorted_result
SELECT * FROM t1;
INSERT INTO t1(a, b) VALUES(0, 10), (0, 20);
--sorted_result
SELECT * FROM t1;


CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;



--echo #
--echo # Scenario 4:
--echo # Create a small table, add some columns instantly, then do DML
--echo # on the table
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);

let $new_cols = 2;
let $instant_add_column = 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';

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

UPDATE t1 SET c = 200 WHERE a > 3;
--sorted_result
SELECT distinct(c) 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;
INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;

DELETE FROM t1 WHERE c = 100;

UPDATE t1 SET c = 300;
--sorted_result
SELECT distinct(c) FROM t1;

SELECT count(*) FROM t1;


let $new_cols = 3;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN t DATETIME DEFAULT CURRENT_TIMESTAMP;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

UPDATE t1 SET e = 'Hello MySQL' WHERE a > 10;
UPDATE t1 SET e = 'Hello MySQL!!' WHERE a > 20;
--sorted_result
SELECT distinct(e) FROM t1;

UPDATE t1 SET c = 500 WHERE e LIKE '%world%';
--sorted_result
SELECT c, e FROM t1 GROUP BY c, e;

INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
sleep 1;
UPDATE t1 SET t = CURRENT_TIMESTAMP WHERE a < 50;
SELECT count(t) FROM t1 GROUP BY t;
DELETE FROM t1 WHERE a < 50;
SELECT count(t) FROM t1 GROUP BY t;

CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;



--echo #
--echo # Scenario 5:
--echo # Create a small table, add some columns instantly, then do DDL
--echo # to build indexes
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);

let $new_cols = 2;
let $instant_add_column = 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';

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

ALTER TABLE t1 ADD KEY(c);

# Replace the numbers in the output with '#' to stablize the result, after all we only care about the index picked.
--replace_column 8 # 10 #
EXPLAIN SELECT c FROM t1;

SELECT c FROM t1 WHERE c != 100;

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);

# Replace the numbers in the output with '#' to stablize the result, after all we only care about the index picked.
--replace_column 8 # 10 #
EXPLAIN SELECT e FROM t1;
SELECT count(e) FROM t1 WHERE e LIKE '%MySQL%';
SELECT count(e) FROM t1 WHERE e LIKE '%world%';

ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a, c);

SELECT a, c, e FROM t1 WHERE a > 25 AND a < 40;

CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;



--echo #
--echo # Scenario 6:
--echo # Create a small table, add some columns instantly, then do DML
--echo # on the table, and some simple rollback
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);

let $new_cols = 2;
let $instant_add_column = 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';

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

START TRANSACTION;

INSERT INTO t1(a, b) VALUES(0, 6);

--sorted_result
SELECT * FROM t1;

ROLLBACK;

--sorted_result
SELECT * FROM t1;


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;

ROLLBACK;

--sorted_result
SELECT a, b, c FROM t1;


START TRANSACTION;

DELETE FROM t1 WHERE a < 5;

INSERT INTO t1(a, b) VALUES(0, 6);

--sorted_result
SELECT * FROM t1;

ROLLBACK;

--sorted_result
SELECT * FROM t1;


CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;


# One interesting pessimistic update rollback

SET SESSION innodb_strict_mode=OFF;
--disable_warnings
--eval CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=$row_format
--enable_warnings
SET SESSION innodb_strict_mode=@saved_innodb_strict_mode;

INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));

SELECT id, length(c1), length(c2), length(c3) FROM t1;

let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd';

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;

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;

START TRANSACTION;

UPDATE t1 SET c4 = 'x' WHERE id = 1;

ROLLBACK;

SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;

DROP TABLE t1;



--echo #
--echo # Scenario 7:
--echo # Confirm some ADD COLUMN are instant, some are not
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);

# ADD COLUMN after LAST should be INSTANT
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100 AFTER b;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

# ADD COLUMN in the middle should be instant
let $new_cols = 2;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT count(*) AS `Expect 2` FROM information_schema.innodb_columns WHERE has_default = 1;

# ADD COLUMN with ADD KEY should not be instant
let $new_cols = 0;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN e INT NOT NULL DEFAULT 100, ADD KEY(e);

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;

# FORCE key word should not be instant
let $new_cols = 0;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN f INT NOT NULL DEFAULT 100, FORCE;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;

# ALGORITHM=INPLACE should not be instant, verify again here
let $new_cols = 0;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN g INT NOT NULL DEFAULT 100, ALGORITHM=INPLACE;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;


CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;


--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c TEXT, FULLTEXT(c)) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1, 'Hello'), (0, 2, 'HELLO'), (0, 3, 'World'), (0, 4, 'Hello world'), (0, 5, 'HELLO WORLD');

# ADD COLUMN to FULLTEXT table should not be instant
let $new_cols = 0;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

DROP TABLE t1;


--echo #
--echo # Scenario 8:
--echo # Check FK constraints on instantly added columns
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

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);

let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 3;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

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;
--sorted_result
SELECT c FROM t2;

UPDATE t1 SET c = 10;
--sorted_result
SELECT c FROM t2;

CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t2;
DROP TABLE t1;



--echo #
--echo # Scenario 9:
--echo # Instant ADD COLUMN on partitioned table, only simple test here
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format 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);

let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

--sorted_result
SELECT * FROM t1;

INSERT INTO t1 VALUES(0, 9, 10), (0, 10, 20);

--sorted_result
SELECT * FROM t1 WHERE b > 8;

UPDATE t1 SET c = 8 WHERE a = 1 OR a = 3 OR a = 5 OR a = 7;

--sorted_result
SELECT * FROM t1;

CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;


# Check instant ADD COLUMN and then common ALTER TABLE and then instant ADD COLUMN

--eval CREATE TABLE t1 (a INT, b INT) ROW_FORMAT=$row_format PARTITION BY HASH(a) PARTITIONS 2;

let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

ALTER TABLE t1 CHANGE COLUMN c c1 INT;

let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

DROP TABLE t1;


--echo #
--echo # Scenario 10:
--echo # EXCHANGE PARTITION is not allowed if either is instant
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format;

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);

let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

--eval CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT NOT NULL DEFAULT 5) ROW_FORMAT=$row_format PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30));

# t1 is instant table, so should fail
--error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;

# Make t2 an instant table
ALTER TABLE t2 ADD COLUMN d INT;

# Make t1 a non-instant table
ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=INPLACE;

# t2 is instant table, so should fail
--error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;

OPTIMIZE TABLE t2;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
# Now this should succeed
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;

DROP TABLE t1, t2;


--echo #
--echo # Scenario 11:
--echo # PRIMARY KEY with more than one column, at least to verify it works with REDUNDANT
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b)) ROW_FORMAT=$row_format;

INSERT INTO t1 VALUES(0, 1), (1, 2), (2, 3), (3, 4);

let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

--sorted_result
SELECT * FROM t1;
UPDATE t1 SET c = b WHERE b <= 2;

CHECK TABLE t1;
--sorted_result
SELECT * FROM t1;

DROP TABLE t1;


--echo #
--echo # Scenario 12:
--echo # Mix ALTER PARTITION and ALTER TABLE ... INPLACE. This is to check if first partition is not
--echo # instant after ALTER PARTITION, will the metadata be copied correctly
--echo #

--eval 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');

let $new_cols = 2;
let $instant_add_column = ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500), ADD COLUMN col6 TEXT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;

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 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
CHECK TABLE t1;

ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10));
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
CHECK TABLE t1;

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 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
CHECK TABLE t1;

ALTER TABLE t1 ADD KEY idx3(col3);
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
CHECK TABLE t1;

--sorted_result
SELECT * FROM t1;

DROP TABLE t1;


--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format 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);

let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;

ALTER TABLE t1 ADD PARTITION PARTITIONS 10;
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
CHECK TABLE t1;

ALTER TABLE t1 ADD KEY(b);
SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
CHECK TABLE t1;

--sorted_result
SELECT * FROM t1;

DROP TABLE t1;



--echo #
--echo # Scenario 13:
--echo # Create a table with a two level clustered index, do instant ADD COLUMN, then the non-leaf node
--echo # should be parsed correctly
--echo #

--eval 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=$row_format 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;

ALTER TABLE t1 ADD COLUMN nc082 TINYTEXT;

CHECK TABLE t1;

SELECT COUNT(*) FROM t1;

DROP TABLE t1;


--echo #
--echo # Scenario 14:
--echo # Create a small table, and add GIS kinds of new columns and verify
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);

# ADD COLUMN POINT
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 POINT, ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;

INSERT INTO t1(a, c1) VALUES(0, ST_PointFromText('POINT(10 10)'));
SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;

# ADD COLUMN LINESTRING
let $new_cols = 2;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN d1 LINESTRING, ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;

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;

# ADD COLUMN POLYGON
let $new_cols = 3;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN e1 POLYGON, ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;

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;

# ADD COLUMN MULTIPOINT
let $new_cols = 4;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN f1 MULTIPOINT, ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;

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;

# ADD COLUMN MULTILINESTRING
let $new_cols = 5;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN g1 MULTILINESTRING, ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;

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;

# ADD COLUMN MULTIPOLYGON
let $new_cols = 6;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN h1 MULTIPOLYGON, ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;

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;


# ADD COLUMN GEOMETRYCOLLECTION
let $new_cols = 7;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN i1 GEOMETRYCOLLECTION, ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'i1' AND has_default = 1;

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;


# ADD COLUMN GEOMETRY
let $new_cols = 8;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN j1 GEOMETRY, ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'j1' AND has_default = 1;

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;

CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;

--echo #
--echo # Scenario 15:
--echo # Create a small table, and add JSON columns and verify
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);

# ADD COLUMN JSON
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 JSON, ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;

INSERT INTO t1(a, c1) VALUES(0, '{"key1": "value1", "key2": "value2"}');
SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;

CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;


--echo #
--echo # Scenario 16:
--echo # Create a small table, and add INSTANT columns and verify with trigger
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
--eval CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

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);

# ADD COLUMN
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;

# TRIGGERS AFTER INSERT INTO ADDED COLUMN
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';
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
DROP TRIGGER t1_ai;


# ADD COLUMN
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;

# INSERT ROW INTO ADDED COLUMN USING TRIGGER
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';
SELECT c1 FROM t2 WHERE c1 = 'dddd';
DROP TRIGGER t2_ai;


CHECK TABLE t1;
CHECK TABLE t2;

SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;

DROP TABLE t1,t2;


--echo #
--echo # Scenario 17:
--echo # Create a small table, and add INSTANT columns and verify with storedprocedure
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);

# ADD COLUMN
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;

# STORED PROCEDURE
CREATE PROCEDURE p1() INSERT INTO t1(a,c1) VALUES(0, 'bbbb');
CALL p1();

SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
DROP PROCEDURE p1;

CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;


--echo #
--echo # Scenario 18:
--echo # Create a small table, and add INSTANT columns and verify with view
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);

# ADD COLUMN
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;

# CREATE VIEW
CREATE VIEW v1 AS SELECT * FROM t1;

INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
SELECT c1 FROM t1 WHERE c1 = 'bbbb';
--sorted_result
SELECT * FROM v1;
DROP VIEW v1;

CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;


--echo #
--echo # Scenario 19:
--echo # Create a small table, and add INSTANT columns and drop it and verify
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);

# ADD COLUMN
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;

INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
SELECT c1 FROM t1 WHERE c1 = 'bbbb';

# DROP COLUMN
let $new_cols = 0;
let $instant_add_column = ALTER TABLE t1 DROP COLUMN c1;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;


--echo #
--echo # Scenario 20:
--echo # Create a small table, and add INSTANT columns and rename table
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);

# ADD COLUMN
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

ALTER TABLE t1 RENAME t2;

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
INSERT INTO t2(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t2 WHERE c1='aaaa';
SELECT c1 FROM t2 WHERE c1 = 'bbbb';

CHECK TABLE t2;

SHOW CREATE TABLE t2;

DROP TABLE t2;


--echo #
--echo # Scenario 21:
--echo # Create a small table, and add INSTANT columns and change its data type INSTANTly won't work
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);

# ADD COLUMN
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
SELECT c1 FROM t1 WHERE c1 = 'bbbb';

# change added column datatype should not be instant
let $new_cols = 0;
let $instant_add_column = ALTER TABLE t1 CHANGE c1 c2 CHAR(10) NOT NULL DEFAULT 'cccc';

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;


--echo #
--echo # Scenario 22:
--echo # Create a small table, and add INSTANT columns and create hash,btree multi column index and verify
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);

# ADD COLUMN
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;

# CREATE INDEX
CREATE INDEX id1 ON t1(c1) USING BTREE;
CREATE INDEX id2 ON t1(c1) USING HASH;

INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
SELECT c1 FROM t1 WHERE c1 = 'bbbb';

DROP INDEX id1 ON t1;
DROP INDEX id2 ON t1;

# ADD ANOTHER COLUMN
let $new_cols = 2;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c2 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;

# CREATE INDEX
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';
SELECT c1 FROM t1 WHERE c1 = 'dddd';

# RENAME INDEX
ALTER TABLE t1 RENAME INDEX id1 TO id2;

DROP INDEX id2 ON t1;

CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;


--echo #
--echo # Scenario 23:
--echo # Create a small table, and add INSTANT columns and perform table join operation
--echo #

--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
--eval CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format

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);

# ADD COLUMN TO TABLE t1
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;

INSERT INTO t1(a, c1) VALUES(0, 'cccc');
SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
SELECT c1 FROM t1 WHERE c1 = 'cccc';

# ADD COLUMN TO TABLE t2
let $new_cols = 1;
let $instant_add_column = ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'bbbb', ALGORITHM = INSTANT;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;

INSERT INTO t2(a, c1) VALUES(0, 'cccc');
SELECT count(*) = max(a) FROM t2 WHERE c1='bbbb';
SELECT c1 FROM t2 WHERE c1 = 'cccc';

--sorted_result
SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;

CHECK TABLE t1;
CHECK TABLE t2;

SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;

DROP TABLE t1;
DROP TABLE t2;


--echo #
--echo # Scenario 24:
--echo # Create a small table, and add stored and(or) virtual columns
--echo # after last stored column in the table
--echo #

--eval 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=$row_format

INSERT INTO t1(a, b, c) VALUES(0, 1, 2), (0, 2, 3), (0, 3, 4), (0, 4, 5), (0, 5, 6);


let $new_cols = 1;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN h INT NOT NULL AFTER c;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

INSERT INTO t1(a, b, c, h) VALUES(0, 6, 20, 40);
--sorted_result
SELECT * FROM t1;


let $new_cols = 2;
let $instant_add_column = ALTER TABLE t1 ADD COLUMN i VARCHAR(100) DEFAULT 'ABCD EFGH' AFTER h, ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(i)) AFTER i;

--source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc

INSERT INTO t1(a, b, c, h, i) VALUES(0, 20, 30, 50, 'qwerty');
--sorted_result
SELECT * FROM t1;

CHECK TABLE t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;