Below is the list of changes that have just been committed into a local
5.0 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-01-12 21:17:47+03:00, sergefp@stripped +9 -0
BUG#24127: Post-review fixes part 3:
mysql-test/r/ndb_subquery.result@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +3 -3
BUG#24127: Post-review fixes part 3:
- Updated test results
mysql-test/r/subselect3.result@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +324 -0
BUG#24127: Post-review fixes part 3:
- Add more testcases
mysql-test/t/subselect3.test@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +202 -0
BUG#24127: Post-review fixes part 3:
- Add more testcases
sql/item_cmpfunc.cc@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +7 -6
BUG#24127: Post-review fixes part 3:
- Replace Item_in_subselect::enable_pushed_conds access with
set_cond_guard_var() calls
sql/item_cmpfunc.h@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +2 -1
BUG#24127: Post-review fixes part 3:
sql/item_subselect.cc@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +20 -32
BUG#24127: Post-review fixes part 3:
sql/item_subselect.h@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +12 -6
BUG#24127: Post-review fixes part 3:
sql/sql_select.cc@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +12 -11
BUG#24127: Post-review fixes part 3:
sql/sql_select.h@stripped, 2007-01-12 21:17:44+03:00, sergefp@stripped +17 -11
BUG#24127: Post-review fixes part 3:
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: sergefp
# Host: pylon.mylan
# Root: /home/psergey/mysql-5.0-bug8804-r11
--- 1.226/sql/item_cmpfunc.cc 2007-01-12 21:17:52 +03:00
+++ 1.227/sql/item_cmpfunc.cc 2007-01-12 21:17:52 +03:00
@@ -816,30 +816,31 @@
Item_in_subselect *item_subs=(Item_in_subselect*)args[1];
if (cache->cols() == 1)
{
- item_subs->enable_pushed_conds= FALSE;
+ item_subs->set_cond_guard_var(0, FALSE);
longlong tmp= args[1]->val_bool_result();
result_for_null_param= null_value= !item_subs->engine->no_rows();
- item_subs->enable_pushed_conds= TRUE;
+ item_subs->set_cond_guard_var(0, TRUE);
}
else
{
+ uint i;
uint ncols= cache->cols();
/*
Turn off the predicates that are based on column compares for
which the left part is currently NULL
*/
- for (uint i= 0; i < ncols; i++)
+ for (i= 0; i < ncols; i++)
{
if (cache->el(i)->null_value)
- item_subs->enable_pushed_conds_vec[i]= FALSE;
+ item_subs->set_cond_guard_var(i, FALSE);
}
longlong tmp= args[1]->val_bool_result();
result_for_null_param= null_value= !item_subs->engine->no_rows();
/* Turn all predicates back on */
- for (uint i= 0; i < ncols; i++)
- item_subs->enable_pushed_conds_vec[i]= TRUE;
+ for (i= 0; i < ncols; i++)
+ item_subs->set_cond_guard_var(i, TRUE);
}
}
}
--- 1.137/sql/item_cmpfunc.h 2007-01-12 21:17:52 +03:00
+++ 1.138/sql/item_cmpfunc.h 2007-01-12 21:17:52 +03:00
@@ -304,13 +304,14 @@
class Item_func_trig_cond: public Item_bool_func
{
-public:
bool *trig_var;
+public:
Item_func_trig_cond(Item *a, bool *f) : Item_bool_func(a) { trig_var= f; }
longlong val_int() { return *trig_var ? args[0]->val_int() : 1; }
enum Functype functype() const { return TRIG_COND_FUNC; };
const char *func_name() const { return "trigcond"; };
bool const_item() const { return FALSE; }
+ bool *get_trig_var() { return trig_var; }
};
class Item_func_not_all :public Item_func_not
--- 1.479/sql/sql_select.cc 2007-01-12 21:17:52 +03:00
+++ 1.480/sql/sql_select.cc 2007-01-12 21:17:52 +03:00
@@ -529,14 +529,14 @@
fact that subquery engine will be using index lookup.
If the subquery compares scalar values, we can remove the condition that
- was wrapped into trig_cond.
+ was wrapped into trig_cond (it will be checked when needed by the subquery
+ engine)
- If the subquery compares row values, we need to keep the wrapped
- equalities in the WHERE clause: when some parts of the left tuple are
- NULLs and some aren't, we'll use full table scan and will rely on the
- equalities for non-NULL tuple parts to be guaranteed to be true.
-
- psergey-todo: ^ clarify ^.
+ If the subquery compares row values, we need to keep the wrapped
+ equalities in the WHERE clause: when the left (outer) tuple has both NULL
+ and non-NULL values, we'll do a full table scan and will rely on the
+ equalities corresponding to non-NULL parts of left tuple to filter out
+ non-matching records.
*/
void JOIN::remove_subq_pushed_predicates(Item **where)
@@ -2965,8 +2965,9 @@
}
/*
- Subquery optimization: check if the encountered condition is one
- added by condition push down into subquery. (psergey-todo: better comment)
+ Subquery optimization: Conditions that are pushed down into subqueries
+ are wrapped into Item_func_trig_cond. We process the wrapped condition
+ but need to set cond_guard for KEYUSE elements generated from it.
*/
{
if (cond->type() == Item::FUNC_ITEM &&
@@ -2983,7 +2984,7 @@
sargables);
// Indicate that this ref access candidate is for subquery lookup:
for (; save != *key_fields; save++)
- save->cond_guard= (((Item_func_trig_cond*)cond)->trig_var);
+ save->cond_guard= ((Item_func_trig_cond*)cond)->get_trig_var();
}
return;
}
@@ -7667,7 +7668,7 @@
SYNOPSIS
remove_additional_cond()
- conds - condition for processing
+ conds Condition for processing
RETURN VALUES
new conditions
--- 1.115/sql/sql_select.h 2007-01-12 21:17:52 +03:00
+++ 1.116/sql/sql_select.h 2007-01-12 21:17:52 +03:00
@@ -36,13 +36,14 @@
*/
bool null_rejecting;
/*
- > 0 - This element was created from a triggered condition (which will
- be turned of for "NULL IN (SELECT ...)" that was pushed down into
- a subselect. The number is the number of column, e.g. for
- (a, b) IN (SELECT x, keypart2 ...)
- we'll get a KEYUSE created for "keypart2=b" with outer_ref_col == 1.
- psergey-todo: fix comment
- UINT_MAX - Otherwise
+ !NULL - This KEYUSE was created from an equality that was wrapped into
+ an Item_func_trig_cond. This means the equality (and validity of
+ this KEYUSE element) can be turned on and off. The on/off state
+ is indicted by the pointed value:
+ *cond_guard == TRUE <=> equality condition is on
+ *cond_guard == FALSE <=> equality condition is off
+
+ NULL - Otherwise (the source equality can't be turned off)
*/
bool *cond_guard;
} KEYUSE;
@@ -60,10 +61,15 @@
store_key **key_copy; //
Item **items; // val()'s for each keypart
/*
- Array of numbers of pushed-down subq predicates that were used to
- construct equalities on keypart #i. If equality on keypart #i was not
- constructed from pushed-down predicate, outer_ref_col[i]==UINT_MAX.
- psergey-todo: change-comment!
+ Array of pointers to trigger variables. Some/all of the pointers may be
+ NULL. The ref access can be used iff
+
+ for each used key part i, (!cond_guards[i] || *cond_guards[i])
+
+ This array is used by subquery code. The subquery code may inject
+ triggered conditions, i.e. conditions that can be 'switched off'. A ref
+ access created from such condition is not valid when at least one of the
+ underlying conditions is switched off (see subquery code for more details)
*/
bool **cond_guards;
/*
--- 1.146/sql/item_subselect.cc 2007-01-12 21:17:52 +03:00
+++ 1.147/sql/item_subselect.cc 2007-01-12 21:17:52 +03:00
@@ -565,8 +565,7 @@
Item_in_subselect::Item_in_subselect(Item * left_exp,
st_select_lex *select_lex):
Item_exists_subselect(), optimizer(0), transformed(0),
- enable_pushed_conds(TRUE), enable_pushed_conds_vec(&enable_pushed_conds),
- upper_item(0)
+ pushed_cond_guards(NULL), upper_item(0)
{
DBUG_ENTER("Item_in_subselect::Item_in_subselect");
left_expr= left_exp;
@@ -950,20 +949,13 @@
unit->uncacheable|= UNCACHEABLE_DEPENDENT;
}
if (!abort_on_null && left_expr->maybe_null)
- enable_pushed_conds_vec= &enable_pushed_conds;
+ {
+ if (!(pushed_cond_guards= (bool*)join->thd->alloc(sizeof(bool))))
+ DBUG_RETURN(RES_ERROR);
+ pushed_cond_guards[0]= TRUE;
+ }
select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
- /*
- Add the left part of a subselect to a WHERE or HAVING clause of
- the right part, e.g.
-
- SELECT 1 IN (SELECT a FROM t1) =>
-
- SELECT Item_in_optimizer(1, SELECT a FROM t1 WHERE a=1)
-
- HAVING is used only if the right part contains a SUM function, a GROUP
- BY or a HAVING clause.
- */
if (join->having || select_lex->with_sum_func ||
select_lex->group_list.elements)
{
@@ -981,7 +973,7 @@
We can encounter "NULL IN (SELECT ...)". Wrap the added condition
within a trig_cond.
*/
- item= new Item_func_trig_cond(item, &enable_pushed_conds);
+ item= new Item_func_trig_cond(item, get_cond_guard(0));
}
/*
@@ -1022,7 +1014,7 @@
if (left_expr->maybe_null)
{
if (!(having= new Item_func_trig_cond(having,
- &enable_pushed_conds)))
+ get_cond_guard(0))))
DBUG_RETURN(RES_ERROR);
}
/*
@@ -1051,7 +1043,7 @@
*/
if (!abort_on_null && left_expr->maybe_null)
{
- if (!(item= new Item_func_trig_cond(item, &enable_pushed_conds)))
+ if (!(item= new Item_func_trig_cond(item, get_cond_guard(0))))
DBUG_RETURN(RES_ERROR);
}
/*
@@ -1094,7 +1086,7 @@
if (!abort_on_null && left_expr->maybe_null)
{
if (!(new_having= new Item_func_trig_cond(new_having,
- &enable_pushed_conds)))
+ get_cond_guard(0))))
DBUG_RETURN(RES_ERROR);
}
new_having->name= (char*)in_having_cond;
@@ -1173,11 +1165,11 @@
if (!abort_on_null && left_expr->maybe_null)
{
- if (!(enable_pushed_conds_vec= (bool*)join->thd->alloc(sizeof(bool) *
- left_expr->cols())))
+ if (!(pushed_cond_guards= (bool*)join->thd->alloc(sizeof(bool) *
+ left_expr->cols())))
DBUG_RETURN(RES_ERROR);
for (uint i= 0; i < cols_num; i++)
- enable_pushed_conds_vec[i]= TRUE;
+ pushed_cond_guards[i]= TRUE;
}
}
@@ -1227,8 +1219,7 @@
Item *col_item= new Item_cond_or(item_eq, item_isnull);
if (!abort_on_null && left_expr->el(i)->maybe_null)
{
- if (!(col_item= new Item_func_trig_cond(col_item,
- enable_pushed_conds_vec + i)))
+ if (!(col_item= new Item_func_trig_cond(col_item, get_cond_guard(i))))
DBUG_RETURN(RES_ERROR);
}
having_item= and_items(having_item, col_item);
@@ -1243,8 +1234,7 @@
if (!abort_on_null && left_expr->el(i)->maybe_null)
{
if (!(item_nnull_test=
- new Item_func_trig_cond(item_nnull_test,
- enable_pushed_conds_vec + i)))
+ new Item_func_trig_cond(item_nnull_test, get_cond_guard(i))))
DBUG_RETURN(RES_ERROR);
}
item_having_part2= and_items(item_having_part2, item_nnull_test);
@@ -1320,12 +1310,10 @@
*/
if (left_expr->el(i)->maybe_null)
{
- if (!(item= new Item_func_trig_cond(item,
- enable_pushed_conds_vec + i)))
+ if (!(item= new Item_func_trig_cond(item, get_cond_guard(i))))
DBUG_RETURN(RES_ERROR);
if (!(having_col_item=
- new Item_func_trig_cond(having_col_item,
- enable_pushed_conds_vec + i)))
+ new Item_func_trig_cond(having_col_item, get_cond_guard(i))))
DBUG_RETURN(RES_ERROR);
}
having_item= and_items(having_item, having_col_item);
@@ -1774,7 +1762,7 @@
bool have_changed_access= FALSE;
JOIN_TAB *changed_tabs[MAX_TABLES];
JOIN_TAB **last_changed_tab= changed_tabs;
- if (1)//enabled_conds) //psergey-todo:!
+ if (item->have_guarded_conds())
{
/*
For at least one of the pushed predicates the following is true:
@@ -1792,13 +1780,13 @@
bool *cond_guard= tab->ref.cond_guards[i];
if (cond_guard && !*cond_guard)
{
- /* ... */
+ /* Change the access method to full table scan */
tab->read_first_record= init_read_record_seq;
tab->read_record.record= tab->table->record[0];
tab->read_record.thd= join->thd;
tab->read_record.ref_length= tab->table->file->ref_length;
*(last_changed_tab++)= tab;
- //psergey-todo: break; ?
+ break;
}
}
}
--- 1.85/sql/item_subselect.h 2007-01-12 21:17:52 +03:00
+++ 1.86/sql/item_subselect.h 2007-01-12 21:17:52 +03:00
@@ -104,6 +104,7 @@
Item *get_tmp_table_item(THD *thd);
void update_used_tables();
void print(String *str);
+ virtual bool have_guarded_conds() { return FALSE; }
bool change_engine(subselect_engine *eng)
{
old_engine= engine;
@@ -249,15 +250,21 @@
bool transformed;
public:
/* Used to trigger on/off conditions that were pushed down to subselect */
- bool enable_pushed_conds;
- bool *enable_pushed_conds_vec;
+ bool *pushed_cond_guards;
+
+ bool *get_cond_guard(int i)
+ {
+ return pushed_cond_guards ? pushed_cond_guards + i : NULL;
+ }
+ void set_cond_guard_var(int i, bool v) { pushed_cond_guards[i]= v; }
+ bool have_guarded_conds() { return test(pushed_cond_guards); }
Item_func_not_all *upper_item; // point on NOT/NOP before ALL/SOME subquery
Item_in_subselect(Item * left_expr, st_select_lex *select_lex);
Item_in_subselect()
:Item_exists_subselect(), optimizer(0), abort_on_null(0), transformed(0),
- enable_pushed_conds(TRUE), enable_pushed_conds_vec(NULL), upper_item(0)
+ pushed_cond_guards(NULL), upper_item(0)
{}
subs_type substype() { return IN_SUBS; }
@@ -349,10 +356,9 @@
stored somewhere by the exec() method itself.
A required side effect: If at least one pushed-down predicate is
- disabled, call of subselect_engine->no_rows()must return correct
- result after this call.
+ disabled, subselect_engine->no_rows() must return correct result after
+ the exec() call.
- psergey-todo: check comment
RETURN
0 - OK
1 - Either an execution error, or the engine was "changed", and the
--- 1.5/mysql-test/r/subselect3.result 2007-01-12 21:17:52 +03:00
+++ 1.6/mysql-test/r/subselect3.result 2007-01-12 21:17:52 +03:00
@@ -305,3 +305,327 @@
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10))
drop table t1, t2;
+create table t1 (oref char(4), grp int, ie int);
+insert into t1 (oref, grp, ie) values
+('aa', 10, 2),
+('aa', 10, 1),
+('aa', 20, NULL),
+('bb', 10, 3),
+('cc', 10, 4),
+('cc', 20, NULL),
+('ee', 10, NULL),
+('ee', 10, NULL),
+('ff', 20, 2),
+('ff', 20, 1);
+create table t2 (oref char(4), a int);
+insert into t2 values
+('ee', NULL),
+('bb', 2),
+('ff', 2),
+('cc', 3),
+('aa', 1),
+('dd', NULL),
+('bb', NULL);
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+oref a Z
+ee NULL NULL
+bb 2 0
+ff 2 1
+cc 3 NULL
+aa 1 1
+dd NULL 0
+bb NULL NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref a
+ff 2
+aa 1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref a
+bb 2
+dd NULL
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+oref a Z
+ee NULL NULL
+bb 2 0
+ff 2 0
+cc 3 NULL
+aa 1 1
+dd NULL 0
+bb NULL NULL
+select oref, a from t2 where
+a in (select min(ie) from t1 where oref=t2.oref group by grp);
+oref a
+aa 1
+select oref, a from t2 where
+a not in (select min(ie) from t1 where oref=t2.oref group by grp);
+oref a
+bb 2
+ff 2
+dd NULL
+update t1 set ie=3 where oref='ff' and ie=1;
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp) Z from t2;
+oref a Z
+ee NULL NULL
+bb 2 0
+ff 2 1
+cc 3 NULL
+aa 1 1
+dd NULL 0
+bb NULL NULL
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+oref a
+ff 2
+aa 1
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+oref a
+bb 2
+dd NULL
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp having min(ie) > 1) Z from t2;
+oref a Z
+ee NULL 0
+bb 2 0
+ff 2 1
+cc 3 0
+aa 1 0
+dd NULL 0
+bb NULL NULL
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+oref a
+ff 2
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+oref a
+ee NULL
+bb 2
+cc 3
+aa 1
+dd NULL
+alter table t1 add index idx(ie);
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7
+2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+oref a Z
+ee NULL NULL
+bb 2 0
+ff 2 1
+cc 3 NULL
+aa 1 1
+dd NULL 0
+bb NULL NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref a
+ff 2
+aa 1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref a
+bb 2
+dd NULL
+alter table t1 drop index idx;
+alter table t1 add index idx(oref,ie);
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7
+2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 test.t2.oref,func 4 Using where; Using index; Full scan on NULL key
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+oref a Z
+ee NULL NULL
+bb 2 0
+ff 2 1
+cc 3 NULL
+aa 1 1
+dd NULL 0
+bb NULL NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref a
+ff 2
+aa 1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref a
+bb 2
+dd NULL
+explain
+select oref, a,
+a in (select min(ie) from t1 where oref=t2.oref
+group by grp having min(ie) > 1) Z
+from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7
+2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort
+select oref, a,
+a in (select min(ie) from t1 where oref=t2.oref
+group by grp having min(ie) > 1) Z
+from t2;
+oref a Z
+ee NULL 0
+bb 2 0
+ff 2 1
+cc 3 0
+aa 1 0
+dd NULL 0
+bb NULL NULL
+select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref
+group by grp having min(ie) > 1);
+oref a
+ff 2
+select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref
+group by grp having min(ie) > 1);
+oref a
+ee NULL
+bb 2
+cc 3
+aa 1
+dd NULL
+drop table t1,t2;
+create table t1 (oref char(4), grp int, ie1 int, ie2 int);
+insert into t1 (oref, grp, ie1, ie2) values
+('aa', 10, 2, 1),
+('aa', 10, 1, 1),
+('aa', 20, 2, 1),
+('bb', 10, 3, 1),
+('cc', 10, 4, 2),
+('cc', 20, 3, 2),
+('ee', 10, 2, 1),
+('ee', 10, 1, 2),
+('ff', 20, 2, 2),
+('ff', 20, 1, 2);
+create table t2 (oref char(4), a int, b int);
+insert into t2 values
+('ee', NULL, 1),
+('bb', 2, 1),
+('ff', 2, 2),
+('cc', 3, NULL),
+('bb', NULL, NULL),
+('aa', 1, 1),
+('dd', 1, NULL);
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+oref a b Z
+ee NULL 1 NULL
+bb 2 1 0
+ff 2 2 1
+cc 3 NULL NULL
+bb NULL NULL NULL
+aa 1 1 1
+dd 1 NULL 0
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
+oref a b
+ff 2 2
+aa 1 1
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+oref a b
+bb 2 1
+dd 1 NULL
+select oref, a, b,
+(a,b) in (select min(ie1),max(ie2) from t1
+where oref=t2.oref group by grp) Z
+from t2;
+oref a b Z
+ee NULL 1 0
+bb 2 1 0
+ff 2 2 0
+cc 3 NULL NULL
+bb NULL NULL NULL
+aa 1 1 1
+dd 1 NULL 0
+select oref, a, b from t2 where
+(a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+oref a b
+aa 1 1
+select oref, a, b from t2 where
+(a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+oref a b
+ee NULL 1
+bb 2 1
+ff 2 2
+dd 1 NULL
+alter table t1 add index idx(ie1,ie2);
+explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7
+2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+oref a b Z
+ee NULL 1 NULL
+bb 2 1 0
+ff 2 2 1
+cc 3 NULL NULL
+bb NULL NULL NULL
+aa 1 1 1
+dd 1 NULL 0
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
+oref a b
+ff 2 2
+aa 1 1
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+oref a b
+bb 2 1
+dd 1 NULL
+explain extended
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7
+2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key
+Warnings:
+Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2`
+drop table t1,t2;
+create table t1 (oref char(4), grp int, ie int primary key);
+insert into t1 (oref, grp, ie) values
+('aa', 10, 2),
+('aa', 10, 1),
+('bb', 10, 3),
+('cc', 10, 4),
+('cc', 20, 5),
+('cc', 10, 6);
+create table t2 (oref char(4), a int);
+insert into t2 values
+('ee', NULL),
+('bb', 2),
+('cc', 5),
+('cc', 2),
+('cc', NULL),
+('aa', 1),
+('bb', NULL);
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7
+2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+oref a Z
+ee NULL 0
+bb 2 0
+cc 5 1
+cc 2 0
+cc NULL NULL
+aa 1 1
+bb NULL NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref a
+cc 5
+aa 1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref a
+ee NULL
+bb 2
+cc 2
+explain
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+oref a Z
+ee NULL 0
+bb 2 0
+cc 5 1
+cc 2 0
+cc NULL NULL
+aa 1 1
+bb NULL NULL
+drop table t1,t2;
--- 1.6/mysql-test/t/subselect3.test 2007-01-12 21:17:52 +03:00
+++ 1.7/mysql-test/t/subselect3.test 2007-01-12 21:17:52 +03:00
@@ -271,3 +271,205 @@
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
drop table t1, t2;
+# Now test different column types:
+create table t1 (oref char(4), grp int, ie int);
+insert into t1 (oref, grp, ie) values
+ ('aa', 10, 2),
+ ('aa', 10, 1),
+ ('aa', 20, NULL),
+
+ ('bb', 10, 3),
+
+ ('cc', 10, 4),
+ ('cc', 20, NULL),
+
+ ('ee', 10, NULL),
+ ('ee', 10, NULL),
+
+ ('ff', 20, 2),
+ ('ff', 20, 1);
+
+create table t2 (oref char(4), a int);
+insert into t2 values
+ ('ee', NULL),
+ ('bb', 2),
+ ('ff', 2),
+ ('cc', 3),
+ ('aa', 1),
+ ('dd', NULL),
+ ('bb', NULL);
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+
+select oref, a from t2 where
+ a in (select min(ie) from t1 where oref=t2.oref group by grp);
+
+select oref, a from t2 where
+ a not in (select min(ie) from t1 where oref=t2.oref group by grp);
+
+#
+update t1 set ie=3 where oref='ff' and ie=1;
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp) Z from t2;
+
+
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp having min(ie) > 1) Z from t2;
+
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+
+#
+alter table t1 add index idx(ie);
+
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+
+alter table t1 drop index idx;
+alter table t1 add index idx(oref,ie);
+
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+explain
+select oref, a,
+ a in (select min(ie) from t1 where oref=t2.oref
+ group by grp having min(ie) > 1) Z
+from t2;
+
+select oref, a,
+ a in (select min(ie) from t1 where oref=t2.oref
+ group by grp having min(ie) > 1) Z
+from t2;
+
+select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref
+ group by grp having min(ie) > 1);
+
+select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref
+ group by grp having min(ie) > 1);
+
+drop table t1,t2;
+
+create table t1 (oref char(4), grp int, ie1 int, ie2 int);
+insert into t1 (oref, grp, ie1, ie2) values
+ ('aa', 10, 2, 1),
+ ('aa', 10, 1, 1),
+ ('aa', 20, 2, 1),
+
+ ('bb', 10, 3, 1),
+
+ ('cc', 10, 4, 2),
+ ('cc', 20, 3, 2),
+
+ ('ee', 10, 2, 1),
+ ('ee', 10, 1, 2),
+
+ ('ff', 20, 2, 2),
+ ('ff', 20, 1, 2);
+
+create table t2 (oref char(4), a int, b int);
+insert into t2 values
+ ('ee', NULL, 1),
+ ('bb', 2, 1),
+ ('ff', 2, 2),
+ ('cc', 3, NULL),
+ ('bb', NULL, NULL),
+ ('aa', 1, 1),
+ ('dd', 1, NULL);
+
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
+
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+
+select oref, a, b,
+ (a,b) in (select min(ie1),max(ie2) from t1
+ where oref=t2.oref group by grp) Z
+from t2;
+
+select oref, a, b from t2 where
+ (a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+
+select oref, a, b from t2 where
+ (a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+
+alter table t1 add index idx(ie1,ie2);
+
+explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
+
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+
+explain extended
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+drop table t1,t2;
+
+create table t1 (oref char(4), grp int, ie int primary key);
+insert into t1 (oref, grp, ie) values
+ ('aa', 10, 2),
+ ('aa', 10, 1),
+
+ ('bb', 10, 3),
+
+ ('cc', 10, 4),
+ ('cc', 20, 5),
+ ('cc', 10, 6);
+
+create table t2 (oref char(4), a int);
+insert into t2 values
+ ('ee', NULL),
+ ('bb', 2),
+ ('cc', 5),
+ ('cc', 2),
+ ('cc', NULL),
+ ('aa', 1),
+ ('bb', NULL);
+
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+explain
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+
+drop table t1,t2;
+
--- 1.3/mysql-test/r/ndb_subquery.result 2007-01-12 21:17:52 +03:00
+++ 1.4/mysql-test/r/ndb_subquery.result 2007-01-12 21:17:52 +03:00
@@ -9,7 +9,7 @@
explain select * from t2 where p NOT IN (select p from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index
+2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1
select * from t2 where p NOT IN (select p from t1) order by p;
p u o
4 4 4
@@ -17,7 +17,7 @@
explain select * from t2 where p NOT IN (select u from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
-2 DEPENDENT SUBQUERY t1 unique_subquery u u 4 func 1 Using index
+2 DEPENDENT SUBQUERY t1 unique_subquery u u 4 func 1
select * from t2 where p NOT IN (select u from t1) order by p;
p u o
4 4 4
@@ -25,7 +25,7 @@
explain select * from t2 where p NOT IN (select o from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
-2 DEPENDENT SUBQUERY t1 index_subquery o o 4 func 1 Using index
+2 DEPENDENT SUBQUERY t1 index_subquery o o 4 func 1
select * from t2 where p NOT IN (select o from t1) order by p;
p u o
4 4 4
| Thread |
|---|
| • bk commit into 5.0 tree (sergefp:1.2376) BUG#24127 | Sergey Petrunia | 12 Jan |
