[Commits] Rev 3377: Apply earlier patch: correct handling of subqueries that in file:///home/psergey/dev2/5.3-show-explain-r10/

Sergey Petrunya psergey at askmonty.org
Wed Jan 4 02:57:04 EET 2012


At file:///home/psergey/dev2/5.3-show-explain-r10/

------------------------------------------------------------
revno: 3377
revision-id: psergey at askmonty.org-20120104005701-9xjn0m4j5ghw2mh0
parent: psergey at askmonty.org-20120103221616-ix6yvribpixbtn6x
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-show-explain-r10
timestamp: Wed 2012-01-04 04:57:01 +0400
message:
  Apply earlier patch: correct handling of subqueries that 
  were not yet optimized or already executed and deleted.
=== modified file 'mysql-test/r/show_explain.result'
--- a/mysql-test/r/show_explain.result	2011-10-27 22:30:02 +0000
+++ b/mysql-test/r/show_explain.result	2012-01-04 00:57:01 +0000
@@ -15,7 +15,7 @@
 show explain for $thr1;
 ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 select count(*) from t1 where a < 100000;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -25,7 +25,7 @@
 select max(c) from t1 where a < 10;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	10	Using where
+1	SIMPLE	t1	range	a	a	5	NULL	10	Using index condition
 max(c)
 9
 # We can catch EXPLAIN, too.
@@ -40,7 +40,7 @@
 set optimizer_switch= @show_expl_tmp;
 # UNION, first branch 
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 explain select a from t0 A union select a+1 from t0 B;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -53,7 +53,7 @@
 NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
 # UNION, second branch
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 explain select a from t0 A union select a+1 from t0 B;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -66,7 +66,7 @@
 NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
 # Uncorrelated  subquery, select
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 select a, (select max(a) from t0 B) from t0 A where a<1;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -76,7 +76,7 @@
 0	9
 # Uncorrelated  subquery, explain
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 explain select a, (select max(a) from t0 B) from t0 A where a<1;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -87,7 +87,7 @@
 2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	
 # correlated  subquery, select
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -97,7 +97,7 @@
 0	9
 # correlated  subquery, explain
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -107,7 +107,7 @@
 0	9
 # correlated  subquery, select, while inside the subquery
 set @show_explain_probe_select_id=2;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -117,7 +117,7 @@
 0	9
 # correlated  subquery, explain, while inside the subquery
 set @show_explain_probe_select_id=2;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -125,4 +125,13 @@
 2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
 a	(select max(a) from t0 b where b.a+a.a<10)
 0	9
+# correlated  subquery, explain, while inside the subquery
+set @show_explain_probe_select_id=1;
+set debug='d,show_explain_probe_join_exec_end';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Query plan already deleted
+a	(select max(a) from t0 b where b.a+a.a<10)
+0	9
 drop table t0,t1;

=== modified file 'mysql-test/t/show_explain.test'
--- a/mysql-test/t/show_explain.test	2011-10-27 22:30:02 +0000
+++ b/mysql-test/t/show_explain.test	2012-01-04 00:57:01 +0000
@@ -12,17 +12,17 @@
 # is that we use the following commands for synchronization:
 #
 #    set @show_explain_probe_select_id=1;
-#    set debug='d,show_explain_probe_1';
+#    set debug='d,show_explain_probe_join_exec_start';
 #    send select count(*) from t1 where a < 100000;
 #
 # When ran with mysqltest_embedded, this translates into: 
 #
-#    Thread1> DBUG_PUSH("d,show_explain_probe_1");
+#    Thread1> DBUG_PUSH("d,show_explain_probe_join_exec_start");
 #    Thread1> create another thread for doing "send ... reap"
 #    Thread2> mysql_parse("select count(*) from t1 where a < 100000");
 #
 # That is, "select count(*) ..." is ran in a thread for which DBUG_PUSH(...)
-# has not been called. As a result, show_explain_probe_1 does not fire, and
+# has not been called. As a result, show_explain_probe_join_exec_start does not fire, and
 # "select count(*) ..." does not wait till its SHOW EXPLAIN command, and the
 # test fails.
 #
