Hi Timour, Ok to push. On Thu, Nov 15, 2012 at 12:56:59PM +0200, Timour Katchaounov wrote:

Sergey,

Could you please review this trivial task, shouldn't take more than 10 min.

Timour

------------------------------------------------------------ revno: 3479 revision-id: timour@askmonty.org-20121115105450-zkiva73utg8pg49j parent: wlad@montyprogram.com-20121111095852-1jsqvi3zcdml80lt fixes bug: https://mariadb.atlassian.net/browse/MDEV-3858 committer: timour@askmonty.org branch nick: 10.0-md3858 timestamp: Thu 2012-11-15 12:54:50 +0200 message: MDEV-3858 Change JOIN_TAB::records_read from ha_rows to double

Currently JOIN_TAB::records_read is of type ha_rows. This is an integer type, which prevents proper selectivity and rows estimates.

=== modified file 'mysql-test/r/subselect_sj_mat.result' --- a/mysql-test/r/subselect_sj_mat.result 2012-07-25 16:53:49 +0000 +++ b/mysql-test/r/subselect_sj_mat.result 2012-11-15 10:54:50 +0000 @@ -94,7 +94,7 @@ a1 a2 explain extended select * from t1i where a1 in (select b1 from t2i where b1 > '0'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2i index it2i1,it2i3 it2i1 # NULL 5 40.00 Using where; Using index; LooseScan +1 PRIMARY t2i index it2i1,it2i3 it2i1 # NULL 5 50.00 Using where; Using index; LooseScan 1 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 Warnings: Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) @@ -117,7 +117,7 @@ a1 a2 explain extended select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 # NULL 5 40.00 Using where; Using index; LooseScan +1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 # NULL 5 50.00 Using where; Using index; LooseScan 1 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 Warnings: Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) @@ -319,7 +319,7 @@ where (a1, a2) in (select b1, b2 from t2 (a1, a2) in (select c1, c2 from t3i where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2i index it2i1,it2i2,it2i3 # # # 5 40.00 # +1 PRIMARY t2i index it2i1,it2i2,it2i3 # # # 5 50.00 # 1 PRIMARY t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # 1 PRIMARY t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 # 1 PRIMARY t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # @@ -407,7 +407,7 @@ id select_type table type possible_keys 2 MATERIALIZED t2 ALL NULL # # # 5 100.00 # 4 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 3 MATERIALIZED t3 ALL NULL # # # 4 100.00 # -7 UNION t2i index it2i1,it2i2,it2i3 # # # 5 40.00 # +7 UNION t2i index it2i1,it2i2,it2i3 # # # 5 50.00 # 7 UNION t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # 7 UNION t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 # 7 UNION t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 #

=== modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2012-11-03 11:28:51 +0000 +++ b/sql/item_subselect.cc 2012-11-15 10:54:50 +0000 @@ -4553,7 +4553,7 @@ double get_fanout_with_deps(JOIN *join, !tab->emb_sj_nest && tab->records_read != 0) { - fanout *= rows2double(tab->records_read); + fanout *= tab->records_read; } } return fanout;

=== modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2012-11-04 15:32:32 +0000 +++ b/sql/sql_select.cc 2012-11-15 10:54:50 +0000 @@ -6789,7 +6789,7 @@ void JOIN::get_prefix_cost_and_fanout(ui

double JOIN::get_examined_rows() { - ha_rows examined_rows; + double examined_rows; double prev_fanout= 1; JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS); JOIN_TAB *prev_tab= tab; @@ -6799,7 +6799,7 @@ double JOIN::get_examined_rows() while ((tab= next_breadth_first_tab(this, WALK_OPTIMIZATION_TABS, tab))) { prev_fanout *= prev_tab->records_read; - examined_rows+= (ha_rows) (tab->get_examined_rows() * prev_fanout); + examined_rows+= tab->get_examined_rows() * prev_fanout; prev_tab= tab; } return examined_rows; @@ -7704,7 +7704,8 @@ get_best_combination(JOIN *join) sub-order */ SJ_MATERIALIZATION_INFO *sjm= cur_pos->table->emb_sj_nest->sj_mat_info; - j->records= j->records_read= (ha_rows)(sjm->is_sj_scan? sjm->rows : 1); + j->records_read= sjm->is_sj_scan? sjm->rows : 1; + j->records= (ha_rows) j->records_read; JOIN_TAB *jt; JOIN_TAB_RANGE *jt_range; if (!(jt= (JOIN_TAB*)join->thd->alloc(sizeof(JOIN_TAB)*sjm->tables)) || @@ -7766,7 +7767,7 @@ get_best_combination(JOIN *join) Save records_read in JOIN_TAB so that select_describe()/etc don't have to access join->best_positions[]. */ - j->records_read= (ha_rows)join->best_positions[tablenr].records_read; + j->records_read= join->best_positions[tablenr].records_read; join->map2table[j->table->tablenr]= j;

/* If we've reached the end of sjm nest, switch back to main sequence */ @@ -10460,7 +10461,7 @@ double JOIN_TAB::scan_time()

ha_rows JOIN_TAB::get_examined_rows() { - ha_rows examined_rows; + double examined_rows;

if (select && select->quick && use_quick != 2) examined_rows= select->quick->records; @@ -10490,7 +10491,7 @@ ha_rows JOIN_TAB::get_examined_rows() } } else - examined_rows= (ha_rows) records_read; + examined_rows= records_read;

return examined_rows; } @@ -21972,7 +21973,7 @@ int JOIN::print_explain(select_result_si } else { - ha_rows examined_rows= tab->get_examined_rows(); + double examined_rows= tab->get_examined_rows();

item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows, MY_INT64_NUM_DECIMAL_DIGITS)); @@ -21982,7 +21983,7 @@ int JOIN::print_explain(select_result_si { float f= 0.0; if (examined_rows) - f= (float) (100.0 * tab->records_read / examined_rows); + f= (100.0 * (float)tab->records_read) / examined_rows; set_if_smaller(f, 100.0); item_list.push_back(new Item_float(f, 2)); }

=== modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2012-11-04 15:32:32 +0000 +++ b/sql/sql_select.h 2012-11-15 10:54:50 +0000 @@ -280,8 +280,8 @@ typedef struct st_join_table { */ double read_time;

- /* psergey-todo: make the below have type double, like POSITION::records_read? */ - ha_rows records_read; + /* Copy of POSITION::records_read, set by get_best_combination() */ + double records_read;

/* Startup cost for execution */ double startup_cost;

_______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

-- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog