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

 3278 Guilhem Bichot	2011-03-14
      test of tracing of SELECTs which are inside the stored routine's
      control flow statements (IF, CASE WHEN).

    modified:
      mysql-test/include/optimizer_trace2.inc
      mysql-test/r/optimizer_trace2_no_prot.result
      mysql-test/r/optimizer_trace2_ps_prot.result
=== modified file 'mysql-test/include/optimizer_trace2.inc'
--- a/mysql-test/include/optimizer_trace2.inc	2011-02-19 12:40:31 +0000
+++ b/mysql-test/include/optimizer_trace2.inc	2011-03-14 14:33:12 +0000
@@ -166,8 +166,10 @@ select max(x) from (select sum(a) as x f
 select TRACE from information_schema.OPTIMIZER_TRACE;
 drop table t1;
 
-# Check that SQL PREPARE produces one statement, and
-# check SQL EXECUTE produces two
+--echo
+--echo # Check that SQL PREPARE produces one statement, and
+--echo # check that SQL EXECUTE produces two
+--echo
 set optimizer_trace_offset=0, optimizer_trace_limit=100;
 prepare stmt from "select 1";
 select * from information_schema.OPTIMIZER_TRACE;
@@ -176,3 +178,44 @@ execute stmt;
 select * from information_schema.OPTIMIZER_TRACE;
 deallocate prepare stmt;
 set optimizer_trace_offset=default, optimizer_trace_limit=default;
+
+--echo
+--echo # Test of SELECTs in IF in stored routine. IF is not a real
+--echo # command: not in enum_sql_command, does not have a dedicated
+--echo # call to mysql_execute_command() for it; IF rather executes as
+--echo # a part of the routine's call (CALL, for a procedure). It is
+--echo # thus traced or not, as the routine's call.
+--echo # Same test for CASE WHEN.
+--echo
+create table t1 (a int);
+delimiter |;
+create procedure p1()
+begin
+  if exists(select 1) then
+    insert into t1 values(1);
+  end if;
+  if exists(select 2) then
+    insert into t1 values(2);
+  end if;
+  if (select count(*) from t1) then
+    insert into t1 values(3);
+  end if;
+  set @a=(select count(a) from t1 where a>0);
+  case (select count(a) from t1 where a>1)
+    when 2 then set @b=2;
+    else set @b=3;
+  end case;
+end|
+delimiter ;|
+set optimizer_trace_offset=0, optimizer_trace_limit=100;
+call p1();
+# IF EXISTS() are traced because part of CALL,
+# same for first IF (SELECT) and for
+# CASE (SELECT).
+# SET @a=(SELECT) is not traced because part of SET
+# which is a real command and not traced.
+select * from information_schema.OPTIMIZER_TRACE;
+select * from t1;
+select @a,@b;
+drop procedure p1;
+drop table t1;

=== modified file 'mysql-test/r/optimizer_trace2_no_prot.result'
--- a/mysql-test/r/optimizer_trace2_no_prot.result	2011-03-01 10:55:27 +0000
+++ b/mysql-test/r/optimizer_trace2_no_prot.result	2011-03-14 14:33:12 +0000
@@ -1147,6 +1147,10 @@ TRACE
   ] /* steps */
 }
 drop table t1;
+
+# Check that SQL PREPARE produces one statement, and
+# check that SQL EXECUTE produces two
+
 set optimizer_trace_offset=0, optimizer_trace_limit=100;
 prepare stmt from "select 1";
 select * from information_schema.OPTIMIZER_TRACE;
@@ -1209,3 +1213,267 @@ select 1	{
 }	0
 deallocate prepare stmt;
 set optimizer_trace_offset=default, optimizer_trace_limit=default;