@@ -70,7 +70,7 @@
 #
 connection con1;
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 send select count(*) from t1 where a < 100000;
 
 connection default;
@@ -102,7 +102,7 @@
 
 --echo # UNION, first branch 
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 send explain select a from t0 A union select a+1 from t0 B;
 connection default;
 --source include/wait_condition.inc
@@ -113,7 +113,7 @@
 
 --echo # UNION, second branch
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 send explain select a from t0 A union select a+1 from t0 B;
 connection default;
 --source include/wait_condition.inc
@@ -124,7 +124,7 @@
 
 --echo # Uncorrelated  subquery, select
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 send select a, (select max(a) from t0 B) from t0 A where a<1;
 connection default;
 --source include/wait_condition.inc
@@ -135,7 +135,7 @@
 
 --echo # Uncorrelated  subquery, explain
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 send explain select a, (select max(a) from t0 B) from t0 A where a<1;
 connection default;
 --source include/wait_condition.inc
@@ -145,7 +145,7 @@
 
 --echo # correlated  subquery, select
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
 connection default;
 --source include/wait_condition.inc
@@ -155,7 +155,7 @@
 
 --echo # correlated  subquery, explain
 set @show_explain_probe_select_id=1;
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
 connection default;
 --source include/wait_condition.inc
@@ -165,7 +165,7 @@
 
 --echo # correlated  subquery, select, while inside the subquery
 set @show_explain_probe_select_id=2; # <---
-set debug='d,show_explain_probe_1';
+set debug='d,show_explain_probe_join_exec_start';
 send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
 connection default;
 --source include/wait_condition.inc
@@ -175,14 +175,24 @@
 
 --echo # correlated  subquery, explain, while inside the subquery
 set @show_explain_probe_select_id=2;
-set debug='d,show_explain_probe_1';
-send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
-connection default;
---source include/wait_condition.inc
-evalp show explain for $thr2;
-connection con1;
-reap;
-
+set debug='d,show_explain_probe_join_exec_start';
+send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+--echo # correlated  subquery, explain, while inside the subquery
+set @show_explain_probe_select_id=1;
+set debug='d,show_explain_probe_join_exec_end';
+send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
 
 # TODO: explain in the parent subuqery when the un-correlated child has been
 # run (and have done irreversible cleanups)
@@ -195,4 +205,7 @@
 ## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
 ##       thread and served together.
 
+## TODO: SHOW EXPLAIN while the primary query is running EXPLAIN EXTENDED/PARTITIONS
+##
+
 drop table t0,t1;

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2012-01-03 22:16:16 +0000
+++ b/sql/sql_lex.cc	2012-01-04 00:57:01 +0000
@@ -3555,16 +3555,11 @@
       using_materialization= TRUE;
   }
 
-  if (this == master_unit()->fake_select_lex)
-    type= "UNION RESULT";
-
   if (&master_unit()->thd->lex->select_lex == this)
   {
      type= is_primary ? "PRIMARY" : "SIMPLE";
   }
-  
-  if (!on_the_fly)
-  //  else
+  else
   {
     if (this == first)
     {
@@ -3594,9 +3589,14 @@
       else
       {
         type= is_uncacheable ? "UNCACHEABLE UNION": "UNION";
+        if (this == master_unit()->fake_select_lex)
+          type= "UNION RESULT";
+
       }
     }
   }
+
+  if (!on_the_fly)
     options|= SELECT_DESCRIBE;
 }
 
@@ -3744,10 +3744,17 @@
 }
 
 
