[MDEV-32547] ALTER IGNORE TABLE only sometimes converts NULL to DEFAULT value Created: 2023-10-23  Updated: 2023-10-23

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Marko Mäkelä Assignee: Michael Widenius
Resolution: Unresolved Votes: 0
Labels: CONSTRAINT

Issue Links:
Relates
relates to MDEV-32523 ASAN errors or assertion failure in r... Stalled

 Description   

While reviewing MDEV-32523, I noticed the following anomaly:

--source include/have_innodb.inc
 
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT DEFAULT 1) ENGINE=INNODB;
 
INSERT INTO t1 VALUES(1, NULL);
 
ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2;
 
SELECT * FROM t1;
 
DROP TABLE t1;

When the test is run with

./mtr --mysqld=--alter-algorithm=copy

the SELECT will report the value of the column c2 as 0, not the previous or current DEFAULT value.

When the test is run with

./mtr --mysqld=--alter-algorithm=inplace

it will (before a fix of MDEV-32523) report c2=1, corresponding to the old DEFAULT value of the column.

What is the expected outcome here?

Another anomaly:

--source include/have_innodb.inc
 
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT DEFAULT 1, CHECK (c2>3))
 
ENGINE=INNODB;
 
INSERT INTO t1 VALUES(1, NULL);
 
ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2;
 
SELECT * FROM t1;
 
DROP TABLE t1;

This will end up with an empty table when using ALGORITHM=COPY, so the IGNORE is not ignoring CHECK constraints. If I use ALGORITHM=INPLACE, the CHECK constraint will wrongly be ignored.
Generated at Thu Feb 08 10:32:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.