MySQL Bugs: #43151: default values doesn't mention implicit default for columns in pkey

Bug #43151	default values doesn't mention implicit default for columns in pkey
Submitted:	24 Feb 2009 15:05	Modified:	18 Mar 2013 14:02
Reporter:	Stewart Smith	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0, 5.1.31	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
NOT NULL Columns get implicit default values if part of a key. Manual says they shouldn't.

Quoting from 
http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

 If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

    *

      If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.
    *

      If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For non-transactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

How to repeat:
mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i int, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL default '0',
  `b` int(11) NOT NULL default '0',
  `c` int(11) NOT NULL default '0',
  `d` int(11) NOT NULL default '0',
  `e` int(11) NOT NULL default '0',
  `f` int(11) NOT NULL default '0',
  `g` int(11) NOT NULL default '0',
  `h` int(11) NOT NULL default '0',
  `i` int(11) NOT NULL default '0',
  PRIMARY KEY  (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`i`,`h`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into t1 (a) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| sql_mode      | STRICT_ALL_TABLES | 
+---------------+-------------------+
1 row in set (0.01 sec)

Suggested fix:
Changing this behaviour will likely break things. I know it breaks alter_table test for one.

Fixed in upcoming patch to Drizzle (don't set implicit default value).

mysql> select version();
+------------------------+
| version()              |
+------------------------+
| 5.0.51a-3ubuntu5.4-log | 
+------------------------+
1 row in set (0.00 sec)

Verified with 5.1.31:

sh-3.2# /usr/local/mysql/bin/mysql -uroot myDB
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.31 MySQL Community Server (GPL)

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

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

mysql>  create table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i int,
    -> primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
Query OK, 0 rows affected (0.45 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) NOT NULL DEFAULT '0',
  `c` int(11) NOT NULL DEFAULT '0',
  `d` int(11) NOT NULL DEFAULT '0',
  `e` int(11) NOT NULL DEFAULT '0',
  `f` int(11) NOT NULL DEFAULT '0',
  `g` int(11) NOT NULL DEFAULT '0',
  `h` int(11) NOT NULL DEFAULT '0',
  `i` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`i`,`h`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.36 sec)

mysql> select * from t1;
+---+---+---+---+---+---+---+---+---+
| a | b | c | d | e | f | g | h | i |
+---+---+---+---+---+---+---+---+---+
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 
+---+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)

Actually, I'd say it is a server bug. But current behavior should be properly documented until the bug is fixed (in 6.0?)

Is this a bug? Look at the SHOW CREATE TABLE output -- every column has "default '0'", so the INSERT just inserts the default value of 0.

If you define each column explicitly as NOT NULL, the SHOW CREATE TABLE output will show that the columns do not have a DEFAULT value, and in that case the INSERT does indeed fail.

The fact that column that is a part of a multiple-column PRIMARY KEY (and is NOT NULL as a result) gets DEFAULT value implicitly is NOT documented. This is a documentation problem to fix.

Changed the default-values section from:

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

To:

If the column cannot take NULL as the value, MySQL defines the column
with no explicit DEFAULT clause. Exception: If the column is defined
as part of a PRIMARY KEY but not explicitly as NOT NULL, MySQL
creates it as a NOT NULL column (because PRIMARY KEY columns must be
NOT NULL), but also assigns it a DEFAULT clause using the implicit
default value. To prevent this, include an explicit NOT NULL in the
definition of any PRIMARY KEY column.

The documentation aspect is handled now.

Setting report to Server: Data Types/Open so that the server bug aspect can be dealt with.

The conditions under which the anomaly appears are:

* Column definition does not include NOT NULL
* Column definition does not include a DEFAULT clause
* Column is included in PRIMARY KEY definition

Duplicate of http://bugs.mysql.com/bug.php?id=36723