ALL/ANY/SOME

decreasing number of rules in sql_yacc.yy to satisfy bison limitation
fixed subselect Items initializer methods


mysql-test/r/subselect.result:
  tests of ALL/ANY/SOME
mysql-test/t/subselect.test:
  tests of ALL/ANY/SOME
sql/item_cmpfunc.cc:
  ALL/ANY/SOME
sql/item_cmpfunc.h:
  ALL/ANY/SOME
sql/item_subselect.cc:
  ALL/ANY/SOME
  fixed subselect Items initializer methods
sql/item_subselect.h:
  ALL/ANY/SOME
  fixed subselect Items initializer methods
sql/lex.h:
  ALL/ANY/SOME
sql/mysql_priv.h:
  decreasing number of rules in sql_yacc.yy to satisfy bison limitation
sql/sql_parse.cc:
  decreasing number of rules in sql_yacc.yy to satisfy bison limitation
sql/sql_yacc.yy:
  ALL/ANY/SOME
  decreasing number of rules in sql_yacc.yy to satisfy bison limitation

8 files changed, 204 insertions, 52 deletions

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index b6ea4beb339..1dfc5d86f5e 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -24,6 +24,30 @@
 #include "mysql_priv.h"
 #include <m_ctype.h>
 
+Item_bool_func2* Item_bool_func2::eq_creator(Item *a, Item *b)
+{
+  return new Item_func_eq(a, b);
+}
+Item_bool_func2* Item_bool_func2::ne_creator(Item *a, Item *b)
+{
+  return new Item_func_ne(a, b);
+}
+Item_bool_func2* Item_bool_func2::gt_creator(Item *a, Item *b)
+{
+  return new Item_func_gt(a, b);
+}
+Item_bool_func2* Item_bool_func2::lt_creator(Item *a, Item *b)
+{
+  return new Item_func_lt(a, b);
+}
+Item_bool_func2* Item_bool_func2::ge_creator(Item *a, Item *b)
+{
+  return new Item_func_ge(a, b);
+}
+Item_bool_func2* Item_bool_func2::le_creator(Item *a, Item *b)
+{
+  return new Item_func_le(a, b);
+}
 
 /*
   Test functions
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index c0dcc2bba8f..9976319740d 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -47,6 +47,13 @@ public:
   bool have_rev_func() const { return rev_functype() != UNKNOWN_FUNC; }
   void print(String *str) { Item_func::print_op(str); }
   bool is_null() { return test(args[0]->is_null() || args[1]->is_null()); }
+
+  static Item_bool_func2* eq_creator(Item *a, Item *b);
+  static Item_bool_func2* ne_creator(Item *a, Item *b);
+  static Item_bool_func2* gt_creator(Item *a, Item *b);
+  static Item_bool_func2* lt_creator(Item *a, Item *b);
+  static Item_bool_func2* ge_creator(Item *a, Item *b);
+  static Item_bool_func2* le_creator(Item *a, Item *b);
 };
 
 
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 7fadd982594..d8e1fee2277 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -44,13 +44,13 @@ Item_subselect::Item_subselect():
 }
 
 void Item_subselect::init(THD *thd, st_select_lex *select_lex,
-			  select_subselect *result, Item *left_expr)
+			  select_subselect *result)
 {
 
   DBUG_ENTER("Item_subselect::init");
   DBUG_PRINT("subs", ("select_lex 0x%xl", (ulong) select_lex));
 
-  select_transformer(select_lex, left_expr);
+  select_transformer(select_lex);
   if (select_lex->next_select())
     engine= new subselect_union_engine(thd, select_lex->master_unit(), result,
 				       this);
@@ -66,8 +66,7 @@ Item_subselect::~Item_subselect()
     delete engine;
 }
 
-void Item_subselect::select_transformer(st_select_lex *select_lex,
-					Item *left_expr) 
+void Item_subselect::select_transformer(st_select_lex *select_lex) 
 {
   DBUG_ENTER("Item_subselect::select_transformer");
   DBUG_VOID_RETURN;
@@ -119,7 +118,7 @@ Item_singleval_subselect::Item_singleval_subselect(THD *thd,
   Item_subselect()
 {
   DBUG_ENTER("Item_singleval_subselect::Item_singleval_subselect");
-  init(thd, select_lex, new select_singleval_subselect(this), 0);
+  init(thd, select_lex, new select_singleval_subselect(this));
   max_columns= 1;
   maybe_null= 1;
   DBUG_VOID_RETURN;
@@ -167,12 +166,11 @@ String *Item_singleval_subselect::val_str (String *str)
 }
 
 Item_exists_subselect::Item_exists_subselect(THD *thd,
-					     st_select_lex *select_lex,
-					     Item *left_expr):
+					     st_select_lex *select_lex):
   Item_subselect()
 {
   DBUG_ENTER("Item_exists_subselect::Item_exists_subselect");
-  init(thd, select_lex, new select_exists_subselect(this), left_expr);
+  init(thd, select_lex, new select_exists_subselect(this));
   max_columns= UINT_MAX;
   null_value= 0; //can't be NULL
   maybe_null= 0; //can't be NULL
@@ -182,12 +180,31 @@ Item_exists_subselect::Item_exists_subselect(THD *thd,
   DBUG_VOID_RETURN;
 }
 
-Item_in_subselect::Item_in_subselect(THD *thd, Item * left_expr,
+Item_in_subselect::Item_in_subselect(THD *thd, Item * left_exp,
 				     st_select_lex *select_lex):
   Item_exists_subselect()
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
-  init(thd, select_lex, new select_exists_subselect(this), left_expr);
+  left_expr= left_exp;
+  init(thd, select_lex, new select_exists_subselect(this));
+  max_columns= UINT_MAX;
+  null_value= 0; //can't be NULL
+  maybe_null= 0; //can't be NULL
+  value= 0;
+  // We need only 1 row to determinate existence
+  select_lex->master_unit()->global_parameters->select_limit= 1;
+  DBUG_VOID_RETURN;
+}
+
+Item_allany_subselect::Item_allany_subselect(THD *thd, Item * left_exp,
+					     compare_func_creator f,
+					     st_select_lex *select_lex):
+  Item_in_subselect()
+{
+  DBUG_ENTER("Item_in_subselect::Item_in_subselect");
+  left_expr= left_exp;
+  func= f;
+  init(thd, select_lex, new select_exists_subselect(this));
   max_columns= UINT_MAX;
   null_value= 0; //can't be NULL
   maybe_null= 0; //can't be NULL
@@ -201,7 +218,6 @@ Item_in_subselect::Item_in_subselect(THD *thd, Item * left_expr,
 void Item_exists_subselect::fix_length_and_dec()
 {
   max_length= 1;
-  
 }
 
 double Item_exists_subselect::val () 
@@ -238,12 +254,20 @@ String *Item_exists_subselect::val_str(String *str)
 Item_in_subselect::Item_in_subselect(Item_in_subselect *item):
   Item_exists_subselect(item)
 {
+  left_expr= item->left_expr;
 }
 
-void Item_in_subselect::select_transformer(st_select_lex *select_lex,
-					   Item *left_expr)
+Item_allany_subselect::Item_allany_subselect(Item_allany_subselect *item):
+  Item_in_subselect(item)
 {
-  DBUG_ENTER("Item_in_subselect::select_transformer");
+  func= item->func;
+}
+
+void Item_in_subselect::single_value_transformer(st_select_lex *select_lex,
+						 Item *left_expr,
+						 compare_func_creator func)
+{
+  DBUG_ENTER("Item_in_subselect::single_value_transformer");
   for(SELECT_LEX * sl= select_lex; sl; sl= sl->next_select())
   {
     Item *item;
@@ -260,28 +284,38 @@ void Item_in_subselect::select_transformer(st_select_lex *select_lex,
     if (sl->having || sl->with_sum_func || sl->group_list.first)
     {
       sl->item_list.push_back(item);
-      item= new Item_ref(sl->item_list.head_ref(), 0, "<result>");
+      item= (*func)(left_expr, new Item_ref(sl->item_list.head_ref(),
+					    0, "<result>"));
       if (sl->having)
-	sl->having= new Item_cond_and(sl->having, 
-				      new Item_func_eq(item, left_expr));
+	sl->having= new Item_cond_and(sl->having, item);
       else
-	sl->having= new Item_func_eq(item, left_expr);
+	sl->having= item;
     }
     else
     {
       sl->item_list.empty();
       sl->item_list.push_back(new Item_int(1));
-      item= new Item_asterisk_remover(item);
+      item= (*func)(left_expr, new Item_asterisk_remover(item));
       if (sl->where)
-	sl->where= new Item_cond_and(sl->where,
-				     new Item_func_eq(item, left_expr));
+	sl->where= new Item_cond_and(sl->where, item);
       else
-	sl->where= new Item_func_eq(item, left_expr);
+	sl->where= item;
     }
   }
   DBUG_VOID_RETURN;
 }
 
+void Item_in_subselect::select_transformer(st_select_lex *select_lex)
+{
+  single_value_transformer(select_lex, left_expr,
+			   &Item_bool_func2::eq_creator);
+}
+
+void Item_allany_subselect::select_transformer(st_select_lex *select_lex)
+{
+  single_value_transformer(select_lex, left_expr, func);
+}
+
 subselect_single_select_engine::subselect_single_select_engine(THD *thd, 
 							       st_select_lex *select,
 							       select_subselect *result,
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 5a5f7bd58d8..3d455b6decf 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -25,6 +25,9 @@ class st_select_lex_unit;
 class JOIN;
 class select_subselect;
 class subselect_engine;
+class Item_bool_func2;
+
+typedef Item_bool_func2* (*compare_func_creator)(Item*, Item*);
 
 /* base class for subselects */
 
