#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-range-subselect/ based on revid:jorgen.loland@stripped
3248 Jorgen Loland 2011-01-11
WL#5741: Add optimizer tracing to subqueries
Tests only
added:
mysql-test/r/optimizer_trace_subquery.result
mysql-test/t/optimizer_trace_subquery.test
=== added file 'mysql-test/r/optimizer_trace_subquery.result'
--- a/mysql-test/r/optimizer_trace_subquery.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/optimizer_trace_subquery.result 2011-01-11 11:56:02 +0000
@@ -0,0 +1,540 @@
+SET optimizer_trace_max_mem_size=1048576;
+SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT, b INT);
+INSERT INTO t1 VALUES (2);
+INSERT INTO t2 VALUES (1,7),(2,7);
+# Subselect execute is traced every time it is executed
+SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=on";
+SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
+(SELECT a FROM t1 WHERE t1.a=t2.a) a
+NULL 1
+2 2
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`)"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `(SELECT a FROM t1 WHERE t1.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`a` = `test`.`t2`.`a`)",
+ "after_equality_propagation": "(`test`.`t1`.`a` = `test`.`t2`.`a`)",
+ "after_constant_propagation": "(`test`.`t1`.`a` = `test`.`t2`.`a`)",
+ "after_trivial_conditions_removal": "(`test`.`t1`.`a` = `test`.`t2`.`a`)"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "('2' = `test`.`t2`.`a`)",
+ "attached_conditions": [
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_exec */
+ },
+ {
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_exec */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+# Subselect execute is traced only the first time it is executed
+SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=on";
+SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
+(SELECT a FROM t1 WHERE t1.a=t2.a) a
+NULL 1
+2 2
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`)"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `(SELECT a FROM t1 WHERE t1.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`a` = `test`.`t2`.`a`)",
+ "after_equality_propagation": "(`test`.`t1`.`a` = `test`.`t2`.`a`)",
+ "after_constant_propagation": "(`test`.`t1`.`a` = `test`.`t2`.`a`)",
+ "after_trivial_conditions_removal": "(`test`.`t1`.`a` = `test`.`t2`.`a`)"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "('2' = `test`.`t2`.`a`)",
+ "attached_conditions": [
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_exec */
+ },
+ {
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_exec */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+DROP TABLE t1,t2;
+SET @@optimizer_trace_features="default";
+CREATE TABLE t1 (a FLOAT(5,4) zerofill);
+CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
+SELECT t1.a
+FROM t1
+WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
+t1.a= (SELECT a FROM t2 LIMIT 1) ;
+a
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+SELECT t1.a
+FROM t1
+WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
+t1.a= (SELECT a FROM t2 LIMIT 1) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` limit 1"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_preparation": {
+ "select#": 3,
+ "steps": [
+ {
+ "expanded_query": "/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` limit 1)) and (`test`.`t1`.`a` <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`a` = (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` limit 1)) and (`test`.`t1`.`a` <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)))",
+ "evaluate_subselect_cond_steps": [
+ {
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_exec */
+ }
+ ] /* evaluate_subselect_cond_steps */,
+ "after_equality_propagation": "((NULL <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `test`.`t2` limit 1), `test`.`t1`.`a`))",
+ "after_constant_propagation": "((NULL <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `test`.`t2` limit 1), `test`.`t1`.`a`))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
+ "after_trivial_conditions_removal": null
+ } /* condition_processing */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "Impossible WHERE"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+SELECT 1 FROM DUAL
+WHERE NOT EXISTS
+(SELECT * FROM t2 WHERE a = 50 AND b = 3);
+1
+1
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+SELECT 1 FROM DUAL
+WHERE NOT EXISTS
+(SELECT * FROM t2 WHERE a = 50 AND b = 3) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL where (not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
+ "after_equality_propagation": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))",
+ "after_constant_propagation": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))",
+ "evaluate_subselect_cond_steps": [
+ {
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))",
+ "after_equality_propagation": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))",
+ "after_constant_propagation": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))",
+ "after_trivial_conditions_removal": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_exec */
+ }
+ ] /* evaluate_subselect_cond_steps */,
+ "after_trivial_conditions_removal": null
+ } /* condition_processing */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+DROP TABLE t1,t2;
=== added file 'mysql-test/t/optimizer_trace_subquery.test'
--- a/mysql-test/t/optimizer_trace_subquery.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/optimizer_trace_subquery.test 2011-01-11 11:56:02 +0000
@@ -0,0 +1,49 @@
+--source include/have_optimizer_trace.inc
+
+SET optimizer_trace_max_mem_size=1048576; # 1MB
+SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT, b INT);
+INSERT INTO t1 VALUES (2);
+INSERT INTO t2 VALUES (1,7),(2,7);
+
+--echo # Subselect execute is traced every time it is executed
+SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=on";
+SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+--echo # Subselect execute is traced only the first time it is executed
+SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=on";
+SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+DROP TABLE t1,t2;
+SET @@optimizer_trace_features="default";
+
+
+CREATE TABLE t1 (a FLOAT(5,4) zerofill);
+CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
+
+# evaluate_subselect_cond_steps for build_equal_item()
+SELECT t1.a
+FROM t1
+WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
+ t1.a= (SELECT a FROM t2 LIMIT 1) ;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+# evaluate_subselect_cond_steps for remove_eq_conds
+SELECT 1 FROM DUAL
+WHERE NOT EXISTS
+ (SELECT * FROM t2 WHERE a = 50 AND b = 3);
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+DROP TABLE t1,t2;
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110111115602-ks9lkelwd9r0f6x7.bundle
