fixed bugs in temporary tables in subselect implementation (SCRUM)

merging with switching on static tables optimization (SCRUM)
fixed subselects with uncacheable results
added test for fixed bugs from bugreports

11 files changed, 246 insertions, 52 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 03308ca7dc4..5cb17e124f6 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -278,7 +278,7 @@ NULL	1
 explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
-2	DEPENDENT SUBSELECT	t1	system	NULL	NULL	NULL	NULL	1	Using where
+2	DEPENDENT SUBSELECT	t1	system	NULL	NULL	NULL	NULL	1	
 3	DEPENDENT UNION	t5	ALL	NULL	NULL	NULL	NULL	2	Using where
 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
 Subselect returns more than 1 record
@@ -321,9 +321,9 @@ INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
 EXPLAIN SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t8	const	PRIMARY	PRIMARY	35	const	1	
-4	SUBSELECT	t8	const	PRIMARY	PRIMARY	35	const	1	
+4	SUBSELECT	t8	const	PRIMARY	PRIMARY	35		1	
 2	SUBSELECT	t8	const	PRIMARY	PRIMARY	35	const	1	
-3	SUBSELECT	t8	const	PRIMARY	PRIMARY	35	const	1	
+3	SUBSELECT	t8	const	PRIMARY	PRIMARY	35		1	
 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
 t8 WHERE pseudo='joce');
 Cardinality error (more/less than 1 columns)
@@ -397,33 +397,51 @@ SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
 Unknown column 'a' in 'having clause'
 SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
 Unknown column 'a' in 'having clause'
-SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY date);
+SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
 mot	topic	date	pseudo
 joce	40143	2002-10-22	joce
-SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100);
+joce	43506	2002-10-22	joce
+SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
 mot	topic	date	pseudo
 SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
 mot	topic	date	pseudo
-SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY date);
+SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
 mot	topic	date	pseudo
 joce	40143	2002-10-22	joce
-SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100);
+joce	43506	2002-10-22	joce
+SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
 mot	topic	date	pseudo
 SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
 mot	topic	date	pseudo
-SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY date);
+SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
 mot	topic	date	pseudo
-joce	40143	2002-10-22	joce
-SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100);
+SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
 mot	topic	date	pseudo
 joce	40143	2002-10-22	joce
 joce	43506	2002-10-22	joce
+SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
+mot	topic	date	pseudo	topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100)
+joce	40143	2002-10-22	joce	1
+joce	43506	2002-10-22	joce	1
 SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
 mot	topic	date	pseudo
 SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
 mot	topic	date	pseudo
 joce	40143	2002-10-22	joce
 joce	43506	2002-10-22	joce
+SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
+mot	topic	date	pseudo
+joce	40143	2002-10-22	joce
+SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
+mot	topic	date	pseudo
+joce	40143	2002-10-22	joce
+SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
+mot	topic	date	pseudo
+joce	40143	2002-10-22	joce
+SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
+mot	topic	date	pseudo	topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000)
+joce	40143	2002-10-22	joce	1
+joce	43506	2002-10-22	joce	0
 drop table t1,t2;
 CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -850,3 +868,86 @@ do (SELECT a from t1);
 Table 'test.t1' doesn't exist
 set @a:=(SELECT a from t1);
 Table 'test.t1' doesn't exist
