summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result12
-rw-r--r--sql/item_cmpfunc.cc33
-rw-r--r--sql/item_subselect.cc4
-rw-r--r--sql/item_subselect.h4
-rw-r--r--sql/mysql_priv.h1
-rw-r--r--sql/mysqld.cc2
-rw-r--r--sql/sql_select.cc16
7 files changed, 43 insertions, 29 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index defb88a6ade..d3a2ecf12cb 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -828,7 +828,7 @@ a t1.a in (select t2.a from t2)
explain SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index
-2 DEPENDENT SUBQUERY t2 index_in a a 5 const 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index_in a a 5 func 2 Using where; Using index
CREATE TABLE t3 (a int(11) default '0');
INSERT INTO t3 VALUES (1),(2),(3);
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
@@ -840,7 +840,7 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
explain SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 const 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where
drop table t1,t2,t3;
create table t1 (a float);
@@ -1272,7 +1272,7 @@ a
explain select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_in a a 5 func 100 Using index
+2 DEPENDENT SUBQUERY t1 index_in a a 5 func 1001 Using index
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
@@ -1280,7 +1280,7 @@ a
explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_in a a 5 func 100 Using index; Using where
+2 DEPENDENT SUBQUERY t1 index_in a a 5 func 1001 Using index; Using where
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
@@ -1289,7 +1289,7 @@ explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 Using index
-2 DEPENDENT SUBQUERY t1 ref a a 10 func,test.t3.a 100 Using where; Using index
+2 DEPENDENT SUBQUERY t1 ref a a 10 func,test.t3.a 1000 Using where; Using index
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
@@ -1303,7 +1303,7 @@ a
explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_in a a 5 func 100 Using index; Using where
+2 DEPENDENT SUBQUERY t1 index_in a a 5 func 1001 Using index; Using where
drop table t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 5b19e4b745b..9104435014e 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -380,22 +380,7 @@ bool Item_in_optimizer::fix_left(THD *thd,
(!cache && !(cache= Item_cache::get_cache(args[0]->result_type()))))
return 1;
cache->setup(args[0]);
- return 0;
-}
-
-
-
-bool Item_in_optimizer::fix_fields(THD *thd, struct st_table_list *tables,
- Item ** ref)
-{
- if (fix_left(thd, tables, ref))
- return 1;
- if (args[0]->maybe_null)
- maybe_null=1;
-
- with_sum_func= args[0]->with_sum_func;
- used_tables_cache= args[0]->used_tables();
- const_item_cache= args[0]->const_item();
+ cache->store(args[0]);
if (cache->cols() == 1)
{
if (args[0]->used_tables())
@@ -414,6 +399,22 @@ bool Item_in_optimizer::fix_fields(THD *thd, struct st_table_list *tables,
((Item_cache *)cache->el(i))->set_used_tables(0);
}
}
+ return 0;
+}
+
+
+
+bool Item_in_optimizer::fix_fields(THD *thd, struct st_table_list *tables,
+ Item ** ref)
+{
+ if (fix_left(thd, tables, ref))
+ return 1;
+ if (args[0]->maybe_null)
+ maybe_null=1;
+
+ with_sum_func= args[0]->with_sum_func;
+ used_tables_cache= args[0]->used_tables();
+ const_item_cache= args[0]->const_item();
if (!args[1]->fixed && args[1]->fix_fields(thd, tables, args))
return 1;
Item_in_subselect * sub= (Item_in_subselect *)args[1];
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 68655d51521..c0171614fae 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -518,7 +518,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
*/
expr= new Item_ref((Item**)optimizer->get_cache(),
(char *)"<no matter>",
- (char *)"<left expr>");
+ (char *)in_left_expr_name);
unit->dependent= 1;
}
@@ -694,7 +694,7 @@ Item_in_subselect::row_value_transformer(JOIN *join,
Item_bool_func2::eq_creator(new Item_ref((*optimizer->get_cache())->
addr(i),
(char *)"<no matter>",
- (char *)"<left expr>"),
+ (char *)in_left_expr_name),
func);
item= and_items(item, func);
}
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index e2738102ebd..dc26ec3fbca 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -192,7 +192,7 @@ public:
class Item_in_subselect :public Item_exists_subselect
{
protected:
- Item * left_expr;
+ Item *left_expr;
/*
expr & optimizer used in subselect rewriting to store Item for
all JOIN in UNION
@@ -204,7 +204,7 @@ protected:
public:
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) {}
+ Item_in_subselect(): Item_exists_subselect(), abort_on_null(0) {}
subs_type substype() { return IN_SUBS; }
void reset()
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 05a2cf4c7f8..204a7cb7b1a 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -705,6 +705,7 @@ extern const char *command_name[];
extern const char *first_keyword, *localhost, *delayed_user, *binary_keyword;
extern const char **errmesg; /* Error messages */
extern const char *myisam_recover_options_str;
+extern const char *in_left_expr_name;
extern uchar *days_in_month;
extern char language[LIBLEN],reg_ext[FN_EXTLEN];
extern char glob_hostname[FN_REFLEN], mysql_home[FN_REFLEN];
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 8ddcbdc572f..379e5aa8ea3 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -316,6 +316,8 @@ char *my_bind_addr_str;
const char **errmesg; /* Error messages */
const char *myisam_recover_options_str="OFF";
const char *sql_mode_str="OFF";
+/* name of reference on left espression in rewritten IN subquery */
+const char *in_left_expr_name= "<left expr>";
FILE *bootstrap_file;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ad0e30d0739..5b20f4f7a5d 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -772,7 +772,8 @@ JOIN::optimize()
if (!having)
{
Item *where= 0;
- if (join_tab[0].type == JT_EQ_REF)
+ if (join_tab[0].type == JT_EQ_REF &&
+ join_tab[0].ref.items[0]->name == in_left_expr_name)
{
if (test_in_subselect(&where))
{
@@ -785,7 +786,8 @@ JOIN::optimize()
where)));
}
}
- else if (join_tab[0].type == JT_REF)
+ else if (join_tab[0].type == JT_REF &&
+ join_tab[0].ref.items[0]->name == in_left_expr_name)
{
if (test_in_subselect(&where))
{
@@ -800,6 +802,7 @@ JOIN::optimize()
}
}
} else if (join_tab[0].type == JT_REF_OR_NULL &&
+ join_tab[0].ref.items[0]->name == in_left_expr_name &&
having->type() == Item::FUNC_ITEM &&
((Item_func *) having)->functype() ==
Item_func::ISNOTNULLTEST_FUNC)
@@ -2347,7 +2350,8 @@ sort_keyuse(KEYUSE *a,KEYUSE *b)
if (a->keypart != b->keypart)
return (int) (a->keypart - b->keypart);
// Place const values before other ones
- if ((res= test(a->used_tables) - test(b->used_tables)))
+ if ((res= test((a->used_tables & ~OUTER_REF_TABLE_BIT)) -
+ test((b->used_tables & ~OUTER_REF_TABLE_BIT))))
return res;
/* Place rows that are not 'OPTIMIZE_REF_OR_NULL' first */
return (int) ((a->optimize & KEY_OPTIMIZE_REF_OR_NULL) -
@@ -2478,6 +2482,12 @@ static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array)
keyuse->ref_table_rows= max(tmp_table->file->records, 100);
}
}
+ /*
+ Outer reference (external field) is constant for single executing
+ of subquery
+ */
+ if (keyuse->used_tables == OUTER_REF_TABLE_BIT)
+ keyuse->ref_table_rows= 1;
}
}
View Lorry Controller Status