[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/

timour at askmonty.org timour at askmonty.org
Mon Aug 20 14:44:01 EEST 2012


At file:///home/tsk/mprog/src/5.5-md447/

------------------------------------------------------------
revno: 3498
revision-id: timour at askmonty.org-20120820114117-kgwsj5ilcovukqzi
parent: knielsen at knielsen-hq.org-20120817123528-eggusbbjukhxshvs
committer: timour at askmonty.org
branch nick: 5.5-md447
timestamp: Mon 2012-08-20 14:41:17 +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.
  
  ******
  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.
-------------- next part --------------
=== 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:41:17 +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:41:17 +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:41:17 +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:41:17 +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:41:17 +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:41:17 +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:41:17 +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:41:17 +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:41:17 +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
 



More information about the commits mailing list