[Maria-developers] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (igor:2791)
#At lp:maria/5.2 based on revid:igor@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) {
participants (1)
-
Igor Babaev