+
+# Test of SELECTs in IF in stored routine. IF is not a real
+# command: not in enum_sql_command, does not have a dedicated
+# call to mysql_execute_command() for it; IF rather executes as
+# a part of the routine's call (CALL, for a procedure). It is
+# thus traced or not, as the routine's call.
+# Same test for CASE WHEN.
+
+create table t1 (a int);
+create procedure p1()
+begin
+if exists(select 1) then
+insert into t1 values(1);
+end if;
+if exists(select 2) then
+insert into t1 values(2);
+end if;
+if (select count(*) from t1) then
+insert into t1 values(3);
+end if;
+set @a=(select count(a) from t1 where a>0);
+case (select count(a) from t1 where a>1)
+when 2 then set @b=2;
+else set @b=3;
+end case;
+end|
+set optimizer_trace_offset=0, optimizer_trace_limit=100;
+call p1();
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE
+call p1()	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 2,
+        "steps": [
+          {
+            "expanded_query": "/* select#2 */ select 1"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "subselect_execution": {
+        "select#": 2,
+        "steps": [
+          {
+            "join_optimization": {
+              "select#": 2,
+              "steps": [
+              ] /* steps */
+            } /* join_optimization */
+          },
+          {
+            "join_execution": {
+              "select#": 2,
+              "steps": [
+              ] /* steps */
+            } /* join_execution */
+          }
+        ] /* steps */
+      } /* subselect_execution */
+    },
+    {
+      "join_preparation": {
+        "select#": 3,
+        "steps": [
+          {
+            "expanded_query": "/* select#3 */ select 2"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "subselect_execution": {
+        "select#": 3,
+        "steps": [
+          {
+            "join_optimization": {
+              "select#": 3,
+              "steps": [
+              ] /* steps */
+            } /* join_optimization */
+          },
+          {
+            "join_execution": {
+              "select#": 3,
+              "steps": [
+              ] /* steps */
+            } /* join_execution */
+          }
+        ] /* steps */
+      } /* subselect_execution */
+    },
+    {
+      "join_preparation": {
+        "select#": 4,
+        "steps": [
+          {
+            "expanded_query": "/* select#4 */ select count(0) from `test`.`t1`"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "subselect_execution": {
+        "select#": 4,
+        "steps": [
+          {
+            "join_optimization": {
+              "select#": 4,
+              "steps": [
+              ] /* steps */,
+              "empty_result": {
+                "cause": "Select tables optimized away"
+              } /* empty_result */
+            } /* join_optimization */
+          },
+          {
+            "join_execution": {
+              "select#": 4,
+              "steps": [
+              ] /* steps */
+            } /* join_execution */
+          }
+        ] /* steps */
+      } /* subselect_execution */
+    },
+    {
+      "join_preparation": {
+        "select#": 6,
+        "steps": [
+          {
+            "expanded_query": "/* select#6 */ select count(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 1)"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "subselect_execution": {
+        "select#": 6,
+        "steps": [
+          {
+            "join_optimization": {
+              "select#": 6,
+              "steps": [
+                {
+                  "condition_processing": {
+                    "condition": "WHERE",
+                    "original_condition": "(`test`.`t1`.`a` > 1)",
+                    "steps": [
+                      {
+                        "transformation": "equality_propagation",
+                        "resulting_condition": "(`test`.`t1`.`a` > 1)"
+                      },
+                      {
+                        "transformation": "constant_propagation",
+                        "resulting_condition": "(`test`.`t1`.`a` > 1)"
+                      },
+                      {
+                        "transformation": "trivial_condition_removal",
+                        "resulting_condition": "(`test`.`t1`.`a` > 1)"
+                      }
+                    ] /* 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.0051,
+                      "records_for_plan": 3,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                },
+                {
+                  "attaching_conditions_to_tables": {
+                    "original_condition": "(`test`.`t1`.`a` > 1)",
+                    "attached_conditions_computation": [
+                    ] /* attached_conditions_computation */,
+                    "attached_conditions_summary": [
+                      {
+                        "database": "test",
+                        "table": "t1",
+                        "attached": "(`test`.`t1`.`a` > 1)"
+                      }
+                    ] /* attached_conditions_summary */
+                  } /* attaching_conditions_to_tables */
+                },
+                {
+                  "refine_plan": [
+                    {
+                      "database": "test",
+                      "table": "t1",
+                      "scan_type": "table"
+                    }
+                  ] /* refine_plan */
+                }
+              ] /* steps */
+            } /* join_optimization */
+          },
+          {
+            "join_execution": {
+              "select#": 6,
+              "steps": [
+              ] /* steps */
+            } /* join_execution */
+          }
+        ] /* steps */
+      } /* subselect_execution */
+    }
+  ] /* steps */
+}	0
+insert into t1 values(1)	{
+  "steps": [
+  ] /* steps */
+}	0
+insert into t1 values(2)	{
+  "steps": [
+  ] /* steps */
+}	0
+insert into t1 values(3)	{
+  "steps": [
+  ] /* steps */
+}	0
+select * from t1;
+a
+1
+2
+3
+select @a,@b;
+@a	@b
+3	2
+drop procedure p1;
+drop table t1;

=== modified file 'mysql-test/r/optimizer_trace2_ps_prot.result'
--- a/mysql-test/r/optimizer_trace2_ps_prot.result	2011-03-01 10:55:27 +0000
+++ b/mysql-test/r/optimizer_trace2_ps_prot.result	2011-03-14 14:33:12 +0000
@@ -1167,6 +1167,10 @@ TRACE
   ] /* steps */
 }
 drop table t1;
+
+# Check that SQL PREPARE produces one statement, and
+# check that SQL EXECUTE produces two
+
 set optimizer_trace_offset=0, optimizer_trace_limit=100;
 prepare stmt from "select 1";
 select * from information_schema.OPTIMIZER_TRACE;
@@ -1229,3 +1233,267 @@ select 1	{
 }	0
 deallocate prepare stmt;
 set optimizer_trace_offset=default, optimizer_trace_limit=default;
+
+# Test of SELECTs in IF in stored routine. IF is not a real
+# command: not in enum_sql_command, does not have a dedicated
+# call to mysql_execute_command() for it; IF rather executes as
+# a part of the routine's call (CALL, for a procedure). It is
+# thus traced or not, as the routine's call.
+# Same test for CASE WHEN.
+
+create table t1 (a int);
+create procedure p1()
+begin
+if exists(select 1) then
+insert into t1 values(1);
+end if;
+if exists(select 2) then
+insert into t1 values(2);
+end if;
+if (select count(*) from t1) then
+insert into t1 values(3);
+end if;
+set @a=(select count(a) from t1 where a>0);
+case (select count(a) from t1 where a>1)
+when 2 then set @b=2;
+else set @b=3;
+end case;
+end|
+set optimizer_trace_offset=0, optimizer_trace_limit=100;
+call p1();
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE
+call p1()	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 2,
+        "steps": [
+          {
+            "expanded_query": "/* select#2 */ select 1"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "subselect_execution": {
+        "select#": 2,
+        "steps": [
+          {
+            "join_optimization": {
+              "select#": 2,
+              "steps": [
+              ] /* steps */
+            } /* join_optimization */
+          },
+          {
+            "join_execution": {
+              "select#": 2,
+              "steps": [
+              ] /* steps */
+            } /* join_execution */
+          }
+        ] /* steps */
+      } /* subselect_execution */
+    },
+    {
+      "join_preparation": {
+        "select#": 3,
+        "steps": [
+          {
+            "expanded_query": "/* select#3 */ select 2"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "subselect_execution": {
+        "select#": 3,
+        "steps": [
+          {
+            "join_optimization": {
+              "select#": 3,
+              "steps": [
+              ] /* steps */
+            } /* join_optimization */
+          },
+          {
+            "join_execution": {
+              "select#": 3,
+              "steps": [
+              ] /* steps */
+            } /* join_execution */
+          }
+        ] /* steps */
+      } /* subselect_execution */
+    },
+    {
+      "join_preparation": {
+        "select#": 4,
+        "steps": [
+          {
+            "expanded_query": "/* select#4 */ select count(0) from `test`.`t1`"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "subselect_execution": {
+        "select#": 4,
+        "steps": [
+          {
+            "join_optimization": {
+              "select#": 4,
+              "steps": [
+              ] /* steps */,
+              "empty_result": {
+                "cause": "Select tables optimized away"
+              } /* empty_result */
+            } /* join_optimization */
+          },
+          {
+            "join_execution": {
+              "select#": 4,
+              "steps": [
+              ] /* steps */
+            } /* join_execution */
+          }
+        ] /* steps */
+      } /* subselect_execution */
+    },
+    {
+      "join_preparation": {
+        "select#": 6,
+        "steps": [
+          {
+            "expanded_query": "/* select#6 */ select count(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 1)"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "subselect_execution": {
+        "select#": 6,
+        "steps": [
+          {
+            "join_optimization": {
+              "select#": 6,
+              "steps": [
+                {
+                  "condition_processing": {
+                    "condition": "WHERE",
+                    "original_condition": "(`test`.`t1`.`a` > 1)",
+                    "steps": [
+                      {
+                        "transformation": "equality_propagation",
+                        "resulting_condition": "(`test`.`t1`.`a` > 1)"
+                      },
+                      {
+                        "transformation": "constant_propagation",
+                        "resulting_condition": "(`test`.`t1`.`a` > 1)"
+                      },
+                      {
+                        "transformation": "trivial_condition_removal",
+                        "resulting_condition": "(`test`.`t1`.`a` > 1)"
+                      }
+                    ] /* 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.0051,
+                      "records_for_plan": 3,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                },
+                {
+                  "attaching_conditions_to_tables": {
+                    "original_condition": "(`test`.`t1`.`a` > 1)",
+                    "attached_conditions_computation": [
+                    ] /* attached_conditions_computation */,
+                    "attached_conditions_summary": [
+                      {
+                        "database": "test",
+                        "table": "t1",
+                        "attached": "(`test`.`t1`.`a` > 1)"
+                      }
+                    ] /* attached_conditions_summary */
+                  } /* attaching_conditions_to_tables */
+                },
+                {
+                  "refine_plan": [
+                    {
+                      "database": "test",
+                      "table": "t1",
+                      "scan_type": "table"
+                    }
+                  ] /* refine_plan */
+                }
+              ] /* steps */
+            } /* join_optimization */
+          },
+          {
+            "join_execution": {
+              "select#": 6,
+              "steps": [
+              ] /* steps */
+            } /* join_execution */
+          }
+        ] /* steps */
+      } /* subselect_execution */
+    }
+  ] /* steps */
+}	0
+insert into t1 values(1)	{
+  "steps": [
+  ] /* steps */
+}	0
+insert into t1 values(2)	{
+  "steps": [
+  ] /* steps */
+}	0
+insert into t1 values(3)	{
+  "steps": [
+  ] /* steps */
+}	0
+select * from t1;
+a
+1
+2
+3
+select @a,@b;
+@a	@b
+3	2
+drop procedure p1;
+drop table t1;


Attachment: [text/bzr-bundle] bzr/guilhem.bichot@oracle.com-20110314143312-2m2iiabbmo3bb576.bundle
Thread
bzr commit into mysql-trunk branch (guilhem.bichot:3278) Guilhem Bichot14 Mar