Some simple optimisation

= ANY and <> ALL converted to (NOT) IN to get advantage of IN optimisation


mysql-test/r/subselect.result:
  test of new optimisation
mysql-test/t/subselect.test:
  test of new optimisation
sql/mysql_priv.h:
  new function for subquery creation
sql/sql_parse.cc:
  layout fix
  new function for subquery creation (convert some ALL/ANY to IN)
sql/sql_yacc.yy:
  same code blocks replaced with function

3 files changed, 47 insertions, 18 deletions

diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index d6e6256c388..cbb2f9969ce 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1015,6 +1015,11 @@ compare_func_creator comp_le_creator(bool invert);
 compare_func_creator comp_lt_creator(bool invert);
 compare_func_creator comp_ne_creator(bool invert);
 
+Item * all_any_subquery_creator(Item *left_expr,
+				chooser_compare_func_creator cmp,
+				bool all,
+				SELECT_LEX *select_lex);
+
 /*
   clean/setup table fields and map
 
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index aa91e307095..7b85eaf8f47 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4610,32 +4610,71 @@ bool check_simple_select()
   return 0;
 }
 
+
 compare_func_creator comp_eq_creator(bool invert)
 {
   return invert?&Item_bool_func2::ne_creator:&Item_bool_func2::eq_creator;
 }
 
+
 compare_func_creator comp_ge_creator(bool invert)
 {
   return invert?&Item_bool_func2::lt_creator:&Item_bool_func2::ge_creator;
 }
 
+
 compare_func_creator comp_gt_creator(bool invert)
 {
   return invert?&Item_bool_func2::le_creator:&Item_bool_func2::gt_creator;
 }
 
+
 compare_func_creator comp_le_creator(bool invert)
 {
   return invert?&Item_bool_func2::gt_creator:&Item_bool_func2::le_creator;
 }
 
+
 compare_func_creator comp_lt_creator(bool invert)
 {
   return invert?&Item_bool_func2::ge_creator:&Item_bool_func2::lt_creator;
 }
 
+
 compare_func_creator comp_ne_creator(bool invert)
 {
   return invert?&Item_bool_func2::eq_creator:&Item_bool_func2::ne_creator;
 }
+
+
+/*
+  Construct ALL/ANY/SOME subquery Item
+
+  SYNOPSIS
+    all_any_subquery_creator()
+    left_expr - pointer to left expression
+    cmp - compare function creator
+    all - true if we create ALL subquery
+    select_lex - pointer on parsed subquery structure
+
+  RETURN VALUE
+    constructed Item (or 0 if out of memory)
+*/
+Item * all_any_subquery_creator(Item *left_expr,
+				chooser_compare_func_creator cmp,
+				bool all,
+				SELECT_LEX *select_lex)
+{
+  if ((cmp == &comp_eq_creator) and !all)       //  = ANY <=> IN
+    return new Item_in_subselect(left_expr, select_lex);
+  
+  if ((cmp == &comp_ne_creator) and all)	// <> ALL <=> NOT IN
+    return new Item_func_not(new Item_in_subselect(left_expr, select_lex));
+
+  Item_allany_subselect *it=
+    new Item_allany_subselect(left_expr, (*cmp)(all), select_lex);
+  if (all)
+    return it->upper_not= new Item_func_not_all(it);	/* ALL */
+
+  return it;						/* ANY/SOME */
+}
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 81a59196e3c..0db76231f69 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -2262,12 +2262,7 @@ expr_expr:
 	| expr comp_op expr %prec EQ	{ $$= (*((*$2)(0)))($1,$3); }
 	| expr comp_op all_or_any in_subselect %prec EQ
 	{
-	  Item_allany_subselect *it=
-	    new Item_allany_subselect($1, (*$2)($3), $4);
-	  if ($3)
-	    $$ = it->upper_not= new Item_func_not_all(it);	/* ALL */
-	  else
-	    $$ = it;			/* ANY/SOME */
+	  $$= all_any_subquery_creator($1, $2, $3, $4);
 	}
 	| expr SHIFT_LEFT expr	{ $$= new Item_func_shift_left($1,$3); }
 	| expr SHIFT_RIGHT expr { $$= new Item_func_shift_right($1,$3); }
@@ -2308,12 +2303,7 @@ no_in_expr:
 	| no_in_expr comp_op expr %prec EQ	{ $$= (*((*$2)(0)))($1,$3); }
 	| no_in_expr comp_op all_or_any in_subselect %prec EQ
 	{
-	  Item_allany_subselect *it=
-	    new Item_allany_subselect($1, (*$2)($3), $4);
-	  if ($3)
-	    $$ = it->upper_not= new Item_func_not_all(it);	/* ALL */
-	  else
-	    $$ = it;			/* ANY/SOME */
+	  all_any_subquery_creator($1, $2, $3, $4);
 	}
 	| no_in_expr SHIFT_LEFT expr  { $$= new Item_func_shift_left($1,$3); }
 	| no_in_expr SHIFT_RIGHT expr { $$= new Item_func_shift_right($1,$3); }
@@ -2363,12 +2353,7 @@ no_and_expr:
 	| no_and_expr comp_op expr %prec EQ { $$= (*((*$2)(0)))($1,$3); }
 	| no_and_expr comp_op all_or_any in_subselect %prec EQ
 	{
-	  Item_allany_subselect *it=
-	    new Item_allany_subselect($1, (*$2)($3), $4);
-	  if ($3)
-	    $$ = it->upper_not= new Item_func_not_all(it);	/* ALL */
-	  else
-	    $$ = it;			/* ANY/SOME */
+	  all_any_subquery_creator($1, $2, $3, $4);
 	}
 	| no_and_expr SHIFT_LEFT expr  { $$= new Item_func_shift_left($1,$3); }
 	| no_and_expr SHIFT_RIGHT expr { $$= new Item_func_shift_right($1,$3); }