MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:kgeorge Date:March 16 2007 8:36am
Subject:bk commit into 5.0 tree (gkodinov:1.2419) BUG#26261
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge 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-16 10:35:39+02:00, gkodinov@stripped +5 -0
  Bug #26261: 
   INSERT uses query_id to verify what fields are
   mentioned in the fields list of the INSERT command.
   However the check for that is made after the 
   ON DUPLICATE KEY is processed. This causes all
   the fields mentioned in ON DUPLICATE KEY to be 
   considered as mentioned in the fields list of 
   INSERT.
   Moved the check up, right after processing the
   fields list.

  mysql-test/r/insert_update.result@stripped, 2007-03-16 10:35:20+02:00, gkodinov@stripped +11 -0
    Bug #26261: test case

  mysql-test/t/insert_update.test@stripped, 2007-03-16 10:35:22+02:00, gkodinov@stripped +21 -0
    Bug #26261: test case

  sql/mysql_priv.h@stripped, 2007-03-16 10:35:23+02:00, gkodinov@stripped +2 -1
    Bug #26261: moved the check inside mysql_prepare_insert

  sql/sql_insert.cc@stripped, 2007-03-16 10:35:25+02:00, gkodinov@stripped +45 -23
    Bug #26261: move the check inside mysql_prepare_insert
    before setting up the ON DUPLICATE KEY part

  sql/sql_prepare.cc@stripped, 2007-03-16 10:35:25+02:00, gkodinov@stripped +1 -1
    Bug #26261: moved the check inside mysql_prepare_insert

# 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:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B26261-5.0-opt

--- 1.436/sql/mysql_priv.h	2007-02-21 22:18:15 +02:00
+++ 1.437/sql/mysql_priv.h	2007-03-16 10:35:23 +02:00
@@ -823,7 +823,8 @@ bool mysql_prepare_insert(THD *thd, TABL
                           List<Item> &fields, List_item *values,
                           List<Item> &update_fields,
                           List<Item> &update_values, enum_duplicates duplic,
-                          COND **where, bool select_insert);
+                          COND **where, bool select_insert,
+                          bool check_fields, bool abort_on_warning);
 bool mysql_insert(THD *thd,TABLE_LIST *table,List<Item> &fields,
                   List<List_item> &values, List<Item> &update_fields,
                   List<Item> &update_values, enum_duplicates flag,

--- 1.218/sql/sql_insert.cc	2007-02-19 14:47:12 +02:00
+++ 1.219/sql/sql_insert.cc	2007-03-16 10:35:25 +02:00
@@ -451,10 +451,15 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
   thd->proc_info="init";
   thd->used_tables=0;
   values= its++;
+  value_count= values->elements;
 
   if (mysql_prepare_insert(thd, table_list, table, fields, values,
 			   update_fields, update_values, duplic, &unused_conds,
-                           FALSE))
+                           FALSE,
+                           (fields.elements || !value_count),
+                           !ignore && (thd->variables.sql_mode &
+                                       (MODE_STRICT_TRANS_TABLES |
+                                        MODE_STRICT_ALL_TABLES))))
     goto abort;
 
   /* mysql_prepare_insert set table_list->table if it was not set */
@@ -480,7 +485,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
   table_list->next_local= 0;
   context->resolve_in_table_list_only(table_list);
 
-  value_count= values->elements;
   while ((values= its++))
   {
     counter++;
@@ -551,17 +555,9 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
     table->file->start_bulk_insert(values_list.elements);
 
   thd->no_trans_update= 0;
-  thd->abort_on_warning= (!ignore &&
-                          (thd->variables.sql_mode &
-                           (MODE_STRICT_TRANS_TABLES |
-                            MODE_STRICT_ALL_TABLES)));
-
-  if ((fields.elements || !value_count) &&
-      check_that_all_fields_are_given_values(thd, table, table_list))
-  {
-    /* thd->net.report_error is now set, which will abort the next loop */
-    error= 1;
-  }
+  thd->abort_on_warning= (!ignore && (thd->variables.sql_mode &
+                                       (MODE_STRICT_TRANS_TABLES |
+                                        MODE_STRICT_ALL_TABLES)));
 
   mark_fields_used_by_triggers_for_insert_stmt(thd, table, duplic);
 
@@ -934,6 +930,10 @@ static bool mysql_prepare_insert_check_t
 			be taken from table_list->table)    
     where		Where clause (for insert ... select)
     select_insert	TRUE if INSERT ... SELECT statement
+    check_fields        TRUE if need to check that all INSERT fields are 
+                        given values.
+    abort_on_warning    whether to report if some INSERT field is not 
+                        assigned as an error (TRUE) or as a warning (FALSE).
 
   TODO (in far future)
     In cases of:
