MySQL Bugs: #36723: MySQL shows wrong default value for PK if NOT NULL not specified

Bug #36723	MySQL shows wrong default value for PK if NOT NULL not specified
Submitted:	14 May 2008 20:09	Modified:	10 Oct 2013 17:05
Reporter:	Stepan Koltsov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.32, 5.0.60	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL generates some default value for primary key if NOT NULL is not specified.

How to repeat:
===
CREATE TABLE a (x INT, PRIMARY KEY(x));

SHOW CREATE TABLE a;
===

yields

===
CREATE TABLE `a` (
  `x` int(11) NOT NULL default '0',
  PRIMARY KEY  (`x`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
===

Columns x now has default value 0. This confuses our mysql-diff application that compares create script and database.

Thank you for a problem report. Verified just as described with 5.0.60:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.60-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table a;
Query OK, 0 rows affected (0.11 sec)

mysql> create table a (x INT, y INT NOT NULL, PRIMARY KEY(x));
Query OK, 0 rows affected (0.08 sec)

mysql> show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `x` int(11) NOT NULL default '0',
  `y` int(11) NOT NULL,
  PRIMARY KEY  (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

So, unlike other NOT NULL column (and PK is NOT NULL by definition), PK got explicit DEFAULT '0' clause. This is clearly against the manual, http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html:

"As of MySQL 5.0.2, 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. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause."

Current behaviour does not depend on SQL mode:

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> drop table a;
Query OK, 0 rows affected (0.05 sec)

mysql> create table a (x INT, y INT NOT NULL, PRIMARY KEY(x));
Query OK, 0 rows affected (0.08 sec)

mysql> show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `x` int(11) NOT NULL default '0',
  `y` int(11) NOT NULL,
  PRIMARY KEY  (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

So, this is either a bug or (as I assume) a documentation request at least.

Just adding my 2 cents to this. 

As we all know PK's cannot contain NULL's, but I think that it shouldn't explicitly set a default value as 0. 
More importantly, if I try to go against the "rules":

mysql> SET SESSION sql_mode=ANSI;       
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a INT DEFAULT NULL, b INT DEFAULT NULL, c INT DEFAULT NULL, d INT DEFAULT NULL, PRIMARY KEY should_not_work(c,d), UNIQUE KEY testcase(a,b)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)

(yes, I'm also noting that naming a PK should not be accepted, as Arjen pointed out in bug #1956 )

mysql> SHOW CREATE TABLE t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE "t1" (
  "a" int(11) default NULL,
  "b" int(11) default NULL,
  "c" int(11) NOT NULL default '0',
  "d" int(11) NOT NULL default '0',
  PRIMARY KEY  ("c","d"),
  UNIQUE KEY "testcase" ("a","b")
) | 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

So not only does it silently add a default '0', but shouldn't we - in theory - be getting an error - or at the very least - a warning that the definitions that we provide is changed?  (At least in this case where I try to define them directly as NULLs, I feel that the parser should throw an error, or am I wrong?)

bug#51910 was marked as a duplicate.

http://bugs.mysql.com/43151 was marked as a duplicate.

Noted in 5.7.3 changelog.

For a column declared as a PRIMARY KEY, the MySQL sometimes
unnecessarily added a DEFAULT clause. For example, for CREATE TABLE t
(a INT, PRIMARY KEY(a)), a DEFAULT 0 clause was added, whereas with
CREATE TABLE t (a INT PRIMARY KEY), it was not.