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

 3314 Guilhem Bichot	2011-05-23
      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).
      We have two choices:
      1) not fix this problem. The user will then have to use one of two workarounds:
      1.1) swap (d) and (c) (indeed (d) is never traced because it is a statement reading
      the OPTIMIZER_TRACE table)
      1.2) before (b), set @@optimizer_trace_limit/offset so that both traces
      of (b) and (c) are retained.
      2) fix this problem (solution presented in this patch): make a statement not be
      traced if it wants to set @@optimizer_trace. So
      'SET @@optimizer_trace=xxx' won't be traced; all other SET usage will be traced.
      
      I like in (2): user-friendliness.
      I dislike in (2): adding a member to LEX. An alternative logic was possible:
      if (sql_command == SQLCOM_SET_OPTION) then scan list of variables-to-be-set;
      but those variables are accessible only as pointers to set_var_base
      which is just an interface and does not offer a way to ask 'is this
      @@optimizer_trace', so I would have needed to add a function
      to the interface 'is this @@optimizer_trace' which didn't sound good either.

    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_lex.cc
      sql/sql_lex.h
      sql/sql_parse.cc
      sql/sql_prepare.cc
      sql/sql_yacc.yy
      sql/sys_vars.cc
=== modified file 'WL4800_TODO.txt'
--- a/WL4800_TODO.txt	2011-05-20 13:15:22 +0000
+++ b/WL4800_TODO.txt	2011-05-23 13:22:22 +0000
@@ -19,15 +19,6 @@ 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.
-
 C3) Jorgen wrote: The optimizer may have second thoughts about which access method to
 use for a table. This should be traced. See example query (1) where
 the trace says that ref-access is best but we change our mind and use
@@ -36,37 +27,3 @@ range access anyway.
    INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
    SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
 Jorgen said he'll fix in the wl4800 branch.
-
-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-23 13:22:22 +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-20 13:15:22 +0000
+++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace2_no_prot.result	2011-05-23 13:22:22 +0000
@@ -1264,10 +1264,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 */
@@ -1392,6 +1400,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": [
     {
@@ -1512,6 +1636,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
@@ -1524,150 +1652,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-23 13:22:22 +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-23 13:22:22 +0000
@@ -932,6 +932,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  sets_var     whether this command sets the value of
+                       @@@@optimizer_trace.
   @param  query        query
   @param  length       query's length
   @param  instr        routine's instruction, if applicable; if so, 'query'
@@ -946,6 +948,7 @@ class Opt_trace_start
 public:
   Opt_trace_start(THD *thd_arg, TABLE_LIST *tbl,
                   enum enum_sql_command sql_command,
+                  bool sets_var,
                   const char *query, size_t query_length,
                   sp_instr *instr,
                   const CHARSET_INFO *query_charset);
@@ -1019,7 +1022,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-23 13:22:22 +0000
@@ -93,6 +93,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,
+                                 bool sets_var,
                                  const char *query, size_t query_length,
                                  sp_instr *instr,
                                  const CHARSET_INFO *query_charset)
@@ -115,20 +116,29 @@ 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 (!sets_var &&                              // (1)
+        sql_command_can_be_traced(sql_command) && // (2)
+        !list_has_optimizer_trace_table(tbl) &&   // (3)
+        !thd->system_thread)                      // (4)
     {
       /*
-        (1) This command is interesting Optimizer-wise.
+        (1) This command does 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 and delete the trace of (b).
 
-        (2) If a SELECT of I_S.OPTIMIZER_TRACE were traced, it would overwrite
+        (2) This command is interesting Optimizer-wise.
+
+        (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-23 13:22:22 +0000
@@ -308,7 +308,7 @@ bool fix_delay_key_write(sys_var *self, 
 sql_mode_t expand_sql_mode(sql_mode_t sql_mode);
 bool sql_mode_string_representation(THD *thd, sql_mode_t sql_mode, LEX_STRING *ls);
 
-extern sys_var *Sys_autocommit_ptr;
+extern sys_var *Sys_autocommit_ptr, *Sys_optimizer_trace_ptr;
 
 const CHARSET_INFO *get_old_charset_by_name(const char *old_name);
 

=== modified file 'sql/sp_head.cc'
--- a/sql/sp_head.cc	2011-05-21 11:17:59 +0000
+++ b/sql/sp_head.cc	2011-05-23 13:22:22 +0000
@@ -2969,8 +2969,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->sets_optimizer_trace_variable, 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_lex.cc'
--- a/sql/sql_lex.cc	2011-04-01 14:04:52 +0000
+++ b/sql/sql_lex.cc	2011-05-23 13:22:22 +0000
@@ -404,6 +404,7 @@ void lex_start(THD *thd)
   lex->m_sql_cmd= NULL;
   lex->proc_list.first= 0;
   lex->escape_used= FALSE;
+  lex->sets_optimizer_trace_variable= false;
   lex->query_tables= 0;
   lex->reset_query_tables_list(FALSE);
   lex->expr_allows_subselect= TRUE;

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2011-03-28 10:56:41 +0000
+++ b/sql/sql_lex.h	2011-05-23 13:22:22 +0000
@@ -2283,6 +2283,8 @@ struct LEX: public Query_tables_list
   bool escape_used;
   bool is_lex_started; /* If lex_start() did run. For debugging. */
 
+  bool sets_optimizer_trace_variable;
+
   LEX();
 
   virtual ~LEX()

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2011-05-06 12:58:06 +0000
+++ b/sql/sql_parse.cc	2011-05-23 13:22:22 +0000
@@ -351,9 +351,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;
@@ -2052,6 +2057,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,
+                      lex->sets_optimizer_trace_variable,
                       thd->query(), thd->query_length(), NULL,
                       thd->variables.character_set_client);
 

=== modified file 'sql/sql_prepare.cc'
--- a/sql/sql_prepare.cc	2011-05-06 12:58:06 +0000
+++ b/sql/sql_prepare.cc	2011-05-23 13:22:22 +0000
@@ -1970,6 +1970,7 @@ static bool check_prepared_statement(Pre
     of what is done at statement execution (in mysql_execute_command()).
   */
   Opt_trace_start ots(thd, tables, sql_command,
+                      lex->sets_optimizer_trace_variable,
                       thd->query(), thd->query_length(), NULL,
                       thd->variables.character_set_client);
 

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2011-03-09 20:54:55 +0000
+++ b/sql/sql_yacc.yy	2011-05-23 13:22:22 +0000
@@ -442,6 +442,8 @@ set_system_variable(THD *thd, struct sys
   /* No AUTOCOMMIT from a stored function or trigger. */
   if (lex->spcont && tmp->var == Sys_autocommit_ptr)
     lex->sphead->m_flags|= sp_head::HAS_SET_AUTOCOMMIT_STMT;
+  else if (tmp->var == Sys_optimizer_trace_ptr)      
+      lex->sets_optimizer_trace_variable= true; 
 
   if (! (var= new set_var(var_type, tmp->var, &tmp->base_name, val)))
     return TRUE;

=== modified file 'sql/sys_vars.cc'
--- a/sql/sys_vars.cc	2011-05-06 12:58:06 +0000
+++ b/sql/sys_vars.cc	2011-05-23 13:22:22 +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-20110523132222-h0ow0l4ngl0pdbh2.bundle
Thread
bzr commit into mysql-trunk branch (guilhem.bichot:3314) Guilhem Bichot23 May