[MDEV-25053] ALTER TABLE .. SET DEFAULT produces invalid table structure Created: 2021-03-04  Updated: 2022-08-04

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.2, 10.3
Fix Version/s: 10.3

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: not-10.4, not-10.5, not-10.6


 Description   

Set to Minor because only 10.2 and 10.3 are affected.

CREATE TABLE t (b TEXT NOT NULL);
ALTER TABLE t ALTER b SET DEFAULT NULL;
SHOW CREATE TABLE t;

The ALTER is obviously wrong, as it attempts to set the default value to NULL while the column is not null. But it works, and the resulting table is this:

10.2 676987c4

CREATE TABLE `t` (
  `b` text NOT NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Reproducible on 10.2-10.3.
Not reproducible on 10.4-10.6.

An attempt to execute this CREATE statement on any version, including the affected ones, expectedly ends with an error:

query 'CREATE TABLE `t` (
`b` text NOT NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
' failed: 1067: Invalid default value for 'b'


Generated at Thu Feb 08 09:34:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.