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 23:22:41+03:00, sergefp@stripped +14 -0
BUG#24127: (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs:
- Make the code produce correct result: use an array of triggers to turn on/off equalities for each
compared column. Also turn on/off optimizations based on those equalities.
- Make EXPLAIN output show "Full scan on NULL key" for tables for which we switch between
ref/unique_subquery/index_subquery and ALL access.
- index_subquery engine now has HAVING clause when it is needed, and it is
displayed in EXPLAIN EXTENDED
- Fix incorrect presense of "Using index" for index/unique-based subqueries (BUG#22930)
// bk trigger note: this commit refers to BUG#24127
mysql-test/r/ndb_subquery.result@stripped, 2007-01-12 23:22:36+03:00, sergefp@stripped +3 -3
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- Updated test results (checked)
mysql-test/r/subselect.result@stripped, 2007-01-12 23:22:36+03:00, sergefp@stripped +14 -14
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- Updated test results (checked)
mysql-test/r/subselect2.result@stripped, 2007-01-12 23:22:36+03:00, sergefp@stripped +5 -5
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- Updated test results (checked)
mysql-test/r/subselect3.result@stripped, 2007-01-12 23:22:37+03:00, sergefp@stripped +404 -4
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- Testcases
mysql-test/t/subselect3.test@stripped, 2007-01-12 23:22:37+03:00, sergefp@stripped +281 -10
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- Testcases
sql/item_cmpfunc.cc@stripped, 2007-01-12 23:22:37+03:00, sergefp@stripped +29 -5
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- For row-based IN subqueries, use one flag per each column. Set the flags appropriately before
running the subquery.
sql/item_cmpfunc.h@stripped, 2007-01-12 23:22:37+03:00, sergefp@stripped +1 -0
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- Added Item_func_trig_cond::get_triv_var()
sql/item_subselect.cc@stripped, 2007-01-12 23:22:37+03:00, sergefp@stripped +156 -111
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- Item_subselect::exec() and subselect_*_engine::exec() don't have parameter
anymore - now Item_subselect owns the pushed down predicates guard flags.
- A correct set of conditional predicates is now pushed into row-based IN
subquery.
- select_indexsubquery_engine now has "HAVING clause" (needed for correct query
results), and it is shown in EXPLAIN EXTENDED
sql/item_subselect.h@stripped, 2007-01-12 23:22:37+03:00, sergefp@stripped +75 -17
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- Item_subselect::exec() and subselect_*_engine::exec() don't have parameter
anymore - now Item_subselect owns the pushed down predicates guard flags.
- A correct set of conditional predicates is now pushed into row-based IN
subquery.
- select_indexsubquery_engine now has "HAVING clause" (needed for correct query
results), and it is shown in EXPLAIN EXTENDED
sql/mysql_priv.h@stripped, 2007-01-12 23:22:37+03:00, sergefp@stripped +1 -1
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- Added "in_having_cond" special Item name
sql/mysqld.cc@stripped, 2007-01-12 23:22:37+03:00, sergefp@stripped +3 -0
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- Added "in_having_cond" special Item name
sql/sql_lex.h@stripped, 2007-01-12 23:22:37+03:00, sergefp@stripped +1 -1
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
sql/sql_select.cc@stripped, 2007-01-12 23:22:37+03:00, sergefp@stripped +120 -76
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- Make "ref" analyzer be able to work with conditional equalities
- Fix subquery optimization code to match the changes in what kinds of
conditions are pushed down into subqueries
- Fix wrong EXPLAIN output in some queries with subquery (BUG#22390)
sql/sql_select.h@stripped, 2007-01-12 23:22:37+03:00, sergefp@stripped +39 -3
BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
- Make "ref" analyzer be able to work with conditional equalities
- Fix wrong EXPLAIN output in some queries with subquery (BUG#22390)
# 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-r12
--- 1.228/sql/item_cmpfunc.cc 2007-01-12 23:22:47 +03:00
+++ 1.229/sql/item_cmpfunc.cc 2007-01-12 23:22:47 +03:00
@@ -813,11 +813,35 @@
We disable the predicates we've pushed down into subselect, run the
subselect and see if it has produced any rows.
*/
- ((Item_in_subselect*)args[1])->enable_pushed_conds= FALSE;
- longlong tmp= args[1]->val_bool_result();
- result_for_null_param= null_value=
- !((Item_in_subselect*)args[1])->engine->no_rows();
- ((Item_in_subselect*)args[1])->enable_pushed_conds= TRUE;
+ Item_in_subselect *item_subs=(Item_in_subselect*)args[1];
+ if (cache->cols() == 1)
+ {
+ 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->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 (i= 0; i < ncols; i++)
+ {
+ if (cache->el(i)->null_value)
+ 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 (i= 0; i < ncols; i++)
+ item_subs->set_cond_guard_var(i, TRUE);
+ }
}
}
return 0;
--- 1.136/sql/item_cmpfunc.h 2007-01-12 23:22:47 +03:00
+++ 1.137/sql/item_cmpfunc.h 2007-01-12 23:22:47 +03:00
@@ -311,6 +311,7 @@
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.427/sql/mysql_priv.h 2007-01-12 23:22:47 +03:00
+++ 1.428/sql/mysql_priv.h 2007-01-12 23:22:47 +03:00
@@ -1197,7 +1197,7 @@
extern const char *first_keyword, *my_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, *in_additional_cond;
+extern const char *in_left_expr_name, *in_additional_cond, *in_having_cond;
extern const char * const triggers_file_ext;
extern const char * const trigname_file_ext;
extern Eq_creator eq_creator;
--- 1.585/sql/mysqld.cc 2007-01-12 23:22:47 +03:00
+++ 1.586/sql/mysqld.cc 2007-01-12 23:22:47 +03:00
@@ -449,10 +449,13 @@
const char **errmesg; /* Error messages */
const char *myisam_recover_options_str="OFF";
const char *myisam_stats_method_str="nulls_unequal";
+
/* name of reference on left espression in rewritten IN subquery */
const char *in_left_expr_name= "<left expr>";
/* name of additional condition */
const char *in_additional_cond= "<IN COND>";
+const char *in_having_cond= "<IN HAVING>";
+
my_decimal decimal_zero;
/* classes for comparation parsing/processing */
Eq_creator eq_creator;
--- 1.235/sql/sql_lex.h 2007-01-12 23:22:47 +03:00
+++ 1.236/sql/sql_lex.h 2007-01-12 23:22:47 +03:00
@@ -469,7 +469,7 @@
void set_thd(THD *thd_arg) { thd= thd_arg; }
friend void lex_start(THD *thd, uchar *buf, uint length);
- friend int subselect_union_engine::exec(bool);
+ friend int subselect_union_engine::exec();
List<Item> *get_unit_column_types();
};
--- 1.478/sql/sql_select.cc 2007-01-12 23:22:47 +03:00
+++ 1.479/sql/sql_select.cc 2007-01-12 23:22:47 +03:00
@@ -513,72 +513,88 @@
/*
- test if it is known for optimisation IN subquery
+ Remove the predicates pushed down into the subquery
SYNOPSIS
- JOIN::test_in_subselect()
- where - pointer for variable in which conditions should be
- stored if subquery is known
+ JOIN::remove_subq_pushed_predicates()
+ where IN Must be NULL
+ OUT The remaining WHERE condition, or NULL
- RETURN
- 1 - known
- 0 - unknown
+ DESCRIPTION
+ Given that this join will be executed using (unique|index)_subquery,
+ without "checking NULL", remove the predicates that were pushed down
+ into the subquery.
+
+ We can remove the equalities that will be guaranteed to be true by the
+ 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 (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 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.
*/
-bool JOIN::test_in_subselect(Item **where)
+void JOIN::remove_subq_pushed_predicates(Item **where)
{
if (conds->type() == Item::FUNC_ITEM &&
((Item_func *)this->conds)->functype() == Item_func::EQ_FUNC &&
((Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM &&
((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM)
{
- join_tab->info= "Using index";
*where= 0;
- return 1;
+ return;
}
if (conds->type() == Item::COND_ITEM &&
((class Item_func *)this->conds)->functype() ==
Item_func::COND_AND_FUNC)
{
- if ((*where= remove_additional_cond(conds)))
- join_tab->info= "Using index; Using where";
- else
- join_tab->info= "Using index";
- return 1;
+ *where= remove_additional_cond(conds);
}
- return 0;
}
/*
- Check if the passed HAVING clause is a clause added by subquery optimizer
+ Index lookup-based subquery: save some flags for EXPLAIN output
SYNOPSIS
- is_having_subq_predicates()
- having Having clause
+ save_index_subquery_explain_info()
+ join_tab Subquery's join tab (there is only one as index lookup is
+ only used for subqueries that are single-table SELECTs)
+ where Subquery's WHERE clause
- RETURN
- TRUE The passed HAVING clause was added by the subquery optimizer
- FALSE Otherwise
-*/
-
-bool is_having_subq_predicates(Item *having)
-{
- if (having->type() == Item::FUNC_ITEM)
+ DESCRIPTION
+ For index lookup-based subquery (i.e. one executed with
+ subselect_uniquesubquery_engine or subselect_indexsubquery_engine),
+ check its EXPLAIN output row should contain
+ "Using index" (TAB_INFO_FULL_SCAN_ON_NULL)
+ "Using Where" (TAB_INFO_USING_WHERE)
+ "Full scan on NULL key" (TAB_INFO_FULL_SCAN_ON_NULL)
+ and set appropriate flags in join_tab->packed_info.
+*/
+
+static void save_index_subquery_explain_info(JOIN_TAB *join_tab, Item* where)
+{
+ join_tab->packed_info= TAB_INFO_HAVE_VALUE;
+ if (join_tab->table->used_keys.is_set(join_tab->ref.key))
+ join_tab->packed_info |= TAB_INFO_USING_INDEX;
+ if (where)
+ join_tab->packed_info |= TAB_INFO_USING_WHERE;
+ for (uint i = 0; i < join_tab->ref.key_parts; i++)
{
- if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC)
- return TRUE;
- if (((Item_func *) having)->functype() == Item_func::TRIG_COND_FUNC)
+ if (join_tab->ref.cond_guards[i])
{
- having= ((Item_func*)having)->arguments()[0];
- if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC)
- return TRUE;
+ join_tab->packed_info |= TAB_INFO_FULL_SCAN_ON_NULL;
+ break;
}
- return TRUE;
}
- return FALSE;
}
+
/*
global select optimisation.
return 0 - success
@@ -1017,51 +1033,47 @@
if (join_tab[0].type == JT_EQ_REF &&
join_tab[0].ref.items[0]->name == in_left_expr_name)
{
- if (test_in_subselect(&where))
- {
- join_tab[0].type= JT_UNIQUE_SUBQUERY;
- error= 0;
- DBUG_RETURN(unit->item->
- change_engine(new
- subselect_uniquesubquery_engine(thd,
- join_tab,
- unit->item,
- where)));
- }
+ remove_subq_pushed_predicates(&where);
+ save_index_subquery_explain_info(join_tab, where);
+ join_tab[0].type= JT_UNIQUE_SUBQUERY;
+ error= 0;
+ DBUG_RETURN(unit->item->
+ change_engine(new
+ subselect_uniquesubquery_engine(thd,
+ join_tab,
+ unit->item,
+ where)));
}
else if (join_tab[0].type == JT_REF &&
join_tab[0].ref.items[0]->name == in_left_expr_name)
{
- if (test_in_subselect(&where))
- {
- join_tab[0].type= JT_INDEX_SUBQUERY;
- error= 0;
- DBUG_RETURN(unit->item->
- change_engine(new
- subselect_indexsubquery_engine(thd,
- join_tab,
- unit->item,
- where,
- 0)));
- }
+ remove_subq_pushed_predicates(&where);
+ save_index_subquery_explain_info(join_tab, where);
+ join_tab[0].type= JT_INDEX_SUBQUERY;
+ error= 0;
+ DBUG_RETURN(unit->item->
+ change_engine(new
+ subselect_indexsubquery_engine(thd,
+ join_tab,
+ unit->item,
+ where,
+ NULL,
+ 0)));
}
} else if (join_tab[0].type == JT_REF_OR_NULL &&
join_tab[0].ref.items[0]->name == in_left_expr_name &&
- is_having_subq_predicates(having))
+ having->name == in_having_cond)
{
join_tab[0].type= JT_INDEX_SUBQUERY;
error= 0;
-
- if ((conds= remove_additional_cond(conds)))
- join_tab->info= "Using index; Using where";
- else
- join_tab->info= "Using index";
-
+ conds= remove_additional_cond(conds);
+ save_index_subquery_explain_info(join_tab, conds);
DBUG_RETURN(unit->item->
change_engine(new subselect_indexsubquery_engine(thd,
join_tab,
unit->item,
conds,
+ having,
1)));
}
@@ -2557,9 +2569,7 @@
when val IS NULL.
*/
bool null_rejecting;
-
- /* TRUE<=> This ref access is an outer subquery reference access */
- bool outer_ref;
+ bool *cond_guard; /* See KEYUSE::cond_guard */
} KEY_FIELD;
/* Values in optimize */
@@ -2858,7 +2868,7 @@
cond->functype() == Item_func::MULT_EQUAL_FUNC) &&
((*value)->type() == Item::FIELD_ITEM) &&
((Item_field*)*value)->field->maybe_null());
- (*key_fields)->outer_ref= FALSE;
+ (*key_fields)->cond_guard= NULL;
(*key_fields)++;
}
@@ -2955,8 +2965,9 @@
}
/*
- Subquery optimization: check if the encountered condition is one
- added by condition push down into subquery.
+ 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 &&
@@ -2973,7 +2984,7 @@
sargables);
// Indicate that this ref access candidate is for subquery lookup:
for (; save != *key_fields; save++)
- save->outer_ref= TRUE;
+ save->cond_guard= ((Item_func_trig_cond*)cond)->get_trig_var();
}
return;
}
@@ -3153,7 +3164,7 @@
keyuse.used_tables=key_field->val->used_tables();
keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL;
keyuse.null_rejecting= key_field->null_rejecting;
- keyuse.outer_ref= key_field->outer_ref;
+ keyuse.cond_guard= key_field->cond_guard;
VOID(insert_dynamic(keyuse_array,(gptr) &keyuse));
}
}
@@ -4992,7 +5003,8 @@
if (!(j->ref.key_buff= (byte*) thd->calloc(ALIGN_SIZE(length)*2)) ||
!(j->ref.key_copy= (store_key**) thd->alloc((sizeof(store_key*) *
(keyparts+1)))) ||
- !(j->ref.items= (Item**) thd->alloc(sizeof(Item*)*keyparts)))
+ !(j->ref.items= (Item**) thd->alloc(sizeof(Item*)*keyparts)) ||
+ !(j->ref.cond_guards= (bool**) thd->alloc(sizeof(uint*)*keyparts)))
{
DBUG_RETURN(TRUE);
}
@@ -5007,6 +5019,8 @@
if (ftkey)
{
j->ref.items[0]=((Item_func*)(keyuse->val))->key_item();
+ /* Predicates pushed down into subquery can't be used FT access */
+ j->ref.cond_guards[0]= NULL;
if (keyuse->used_tables)
DBUG_RETURN(TRUE); // not supported yet. SerG
@@ -5023,6 +5037,7 @@
uint maybe_null= test(keyinfo->key_part[i].null_bit);
j->ref.items[i]=keyuse->val; // Save for cond removal
+ j->ref.cond_guards[i]= keyuse->cond_guard;
if (keyuse->null_rejecting)
j->ref.null_rejecting |= 1 << i;
keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables;
@@ -7653,7 +7668,7 @@
SYNOPSIS
remove_additional_cond()
- conds - condition for processing
+ conds Condition for processing
RETURN VALUES
new conditions
@@ -10911,7 +10926,9 @@
int init_read_record_seq(JOIN_TAB *tab)
{
tab->read_record.read_record= rr_sequential;
- return tab->read_record.file->ha_rnd_init(1);
+ if (tab->read_record.file->ha_rnd_init(1))
+ return 1;
+ return (*tab->read_record.read_record)(&tab->read_record);
}
static int
@@ -14819,6 +14836,24 @@
if (tab->info)
item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs));
+ else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
+ {
+ if (tab->packed_info & TAB_INFO_USING_INDEX)
+ extra.append(STRING_WITH_LEN("; Using index"));
+ if (tab->packed_info & TAB_INFO_USING_WHERE)
+ extra.append(STRING_WITH_LEN("; Using where"));
+ if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
+ extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+ /* Skip initial "; "*/
+ const char *str= extra.ptr();
+ uint32 len= extra.length();
+ if (len)
+ {
+ str += 2;
+ len -= 2;
+ }
+ item_list.push_back(new Item_string(str, len, cs));
+ }
else
{
if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
@@ -14877,6 +14912,15 @@
}
if (distinct & test_all_bits(used_tables,thd->used_tables))
extra.append(STRING_WITH_LEN("; Distinct"));
+
+ for (uint part= 0; part < tab->ref.key_parts; part++)
+ {
+ if (tab->ref.cond_guards[part])
+ {
+ extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+ break;
+ }
+ }
/* Skip initial "; "*/
const char *str= extra.ptr();
--- 1.114/sql/sql_select.h 2007-01-12 23:22:47 +03:00
+++ 1.115/sql/sql_select.h 2007-01-12 23:22:47 +03:00
@@ -35,8 +35,17 @@
satisfied if val has NULL 'value'.
*/
bool null_rejecting;
- /* TRUE<=> This ref access is an outer subquery reference access */
- bool outer_ref;
+ /*
+ !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;
class store_key;
@@ -51,6 +60,18 @@
byte *key_buff2; // key_buff+key_length
store_key **key_copy; //
Item **items; // val()'s for each keypart
+ /*
+ 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;
/*
(null_rejecting & (1<<i)) means the condition is '=' and no matching
rows will be produced if items[i] IS NULL (see add_not_null_conds())
@@ -99,6 +120,13 @@
NESTED_LOOP_QUERY_LIMIT= 3, NESTED_LOOP_CURSOR_LIMIT= 4
};
+
+/* Values for JOIN_TAB::packed_info */
+#define TAB_INFO_HAVE_VALUE 1
+#define TAB_INFO_USING_INDEX 2
+#define TAB_INFO_USING_WHERE 4
+#define TAB_INFO_FULL_SCAN_ON_NULL 8
+
typedef enum_nested_loop_state
(*Next_select_func)(JOIN *, struct st_join_table *, bool);
typedef int (*Read_record_func)(struct st_join_table *tab);
@@ -119,7 +147,15 @@
st_join_table *last_inner; /* last table table for embedding outer join */
st_join_table *first_upper; /* first inner table for embedding outer join */
st_join_table *first_unmatched; /* used for optimization purposes only */
+
+ /* Special content for EXPLAIN 'Extra' column or NULL if none */
const char *info;
+ /*
+ Bitmap of TAB_INFO_* bits that encodes special line for EXPLAIN 'Extra'
+ column, or 0 if there is no info.
+ */
+ uint packed_info;
+
Read_record_func read_first_record;
Next_select_func next_select;
READ_RECORD read_record;
@@ -386,7 +422,7 @@
Item_sum ***func);
int rollup_send_data(uint idx);
int rollup_write_data(uint idx, TABLE *table);
- bool test_in_subselect(Item **where);
+ void remove_subq_pushed_predicates(Item **where);
/*
Release memory and, if possible, the open tables held by this execution
plan (and nested plans). It's used to release some tables before
--- 1.168/mysql-test/r/subselect.result 2007-01-12 23:22:47 +03:00
+++ 1.169/mysql-test/r/subselect.result 2007-01-12 23:22:47 +03:00
@@ -892,7 +892,7 @@
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index
2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 Using index
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
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;
@@ -1305,7 +1305,7 @@
explain extended 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 PRIMARY 4 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where
+2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where (`test`.`t1`.`b` <> 30))))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
@@ -1462,27 +1462,27 @@
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key
Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key
Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Full scan on NULL key
Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Using where
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Using where; Full scan on NULL key
Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2'))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
drop table t1,t2;
create table t2 (a int, b int);
create table t3 (a int);
@@ -2816,19 +2816,19 @@
1 PRIMARY t1 ALL NULL NULL NULL NULL 8
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having trigcond((<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))))
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
CREATE TABLE t1 (a char(5), b char(5));
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
@@ -3009,7 +3009,7 @@
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 3
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
SELECT a, a IN (SELECT a FROM t1) FROM t2;
a a IN (SELECT a FROM t1)
1 1
--- 1.13/mysql-test/r/subselect2.result 2007-01-12 23:22:47 +03:00
+++ 1.14/mysql-test/r/subselect2.result 2007-01-12 23:22:47 +03:00
@@ -126,11 +126,11 @@
1 PRIMARY t2 ALL DDOCTYPEID_IDX NULL NULL NULL 9 Using where
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1
-2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using index; Using where
-3 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using index; Using where
-4 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using index; Using where
-5 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using index; Using where
-6 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 Using index; Using where
+2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where
+3 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where
+4 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where
+5 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where
+6 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 Using where
drop table t1, t2, t3, t4;
CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
INSERT INTO t1 VALUES (1),(2);
--- 1.147/sql/item_subselect.cc 2007-01-12 23:22:47 +03:00
+++ 1.148/sql/item_subselect.cc 2007-01-12 23:22:47 +03:00
@@ -191,16 +191,16 @@
return res;
}
-bool Item_subselect::exec(bool full_scan)
+bool Item_subselect::exec()
{
int res;
- res= engine->exec(full_scan);
+ res= engine->exec();
if (engine_changed)
{
engine_changed= 0;
- return exec(full_scan);
+ return exec();
}
return (res);
}
@@ -448,13 +448,13 @@
void Item_singlerow_subselect::bring_value()
{
- exec(FALSE);
+ exec();
}
double Item_singlerow_subselect::val_real()
{
DBUG_ASSERT(fixed == 1);
- if (!exec(FALSE) && !value->null_value)
+ if (!exec() && !value->null_value)
{
null_value= 0;
return value->val_real();
@@ -469,7 +469,7 @@
longlong Item_singlerow_subselect::val_int()
{
DBUG_ASSERT(fixed == 1);
- if (!exec(FALSE) && !value->null_value)
+ if (!exec() && !value->null_value)
{
null_value= 0;
return value->val_int();
@@ -483,7 +483,7 @@
String *Item_singlerow_subselect::val_str(String *str)
{
- if (!exec(FALSE) && !value->null_value)
+ if (!exec() && !value->null_value)
{
null_value= 0;
return value->val_str(str);
@@ -498,7 +498,7 @@
my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
{
- if (!exec(FALSE) && !value->null_value)
+ if (!exec() && !value->null_value)
{
null_value= 0;
return value->val_decimal(decimal_value);
@@ -513,7 +513,7 @@
bool Item_singlerow_subselect::val_bool()
{
- if (!exec(FALSE) && !value->null_value)
+ if (!exec() && !value->null_value)
{
null_value= 0;
return value->val_bool();
@@ -565,7 +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), upper_item(0)
+ pushed_cond_guards(NULL), upper_item(0)
{
DBUG_ENTER("Item_in_subselect::Item_in_subselect");
left_expr= left_exp;
@@ -610,7 +610,7 @@
double Item_exists_subselect::val_real()
{
DBUG_ASSERT(fixed == 1);
- if (exec(FALSE))
+ if (exec())
{
reset();
return 0;
@@ -621,7 +621,7 @@
longlong Item_exists_subselect::val_int()
{
DBUG_ASSERT(fixed == 1);
- if (exec(FALSE))
+ if (exec())
{
reset();
return 0;
@@ -632,7 +632,7 @@
String *Item_exists_subselect::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
- if (exec(FALSE))
+ if (exec())
{
reset();
return 0;
@@ -645,7 +645,7 @@
my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value)
{
DBUG_ASSERT(fixed == 1);
- if (exec(FALSE))
+ if (exec())
{
reset();
return 0;
@@ -658,7 +658,7 @@
bool Item_exists_subselect::val_bool()
{
DBUG_ASSERT(fixed == 1);
- if (exec(FALSE))
+ if (exec())
{
reset();
return 0;
@@ -676,7 +676,7 @@
DBUG_ASSERT(0);
DBUG_ASSERT(fixed == 1);
null_value= 0;
- if (exec(!enable_pushed_conds))
+ if (exec())
{
reset();
null_value= 1;
@@ -697,7 +697,7 @@
DBUG_ASSERT(0);
DBUG_ASSERT(fixed == 1);
null_value= 0;
- if (exec(!enable_pushed_conds))
+ if (exec())
{
reset();
null_value= 1;
@@ -718,7 +718,7 @@
DBUG_ASSERT(0);
DBUG_ASSERT(fixed == 1);
null_value= 0;
- if (exec(!enable_pushed_conds))
+ if (exec())
{
reset();
null_value= 1;
@@ -738,7 +738,7 @@
{
DBUG_ASSERT(fixed == 1);
null_value= 0;
- if (exec(!enable_pushed_conds))
+ if (exec())
{
reset();
null_value= 1;
@@ -758,7 +758,7 @@
DBUG_ASSERT(0);
null_value= 0;
DBUG_ASSERT(fixed == 1);
- if (exec(!enable_pushed_conds))
+ if (exec())
{
reset();
null_value= 1;
@@ -948,19 +948,14 @@
unit->uncacheable|= UNCACHEABLE_DEPENDENT;
}
+ if (!abort_on_null && left_expr->maybe_null)
+ {
+ 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)
{
@@ -976,9 +971,9 @@
{
/*
We can encounter "NULL IN (SELECT ...)". Wrap the added condition
- within a trigger.
+ 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));
}
/*
@@ -987,6 +982,8 @@
argument (reference) to fix_fields()
*/
select_lex->having= join->having= and_items(join->having, item);
+ if (join->having == item)
+ item->name= (char*)in_having_cond;
select_lex->having_fix_field= 1;
/*
we do not check join->having->fixed, because Item_and (from and_items)
@@ -1017,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);
}
/*
@@ -1025,6 +1022,7 @@
we can assign select_lex->having here, and pass 0 as last
argument (reference) to fix_fields()
*/
+ having->name= (char*)in_having_cond;
select_lex->having= join->having= having;
select_lex->having_fix_field= 1;
/*
@@ -1040,16 +1038,21 @@
new Item_func_isnull(orig_item));
}
/*
- If we may encounter NULL IN (SELECT ...) and care between NULL and
- FALSE, wrap it in a trigger.
+ If we may encounter NULL IN (SELECT ...) and care whether subquery
+ result is NULL or FALSE, wrap condition in a trig_cond.
*/
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);
}
-
+ /*
+ TODO: figure out why the following is done here in
+ single_value_transformer but there is no corresponding action in
+ row_value_transformer?
+ */
item->name= (char *)in_additional_cond;
+
/*
AND can't be changed during fix_fields()
we can assign select_lex->having here, and pass 0 as last
@@ -1083,11 +1086,13 @@
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;
select_lex->having= join->having= new_having;
select_lex->having_fix_field= 1;
+
/*
we do not check join->having->fixed, because comparison function
(from func->create) can't be fixed after creation
@@ -1157,6 +1162,15 @@
thd->lex->current_select= current;
unit->uncacheable|= UNCACHEABLE_DEPENDENT;
+
+ if (!abort_on_null && left_expr->maybe_null)
+ {
+ 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++)
+ pushed_cond_guards[i]= TRUE;
+ }
}
select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
@@ -1173,6 +1187,7 @@
is_not_null_test(v3))
where is_not_null_test used to register nulls in case if we have
not found matching to return correct NULL value
+ TODO: say here explicitly if the order of AND parts matters or not.
*/
Item *item_having_part2= 0;
for (uint i= 0; i < cols_num; i++)
@@ -1201,21 +1216,28 @@
(char *)"<no matter>",
(char *)"<list ref>")
);
- having_item=
- and_items(having_item,
- new Item_cond_or(item_eq, item_isnull));
- item_having_part2=
- and_items(item_having_part2,
- new
- Item_is_not_null_test(this,
- new
- Item_ref(&select_lex->context,
- select_lex->
- ref_pointer_array + i,
- (char *)"<no matter>",
- (char *)"<list ref>")
- )
- );
+ 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, get_cond_guard(i))))
+ DBUG_RETURN(RES_ERROR);
+ }
+ having_item= and_items(having_item, col_item);
+
+ Item *item_nnull_test=
+ new Item_is_not_null_test(this,
+ new Item_ref(&select_lex->context,
+ select_lex->
+ ref_pointer_array + i,
+ (char *)"<no matter>",
+ (char *)"<list ref>"));
+ if (!abort_on_null && left_expr->el(i)->maybe_null)
+ {
+ if (!(item_nnull_test=
+ 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);
item_having_part2->top_level_item();
}
having_item= and_items(having_item, item_having_part2);
@@ -1264,18 +1286,15 @@
);
if (!abort_on_null)
{
- having_item=
- and_items(having_item,
- new
- Item_is_not_null_test(this,
- new
- Item_ref(&select_lex->context,
- select_lex->
- ref_pointer_array + i,
- (char *)"<no matter>",
- (char *)"<list ref>")
- )
- );
+ Item *having_col_item=
+ new Item_is_not_null_test(this,
+ new
+ Item_ref(&select_lex->context,
+ select_lex->ref_pointer_array + i,
+ (char *)"<no matter>",
+ (char *)"<list ref>"));
+
+
item_isnull= new
Item_func_isnull(new
Item_direct_ref(&select_lex->context,
@@ -1284,14 +1303,23 @@
(char *)"<no matter>",
(char *)"<list ref>")
);
-
item= new Item_cond_or(item, item_isnull);
+ /*
+ TODO: why we create the above for cases where the right part
+ cant be NULL?
+ */
+ if (left_expr->el(i)->maybe_null)
+ {
+ 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, get_cond_guard(i))))
+ DBUG_RETURN(RES_ERROR);
+ }
+ having_item= and_items(having_item, having_col_item);
}
-
where_item= and_items(where_item, item);
}
- if (where_item)
- where_item= new Item_func_trig_cond(where_item, &enable_pushed_conds);
/*
AND can't be changed during fix_fields()
we can assign select_lex->where here, and pass 0 as last
@@ -1305,9 +1333,9 @@
if (having_item)
{
bool res;
- having_item= new Item_func_trig_cond(having_item, &enable_pushed_conds);
-
select_lex->having= join->having= and_items(join->having, having_item);
+ if (having_item == select_lex->having)
+ having_item->name= (char*)in_having_cond;
select_lex->having->top_level_item();
/*
AND can't be changed during fix_fields()
@@ -1693,7 +1721,7 @@
int join_read_always_key_or_null(JOIN_TAB *tab);
int join_read_next_same_or_null(READ_RECORD *info);
-int subselect_single_select_engine::exec(bool full_scan)
+int subselect_single_select_engine::exec()
{
DBUG_ENTER("subselect_single_select_engine::exec");
char const *save_where= thd->where;
@@ -1731,9 +1759,13 @@
if (!executed)
{
item->reset_value_registration();
- if (full_scan)
+ bool have_changed_access= FALSE;
+ JOIN_TAB *changed_tabs[MAX_TABLES];
+ JOIN_TAB **last_changed_tab= changed_tabs;
+ if (item->have_guarded_conds())
{
/*
+ For at least one of the pushed predicates the following is true:
We should not apply optimizations based on the condition that was
pushed down into the subquery. Those optimizations are ref[_or_null]
acceses. Change them to be full table scans.
@@ -1741,32 +1773,36 @@
for (uint i=join->const_tables ; i < join->tables ; i++)
{
JOIN_TAB *tab=join->join_tab+i;
- if (tab->keyuse && tab->keyuse->outer_ref)
+ if (tab && tab->keyuse)
{
- 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;
+ for (uint i= 0; i < tab->ref.key_parts; i++)
+ {
+ 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;
+ break;
+ }
+ }
}
}
}
join->exec();
- if (full_scan)
+ /* Enable the optimizations back */
+ for (JOIN_TAB **ptab= changed_tabs; ptab != last_changed_tab; ptab++)
{
- /* Enable the optimizations back */
- for (uint i=join->const_tables ; i < join->tables ; i++)
- {
- JOIN_TAB *tab=join->join_tab+i;
- if (tab->keyuse && tab->keyuse->outer_ref)
- {
- tab->read_record.record= 0;
- tab->read_record.ref_length= 0;
- tab->read_first_record= join_read_always_key_or_null;
- tab->read_record.read_record= join_read_next_same_or_null;
- }
- }
+ JOIN_TAB *tab= *ptab;
+ tab->read_record.record= 0;
+ tab->read_record.ref_length= 0;
+ tab->read_first_record= join_read_always_key_or_null;
+ tab->read_record.read_record= join_read_next_same_or_null;
}
executed= 1;
thd->where= save_where;
@@ -1778,13 +1814,9 @@
DBUG_RETURN(0);
}
-int subselect_union_engine::exec(bool full_scan)
+int subselect_union_engine::exec()
{
char const *save_where= thd->where;
- /*
- Ignore the full_scan parameter: the pushed down predicates are only used
- for filtering, and the caller has disabled them if necessary.
- */
int res= unit->exec();
thd->where= save_where;
return res;
@@ -1792,7 +1824,7 @@
/*
- Search for at least on row satisfying select condition
+ Search for at least one row satisfying select condition
SYNOPSIS
subselect_uniquesubquery_engine::scan_table()
@@ -1801,8 +1833,8 @@
Scan the table using sequential access until we find at least one row
satisfying select condition.
- The result of this function (info about whether a row was found) is
- stored in this->empty_result_set.
+ The caller must set this->empty_result_set=FALSE before calling this
+ function. This function will set it to TRUE if it finds a matching row.
RETURN
FALSE - OK
@@ -1814,7 +1846,6 @@
int error;
TABLE *table= tab->table;
DBUG_ENTER("subselect_uniquesubquery_engine::scan_table");
- empty_result_set= TRUE;
if (table->file->inited)
table->file->ha_index_end();
@@ -1907,10 +1938,13 @@
- FALSE otherwise.
In some cases (IN subselect is a top level item, i.e. abort_on_null==TRUE)
- the caller doesn't distinguish between NULL and FALSE result and we just
+ the caller doesn't distinguish between NULL and FALSE result and we just
return FALSE.
- Otherwise we make a full table scan to see if there is at least one matching row.
-
+ Otherwise we make a full table scan to see if there is at least one
+ matching row.
+
+ The result of this function (info about whether a row was found) is
+ stored in this->empty_result_set.
NOTE
RETURN
@@ -1918,11 +1952,12 @@
TRUE - an error occured while scanning
*/
-int subselect_uniquesubquery_engine::exec(bool full_scan)
+int subselect_uniquesubquery_engine::exec()
{
DBUG_ENTER("subselect_uniquesubquery_engine::exec");
int error;
TABLE *table= tab->table;
+ empty_result_set= TRUE;
/* TODO: change to use of 'full_scan' here? */
if (copy_ref_key())
@@ -1943,9 +1978,13 @@
{
error= 0;
table->null_row= 0;
- ((Item_in_subselect *) item)->value= (!table->status &&
- (!cond || cond->val_int()) ? 1 :
- 0);
+ if (!table->status && (!cond || cond->val_int()))
+ {
+ ((Item_in_subselect *) item)->value= 1;
+ empty_result_set= FALSE;
+ }
+ else
+ ((Item_in_subselect *) item)->value= 0;
}
DBUG_RETURN(error != 0);
@@ -2011,7 +2050,7 @@
1
*/
-int subselect_indexsubquery_engine::exec(bool full_scan)
+int subselect_indexsubquery_engine::exec()
{
DBUG_ENTER("subselect_indexsubquery_engine::exec");
int error;
@@ -2052,8 +2091,9 @@
table->null_row= 0;
if (!table->status)
{
- if (!cond || cond->val_int())
+ if ((!cond || cond->val_int()) && (!having || having->val_int()))
{
+ empty_result_set= FALSE;
if (null_finding)
((Item_in_subselect *) item)->was_null= 1;
else
@@ -2196,10 +2236,15 @@
str->append(key_info->name);
if (check_null)
str->append(STRING_WITH_LEN(" checking NULL"));
- if (cond)
+ if (cond)
{
str->append(STRING_WITH_LEN(" where "));
cond->print(str);
+ }
+ if (having)
+ {
+ str->append(STRING_WITH_LEN(" having "));
+ having->print(str);
}
str->append(')');
}
--- 1.85/sql/item_subselect.h 2007-01-12 23:22:47 +03:00
+++ 1.86/sql/item_subselect.h 2007-01-12 23:22:47 +03:00
@@ -94,7 +94,7 @@
return null_value;
}
bool fix_fields(THD *thd, Item **ref);
- virtual bool exec(bool full_scan);
+ virtual bool exec();
virtual void fix_length_and_dec();
table_map used_tables() const;
table_map not_null_tables() const { return 0; }
@@ -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,13 +250,21 @@
bool transformed;
public:
/* Used to trigger on/off conditions that were pushed down to subselect */
- bool enable_pushed_conds;
+ 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), upper_item(0)
+ pushed_cond_guards(NULL), upper_item(0)
{}
subs_type substype() { return IN_SUBS; }
@@ -340,23 +349,22 @@
SYNOPSIS
exec()
- full_scan TRUE - Pushed-down predicates are disabled, the engine
- must disable made based on those predicates.
- FALSE - Pushed-down predicates are in effect.
+
DESCRIPTION
Execute the engine. The result of execution is subquery value that is
either captured by previously set up select_result-based 'sink' or
stored somewhere by the exec() method itself.
- A required side effect: if full_scan==TRUE, subselect_engine->no_rows()
- should return correct result.
+ A required side effect: If at least one pushed-down predicate is
+ disabled, subselect_engine->no_rows() must return correct result after
+ the exec() call.
RETURN
0 - OK
- 1 - Either an execution error, or the engine was be "changed", and
+ 1 - Either an execution error, or the engine was "changed", and the
caller should call exec() again for the new engine.
*/
- virtual int exec(bool full_scan)= 0;
+ virtual int exec()= 0;
virtual uint cols()= 0; /* return number of columns in select */
virtual uint8 uncacheable()= 0; /* query is uncacheable */
enum Item_result type() { return res_type; }
@@ -391,7 +399,7 @@
void cleanup();
int prepare();
void fix_length_and_dec(Item_cache** row);
- int exec(bool full_scan);
+ int exec();
uint cols();
uint8 uncacheable();
void exclude();
@@ -415,7 +423,7 @@
void cleanup();
int prepare();
void fix_length_and_dec(Item_cache** row);
- int exec(bool full_scan);
+ int exec();
uint cols();
uint8 uncacheable();
void exclude();
@@ -429,11 +437,30 @@
struct st_join_table;
+
+
+/*
+ A subquery execution engine that evaluates the subquery by doing one index
+ lookup in a unique index.
+
+ This engine is used to resolve subqueries in forms
+
+ outer_expr IN (SELECT tbl.unique_key FROM tbl WHERE subq_where)
+
+ or, tuple-based:
+
+ (oe1, .. oeN) IN (SELECT uniq_key_part1, ... uniq_key_partK
+ FROM tbl WHERE subqwhere)
+
+ i.e. the subquery is a single table SELECT without GROUP BY, aggregate
+ functions, etc.
+*/
+
class subselect_uniquesubquery_engine: public subselect_engine
{
protected:
st_join_table *tab;
- Item *cond;
+ Item *cond; /* The WHERE condition of subselect */
/*
TRUE<=> last execution produced empty set. Valid only when left
expression is NULL.
@@ -453,7 +480,7 @@
void cleanup();
int prepare();
void fix_length_and_dec(Item_cache** row);
- int exec(bool full_scan);
+ int exec();
uint cols() { return 1; }
uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; }
void exclude();
@@ -471,16 +498,47 @@
{
/* FALSE for 'ref', TRUE for 'ref-or-null'. */
bool check_null;
+ /*
+ The "having" clause. This clause (further reffered to as "artificial
+ having") was inserted by subquery transformation code. It contains
+ Item(s) that have a side-effect: they record whether the subquery has
+ produced a row with NULL certain components. We need to use it for cases
+ like
+ (oe1, oe2) IN (SELECT t.key, t.no_key FROM t1)
+ where we do index lookup on t.key=oe1 but need also to check if there
+ was a row such that t.no_key IS NULL.
+
+ NOTE: This is currently here and not in the uniquesubquery_engine. Ideally
+ it should have been in uniquesubquery_engine in order to allow execution of
+ subqueries like
+
+ (oe1, oe2) IN (SELECT primary_key, non_key_maybe_null_field FROM tbl)
+
+ We could use uniquesubquery_engine for the first component and let
+ Item_is_not_null_test( non_key_maybe_null_field) to handle the second.
+
+ However, subqueries like the above are currently not handled by index
+ lookup-based subquery engines, the engine applicability check misses
+ them: it doesn't switch the engine for case of artificial having and
+ [eq_]ref access (only for artifical having + ref_or_null or no having).
+ The above example subquery is handled as a full-blown SELECT with eq_ref
+ access to one table.
+
+ Due to this limitation, the "artificial having" currently needs to be
+ checked by only in indexsubquery_engine.
+ */
+ Item *having;
public:
// constructor can assign THD because it will be called after JOIN::prepare
subselect_indexsubquery_engine(THD *thd, st_join_table *tab_arg,
Item_subselect *subs, Item *where,
- bool chk_null)
+ Item *having_arg, bool chk_null)
:subselect_uniquesubquery_engine(thd, tab_arg, subs, where),
- check_null(chk_null)
+ check_null(chk_null),
+ having(having_arg)
{}
- int exec(bool full_scan);
+ int exec();
void print (String *str);
};
--- 1.2/mysql-test/r/subselect3.result 2007-01-12 23:22:47 +03:00
+++ 1.3/mysql-test/r/subselect3.result 2007-01-12 23:22:47 +03:00
@@ -89,10 +89,10 @@
select oref, a, a in (select a 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 4
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Using where
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 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`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`)))) AS `Z` from `test`.`t2`
+Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t2`
flush status;
select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
oref a
@@ -153,7 +153,7 @@
from t3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
-2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 Using where
+2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 Using where
Warnings:
Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
@@ -181,7 +181,7 @@
from t3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3
-2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 Using where
+2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 Using where
Warnings:
Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
@@ -229,3 +229,403 @@
NULL 1 NULL
NULL 0 0
drop table t1, t2;
+create table t3 (a int);
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, oref int);
+insert into t2 values (NULL,1, 100), (NULL,2, 100);
+create table t1 (a int, b int, c int, key(a,b));
+insert into t1 select 2*A, 2*A, 100 from t3;
+explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=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 2
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 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`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2`
+select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
+a b oref Z
+NULL 1 100 0
+NULL 2 100 NULL
+create table t4 (x int);
+insert into t4 select A.a + 10*B.a from t1 A, t1 B;
+explain extended
+select a,b, oref,
+(a,b) in (select a,b from t1,t4 where c=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 2
+2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 Using where
+Warnings:
+Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
+select a,b, oref,
+(a,b) in (select a,b from t1,t4 where c=t2.oref) Z
+from t2;
+a b oref Z
+NULL 1 100 0
+NULL 2 100 NULL
+drop table t1,t2,t3,t4;
+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);
+alter table t1 add index idx(ie1,ie2);
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ;
+oref a b Z
+cc 3 NULL NULL
+insert into t2 values ('new1', 10,10);
+insert into t1 values ('new1', 1234, 10, NULL);
+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;
+oref a b Z
+new1 10 10 NULL
+explain extended
+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;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 8 Using where
+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` 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.2/mysql-test/t/subselect3.test 2007-01-12 23:22:47 +03:00
+++ 1.3/mysql-test/t/subselect3.test 2007-01-12 23:22:47 +03:00
@@ -16,13 +16,14 @@
(3, 1, 4),
(3, 2, NULL);
-# Ok, for
+# Ok, for
# select max(ie) from t1 where oref=PARAM group by grp
# we'll have:
-# 1 -> (1, NULL) matching + NULL
-# 2 -> (3) non-matching
-# 3 -> (3, NULL) non-matching + NULL
-# 4 -> () nothing.
+# PARAM subquery result
+# 1 -> {(1), (NULL)} matching + NULL
+# 2 -> {(3)} non-matching
+# 3 -> {(3), (NULL)} non-matching + NULL
+# 4 -> {} empty set
create table t2 (oref int, a int);
insert into t2 values
@@ -141,7 +142,7 @@
#
-# BUG#24085
+# BUG#24085: Wrong query result for "NULL IN (SELECT ... UNION SELECT ...)"
#
# case 1: NULL IN (SELECT not_null_val FROM ...) w/o HAVING/GROUP-BY/etc
@@ -172,11 +173,13 @@
insert into t1 (oref, grp) values
(1, 1),
(1, 1);
-# Ok, for
-# select count(*) from t1 group by grp having grp=$PARAM$
+
+# Ok, for
+# select count(*) from t1 group by grp having grp=PARAM
# we'll have:
-# 1 -> (2)
-# 2 -> () - nothing
+# PARAM subuqery result
+# 1 -> {(2)}
+# 2 -> {} - empty set
create table t2 (oref int, a int);
insert into t2 values
(1, NULL),
@@ -201,4 +204,272 @@
t1 where t1.b = t2.b) Z from t2 ;
select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
drop table t1, t2;
+
+
+#
+# BUG#24127: Incorrect results of row-based subqueries with NULLs on the left side.
+#
+create table t3 (a int);
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, oref int);
+insert into t2 values (NULL,1, 100), (NULL,2, 100);
+
+create table t1 (a int, b int, c int, key(a,b));
+insert into t1 select 2*A, 2*A, 100 from t3;
+
+# First test index subquery engine
+explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
+select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
+
+# Then check that we do turn off 'ref' scans in the subquery
+create table t4 (x int);
+insert into t4 select A.a + 10*B.a from t1 A, t1 B;
+explain extended
+ select a,b, oref,
+ (a,b) in (select a,b from t1,t4 where c=t2.oref) Z
+ from t2;
+select a,b, oref,
+ (a,b) in (select a,b from t1,t4 where c=t2.oref) Z
+from t2;
+
+drop table t1,t2,t3,t4;
+
+# More tests for tricky multi-column cases, where some of pushed-down
+# equalities are used for index lookups and some arent.
+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);
+alter table t1 add index idx(ie1,ie2);
+
+--cc 3 NULL NULL
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ;
+insert into t2 values ('new1', 10,10);
+insert into t1 values ('new1', 1234, 10, NULL);
+-- new1, 10, 10, NULL,
+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;
+explain extended
+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 23:22:47 +03:00
+++ 1.4/mysql-test/r/ndb_subquery.result 2007-01-12 23:22:47 +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 |