+CREATE TABLE t1 (a int, KEY(a));
+HANDLER t1 OPEN;
+HANDLER t1 READ a=((SELECT 1));
+You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use
+HANDLER t1 CLOSE;
+drop table t1;
+create table t1 (a int);
+create table t2 (b int);
+insert into t1 values (1),(2);
+insert into t2 values (1);
+select a from t1 where a in (select a from t1 where a in (select b from t2));
+a
+1
+drop table t1, t2;
+create table t1 (a int, b int);
+create table t2 like t1;
+insert into t1 values (1,2),(1,3),(1,4),(1,5);
+insert into t2 values (1,2),(1,3);
+select * from t1 where row(a,b) in (select a,b from t2);
+a	b
+1	2
+1	3
+drop table t1, t2;
+CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY  (`i`)) TYPE=MyISAM CHARSET=latin1;
+INSERT INTO t1 VALUES (1);
+UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
+Invalid use of group function
+drop table t1;
+CREATE TABLE t (a int(1));
+EXPLAIN SELECT (SELECT RAND() FROM t) FROM t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	system	NULL	NULL	NULL	NULL	0	const row not found
+2	UNCACHEABLE SUBSELECT	t	system	NULL	NULL	NULL	NULL	0	const row not found
+EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t) FROM t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	system	NULL	NULL	NULL	NULL	0	const row not found
+2	UNCACHEABLE SUBSELECT	t	system	NULL	NULL	NULL	NULL	0	const row not found
+EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t) FROM t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	system	NULL	NULL	NULL	NULL	0	const row not found
+2	UNCACHEABLE SUBSELECT	t	system	NULL	NULL	NULL	NULL	0	const row not found
+drop table t;
+CREATE TABLE `t1` (
+`mot` varchar(30) character set latin1 NOT NULL default '',
+`topic` mediumint(8) unsigned NOT NULL default '0',
+`date` date NOT NULL default '0000-00-00',
+`pseudo` varchar(35) character set latin1 NOT NULL default '',
+PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
+KEY `pseudo` (`pseudo`,`date`,`topic`),
+KEY `topic` (`topic`)
+) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
+CREATE TABLE `t2` (
+`mot` varchar(30) character set latin1 NOT NULL default '',
+`topic` mediumint(8) unsigned NOT NULL default '0',
+`date` date NOT NULL default '0000-00-00',
+`pseudo` varchar(35) character set latin1 NOT NULL default '',
+PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
+KEY `pseudo` (`pseudo`,`date`,`topic`),
+KEY `topic` (`topic`)
+) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
+CREATE TABLE `t3` (
+`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
+`maxnumrep` int(10) unsigned NOT NULL default '0',
+PRIMARY KEY  (`numeropost`),
+UNIQUE KEY `maxnumrep` (`maxnumrep`)
+) TYPE=MyISAM CHARSET=latin1;
+INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
+INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
+INSERT INTO t3 VALUES (1,1);
+SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
+numeropost=topic);
+topic
+2
+select * from t1;
+mot	topic	date	pseudo
+joce	1	0000-00-00	joce
+test	2	0000-00-00	test
+DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
+EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
+select * from t1;
+mot	topic	date	pseudo
+joce	1	0000-00-00	joce
+drop table t1, t2, t3;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index baf58916bd4..da9d7330c46 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -215,13 +215,13 @@ SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING t
 SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
 SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
 SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
-SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100;
 SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
 SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
 SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
-delete from t2 where topic=40143;
-SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
-SELECT *, topic as fff, exists (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100 and fff = topic) from t2;
+SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
+SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
+SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
+SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
 drop table t1,t2;
 
 #forumconthardwarefr7
@@ -483,3 +483,79 @@ drop table t1;
 do (SELECT a from t1);
 -- error 1146
 set @a:=(SELECT a from t1);
+
+CREATE TABLE t1 (a int, KEY(a)); 
+HANDLER t1 OPEN;
+-- error 1149
+HANDLER t1 READ a=((SELECT 1));
+HANDLER t1 CLOSE;
+drop table t1;
+
+create table t1 (a int);
+create table t2 (b int);
+insert into t1 values (1),(2);
+insert into t2 values (1);
+select a from t1 where a in (select a from t1 where a in (select b from t2));
+drop table t1, t2;
+
+create table t1 (a int, b int);
+create table t2 like t1;
+insert into t1 values (1,2),(1,3),(1,4),(1,5);
+insert into t2 values (1,2),(1,3);
+select * from t1 where row(a,b) in (select a,b from t2);
+drop table t1, t2;
+
+CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY  (`i`)) TYPE=MyISAM CHARSET=latin1;
+INSERT INTO t1 VALUES (1);
+-- error 1111
+UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
+drop table t1;
+
+#test of uncacheable subqueries
+CREATE TABLE t (a int(1));
+EXPLAIN SELECT (SELECT RAND() FROM t) FROM t;
+EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t) FROM t;
+EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t) FROM t;
+drop table t;
+
+
+CREATE TABLE `t1` (
+  `mot` varchar(30) character set latin1 NOT NULL default '',
+  `topic` mediumint(8) unsigned NOT NULL default '0',
+  `date` date NOT NULL default '0000-00-00',
+  `pseudo` varchar(35) character set latin1 NOT NULL default '',
+  PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
+  KEY `pseudo` (`pseudo`,`date`,`topic`),
+  KEY `topic` (`topic`)
+) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
+
+CREATE TABLE `t2` (
+  `mot` varchar(30) character set latin1 NOT NULL default '',
+  `topic` mediumint(8) unsigned NOT NULL default '0',
+  `date` date NOT NULL default '0000-00-00',
+  `pseudo` varchar(35) character set latin1 NOT NULL default '',
+  PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
+  KEY `pseudo` (`pseudo`,`date`,`topic`),
+  KEY `topic` (`topic`)
+) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
+
+CREATE TABLE `t3` (
+  `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
+  `maxnumrep` int(10) unsigned NOT NULL default '0',
+  PRIMARY KEY  (`numeropost`),
+  UNIQUE KEY `maxnumrep` (`maxnumrep`)
+) TYPE=MyISAM CHARSET=latin1;
+INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
+
+INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
+
+INSERT INTO t3 VALUES (1,1);
+
+SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
+numeropost=topic);
+select * from t1;
+DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
+EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
+select * from t1;
+
+drop table t1, t2, t3;
\ No newline at end of file
diff --git a/sql/item_create.cc b/sql/item_create.cc
index 0dba7f6e3ae..ab201fbfd71 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -167,7 +167,7 @@ Item *create_func_from_days(Item* a)
 
 Item *create_func_get_lock(Item* a, Item *b)
 {
-  current_thd->lex.safe_to_cache_query=0;
+  current_thd->lex.uncacheable();
   return new Item_func_get_lock(a, b);
 }
 