@@ -954,7 +954,8 @@ bool mysql_prepare_insert(THD *thd, TABL
                           TABLE *table, List<Item> &fields, List_item *values,
                           List<Item> &update_fields, List<Item> &update_values,
                           enum_duplicates duplic,
-                          COND **where, bool select_insert)
+                          COND **where, bool select_insert,
+                          bool check_fields, bool abort_on_warning)
 {
   SELECT_LEX *select_lex= &thd->lex->select_lex;
   Name_resolution_context *context= &select_lex->context;
@@ -1017,10 +1018,22 @@ bool mysql_prepare_insert(THD *thd, TABL
     table_list->next_local= 0;
     context->resolve_in_table_list_only(table_list);
 
-    if (!(res= check_insert_fields(thd, context->table_list, fields, *values,
-                                 !insert_into_view, &map) ||
-          setup_fields(thd, 0, *values, 0, 0, 0)) 
-        && duplic == DUP_UPDATE)
+    res= check_insert_fields(thd, context->table_list, fields, *values,
+                             !insert_into_view, &map) ||
+      setup_fields(thd, 0, *values, 0, 0, 0);
+
+    if (!res && check_fields)
+    {
+      bool saved_abort_on_warning= thd->abort_on_warning;
+      thd->abort_on_warning= abort_on_warning;
+      res= check_that_all_fields_are_given_values(thd, 
+                                                  table ? table : 
+                                                  context->table_list->table,
+                                                  context->table_list);
+      thd->abort_on_warning= saved_abort_on_warning;
+    }
+
+    if (!res && duplic == DUP_UPDATE)
     {
       select_lex->no_wrap_view_item= TRUE;
       res= check_update_fields(thd, context->table_list, update_fields, &map);
@@ -2295,7 +2308,7 @@ bool mysql_insert_select_prepare(THD *th
                            lex->query_tables->table, lex->field_list, 0,
                            lex->update_list, lex->value_list,
                            lex->duplicates,
-                           &select_lex->where, TRUE))
+                           &select_lex->where, TRUE, FALSE, FALSE))
     DBUG_RETURN(TRUE);
 
   /*
@@ -2357,7 +2370,18 @@ select_insert::prepare(List<Item> &value
                            !insert_into_view, &map) ||
        setup_fields(thd, 0, values, 0, 0, 0);
 
-  if (info.handle_duplicates == DUP_UPDATE)
+  if (!res && fields->elements)
+  {
+    bool saved_abort_on_warning= thd->abort_on_warning;
+    thd->abort_on_warning= !info.ignore && (thd->variables.sql_mode &
+                                            (MODE_STRICT_TRANS_TABLES |
+                                             MODE_STRICT_ALL_TABLES));
+    res= check_that_all_fields_are_given_values(thd, table_list->table, 
+                                                table_list);
+    thd->abort_on_warning= saved_abort_on_warning;
+  }
+
+  if (info.handle_duplicates == DUP_UPDATE && !res)
   {
     Name_resolution_context *context= &lex->select_lex.context;
     Name_resolution_context_state ctx_state;
@@ -2459,9 +2483,7 @@ select_insert::prepare(List<Item> &value
                           (thd->variables.sql_mode &
                            (MODE_STRICT_TRANS_TABLES |
                             MODE_STRICT_ALL_TABLES)));
-  res= ((fields->elements &&
-         check_that_all_fields_are_given_values(thd, table, table_list)) ||
-        table_list->prepare_where(thd, 0, TRUE) ||
+  res= (table_list->prepare_where(thd, 0, TRUE) ||
         table_list->prepare_check_option(thd));
 
   if (!res)

--- 1.21/mysql-test/r/insert_update.result	2007-02-19 14:39:24 +02:00
+++ 1.22/mysql-test/r/insert_update.result	2007-03-16 10:35:20 +02:00
@@ -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;
+SET SQL_MODE = 'TRADITIONAL';
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL);
+INSERT INTO t1 (a) VALUES (1);
+ERROR HY000: Field 'b' doesn't have a default value
+INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b;
+ERROR HY000: Field 'b' doesn't have a default value
+INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b;
+ERROR HY000: Field 'b' doesn't have a default value
+SELECT * FROM t1;
+a	b
+DROP TABLE t1;

--- 1.21/mysql-test/t/insert_update.test	2007-02-19 14:39:25 +02:00
+++ 1.22/mysql-test/t/insert_update.test	2007-03-16 10:35:22 +02:00
@@ -162,3 +162,24 @@ 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 #26261: Missing default value isn't noticed in 
+#   insert ... on duplicate key update
+#
+SET SQL_MODE = 'TRADITIONAL';
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL);
+
+--error 1364
+INSERT INTO t1 (a) VALUES (1);
+
+--error 1364
+INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b;
+
+--error 1364
+INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b;
+
+SELECT * FROM t1;
+
+DROP TABLE t1;

--- 1.195/sql/sql_prepare.cc	2007-02-19 14:47:12 +02:00
+++ 1.196/sql/sql_prepare.cc	2007-03-16 10:35:25 +02:00
@@ -1058,7 +1058,7 @@ static bool mysql_test_insert(Prepared_s
 
     if (mysql_prepare_insert(thd, table_list, table_list->table,
                              fields, values, update_fields, update_values,
-                             duplic, &unused_conds, FALSE))
+                             duplic, &unused_conds, FALSE, FALSE, FALSE))
       goto error;
 
     value_count= values->elements;
Thread
bk commit into 5.0 tree (gkodinov:1.2419) BUG#26261kgeorge16 Mar