MySQL Bugs: #47308: 'sql_auto_is_null' problem with bulk-INSERT

Bug #47308	'sql_auto_is_null' problem with bulk-INSERT
Submitted:	14 Sep 2009 12:39	Modified:	5 Oct 2009 15:38
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.38	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
I do not find expected behaviour with 'sql_auto_is_null' variable wiht BULK INSERTS

Reference:

http://dev.mysql.com/doc/refman/5.1/en/server-session-variables.html#sysvar_sql_auto_is_nu...

"If set to 1 (the default), you can find the last inserted row for a table that contains an AUTO_INCREMENT column by using the following construct: 
WHERE auto_increment_column IS NULL"

How to repeat:
SHOW VARIABLES LIKE 'sql_auto_is_null';
/*
Variable_name     Value 
----------------  ------
sql_auto_is_null  ON    
*/

DROP TABLE IF EXISTS t1 ;

CREATE TABLE `t1` (
   `id` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
   `txt` VARCHAR(50) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
INSERT INTO t1 (txt) VALUES ('a'),('b');

SELECT * FROM t1 WHERE id IS NULL;
/*
    id  txt   
------  ------
     1  a     
*/

DROP TABLE IF EXISTS t1 ;

CREATE TABLE `t1` (
   `id` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
   `txt` VARCHAR(50) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t1 (txt) VALUES ('a');
INSERT INTO t1 (txt) VALUES ('b');

SELECT * FROM t1 WHERE id IS NULL;
/*
    id  txt   
------  ------
     2  b        
*/

Suggested fix:
I think it is a bug, but if not (ie: if server internally INSERTS in an order that is not transparent for user) documentation should explicitly tell that only with 'single-INSERTS' and not 'bulk-INSERTS' the result of "..WHERE id IS NULL" is usable.

edited synopsis

I think http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id explains the case of bulk INSERT in details, so this is a request for more clear documentation at best.

OK, that explains, but nowhere in this page http://dev.mysql.com/doc/refman/5.1/en/server-session-variables.html#sysvar_sql_auto_is_nu... is there any mentioning that " .. WHERE id IS NULL" makes use of LAST_INSERT_ID() to decide what is 'last inserted row' if sql_auto_is_null is ON. 

the text

"If set to 1 (the default), you can find the last inserted row for a
table that contains an AUTO_INCREMENT column by using the following
construct: WHERE auto_increment_column IS NULL"

.. judged by itself is not (always) correct - it does not always find 'the last inserted row'. It finds the row 'identified by <link>LAST_INSERT_ID()</link>' and that may be different. The information functions page has this ".. returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column". And that explains - and also explains this btw:: 

INSERT INTO t1 VALUES (4,'d');
SELECT * FROM t1 WHERE id IS NULL; -- empty set

According to the *text* in http://dev.mysql.com/doc/refman/5.1/en/server-session-variables.html#sysvar_sql_auto_is_nu... that should return row with id = 4, because that row is the 'last inserted row' simply!  However '4' was specified by user and not auto-generated.

It is not obvious that one need to look in page page describing 'information functions' to understand the behavior of a server variable and what 'the last inserted row' is, if it is not what it intuitively means in plain language. So .. yes .. documentation request! Changed category to 'docs' accordingly.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

It turns out that using IS NULL when sql_auto_is_null = 1 is the same as invoking LAST_INSERT_ID(), so the description for that function describes what should happen for multiple-row inserts (you get the first value, not the last).  I have updated the sql_auto_is_null description to refer to LAST_INSERT_ID() accordingly:

If this 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. For details, including the return value after a multiple-row insert, see Section 11.11.3, “Information Functions”. If no AUTO_INCREMENT value was successfully inserted, the SELECT statement returns no row.

The behavior of retrieving an AUTO_INCREMENT value by using an IS NULL comparison is used by some ODBC programs, such as Access. See Section 21.1.7.1.1, “Obtaining Auto-Increment Values”. This behavior can be disabled by setting sql_auto_is_null to 0.