bk commit into 5.0 tree (dlenev:1.2322) BUG#24491

[dlenev]

Below is the list of changes that have just been committed into a local
5.0 repository of dlenev. When dlenev 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-01-23 15:03:48+03:00, dlenev@stripped +5 -0
  Proposed fix for bug#24491 "using alias from source table in insert ...
  on duplicate key".
  
  INSERT ... SELECT ... ON DUPLICATE KEY UPDATE which was used in
  stored routine or as prepared statement and which in its ON DUPLICATE
  KEY clause erroneously tried to assign value to a column mentioned only
  in its SELECT part was properly emitting error on the first execution
  but succeeded on the second and following executions.
  
  Code which is responsible for name resolution of fields mentioned in
  UPDATE clause (e.g. see select_insert::prepare()) modifies table list
  and Name_resolution_context used in this process. It uses
  Name_resolution_context_state::save_state/restore_state() to revert
  these modifications. Unfortunately those two methods failed to revert
  properly modifications to TABLE_LIST::next_name_resolution_table
  and this broke name resolution process for successive executions.
  
  This patch fixes Name_resolution_context_state::save_state/restore_state()
  in such way that it properly handles TABLE_LIST::next_name_resolution_table.

  mysql-test/r/ps.result@stripped, 2007-01-23 15:03:45+03:00, dlenev@stripped +16 -0
    Added test case for bug#24491 "using alias from source table in insert ...
    on duplicate key"

  mysql-test/r/sp-error.result@stripped, 2007-01-23 15:03:46+03:00, dlenev@stripped
+19 -0
    Added test case for bug#24491 "using alias from source table in insert ...
    on duplicate key"

  mysql-test/t/ps.test@stripped, 2007-01-23 15:03:46+03:00, dlenev@stripped +30 -0
    Added test case for bug#24491 "using alias from source table in insert ...
    on duplicate key"

  mysql-test/t/sp-error.test@stripped, 2007-01-23 15:03:46+03:00, dlenev@stripped +32
-0
    Added test case for bug#24491 "using alias from source table in insert ...
    on duplicate key"

  sql/item.h@stripped, 2007-01-23 15:03:46+03:00, dlenev@stripped +2 -7
    Name_resolution_context::save_state/restore_state():
      At the moment these methods are used only by code implementing
      INSERT and INSERT ... SELECT statements. This code doesn't modify
     'next_name_resolution_table' member of table list element
      corresponding to the first table of SELECT clause (pointed by
      'first_name_resolution_table'). But it modifies table list element
      corresponding to the target table of INSERT (pointed by 'table_list')
      So these methods were changed to reflect this.

# 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:	dlenev
# Host:	mockturtle.local
# Root:	/home/dlenev/src/mysql-5.0-bg24491

--- 1.212/sql/item.h	2007-01-23 15:03:53 +03:00
+++ 1.213/sql/item.h	2007-01-23 15:03:53 +03:00
@@ -337,23 +337,18 @@ public:
   {
     save_table_list=                  context->table_list;
     save_first_name_resolution_table= context->first_name_resolution_table;
-    save_next_name_resolution_table=  (context->first_name_resolution_table) ?
-                                      context->first_name_resolution_table->
-                                               next_name_resolution_table :
-                                      NULL;
     save_resolve_in_select_list=      context->resolve_in_select_list;
     save_next_local=                  table_list->next_local;
+    save_next_name_resolution_table=  table_list->next_name_resolution_table;
   }
 
   /* Restore a name resolution context from saved state. */
   void restore_state(Name_resolution_context *context, TABLE_LIST *table_list)
   {
     table_list->next_local=                save_next_local;
+    table_list->next_name_resolution_table= save_next_name_resolution_table;
     context->table_list=                   save_table_list;
     context->first_name_resolution_table=  save_first_name_resolution_table;
-    if (context->first_name_resolution_table)
-      context->first_name_resolution_table->
-               next_name_resolution_table= save_next_name_resolution_table;
     context->resolve_in_select_list=       save_resolve_in_select_list;
   }
 };

--- 1.109/mysql-test/r/sp-error.result	2007-01-23 15:03:53 +03:00
+++ 1.110/mysql-test/r/sp-error.result	2007-01-23 15:03:53 +03:00
@@ -1250,3 +1250,22 @@ ERROR HY000: View's SELECT contains a va
 PREPARE stmt FROM "CREATE VIEW v AS SELECT ?";
 ERROR HY000: View's SELECT contains a variable or parameter
 DROP TABLE t1;
