bk commit into 5.0 tree (gkodinov:1.2387) BUG#25831

[kgeorge]

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-02-01 19:13:27+02:00, gkodinov@stripped +10 -0
  Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
   Several problems fixed: 
    1. There was a "catch-all" context initialization in setup_tables()
      that was causing the table that we insert into to be visible in the 
      SELECT part of an INSERT .. SELECT .. statement with no tables in
      its FROM clause.
      Fixed by removing the "catch-all" statement and initializing the 
      context in the parser.
    2. Incomplete name resolution context when resolving the right-hand
      values in the ON DUPLICATE KEY UPDATE ... part of an INSERT ... SELECT ...
      caused columns from NATURAL JOIN/JOIN USING table references in the
      FROM clause of the select to be unavailable.
      Fixed by establishing a proper name resolution context.
    3. When setting up the special name resolution context for problem 2
      there was no check for cases where an aggregate function without a
      GROUP BY effectively takes the column from the SELECT part of an 
      INSERT ... SELECT unavailable for ON DUPLICATE KEY UPDATE.
      Fixed by checking for that condition when setting up the name 
      resolution context.

  mysql-test/r/fulltext_order_by.result@stripped, 2007-02-01 19:13:10+02:00,
gkodinov@stripped +2 -2
    Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
     - fixed error codes

  mysql-test/r/insert_update.result@stripped, 2007-02-01 19:13:11+02:00, gkodinov@stripped
+17 -0
    Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
     - test case

  mysql-test/r/union.result@stripped, 2007-02-01 19:13:12+02:00, gkodinov@stripped +1 -1
    Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
     - fixed error codes

  mysql-test/t/fulltext_order_by.test@stripped, 2007-02-01 19:13:13+02:00,
gkodinov@stripped +2 -2
    Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
     - fixed error codes

  mysql-test/t/insert_update.test@stripped, 2007-02-01 19:13:14+02:00, gkodinov@stripped
+23 -0
    Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
     - test case

  mysql-test/t/union.test@stripped, 2007-02-01 19:13:14+02:00, gkodinov@stripped +1 -1
    Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
     - fixed error codes

  sql/item.h@stripped, 2007-02-01 19:13:15+02:00, gkodinov@stripped +1 -1
    Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
     - save_next_local is not referenced any more outside class methods

  sql/sql_base.cc@stripped, 2007-02-01 19:13:16+02:00, gkodinov@stripped +0 -15
    Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
     - removed a "catch-all" code to cater for correct context initialization

  sql/sql_insert.cc@stripped, 2007-02-01 19:13:17+02:00, gkodinov@stripped +14 -15
    Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
     - Fixed the context of resolving the values in INSERT SELECT ON UPDATE

  sql/sql_yacc.yy@stripped, 2007-02-01 19:13:18+02:00, gkodinov@stripped +6 -1
    Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
     - Set the context here instead of setup_tables()

# 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/B25831-5.0-opt

--- 1.217/sql/item.h	2007-01-12 23:43:23 +02:00
+++ 1.218/sql/item.h	2007-02-01 19:13:15 +02:00
@@ -325,10 +325,10 @@ private:
   TABLE_LIST *save_first_name_resolution_table;
   TABLE_LIST *save_next_name_resolution_table;
   bool        save_resolve_in_select_list;
+  TABLE_LIST *save_next_local;
 
 public:
   Name_resolution_context_state() {}          /* Remove gcc warning */
-  TABLE_LIST *save_next_local;
 
 public:
   /* Save the state of a name resolution context. */

