List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:May 27 2011 1:39pm
Subject:bzr commit into mysql-trunk branch (guilhem.bichot:3318)
View as plain text  
#At file:///home/mysql_src/bzrrepos_new/mysql-next-mr-opt-backporting-wl4800/ based on revid:jorgen.loland@stripped

 3318 Guilhem Bichot	2011-05-24
      Trace SET and DO, alternative two.
      The only difference with the previous patch is that instead of adding a member
      to LEX (LEX::sets_optimizer_trace_variable) we add a virtual function
      to set_var_base (set_var_base::is_var_optimizer_trace()). This is maybe less
      invasive?
      Tests are as in the previous patch.

    modified:
      WL4800_TODO.txt
      mysql-test/suite/optimizer_trace/include/optimizer_trace2.inc
      mysql-test/suite/optimizer_trace/r/optimizer_trace2_no_prot.result
      mysql-test/suite/optimizer_trace/r/optimizer_trace2_ps_prot.result
      sql/opt_trace.h
      sql/opt_trace2server.cc
      sql/set_var.h
      sql/sp_head.cc
      sql/sql_parse.cc
      sql/sql_prepare.cc
      sql/sys_vars.cc
=== modified file 'WL4800_TODO.txt'
--- a/WL4800_TODO.txt	2011-05-27 12:37:37 +0000
+++ b/WL4800_TODO.txt	2011-05-24 08:04:11 +0000
@@ -16,48 +16,3 @@ SELECT INTO DUMPFILE instead of OUTFILE
 - in changelog, mention the added /*select#*/ in EXPLAIN EXTENDED; in
 the manual, mention use of this to decrypt what "id" is about in
 EXPLAIN output.
-
-C) QUESTIONS FOR REVIEWERS
-
-C1) should we have a "version" property in the top trace
-object? Assume that at some point in development there is a
-significant overhaul of the trace's organization: new apps will have
-to be able to parse old and new traces; would it help them to have a
-version number in order to distinguish between old/new? Drawback: do
-not forget to bump the version when doing certain changes (and: what
-changes? any little change? maybe).
-Guilhem suggests: don't have a version.
-
-C4) Jorgen wrote: Make --opt-trace-protocol dump traces to a separate file so that mtr
-can run with it without failing all tests.
-Guilhem asks: good idea, but how much is it needed?
---explain-protocol, --sp-protocol, --view-protocol make all tests
-fail. Maybe I can merely move this as an idea in a comment in mysqltest.cc
-near the definition of opt-trace-protocol?
-
-C5) should the debug binary really assert(0) if json syntax error? is it a
-good idea at the customer's? On the other hand, how to make sure a
-developer notices a syntax error when running tests?
-sql_print_warning() is an idea.
-Guilhem suggests: keep it as it is (assert(0))
-
-C7) Dmitry advised we should trace SET and DO. Right now SET-which-sets-local-routine-vars is
-traced, but SET-which-sets-other-vars is not, this is
-inconsistent. And SET may use a subquery (SET @a=(subq)) which is
-worth tracing.
-Guilhem says: agree. A drawback of this is that
-SET optimizer_trace="enabled=off";
-will be traced, which is a nuisance, as it deletes previous trace.
-But next item below which could eliminate this drawback.
-
-C8) should we limit tracing to statements using
-tables/views? This would decrease the noise when tracing a routine
-(even DECLARE is traced now), and would nicely distinguish between
-  SET @x=(subq); # should be traced
-and
-  SET optimizer_trace="enabled=off"; # tracing is nuisance, as it
-                                     # deletes previous trace
-The worst consequence it could have is that
-  SELECT stored_func();
-would not be traced (but its substatements would still be, if they use
-tables/views).

=== modified file 'mysql-test/suite/optimizer_trace/include/optimizer_trace2.inc'
--- a/mysql-test/suite/optimizer_trace/include/optimizer_trace2.inc	2011-05-20 13:15:22 +0000
+++ b/mysql-test/suite/optimizer_trace/include/optimizer_trace2.inc	2011-05-24 08:04:11 +0000
@@ -229,7 +229,7 @@ end|
 delimiter ;|
 set optimizer_trace_offset=0, optimizer_trace_limit=100;
 set @old_max=@@optimizer_trace_max_mem_size;
