MySQL Bugs: #30643: Sql query returning different result sets when called more then once

Bug #30643	Sql query returning different result sets when called more then once
Submitted:	27 Aug 2007 9:19	Modified:	19 Nov 2007 15:47
Reporter:	walid bakkar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.27, 4.1, 5.0, 5.1, 5.2 BK	OS:	Any (Linux, Windows)
Assigned to:	Martin Hansson	CPU Architecture:	Any

Description:
Sql query returning different result sets when called more then once.
when calling the query the first time, the correct result is returned. calling the same query again, returns documents that do not match the criteria.

How to repeat:
-Load the attached db
-create the following temp table:
create TEMPORARY table if not exists AF_VFT (rnum int auto_increment PRIMARY KEY, docid bigint) ENGINE = MEMORY;
-run: insert into AF_VFT (docid) select d_docid from (select distinct d.docid as d_docid from DOCUMENT d where ((d.docid != 1 OR d.docid IS NULL ))) ids  order by d_docid DESC;
first time, 8 records are inserted. the next run will insert 9 records. the following one will go back to inserting 8. correct behavior is to insert 8.

Thank you for the report.

Verified as described.

test case

Attachment: bug30643.test (application/octet-stream, text), 2.94 KiB.

This appears to be a 'feature', albeit an inconsistent one. The following triggers the bug:

CREATE TABLE t1 (
  a INT AUTO_INCREMENT,
  KEY( a )
);
INSERT INTO t1 VALUES (1), (2), (3);

CREATE TABLE t2 (
  a INT,
  b INT AUTO_INCREMENT,
  KEY( b )  
);
INSERT INTO t2( a ) SELECT a FROM t1;

SELECT COUNT(*) FROM t1 WHERE a IS NULL;
> 1
SELECT COUNT(*) FROM t1 WHERE a IS NULL;
> 0

I found this comment in the code:

      Handles this special case for some ODBC applications:
      The are requesting the row that was just updated with a auto_increment
      value with this construct:

      SELECT * from table_name where auto_increment_column IS NULL
      This will be changed to:
      SELECT * from table_name where auto_increment_column = LAST_INSERT_ID

In order to be compatible with odbc, MySQL has the feature that *right after* an update, a query with "auto_increment_column IS NULL" in the WHERE clause gets
rewritten to: "auto_increment_column = last_insert_id()". This behavior is present for the duration of one statement only.

The function last_insert_id() returns the last auto-generated value for a table. Hence:

SELECT last_insert_id(-1);
CREATE TABLE t1 ( a INT AUTO_INCREMENT, KEY( a ) );
INSERT INTO t1 VALUES (1), (2);
SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               -1 |
+------------------+
INSERT INTO t1 VALUES (NULL); // forces auto-generation of value
SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                3 |
+------------------+

In Sveta's test what we saw was that after insert into af_vft, last_insert_id() = 1. So the first select count [...] following it gets rewritten to:

select count(d_docid) from (select distinct d.docid as d_docid
from document d where ((d.docid != 1 or d.docid = 1 ))) ids  order by d_docid desc;

Which returns the correct value of 1.

This behavior cannot be changed since odbc, and thus many applications(including Delphi and Access) depend on it. Many other DBMSes behave the same way.

For more information, please consult the manual:

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
http://dev.mysql.com/doc/refman/5.0/en/myodbc-usagenotes-functionality.html#myodbc-usageno...

According to Monty, we have no choice. We must support ODBC. Using a statement such as WHERE column IS NULL on an autoincremented colun is not a statement that should normally be used. The result would under "normal" circumstances be always empty.