bk commit into 5.0 tree (bell:1.1960) BUG#14342

[sanja]

Below is the list of changes that have just been committed into a local
5.0 repository of bell. When bell 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
  1.1960 05/11/01 23:00:55 bell@stripped +9 -0
  Setting of correct mem_root for subquery execution (BUG#14342)
  debug output added

  sql/sql_union.cc
    1.131 05/11/01 23:00:49 bell@stripped +6 -1
    layout fixed
    debug output added

  sql/sql_select.cc
    1.370 05/11/01 23:00:49 bell@stripped +9 -2
    JOIN processing debug output added

  sql/sql_class.h
    1.272 05/11/01 23:00:49 bell@stripped +8 -0
    statement execution lifetime mem_root pointer

  sql/sql_class.cc
    1.218 05/11/01 23:00:49 bell@stripped +1 -0
    statement execution lifetime mem_root pointer initialization

  sql/sp_head.cc
    1.194 05/11/01 23:00:49 bell@stripped +4 -0
    set and reset statement execution lifetime mem_root pointer

  sql/opt_range.cc
    1.194 05/11/01 23:00:49 bell@stripped +16 -3
    mem_root tracking debug output added

  sql/item_subselect.cc
    1.121 05/11/01 23:00:49 bell@stripped +8 -0
    correct mem_root set before subquery execution

  mysql-test/t/subselect_innodb.test
    1.15 05/11/01 23:00:49 bell@stripped +54 -0
    BUG#14342 test case

  mysql-test/r/subselect_innodb.result
    1.12 05/11/01 23:00:49 bell@stripped +72 -0
    BUG#14342 test case

# 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:	bell
# Host:	sanja.is.com.ua
# Root:	/home/bell/mysql/bk/work-bug6-5.0

--- 1.193/sql/opt_range.cc	2005-10-19 11:41:23 +03:00
+++ 1.194/sql/opt_range.cc	2005-11-01 23:00:49 +02:00
@@ -720,6 +720,7 @@
                                        bool no_alloc, MEM_ROOT *parent_alloc)
   :dont_free(0),error(0),free_file(0),in_range(0),cur_range(NULL),range(0)
 {
+  DBUG_ENTER("QUICK_RANGE_SELECT::QUICK_RANGE_SELECT");
   sorted= 0;
   index= key_nr;
   head=  table;
@@ -735,14 +736,17 @@
 
   if (!no_alloc && !parent_alloc)
   {
+    DBUG_PRINT("info", ("was mem root: 0x%lx", (ulong)thd->mem_root));
     // Allocates everything through the internal memroot
     init_sql_alloc(&alloc, thd->variables.range_alloc_block_size, 0);
     thd->mem_root= &alloc;
+    DBUG_PRINT("info", ("set mem root: 0x%lx", (ulong)&alloc));
   }
   else
     bzero((char*) &alloc,sizeof(alloc));
   file= head->file;
   record= head->record[0];
+  DBUG_VOID_RETURN;
 }
 
 
@@ -1067,12 +1071,16 @@
                                                TABLE *table)
   : thd(thd_param), scans_inited(FALSE)
 {
+  DBUG_ENTER("QUICK_ROR_UNION_SELECT::QUICK_ROR_UNION_SELECT");
   index= MAX_KEY;
   head= table;
   rowid_length= table->file->ref_length;
   record= head->record[0];
+  DBUG_PRINT("info", ("was mem root: 0x%lx", (ulong)thd->mem_root));
   init_sql_alloc(&alloc, thd->variables.range_alloc_block_size, 0);
   thd_param->mem_root= &alloc;
+  DBUG_PRINT("info", ("set mem root: 0x%lx", (ulong)&alloc));
+  DBUG_VOID_RETURN;
 }
 
 
@@ -1768,6 +1776,7 @@
     param.table=head;
     param.keys=0;
     param.mem_root= &alloc;
+    DBUG_PRINT("info", ("stored old mem root: 0x%lx", (ulong) thd->mem_root));
     param.old_root= thd->mem_root;
     param.needed_reg= &needed_reg;
     param.imerge_cost_buff_size= 0;
@@ -1785,6 +1794,7 @@
     }
     key_parts= param.key_parts;
     thd->mem_root= &alloc;
