MySQL Bugs: #51910: CREATE TABLE adds implicit DEFAULT value for primary key when not defined inline

Bug #51910	CREATE TABLE adds implicit DEFAULT value for primary key when not defined inline
Submitted:	10 Mar 2010 13:06	Modified:	10 Jan 2013 14:30
Reporter:	Tobias Asplund	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.32, 5.1.45	OS:	MacOS
Assigned to:		CPU Architecture:	Any

Description:
Two different, but equivalent ways of creating a simple table will not produce the same end result.

mysql> CREATE TABLE t10 (
    ->   a INT PRIMARY KEY      -- PK created inline
    -> );

mysql> CREATE TABLE t11 (                       
    ->   b INT,
    ->   PRIMARY KEY (b)          -- PK created as index
    -> );

mysql> SHOW CREATE TABLE t10 \G SHOW CREATE TABLE t11 \G
*************************** 1. row ***************************
       Table: t10
Create Table: CREATE TABLE `t10` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: t11
Create Table: CREATE TABLE `t11` (
  `b` int(11) NOT NULL DEFAULT '0',                     -- Where does this DEFAULT '0' come from?
  PRIMARY KEY (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE t10 ( a INT PRIMARY KEY );
CREATE TABLE t11 ( b INT,PRIMARY KEY (b));

SHOW CREATE TABLE t10 \G SHOW CREATE TABLE t11 \G

Suggested fix:
Why does it add a DEFAULT when I did not specify one?

Indeed, it does not look consistent:

77-52-24-143:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.45-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t10 ( a INT PRIMARY KEY );
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE t11 ( b INT,PRIMARY KEY (b));
Query OK, 0 rows affected (0.11 sec)

mysql> show create table t10\G
*************************** 1. row ***************************
       Table: t10
Create Table: CREATE TABLE `t10` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> show create table t11\G
*************************** 1. row ***************************
       Table: t11
Create Table: CREATE TABLE `t11` (
  `b` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

Maybe it has something to do with this:

mysql> CREATE TABLE t12 ( b INT );
Query OK, 0 rows affected (0.06 sec)

mysql> show create table t12\G
*************************** 1. row ***************************
       Table: t12
Create Table: CREATE TABLE `t12` (
  `b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I'd say that there should be NO DEFAULT value added implicitly, in any case. But our manual, http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html, says that DEFAULT NULL is OK for columns accepting NULL values. 

The case reported is not clearly explained in the manual though, IMHO.

Duplicate of bug#36723