MySQL Bugs: #37735: WHERE clause fails immediately following INSERT on AUTO INCREMENT column

Bug #37735	WHERE clause fails immediately following INSERT on AUTO INCREMENT column
Submitted:	30 Jun 2008 8:40	Modified:	30 Jun 2008 15:24
Reporter:	Simon Litchfield	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1, 5.0, 4.1, 6.0 BZR	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A WHERE clause involving a NOT .. IS NULL condition on an AUTO INCREMENT column is ignored, but seemingly only when it is the first such statement immediately following a series of CREATE TABLE .. INSERT .. statements during the same connection.

If the connection is reset, or some other statements are executed in between, it seems to work fine. 

The bug has been confirmed by running the SQL below on server versions 5.0.27 and 5.0.58, and occurs on both MyISAM and InnoDB.

How to repeat:
CREATE TABLE `articles_channel` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(255) NOT NULL
)
;
CREATE TABLE `articles_article` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `title` varchar(255) NOT NULL
)
;
CREATE TABLE `articles_article_channels` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `article_id` integer NOT NULL,
    `channel_id` integer NOT NULL,
    UNIQUE (`article_id`, `channel_id`)
)
;
INSERT INTO `articles_channel` (`id`, `name`) VALUES (6, 'Business');
INSERT INTO `articles_article` (`id`, `title`) VALUES (1, 'Article Title 1');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (1, 6);
INSERT INTO `articles_article` (`id`, `title`) VALUES (2, 'Article Title 2');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (2, 6);
INSERT INTO `articles_article` (`id`, `title`) VALUES (3, 'Article Title 3');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (3, 6);
INSERT INTO `articles_article` (`id`, `title`) VALUES (4, 'Article Title 4');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (4, 6);
INSERT INTO `articles_article` (`id`, `title`) VALUES (5, 'Other Article Title 5');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (5, 6);
INSERT INTO `articles_article` (`id`, `title`) VALUES (6, 'Other Article Title 6');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (6, 6);
INSERT INTO `articles_article` (`id`, `title`) VALUES (7, 'Other Article Title 7');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (7, 6);
INSERT INTO `articles_article` (`id`, `title`) VALUES (8, 'Other Article Title 8');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (8, 6);
INSERT INTO `articles_article` (`id`, `title`) VALUES (9, 'Yet Another Article');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (9, 6);
SELECT `articles_article`.`id` FROM `articles_article` WHERE NOT (`articles_article`.`id` = 9  AND NOT (`articles_article`.`id` IS NULL));
SELECT `articles_article`.`id` FROM `articles_article` WHERE NOT (`articles_article`.`id` = 9  AND NOT (`articles_article`.`id` IS NULL));
DROP TABLE articles_channel;
DROP TABLE articles_article;
DROP TABLE articles_article_channels;

Thank you for the report.

Verified as described.

Many thanks for writing a bug report.

Verified as described by using given tests.

Verified on bzr trees for 5.0 and 5.1.

Hi,

this is not a bug.

Let me explain why:

SELECT `articles_article`.`id` FROM `articles_article` WHERE NOT (`articles_article`.`id`
= 9  AND NOT (`articles_article`.`id` IS NULL));

only look to the where condition and I made it more human readable:

WHERE NOT (a=9 AND not b is NULL)

According to http://dev.mysql.com/doc/refman/5.0/en/create-table.html#id1099856:

"To make MySQL compatible with some ODBC applications, you can find the AUTO_INCREMENT value for the last inserted row with the following query:

SELECT * FROM tbl_name WHERE auto_col IS NULL"

This means your condition is:

WHERE NOT(a=9 AND NOT b=last inserted id)

at the first time, your last inserted id is 9.

This means: a=9 => TRUE, b=last inserted id => TRUE but you said NOT b and that means TRUE will get FALSE

So, you have: TRUE AND FALSE ... the result is FALSE

Now you say not to this FALSE and it get TRUE.

Now you run the query the second time. Last inserted id always just can get determined for the first time.