@@ -327,7 +327,7 @@ Item *create_func_radians(Item *a)
 
 Item *create_func_release_lock(Item* a)
 {
-  current_thd->lex.safe_to_cache_query=0;
+  current_thd->lex.uncacheable();
   return new Item_func_release_lock(a);
 }
 
@@ -435,13 +435,13 @@ Item *create_func_year(Item* a)
 
 Item *create_load_file(Item* a)
 {
-  current_thd->lex.safe_to_cache_query=0;
+  current_thd->lex.uncacheable();
   return new Item_load_file(a);
 }
 
 Item *create_wait_for_master_pos(Item* a, Item* b)
 {
-  current_thd->lex.safe_to_cache_query=0;
+  current_thd->lex.uncacheable();
   return new Item_master_pos_wait(a, b);
 }
 
@@ -463,7 +463,7 @@ Item *create_func_cast(Item *a, Item_cast cast_type)
 
 Item *create_func_is_free_lock(Item* a)
 {
-  current_thd->lex.safe_to_cache_query=0;
+  current_thd->lex.uncacheable();
   return new Item_func_is_free_lock(a);
 }
 
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 3f231c73585..860a23e3634 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2544,7 +2544,7 @@ Item *get_system_var(enum_var_type var_type, LEX_STRING name)
   }
   if (!(item=var->item(thd, var_type)))
     return 0;					// Impossible
-  thd->lex.safe_to_cache_query=0;
+  thd->lex.uncacheable();
   buff[0]='@';
   buff[1]='@';
   pos=buff+2;
@@ -2570,7 +2570,7 @@ Item *get_system_var(enum_var_type var_type, const char *var_name, uint length,
   DBUG_ASSERT(var != 0);
   if (!(item=var->item(thd, var_type)))
     return 0;					// Impossible
-  thd->lex.safe_to_cache_query=0;
+  thd->lex.uncacheable();
   item->set_name(item_name);		// Will use original name
   return item;
 }
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 18a32b97a9f..ffb4d715159 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -129,7 +129,8 @@ void Item_subselect::fix_length_and_dec()
 
 inline table_map Item_subselect::used_tables() const
 {
-  return (table_map) engine->depended() ? 1L : 0L; 
+  return (table_map) (engine->dependent() ? 1L :
+		      (engine->uncacheable() ? RAND_TABLE_BIT : 0L));
 }
 
 Item_singlerow_subselect::Item_singlerow_subselect(THD *thd,
@@ -795,7 +796,7 @@ int subselect_single_select_engine::exec()
       DBUG_RETURN(join->error?join->error:1);
     }
   }
