Bug #64997 WHERE [AUTO_INCREMENT PK] IS NULL incorrectly returns a row after NULL INSERT

• Submitted: 16 Apr 2012 17:45
• Modified: 16 Apr 2012 18:15
• Reporter: David Schmitt
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.77
• OS: Linux (RHEL 5.6, CentOS 5.5)
• CPU Architecture: Any
• Tags: auto_increment, insert, null, SELECT

[16 Apr 2012 17:45] David Schmitt

Description:
After inserting a row using the NULL value for an AUTO_INCREMENT primary key column, the very next query (only) will incorrectly return that row when filtering based on primary key column 'IS NULL'.

Subsequent queries will (correctly) not return the row.

Occurs with persistent tables and temporary tables
Occurs with MyISAM and InnoDB
Occurs with command line mysql and PHP MySQLi API

Setting the primary key column to zero instead of the NULL value avoids the bug.

How to repeat:
-- Setup
CREATE TEMPORARY TABLE dummy (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

-- 1. Affects row counts
INSERT INTO dummy (id, name) VALUES (NULL, 'Test Row Counts');

-- First query returns 1
SELECT count(*) FROM dummy WHERE id IS NULL;

-- Second query returns 0
SELECT count(*) FROM dummy WHERE id IS NULL;

-- 2. Affects row results
INSERT INTO dummy (id, name) VALUES (NULL, 'Test Row Results');

-- First query returns one row
SELECT * FROM dummy WHERE id IS NULL;

-- Second query returns no rows
SELECT * FROM dummy WHERE id IS NULL;

Suggested fix:
Prevent query from returning row when value is not null.

[16 Apr 2012 18:15] Valeriy Kravchuk

This is not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_is-null:

"To work well with ODBC programs, MySQL supports the following extra features when using IS NULL:

    If sql_auto_is_null variable is set to 1 (the default), then after a statement that successfully inserts an automatically generated AUTO_INCREMENT value, you can find that value by issuing a statement of the following form:

    SELECT * FROM tbl_name WHERE auto_col IS NULL

    If the statement returns a row, the value returned is the same as if you invoked the LAST_INSERT_ID() function."