This means: a=9 => TRUE, b=last inserted id => FALSE because of NOT the FALSE will get TRUE.
TRUE AND TRUE will get TRUE
And then NOT TRUE will get FALSE

So the 9 won't be selected here.

Try reducing the number of inserts to less than 9x -- say 3x, and re-run the SQL. Works fine.

Also, I've got to wonder, has there been any recent discussion re the validity of this 'feature' or any way to disable it? It seems very inconsistent to me, and I would have thought it would be the ODBC driver's responsibility to execute LAST_INSERT_ID().

Thank you for the feedback.

Regarding "Try reducing the number of inserts to less than 9x -- say 3x, and re-run the SQL. Works fine." Please check value of LAST_INSERT_ID() for such query and if this is not NULL provide us repeatable test case which works fine. Currently re-closing the report as "Not a Bug".

Sorry, I wasn't very clear. Try running the below. Note both SELECTs here return the same; yet in my earlier example, the first SELECT returned an extra row. This behaviour is inconsistent with the documentation. My other side point was was regarding the validity of such behaviour.

CREATE TABLE `articles_channel` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(255) NOT NULL
)
;
CREATE TABLE `articles_article` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `title` varchar(255) NOT NULL
)
;
CREATE TABLE `articles_article_channels` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `article_id` integer NOT NULL,
    `channel_id` integer NOT NULL,
    UNIQUE (`article_id`, `channel_id`)
)
;
INSERT INTO `articles_channel` (`id`, `name`) VALUES (6, 'Business');
INSERT INTO `articles_article` (`id`, `title`) VALUES (1, 'Article Title 1');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (1, 6);
INSERT INTO `articles_article` (`id`, `title`) VALUES (2, 'Article Title 2');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (2, 6);
INSERT INTO `articles_article` (`id`, `title`) VALUES (9, 'Yet Another Article');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (9, 6);
SELECT `articles_article`.`id` FROM `articles_article` WHERE NOT (`articles_article`.`id`
= 9  AND NOT (`articles_article`.`id` IS NULL));
SELECT `articles_article`.`id` FROM `articles_article` WHERE NOT (`articles_article`.`id`
= 9  AND NOT (`articles_article`.`id` IS NULL));
DROP TABLE articles_channel;
DROP TABLE articles_article;
DROP TABLE articles_article_channels;

Thank you for the feedback.

In case of

INSERT INTO `articles_channel` (`id`, `name`) VALUES (6, 'Business');
INSERT INTO `articles_article` (`id`, `title`) VALUES (1, 'Article Title 1');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (1, 6);
INSERT INTO `articles_article` (`id`, `title`) VALUES (2, 'Article Title 2');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (2, 6);
INSERT INTO `articles_article` (`id`, `title`) VALUES (9, 'Yet Another Article');
INSERT INTO `articles_article_channels` (`article_id`, `channel_id`) VALUES (9, 6);

last insert generates auto_increment 3, not 9. This is why you get same results for both queries. This happens because auto_col IS NULL works exactly as LAST_INSERT_ID() which means insert id from last table, not table used in the query.

In my first example, exactly the same two statements preceded the two SELECTs. Also, the IS NULL is referencing articles_article, not articles_article_channels.

Simon,

you only will get this mad behaviour when by accident the last inserted ID is the same value as the ID which you won't select.

The behaviour that you reported here originally is according to our documentation and to my explanations about boolean algebra an expected behaviour.

That WHERE auto_increment_id IS NULL will select last_insert_id() and will behave same way as like last_insert_id is confusing but it's documented that it is so and so it's an expected behaviour.

OK, so to clarify --

1. Insert data to table A and get an auto ID of 3.
2. Select data from any unrelated table B, with WHERE ... IS NULL on an auto column.
3. Results will include an extra row from B, if the completely unrelated auto column just so happens to have the value of 3.

This is obviously a bug. Even if the behaviour described in the documentation is deemed desirable (which by the way seems ridiculous), it would need to be limited to the same table and same column that the last insert ID came from.