#At lp:maria/5.2 based on
revid:i...@askmonty.org-20100513065914-yq9y2pbd9zn2fm7w
2791 Igor Babaev 2010-05-18
Fixed bugs in the backport of derived tables (mwl106).
modified:
mysql-test/r/derived_view.result
mysql-test/r/table_elim.result
sql/item_cmpfunc.cc
sql/item_cmpfunc.h
sql/sql_class.h
sql/sql_select.cc
sql/sql_select.h
sql/sql_union.cc
=== modified file 'mysql-test/r/derived_view.result'
--- a/mysql-test/r/derived_view.result 2010-04-29 21:10:39 +0000
+++ b/mysql-test/r/derived_view.result 2010-05-18 17:46:32 +0000
@@ -442,7 +442,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00
Using where
1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00
Using where; Using join buffer
Warnings:
-Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS
`f11`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` join
`test`.`t1` where ((`test`.`t1`.`f1` = `test`.`t1`.`f1`) and (`test`.`t1`.`f1`
> 2) and (`test`.`t1`.`f1` < 7) and ((`test`.`t1`.`f1` > 2) and
(`test`.`t1`.`f1` < 7)))
+Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS
`f11`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` join
`test`.`t1` where ((`test`.`t1`.`f1` = `test`.`t1`.`f1`) and (`test`.`t1`.`f1`
> 2) and (`test`.`t1`.`f1` < 7) and (`test`.`t1`.`f1` > 2) and
(`test`.`t1`.`f1` < 7))
select * from
(select * from
(select * from t1 where f1 < 7 ) tt where f1 > 2 ) x
=== modified file 'mysql-test/r/table_elim.result'
--- a/mysql-test/r/table_elim.result 2010-03-20 12:01:47 +0000
+++ b/mysql-test/r/table_elim.result 2010-05-18 17:46:32 +0000
@@ -117,58 +117,58 @@ t2 where id=f.id);
This should use one table:
explain select id from v1 where id=2;
id select_type table type possible_keys key key_len ref
rows Extra
-1 PRIMARY f const PRIMARY PRIMARY 4 const 1 Using
index
+1 SIMPLE f const PRIMARY PRIMARY 4 const 1 Using
index
This should use one table:
explain extended select id from v1 where id in (1,2,3,4);
id select_type table type possible_keys key key_len ref
rows filtered Extra
-1 PRIMARY f range PRIMARY PRIMARY 4 NULL 4 100.00
Using where; Using index
+1 SIMPLE f range PRIMARY PRIMARY 4 NULL 4 100.00
Using where; Using index
Warnings:
-Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in
SELECT #1
+Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in
SELECT #2
Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in
(1,2,3,4))
This should use facts and a1 tables:
explain extended select id from v1 where attr1 between 12 and 14;
id select_type table type possible_keys key key_len ref
rows filtered Extra
-1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2
100.00 Using index condition; Using MRR
-1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1
100.00 Using index
+1 SIMPLE a1 range PRIMARY,attr1 attr1 5 NULL 2
100.00 Using index condition; Using MRR
+1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a1.id 1
100.00 Using index
Warnings:
-Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in
SELECT #1
+Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in
SELECT #2
Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1`
`a1` where ((`f`.`id` = `a1`.`id`) and (`a1`.`attr1` between 12 and 14))
This should use facts, a2 and its subquery:
explain extended select id from v1 where attr2 between 12 and 14;
id select_type table type possible_keys key key_len ref
rows filtered Extra
-1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5
100.00 Using index condition; Using where; Using MRR
-1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1
100.00 Using index
+1 SIMPLE a2 range PRIMARY,attr2 attr2 5 NULL 5
100.00 Using index condition; Using where; Using MRR
+1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a2.id 1
100.00 Using index
3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4
test.a2.id 2 100.00 Using index
Warnings:
-Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in
SELECT #1
+Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in
SELECT #2
Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2`
`a2` where ((`f`.`id` = `a2`.`id`) and (`a2`.`attr2` between 12 and 14) and
(`a2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from
`test`.`t2` where (`test`.`t2`.`id` = `a2`.`id`))))
This should use one table:
explain select id from v2 where id=2;
id select_type table type possible_keys key key_len ref
rows Extra
-1 PRIMARY f const PRIMARY PRIMARY 4 const 1 Using
index
+1 SIMPLE f const PRIMARY PRIMARY 4 const 1 Using
index
This should use one table:
explain extended select id from v2 where id in (1,2,3,4);
id select_type table type possible_keys key key_len ref
rows filtered Extra
-1 PRIMARY f range PRIMARY PRIMARY 4 NULL 4 100.00
Using where; Using index
+1 SIMPLE f range PRIMARY PRIMARY 4 NULL 4 100.00
Using where; Using index
Warnings:
-Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in
SELECT #1
+Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in
SELECT #2
Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in
(1,2,3,4))
This should use facts and a1 tables:
explain extended select id from v2 where attr1 between 12 and 14;
id select_type table type possible_keys key key_len ref
rows filtered Extra
-1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2
100.00 Using index condition; Using MRR
-1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1
100.00 Using index
+1 SIMPLE a1 range PRIMARY,attr1 attr1 5 NULL 2
100.00 Using index condition; Using MRR
+1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a1.id 1
100.00 Using index
Warnings:
-Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in
SELECT #1
+Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in
SELECT #2
Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1`
`a1` where ((`f`.`id` = `a1`.`id`) and (`a1`.`attr1` between 12 and 14))
This should use facts, a2 and its subquery:
explain extended select id from v2 where attr2 between 12 and 14;
id select_type table type possible_keys key key_len ref
rows filtered Extra
-1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5
100.00 Using index condition; Using MRR
-1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1
100.00 Using where; Using index
+1 SIMPLE a2 range PRIMARY,attr2 attr2 5 NULL 5
100.00 Using index condition; Using MRR
+1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a2.id 1
100.00 Using where; Using index
3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4
test.f.id 2 100.00 Using index
Warnings:
-Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in
SELECT #1
+Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in
SELECT #2
Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2`
`a2` where ((`f`.`id` = `a2`.`id`) and (`a2`.`attr2` between 12 and 14) and
(`a2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from
`test`.`t2` where (`test`.`t2`.`id` = `f`.`id`))))
drop view v1, v2;
drop table t0, t1, t2;
=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc 2010-04-29 21:10:39 +0000
+++ b/sql/item_cmpfunc.cc 2010-05-18 17:46:32 +0000
@@ -4232,8 +4232,20 @@ Item_cond::fix_fields(THD *thd, Item **r
(item= *li.ref())->check_cols(1))
return TRUE; /* purecov: inspected */
used_tables_cache|= item->used_tables();
+#if 0
if (!item->const_item())
const_item_cache= FALSE;
+#else
+ if (item->const_item())
+ and_tables_cache= (table_map) 0;
+ else
+ {
+ table_map tmp_table_map= item->not_null_tables();
+ not_null_tables_cache|= tmp_table_map;
+ and_tables_cache&= tmp_table_map;
+ const_item_cache= FALSE;
+ }
+#endif
with_sum_func= with_sum_func || item->with_sum_func;
with_subselect|= item->with_subselect;
@@ -4253,6 +4265,7 @@ Item_cond::eval_not_null_tables(uchar *o
{
Item *item;
List_iterator<Item> li(list);
+ and_tables_cache= ~(table_map) 0;
while ((item=li++))
{
table_map tmp_table_map;
=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h 2010-04-29 21:10:39 +0000
+++ b/sql/item_cmpfunc.h 2010-05-18 17:46:32 +0000
@@ -1778,19 +1778,6 @@ inline Item *and_conds(Item *a, Item *b)
{
if (!b) return a;
if (!a) return b;
- /* Try to minimize item tree by adding to already present AND functions. */
- if (a->type() == Item::COND_ITEM &&
- ((Item_cond*) a)->functype() == Item_func::COND_AND_FUNC)
- {
- ((Item_cond*)a)->add(b);
- return a;
- }
- else if (b->type() == Item::COND_ITEM &&
- ((Item_cond*) b)->functype() == Item_func::COND_AND_FUNC)
- {
- ((Item_cond*)b)->add(a);
- return b;
- }
return new Item_cond_and(a, b);
}
=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h 2010-04-29 21:10:39 +0000
+++ b/sql/sql_class.h 2010-05-18 17:46:32 +0000
@@ -2790,11 +2790,6 @@ public:
*/
bool bit_fields_as_long;
- /*
- Whether to create or postpone actual creation of this temporary table.
- TRUE <=> create_tmp_table will create only the TABLE structure.
- */
- bool skip_create_table;
TMP_TABLE_PARAM()
:copy_field(0), group_parts(0),
group_length(0), group_null_parts(0), convert_blob_length(0),
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-05-12 04:09:58 +0000
+++ b/sql/sql_select.cc 2010-05-18 17:46:32 +0000
@@ -9845,8 +9845,9 @@ simplify_joins(JOIN *join, List<TABLE_LI
{
conds= and_conds(conds, table->on_expr);
conds->top_level_item();
- if (!conds->fixed)
- conds->fix_fields(join->thd, &conds);
+ /* conds is always a new item as both cond and on_expr existed */
+ DBUG_ASSERT(!conds->fixed);
+ conds->fix_fields(join->thd, &conds);
}
else
conds= table->on_expr;
@@ -11035,7 +11036,7 @@ TABLE *
create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
ORDER *group, bool distinct, bool save_sum_fields,
ulonglong select_options, ha_rows rows_limit,
- char *table_alias)
+ char *table_alias, bool do_not_open)
{
MEM_ROOT *mem_root_save, own_root;
TABLE *table;
@@ -11728,7 +11729,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
if (thd->is_fatal_error) // If end of memory
goto err; /* purecov: inspected */
share->db_record_offset= 1;
- if (!param->skip_create_table)
+ if (!do_not_open)
{
if (share->db_type() == TMP_ENGINE_HTON)
{
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2010-04-29 21:10:39 +0000
+++ b/sql/sql_select.h 2010-05-18 17:46:32 +0000
@@ -1984,7 +1984,7 @@ void push_index_cond(JOIN_TAB *tab, uint
TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
ORDER *group, bool distinct, bool save_sum_fields,
ulonglong select_options, ha_rows rows_limit,
- char* alias);
+ char* alias, bool do_not_open=FALSE);
void free_tmp_table(THD *thd, TABLE *entry);
bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table,
ENGINE_COLUMNDEF *start_recinfo,
=== modified file 'sql/sql_union.cc'
--- a/sql/sql_union.cc 2010-05-12 04:09:58 +0000
+++ b/sql/sql_union.cc 2010-05-18 17:46:32 +0000
@@ -126,12 +126,11 @@ select_union::create_result_table(THD *t
tmp_table_param.init();
tmp_table_param.field_count= column_types->elements;
tmp_table_param.bit_fields_as_long= bit_fields_as_long;
- tmp_table_param.skip_create_table= !create_table;
-
if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
(ORDER*) 0, is_union_distinct, 1,
- options, HA_POS_ERROR, (char*) alias)))
+ options, HA_POS_ERROR, (char*) alias,
+ !create_table)))
return TRUE;
if (create_table)
{
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
