#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 Bichot | 14 Mar |
