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