-  if (select_lex->dependent && executed)
+  if ((select_lex->dependent || select_lex->uncacheable) && executed)
   {
     if (join->reinit())
     {
@@ -837,16 +838,26 @@ uint subselect_union_engine::cols()
   return unit->first_select()->item_list.elements;
 }
 
-bool subselect_single_select_engine::depended()
+bool subselect_single_select_engine::dependent()
 {
   return select_lex->dependent;
 }
 
-bool subselect_union_engine::depended()
+bool subselect_union_engine::dependent()
 {
   return unit->dependent;
 }
 
+bool subselect_single_select_engine::uncacheable()
+{
+  return select_lex->uncacheable;
+}
+
+bool subselect_union_engine::uncacheable()
+{
+  return unit->uncacheable;
+}
+
 bool subselect_single_select_engine::check_loop(uint id)
 {
   DBUG_ENTER("subselect_single_select_engine::check_loop");
@@ -865,13 +876,9 @@ bool subselect_union_engine::check_loop(uint id)
 void subselect_single_select_engine::exclude()
 {
   select_lex->master_unit()->exclude_level();
-  //if (current_thd->lex->describe)
 }
 
 void subselect_union_engine::exclude()
 {
   unit->exclude_level();
-  // for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
-  //  if (sl->join && sl->join->check_loop(id))
-  //    DBUG_RETURN(1);
 }
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index cf7f612224a..3b74e7d6070 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -223,7 +223,8 @@ public:
   virtual void fix_length_and_dec(Item_cache** row)= 0;
   virtual int exec()= 0;
   virtual uint cols()= 0; /* return number of columnss in select */
-  virtual bool depended()= 0; /* depended from outer select */
+  virtual bool dependent()= 0; /* depended from outer select */
+  virtual bool uncacheable()= 0; /* query is uncacheable */
   enum Item_result type() { return res_type; }
   virtual bool check_loop(uint id)= 0;
   virtual void exclude()= 0;
@@ -245,7 +246,8 @@ public:
   void fix_length_and_dec(Item_cache** row);
   int exec();
   uint cols();
-  bool depended();
+  bool dependent();
+  bool uncacheable();
   bool check_loop(uint id);
   void exclude();
 };
@@ -262,7 +264,8 @@ public:
   void fix_length_and_dec(Item_cache** row);
   int exec();
   uint cols();
-  bool depended();
+  bool dependent();
+  bool uncacheable();
   bool check_loop(uint id);
   void exclude();
 };
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 2b6c337bedb..6bb81de5fcf 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -995,7 +995,7 @@ int yylex(void *arg, void *yythd)
 
 void st_select_lex_node::init_query()
 {
-  no_table_names_allowed= dependent= 0;
+  no_table_names_allowed= uncacheable= dependent= 0;
   ref_pointer_array= 0;
 }
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index a9a792ee783..1cd38cf9f67 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -216,6 +216,7 @@ public:
   uint with_sum_func;   /* sum function indicator and number of it */
   bool create_refs;
   bool dependent;	/* dependent from outer select subselect */
+  bool uncacheable;     /* result of this query can't be cached */
   bool no_table_names_allowed; /* used for global order by */
 
   static void *operator new(size_t size)
@@ -475,6 +476,13 @@ typedef struct st_lex
   CHARSET_INFO *charset;
   char *help_arg;
   bool tmp_table_used;
+
+  inline void uncacheable()
+  {
+    safe_to_cache_query= 0;
+    current_select->uncacheable = 
+      current_select->master_unit()->uncacheable= 1;
+  }
 } LEX;
 
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index c39b958edfb..ad0b22db754 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -839,7 +839,6 @@ JOIN::reinit()
     exec_tmp_table1->file->extra(HA_EXTRA_RESET_STATE);
     exec_tmp_table1->file->delete_all_rows();
     free_io_cache(exec_tmp_table1);
-    memcpy(ref_pointer_array, items0, ref_pointer_array_size);
   }
   if (exec_tmp_table2)
   {
@@ -850,9 +849,6 @@ JOIN::reinit()
   if (items0)
     memcpy(ref_pointer_array, items0, ref_pointer_array_size);
 
-  tmp_table_param.copy_funcs.empty();
-  tmp_table_param.copy_field= tmp_table_param.copy_field_end= 0;
-
   if (tmp_join)
     restore_tmp();
 
@@ -4824,7 +4820,7 @@ bool create_myisam_from_heap(THD *thd, TABLE *table, TMP_TABLE_PARAM *param,
 static int
 do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure)
 {
-  int error;
+  int error= 0;
   JOIN_TAB *join_tab;
   int (*end_select)(JOIN *, struct st_join_table *,bool);
   DBUG_ENTER("do_select");
@@ -8032,9 +8028,11 @@ int mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
 				((sl->linkage == DERIVED_TABLE_TYPE) ?
 				 "DERIVED":
 				((sl->dependent)?"DEPENDENT SUBSELECT":
-				 "SUBSELECT")):
+				 (sl->uncacheable?"UNCACHEABLE SUBSELECT":
+				   "SUBSELECT"))):
 				((sl->dependent)?"DEPENDENT UNION":
-				 "UNION"))),
+				 sl->uncacheable?"UNCACHEABLE UNION":
+				  "UNION"))),
 			      result);
     if (res)
       break;
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index ffd6ac48c2c..259f6dbb4b5 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -207,11 +207,11 @@ int st_select_lex_unit::exec()
   DBUG_ENTER("st_select_lex_unit::exec");
   SELECT_LEX_NODE *lex_select_save= thd->lex.current_select;
   