-set optimizer_trace_max_mem_size=20000;
+set optimizer_trace_max_mem_size=40000;
 call p1();
 # SET @a=(SELECT) is not traced because part of SET
 # which is a real command and not traced.
@@ -241,27 +241,12 @@ drop procedure p1;
 drop table t1;
 
 --echo
---echo # Test that in a non-traced SQL command, substatements can be
---echo # traced
+--echo # Test of tracing of DO.
 --echo
 
-create table t1(a int);
-insert into t1 values(1),(2);
-delimiter |;
-create function f1() returns int
-begin
-  declare b int;
-  select 48 into b from dual;
-  select a into b from t1 limit 1;
-  insert into t1 values(b*10);
-  return 36;
-end|
-delimiter ;|
 set optimizer_trace_offset=0, optimizer_trace_limit=100;
-do f1(); # DO is never traced
+do (select 42);
 select * from information_schema.OPTIMIZER_TRACE;
-drop function f1;
-drop table t1;
 
 --echo
 --echo # Test of tracing of subquery used in parameter of routine call

=== modified file 'mysql-test/suite/optimizer_trace/r/optimizer_trace2_no_prot.result'
--- a/mysql-test/suite/optimizer_trace/r/optimizer_trace2_no_prot.result	2011-05-26 16:24:06 +0000
+++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace2_no_prot.result	2011-05-24 08:04:11 +0000
@@ -1266,10 +1266,18 @@ end case;
 end|
 set optimizer_trace_offset=0, optimizer_trace_limit=100;
 set @old_max=@@optimizer_trace_max_mem_size;
-set optimizer_trace_max_mem_size=20000;
+set optimizer_trace_max_mem_size=40000;
 call p1();
 select * from information_schema.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
