MySQL Bugs: #42270: With SQL server mode NO_AUTO_VALUE_ON

Bug #42270	With SQL server mode NO_AUTO_VALUE_ON_ZERO, DEFAULT is treated as 0
Submitted:	22 Jan 2009 13:52	Modified:	22 Jan 2009 14:30
Reporter:	Kristof Coomans	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0, 5.1.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DEFAULT, NO_AUTO_VALUE_ON_ZERO, server mode

Description:
If you have configured the SQL mode to NO_AUTO_VALUE_ON_ZERO, then DEFAULT is considered to be 0.

How to repeat:
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"

CREATE TABLE ezurlalias_ml_incr (
  id int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY ( id )
) TYPE=InnoDB;

#inserts 0, while next auto_increment value is 1
INSERT INTO ezurlalias_ml_incr(id) VALUES(DEFAULT);

#inserts 0 again, causing an error
INSERT INTO ezurlalias_ml_incr(id) VALUES(DEFAULT);

Suggested fix:
DEFAULT should be handled the same as NULL, generating the auto_increment sequence number.

See http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_no_auto_value_on_zero

Verified just as described with 5.1.30 also:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.30-enterprise-gpl-advanced-debug-log MySQL Enterprise Server
 - Advanced Edition Debug (GPL)

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

mysql> SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE ezurlalias_ml_incr (
    ->   id int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY ( id )
    -> ) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (1.13 sec)

mysql> INSERT INTO ezurlalias_ml_incr(id) VALUES(DEFAULT);
Query OK, 1 row affected (0.09 sec)

mysql> select * from ezurlalias_ml_incr;
+----+
| id |
+----+
|  0 |
+----+
1 row in set (0.09 sec)

mysql> INSERT INTO ezurlalias_ml_incr(id) VALUES(DEFAULT);
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

While the manual (http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html) clearly says:

"For numeric types, the default is 0, with the exception that for integer or floating-point types declared with the AUTO_INCREMENT  attribute, the default is the next value in the sequence."