keep old engine & JOIN if we changed subquery Item (Bug #3646)

mysql-test/r/subselect.result:
  ALL/ANY test
mysql-test/t/subselect.test:
  ALL/ANY test
sql/item_subselect.cc:
  keep old engine & JOIN if we changed subquery Item, which allow avoid second all SELECT items fix_fields call with pervios clean up (because of second setup_tables which remove table->key_use and maybe something else)
  keep list when we change Item in SELECT list
  processing inserted max/min function (now JOIN::prepare will be called only once)
  methods of changing item for subselect engines
sql/item_subselect.h:
  change item & results procedure
sql/item_sum.cc:
  Item_sum_hybrid::clear moved to .cc file (to keep .h clean and to make inserving/removing debug info easy)
sql/item_sum.h:
  Item_sum_hybrid::clear moved to .cc file (to keep .h clean and to make inserving/removing debug info easy)
sql/sql_lex.cc:
  note about new method
sql/sql_lex.h:
  method for changing result of UNION JOINs
sql/sql_select.cc:
  method for changing result in JOIN
sql/sql_select.h:
  method for changing result in JOIN
sql/sql_union.cc:
  method for changing result in JOIN

11 files changed, 291 insertions, 20 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index a2210fde222..690d5006c72 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1719,3 +1719,92 @@ SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t
 id	c
 1	1
 2	0
+DROP TABLE t1,t2;
+CREATE TABLE t1 ( a int, b int );
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
+SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
+a
+3
+SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
+a
+1
+SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
+a
+2
+SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
+a
+1
+3
+SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
+a
+3
+SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
+a
+1
+SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
+a
+2
+SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
+a
+1
+3
+ALTER TABLE t1 ADD INDEX (a);
+SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
+a
+3
+SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
+a
+1
+SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
+a
+2
+SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
+a
+1
+3
+SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
+a
+3
+SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
+a
+1
+SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
+a
+2
+SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
+a
+1
+3
+DROP TABLE t1;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 1b5bb807b97..94389963ae6 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1125,4 +1125,36 @@ INSERT INTO t1 VALUES (1), (2);
 INSERT INTO t2 VALUES (1);
 SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
 SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id;
-DROP TABLE t1,t2
+DROP TABLE t1,t2;
+
+#
+# ALL/ANY test
+#
+CREATE TABLE t1 ( a int, b int );
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
+SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
+ALTER TABLE t1 ADD INDEX (a);
+SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
+SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
+DROP TABLE t1;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 2d10be62d53..4503c1b63a9 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -56,10 +56,24 @@ void Item_subselect::init(st_select_lex *select_lex,
   DBUG_PRINT("subs", ("select_lex 0x%xl", (ulong) select_lex));
   unit= select_lex->master_unit();
 
-  if (select_lex->next_select())
-    engine= new subselect_union_engine(unit, result, this);
+  if (unit->item)
+  {
+    /*
+      Item can be changed in JOIN::prepare while engine in JOIN::optimize
+      => we do not copy old_engine here
+    */
+    engine= unit->item->engine;
+    unit->item->engine= 0;
+    unit->item= this;
+    engine->change_item(this, result);
+  }
   else
-    engine= new subselect_single_select_engine(select_lex, result, this);
+  {
+    if (select_lex->next_select())
+      engine= new subselect_union_engine(unit, result, this);
+    else
+      engine= new subselect_single_select_engine(select_lex, result, this);
+  }
   DBUG_VOID_RETURN;
 }
 
@@ -69,11 +83,13 @@ void Item_subselect::cleanup()
   Item_result_field::cleanup();
   if (old_engine)
   {
-    engine->cleanup();
+    if (engine)
+      engine->cleanup();
     engine= old_engine;
     old_engine= 0;
   }
-  engine->cleanup();
+  if (engine)
+    engine->cleanup();
   reset();
   value_assigned= 0;
   DBUG_VOID_RETURN;
@@ -127,7 +143,6 @@ bool Item_subselect::fix_fields(THD *thd_param, TABLE_LIST *tables, Item **ref)
       if (have_to_be_excluded)
 	engine->exclude();
       substitution= 0;
-      fixed= 1;
       thd->where= "checking transformed subquery";      
       if (!(*ref)->fixed)
 	ret= (*ref)->fix_fields(thd, tables, ref);
@@ -660,10 +675,20 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 	item= new Item_sum_min(*select_lex->ref_pointer_array);
       }
       *select_lex->ref_pointer_array= item;