+drop tables if exists t1;
+drop procedure if exists bug24491;
+create table t1 (id int primary key auto_increment, value varchar(10));
+insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');
+create procedure bug24491()
+insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate
key update v = 'DUP';
+call bug24491();
+ERROR 42S22: Unknown column 'v' in 'field list'
+call bug24491();
+ERROR 42S22: Unknown column 'v' in 'field list'
+drop procedure bug24491;
+create procedure bug24491()
+insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on
duplicate key update y.value = 'DUP';
+call bug24491();
+ERROR 42S22: Unknown column 'y.value' in 'field list'
+call bug24491();
+ERROR 42S22: Unknown column 'y.value' in 'field list'
+drop procedure bug24491;
+drop tables t1;

--- 1.110/mysql-test/t/sp-error.test	2007-01-23 15:03:53 +03:00
+++ 1.111/mysql-test/t/sp-error.test	2007-01-23 15:03:53 +03:00
@@ -1809,6 +1809,38 @@ DROP TABLE t1;
 
 
 #
+# BUG#24491 "using alias from source table in insert ... on duplicate key"
+#
+--disable_warnings
+drop tables if exists t1;
+drop procedure if exists bug24491;
+--enable_warnings
+create table t1 (id int primary key auto_increment, value varchar(10));
+insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');
+# Let us create routine with INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 
+# statement which in its ON DUPLICATE KEY clause erroneously tries to assign
+# value to a column which is mentioned only in SELECT part.
+create procedure bug24491()
+  insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on
duplicate key update v = 'DUP';
+# Both first and second calls to it should fail
+--error ER_BAD_FIELD_ERROR 
+call bug24491();
+--error ER_BAD_FIELD_ERROR
+call bug24491();
+drop procedure bug24491;
+# And now the same test for more complex case which is more close
+# to the one that was reported originally.
+create procedure bug24491()
+  insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on
duplicate key update y.value = 'DUP';
+--error ER_BAD_FIELD_ERROR 
+call bug24491();
+--error ER_BAD_FIELD_ERROR
+call bug24491();
+drop procedure bug24491;
+drop tables t1;
+
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings

--- 1.82/mysql-test/r/ps.result	2007-01-23 15:03:53 +03:00
+++ 1.83/mysql-test/r/ps.result	2007-01-23 15:03:53 +03:00
@@ -1496,4 +1496,20 @@ Variable_name	Value
 Slow_queries	1
 deallocate prepare no_index;
 deallocate prepare sq;
+drop tables if exists t1;
+create table t1 (id int primary key auto_increment, value varchar(10));
+insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');
+prepare stmt from "insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as
v) as y on duplicate key update v = 'DUP'";
+execute stmt;
+ERROR 42S22: Unknown column 'v' in 'field list'
+execute stmt;
+ERROR 42S22: Unknown column 'v' in 'field list'
+deallocate prepare stmt;
+prepare stmt from "insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as
value) as y on duplicate key update y.value = 'DUP'";
+execute stmt;
+ERROR 42S22: Unknown column 'y.value' in 'field list'
+execute stmt;
+ERROR 42S22: Unknown column 'y.value' in 'field list'
+deallocate prepare stmt;
+drop tables t1;
 End of 5.0 tests.

--- 1.79/mysql-test/t/ps.test	2007-01-23 15:03:53 +03:00
+++ 1.80/mysql-test/t/ps.test	2007-01-23 15:03:53 +03:00
@@ -1540,4 +1540,34 @@ execute sq;
 deallocate prepare no_index;
 deallocate prepare sq;
 
+
+#
+# BUG#24491 "using alias from source table in insert ... on duplicate key"
+#
+--disable_warnings
+drop tables if exists t1;
+--enable_warnings
+create table t1 (id int primary key auto_increment, value varchar(10));
+insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');
+# Let us prepare INSERT ... SELECT ... ON DUPLICATE KEY UPDATE statement
+# which in its ON DUPLICATE KEY clause erroneously tries to assign value
+# to a column which is mentioned only in SELECT part.
+prepare stmt from "insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as
v) as y on duplicate key update v = 'DUP'";
+# Both first and second attempts to execute it should fail
+--error ER_BAD_FIELD_ERROR 
+execute stmt;
+--error ER_BAD_FIELD_ERROR
+execute stmt;
+deallocate prepare stmt;
+# And now the same test for more complex case which is more close
+# to the one that was reported originally.
+prepare stmt from "insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as
value) as y on duplicate key update y.value = 'DUP'";
+--error ER_BAD_FIELD_ERROR 
+execute stmt;
+--error ER_BAD_FIELD_ERROR
+execute stmt;
+deallocate prepare stmt;
+drop tables t1;
+
+
 --echo End of 5.0 tests.