BUG#20975: Incorrect query result for NOT (subquery):

  Add implementations of Item_func_{nop,not}_all::neg_transformer


mysql-test/r/subselect.result:
  BUG#20975: testcase
mysql-test/t/subselect.test:
  BUG#20975: testcase
sql/mysql_priv.h:
  Make chooser_compare_func_creator visible in item.h

6 files changed, 34 insertions, 10 deletions

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index f14efc7187b..a32bd0a7337 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -3099,6 +3099,28 @@ Item *Item_cond_or::neg_transformer(THD *thd)	/* NOT(a OR b OR ...)  -> */
 }
 
 
+Item *Item_func_nop_all::neg_transformer(THD *thd)
+{
+  /* "NOT (e $cmp$ ANY (SELECT ...)) -> e $rev_cmp$" ALL (SELECT ...) */
+  Item_func_not_all *new_item= new Item_func_not_all(args[0]);
+  Item_allany_subselect *allany= (Item_allany_subselect*)args[0];
+  allany->func= allany->func_creator(FALSE);
+  allany->all= !allany->all;
+  allany->upper_item= new_item;
+  return new_item;
+}
+
+Item *Item_func_not_all::neg_transformer(THD *thd)
+{
+  /* "NOT (e $cmp$ ALL (SELECT ...)) -> e $rev_cmp$" ANY (SELECT ...) */
+  Item_func_nop_all *new_item= new Item_func_nop_all(args[0]);
+  Item_allany_subselect *allany= (Item_allany_subselect*)args[0];
+  allany->all= !allany->all;
+  allany->func= allany->func_creator(TRUE);
+  allany->upper_item= new_item;
+  return new_item;
+}
+
 Item *Item_func_eq::negated_item()		/* a = b  ->  a != b */
 {
   return new Item_func_ne(args[0], args[1]);
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 73abe208d9e..0e157fd412c 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -268,6 +268,7 @@ public:
   void set_sum_test(Item_sum_hybrid *item) { test_sum_item= item; };
   void set_sub_test(Item_maxmin_subselect *item) { test_sub_item= item; };
   bool empty_underlying_subquery();
+  Item *neg_transformer(THD *thd);
 };
 
 
@@ -278,6 +279,7 @@ public:
   Item_func_nop_all(Item *a) :Item_func_not_all(a) {}
   longlong val_int();
   const char *func_name() const { return "<nop>"; }
+  Item *neg_transformer(THD *thd);
 };
 
 
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index f6f8eec9af5..c95a91de13e 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -542,14 +542,14 @@ Item_in_subselect::Item_in_subselect(Item * left_exp,
 }
 
 Item_allany_subselect::Item_allany_subselect(Item * left_exp,
-					     Comp_creator *fn,
+                                             chooser_compare_func_creator fc,
 					     st_select_lex *select_lex,
 					     bool all_arg)
-  :Item_in_subselect(), all(all_arg)
+  :Item_in_subselect(), all(all_arg), func_creator(fc)
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
   left_expr= left_exp;
-  func= fn;
+  func= func_creator(all_arg);
   init(select_lex, new select_exists_subselect(this));
   max_columns= 1;
   abort_on_null= 0;
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index dec32398a80..93171ad64a1 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -251,14 +251,13 @@ public:
 /* ALL/ANY/SOME subselect */
 class Item_allany_subselect :public Item_in_subselect
 {
-protected:
-  Comp_creator *func;
-
 public:
+  chooser_compare_func_creator func_creator;
+  Comp_creator *func;
   bool all;
 
-  Item_allany_subselect(Item * left_expr, Comp_creator *f,
-		     st_select_lex *select_lex, bool all);
+  Item_allany_subselect(Item * left_expr, chooser_compare_func_creator fc,
+                        st_select_lex *select_lex, bool all);
 
   // only ALL subquery has upper not
   subs_type substype() { return all?ALL_SUBS:ANY_SUBS; }
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index d231942bb7a..d03c6acac7c 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -387,8 +387,9 @@ enum enum_var_type
   OPT_DEFAULT, OPT_SESSION, OPT_GLOBAL
 };
 class sys_var;
-#include "item.h"
+class Comp_creator;
 typedef Comp_creator* (*chooser_compare_func_creator)(bool invert);
+#include "item.h"
 /* sql_parse.cc */
 void free_items(Item *item);
 void cleanup_items(Item *item);
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index fbe36bfdc4a..660c77e81e4 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -5436,7 +5436,7 @@ Item * all_any_subquery_creator(Item *left_expr,
     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, all);
+    new Item_allany_subselect(left_expr, cmp, select_lex, all);
   if (all)
     return it->upper_item= new Item_func_not_all(it);	/* ALL */