maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04859
Re: Fwd: [Commits] Rev 3498: Fix bug mdev-447: Wrong output from the EXPLAIN command of the test case for lp bug #714999 in file:///home/tsk/mprog/src/5.5-md447/
Hi Timour
Ok to push.
On Mon, Aug 20, 2012 at 02:47:35PM +0300, Timour Katchaounov wrote:
> Sergey,
>
> Could you please review this simple patch.
> It is a backport of several lines from your code.
>
> Timour
>
> ------------------------------------------------------------
> revno: 3498
> revision-id: timour@xxxxxxxxxxxx-20120820114533-hznbt0z7ikdxoawq
> parent: knielsen@xxxxxxxxxxxxxxx-20120817123528-eggusbbjukhxshvs
> committer: timour@xxxxxxxxxxxx
> branch nick: 5.5-md447
> timestamp: Mon 2012-08-20 14:45:33 +0300
> message:
> Fix bug mdev-447: Wrong output from the EXPLAIN command of the test case for lp bug #714999
>
> The fix backports from MWL#182: Explain running statements the logic that
> saves the original JOIN_TAB array of a query plan after optimization. This
> array is later used during EXPLAIN to iterate over the original JOIN plan
> nodes in the cases when this plan could be changed by early subquery
> execution during the optimization phase of the outer query.
>
>
> === modified file 'mysql-test/r/subselect.result'
> --- a/mysql-test/r/subselect.result 2012-07-25 16:41:48 +0000
> +++ b/mysql-test/r/subselect.result 2012-08-20 11:45:33 +0000
> @@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
> EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
> -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
> DROP TABLE t1;
> CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
> INSERT INTO t1 VALUES
>
> === modified file 'mysql-test/r/subselect_innodb.result'
> --- a/mysql-test/r/subselect_innodb.result 2012-06-06 19:26:40 +0000
> +++ b/mysql-test/r/subselect_innodb.result 2012-08-20 11:45:33 +0000
> @@ -333,7 +333,7 @@ WHERE (SELECT DISTINCT b FROM t3) > 0);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using where; Using index
> 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
> -3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 0 Using temporary
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using temporary
> SELECT *
> FROM t1
> WHERE t1.a = (
> @@ -386,7 +386,7 @@ select 1 from t1 where 1 like (select 1
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 1
> 2 SUBQUERY t1 ALL NULL NULL NULL NULL 1
> -3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
> +3 SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using temporary; Using filesort
> select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1));
> 1
> 1
>
> === modified file 'mysql-test/r/subselect_mat_cost_bugs.result'
> --- a/mysql-test/r/subselect_mat_cost_bugs.result 2012-07-25 16:41:48 +0000
> +++ b/mysql-test/r/subselect_mat_cost_bugs.result 2012-08-20 11:45:33 +0000
> @@ -148,7 +148,7 @@ FROM t2 GROUP BY f1
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> 2 SUBQUERY t1 system NULL NULL NULL NULL 1
> -3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
> +3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
> drop table t1, t2, t3;
> #
> # LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed
>
> === modified file 'mysql-test/r/subselect_no_mat.result'
> --- a/mysql-test/r/subselect_no_mat.result 2012-07-25 16:41:48 +0000
> +++ b/mysql-test/r/subselect_no_mat.result 2012-08-20 11:45:33 +0000
> @@ -4200,7 +4200,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
> EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
> -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
> DROP TABLE t1;
> CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
> INSERT INTO t1 VALUES
>
> === modified file 'mysql-test/r/subselect_no_opts.result'
> --- a/mysql-test/r/subselect_no_opts.result 2012-07-25 16:41:48 +0000
> +++ b/mysql-test/r/subselect_no_opts.result 2012-08-20 11:45:33 +0000
> @@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
> EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
> -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
> DROP TABLE t1;
> CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
> INSERT INTO t1 VALUES
>
> === modified file 'mysql-test/r/subselect_no_scache.result'
> --- a/mysql-test/r/subselect_no_scache.result 2012-07-25 16:41:48 +0000
> +++ b/mysql-test/r/subselect_no_scache.result 2012-08-20 11:45:33 +0000
> @@ -4202,7 +4202,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
> EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
> -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
> DROP TABLE t1;
> CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
> INSERT INTO t1 VALUES
>
> === modified file 'mysql-test/r/subselect_no_semijoin.result'
> --- a/mysql-test/r/subselect_no_semijoin.result 2012-07-25 16:41:48 +0000
> +++ b/mysql-test/r/subselect_no_semijoin.result 2012-08-20 11:45:33 +0000
> @@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
> EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
> -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
> DROP TABLE t1;
> CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
> INSERT INTO t1 VALUES
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2012-08-15 06:34:18 +0000
> +++ b/sql/sql_select.cc 2012-08-20 11:45:33 +0000
> @@ -271,8 +271,11 @@ Item_equal *find_item_equal(COND_EQUAL *
> bool *inherited_fl);
> JOIN_TAB *first_depth_first_tab(JOIN* join);
> JOIN_TAB *next_depth_first_tab(JOIN* join, JOIN_TAB* tab);
> -JOIN_TAB *first_breadth_first_tab(JOIN *join);
> -JOIN_TAB *next_breadth_first_tab(JOIN *join, JOIN_TAB *tab);
> +
> +enum enum_exec_or_opt {WALK_OPTIMIZATION_TABS , WALK_EXECUTION_TABS};
> +JOIN_TAB *first_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind);
> +JOIN_TAB *next_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind,
> + JOIN_TAB *tab);
>
> /**
> This handles SELECT with and without UNION.
> @@ -6649,12 +6652,12 @@ double JOIN::get_examined_rows()
> {
> ha_rows examined_rows;
> double prev_fanout= 1;
> - JOIN_TAB *tab= first_breadth_first_tab(this);
> + JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS);
> JOIN_TAB *prev_tab= tab;
>
> examined_rows= tab->get_examined_rows();
>
> - while ((tab= next_breadth_first_tab(this, tab)))
> + 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);
> @@ -7269,23 +7272,30 @@ prev_record_reads(POSITION *positions, u
> Enumerate join tabs in breadth-first fashion, including const tables.
> */
>
> -JOIN_TAB *first_breadth_first_tab(JOIN *join)
> +JOIN_TAB *first_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind)
> {
> - return join->join_tab; /* There's always one (i.e. first) table */
> + /* There's always one (i.e. first) table */
> + return (tabs_kind == WALK_EXECUTION_TABS)? join->join_tab:
> + join->table_access_tabs;
> }
>
>
> -JOIN_TAB *next_breadth_first_tab(JOIN *join, JOIN_TAB *tab)
> +JOIN_TAB *next_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind,
> + JOIN_TAB *tab)
> {
> + JOIN_TAB* const first_top_tab= first_breadth_first_tab(join, tabs_kind);
> + const uint n_top_tabs_count= (tabs_kind == WALK_EXECUTION_TABS)?
> + join->top_join_tab_count:
> + join->top_table_access_tabs_count;
> if (!tab->bush_root_tab)
> {
> /* We're at top level. Get the next top-level tab */
> tab++;
> - if (tab < join->join_tab + join->top_join_tab_count)
> + if (tab < first_top_tab + n_top_tabs_count)
> return tab;
>
> /* No more top-level tabs. Switch to enumerating SJM nest children */
> - tab= join->join_tab;
> + tab= first_top_tab;
> }
> else
> {
> @@ -7309,7 +7319,7 @@ JOIN_TAB *next_breadth_first_tab(JOIN *j
> Ok, "tab" points to a top-level table, and we need to find the next SJM
> nest and enter it.
> */
> - for (; tab < join->join_tab + join->top_join_tab_count; tab++)
> + for (; tab < first_top_tab + n_top_tabs_count; tab++)
> {
> if (tab->bush_children)
> return tab->bush_children->start;
> @@ -7333,7 +7343,7 @@ JOIN_TAB *first_top_level_tab(JOIN *join
>
> JOIN_TAB *next_top_level_tab(JOIN *join, JOIN_TAB *tab)
> {
> - tab= next_breadth_first_tab(join, tab);
> + tab= next_breadth_first_tab(join, WALK_EXECUTION_TABS, tab);
> if (tab && tab->bush_root_tab)
> tab= NULL;
> return tab;
> @@ -7633,6 +7643,12 @@ get_best_combination(JOIN *join)
>
> join->top_join_tab_count= join->join_tab_ranges.head()->end -
> join->join_tab_ranges.head()->start;
> + /*
> + Save pointers to select join tabs for SHOW EXPLAIN
> + */
> + join->table_access_tabs= join->join_tab;
> + join->top_table_access_tabs_count= join->top_join_tab_count;
> +
> update_depend_map(join);
> DBUG_RETURN(0);
> }
> @@ -21389,8 +21405,8 @@ static void select_describe(JOIN *join,
> bool printing_materialize_nest= FALSE;
> uint select_id= join->select_lex->select_number;
>
> - for (JOIN_TAB *tab= first_breadth_first_tab(join); tab;
> - tab= next_breadth_first_tab(join, tab))
> + for (JOIN_TAB *tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); tab;
> + tab= next_breadth_first_tab(join, WALK_OPTIMIZATION_TABS, tab))
> {
> if (tab->bush_root_tab)
> {
> @@ -21473,16 +21489,8 @@ static void select_describe(JOIN *join,
> else
> {
> TABLE_LIST *real_table= table->pos_in_table_list;
> - /*
> - Internal temporary tables have no corresponding table reference
> - object. Such a table may appear in EXPLAIN when a subquery that needs
> - a temporary table has been executed, and JOIN::exec replaced the
> - original JOIN with a plan to access the data in the temp table
> - (made by JOIN::make_simple_join).
> - */
> - const char *tab_name= real_table ? real_table->alias :
> - "internal_tmp_table";
> - item_list.push_back(new Item_string(tab_name, strlen(tab_name), cs));
> + item_list.push_back(new Item_string(real_table->alias,
> + strlen(real_table->alias), cs));
> }
> /* "partitions" column */
> if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
>
> === modified file 'sql/sql_select.h'
> --- a/sql/sql_select.h 2012-06-06 19:26:40 +0000
> +++ b/sql/sql_select.h 2012-08-20 11:45:33 +0000
> @@ -897,6 +897,19 @@ class JOIN :public Sql_alloc
>
> public:
> JOIN_TAB *join_tab, **best_ref;
> +
> + /*
> + For "Using temporary+Using filesort" queries, JOIN::join_tab can point to
> + either:
> + 1. array of join tabs describing how to run the select, or
> + 2. array of single join tab describing read from the temporary table.
> +
> + SHOW EXPLAIN code needs to read/show #1. This is why two next members are
> + there for saving it.
> + */
> + JOIN_TAB *table_access_tabs;
> + uint top_table_access_tabs_count;
> +
> JOIN_TAB **map2table; ///< mapping between table indexes and JOIN_TABs
> JOIN_TAB *join_tab_save; ///< saved join_tab for subquery reexecution
>
>
> _______________________________________________
> commits mailing list
> commits@xxxxxxxxxxx
> 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
