bk commit into 5.0 tree (gshchepa:1.2482) BUG#28000

[<gshchepa]

Below is the list of changes that have just been committed into a local
5.0 repository of uchum. When uchum 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-05-11 01:17:54+05:00, gshchepa@stripped +3 -0
  Fixed bug #28000.
  Bug occurs in INSERT IGNORE ... SELECT ... ON DUPLICATE KEY UPDATE
  sentences, when SELECT returns duplicated values and UPDATE clause
  tries to assign NULL values to NOT NULL fields.
  NOTE: By current design MySQL server treats INSERT IGNORE ... ON
  DUPLICATE sentences as INSERT ... ON DUPLICATE (without IGNORE
  clause), but MySQL manual lacks of this information. After
  discussion with Serg we are agreed to legalize this behaviour.
  
  write_record() function was returning error values even within
  INSERT IGNORE, because ignore_errors parameter of
  fill_record_n_invoke_before_triggers() function call was
  always set to FALSE. FALSE is replaced by info->ignore.

  mysql-test/r/insert_update.result@stripped, 2007-05-11 00:25:19+05:00, gshchepa@stripped +35
-0
    Added test case for bug #28000.

  mysql-test/t/insert_update.test@stripped, 2007-05-11 00:25:37+05:00, gshchepa@stripped +26
-0
    Added test case for bug #28000.

  sql/sql_insert.cc@stripped, 2007-05-11 00:23:27+05:00, gshchepa@stripped +2 -1
    Fixed bug #28000:
    write_record() function was returning error values even within
    INSERT IGNORE, because ignore_errors parameter of
    fill_record_n_invoke_before_triggers() function call was
    always set to FALSE. FALSE is replaced by info->ignore.

# 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:	gshchepa
# Host:	gleb.loc
# Root:	/home/uchum/work/bk/mysql-5.0-opt-28000

--- 1.230/sql/sql_insert.cc	2007-04-12 14:46:07 +05:00
+++ 1.231/sql/sql_insert.cc	2007-05-11 00:23:27 +05:00
@@ -1258,7 +1258,8 @@ int write_record(THD *thd, TABLE *table,
         DBUG_ASSERT(info->update_fields->elements ==
                     info->update_values->elements);
         if (fill_record_n_invoke_before_triggers(thd, *info->update_fields,
-                                                 *info->update_values, 0,
+                                                 *info->update_values,
+                                                 info->ignore,
                                                  table->triggers,
                                                  TRG_EVENT_UPDATE))
           goto before_trg_err;

--- 1.25/mysql-test/r/insert_update.result	2007-05-08 00:20:42 +05:00
+++ 1.26/mysql-test/r/insert_update.result	2007-05-11 00:25:19 +05:00
@@ -358,3 +358,38 @@ id	c1	cnt
 5	Y	1
 6	Z	1
 DROP TABLE t1;
+CREATE TABLE t1 (
+id INT AUTO_INCREMENT PRIMARY KEY,
+c1 INT NOT NULL,
+cnt INT DEFAULT 1
+);
+INSERT INTO t1 (id,c1) VALUES (1,10);
+SELECT * FROM t1;
+id	c1	cnt
+1	10	1
+CREATE TABLE t2 (id INT, c1 INT);
+INSERT INTO t2 VALUES (1,NULL), (2,2);
+INSERT INTO t1 (id,c1) SELECT 1,NULL
+ON DUPLICATE KEY UPDATE c1=NULL;
+ERROR 23000: Column 'c1' cannot be null
+SELECT * FROM t1;
+id	c1	cnt
+1	10	1
+INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL
+ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+Warnings:
+Warning	1263	Column was set to data type implicit default; NULL supplied for NOT NULL
column 'c1' at row 1
+Error	1048	Column 'c1' cannot be null
+SELECT * FROM t1;
+id	c1	cnt
+1	0	2
+INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2
+ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+Warnings:
+Warning	1263	Column was set to data type implicit default; NULL supplied for NOT NULL
column 'c1' at row 1
+Error	1048	Column 'c1' cannot be null
+SELECT * FROM t1;
+id	c1	cnt
+1	0	3
+2	2	1
+DROP TABLE t1;

--- 1.25/mysql-test/t/insert_update.test	2007-05-08 00:20:34 +05:00
+++ 1.26/mysql-test/t/insert_update.test	2007-05-11 00:25:37 +05:00
@@ -264,3 +264,29 @@ INSERT  INTO t1 (c1) VALUES ('A'), ('X')
   ON DUPLICATE KEY UPDATE cnt=cnt+1;
 SELECT * FROM t1;
 DROP TABLE t1;
+
+#
+# Bug#28000: INSERT IGNORE ... SELECT ... ON DUPLICATE
+# with erroneous UPDATE: NOT NULL field with NULL value.
+#
+CREATE TABLE t1 (
+  id INT AUTO_INCREMENT PRIMARY KEY,
+  c1 INT NOT NULL,
+  cnt INT DEFAULT 1
+);
+INSERT INTO t1 (id,c1) VALUES (1,10);
+SELECT * FROM t1;
+CREATE TABLE t2 (id INT, c1 INT);
+INSERT INTO t2 VALUES (1,NULL), (2,2);
+--error 1048
+INSERT INTO t1 (id,c1) SELECT 1,NULL
+  ON DUPLICATE KEY UPDATE c1=NULL;
+SELECT * FROM t1;
+INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL
+  ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+SELECT * FROM t1;
+INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2
+  ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+SELECT * FROM t1;
+
+DROP TABLE t1;