--- 1.364/sql/sql_base.cc	2007-01-11 22:20:26 +02:00
+++ 1.365/sql/sql_base.cc	2007-02-01 19:13:16 +02:00
@@ -4499,21 +4499,6 @@ bool setup_tables(THD *thd, Name_resolut
   DBUG_ENTER("setup_tables");
 
   /*
-    Due to the various call paths that lead to setup_tables() it may happen
-    that context->table_list and context->first_name_resolution_table can be
-    NULL (this is typically done when creating TABLE_LISTs internally).
-    TODO:
-    Investigate all cases when this my happen, initialize the name resolution
-    context correctly in all those places, and remove the context reset below.
-  */
-  if (!context->table_list || !context->first_name_resolution_table)
-  {
-    /* Test whether the context is in a consistent state. */
-    DBUG_ASSERT(!context->first_name_resolution_table &&
!context->table_list);
-    context->table_list= context->first_name_resolution_table= tables;
-  }
-
-  /*
     this is used for INSERT ... SELECT.
     For select we setup tables except first (and its underlying tables)
   */

--- 1.214/sql/sql_insert.cc	2007-01-23 12:04:15 +02:00
+++ 1.215/sql/sql_insert.cc	2007-02-01 19:13:17 +02:00
@@ -966,6 +966,7 @@ bool mysql_prepare_insert(THD *thd, TABL
   DBUG_PRINT("enter", ("table_list 0x%lx, table 0x%lx, view %d",
 		       (ulong)table_list, (ulong)table,
 		       (int)insert_into_view));
+  DBUG_ASSERT (!select_insert || !values);
 
   /*
     For subqueries in VALUES() we should not see the table in which we are
@@ -1022,19 +1023,16 @@ bool mysql_prepare_insert(THD *thd, TABL
       When we are not using GROUP BY we can refer to other tables in the
       ON DUPLICATE KEY part.
     */       
-    if (select_lex->group_list.elements == 0)
-    {
-      context->table_list->next_local=       ctx_state.save_next_local;
-      /* first_name_resolution_table was set by resolve_in_table_list_only() */
-      context->first_name_resolution_table->
-        next_name_resolution_table=          ctx_state.save_next_local;
-    }
+    DBUG_ASSERT (!select_lex->group_list.elements);
+    ctx_state.restore_state(context, table_list);
     if (!res)
       res= setup_fields(thd, 0, update_values, 1, 0, 0);
   }
-
-  /* Restore the current context. */
-  ctx_state.restore_state(context, table_list);
+  else
+  {
+    /* Restore the current context. */
+    ctx_state.restore_state(context, table_list);
+  }
 
   if (res)
     DBUG_RETURN(res);
@@ -2358,6 +2356,7 @@ select_insert::prepare(List<Item> &value
     /* Save the state of the current name resolution context. */
     Name_resolution_context *context= &lex->select_lex.context;
     Name_resolution_context_state ctx_state;
+    TABLE_LIST *first_select_table= context->first_name_resolution_table;
 
     /* Save the state of the current name resolution context. */
     ctx_state.save_state(context, table_list);
@@ -2374,17 +2373,17 @@ select_insert::prepare(List<Item> &value
       When we are not using GROUP BY we can refer to other tables in the
       ON DUPLICATE KEY part
     */       
-    if (lex->select_lex.group_list.elements == 0)
+    DBUG_ASSERT (!table_list->next_name_resolution_table);
+    if (lex->select_lex.group_list.elements == 0 &&
+        !lex->select_lex.with_sum_func)
     {
-      context->table_list->next_local=       ctx_state.save_next_local;
-      /* first_name_resolution_table was set by resolve_in_table_list_only() */
-      context->first_name_resolution_table->
-        next_name_resolution_table=          ctx_state.save_next_local;
+      table_list->next_name_resolution_table= first_select_table;
     }
     res= res || setup_fields(thd, 0, *info.update_values, 1, 0, 0);
 
     /* Restore the current context. */
     ctx_state.restore_state(context, table_list);
+    table_list->next_name_resolution_table= NULL;
   }
 
   lex->current_select= lex_current_select_save;

--- 1.501/sql/sql_yacc.yy	2007-01-15 12:06:13 +02:00
+++ 1.502/sql/sql_yacc.yy	2007-02-01 19:13:18 +02:00
@@ -4188,8 +4188,13 @@ select_into:
 	| select_from into;
 
 select_from:
-	  FROM join_table_list where_clause group_clause having_clause
+        FROM join_table_list where_clause group_clause having_clause
 	       opt_order_clause opt_limit_clause procedure_clause
+          {
+            Select->context.table_list=
+              Select->context.first_name_resolution_table= 
+                (TABLE_LIST *) Select->table_list.first;
+          }
         | FROM DUAL_SYM where_clause opt_limit_clause
           /* oracle compatibility: oracle always requires FROM clause,
              and DUAL is system table without fields.

--- 1.87/mysql-test/r/union.result	2006-10-13 02:10:30 +03:00
+++ 1.88/mysql-test/r/union.result	2007-02-01 19:13:12 +02:00
@@ -430,7 +430,7 @@ drop temporary table t1;
 create table t1 select a from t1 union select a from t2;
 ERROR HY000: You can't specify target table 't1' for update in FROM clause
 select a from t1 union select a from t2 order by t2.a;
-ERROR 42S22: Unknown column 't2.a' in 'order clause'
+ERROR 42S02: Unknown table 't2' in order clause
 drop table t1,t2;
 select length(version()) > 1 as `*` UNION select 2;
 *

--- 1.97/mysql-test/t/union.test	2006-08-03 10:30:57 +03:00
+++ 1.98/mysql-test/t/union.test	2007-02-01 19:13:14 +02:00
@@ -255,7 +255,7 @@ create temporary table t1 select a from 
 drop temporary table t1;
 --error 1093
 create table t1 select a from t1 union select a from t2;
---error 1054
+--error ER_UNKNOWN_TABLE
 select a from t1 union select a from t2 order by t2.a;
 drop table t1,t2;
 

--- 1.20/mysql-test/r/fulltext_order_by.result	2005-08-12 18:04:47 +03:00
+++ 1.21/mysql-test/r/fulltext_order_by.result	2007-02-01 19:13:10 +02:00
@@ -126,7 +126,7 @@ group by 
 a.text, b.id, b.betreff
 order by 
 match(b.betreff) against ('+abc' in boolean mode) desc;
-ERROR 42S22: Unknown column 'b.betreff' in 'order clause'
+ERROR 42S02: Unknown table 'b' in order clause
 select a.text, b.id, b.betreff
 from 
 t2 a inner join t3 b on a.id = b.forum inner join
@@ -142,7 +142,7 @@ where 
 match(c.beitrag) against ('+abc' in boolean mode)
 order by 
 match(b.betreff) against ('+abc' in boolean mode) desc;
-ERROR 42S22: Unknown column 'b.betreff' in 'order clause'
+ERROR 42S02: Unknown table 'b' in order clause
 select a.text, b.id, b.betreff
 from 
 t2 a inner join t3 b on a.id = b.forum inner join

--- 1.20/mysql-test/r/insert_update.result	2006-09-15 13:02:56 +03:00
+++ 1.21/mysql-test/r/insert_update.result	2007-02-01 19:13:11 +02:00
@@ -219,3 +219,20 @@ SELECT * FROM t1;
 a	b
 45	2
 DROP TABLE t1;
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+INSERT INTO t1 SELECT 1, j;
+ERROR 42S22: Unknown column 'j' in 'field list'
+DROP TABLE t1;
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT, b INT);
+CREATE TABLE t3 (a INT, c INT);
+INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3 
+ON DUPLICATE KEY UPDATE j= a;
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1, 1);
+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;

--- 1.22/mysql-test/t/fulltext_order_by.test	2005-08-12 18:04:48 +03:00
+++ 1.23/mysql-test/t/fulltext_order_by.test	2007-02-01 19:13:13 +02:00
@@ -80,7 +80,7 @@ CREATE TABLE t3 (
   FULLTEXT KEY betreff (betreff)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;
 
---error 1054
+--error ER_UNKNOWN_TABLE
 select a.text, b.id, b.betreff
 from 
   t2 a inner join t3 b on a.id = b.forum inner join
@@ -100,7 +100,7 @@ group by 
 order by 
   match(b.betreff) against ('+abc' in boolean mode) desc;
   
---error 1054
+--error ER_UNKNOWN_TABLE
 select a.text, b.id, b.betreff
 from 
   t2 a inner join t3 b on a.id = b.forum inner join

--- 1.20/mysql-test/t/insert_update.test	2006-09-15 12:54:09 +03:00
+++ 1.21/mysql-test/t/insert_update.test	2007-02-01 19:13:14 +02:00
@@ -139,3 +139,26 @@ INSERT INTO t1 VALUES (45, 1) ON DUPLICA
 SELECT * FROM t1;
 
 DROP TABLE t1;
+
+#
+# Bug#25831: Deficiencies in INSERT ... SELECT ... field name resolving.
+#
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+--error ER_BAD_FIELD_ERROR
+INSERT INTO t1 SELECT 1, j;
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT, b INT);
+CREATE TABLE t3 (a INT, c INT);
+INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3 
+  ON DUPLICATE KEY UPDATE j= a;
+DROP TABLE t1,t2,t3;
+
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1, 1);
+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;