-      select_lex->item_list.empty();
-      select_lex->item_list.push_back(item);
+      {
+	List_iterator<Item> it(select_lex->item_list);
+	it++;
+	it.replace(item);
+      }
 
-      // fix_fields call for 'item' will be made during new subquery fix_fields
+      /*
+	Item_sum_(max|min) can't substitute other item => we can use 0 as
+	reference
+      */
+      if (item->fix_fields(thd, join->tables_list, 0))
+	goto err;
+      /* we added aggregate function => we have to change statistic */
+      count_field_types(&join->tmp_table_param, join->all_fields, 0);
 
       subs= new Item_singlerow_subselect(select_lex);
     }
@@ -1428,3 +1453,67 @@ void subselect_indexsubquery_engine::print(String *str)
   }
   str->append(')');
 }
+
+/*
+  change select_result object of engine
+
+  SINOPSYS
+    subselect_single_select_engine::change_result()
+    si		new subselect Item
+    res		new select_result object
+
+  RETURN
+    0  OK
+    -1 error
+*/
+
+int subselect_single_select_engine::change_item(Item_subselect *si,
+						select_subselect *res)
+{
+  item= si;
+  result= res;
+  return select_lex->join->change_result(result);
+}
+
+
+/*
+  change select_result object of engine
+
+  SINOPSYS
+    subselect_single_select_engine::change_result()
+    si		new subselect Item
+    res		new select_result object
+
+  RETURN
+    0  OK
+    -1 error
+*/
+
+int subselect_union_engine::change_item(Item_subselect *si,
+					select_subselect *res)
+{
+  item= si;
+  int rc= unit->change_result(res, result);
+  result= res;
+  return rc;
+}
+
+
+/*
+  change select_result emulation, never should be called
+
+  SINOPSYS
+    subselect_single_select_engine::change_result()
+    si		new subselect Item
+    res		new select_result object
+
+  RETURN
+    -1 error
+*/
+
+int subselect_uniquesubquery_engine::change_item(Item_subselect *si,
+						 select_subselect *res)
+{
+  DBUG_ASSERT(0);
+  return -1;
+}
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 6d8f5353695..364781de362 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -290,6 +290,7 @@ public:
   virtual table_map upper_select_const_tables()= 0;
   static table_map calc_const_tables(TABLE_LIST *);
   virtual void print(String *str)= 0;
+  virtual int change_item(Item_subselect *si, select_subselect *result)= 0;
 };
 
 
@@ -313,6 +314,7 @@ public:
   void exclude();
   table_map upper_select_const_tables();
   void print (String *str);
+  int change_item(Item_subselect *si, select_subselect *result);
 };
 
 
@@ -332,6 +334,7 @@ public:
   void exclude();
   table_map upper_select_const_tables();
   void print (String *str);
+  int change_item(Item_subselect *si, select_subselect *result);
 };
 
 
@@ -360,6 +363,7 @@ public:
   void exclude();
   table_map upper_select_const_tables() { return 0; }
   void print (String *str);
+  int change_item(Item_subselect *si, select_subselect *result);
 };
 
 
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index eac8d31b256..0c5b29fc069 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -502,6 +502,14 @@ void Item_sum_variance::update_field()
 
 /* min & max */
 
