diff options
| -rw-r--r-- | mysql-test/r/odbc.result | 11 | ||||
| -rw-r--r-- | mysql-test/r/rpl_insert_id.result | 13 | ||||
| -rw-r--r-- | mysql-test/t/odbc.test | 10 | ||||
| -rw-r--r-- | mysql-test/t/rpl_insert_id.test | 19 | ||||
| -rw-r--r-- | sql/sql_class.cc | 1 | ||||
| -rw-r--r-- | sql/sql_class.h | 3 | ||||
| -rw-r--r-- | sql/sql_select.cc | 4 |
7 files changed, 59 insertions, 2 deletions
diff --git a/mysql-test/r/odbc.result b/mysql-test/r/odbc.result index 2d9d39393b1..5629d3dab33 100644 --- a/mysql-test/r/odbc.result +++ b/mysql-test/r/odbc.result @@ -14,3 +14,14 @@ explain select * from t1 where b is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE drop table t1; +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY); +INSERT INTO t1 VALUES (NULL); +SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL; +a last_insert_id() +1 1 +SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL; +a last_insert_id() +SELECT sql_no_cache a, last_insert_id() FROM t1; +a last_insert_id() +1 1 +DROP TABLE t1; diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index b11f1b92020..21eade8f9cf 100644 --- a/mysql-test/r/rpl_insert_id.result +++ b/mysql-test/r/rpl_insert_id.result @@ -73,7 +73,20 @@ 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 if exists t1, t2; +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; drop function if exists bug15728; drop function if exists bug15728_insert; drop table if exists t1, t2; diff --git a/mysql-test/t/odbc.test b/mysql-test/t/odbc.test index d4b6fc35e74..6a754bb32a7 100644 --- a/mysql-test/t/odbc.test +++ b/mysql-test/t/odbc.test @@ -21,4 +21,14 @@ select * from t1 where a is null; explain select * from t1 where b is null; drop table t1; +# +# Bug #14553: NULL in WHERE resets LAST_INSERT_ID +# +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY); +INSERT INTO t1 VALUES (NULL); +SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL; +SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL; +SELECT sql_no_cache a, last_insert_id() FROM t1; +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index e038829760d..94ce7f95fb4 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -77,6 +77,25 @@ sync_slave_with_master; connection master; drop table t1; sync_slave_with_master; + +# +# Bug#14553: NULL in WHERE resets LAST_INSERT_ID +# +connection master; +drop table if exists t1,t2; +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; +sync_slave_with_master; +connection slave; +select * from t2; +connection master; +drop table t1; +drop table t2; +sync_slave_with_master; # End of 4.1 tests diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 06082a57964..99b609e4267 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -269,6 +269,7 @@ THD::THD() tablespace_op=FALSE; ulong tmp=sql_rnd_with_mutex(); randominit(&rand, tmp + (ulong) &rand, tmp + (ulong) ::query_id); + substitute_null_with_insert_id = FALSE; thr_lock_info_init(&lock_info); /* safety: will be reset after start */ thr_lock_owner_init(&main_lock_id, &lock_info); } diff --git a/sql/sql_class.h b/sql/sql_class.h index eb075dd54bb..95d8f06a65e 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1321,6 +1321,8 @@ public: bool no_errors, password, is_fatal_error; bool query_start_used, rand_used, time_zone_used; bool last_insert_id_used,insert_id_used, clear_next_insert_id; + /* for IS NULL => = last_insert_id() fix in remove_eq_conds() */ + bool substitute_null_with_insert_id; bool in_lock_tables; bool query_error, bootstrap, cleanup_done; bool tmp_table_used; @@ -1452,6 +1454,7 @@ public: { last_insert_id= id_arg; insert_id_used=1; + substitute_null_with_insert_id= TRUE; } inline ulonglong insert_id(void) { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 41ff9185cfb..36ce2354e6e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7854,7 +7854,7 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) Field *field=((Item_field*) args[0])->field; if (field->flags & AUTO_INCREMENT_FLAG && !field->table->maybe_null && (thd->options & OPTION_AUTO_IS_NULL) && - thd->insert_id()) + thd->insert_id() && thd->substitute_null_with_insert_id) { #ifdef HAVE_QUERY_CACHE query_cache_abort(&thd->net); @@ -7873,7 +7873,7 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) */ cond->fix_fields(thd, &cond); } - thd->insert_id(0); // Clear for next request + thd->substitute_null_with_insert_id= FALSE; // Clear for next request } /* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */ else if (((field->type() == FIELD_TYPE_DATE) || |