+set @old_max=@@optimizer_trace_max_mem_size	{
+  "steps": [
+  ] /* steps */
+}	0	0
+set optimizer_trace_max_mem_size=40000	{
+  "steps": [
+  ] /* steps */
+}	0	0
 call p1()	{
   "steps": [
   ] /* steps */
@@ -1394,6 +1402,122 @@ insert into t1 values(3)	{
   "steps": [
   ] /* steps */
 }	0	0
+SET @a=(select count(a) from t1 where a>0)	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 5,
+        "steps": [
+          {
+            "expanded_query": "/* select#5 */ select count(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 0)"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "subselect_execution": {
+        "select#": 5,
+        "steps": [
+          {
+            "join_optimization": {
+              "select#": 5,
+              "steps": [
+                {
+                  "condition_processing": {
+                    "condition": "WHERE",
+                    "original_condition": "(`test`.`t1`.`a` > 0)",
+                    "steps": [
+                      {
+                        "transformation": "equality_propagation",
+                        "resulting_condition": "(`test`.`t1`.`a` > 0)"
+                      },
+                      {
+                        "transformation": "constant_propagation",
+                        "resulting_condition": "(`test`.`t1`.`a` > 0)"
+                      },
+                      {
+                        "transformation": "trivial_condition_removal",
+                        "resulting_condition": "(`test`.`t1`.`a` > 0)"
+                      }
+                    ] /* steps */
+                  } /* condition_processing */
+                },
+                {
+                  "ref_optimizer_key_uses": [
+                  ] /* ref_optimizer_key_uses */
+                },
+                {
+                  "records_estimation": [
+                    {
+                      "database": "test",
+                      "table": "t1",
+                      "table_scan": {
+                        "records": 3,
+                        "cost": 2
+                      } /* table_scan */
+                    }
+                  ] /* records_estimation */
+                },
+                {
+                  "considered_execution_plans": [
+                    {
+                      "database": "test",
+                      "table": "t1",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "scan",
+                            "using_join_cache": true,
+                            "records": 3,
+                            "cost": 2.0051,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.6051,
+                      "records_for_plan": 3,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                },
+                {
+                  "attaching_conditions_to_tables": {
+                    "original_condition": "(`test`.`t1`.`a` > 0)",
+                    "attached_conditions_computation": [
+                    ] /* attached_conditions_computation */,
+                    "attached_conditions_summary": [
+                      {
+                        "database": "test",
+                        "table": "t1",
+                        "attached": "(`test`.`t1`.`a` > 0)"
+                      }
+                    ] /* attached_conditions_summary */
+                  } /* attaching_conditions_to_tables */
+                },
+                {
+                  "refine_plan": [
+                    {
+                      "database": "test",
+                      "table": "t1",
+                      "scan_type": "table"
+                    }
+                  ] /* refine_plan */
+                }
+              ] /* steps */
+            } /* join_optimization */
+          },
+          {
+            "join_execution": {
+              "select#": 5,
+              "steps": [
+              ] /* steps */
+            } /* join_execution */
+          }
+        ] /* steps */
+      } /* subselect_execution */
+    }
+  ] /* steps */
+}	0	0
 set_case_expr (15) 0 (select count(`a`) from `test`.`t1` where (`a` > 1))	{
   "steps": [
     {
@@ -1514,6 +1638,10 @@ jump_if_not 11(15) (case_expr@0 = 2)	{
   "steps": [
   ] /* steps */
 }	0	0
+SET @b=2	{
+  "steps": [
+  ] /* steps */
+}	0	0
 select * from t1;
 a
 1
@@ -1526,150 +1654,26 @@ set optimizer_trace_max_mem_size=@old_ma
 drop procedure p1;
 drop table t1;
 
-# Test that in a non-traced SQL command, substatements can be
-# traced
+# Test of tracing of DO.
 
-create table t1(a int);
-insert into t1 values(1),(2);
-create function f1() returns int
-begin
-declare b int;
-select 48 into b from dual;
-select a into b from t1 limit 1;
-insert into t1 values(b*10);
-return 36;
-end|
 set optimizer_trace_offset=0, optimizer_trace_limit=100;
-do f1();
+do (select 42);
 select * from information_schema.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
-set b@0 NULL	{
-  "steps": [
-  ] /* steps */
-}	0	0
-select 48 into b from dual	{
-  "steps": [
-    {
-      "join_preparation": {
-        "select#": 1,
-        "steps": [
-          {
-            "expanded_query": "/* select#1 */ select 48 AS `48`"
-          }
-        ] /* steps */
-      } /* join_preparation */
-    },
-    {
-      "join_optimization": {
-        "select#": 1,
-        "steps": [
-        ] /* steps */
-      } /* join_optimization */
-    },
-    {
-      "join_execution": {
-        "select#": 1,
-        "steps": [
-        ] /* steps */
-      } /* join_execution */
-    }
-  ] /* steps */
-}	0	0
-select a into b from t1 limit 1	{
+do (select 42)	{
   "steps": [
     {
       "join_preparation": {
-        "select#": 1,
+        "select#": 2,
         "steps": [
           {
-            "expanded_query": "/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` limit 1"
+            "expanded_query": "/* select#2 */ select 42"
           }
         ] /* steps */
       } /* join_preparation */
-    },
-    {
-      "join_optimization": {
-        "select#": 1,
-        "steps": [
-          {
-            "records_estimation": [
-              {
-                "database": "test",
-                "table": "t1",
-                "table_scan": {
-                  "records": 2,
-                  "cost": 2
-                } /* table_scan */
-              }
-            ] /* records_estimation */
-          },
-          {
-            "considered_execution_plans": [
-              {
-                "database": "test",
-                "table": "t1",
-                "best_access_path": {
-                  "considered_access_paths": [
-                    {
-                      "access_type": "scan",
-                      "using_join_cache": true,
-                      "records": 2,
-                      "cost": 2.0034,
-                      "chosen": true
-                    }
-                  ] /* considered_access_paths */
-                } /* best_access_path */,
-                "cost_for_plan": 2.4034,
-                "records_for_plan": 2,
-                "chosen": true
-              }
-            ] /* considered_execution_plans */
-          },
-          {
-            "attaching_conditions_to_tables": {
-              "original_condition": null,
-              "attached_conditions_computation": [
-              ] /* attached_conditions_computation */,
-              "attached_conditions_summary": [
-                {
-                  "database": "test",
-                  "table": "t1",
-                  "attached": null
-                }
-              ] /* attached_conditions_summary */
-            } /* attaching_conditions_to_tables */
-          },
-          {
-            "refine_plan": [
-              {
-                "database": "test",
-                "table": "t1",
-                "scan_type": "table"
-              }
-            ] /* refine_plan */
-          }
-        ] /* steps */
-      } /* join_optimization */
-    },
-    {
-      "join_execution": {
-        "select#": 1,
-        "steps": [
-        ] /* steps */
-      } /* join_execution */
     }
   ] /* steps */
 }	0	0
-insert into t1 values( NAME_CONST('b',1)*10)	{
-  "steps": [
-  ] /* steps */
-}	0	0
-freturn 3 36	{
-  "steps": [
-  ] /* steps */
-}	0	0
-drop function f1;
-drop table t1;
 
 # Test of tracing of subquery used in parameter of routine call
 

=== modified file 'mysql-test/suite/optimizer_trace/r/optimizer_trace2_ps_prot.result'
--- a/mysql-test/suite/optimizer_trace/r/optimizer_trace2_ps_prot.result	2011-05-20 13:15:22 +0000
+++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace2_ps_prot.result	2011-05-24 08:04:11 +0000
@@ -1284,10 +1284,18 @@ end case;
 end|
 set optimizer_trace_offset=0, optimizer_trace_limit=100;
 set @old_max=@@optimizer_trace_max_mem_size;
-set optimizer_trace_max_mem_size=20000;
+set optimizer_trace_max_mem_size=40000;
 call p1();
 select * from information_schema.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
+set @old_max=@@optimizer_trace_max_mem_size	{
+  "steps": [
+  ] /* steps */
+}	0	0
+set optimizer_trace_max_mem_size=40000	{
+  "steps": [
+  ] /* steps */
+}	0	0
 call p1()	{
   "steps": [
   ] /* steps */
@@ -1412,6 +1420,122 @@ insert into t1 values(3)	{
   "steps": [
   ] /* steps */
 }	0	0
+SET @a=(select count(a) from t1 where a>0)	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 5,
+        "steps": [
+          {
+            "expanded_query": "/* select#5 */ select count(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 0)"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "subselect_execution": {
+        "select#": 5,
+        "steps": [
+          {
+            "join_optimization": {
+              "select#": 5,
+              "steps": [
+                {
+                  "condition_processing": {
+                    "condition": "WHERE",
+                    "original_condition": "(`test`.`t1`.`a` > 0)",
+                    "steps": [
+                      {
+                        "transformation": "equality_propagation",
+                        "resulting_condition": "(`test`.`t1`.`a` > 0)"
+                      },
+                      {
+                        "transformation": "constant_propagation",
+                        "resulting_condition": "(`test`.`t1`.`a` > 0)"
+                      },
+                      {
+                        "transformation": "trivial_condition_removal",
+                        "resulting_condition": "(`test`.`t1`.`a` > 0)"
+                      }
+                    ] /* steps */
+                  } /* condition_processing */
+                },
+                {
+                  "ref_optimizer_key_uses": [
+                  ] /* ref_optimizer_key_uses */
+                },
+                {
+                  "records_estimation": [
+                    {
+                      "database": "test",
+                      "table": "t1",
+                      "table_scan": {
+                        "records": 3,
+                        "cost": 2
+                      } /* table_scan */
+                    }
+                  ] /* records_estimation */
+                },
+                {
+                  "considered_execution_plans": [
+                    {
+                      "database": "test",
+                      "table": "t1",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "scan",
+                            "using_join_cache": true,
+                            "records": 3,
+                            "cost": 2.0051,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.6051,
+                      "records_for_plan": 3,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                },
+                {
+                  "attaching_conditions_to_tables": {
+                    "original_condition": "(`test`.`t1`.`a` > 0)",
+                    "attached_conditions_computation": [
+                    ] /* attached_conditions_computation */,
+                    "attached_conditions_summary": [
+                      {
+                        "database": "test",
+                        "table": "t1",
+                        "attached": "(`test`.`t1`.`a` > 0)"
+                      }
+                    ] /* attached_conditions_summary */
+                  } /* attaching_conditions_to_tables */
+                },
+                {
+                  "refine_plan": [
+                    {
+                      "database": "test",
+                      "table": "t1",
+                      "scan_type": "table"
+                    }
+                  ] /* refine_plan */
+                }
+              ] /* steps */
+            } /* join_optimization */
+          },
+          {
+            "join_execution": {
+              "select#": 5,
+              "steps": [
+              ] /* steps */
+            } /* join_execution */
+          }
+        ] /* steps */
+      } /* subselect_execution */
+    }
+  ] /* steps */
+}	0	0
 set_case_expr (15) 0 (select count(`a`) from `test`.`t1` where (`a` > 1))	{
   "steps": [
     {
@@ -1532,6 +1656,10 @@ jump_if_not 11(15) (case_expr@0 = 2)	{
   "steps": [
   ] /* steps */
 }	0	0
+SET @b=2	{
+  "steps": [
+  ] /* steps */
+}	0	0
 select * from t1;
 a
 1
@@ -1544,150 +1672,61 @@ set optimizer_trace_max_mem_size=@old_ma
 drop procedure p1;
 drop table t1;
 
-# Test that in a non-traced SQL command, substatements can be
-# traced
+# Test of tracing of DO.
 
-create table t1(a int);
-insert into t1 values(1),(2);
-create function f1() returns int
-begin
-declare b int;
-select 48 into b from dual;
-select a into b from t1 limit 1;
-insert into t1 values(b*10);
-return 36;
-end|
 set optimizer_trace_offset=0, optimizer_trace_limit=100;
-do f1();
+do (select 42);
 select * from information_schema.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
-set b@0 NULL	{
-  "steps": [
-  ] /* steps */
-}	0	0
-select 48 into b from dual	{
+do (select 42)	{
   "steps": [
     {
       "join_preparation": {
-        "select#": 1,
+        "select#": 2,
         "steps": [
           {
-            "expanded_query": "/* select#1 */ select 48 AS `48`"
+            "expanded_query": "/* select#2 */ select 42"
           }
         ] /* steps */
       } /* join_preparation */
-    },
-    {
-      "join_optimization": {
-        "select#": 1,
-        "steps": [
-        ] /* steps */
-      } /* join_optimization */
-    },
-    {
-      "join_execution": {
-        "select#": 1,
-        "steps": [
-        ] /* steps */
-      } /* join_execution */
     }
   ] /* steps */
 }	0	0
-select a into b from t1 limit 1	{
+do (select 42)	{
   "steps": [
     {
       "join_preparation": {
-        "select#": 1,
+        "select#": 2,
         "steps": [
           {
-            "expanded_query": "/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` limit 1"
+            "expanded_query": "/* select#2 */ select 42"
           }
         ] /* steps */
       } /* join_preparation */
     },
     {
-      "join_optimization": {
-        "select#": 1,
+      "subselect_execution": {
+        "select#": 2,
         "steps": [
           {
-            "records_estimation": [
-              {
-                "database": "test",
-                "table": "t1",
-                "table_scan": {
-                  "records": 2,
-                  "cost": 2
-                } /* table_scan */
-              }
-            ] /* records_estimation */
-          },
-          {
-            "considered_execution_plans": [
-              {
-                "database": "test",
-                "table": "t1",
-                "best_access_path": {
-                  "considered_access_paths": [
-                    {
-                      "access_type": "scan",
-                      "using_join_cache": true,
-                      "records": 2,
-                      "cost": 2.0034,
-                      "chosen": true
-                    }
-                  ] /* considered_access_paths */
-                } /* best_access_path */,
-                "cost_for_plan": 2.4034,
-                "records_for_plan": 2,
-                "chosen": true
-              }
-            ] /* considered_execution_plans */
-          },
-          {
-            "attaching_conditions_to_tables": {
-              "original_condition": null,
-              "attached_conditions_computation": [
-              ] /* attached_conditions_computation */,
-              "attached_conditions_summary": [
-                {
-                  "database": "test",
-                  "table": "t1",
-                  "attached": null
-                }
-              ] /* attached_conditions_summary */
-            } /* attaching_conditions_to_tables */
+            "join_optimization": {
+              "select#": 2,
+              "steps": [
+              ] /* steps */
+            } /* join_optimization */
           },
           {
-            "refine_plan": [
-              {
-                "database": "test",
-                "table": "t1",
-                "scan_type": "table"
-              }
-            ] /* refine_plan */
+            "join_execution": {
+              "select#": 2,
+              "steps": [
+              ] /* steps */
+            } /* join_execution */
           }
         ] /* steps */
-      } /* join_optimization */
-    },
-    {
-      "join_execution": {
-        "select#": 1,
-        "steps": [
-        ] /* steps */
-      } /* join_execution */
+      } /* subselect_execution */
     }
   ] /* steps */
 }	0	0
-insert into t1 values( NAME_CONST('b',1)*10)	{
-  "steps": [
-  ] /* steps */
-}	0	0
-freturn 3 36	{
-  "steps": [
-  ] /* steps */
-}	0	0
-drop function f1;
-drop table t1;
 
 # Test of tracing of subquery used in parameter of routine call
 

=== modified file 'sql/opt_trace.h'
--- a/sql/opt_trace.h	2011-05-23 09:51:41 +0000
+++ b/sql/opt_trace.h	2011-05-24 08:04:11 +0000
@@ -27,6 +27,7 @@ struct TABLE_LIST;
 struct TABLE;
 class sp_head;
 class sp_instr;
+class set_var_base;
 
 /**
    @file
@@ -932,6 +933,8 @@ private:
   @param  thd          the THD
   @param  tbl          list of tables read/written by the statement.
   @param  sql_command  SQL command being prepared or executed
+  @param  set_vars     what variables are set by this command (only used if
+                       sql_command is SQLCOM_SET_OPTION)
   @param  query        query
   @param  length       query's length
   @param  instr        routine's instruction, if applicable; if so, 'query'
@@ -946,6 +949,7 @@ class Opt_trace_start
 public:
   Opt_trace_start(THD *thd_arg, TABLE_LIST *tbl,
                   enum enum_sql_command sql_command,
+                  List<set_var_base> *set_vars,
                   const char *query, size_t query_length,
                   sp_instr *instr,
                   const CHARSET_INFO *query_charset);
@@ -1019,7 +1023,7 @@ void opt_trace_disable_if_no_security_co
    @param underlying_tables underlying tables/views of 'view'
  */
 void opt_trace_disable_if_no_view_access(THD *thd, TABLE_LIST *view,
-                                 TABLE_LIST *underlying_tables);
+                                         TABLE_LIST *underlying_tables);
 
 /**
   If tracing is on, checks additional privileges on a stored routine, to make

=== modified file 'sql/opt_trace2server.cc'
--- a/sql/opt_trace2server.cc	2011-05-23 09:51:41 +0000
+++ b/sql/opt_trace2server.cc	2011-05-24 08:04:11 +0000
@@ -86,6 +86,21 @@ inline bool sql_command_can_be_traced(en
 }
 
 
+/// @returns whether this command is "SET ... @@@@OPTIMIZER_TRACE=..."
+bool sets_var_optimizer_trace(enum enum_sql_command sql_command,
+                              List<set_var_base> *set_vars)
+{
+  if (sql_command == SQLCOM_SET_OPTION)
+  {
+    List_iterator_fast<set_var_base> it(*set_vars);
+    const set_var_base *var;
+    while ((var= it++))
+      if (var->is_var_optimizer_trace())
+        return true;
+  }
+  return false;
+}
+
 void opt_trace_disable_if_no_tables_access(THD *thd, TABLE_LIST *tbl);
 
 } // namespace
@@ -93,6 +108,7 @@ void opt_trace_disable_if_no_tables_acce
 
 Opt_trace_start::Opt_trace_start(THD *thd, TABLE_LIST *tbl,
                                  enum enum_sql_command sql_command,
+                                 List<set_var_base> *set_vars,
                                  const char *query, size_t query_length,
                                  sp_instr *instr,
                                  const CHARSET_INFO *query_charset)
@@ -115,20 +131,30 @@ Opt_trace_start::Opt_trace_start(THD *th
   // First step, decide on what type of I_S support we want
   if (unlikely(var & Opt_trace_context::FLAG_ENABLED))
   {
-    if (sql_command_can_be_traced(sql_command) && // (1)
-        !list_has_optimizer_trace_table(tbl) &&   // (2)
-        !thd->system_thread)                      // (3)
+    if (sql_command_can_be_traced(sql_command) &&           // (1)
+        !sets_var_optimizer_trace(sql_command, set_vars) && // (2)
+        !list_has_optimizer_trace_table(tbl) &&             // (3)
+        !thd->system_thread)                                // (4)
     {
       /*
         (1) This command is interesting Optimizer-wise.
 
-        (2) If a SELECT of I_S.OPTIMIZER_TRACE were traced, it would overwrite
+        (2) This command is not "SET ... @@optimizer_trace=...". Otherwise,
+        this simple usage:
+        a) enable opt trace with SET
+        b) run SELECT query of interest
+        c) disable opt trace with SET
+        d) read OPTIMIZER_TRACE table
+        would not work: (c) would be traced which would delete the trace of
+        (b).
+
+        (3) If a SELECT of I_S.OPTIMIZER_TRACE were traced, it would overwrite
         the interesting trace of the previous statement. Note that
         list_has_optimizer_trace_table() is an expensive function (scanning the
         list of all used tables, doing checks on their names) but we call it
         only if @@optimizer_trace has enabled=on.
 
-        (3) Usage of the trace in a system thread would be
+        (4) Usage of the trace in a system thread would be
         impractical. Additionally:
         - threads of the Events Scheduler have an unusual security context
         (thd->main_security_ctx.priv_user==NULL, see comment in

=== modified file 'sql/set_var.h'
--- a/sql/set_var.h	2011-03-24 10:35:12 +0000
+++ b/sql/set_var.h	2011-05-24 08:04:11 +0000
@@ -187,6 +187,8 @@ public:
   virtual int check(THD *thd)=0;           /* To check privileges etc. */
   virtual int update(THD *thd)=0;                  /* To set the value */
   virtual int light_check(THD *thd) { return check(thd); }   /* for PS */
+  /// @returns whether this variable is @@@@optimizer_trace.
+  virtual bool is_var_optimizer_trace() const { return false; }
 };
 
 
@@ -232,6 +234,11 @@ public:
   int check(THD *thd);
   int update(THD *thd);
   int light_check(THD *thd);
+  virtual bool is_var_optimizer_trace() const
+  {
+    extern sys_var *Sys_optimizer_trace_ptr;
+    return var == Sys_optimizer_trace_ptr;
+  }
 };
 
 

=== modified file 'sql/sp_head.cc'
--- a/sql/sp_head.cc	2011-05-24 12:46:22 +0000
+++ b/sql/sp_head.cc	2011-05-24 08:04:11 +0000
@@ -2970,8 +2970,8 @@ sp_lex_keeper::reset_lex_and_exec_core(T
       have a subquery in parameter and are worth tracing. They don't
       correspond to a SQL command so we pretend that they are SQLCOM_SELECT.
     */
-    Opt_trace_start ots(thd, m_lex->query_tables,
-                        SQLCOM_SELECT, NULL, 0, instr,
+    Opt_trace_start ots(thd, m_lex->query_tables, SQLCOM_SELECT,
+                        &m_lex->var_list, NULL, 0, instr,
                         thd->variables.character_set_client);
     Opt_trace_object trace_command(&thd->opt_trace);
     Opt_trace_array trace_command_steps(&thd->opt_trace, "steps");

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2011-05-24 12:46:22 +0000
+++ b/sql/sql_parse.cc	2011-05-24 08:04:11 +0000
@@ -352,9 +352,14 @@ void init_update_queries(void)
   sql_command_flags[SQLCOM_SELECT]=         CF_REEXECUTION_FRAGILE |
                                             CF_CAN_GENERATE_ROW_EVENTS |
                                             CF_OPTIMIZER_TRACE;
-  sql_command_flags[SQLCOM_SET_OPTION]=     CF_REEXECUTION_FRAGILE | CF_AUTO_COMMIT_TRANS;
+  // (1) so that subquery is traced when doing "SET @var = (subquery)"
+  sql_command_flags[SQLCOM_SET_OPTION]=     CF_REEXECUTION_FRAGILE |
+                                            CF_AUTO_COMMIT_TRANS |
+                                            CF_OPTIMIZER_TRACE; // (1)
+  // (1) so that subquery is traced when doing "DO @var := (subquery)"
   sql_command_flags[SQLCOM_DO]=             CF_REEXECUTION_FRAGILE |
-                                            CF_CAN_GENERATE_ROW_EVENTS;
+                                            CF_CAN_GENERATE_ROW_EVENTS |
+                                            CF_OPTIMIZER_TRACE; // (1)
 
   sql_command_flags[SQLCOM_SHOW_STATUS_PROC]= CF_STATUS_COMMAND | CF_REEXECUTION_FRAGILE;
   sql_command_flags[SQLCOM_SHOW_STATUS]=      CF_STATUS_COMMAND | CF_REEXECUTION_FRAGILE;
@@ -2111,7 +2116,7 @@ mysql_execute_command(THD *thd)
 
   status_var_increment(thd->status_var.com_stat[lex->sql_command]);
 
-  Opt_trace_start ots(thd, all_tables, lex->sql_command,
+  Opt_trace_start ots(thd, all_tables, lex->sql_command, &lex->var_list,
                       thd->query(), thd->query_length(), NULL,
                       thd->variables.character_set_client);
 

=== modified file 'sql/sql_prepare.cc'
--- a/sql/sql_prepare.cc	2011-05-24 12:46:22 +0000
+++ b/sql/sql_prepare.cc	2011-05-24 08:04:11 +0000
@@ -1964,7 +1964,7 @@ static bool check_prepared_statement(Pre
     For the optimizer trace, this is the symmetric, for statement preparation,
     of what is done at statement execution (in mysql_execute_command()).
   */
-  Opt_trace_start ots(thd, tables, sql_command,
+  Opt_trace_start ots(thd, tables, sql_command, &lex->var_list,
                       thd->query(), thd->query_length(), NULL,
                       thd->variables.character_set_client);
 

=== modified file 'sql/sys_vars.cc'
--- a/sql/sys_vars.cc	2011-05-24 12:46:22 +0000
+++ b/sql/sys_vars.cc	2011-05-24 08:04:11 +0000
@@ -1666,6 +1666,8 @@ static Sys_var_flagset Sys_optimizer_tra
        SESSION_VAR(optimizer_trace), CMD_LINE(REQUIRED_ARG),
        Opt_trace_context::flag_names,
        DEFAULT(Opt_trace_context::FLAG_DEFAULT));
+/// for sql_yacc.yy
+export sys_var *Sys_optimizer_trace_ptr= &Sys_optimizer_trace;
 
 /*
   Note how "misc" is not here: it is not accessible to the user; disabling


Attachment: [text/bzr-bundle] bzr/guilhem.bichot@oracle.com-20110524080411-77883q0k9t00ocqt.bundle
Thread
bzr commit into mysql-trunk branch (guilhem.bichot:3318) Guilhem Bichot27 May