Bug #81010 ALTER TABLE DROP DEFAULT - default value of NULL not forced
Submitted: 8 Apr 2016 10:34 Modified: 11 Apr 2016 13:18
Reporter: Power Gamer Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7.11, 5.6.29, 8.0.17 OS:Windows
Assigned to: CPU Architecture:Any
Tags: DROP DEFAULT, null

[8 Apr 2016 10:34] Power Gamer
Description:
###### Quote 1. ######
http://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html

If a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows: 

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. 

###### Quote 2. ######
http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

ALTER ... SET DEFAULT or ALTER ... DROP DEFAULT specify a new default value for a column or remove the old default value, respectively. If the old default is removed and the column can be NULL, the new default is NULL.

According to two doc quotes above it should not be possible to have a column that allows NULLs and has no default value at the same time (MySQL forces a default value of NULL for such columns). 

Yet doing ALTER TABLE ttt ALTER a DROP DEFAULT (see example below) produces a column allowing NULLs but having no default value at the same time. This behavior contradicts the Quote 2 above from the docs.

P.S. Tested on InnoDB tables, sql_mode=ANSI,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

How to repeat:
CREATE TABLE "ttt" (
  "a" int(11),
  "b" int(11) NOT NULL
);

# Behaves according to Quote 1 - MySQL forced a default value of NULL for column "a".
SHOW CREATE TABLE ttt; # CREATE TABLE "ttt" ("a" int(11) DEFAULT NULL, "b" int(11) NOT NULL)
INSERT INTO ttt(b) VALUES(11); # No error

# Contradicts Quote 2 - default Behaves according to Quote 1 - MySQL forced a default value of NULL for column "a" is not forced during ALTER TABLE.
ALTER TABLE ttt ALTER a DROP DEFAULT;
SHOW CREATE TABLE ttt; # CREATE TABLE "ttt2" ("a" int(11), "b" int(11) NOT NULL)
INSERT INTO ttt(b) VALUES(22); # SQL Error (1364): Field 'a' doesn't have a default value

Suggested fix:
Fix the doc (quote 2) or fix the behavior of ALTER TABLE DROP DEFAULT.
[8 Apr 2016 11:01] Power Gamer
In "How to repeat" section a comment for ALTER TABLE should read:

# Contradicts Quote 2 - default value of NULL for column "a" is not forced during ALTER TABLE.
ALTER TABLE ttt ALTER a DROP DEFAULT;
[11 Apr 2016 13:18] MySQL Verification Team
Hello!

Thank you for the report and test case.
Observed this with 5.6.29/5.7.11.

Thanks,
Umesh
[11 Apr 2016 13:19] MySQL Verification Team
Related Bug #80222
[14 Oct 2019 10:09] MySQL Verification Team
Bug #97212 marked as duplicate of this one