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
Triage: Triaged: D2 (Serious)

[24 Feb 2009 15:05] Stewart Smith
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).
[24 Feb 2009 15:06] Stewart Smith
mysql> select version();
+------------------------+
| version()              |
+------------------------+
| 5.0.51a-3ubuntu5.4-log | 
+------------------------+
1 row in set (0.00 sec)
[24 Feb 2009 15:17] Valeriy Kravchuk
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?)
[6 Mar 2009 17:58] Paul Dubois
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.
[6 Mar 2009 18:49] Valeriy Kravchuk
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.
[20 Aug 2010 17:59] Paul Dubois
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.
[20 Aug 2010 18:00] Paul Dubois
The documentation aspect is handled now.

Setting report to Server: Data Types/Open so that the server bug aspect can be dealt with.
[20 Aug 2010 18:06] Paul Dubois
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
[18 Mar 2013 13:23] Abhishek Ranjan
Duplicate of http://bugs.mysql.com/bug.php?id=36723