+void Item_sum_hybrid::clear()
+{
+  sum= 0.0;
+  sum_int= 0;
+  value.length(0);
+  null_value= 1;
+}
+
 double Item_sum_hybrid::val()
 {
   DBUG_ASSERT(fixed == 1);
diff --git a/sql/item_sum.h b/sql/item_sum.h
index 4cded41a9f6..ef947900fd2 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -415,13 +415,7 @@ class Item_sum_hybrid :public Item_sum
   table_map used_tables() const { return used_table_cache; }
   bool const_item() const { return !used_table_cache; }
 
-  void clear()
-  {
-    sum=0.0;
-    sum_int=0;
-    value.length(0);
-    null_value=1;
-  }
+  void clear();
   double val();
   longlong val_int();
   void reset_field();
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 5fa8b37285e..1038066f6ef 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1710,6 +1710,7 @@ TABLE_LIST *st_lex::link_first_table_back(TABLE_LIST *tables,
   st_select_lex::print is in sql_select.h
 
   st_select_lex_unit::prepare, st_select_lex_unit::exec,
-  st_select_lex_unit::cleanup, st_select_lex_unit::reinit_exec_mechanism
+  st_select_lex_unit::cleanup, st_select_lex_unit::reinit_exec_mechanism,
+  st_select_lex_unit::change_result
   are in sql_union.cc
 */
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index aa3e81fd9c9..557c037c799 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -371,6 +371,7 @@ public:
   void print(String *str);
 
   ulong init_prepare_fake_select_lex(THD *thd);
+  int change_result(select_subselect *result, select_subselect *old_result);
 
   friend void mysql_init_query(THD *thd);
   friend int subselect_union_engine::exec();
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5db4a0042ea..aeab7a9719c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1585,8 +1585,7 @@ mysql_select(THD *thd, Item ***rref_pointer_array,
       if (select_lex->linkage != GLOBAL_OPTIONS_TYPE)
       {
 	//here is EXPLAIN of subselect or derived table
-	join->result= result;
-	if (!join->procedure && result->prepare(join->fields_list, unit))
+	if (join->change_result(result))
 	{
 	  DBUG_RETURN(-1);
 	}
@@ -9514,3 +9513,27 @@ void st_select_lex::print(THD *thd, String *str)
 
   // PROCEDURE unsupported here
 }
+
+
+/*
+  change select_result object of JOIN
+
+  SYNOPSIS
+    JOIN::change_result()
+    res		new select_result object
+
+  RETURN
+    0 - OK
+    -1 - error
+*/
+
+int JOIN::change_result(select_result *res)
+{
+  DBUG_ENTER("JOIN::change_result");
+  result= res;
+  if (!procedure && result->prepare(fields_list, select_lex->master_unit()))
+  {
+    DBUG_RETURN(-1);
+  }
+  DBUG_RETURN(0);
+}
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 5a246a477cf..8aca43484d2 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -304,6 +304,7 @@ class JOIN :public Sql_alloc
     return (do_send_rows && tmp_table_param.sum_func_count != 0 &&
 	    !group_list);
   }
+  int change_result(select_result *result);
 };
 
 
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 63638b618d9..de3eeec0db0 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -555,3 +555,32 @@ void st_select_lex_unit::reinit_exec_mechanism()
   }
 #endif
 }
+
+
+/*
+  change select_result object of unit
+
+  SYNOPSIS
+    st_select_lex_unit::change_result()
+    result	new select_result object
+    old_result	old select_result object
+
+  RETURN
+    0 - OK
+    -1 - error
+*/
+
+int st_select_lex_unit::change_result(select_subselect *result,
+				      select_subselect *old_result)
+{
+  int res= 0;
+  for (SELECT_LEX *sl= first_select_in_union(); sl; sl= sl->next_select())
+  {
+    if (sl->join && sl->join->result == old_result)
+      if ((res= sl->join->change_result(result)))
+	return (res);
+  }
+  if (fake_select_lex && fake_select_lex->join)
+    res= fake_select_lex->join->change_result(result);
+  return (res);
+}