[MDEV-29001] ALTER COLUMN DROP DEFAULT makes SHOW CREATE TABLE diverge from the actual structure Created: 2022-07-01  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Aleksey Midenkov
Resolution: Unresolved Votes: 0
Labels: upstream


 Description   

MariaDB KB doesn't describe the exact semantics of DROP DEFAULT (or I couldn't find it). But at least according to MySQL manual,

If the old default is removed and the column can be NULL, the new default is NULL.

This is not what in fact happens (neither in MariaDB nor in MySQL).

CREATE TABLE t1 (a INT);
SHOW CREATE TABLE t1;
ALTER TABLE t1 ALTER a DROP DEFAULT;
SHOW CREATE TABLE t1;
 
INSERT INTO t1 () VALUES ();
 
DROP TABLE t1;

10.3 e34f8781

CREATE TABLE t1 (a INT);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
ALTER TABLE t1 ALTER a DROP DEFAULT;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 () VALUES ();
 
mysqltest: At line 6: query 'INSERT INTO t1 () VALUES ()' failed: 1364: Field 'a' doesn't have a default value

So, the column loses NULL as a default value and doesn't get a new one.
What seems worse, SHOW CREATE TABLE returns SQL which, if executed, will create a table with a default NULL value.

Since there is no way to indicate in SHOW CREATE TABLE that a null-able column should not have a default value, I suppose the behavior should be adjusted to what MySQL manual describes.

MySQL (5.6/5.7/8.0) behaves the same way. There is an open (accepted) bug report about it in MySQL bug system.



 Comments   
Comment by Elena Stepanova [ 2022-12-06 ]

The obvious consequence is that mysqldump-based provisioning involving such such tables is broken.

Generated at Thu Feb 08 10:05:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.