@@ -56,15 +59,14 @@ public:
      to subselect Item class to select_subselect classes constructor.
   */
   virtual void init (THD *thd, st_select_lex *select_lex, 
-		     select_subselect *result,
-		     Item *left_expr= 0);
+		     select_subselect *result);
 
   ~Item_subselect();
   virtual void assign_null() 
   {
     null_value= 1;
   }
-  virtual void select_transformer(st_select_lex *select_lex, Item *left_expr);
+  virtual void select_transformer(st_select_lex *select_lex);
   bool assigned() { return value_assigned; }
   void assigned(bool a) { value_assigned= a; }
   enum Type type() const;
@@ -129,8 +131,7 @@ protected:
   longlong value; /* value of this item (boolean: exists/not-exists) */
 
 public:
-  Item_exists_subselect(THD *thd, st_select_lex *select_lex,
-			Item *left_expr= 0);
+  Item_exists_subselect(THD *thd, st_select_lex *select_lex);
   Item_exists_subselect(Item_exists_subselect *item):
     Item_subselect(item)
   {
@@ -156,10 +157,29 @@ public:
 
 class Item_in_subselect :public Item_exists_subselect
 {
+protected:
+  Item * left_expr;
+
 public:
   Item_in_subselect(THD *thd, Item * left_expr, st_select_lex *select_lex);
   Item_in_subselect(Item_in_subselect *item);
-  virtual void select_transformer(st_select_lex *select_lex, Item *left_exp);
+  Item_in_subselect(): Item_exists_subselect() {}
+  virtual void select_transformer(st_select_lex *select_lex);
+  void single_value_transformer(st_select_lex *select_lex,
+				Item *left_expr, compare_func_creator func);
+};
+
+/* ALL/ANY/SOME subselect */
+class Item_allany_subselect :public Item_in_subselect
+{
+protected:
+  compare_func_creator func;
+
+public:
+  Item_allany_subselect(THD *thd, Item * left_expr, compare_func_creator f,
+		     st_select_lex *select_lex);
+  Item_allany_subselect(Item_allany_subselect *item);
+  virtual void select_transformer(st_select_lex *select_lex);
 };
 
 class subselect_engine
diff --git a/sql/lex.h b/sql/lex.h
index 4b56eb4b5d8..744b4c9bf27 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -56,6 +56,7 @@ static SYMBOL symbols[] = {
   { "AGAINST",          SYM(AGAINST),0,0},
   { "ANALYZE",		SYM(ANALYZE_SYM),0,0},
   { "AND",		SYM(AND),0,0},
+  { "ANY",              SYM(ANY_SYM),0,0},
   { "AS",		SYM(AS),0,0},
   { "ASC",		SYM(ASC),0,0},
   { "AVG",		SYM(AVG_SYM),0,0},
@@ -323,6 +324,7 @@ static SYMBOL symbols[] = {
   { "SHUTDOWN",		SYM(SHUTDOWN),0,0},
   { "SLAVE",            SYM(SLAVE),0,0},
   { "SMALLINT",		SYM(SMALLINT),0,0},
+  { "SOME",             SYM(ANY_SYM),0,0},
   { "SONAME",		SYM(UDF_SONAME_SYM),0,0},
   { "SPATIAL",		SYM(SPATIAL_SYM),0,0},
   { "SQL_BIG_RESULT",	SYM(SQL_BIG_RESULT),0,0},
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index c61621bf91d..457dbe7a73d 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -263,6 +263,7 @@ inline THD *_current_thd(void)
 #include "field.h"				/* Field definitions */
 #include "sql_udf.h"
 #include "item.h"
+typedef compare_func_creator (*chooser_compare_func_creator)(bool invert);
 #include "sql_class.h"
 #include "opt_range.h"
 
@@ -867,3 +868,11 @@ inline void mark_as_null_row(TABLE *table)
   table->status|=STATUS_NULL_ROW;
   bfill(table->null_flags,table->null_bytes,255);
 }
+
+compare_func_creator comp_eq_creator(bool invert);
+compare_func_creator comp_ge_creator(bool invert);
+compare_func_creator comp_gt_creator(bool invert);
+compare_func_creator comp_le_creator(bool invert);
+compare_func_creator comp_lt_creator(bool invert);
+compare_func_creator comp_ne_creator(bool invert);
+
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 57325ef19cf..28a2db61f6d 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3706,3 +3706,33 @@ 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;
+}
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 3ac15324ce6..e2e17d98f2c 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -71,6 +71,7 @@ inline Item *or_or_concat(Item* A, Item* B)
   CHARSET_INFO *charset;
   interval_type interval;
   st_select_lex *select_lex;
+  chooser_compare_func_creator boolfunc2creator;
 }
 
 %{
@@ -104,6 +105,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token	AFTER_SYM
 %token	ALTER
 %token	ANALYZE_SYM
+%token	ANY_SYM
 %token	AVG_SYM
 %token	BEGIN_SYM
 %token	BINLOG_SYM
@@ -551,7 +553,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 	type int_type real_type order_dir opt_field_spec lock_option
 	udf_type if_exists opt_local opt_table_options table_options
 	table_option opt_if_not_exists opt_var_type opt_var_ident_type
-	delete_option
+	delete_option all_or_any
 
 %type <ulong_num>
 	ULONG_NUM raid_types merge_insert_types
@@ -616,6 +618,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 
 %type <select_lex> in_subselect in_subselect_init
 
+%type <boolfunc2creator> comp_op
+
 %type <NONE>
 	query verb_clause create change select do drop insert replace insert2
 	insert_values update delete truncate rename
@@ -641,7 +645,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 	opt_mi_check_type opt_to mi_check_types normal_join
 	table_to_table_list table_to_table opt_table_list opt_as
 	handler_rkey_function handler_read_or_scan
-	single_multi table_wild_list table_wild_one opt_wild union union_list
+	single_multi table_wild_list table_wild_one opt_wild union
 	precision union_option opt_on_delete_item subselect_start opt_and
 	subselect_end select_var_list select_var_list_init help
 END_OF_INPUT
@@ -1723,6 +1727,18 @@ optional_braces:
 expr:	expr_expr	{ $$= $1; }
 	| simple_expr	{ $$= $1; };
 
+comp_op:  EQ		{ $$ = &comp_eq_creator; }
+	| GE		{ $$ = &comp_ge_creator; }
+	| GT_SYM	{ $$ = &comp_gt_creator; }
+	| LE		{ $$ = &comp_le_creator; }
+	| LT		{ $$ = &comp_lt_creator; }
+	| NE		{ $$ = &comp_ne_creator; }
+	;
+
+all_or_any: ALL     { $$ = 1; }
+        |   ANY_SYM { $$ = 0; }
+        ;
+
 /* expressions that begin with 'expr' */
 expr_expr:
 	  expr IN_SYM '(' expr_list ')'
@@ -1749,13 +1765,17 @@ expr_expr:
 	| expr NOT REGEXP expr { $$= new Item_func_not(new Item_func_regex($1,$4)); }
 	| expr IS NULL_SYM	{ $$= new Item_func_isnull($1); }
 	| expr IS NOT NULL_SYM { $$= new Item_func_isnotnull($1); }
-	| expr EQ expr		{ $$= new Item_func_eq($1,$3); }
 	| expr EQUAL_SYM expr	{ $$= new Item_func_equal($1,$3); }
-	| expr GE expr		{ $$= new Item_func_ge($1,$3); }
-	| expr GT_SYM expr	{ $$= new Item_func_gt($1,$3); }
-	| expr LE expr		{ $$= new Item_func_le($1,$3); }
-	| expr LT expr		{ $$= new Item_func_lt($1,$3); }
-	| expr NE expr		{ $$= new Item_func_ne($1,$3); }
+	| 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(current_thd, $1, (*$2)($3), $4);
+	  if ($3)
+	    $$ = new Item_func_not(it);	/* ALL */
+	  else
+	    $$ = it;			/* ANY/SOME */
+	}
 	| expr SHIFT_LEFT expr	{ $$= new Item_func_shift_left($1,$3); }
 	| expr SHIFT_RIGHT expr { $$= new Item_func_shift_right($1,$3); }
 	| expr '+' expr		{ $$= new Item_func_plus($1,$3); }
