bk commit into 5.0 tree (bell:1.1965) 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.1965 05/11/04 13:16:46 bell@stripped +3 -0
  avoiding of calling Item::val_* methods family with opt_range mem_root, because its life
time is too short. (BUG#14342)

  sql/opt_range.cc
    1.197 05/11/04 13:16:41 bell@stripped +13 -6
    avoiding of calling Item::val_* methods family with opt_range mem_root, because its
life time is too short.

  mysql-test/t/subselect_innodb.test
    1.15 05/11/04 13:16:41 bell@stripped +54 -0
    BUG#14342 test case

  mysql-test/r/subselect_innodb.result
    1.12 05/11/04 13:16:41 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-bug4-5.0

--- 1.196/sql/opt_range.cc	2005-11-03 16:42:03 +02:00
+++ 1.197/sql/opt_range.cc	2005-11-04 13:16:41 +02:00
@@ -5759,10 +5759,17 @@
   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);
+  /* save mem_root set by QUICK_RANGE_SELECT constructor */
+  MEM_ROOT *alloc= thd->mem_root;
   KEY *key_info = &table->key_info[ref->key];
   KEY_PART *key_part;
   QUICK_RANGE *range;
   uint part;
+  /*
+    return back default mem_root (thd->mem_root) changed by
+    QUICK_RANGE_SELECT constructor
+  */
+  thd->mem_root= old_root;
 
   if (!quick)
     return 0;			/* no ranges found */
@@ -5774,7 +5781,7 @@
   quick->records= records;
 
   if (cp_buffer_from_ref(thd,ref) && thd->is_fatal_error ||
-      !(range= new QUICK_RANGE()))
+      !(range= new(alloc) QUICK_RANGE()))
     goto err;                                   // out of memory
 
   range->min_key=range->max_key=(char*) ref->key_buff;
@@ -5809,20 +5816,20 @@
     QUICK_RANGE *null_range;
 
     *ref->null_ref_key= 1;		// Set null byte then create a range
-    if (!(null_range= new QUICK_RANGE((char*)ref->key_buff, ref->key_length,
-				      (char*)ref->key_buff, ref->key_length,
-				      EQ_RANGE)))
+    if (!(null_range= new (alloc) QUICK_RANGE((char*)ref->key_buff,
+                                              ref->key_length,
+                                              (char*)ref->key_buff,
+                                              ref->key_length,
+                                              EQ_RANGE)))
       goto err;
     *ref->null_ref_key= 0;		// Clear null byte
     if (insert_dynamic(&quick->ranges,(gptr)&null_range))
       goto err;
   }
 
-  thd->mem_root= old_root;
   return quick;
 
 err:
-  thd->mem_root= old_root;
   delete quick;
   return 0;
 }

--- 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-04 13:16:41 +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-04 13:16:41 +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;