From: Date: March 26 2007 10:26pm Subject: bk commit into 5.0 tree (evgen:1.2477) BUG#23233 List-Archive: http://lists.mysql.com/commits/22964 X-Bug: 23233 Message-Id: <20070326202612.7B8CA23D61@moonbone.moonbone.local> Below is the list of changes that have just been committed into a local 5.0 repository of evgen. When evgen does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-03-27 00:26:08+04:00, evgen@stripped +5 -0 Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the NO_AUTO_VALUE_ON_ZERO mode. In the NO_AUTO_VALUE_ON_ZERO mode the table->auto_increment_field_not_null variable is used to indicate that a non-NULL value was specified by the user for an auto_increment column. When an INSERT .. ON DUPLICATE updates the auto_increment field this variable is set to true and stays unchanged for the next insert operation. This makes the next inserted row sometimes wrongly have 0 as the value of the auto_increment field. Now the fill_record() function resets the table->auto_increment_field_not_null variable before filling the record. mysql-test/r/insert_update.result@stripped, 2007-03-27 00:23:18+04:00, evgen@stripped +78 -0 Added the test case for the bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the NO_AUTO_VALUE_ON_ZERO mode. mysql-test/t/insert_update.test@stripped, 2007-03-27 00:23:14+04:00, evgen@stripped +52 -0 Added the test case for the bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the NO_AUTO_VALUE_ON_ZERO mode. sql/handler.cc@stripped, 2007-03-27 00:24:16+04:00, evgen@stripped +1 -4 Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the NO_AUTO_VALUE_ON_ZERO mode. Now the handler::update_auto_increment() function doesn't reset the table->auto_increment_field_not_null variable as it is done in the fill_record() function. sql/sql_base.cc@stripped, 2007-03-27 00:24:09+04:00, evgen@stripped +32 -3 Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the NO_AUTO_VALUE_ON_ZERO mode. Now the open_table() function initializes the table->auto_increment_field_not_null variable. sql/table.h@stripped, 2007-03-27 00:23:43+04:00, evgen@stripped +5 -0 Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the NO_AUTO_VALUE_ON_ZERO mode. A comment added. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: evgen # Host: moonbone.local # Root: /mnt/gentoo64/work/23233-bug-5.0-opt-mysql --- 1.228/sql/handler.cc 2007-02-21 14:04:59 +03:00 +++ 1.229/sql/handler.cc 2007-03-27 00:24:16 +04:00 @@ -1598,7 +1598,6 @@ ulonglong nr; THD *thd= table->in_use; struct system_variables *variables= &thd->variables; - bool auto_increment_field_not_null; DBUG_ENTER("handler::update_auto_increment"); /* @@ -1606,11 +1605,9 @@ row was not inserted */ thd->prev_insert_id= thd->next_insert_id; - auto_increment_field_not_null= table->auto_increment_field_not_null; - table->auto_increment_field_not_null= FALSE; if ((nr= table->next_number_field->val_int()) != 0 || - auto_increment_field_not_null && + table->auto_increment_field_not_null && thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO) { /* Clear flag for next row */ --- 1.370/sql/sql_base.cc 2007-03-08 20:29:58 +03:00 +++ 1.371/sql/sql_base.cc 2007-03-27 00:24:09 +04:00 @@ -1640,6 +1640,9 @@ table->used_keys= table->s->keys_for_keyread; table->fulltext_searched= 0; table->file->ft_handler= 0; + /* Catch wrong handling of the auto_increment_field_not_null. */ + DBUG_ASSERT(!table->auto_increment_field_not_null); + table->auto_increment_field_not_null= FALSE; if (table->timestamp_field) table->timestamp_field_type= table->timestamp_field->get_auto_set_type(); table->pos_in_table_list= table_list; @@ -5283,27 +5286,53 @@ List_iterator_fast f(fields),v(values); Item *value, *fld; Item_field *field; + TABLE *table= 0; DBUG_ENTER("fill_record"); + /* + Reset the table->auto_increment_field_not_null as it is valid for + only one row. + */ + if (fields.elements) + { + /* + On INSERT or UPDATE fields are checked to be from the same table, + thus we safely can take table from the first field. + */ + fld= f++; + if (!(field= fld->filed_for_view_update())) + { + my_error(ER_NONUPDATEABLE_COLUMN, MYF(0), fld->name); + goto err; + } + table= field->field->table; + if (table->next_number_field) + table->auto_increment_field_not_null= FALSE; + f.rewind(); + } while ((fld= f++)) { if (!(field= fld->filed_for_view_update())) { my_error(ER_NONUPDATEABLE_COLUMN, MYF(0), fld->name); - DBUG_RETURN(TRUE); + goto err; } value=v++; Field *rfield= field->field; - TABLE *table= rfield->table; + table= rfield->table; if (rfield == table->next_number_field) table->auto_increment_field_not_null= TRUE; if ((value->save_in_field(rfield, 0) < 0) && !ignore_errors) { my_message(ER_UNKNOWN_ERROR, ER(ER_UNKNOWN_ERROR), MYF(0)); - DBUG_RETURN(TRUE); + goto err; } } DBUG_RETURN(thd->net.report_error); +err: + if (table && table->next_number_field) + table->auto_increment_field_not_null= FALSE; + DBUG_RETURN(TRUE); } --- 1.139/sql/table.h 2007-03-06 21:19:14 +03:00 +++ 1.140/sql/table.h 2007-03-27 00:23:43 +04:00 @@ -273,6 +273,11 @@ my_bool no_cache; /* To signal that we should reset query_id for tables and cols */ my_bool clear_query_id; + /* + To indicate that a non-null value of the auto_increment field + was provided by the user or retrieved from the current record. + Used only in the MODE_NO_AUTO_VALUE_ON_ZERO mode. + */ my_bool auto_increment_field_not_null; my_bool insert_or_update; /* Can be used by the handler */ my_bool alias_name_used; /* true if table_name is alias */ --- 1.22/mysql-test/r/insert_update.result 2007-03-15 18:33:46 +03:00 +++ 1.23/mysql-test/r/insert_update.result 2007-03-27 00:23:18 +04:00 @@ -247,3 +247,81 @@ LAST_INSERT_ID() 1 DROP TABLE t1; +SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; +CREATE TABLE `t1` ( +`id` int(11) PRIMARY KEY auto_increment, +`f1` varchar(10) NOT NULL UNIQUE +); +INSERT IGNORE INTO t1 (f1) VALUES ("test1") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +INSERT IGNORE INTO t1 (f1) VALUES ("test1") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +1 +SELECT * FROM t1; +id f1 +1 test1 +INSERT IGNORE INTO t1 (f1) VALUES ("test2") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT * FROM t1; +id f1 +1 test1 +2 test2 +INSERT IGNORE INTO t1 (f1) VALUES ("test2") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +2 +SELECT * FROM t1; +id f1 +1 test1 +2 test2 +INSERT IGNORE INTO t1 (f1) VALUES ("test3") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +3 +SELECT * FROM t1; +id f1 +1 test1 +2 test2 +3 test3 +DROP TABLE t1; +CREATE TABLE `t1` ( +`id` int(11) PRIMARY KEY auto_increment, +`f1` varchar(10) NOT NULL UNIQUE +); +INSERT IGNORE INTO t1 (f1) VALUES ("test1") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +1 +SELECT * FROM t1; +id f1 +1 test1 +INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +1 +SELECT * FROM t1; +id f1 +1 test1 +2 test4 +DROP TABLE t1; +CREATE TABLE `t1` ( +`id` int(11) PRIMARY KEY auto_increment, +`f1` varchar(10) NOT NULL UNIQUE, +tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp +); +INSERT INTO t1 (f1) VALUES ("test1"); +SELECT * FROM t1; +id f1 tim1 +1 test1 2003-01-01 00:00:00 +REPLACE INTO t1 VALUES (0,"test1",null); +SELECT * FROM t1; +id f1 tim1 +0 test1 2007-03-26 23:12:49 +DROP TABLE t1; +SET SQL_MODE=''; --- 1.22/mysql-test/t/insert_update.test 2007-03-15 18:34:02 +03:00 +++ 1.23/mysql-test/t/insert_update.test 2007-03-27 00:23:14 +04:00 @@ -174,3 +174,55 @@ INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1); SELECT LAST_INSERT_ID(); DROP TABLE t1; + +# +# Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the +# NO_AUTO_VALUE_ON_ZERO mode. +# +SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; +CREATE TABLE `t1` ( + `id` int(11) PRIMARY KEY auto_increment, + `f1` varchar(10) NOT NULL UNIQUE +); +INSERT IGNORE INTO t1 (f1) VALUES ("test1") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +INSERT IGNORE INTO t1 (f1) VALUES ("test1") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +INSERT IGNORE INTO t1 (f1) VALUES ("test2") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT * FROM t1; +INSERT IGNORE INTO t1 (f1) VALUES ("test2") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +INSERT IGNORE INTO t1 (f1) VALUES ("test3") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +DROP TABLE t1; +CREATE TABLE `t1` ( + `id` int(11) PRIMARY KEY auto_increment, + `f1` varchar(10) NOT NULL UNIQUE +); +INSERT IGNORE INTO t1 (f1) VALUES ("test1") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +DROP TABLE t1; +CREATE TABLE `t1` ( + `id` int(11) PRIMARY KEY auto_increment, + `f1` varchar(10) NOT NULL UNIQUE, + tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp +); +INSERT INTO t1 (f1) VALUES ("test1"); +SELECT * FROM t1; +REPLACE INTO t1 VALUES (0,"test1",null); +SELECT * FROM t1; +DROP TABLE t1; +SET SQL_MODE='';