From cc6a70e7baeba8bdbd942ae233e35c84c20b3be2 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 10 Jul 2006 16:27:03 +0300 Subject: BUG#14553: NULL in WHERE resets LAST_INSERT_ID 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 is done with a special code that replaces 'auto_col IS NULL' with 'auto_col = LAST_INSERT_ID'. However this also resets the LAST_INSERT_ID to 0 as it uses it for a flag so as to ensure that only the first SELECT ... WHERE auto_col IS NULL after an INSERT has this special behaviour. In order to avoid resetting the LAST_INSERT_ID a special flag is introduced in the THD class. This flag is used to restrict the second and subsequent SELECTs instead of LAST_INSERT_ID. mysql-test/r/odbc.result: test suite for the bug mysql-test/r/rpl_insert_id.result: test for the fix in replication mysql-test/t/odbc.test: test suite for the bug mysql-test/t/rpl_insert_id.test: test for the fix in replication sql/sql_class.cc: initialize the flag sql/sql_class.h: flag's declaration and set code when setting the last_insert_id sql/sql_select.cc: the special flag is used instead of last_insert_id --- mysql-test/r/rpl_insert_id.result | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'mysql-test/r/rpl_insert_id.result') diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index 8482f631553..e683ea2fb39 100644 --- a/mysql-test/r/rpl_insert_id.result +++ b/mysql-test/r/rpl_insert_id.result @@ -73,3 +73,17 @@ CREATE TABLE t1 ( a INT UNIQUE ); SET FOREIGN_KEY_CHECKS=0; INSERT INTO t1 VALUES (1),(1); ERROR 23000: Duplicate entry '1' for key 1 +drop table t1; +create table t1(a int auto_increment, key(a)); +create table t2(a int); +insert into t1 (a) values (null); +insert into t2 (a) select a from t1 where a is null; +insert into t2 (a) select a from t1 where a is null; +select * from t2; +a +1 +select * from t2; +a +1 +drop table t1; +drop table t2; -- cgit v1.2.1