List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:May 27 2011 2:16pm
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-27
      Trace SET and DO.
      
      Why trace DO: so that subquery is traced when doing "DO @var := (subquery)"
      (imagine a routine at a customers', which we cannot modify, but Support wants to trace
      the subquery, and the context set by the routine is important for the subquery's
      execution, so we cannot trace the subquery as a standalone SELECT).
      
      Why trace SET: same reason: 
      so that subquery is traced when doing "SET @var = (subquery)". Moreover,
      we already traced "SET local_var_of_routine" but not "SET @user_var"
      (see optimizer_trace2_no_prot.result), which was inconsistent.
      Tracing SET however poses one problem: this, so-far working, simple usage:
       a) enable opt trace with 'SET @@optimizer_trace="enabled=on"'
       b) run SELECT query of interest
       c) disable opt trace with 'SET @@optimizer_trace="enabled=off"'
       d) SELECT from OPTIMIZER_TRACE to see trace of (b)
      does not work anymore: (c) is now traced which deletes the trace of (b).
      Solution: we make SET not be traced if it involves @@optimizer_trace.

    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-27 14:16:42 +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-27 14:16:42 +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-27 14:16:42 +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-27 14:16:42 +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-27 14:16:42 +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
@@ -1132,6 +1136,7 @@ class Opt_trace_start
 public:
   Opt_trace_start(THD *thd, const 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) {}

=== 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-27 14:16:42 +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-27 14:16:42 +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,13 @@ public:
   int check(THD *thd);
   int update(THD *thd);
   int light_check(THD *thd);
+#ifdef OPTIMIZER_TRACE
+  virtual bool is_var_optimizer_trace() const
+  {
+    extern sys_var *Sys_optimizer_trace_ptr;
+    return var == Sys_optimizer_trace_ptr;
+  }
+#endif
 };
 
 

=== 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-27 14:16:42 +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-27 14:16:42 +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-27 14:16:42 +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-27 14:16:42 +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));
+// @see set_var::is_var_optimizer_trace()
+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-20110527141642-8frx7jzpnrv9ijz0.bundle
Thread
bzr commit into mysql-trunk branch (guilhem.bichot:3318) Guilhem Bichot27 May