MySQL Bugs: #81010: ALTER TABLE DROP DEFAULT - default value of NULL not forced

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

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.

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;

Hello!

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

Thanks,
Umesh

Related Bug #80222

Bug #97212 marked as duplicate of this one