+    DBUG_PRINT("info", ("set mem root: 0x%lx", (ulong)&alloc));
 
     /*
       Make an array with description of all key parts of all table keys.
@@ -1934,6 +1944,7 @@
     }
 
     thd->mem_root= param.old_root;
+    DBUG_PRINT("info", ("reset mem root: 0x%lx", thd->mem_root));
 
     /* If we got a read plan, create a quick select from it. */
     if (best_trp)
@@ -1949,6 +1960,7 @@
   free_mem:
     free_root(&alloc,MYF(0));			// Return memory & allocator
     thd->mem_root= param.old_root;
+    DBUG_PRINT("info", ("reset mem root: 0x%lx", thd->mem_root));
     thd->no_errors=0;
   }
 
@@ -5748,6 +5760,7 @@
 QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table,
                                              TABLE_REF *ref, ha_rows records)
 {
+  DBUG_ENTER("get_quick_select_for_ref");
   MEM_ROOT *old_root= thd->mem_root;
   /* The following call may change thd->mem_root */
   QUICK_RANGE_SELECT *quick= new QUICK_RANGE_SELECT(thd, table, ref->key, 0);
@@ -5757,7 +5770,7 @@
   uint part;
 
   if (!quick)
-    return 0;			/* no ranges found */
+    DBUG_RETURN(0);			/* no ranges found */
   if (quick->init())
   {
     delete quick;
@@ -5811,12 +5824,12 @@
   }
 
   thd->mem_root= old_root;
-  return quick;
+  DBUG_RETURN(quick);
 
 err:
   thd->mem_root= old_root;
   delete quick;
-  return 0;
+  DBUG_RETURN(0);
 }
 
 

--- 1.217/sql/sql_class.cc	2005-10-25 12:02:41 +03:00
+++ 1.218/sql/sql_class.cc	2005-11-01 23:00:49 +02:00
@@ -305,6 +305,7 @@
   total_warn_count= 0;
   update_charset();
   bzero((char *) &status_var, sizeof(status_var));
+  execution_mem_root= &main_mem_root;
 }
 
 

--- 1.271/sql/sql_class.h	2005-10-25 12:02:41 +03:00
+++ 1.272/sql/sql_class.h	2005-11-01 23:00:49 +02:00
@@ -1368,6 +1368,14 @@
   sp_rcontext *spcont;		// SP runtime context
   sp_cache   *sp_proc_cache;
   sp_cache   *sp_func_cache;
+  /*
+    It is a pointer to the mem_root with life time of statement execution,
+    for all queries except SP internal queries it points to
+    THD::main_mem_root (stetement execution means query execution in this
+    case), for execution of SP internals it is set to mem_root which frees
+    after every statement of SP execution (see sp_head::execute()).
+  */
+  MEM_ROOT *execution_mem_root;
 
   /*
     If we do a purge of binary logs, log index info of the threads

--- 1.369/sql/sql_select.cc	2005-10-29 01:07:36 +03:00
+++ 1.370/sql/sql_select.cc	2005-11-01 23:00:49 +02:00
@@ -308,6 +308,7 @@
 	      SELECT_LEX_UNIT *unit_arg)
 {
   DBUG_ENTER("JOIN::prepare");
+  DBUG_PRINT("enter", ("join: 0x%lx", (ulong) this));
 
   // to prevent double initialization on EXPLAIN
   if (optimized)
@@ -532,6 +533,7 @@
 JOIN::optimize()
 {
   DBUG_ENTER("JOIN::optimize");
+  DBUG_PRINT("enter", ("join: 0x%lx", (ulong) this));
   // to prevent double initialization on EXPLAIN
   if (optimized)
     DBUG_RETURN(0);
@@ -1119,6 +1121,7 @@
 JOIN::reinit()
 {
   DBUG_ENTER("JOIN::reinit");
+  DBUG_PRINT("enter", ("join: 0x%lx", (ulong) this));
   first_record= 0;
 
   if (exec_tmp_table1)
@@ -1178,6 +1181,7 @@
   List<Item> *columns_list= &fields_list;
   int      tmp_error;
   DBUG_ENTER("JOIN::exec");
+  DBUG_PRINT("enter", ("join: 0x%lx", (ulong) this));
 
   error= 0;
   if (procedure)
@@ -1690,6 +1694,7 @@
 JOIN::destroy()
 {
   DBUG_ENTER("JOIN::destroy");
+  DBUG_PRINT("enter", ("join: 0x%lx", (ulong) this));
   select_lex->join= 0;
 
   if (tmp_join)
@@ -5780,6 +5785,7 @@
   bool full= (!select_lex->uncacheable && !thd->lex->describe);
   bool can_unlock= full;
   DBUG_ENTER("JOIN::join_free");
+  DBUG_PRINT("enter", ("join: 0x%lx", (ulong) this));
 
   cleanup(full);
 
@@ -5797,8 +5803,7 @@
         ... HAVING FALSE OR a IN (SELECT ...))
         but all table cursors must be closed before the unlock.
       */