@@ -1789,13 +1809,17 @@ no_in_expr:
 	| no_in_expr NOT REGEXP expr { $$= new Item_func_not(new Item_func_regex($1,$4)); }
 	| no_in_expr IS NULL_SYM	{ $$= new Item_func_isnull($1); }
 	| no_in_expr IS NOT NULL_SYM { $$= new Item_func_isnotnull($1); }
-	| no_in_expr EQ expr		{ $$= new Item_func_eq($1,$3); }
 	| no_in_expr EQUAL_SYM expr	{ $$= new Item_func_equal($1,$3); }
-	| no_in_expr GE expr		{ $$= new Item_func_ge($1,$3); }
-	| no_in_expr GT_SYM expr	{ $$= new Item_func_gt($1,$3); }
-	| no_in_expr LE expr		{ $$= new Item_func_le($1,$3); }
-	| no_in_expr LT expr		{ $$= new Item_func_lt($1,$3); }
-	| no_in_expr NE expr		{ $$= new Item_func_ne($1,$3); }
+	| 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(current_thd, $1, (*$2)($3), $4);
+	  if ($3)
+	    $$ = new Item_func_not(it);	/* ALL */
+	  else
+	    $$ = it;			/* ANY/SOME */
+	}
 	| 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); }
 	| no_in_expr '+' expr		{ $$= new Item_func_plus($1,$3); }
