reporting empty result added in case of max/min optimisation of ALL/ANY/SOME subqueries

fixed null processing in NOT operation used in ALL subquery (Bug #6247)


mysql-test/r/subselect.result:
  new tests of ALL/ANY wiews
mysql-test/t/subselect.test:
  new tests of ALL/ANY wiews
sql/item_cmpfunc.cc:
  fixed special NOT ALL processing
  fixed processing max/min optimized subqueries with empty results (added methods to detect empty results) and special NOP operation to process them for SOME/ANY sobqueries
sql/item_cmpfunc.h:
  fixed processing max/min optimized subqueries with empty results (added methods to detect empty results) and special NOP operation to process them for SOME/ANY sobqueries
sql/item_subselect.cc:
  reporting empty result added for max/min subqueries
sql/item_subselect.h:
  reporting empty result added for max/min subqueries
sql/item_sum.cc:
  reporting empty result added fox max/min aggregate functions
sql/item_sum.h:
  reporting empty result added fox max/min aggregate functions
sql/sql_class.cc:
  reporting empty result added for max/min subqueries
sql/sql_parse.cc:
  reporting empty result added for max/min subqueries
sql/sql_union.cc:
  reporting empty result added for max/min subqueries

1 files changed, 28 insertions, 7 deletions

diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 62cd016b0df..b263b06c91f 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -271,7 +271,7 @@ Item_singlerow_subselect::Item_singlerow_subselect(st_select_lex *select_lex)
 Item_maxmin_subselect::Item_maxmin_subselect(Item_subselect *parent,
 					     st_select_lex *select_lex,
 					     bool max_arg)
-  :Item_singlerow_subselect()
+  :Item_singlerow_subselect(), was_values(TRUE)
 {
   DBUG_ENTER("Item_maxmin_subselect::Item_maxmin_subselect");
   max= max_arg;
@@ -290,12 +290,26 @@ Item_maxmin_subselect::Item_maxmin_subselect(Item_subselect *parent,
   DBUG_VOID_RETURN;
 }
 
+void Item_maxmin_subselect::cleanup()
+{
+  /*
+    By default is is TRUE to avoid TRUE reporting by
+    Item_func_not_all/Item_func_nop_all if this item was never called.
+
+    Engine exec() set it to FALSE by reset_value_registration() call.
+  */
+
+  was_values= TRUE;
+}
+
+
 void Item_maxmin_subselect::print(String *str)
 {
   str->append(max?"<max>":"<min>", 5);
   Item_singlerow_subselect::print(str);
 }
 
+
 void Item_singlerow_subselect::reset()
 {
   null_value= 1;
@@ -303,6 +317,7 @@ void Item_singlerow_subselect::reset()
     value->null_value= 1;
 }
 
+
 Item_subselect::trans_res
 Item_singlerow_subselect::select_transformer(JOIN *join)
 {
@@ -519,7 +534,7 @@ bool Item_in_subselect::test_limit(SELECT_LEX_UNIT *unit)
 
 Item_in_subselect::Item_in_subselect(Item * left_exp,
 				     st_select_lex *select_lex):
-  Item_exists_subselect(), transformed(0), upper_not(0)
+  Item_exists_subselect(), transformed(0), upper_item(0)
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
   left_expr= left_exp;
@@ -680,7 +695,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
     NULL/IS NOT NULL functions). If so, we rewrite ALL/ANY with NOT EXISTS
     later in this method.
   */
-  if ((abort_on_null || (upper_not && upper_not->top_level())) &&
+  if ((abort_on_null || (upper_item && upper_item->top_level())) &&
       !select_lex->master_unit()->uncacheable && !func->eqne_op())
   {
     if (substitution)
@@ -694,7 +709,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 	!select_lex->with_sum_func &&
 	!(select_lex->next_select()))
     {
-      Item *item;
+      Item_sum_hybrid *item;
       if (func->l_op())
       {
 	/*
@@ -711,6 +726,8 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 	*/
 	item= new Item_sum_min(*select_lex->ref_pointer_array);
       }
+      if (upper_item)
+        upper_item->set_sum_test(item);
       *select_lex->ref_pointer_array= item;
       {
 	List_iterator<Item> it(select_lex->item_list);
@@ -731,10 +748,13 @@ Item_in_subselect::single_value_transformer(JOIN *join,
     }
     else
     {
+      Item_maxmin_subselect *item;
       // remove LIMIT placed  by ALL/ANY subquery
       select_lex->master_unit()->global_parameters->select_limit=
 	HA_POS_ERROR;
-      subs= new Item_maxmin_subselect(this, select_lex, func->l_op());
+      subs= item= new Item_maxmin_subselect(this, select_lex, func->l_op());
+      if (upper_item)
+        upper_item->set_sub_test(item);
     }
     // left expression belong to outer select
     SELECT_LEX *current= thd->lex->current_select, *up;
@@ -1041,8 +1061,8 @@ Item_subselect::trans_res
 Item_allany_subselect::select_transformer(JOIN *join)
 {
   transformed= 1;
-  if (upper_not)
-    upper_not->show= 1;
+  if (upper_item)
+    upper_item->show= 1;
   return single_value_transformer(join, func);
 }
 
@@ -1247,6 +1267,7 @@ int subselect_single_select_engine::exec()
   }
   if (!executed)
   {
+    item->reset_value_registration();
     join->exec();
     executed= 1;
     join->thd->where= save_where;