Bug #96864 Addition of columns with generated defaults is broken in ALTER TABLE COPY
Submitted: 13 Sep 10:53 Modified: 9 Oct 14:38
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.19-git OS:Any
Assigned to: CPU Architecture:Any

[13 Sep 10:53] Dmitry Lenev
Description:
Columns with defaults using expression are not handled 
correctly in ALTER TABLE using COPY algorithm in non-trivial
cases:

The order of evaluation of default expression and generated columns is wrong.
See How-to-report section 1.

The code which detects if default expression needs evaluation is wrong
as it assumes that column with default expression is not new if there
is ANY column in the same position in old version of the table.
See How-to-report section 2.

How to repeat:
# Section 1:
CREATE TABLE t1 (a INT PRIMARY KEY,
                 b INT DEFAULT (a),
                 c INT GENERATED ALWAYS AS (-b) STORED);
INSERT INTO t1 (a) VALUES (1), (2);
SELECT * FROM t1;
# Returns correct results:
#	a      b       c
#	1      1       -1
#	2      2       -2

CREATE TABLE t2 (a INT PRIMARY KEY);
INSERT INTO t2 VALUES (1), (2);
ALTER TABLE t2 ADD COLUMN b INT DEFAULT (a), ADD COLUMN c INT GENERATED ALWAYS AS (-b) STORED;
SELECT * FROM t2;
# Notice the difference in 'c', which should not be there!
#	a      b       c
#	1      1       0
#	2      2       -1
DROP TABLES t1, t2;

# Section 2:
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1, 1), (2, 2);
ALTER TABLE t1 DROP COLUMN b, ADD COLUMN c INT DEFAULT (a);
SELECT * FROM t1;
# Why NULLs ?
#	a      c
#	1      NULL
#	2      NULL
DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT DEFAULT (1+1));
INSERT INTO t1 VALUES (1, 0), (2, 0);
ALTER TABLE t1 ADD COLUMN c INT AFTER a, ALGORITHM=COPY;
SELECT * FROM t1;
# Why b doesn't preserve values?
#	a      c       b
#	1      NULL    2
#	2      NULL    2
DROP TABLE t1;
[9 Oct 14:38] Paul Dubois
Posted by developer:
 
Fixed in 8.0.19.

Some ALTER TABLE operations using the COPY algorithm did not handle
columns with expression default values properly.