diff options
| -rw-r--r-- | mysql-test/r/subselect.result | 26 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 16 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 22 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 2 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 6 | ||||
| -rw-r--r-- | sql/item_subselect.h | 9 | ||||
| -rw-r--r-- | sql/mysql_priv.h | 4 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 2 |
8 files changed, 76 insertions, 11 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index ae929cf9c2e..8b10402c823 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2888,6 +2888,32 @@ select 1 from dual where 1 < any (select 2 from dual); select 1 from dual where 1 < all (select 2 from dual where 1!=1); 1 1 +create table t1 (s1 char); +insert into t1 values (1),(2); +select * from t1 where (s1 < any (select s1 from t1)); +s1 +1 +select * from t1 where not (s1 < any (select s1 from t1)); +s1 +2 +select * from t1 where (s1 < ALL (select s1+1 from t1)); +s1 +1 +select * from t1 where not(s1 < ALL (select s1+1 from t1)); +s1 +2 +select * from t1 where (s1+1 = ANY (select s1 from t1)); +s1 +1 +select * from t1 where NOT(s1+1 = ANY (select s1 from t1)); +s1 +2 +select * from t1 where (s1 = ALL (select s1/s1 from t1)); +s1 +1 +select * from t1 where NOT(s1 = ALL (select s1/s1 from t1)); +s1 +2 create table t1 (df decimal(5,1)); insert into t1 values(1.1); insert into t1 values(2.2); diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 8bf8337714f..a45d5fc2e43 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1851,6 +1851,22 @@ select 1 from dual where 2 > any (select 1); select 1 from dual where 2 > all (select 1); select 1 from dual where 1 < any (select 2 from dual); select 1 from dual where 1 < all (select 2 from dual where 1!=1); + +# BUG#20975 Wrong query results for subqueries within NOT +create table t1 (s1 char); +insert into t1 values (1),(2); + +select * from t1 where (s1 < any (select s1 from t1)); +select * from t1 where not (s1 < any (select s1 from t1)); + +select * from t1 where (s1 < ALL (select s1+1 from t1)); +select * from t1 where not(s1 < ALL (select s1+1 from t1)); + +select * from t1 where (s1+1 = ANY (select s1 from t1)); +select * from t1 where NOT(s1+1 = ANY (select s1 from t1)); + +select * from t1 where (s1 = ALL (select s1/s1 from t1)); +select * from t1 where NOT(s1 = ALL (select s1/s1 from t1)); # End of 4.1 tests # diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 98453899375..34170124cd7 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -3656,6 +3656,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 a2b10eacc79..47f9f2aa98f 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -311,6 +311,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); }; @@ -321,6 +322,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 5404021a348..3c279d827b2 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -572,14 +572,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 293408dc09e..45df4f3880d 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -269,14 +269,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 c9ae743addd..619755fc6ff 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -520,9 +520,9 @@ enum enum_var_type OPT_DEFAULT= 0, OPT_SESSION, OPT_GLOBAL }; class sys_var; -#include "item.h" -extern my_decimal decimal_zero; +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 6dbd6623264..ce285e8d876 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -7005,7 +7005,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 */ |