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-11 12:19:24+03:00, sergefp@stripped +10 -0
Merge spetrunia@stripped:/home/bk/mysql-5.0-opt
into mysql.com:/home/psergey/mysql-5.0-bug8804-r11
MERGE: 1.2248.175.5
mysql-test/r/subselect.result@stripped, 2007-01-11 12:19:17+03:00, sergefp@stripped +0 -0
Auto merged
MERGE: 1.161.1.9
sql/item_cmpfunc.cc@stripped, 2007-01-11 12:19:17+03:00, sergefp@stripped +0 -0
Auto merged
MERGE: 1.224.1.4
sql/item_cmpfunc.h@stripped, 2007-01-11 12:19:17+03:00, sergefp@stripped +0 -0
Auto merged
MERGE: 1.134.1.2
sql/item_subselect.cc@stripped, 2007-01-11 12:19:17+03:00, sergefp@stripped +0 -0
Auto merged
MERGE: 1.140.1.6
sql/item_subselect.h@stripped, 2007-01-11 12:19:18+03:00, sergefp@stripped +0 -0
Auto merged
MERGE: 1.81.1.4
sql/mysql_priv.h@stripped, 2007-01-11 12:19:18+03:00, sergefp@stripped +0 -0
Auto merged
MERGE: 1.417.1.2
sql/mysqld.cc@stripped, 2007-01-11 12:19:18+03:00, sergefp@stripped +0 -0
Auto merged
MERGE: 1.576.2.1
sql/sql_lex.h@stripped, 2007-01-11 12:19:18+03:00, sergefp@stripped +0 -0
Auto merged
MERGE: 1.225.4.5
sql/sql_select.cc@stripped, 2007-01-11 12:19:18+03:00, sergefp@stripped +0 -0
Auto merged
MERGE: 1.467.5.3
sql/sql_select.h@stripped, 2007-01-11 12:19:18+03:00, sergefp@stripped +0 -0
Auto merged
MERGE: 1.112.1.2
# 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/RESYNC
--- 1.225/sql/item_cmpfunc.cc 2007-01-11 12:19:32 +03:00
+++ 1.226/sql/item_cmpfunc.cc 2007-01-11 12:19:32 +03:00
@@ -1,9 +1,8 @@
-/* Copyright (C) 2000-2003 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
+/* Copyright (C) 2000-2006 MySQL AB
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
- the Free Software Foundation; either version 2 of the License, or
- (at your option) any later version.
+ the Free Software Foundation; version 2 of the License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
@@ -2251,7 +2250,7 @@
cmp_item_row::~cmp_item_row()
{
DBUG_ENTER("~cmp_item_row");
- DBUG_PRINT("enter",("this: 0x%lx", this));
+ DBUG_PRINT("enter",("this: 0x%lx", (long) this));
if (comparators)
{
for (uint i= 0; i < n; i++)
@@ -2520,7 +2519,6 @@
if (cmp_type == STRING_RESULT)
in_item->cmp_charset= cmp_collation.collation;
}
- maybe_null= args[0]->maybe_null;
max_length= 1;
}
@@ -3014,7 +3012,7 @@
Handle optimization if the argument can't be null
This has to be here because of the test in update_used_tables().
*/
- if (!used_tables_cache)
+ if (!used_tables_cache && !with_subselect)
return cached_value;
return args[0]->is_null() ? 1: 0;
}
@@ -3023,10 +3021,10 @@
{
DBUG_ASSERT(fixed == 1);
DBUG_ENTER("Item_is_not_null_test::val_int");
- if (!used_tables_cache)
+ if (!used_tables_cache && !with_subselect)
{
owner->was_null|= (!cached_value);
- DBUG_PRINT("info", ("cached :%d", cached_value));
+ DBUG_PRINT("info", ("cached :%ld", (long) cached_value));
DBUG_RETURN(cached_value);
}
if (args[0]->is_null())
@@ -3050,7 +3048,7 @@
else
{
args[0]->update_used_tables();
- if (!(used_tables_cache=args[0]->used_tables()))
+ if (!(used_tables_cache=args[0]->used_tables()) && !with_subselect)
{
/* Remember if the value is always NULL or never NULL */
cached_value= (longlong) !args[0]->is_null();
--- 1.136/sql/item_cmpfunc.h 2007-01-11 12:19:32 +03:00
+++ 1.137/sql/item_cmpfunc.h 2007-01-11 12:19:32 +03:00
@@ -2,8 +2,7 @@
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
- the Free Software Foundation; either version 2 of the License, or
- (at your option) any later version.
+ the Free Software Foundation; version 2 of the License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
@@ -1028,7 +1027,8 @@
else
{
args[0]->update_used_tables();
- if ((const_item_cache= !(used_tables_cache= args[0]->used_tables())))
+ if ((const_item_cache= !(used_tables_cache= args[0]->used_tables())) &&
+ !with_subselect)
{
/* Remember if the value is always NULL or never NULL */
cached_value= (longlong) args[0]->is_null();
--- 1.427/sql/mysql_priv.h 2007-01-11 12:19:32 +03:00
+++ 1.428/sql/mysql_priv.h 2007-01-11 12:19:32 +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-11 12:19:32 +03:00
+++ 1.586/sql/mysqld.cc 2007-01-11 12:19:32 +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-11 12:19:32 +03:00
+++ 1.236/sql/sql_lex.h 2007-01-11 12:19:32 +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-11 12:19:32 +03:00
+++ 1.479/sql/sql_select.cc 2007-01-11 12:19:32 +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.
+
+ 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 ^.
*/
-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)++;
}
@@ -2956,7 +2966,7 @@
/*
Subquery optimization: check if the encountered condition is one
- added by condition push down into subquery.
+ added by condition push down into subquery. (psergey-todo: better comment)
*/
{
if (cond->type() == Item::FUNC_ITEM &&
@@ -2973,7 +2983,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)->trig_var);
}
return;
}
@@ -3153,7 +3163,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 +5002,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 +5018,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 +5036,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;
@@ -10911,7 +10925,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 +14835,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 +14911,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-11 12:19:32 +03:00
+++ 1.115/sql/sql_select.h 2007-01-11 12:19:32 +03:00
@@ -1,9 +1,8 @@
-/* Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
+/* Copyright (C) 2000-2006 MySQL AB
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
- the Free Software Foundation; either version 2 of the License, or
- (at your option) any later version.
+ the Free Software Foundation; version 2 of the License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
--- 1.167/mysql-test/r/subselect.result 2007-01-11 12:19:32 +03:00
+++ 1.168/mysql-test/r/subselect.result 2007-01-11 12:19:32 +03:00
@@ -744,7 +744,7 @@
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1))) union select 3 AS `3` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3)))))
+Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
id
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
@@ -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;
@@ -907,7 +907,7 @@
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and trigcond(((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
drop table t1,t2,t3;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
@@ -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.145/sql/item_subselect.cc 2007-01-11 12:19:32 +03:00
+++ 1.146/sql/item_subselect.cc 2007-01-11 12:19:32 +03:00
@@ -2,8 +2,7 @@
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
- the Free Software Foundation; either version 2 of the License, or
- (at your option) any later version.
+ the Free Software Foundation; version 2 of the License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
@@ -54,7 +53,7 @@
{
DBUG_ENTER("Item_subselect::init");
- DBUG_PRINT("enter", ("select_lex: 0x%x", (ulong) select_lex));
+ DBUG_PRINT("enter", ("select_lex: 0x%lx", (long) select_lex));
unit= select_lex->master_unit();
if (unit->item)
@@ -349,6 +348,7 @@
*/
!(select_lex->item_list.head()->type() == FIELD_ITEM ||
select_lex->item_list.head()->type() == REF_ITEM) &&
+ !join->conds && !join->having &&
/*
switch off this optimization for prepare statement,
because we do not rollback this changes
@@ -373,8 +373,6 @@
*/
substitution->walk(&Item::remove_dependence_processor,
(byte *) select_lex->outer_select());
- /* SELECT without FROM clause can't have WHERE or HAVING clause */
- DBUG_ASSERT(join->conds == 0 && join->having == 0);
return RES_REDUCE;
}
return RES_OK;
@@ -2342,6 +2340,22 @@
bool subselect_single_select_engine::no_tables()
{
return(select_lex->table_list.elements == 0);
+}
+
+
+/*
+ Check statically whether the subquery can return NULL
+
+ SINOPSYS
+ subselect_single_select_engine::may_be_null()
+
+ RETURN
+ FALSE can guarantee that the subquery never return NULL
+ TRUE otherwise
+*/
+bool subselect_single_select_engine::may_be_null()
+{
+ return ((no_tables() && !join->conds && !join->having) ? maybe_null : 1);
}
--- 1.84/sql/item_subselect.h 2007-01-11 12:19:32 +03:00
+++ 1.85/sql/item_subselect.h 2007-01-11 12:19:32 +03:00
@@ -2,8 +2,7 @@
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
- the Free Software Foundation; either version 2 of the License, or
- (at your option) any later version.
+ the Free Software Foundation; version 2 of the License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
@@ -91,7 +90,7 @@
enum Type type() const;
bool is_null()
{
- val_int();
+ update_null_value();
return null_value;
}
bool fix_fields(THD *thd, Item **ref);
@@ -365,7 +364,7 @@
enum Item_result type() { return res_type; }
enum_field_types field_type() { return res_field_type; }
virtual void exclude()= 0;
- bool may_be_null() { return maybe_null; };
+ virtual bool may_be_null() { return maybe_null; };
virtual table_map upper_select_const_tables()= 0;
static table_map calc_const_tables(TABLE_LIST *);
virtual void print(String *str)= 0;
@@ -402,6 +401,7 @@
void print (String *str);
bool change_result(Item_subselect *si, select_subselect *result);
bool no_tables();
+ bool may_be_null();
bool is_executed() const { return executed; }
bool no_rows();
};
| Thread |
|---|
| • bk commit into 5.0 tree (sergefp:1.2375) | Sergey Petrunia | 11 Jan |