-      sl->cleanup_all_joins(full_local);
-      /* Can't unlock if at least one JOIN is still needed */
+      sl->cleanup_all_joins(full_local); /* Can't unlock if at least one JOIN is still
needed */
       can_unlock= can_unlock && full_local;
     }
 
@@ -5839,6 +5844,7 @@
 void JOIN::cleanup(bool full)
 {
   DBUG_ENTER("JOIN::cleanup");
+  DBUG_PRINT("enter", ("join: 0x%lx, full: %d", (ulong) this, (int) full));
 
   if (table)
   {
@@ -11101,6 +11107,7 @@
   SQL_SELECT *select;
   JOIN_TAB *tab;
   DBUG_ENTER("create_sort_index");
+  DBUG_PRINT("enter", ("join 0x%lx", (ulong) join));
 
   if (join->tables == join->const_tables)
     DBUG_RETURN(0);				// One row, no need to sort

--- 1.130/sql/sql_union.cc	2005-10-13 10:52:53 +03:00
+++ 1.131/sql/sql_union.cc	2005-11-01 23:00:49 +02:00
@@ -703,7 +703,7 @@
 bool st_select_lex::cleanup()
 {
   bool error= FALSE;
-  DBUG_ENTER("st_select_lex::cleanup()");
+  DBUG_ENTER("st_select_lex::cleanup");
 
   if (join)
   {
@@ -725,12 +725,17 @@
 {
   SELECT_LEX_UNIT *unit;
   SELECT_LEX *sl;
+  DBUG_ENTER("st_select_lex::cleanup_all_joins");
+  DBUG_PRINT("enter", ("select: 0x%lx, full: %d", (ulong)this, (int)full));
 
   if (join)
     join->cleanup(full);
 
   for (unit= first_inner_unit(); unit; unit= unit->next_unit())
+  {
     for (sl= unit->first_select(); sl; sl= sl->next_select())
       sl->cleanup_all_joins(full);
+  }
+  DBUG_VOID_RETURN;
 }
 

--- 1.11/mysql-test/r/subselect_innodb.result	2005-10-13 10:52:52 +03:00
+++ 1.12/mysql-test/r/subselect_innodb.result	2005-11-01 23:00:49 +02:00
@@ -172,3 +172,75 @@
 countrycount	smcnt	country	total_funds
 1	1200	USA	1200
 drop table t1;
+CREATE TABLE `t1` (
+`t3_id` int NOT NULL,
+`t1_id` int NOT NULL,
+PRIMARY KEY  (`t1_id`)
+);
+CREATE TABLE `t2` (
+`t2_id` int NOT NULL,
+`t1_id` int NOT NULL,
+`b` int NOT NULL,
+PRIMARY KEY  (`t2_id`),
+UNIQUE KEY `idx_t2_t1_b` (`t1_id`,`b`)
+) ENGINE=InnoDB;
+CREATE TABLE `t3` (
+`t3_id` int NOT NULL
+);
+INSERT INTO `t3` VALUES (3);
+select
+(SELECT rs.t2_id
+FROM t2 rs
+WHERE rs.t1_id=
+(SELECT lt.t1_id
+FROM t1 lt
+WHERE lt.t3_id=a.t3_id)
+ORDER BY b DESC LIMIT 1)
+from t3 AS a;
+(SELECT rs.t2_id
+FROM t2 rs
+WHERE rs.t1_id=
+(SELECT lt.t1_id
+FROM t1 lt
+WHERE lt.t3_id=a.t3_id)
+ORDER BY b DESC LIMIT 1)
+NULL
+DROP PROCEDURE IF EXISTS p1;
+create procedure p1()
+begin
+declare done int default 3;
+repeat
+select
+(SELECT rs.t2_id
+FROM t2 rs
+WHERE rs.t1_id=
+(SELECT lt.t1_id
+FROM t1 lt
+WHERE lt.t3_id=a.t3_id)
+ORDER BY b DESC LIMIT 1) as x
+from t3 AS a;
+set done= done-1;
+until done <= 0 end repeat;
+end//
+call p1();
+x
+NULL
+x
+NULL
+x
+NULL
+call p1();
+x
+NULL
+x
+NULL
+x
+NULL
+call p1();
+x
+NULL
+x
+NULL
+x
+NULL
+drop tables t1,t2,t3;

--- 1.14/mysql-test/t/subselect_innodb.test	2005-10-13 10:52:52 +03:00
+++ 1.15/mysql-test/t/subselect_innodb.test	2005-11-01 23:00:49 +02:00
@@ -183,3 +183,57 @@
 
 drop table t1;
 
+#
+# BUG#14342: wrong placement of subquery internals in complex queries
+#
+CREATE TABLE `t1` (
+  `t3_id` int NOT NULL,
+  `t1_id` int NOT NULL,
+  PRIMARY KEY  (`t1_id`)
+);
+CREATE TABLE `t2` (
+  `t2_id` int NOT NULL,
+  `t1_id` int NOT NULL,
+  `b` int NOT NULL,
+  PRIMARY KEY  (`t2_id`),
+  UNIQUE KEY `idx_t2_t1_b` (`t1_id`,`b`)
+) ENGINE=InnoDB;
+CREATE TABLE `t3` (
+  `t3_id` int NOT NULL
+);
+INSERT INTO `t3` VALUES (3);
+select
+  (SELECT rs.t2_id
+   FROM t2 rs
+   WHERE rs.t1_id=
+     (SELECT lt.t1_id
+      FROM t1 lt
+      WHERE lt.t3_id=a.t3_id)
+   ORDER BY b DESC LIMIT 1)
+from t3 AS a;
+# repeat above query in SP
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+delimiter //;
+create procedure p1()
+begin
+  declare done int default 3;
+  repeat
+    select
+      (SELECT rs.t2_id
+       FROM t2 rs
+       WHERE rs.t1_id=
+         (SELECT lt.t1_id
+          FROM t1 lt
+          WHERE lt.t3_id=a.t3_id)
+       ORDER BY b DESC LIMIT 1) as x
+    from t3 AS a;
+    set done= done-1;
+  until done <= 0 end repeat;
+end//
+delimiter ;//
+call p1();
+call p1();
+call p1();
+drop tables t1,t2,t3;

--- 1.120/sql/item_subselect.cc	2005-10-13 10:52:52 +03:00
+++ 1.121/sql/item_subselect.cc	2005-11-01 23:00:49 +02:00
@@ -193,9 +193,17 @@
 
 bool Item_subselect::exec()
 {
+  MEM_ROOT *save_mem_root= thd->mem_root;
   int res;
 
+  /*
+    The subquery need statement execution lifetime mem_root as current
+    mem_root to avoid allocation of some parts of the subquery in
+    the QUICK_RANGE_SELECT mem_root, for example
+  */
+  thd->mem_root= thd->execution_mem_root;
   res= engine->exec();
+  thd->mem_root= save_mem_root;
 
   if (engine_changed)
   {

--- 1.193/sql/sp_head.cc	2005-10-21 14:07:50 +03:00
+++ 1.194/sql/sp_head.cc	2005-11-01 23:00:49 +02:00
@@ -859,6 +859,7 @@
   uint ip= 0;
   ulong save_sql_mode;
   Query_arena *old_arena;
+  MEM_ROOT *save_execution_mem_root= thd->execution_mem_root;
   /* per-instruction arena */
   MEM_ROOT execute_mem_root;
   Query_arena execute_arena(&execute_mem_root, INITIALIZED_FOR_SP),
@@ -953,6 +954,8 @@
   */
   thd->spcont->callers_arena= &backup_arena;
 
+  /* set mem_root of execution */
+  thd->execution_mem_root= &execute_mem_root;
   do
   {
     sp_instr *i;
@@ -1040,6 +1043,7 @@
     }
   } while (ret == 0 && !thd->killed);
 
+  thd->execution_mem_root= save_execution_mem_root;
   thd->restore_active_arena(&execute_arena, &backup_arena);