@@ -1837,13 +1861,17 @@ no_and_expr:
 	| no_and_expr NOT REGEXP expr { $$= new Item_func_not(new Item_func_regex($1,$4)); }
 	| no_and_expr IS NULL_SYM	{ $$= new Item_func_isnull($1); }
 	| no_and_expr IS NOT NULL_SYM { $$= new Item_func_isnotnull($1); }
-	| no_and_expr EQ expr		{ $$= new Item_func_eq($1,$3); }
 	| no_and_expr EQUAL_SYM expr	{ $$= new Item_func_equal($1,$3); }
-	| no_and_expr GE expr		{ $$= new Item_func_ge($1,$3); }
-	| no_and_expr GT_SYM expr	{ $$= new Item_func_gt($1,$3); }
-	| no_and_expr LE expr		{ $$= new Item_func_le($1,$3); }
-	| no_and_expr LT expr		{ $$= new Item_func_lt($1,$3); }
-	| no_and_expr NE expr		{ $$= new Item_func_ne($1,$3); }
+	| 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(current_thd, $1, (*$2)($3), $4);
+	  if ($3)
+	    $$ = new Item_func_not(it);	/* ALL */
+	  else
+	    $$ = it;			/* ANY/SOME */
+	}
 	| 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); }
 	| no_and_expr '+' expr		{ $$= new Item_func_plus($1,$3); }
@@ -3495,6 +3523,7 @@ keyword:
 	| AFTER_SYM		{}
 	| AGAINST		{}
 	| AGGREGATE_SYM		{}
+	| ANY_SYM		{}
 	| AUTO_INC		{}
 	| AVG_ROW_LENGTH	{}
 	| AVG_SYM		{}
@@ -4173,10 +4202,7 @@ rollback:
 
 union:	
 	/* empty */ {}
-	| union_list;
-
-union_list:
-	UNION_SYM    union_option
+	|UNION_SYM    union_option
 	{
 	  LEX *lex=Lex;
 	  if (lex->exchange)