[#MDEV-30965] Odd behavior with default timestamps and check constraints creates invalid table

I need some help categorizing this issue, as I'm not sure where the "legacy design" ends and the unexpected behavior starts. There can also be more than one problem here.

The results are for 11.0 with

select @@explicit_defaults_for_timestamp;

@@explicit_defaults_for_timestamp

although it behaves the same way with explicit_defaults_for_timestamp=OFF.

In the test case below, the table t1 is first created with a current_timestamp as a default for a non-null datetime column. It gets created correctly, as expected.

11.0 a79abb65
CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

Then ALTER TABLE attempts to drop the default. ALTER ends without warnings or errors, but the resulting table still has a default for the column:

ALTER TABLE t1 ALTER COLUMN a DROP DEFAULT;

show create table t1;

Table	Create Table

t1	CREATE TABLE `t1` (

  `a` datetime NOT NULL DEFAULT current_timestamp()

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

I would say that in case of explicit_defaults_for_timestamp=ON it is already a problem, but given MDEV-28983, I'm not completely sure.

The next ALTER adds a column with a CHECK constraint referring to this existing column, and puts the new column before the existing one. It gets added successfully.

ALTER TABLE t1 ADD b DATE CHECK (b > a) FIRST;

show create table t1;

Table	Create Table

t1	CREATE TABLE `t1` (

  `b` date DEFAULT NULL CHECK (`b` > `a`),

  `a` datetime NOT NULL DEFAULT current_timestamp()

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

But the resulting structure is invalid. If we try to create such a table directly, it fails:

query 'CREATE TABLE `t1` (

`b` date DEFAULT NULL CHECK (`b` > `a`),

`a` datetime NOT NULL DEFAULT current_timestamp()

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

' failed: ER_EXPRESSION_REFERS_TO_UNINIT_FIELD (4029): Expression for field `b` is referring to uninitialized field `a`

So I'd say it's another problem, either ALTER should not succeed if it creates an invalid structure, or this structure should be valid. But I can't figure out why it is important that the table has a fake current_timestamp, and why it doesn't fail when the table simply does not have a default from the start.

The resulting incorrect structure of course makes any further mysqldump output invalid or, in the test case here, causes a replication failure.

2023-03-29 23:16:29 8 [ERROR] Slave SQL: Error 'Expression for field `b` is referring to uninitialized field `a`' on query. Default database: 'test'. Query: 'CREATE TABLE `t2` (

  `b` date DEFAULT NULL CHECK (`b` > `a`),

  `a` datetime NOT NULL DEFAULT current_timestamp()

)', Gtid 0-1-4, Internal MariaDB error code: 4029

2023-03-29 23:16:29 8 [Warning] Slave: Expression for field `b` is referring to uninitialized field `a` Error_code: 4029

But I don't believe that replication is to blame here.

Test case
--source include/have_binlog_format_row.inc
--source include/master-slave.inc

CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
ALTER TABLE t1 ALTER COLUMN a DROP DEFAULT;
ALTER TABLE t1 ADD b DATE CHECK (b > a) FIRST;

CREATE TABLE t2 SELECT * FROM t1;

--sync_slave_with_master

# Cleanup
--connection master
DROP TABLE t1, t2;
--source include/rpl_end.inc

[MDEV-30965] Odd behavior with default timestamps and check constraints creates invalid table Created: 2023-03-29 Updated: 2023-11-28
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Data Definition - Alter Table, Temporal Types
Affects Version/s:	10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s:	10.4, 10.5, 10.6, 10.11, 11.0

[MDEV-30965] Odd behavior with default timestamps and check constraints creates invalid table Created: 2023-03-29 Updated: 2023-11-28