summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result17
-rw-r--r--mysql-test/t/subselect.test14
-rw-r--r--sql/item_cmpfunc.cc16
-rw-r--r--sql/item_cmpfunc.h10
-rw-r--r--sql/item_subselect.cc77
-rw-r--r--sql/item_subselect.h7
-rw-r--r--sql/item_sum.cc3
-rw-r--r--sql/sql_union.cc1
-rw-r--r--sql/sql_yacc.yy6
9 files changed, 130 insertions, 21 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index e941c115b2c..e06f9b48a51 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -247,6 +247,10 @@ select * from t3 where a >= any (select b from t2);
a
6
7
+explain select * from t3 where a >= any (select b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 3
select * from t3 where a >= all (select b from t2);
a
7
@@ -1317,3 +1321,16 @@ a (select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 an
2 2
1 2
drop table t1,t2,t3;
+create table t2 (a int, b int);
+create table t3 (a int);
+insert into t3 values (6),(7),(3);
+select * from t3 where a >= all (select b from t2);
+a
+6
+7
+3
+explain select * from t3 where a >= all (select b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+drop table if exists t2, t3;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 9ceed286063..862f0272ae9 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -100,6 +100,7 @@ insert into t2 values (100, 5);
select * from t3 where a < any (select b from t2);
select * from t3 where a < all (select b from t2);
select * from t3 where a >= any (select b from t2);
+explain select * from t3 where a >= any (select b from t2);
select * from t3 where a >= all (select b from t2);
delete from t2 where a=100;
-- error 1239
@@ -854,6 +855,7 @@ select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
drop table t1, t2, t3;
+
#
# alloc_group_fields() working
#
@@ -864,4 +866,14 @@ insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
insert into t3 values (3,3), (2,2), (1,1);
select a,(select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1) from t3;
-drop table t1,t2,t3;s
+drop table t1,t2,t3;
+
+#
+# correct ALL optimisation
+#
+create table t2 (a int, b int);
+create table t3 (a int);
+insert into t3 values (6),(7),(3);
+select * from t3 where a >= all (select b from t2);
+explain select * from t3 where a >= all (select b from t2);
+drop table if exists t2, t3;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index e8d3cba3ddb..f77ce039dd6 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -97,6 +97,22 @@ longlong Item_func_not::val_int()
}
/*
+ special NOT for ALL subquery
+*/
+
+longlong Item_func_not_all::val_int()
+{
+ double value= args[0]->val();
+ if (abort_on_null)
+ {
+ null_value= 0;
+ return (args[0]->null_value || value == 0) ? 1 : 0;
+ }
+ null_value= args[0]->null_value;
+ return (!null_value && value == 0) ? 1 : 0;
+}
+
+/*
Convert a constant expression or string to an integer.
This is done when comparing DATE's of different formats and
also when comparing bigint to strings (in which case the string
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 44e1cf99511..ea652353828 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -155,6 +155,16 @@ public:
const char *func_name() const { return "not"; }
};
+class Item_func_not_all :public Item_func_not
+{
+ bool abort_on_null;
+public:
+ Item_func_not_all(Item *a) :Item_func_not(a), abort_on_null(0) {}
+ virtual void top_level_item() { abort_on_null= 1; }
+ bool top_level() { return abort_on_null; }
+ longlong val_int();
+};
+
class Item_func_eq :public Item_bool_rowready_func2
{
public:
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 3d2a88d29e5..ff9ca728b3d 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -357,7 +357,7 @@ bool Item_in_subselect::test_limit(SELECT_LEX_UNIT *unit)
Item_in_subselect::Item_in_subselect(THD *thd, Item * left_exp,
st_select_lex *select_lex):
- Item_exists_subselect()
+ Item_exists_subselect(), upper_not(0)
{
DBUG_ENTER("Item_in_subselect::Item_in_subselect");
left_expr= left_exp;
@@ -373,8 +373,8 @@ Item_in_subselect::Item_in_subselect(THD *thd, Item * left_exp,
Item_allany_subselect::Item_allany_subselect(THD *thd, Item * left_exp,
compare_func_creator fn,
- st_select_lex *select_lex):
- Item_in_subselect()
+ st_select_lex *select_lex)
+ :Item_in_subselect()
{
DBUG_ENTER("Item_in_subselect::Item_in_subselect");
left_expr= left_exp;
@@ -471,17 +471,16 @@ String *Item_in_subselect::val_str(String *str)
}
Item_in_subselect::Item_in_subselect(Item_in_subselect *item):
- Item_exists_subselect(item)
+ Item_exists_subselect(item), upper_not(item->upper_not)
{
left_expr= item->left_expr;
abort_on_null= item->abort_on_null;
}
-Item_allany_subselect::Item_allany_subselect(Item_allany_subselect *item):
- Item_in_subselect(item)
-{
- func= item->func;
-}
+Item_allany_subselect::Item_allany_subselect(Item_allany_subselect *item)
+ :Item_in_subselect(item),
+ func(item->func)
+{}
Item_subselect::trans_res
Item_in_subselect::single_value_transformer(JOIN *join,
@@ -495,6 +494,57 @@ Item_in_subselect::single_value_transformer(JOIN *join,
THD *thd= join->thd;
thd->where= "scalar IN/ALL/ANY subquery";
+ if ((abort_on_null || (upper_not && upper_not->top_level())) &&
+ !select_lex->master_unit()->dependent &&
+ (func == &Item_bool_func2::gt_creator ||
+ func == &Item_bool_func2::lt_creator ||
+ func == &Item_bool_func2::ge_creator ||
+ func == &Item_bool_func2::le_creator) &&
+ !select_lex->group_list.elements &&
+ !select_lex->with_sum_func)
+ {
+ Item *item;
+ subs_type type= substype();
+ if (func == &Item_bool_func2::le_creator ||
+ func == &Item_bool_func2::lt_creator)
+ {
+ /*
+ (ALL && (> || =>)) || (ANY && (< || =<))
+ for ALL condition is inverted
+ */
+ item= new Item_sum_max(*select_lex->ref_pointer_array);
+ }
+ else
+ {
+ /*
+ (ALL && (< || =<)) || (ANY && (> || =>))
+ for ALL condition is inverted
+ */
+ item= new Item_sum_min(*select_lex->ref_pointer_array);
+ }
+ *select_lex->ref_pointer_array= item;
+ select_lex->item_list.empty();
+ select_lex->item_list.push_back(item);
+
+ if (item->fix_fields(thd, join->tables_list, &item))
+ {
+ DBUG_RETURN(ERROR);
+ }
+
+ // left expression belong to outer select
+ SELECT_LEX *current= thd->lex.current_select, *up;
+ thd->lex.current_select= up= current->return_after_parsing();
+ if (left_expr->fix_fields(thd, up->get_table_list(), 0))
+ {
+ thd->lex.current_select= current;
+ DBUG_RETURN(ERROR);
+ }
+ thd->lex.current_select= current;
+ substitution= (*func)(left_expr,
+ new Item_singlerow_subselect(thd, select_lex));
+ DBUG_RETURN(OK);
+ }
+
if (!substitution)
{
//first call for this unit
@@ -736,10 +786,11 @@ Item_allany_subselect::select_transformer(JOIN *join)
return single_value_transformer(join, left_expr, func);
}
-subselect_single_select_engine::subselect_single_select_engine(THD *thd,
- st_select_lex *select,
- select_subselect *result,
- Item_subselect *item):
+subselect_single_select_engine::
+ subselect_single_select_engine(THD *thd,
+ st_select_lex *select,
+ select_subselect *result,
+ Item_subselect *item):
subselect_engine(thd, item, result),
prepared(0), optimized(0), executed(0)
{
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index e2738102ebd..65039589da4 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -53,7 +53,7 @@ public:
enum trans_res {OK, REDUCE, ERROR};
enum subs_type {UNKNOWN_SUBS, SINGLEROW_SUBS,
- EXISTS_SUBS, IN_SUBS, ALLANY_SUBS};
+ EXISTS_SUBS, IN_SUBS, ALL_SUBS, ANY_SUBS};
Item_subselect();
Item_subselect(Item_subselect *item)
@@ -202,6 +202,8 @@ protected:
bool was_null;
bool abort_on_null;
public:
+ Item_func_not_all *upper_not; // point on NOT before ALL subquery
+
Item_in_subselect(THD *thd, Item * left_expr, st_select_lex *select_lex);
Item_in_subselect(Item_in_subselect *item);
Item_in_subselect(): Item_exists_subselect(), abort_on_null(0) {}
@@ -241,7 +243,8 @@ 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);
- subs_type substype() { return ALLANY_SUBS; }
+ // only ALL subquery has upper not
+ subs_type substype() { return upper_not?ALL_SUBS:ANY_SUBS; }
trans_res select_transformer(JOIN *join);
};
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index fde39135358..9cb1df80e97 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -179,7 +179,8 @@ Item_sum_hybrid::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
return 1;
}
thd->allow_sum_func=0; // No included group funcs
- if (item->fix_fields(thd, tables, args) || item->check_cols(1))
+ if (!item->fixed &&
+ item->fix_fields(thd, tables, args) || item->check_cols(1))
return 1;
hybrid_type=item->result_type();
if (hybrid_type == INT_RESULT)
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 0866163b3fd..04a5904b5f7 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -290,7 +290,6 @@ int st_select_lex_unit::exec()
view we should do here same procedura as it was done by
setup_table
*/
- DBUG_PRINT("SUBS", ("shared %s", table_list->real_name));
setup_table_map(table_list->table, table_list, tablenr);
}
}
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index da7cde6c316..743f3b0d53b 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -2140,7 +2140,7 @@ expr_expr:
Item_allany_subselect *it=
new Item_allany_subselect(YYTHD, $1, (*$2)($3), $4);
if ($3)
- $$ = new Item_func_not(it); /* ALL */
+ $$ = it->upper_not= new Item_func_not_all(it); /* ALL */
else
$$ = it; /* ANY/SOME */
}
@@ -2186,7 +2186,7 @@ no_in_expr:
Item_allany_subselect *it=
new Item_allany_subselect(YYTHD, $1, (*$2)($3), $4);
if ($3)
- $$ = new Item_func_not(it); /* ALL */
+ $$ = it->upper_not= new Item_func_not_all(it); /* ALL */
else
$$ = it; /* ANY/SOME */
}
@@ -2241,7 +2241,7 @@ no_and_expr:
Item_allany_subselect *it=
new Item_allany_subselect(YYTHD, $1, (*$2)($3), $4);
if ($3)
- $$ = new Item_func_not(it); /* ALL */
+ $$ = it->upper_not= new Item_func_not_all(it); /* ALL */
else
$$ = it; /* ANY/SOME */
}
View Lorry Controller Status