avoiding of calling Item::val_* methods family with opt_range mem_root, because its life time is too short. (BUG#14342)

mysql-test/r/subselect_innodb.result:
  BUG#14342 test case
mysql-test/t/subselect_innodb.test:
  BUG#14342 test case
sql/opt_range.cc:
  avoiding of calling Item::val_* methods family with opt_range mem_root, because its life time is too short.

3 files changed, 139 insertions, 6 deletions

diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index a23b584e510..4dec7882d58 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -172,3 +172,75 @@ group by country;
 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;
diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test
index a07cc93ad68..4bfc4d17588 100644
--- a/mysql-test/t/subselect_innodb.test
+++ b/mysql-test/t/subselect_innodb.test
@@ -183,3 +183,57 @@ group by country;
 
 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;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 63a49a46110..de52811c12f 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -5759,10 +5759,17 @@ QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table,
   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_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table,
   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_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table,
     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;
 }