diff options
| -rw-r--r-- | mysql-test/r/insert_update.result | 11 | ||||
| -rw-r--r-- | mysql-test/t/insert_update.test | 12 | ||||
| -rw-r--r-- | sql/sql_class.h | 22 | ||||
| -rw-r--r-- | sql/sql_insert.cc | 16 |
4 files changed, 49 insertions, 12 deletions
diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index f658ff06624..b1dee844515 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -236,3 +236,14 @@ INSERT INTO t2 VALUES (1), (3); INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a; ERROR 42S22: Unknown column 'a' in 'field list' DROP TABLE t1,t2; +CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY, +f2 VARCHAR(5) NOT NULL UNIQUE); +INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +1 +INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +1 +DROP TABLE t1; diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test index 4581cc7a875..2ef378aa478 100644 --- a/mysql-test/t/insert_update.test +++ b/mysql-test/t/insert_update.test @@ -162,3 +162,15 @@ INSERT INTO t2 VALUES (1), (3); --error ER_BAD_FIELD_ERROR INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a; DROP TABLE t1,t2; + +# +# Bug#27033: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE if rows were +# touched but not actually changed. +# +CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY, + f2 VARCHAR(5) NOT NULL UNIQUE); +INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1); +SELECT LAST_INSERT_ID(); +INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1); +SELECT LAST_INSERT_ID(); +DROP TABLE t1; diff --git a/sql/sql_class.h b/sql/sql_class.h index 995b5ac0bde..99803802001 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -356,13 +356,25 @@ public: inline uint32 get_open_count() { return open_count; } }; - +/* + The COPY_INFO structure is used by INSERT/REPLACE code. + The schema of the row counting by the INSERT/INSERT ... ON DUPLICATE KEY + UPDATE code: + If a row is inserted then the copied variable is incremented. + If a row is updated by the INSERT ... ON DUPLICATE KEY UPDATE and the + new data differs from the old one then the copied and the updated + variables are incremented. + The touched variable is incremented if a row was touched by the update part + of the INSERT ... ON DUPLICATE KEY UPDATE no matter whether the row + was actually changed or not. +*/ typedef struct st_copy_info { - ha_rows records; - ha_rows deleted; - ha_rows updated; - ha_rows copied; + ha_rows records; /* Number of processed records */ + ha_rows deleted; /* Number of deleted records */ + ha_rows updated; /* Number of updated records */ + ha_rows copied; /* Number of copied records */ ha_rows error_count; + ha_rows touched; /* Number of touched records */ enum enum_duplicates handle_duplicates; int escape_char, last_errno; bool ignore; diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index a5f6f08973d..629d1e68d4e 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -522,7 +522,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, /* Fill in the given fields and dump it to the table file */ - info.records= info.deleted= info.copied= info.updated= 0; + info.records= info.deleted= info.copied= info.updated= info.touched= 0; info.ignore= ignore; info.handle_duplicates=duplic; info.update_fields= &update_fields; @@ -767,8 +767,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, (!table->triggers || !table->triggers->has_delete_triggers())) table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE); - /* Reset value of LAST_INSERT_ID if no rows where inserted */ - if (!info.copied && thd->insert_id_used) + /* Reset value of LAST_INSERT_ID if no rows were inserted or touched */ + if (!info.copied && !info.touched && thd->insert_id_used) { thd->insert_id(0); id=0; @@ -1221,15 +1221,17 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) } goto err; } + + if (table->next_number_field) + table->file->adjust_next_insert_id_after_explicit_value( + table->next_number_field->val_int()); + info->touched++; + if ((table->file->table_flags() & HA_PARTIAL_COLUMN_READ) || compare_record(table, thd->query_id)) { info->updated++; - if (table->next_number_field) - table->file->adjust_next_insert_id_after_explicit_value( - table->next_number_field->val_int()); - trg_error= (table->triggers && table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER, |