-  if (executed && !dependent)
+  if (executed && !(dependent||uncacheable))
     DBUG_RETURN(0);
   executed= 1;
   
-  if (dependent || !item || !item->assigned())
+  if ((dependent||uncacheable) || !item || !item->assigned())
   {
     if (optimized && item && item->assigned())
     {
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index e9e26b06bb4..575b953bbfe 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1798,7 +1798,7 @@ select_option:
 	      YYABORT;
 	    Select->options|= OPTION_FOUND_ROWS;
 	  }
-	| SQL_NO_CACHE_SYM { Lex->safe_to_cache_query=0; }
+	| SQL_NO_CACHE_SYM { Lex->uncacheable();; }
 	| SQL_CACHE_SYM    { Select->options|= OPTION_TO_QUERY_CACHE; }
 	| ALL		{}
 	;
@@ -2045,12 +2045,12 @@ simple_expr:
 	| '@' ident_or_text SET_VAR expr
 	  {
 	    $$= new Item_func_set_user_var($2,$4);
-	    Lex->safe_to_cache_query=0;
+	    Lex->uncacheable();;
 	  }
 	| '@' ident_or_text
 	  {
 	    $$= new Item_func_get_user_var($2);
-	    Lex->safe_to_cache_query=0;
+	    Lex->uncacheable();;
 	  }
 	| '@' '@' opt_var_ident_type ident_or_text
 	  {
@@ -2136,7 +2136,7 @@ simple_expr:
 	| ENCRYPT '(' expr ')'
 	  {
 	    $$= new Item_func_encrypt($3);
-	    Lex->safe_to_cache_query=0;
+	    Lex->uncacheable();;
 	  }
 	| ENCRYPT '(' expr ',' expr ')'   { $$= new Item_func_encrypt($3,$5); }
 	| DECODE_SYM '(' expr ',' TEXT_STRING ')'
@@ -2192,11 +2192,12 @@ simple_expr:
 	  {
     	    $$= get_system_var(OPT_SESSION, "last_insert_id", 14,
 			      "last_insert_id()");
+	    Lex->safe_to_cache_query= 0;
 	  }
 	| LAST_INSERT_ID '(' expr ')'
 	  {
 	    $$= new Item_func_set_last_insert_id($3);
-	    Lex->safe_to_cache_query=0;
+	    Lex->safe_to_cache_query= 0;
 	  }
 	| LEFT '(' expr ',' expr ')'
 	  { $$= new Item_func_left($3,$5); }
@@ -2272,9 +2273,9 @@ simple_expr:
 	| POSITION_SYM '(' no_in_expr IN_SYM expr ')'
 	  { $$ = new Item_func_locate($5,$3); }
 	| RAND '(' expr ')'
-	  { $$= new Item_func_rand($3); Lex->safe_to_cache_query=0;}
+	  { $$= new Item_func_rand($3); Lex->uncacheable();}
 	| RAND '(' ')'
-	  { $$= new Item_func_rand(); Lex->safe_to_cache_query=0;}
+	  { $$= new Item_func_rand(); Lex->uncacheable();}
 	| REPLACE '(' expr ',' expr ',' expr ')'
 	  { $$= new Item_func_replace($3,$5,$7); }
 	| RIGHT '(' expr ',' expr ')'
@@ -2376,7 +2377,7 @@ simple_expr:
 	| BENCHMARK_SYM '(' ULONG_NUM ',' expr ')'
 	  {
 	    $$=new Item_func_benchmark($3,$5);
-	    Lex->safe_to_cache_query=0;
+	    Lex->uncacheable();
 	  }
 	| EXTRACT_SYM '(' interval FROM expr ')'
 	{ $$=new Item_extract( $3, $5); };
@@ -2874,7 +2875,7 @@ procedure_clause:
 	    lex->proc_list.next= (byte**) &lex->proc_list.first;
 	    if (add_proc_to_list(lex->thd, new Item_field(NULL,NULL,$2.str)))
 	      YYABORT;
-	    Lex->safe_to_cache_query=0;
+	    Lex->uncacheable();
 	  }
 	  '(' procedure_list ')';
 
@@ -2948,7 +2949,7 @@ into:
 	}
         | INTO select_var_list_init
 	{
-	  Lex->safe_to_cache_query=0;
+	  Lex->uncacheable();
 	}
         ;