+int print_explain_message_line(select_result_sink *result, 
+                               SELECT_LEX *select_lex,
+                               bool on_the_fly,
+                               uint8 options,
+                               const char *message);
+
+
 int st_select_lex::print_explain(select_result_sink *output)
 {
   int res;
-  if (join && join->optimized == 2)
+  if (join && join->have_query_plan == JOIN::QEP_AVAILABLE)
   {
     res= join->print_explain(output, TRUE,
                              FALSE, // need_tmp_table, 
@@ -3773,13 +3780,18 @@
   }
   else
   {
-    /* Produce "not yet optimized" line */
-    const char *msg="Not yet optimized";
-    res= join->print_explain(output, TRUE,
-                             FALSE, // need_tmp_table, 
-                             FALSE, // bool need_order,
-                             FALSE, // bool distinct,
-                             msg); //const char *message
+    const char *msg;
+    if (!join)
+      DBUG_ASSERT(0); // psergey: TODO: can this happen or not?
+    if (join->have_query_plan == JOIN::QEP_NOT_PRESENT_YET)
+      msg= "Not yet optimized";
+    else
+    {
+      DBUG_ASSERT(join->have_query_plan == JOIN::QEP_DELETED);
+      msg= "Query plan already deleted";
+    }
+    res= print_explain_message_line(output, this, TRUE /* on_the_fly */,
+                                    0, msg);
   }
 err:
   return 0;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-01-03 22:16:16 +0000
+++ b/sql/sql_select.cc	2012-01-04 00:57:01 +0000
@@ -931,7 +931,8 @@
 int JOIN::optimize()
 {
   int res= optimize_inner();
-  optimized= 2;
+  if (!res)
+    have_query_plan= QEP_AVAILABLE;
   return res;
 }
 /**
@@ -2104,7 +2105,22 @@
     Enable SHOW EXPLAIN only if we're in the top-level query.
   */
   thd->apc_target.enable();
+  DBUG_EXECUTE_IF("show_explain_probe_join_exec_start", 
+                  if (dbug_user_var_equals_int(thd, 
+                                               "show_explain_probe_select_id", 
+                                               select_lex->select_number))
+                        dbug_serve_apcs(thd, 1);
+                 );
+
   exec_inner();
+
+  DBUG_EXECUTE_IF("show_explain_probe_join_exec_end", 
+                  if (dbug_user_var_equals_int(thd, 
+                                               "show_explain_probe_select_id", 
+                                               select_lex->select_number))
+                        dbug_serve_apcs(thd, 1);
+                 );
+
   thd->apc_target.disable();
 }
 
@@ -2127,13 +2143,6 @@
   int      tmp_error;
   DBUG_ENTER("JOIN::exec");
   
-  DBUG_EXECUTE_IF("show_explain_probe_1", 
-                  if (dbug_user_var_equals_int(thd, 
-                                               "show_explain_probe_select_id", 
-                                               select_lex->select_number))
-                        dbug_serve_apcs(thd, 1);
-                 );
-
   thd_proc_info(thd, "executing");
   error= 0;
   if (procedure)
@@ -2421,9 +2430,17 @@
       DBUG_PRINT("info",("Creating group table"));
       
       /* Free first data from old join */
+      
+      fprintf(stderr,"Q: %s\n", thd->query());
+      //DBUG_ASSERT(0);
+      /*
+        psergey-todo: this is the place of pre-mature JOIN::free call.
+      */
       curr_join->join_free();
+      //psergey-todo: SHOW EXPLAIN probe here
       if (curr_join->make_simple_join(this, curr_tmp_table))
 	DBUG_VOID_RETURN;
+      //psergey-todo: SHOW EXPLAIN probe here
       calc_group_buffer(curr_join, group_list);
       count_field_types(select_lex, &curr_join->tmp_table_param,
 			curr_join->tmp_all_fields1,
@@ -2544,7 +2561,9 @@
     if (curr_tmp_table->distinct)
       curr_join->select_distinct=0;		/* Each row is unique */
     
+    //psergey-todo: SHOW EXPLAIN probe here
     curr_join->join_free();			/* Free quick selects */
+    //psergey-todo: SHOW EXPLAIN probe here
     if (curr_join->select_distinct && ! curr_join->group_list)
     {
       thd_proc_info(thd, "Removing duplicates");
@@ -10113,7 +10132,8 @@
 {
   DBUG_ENTER("JOIN::cleanup");
   DBUG_PRINT("enter", ("full %u", (uint) full));
-
+  
+  have_query_plan= QEP_DELETED;
   if (table)
   {
     JOIN_TAB *tab;
@@ -20706,6 +20726,41 @@
 }
 
 
+/*
+  Print an EXPLAIN line with all NULLs and given message in the 'Extra' column
+*/
+int print_explain_message_line(select_result_sink *result, 
+                               SELECT_LEX *select_lex,
+                               bool on_the_fly,
+                               uint8 options,
+                               const char *message)
+{
+  const CHARSET_INFO *cs= system_charset_info;
+  Item *item_null= new Item_null();
+  List<Item> item_list;
+
+  if (on_the_fly)
+    select_lex->set_explain_type(on_the_fly);
+
+  item_list.push_back(new Item_int((int32)
+                                   select_lex->select_number));
+  item_list.push_back(new Item_string(select_lex->type,
+                                      strlen(select_lex->type), cs));
+  for (uint i=0 ; i < 7; i++)
+    item_list.push_back(item_null);
+  if (options & DESCRIBE_PARTITIONS)
+    item_list.push_back(item_null);
+  if (options & DESCRIBE_EXTENDED)
+    item_list.push_back(item_null);
+
+  item_list.push_back(new Item_string(message,strlen(message),cs));
+
+  if (result->send_data(item_list))
+    return 1;
+  return 0;
+}
+
+
 int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly,
                                SELECT_LEX *select_lex, uint8 select_options)
 {
@@ -20810,7 +20865,7 @@
   DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s",
 		      (ulong)join->select_lex, join->select_lex->type,
 		      message ? message : "NULL"));
-  DBUG_ASSERT(this->optimized == 2);
+  DBUG_ASSERT(this->have_query_plan == QEP_AVAILABLE);
   /* Don't log this into the slow query log */
 
   if (!on_the_fly)
@@ -20825,7 +20880,9 @@
   */
   if (message)
   {
-    item_list.push_back(new Item_int((int32)
+    // join->thd->lex->describe <- as options
+#if 0
+    item_list.push_bace(new Item_int((int32)
 				     join->select_lex->select_number));
     item_list.push_back(new Item_string(join->select_lex->type,
 					strlen(join->select_lex->type), cs));
@@ -20839,9 +20896,16 @@
     item_list.push_back(new Item_string(message,strlen(message),cs));
     if (result->send_data(item_list))
       error= 1;
+#endif 
+    //psergey-todo: is passing  join->thd->lex->describe correct for SHOW EXPLAIN?
+    if (print_explain_message_line(result, join->select_lex, on_the_fly, 
+                                   join->thd->lex->describe, message))
+      error= 1;
+
   }
   else if (join->select_lex == join->unit->fake_select_lex)
   {
+    //psergey-todo: is passing  join->thd->lex->describe correct for SHOW EXPLAIN?
     if (print_fake_select_lex_join(result, on_the_fly, 
                                    join->select_lex, 
                                    join->thd->lex->describe))
@@ -20853,7 +20917,7 @@
     table_map used_tables=0;
     //if (!join->select_lex->type)
     if (on_the_fly)
-      join->select_lex->set_explain_type(on_the_fly); //psergey-todo: this adds SELECT_DESCRIBE to options! bad for on-the-fly 
+      join->select_lex->set_explain_type(on_the_fly);
 
     bool printing_materialize_nest= FALSE;
     uint select_id= join->select_lex->select_number;

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-01-03 22:16:16 +0000
+++ b/sql/sql_select.h	2012-01-04 00:57:01 +0000
@@ -1137,8 +1137,10 @@
   const char *zero_result_cause; ///< not 0 if exec must return zero result
   
   bool union_part; ///< this subselect is part of union 
-  int  optimized; ///< flag to avoid double optimization in EXPLAIN
+  bool  optimized; ///< flag to avoid double optimization in EXPLAIN
   bool initialized; ///< flag to avoid double init_execution calls
+  
+  enum { QEP_NOT_PRESENT_YET, QEP_AVAILABLE, QEP_DELETED} have_query_plan;
 
   /*
     Additional WHERE and HAVING predicates to be considered for IN=>EXISTS
@@ -1221,6 +1223,7 @@
     ref_pointer_array_size= 0;
     zero_result_cause= 0;
     optimized= 0;
+    have_query_plan= QEP_NOT_PRESENT_YET;
     initialized= 0;
     cond_equal= 0;
     having_equal= 0;



More information about the commits mailing list