3891 Jorgen Loland 2012-05-21
BUG#13423851: CRASH IN ITEM_FIELD::ITEM_FIELD, WITH QUERY
USING A VIEW
Original committer forgot to record ps-protocol
optimizer trace result files.
modified:
mysql-test/suite/opt_trace/r/general_ps_prot_all.result
mysql-test/suite/opt_trace/r/general_ps_prot_none.result
3890 Jorgen Loland 2012-05-21
Bug#11754168 - PARTS OF INDEX_MERGE_INNODB.TEST ARE DISABLED
DUE TO EXPLAIN DIFFS
Trying to stabilize index_merge_innodb test
modified:
mysql-test/include/index_merge1.inc
mysql-test/r/index_merge_innodb.result
mysql-test/r/index_merge_myisam.result
=== modified file 'mysql-test/suite/opt_trace/r/general_ps_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/general_ps_prot_all.result 2012-03-31 18:30:05 +0000
+++ b/mysql-test/suite/opt_trace/r/general_ps_prot_all.result 2012-05-21 09:17:07 +0000
@@ -7450,7 +7450,7 @@ freturn 3 ret@0 {
"steps": [
] /* steps */
} 0 0
-set optimizer_trace_offset=-20, optimizer_trace_limit=20|
+set optimizer_trace_offset=-60, optimizer_trace_limit=60|
select * from t6 where d in (select f1() from t2 where s="c")|
d
select * from information_schema.OPTIMIZER_TRACE|
@@ -7579,12 +7579,6 @@ select * from t6 where d in (select f1()
},
{
"ref_optimizer_key_uses": [
- {
- "table": "`t6`",
- "field": "d",
- "equals": "`f1`()",
- "null_rejecting": false
- }
] /* ref_optimizer_key_uses */
},
{
@@ -7595,53 +7589,10 @@ select * from t6 where d in (select f1()
"rows_estimation": [
{
"table": "`t6`",
- "range_analysis": {
- "table_scan": {
- "rows": 4,
- "cost": 4.9068
- } /* table_scan */,
- "potential_range_indices": [
- {
- "index": "d",
- "usable": true,
- "key_parts": [
- "d"
- ] /* key_parts */
- }
- ] /* potential_range_indices */,
- "best_covering_index_scan": {
- "index": "d",
- "cost": 1.8698,
- "chosen": true
- } /* best_covering_index_scan */,
- "setup_range_conditions": [
- ] /* setup_range_conditions */,
- "group_index_range": {
- "chosen": false,
- "cause": "not_single_table"
- } /* group_index_range */,
- "analyzing_range_alternatives": {
- "range_scan_alternatives": [
- {
- "index": "d",
- "index_dives_for_eq_ranges": true,
- "ranges": [
- "3 <= d <= 3"
- ] /* ranges */,
- "index_only": true,
- "rows": 1,
- "cost": 2.21,
- "rowid_ordered": true,
- "chosen": false,
- "cause": "cost"
- }
- ] /* range_scan_alternatives */,
- "analyzing_roworder_intersect": {
- "usable": false,
- "cause": "too_few_roworder_scans"
- } /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
- } /* range_analysis */
+ "table_scan": {
+ "rows": 4,
+ "cost": 2
+ } /* table_scan */
},
{
"table": "`t2`",
@@ -7709,28 +7660,21 @@ select * from t6 where d in (select f1()
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "d",
- "rows": 1,
- "cost": 3.2,
- "chosen": true
- },
- {
"access_type": "scan",
"using_join_cache": true,
- "rows": 1,
- "cost": 2.6076,
+ "rows": 4,
+ "cost": 2.0076,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 5.8289,
- "rows_for_plan": 3,
+ "cost_for_plan": 7.0289,
+ "rows_for_plan": 12,
"semijoin_strategy_choice": [
{
"strategy": "DuplicatesWeedout",
- "cost": 8.6289,
- "rows": 1,
+ "cost": 12.229,
+ "rows": 4,
"duplicate_tables_left": true,
"chosen": true
}
@@ -7746,108 +7690,21 @@ select * from t6 where d in (select f1()
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "d",
- "rows": 1,
- "cost": 1.2,
- "chosen": true
- },
- {
"access_type": "scan",
- "cost": 2,
"rows": 4,
- "cause": "cost",
- "chosen": false
+ "cost": 2.0068,
+ "chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 1.2,
- "rows_for_plan": 1,
+ "cost_for_plan": 2.8068,
+ "rows_for_plan": 4,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "plan_prefix": [
- "`t6`"
- ] /* plan_prefix */,
- "table": "`t2`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 3,
- "cost": 2.0213,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 3.8213,
- "rows_for_plan": 3,
- "semijoin_strategy_choice": [
- {
- "strategy": "FirstMatch",
- "recalculate_access_paths_and_cost": {
- "tables": [
- {
- "table": "`t2`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 3,
- "cost": 2.0213,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recalculate_access_paths_and_cost */,
- "cost": 3.8213,
- "rows": 1,
- "chosen": true
- },
- {
- "strategy": "MaterializeLookup",
- "cost": 6.6212,
- "rows": 1,
- "duplicate_tables_left": false,
- "chosen": false
- },
- {
- "strategy": "DuplicatesWeedout",
- "cost": 6.6213,
- "rows": 1,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
+ "pruned_by_heuristic": true
},
{
- "final_semijoin_strategy": "FirstMatch",
- "recalculate_access_paths_and_cost": {
- "tables": [
- {
- "table": "`t2`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 3,
- "cost": 2.0215,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recalculate_access_paths_and_cost */
+ "final_semijoin_strategy": "DuplicateWeedout"
}
] /* considered_execution_plans */
},
@@ -7858,12 +7715,12 @@ select * from t6 where d in (select f1()
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
- "table": "`t6`",
- "attached": null
+ "table": "`t2`",
+ "attached": "(`t2`.`s` = 'c')"
},
{
- "table": "`t2`",
- "attached": "((`t6`.`d` = `f1`()) and (`t2`.`s` = 'c'))"
+ "table": "`t6`",
+ "attached": "(`t6`.`d` = `f1`())"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
@@ -7871,11 +7728,23 @@ select * from t6 where d in (select f1()
{
"refine_plan": [
{
- "table": "`t6`"
+ "creating_tmp_table": {
+ "tmp_table_info": {
+ "row_length": 9,
+ "key_length": 7,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 116508
+ } /* tmp_table_info */
+ } /* creating_tmp_table */
},
{
"table": "`t2`",
"access_type": "table_scan"
+ },
+ {
+ "table": "`t6`",
+ "access_type": "index_scan"
}
] /* refine_plan */
}
@@ -8127,64 +7996,35 @@ freturn 3 ret@0 {
"steps": [
] /* steps */
} 0 0
-select count(*) from information_schema.OPTIMIZER_TRACE|
-count(*)
-12
-set optimizer_trace_offset=3, optimizer_trace_limit=1|
-select * from t6 where d in (select f1() from t2 where s="c")|
-d
-select * from information_schema.OPTIMIZER_TRACE|
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
-insert into t1 values("z",0) {
+set ret@0 NULL {
"steps": [
] /* steps */
} 0 0
-create procedure p1(arg char(1))
-begin
-declare res int;
-select d into res from t6 where d in (select f1() from t2 where s=arg);
-select d+1 into res from t6 where d=res+1;
-end|
-set optimizer_trace_offset=0, optimizer_trace_limit=100;
-call p1("c")|
-Warnings:
-Warning 1329 No data - zero rows fetched, selected, or processed
-select * from information_schema.OPTIMIZER_TRACE|
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
-call p1("c") {
+insert into t1 values("z",0) {
"steps": [
] /* steps */
} 0 0
-set res@1 NULL {
+delete from t1 where id="z" {
"steps": [
+ {
+ "table": "`t1`",
+ "range_analysis": {
+ "table_scan": {
+ "rows": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
+ }
] /* steps */
} 0 0
-select d into res from t6 where d in (select f1() from t2 where s=arg) {
+select sum(data) into ret from t1 {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
- "join_preparation": {
- "select#": 2,
- "steps": [
- {
- "expanded_query": "/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = arg@0)"
- },
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true
- } /* transformation */
- }
- ] /* steps */
- } /* join_preparation */
- },
- {
- "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` where `t6`.`d` in (/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = arg@0))"
+ "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`"
}
] /* steps */
} /* join_preparation */
@@ -8194,348 +8034,717 @@ select d into res from t6 where d in (se
"select#": 1,
"steps": [
{
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true,
- "evaluating_constant_semijoin_conditions": [
- ] /* evaluating_constant_semijoin_conditions */
- } /* transformation */
- },
- {
- "transformations_to_nested_joins": {
- "transformations": [
- "semijoin"
- ] /* transformations */,
- "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` semi join (`t2`) where (1 and (`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))"
- } /* transformations_to_nested_joins */
- },
- {
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(1 and (`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))",
- "steps": [
- {
- "transformation": "equality_propagation",
- "resulting_condition": "(1 and multiple equal(arg@0, `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
- },
- {
- "transformation": "constant_propagation",
- "resulting_condition": "(1 and multiple equal(arg@0, `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
- },
- {
- "transformation": "trivial_condition_removal",
- "resulting_condition": "(multiple equal(arg@0, `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
- }
- ] /* steps */
- } /* condition_processing */
- },
- {
"table_dependencies": [
{
- "table": "`t6`",
+ "table": "`t1`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
- },
- {
- "table": "`t2`",
- "row_may_be_null": false,
- "map_bit": 1,
- "depends_on_map_bits": [
- ] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
- "ref_optimizer_key_uses": [
+ "rows_estimation": [
{
- "table": "`t6`",
- "field": "d",
- "equals": "`f1`()",
- "null_rejecting": false
+ "table": "`t1`",
+ "table_scan": {
+ "rows": 2,
+ "cost": 2
+ } /* table_scan */
}
- ] /* ref_optimizer_key_uses */
- },
- {
- "pulled_out_semijoin_tables": [
- ] /* pulled_out_semijoin_tables */
+ ] /* rows_estimation */
},
{
- "rows_estimation": [
+ "considered_execution_plans": [
{
- "table": "`t6`",
- "range_analysis": {
- "table_scan": {
- "rows": 4,
- "cost": 4.9068
- } /* table_scan */,
- "potential_range_indices": [
+ "plan_prefix": [
+ ] /* plan_prefix */,
+ "table": "`t1`",
+ "best_access_path": {
+ "considered_access_paths": [
{
- "index": "d",
- "usable": true,
- "key_parts": [
- "d"
- ] /* key_parts */
+ "access_type": "scan",
+ "rows": 2,
+ "cost": 2.0154,
+ "chosen": true
}
- ] /* potential_range_indices */,
- "best_covering_index_scan": {
- "index": "d",
- "cost": 1.8698,
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.4154,
+ "rows_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "table": "`t1`",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "table": "`t1`",
+ "access_type": "table_scan"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+freturn 3 ret@0 {
+ "steps": [
+ ] /* steps */
+} 0 0
+set ret@0 NULL {
+ "steps": [
+ ] /* steps */
+} 0 0
+insert into t1 values("z",0) {
+ "steps": [
+ ] /* steps */
+} 0 0
+delete from t1 where id="z" {
+ "steps": [
+ {
+ "table": "`t1`",
+ "range_analysis": {
+ "table_scan": {
+ "rows": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
+ }
+ ] /* steps */
+} 0 0
+select sum(data) into ret from t1 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "table_dependencies": [
+ {
+ "table": "`t1`",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": [
+ ] /* depends_on_map_bits */
+ }
+ ] /* table_dependencies */
+ },
+ {
+ "rows_estimation": [
+ {
+ "table": "`t1`",
+ "table_scan": {
+ "rows": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* rows_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "plan_prefix": [
+ ] /* plan_prefix */,
+ "table": "`t1`",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "rows": 2,
+ "cost": 2.0154,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.4154,
+ "rows_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "table": "`t1`",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "table": "`t1`",
+ "access_type": "table_scan"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+freturn 3 ret@0 {
+ "steps": [
+ ] /* steps */
+} 0 0
+select count(*) from information_schema.OPTIMIZER_TRACE|
+count(*)
+22
+set optimizer_trace_offset=3, optimizer_trace_limit=1|
+select * from t6 where d in (select f1() from t2 where s="c")|
+d
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+insert into t1 values("z",0) {
+ "steps": [
+ ] /* steps */
+} 0 0
+create procedure p1(arg char(1))
+begin
+declare res int;
+select d into res from t6 where d in (select f1() from t2 where s=arg);
+select d+1 into res from t6 where d=res+1;
+end|
+set optimizer_trace_offset=0, optimizer_trace_limit=100;
+call p1("c")|
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+call p1("c") {
+ "steps": [
+ ] /* steps */
+} 0 0
+set res@1 NULL {
+ "steps": [
+ ] /* steps */
+} 0 0
+select d into res from t6 where d in (select f1() from t2 where s=arg) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = arg@0)"
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
"chosen": true
- } /* best_covering_index_scan */,
- "setup_range_conditions": [
- ] /* setup_range_conditions */,
- "group_index_range": {
- "chosen": false,
- "cause": "not_single_table"
- } /* group_index_range */,
- "analyzing_range_alternatives": {
- "range_scan_alternatives": [
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` where `t6`.`d` in (/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = arg@0))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true,
+ "evaluating_constant_semijoin_conditions": [
+ ] /* evaluating_constant_semijoin_conditions */
+ } /* transformation */
+ },
+ {
+ "transformations_to_nested_joins": {
+ "transformations": [
+ "semijoin"
+ ] /* transformations */,
+ "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` semi join (`t2`) where (1 and (`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))"
+ } /* transformations_to_nested_joins */
+ },
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(1 and (`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(1 and multiple equal(arg@0, `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(1 and multiple equal(arg@0, `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(multiple equal(arg@0, `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "table_dependencies": [
+ {
+ "table": "`t6`",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": [
+ ] /* depends_on_map_bits */
+ },
+ {
+ "table": "`t2`",
+ "row_may_be_null": false,
+ "map_bit": 1,
+ "depends_on_map_bits": [
+ ] /* depends_on_map_bits */
+ }
+ ] /* table_dependencies */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "pulled_out_semijoin_tables": [
+ ] /* pulled_out_semijoin_tables */
+ },
+ {
+ "rows_estimation": [
+ {
+ "table": "`t6`",
+ "table_scan": {
+ "rows": 4,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "table": "`t2`",
+ "table_scan": {
+ "rows": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* rows_estimation */
+ },
+ {
+ "execution_plan_for_potential_materialization": {
+ "steps": [
+ {
+ "considered_execution_plans": [
+ {
+ "plan_prefix": [
+ ] /* plan_prefix */,
+ "table": "`t2`",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "rows": 3,
+ "cost": 2.0212,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.6212,
+ "rows_for_plan": 3,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */
+ } /* execution_plan_for_potential_materialization */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "plan_prefix": [
+ ] /* plan_prefix */,
+ "table": "`t2`",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "rows": 3,
+ "cost": 2.0212,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.6212,
+ "rows_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "plan_prefix": [
+ "`t2`"
+ ] /* plan_prefix */,
+ "table": "`t6`",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "rows": 4,
+ "cost": 2.0076,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 7.0289,
+ "rows_for_plan": 12,
+ "semijoin_strategy_choice": [
{
- "index": "d",
- "index_dives_for_eq_ranges": true,
- "ranges": [
- "3 <= d <= 3"
- ] /* ranges */,
- "index_only": true,
- "rows": 1,
- "cost": 2.21,
- "rowid_ordered": true,
- "chosen": false,
- "cause": "cost"
+ "strategy": "DuplicatesWeedout",
+ "cost": 12.229,
+ "rows": 4,
+ "duplicate_tables_left": true,
+ "chosen": true
}
- ] /* range_scan_alternatives */,
- "analyzing_roworder_intersect": {
- "usable": false,
- "cause": "too_few_roworder_scans"
- } /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
- } /* range_analysis */
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "plan_prefix": [
+ ] /* plan_prefix */,
+ "table": "`t6`",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "rows": 4,
+ "cost": 2.0068,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.8068,
+ "rows_for_plan": 4,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "pruned_by_heuristic": true
+ },
+ {
+ "final_semijoin_strategy": "DuplicateWeedout"
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`t6`.`d` = `f1`()) and (`t2`.`s` = arg@0))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "table": "`t2`",
+ "attached": "(`t2`.`s` = arg@0)"
+ },
+ {
+ "table": "`t6`",
+ "attached": "(`t6`.`d` = `f1`())"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "creating_tmp_table": {
+ "tmp_table_info": {
+ "row_length": 9,
+ "key_length": 7,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 116508
+ } /* tmp_table_info */
+ } /* creating_tmp_table */
+ },
+ {
+ "table": "`t2`",
+ "access_type": "table_scan"
},
{
- "table": "`t2`",
+ "table": "`t6`",
+ "access_type": "index_scan"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+set ret@0 NULL {
+ "steps": [
+ ] /* steps */
+} 0 0
+insert into t1 values("z",0) {
+ "steps": [
+ ] /* steps */
+} 0 0
+delete from t1 where id="z" {
+ "steps": [
+ {
+ "table": "`t1`",
+ "range_analysis": {
+ "table_scan": {
+ "rows": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
+ }
+ ] /* steps */
+} 0 0
+select sum(data) into ret from t1 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "table_dependencies": [
+ {
+ "table": "`t1`",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": [
+ ] /* depends_on_map_bits */
+ }
+ ] /* table_dependencies */
+ },
+ {
+ "rows_estimation": [
+ {
+ "table": "`t1`",
+ "table_scan": {
+ "rows": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* rows_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "plan_prefix": [
+ ] /* plan_prefix */,
+ "table": "`t1`",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "rows": 2,
+ "cost": 2.0154,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.4154,
+ "rows_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "table": "`t1`",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "table": "`t1`",
+ "access_type": "table_scan"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+freturn 3 ret@0 {
+ "steps": [
+ ] /* steps */
+} 0 0
+set ret@0 NULL {
+ "steps": [
+ ] /* steps */
+} 0 0
+insert into t1 values("z",0) {
+ "steps": [
+ ] /* steps */
+} 0 0
+delete from t1 where id="z" {
+ "steps": [
+ {
+ "table": "`t1`",
+ "range_analysis": {
+ "table_scan": {
+ "rows": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
+ }
+ ] /* steps */
+} 0 0
+select sum(data) into ret from t1 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "table_dependencies": [
+ {
+ "table": "`t1`",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": [
+ ] /* depends_on_map_bits */
+ }
+ ] /* table_dependencies */
+ },
+ {
+ "rows_estimation": [
+ {
+ "table": "`t1`",
"table_scan": {
- "rows": 3,
+ "rows": 2,
"cost": 2
} /* table_scan */
}
] /* rows_estimation */
},
{
- "execution_plan_for_potential_materialization": {
- "steps": [
- {
- "considered_execution_plans": [
- {
- "plan_prefix": [
- ] /* plan_prefix */,
- "table": "`t2`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "rows": 3,
- "cost": 2.0212,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.6212,
- "rows_for_plan": 3,
- "chosen": true
- }
- ] /* considered_execution_plans */
- }
- ] /* steps */
- } /* execution_plan_for_potential_materialization */
- },
- {
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
- "table": "`t2`",
+ "table": "`t1`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
- "rows": 3,
- "cost": 2.0212,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.6212,
- "rows_for_plan": 3,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "plan_prefix": [
- "`t2`"
- ] /* plan_prefix */,
- "table": "`t6`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "d",
- "rows": 1,
- "cost": 3.2,
- "chosen": true
- },
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 1,
- "cost": 2.6076,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 5.8289,
- "rows_for_plan": 3,
- "semijoin_strategy_choice": [
- {
- "strategy": "DuplicatesWeedout",
- "cost": 8.6289,
- "rows": 1,
- "duplicate_tables_left": true,
- "chosen": true
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- },
- {
- "plan_prefix": [
- ] /* plan_prefix */,
- "table": "`t6`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "d",
- "rows": 1,
- "cost": 1.2,
+ "rows": 2,
+ "cost": 2.0154,
"chosen": true
- },
- {
- "access_type": "scan",
- "cost": 2,
- "rows": 4,
- "cause": "cost",
- "chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 1.2,
- "rows_for_plan": 1,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "plan_prefix": [
- "`t6`"
- ] /* plan_prefix */,
- "table": "`t2`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 3,
- "cost": 2.0213,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 3.8213,
- "rows_for_plan": 3,
- "semijoin_strategy_choice": [
- {
- "strategy": "FirstMatch",
- "recalculate_access_paths_and_cost": {
- "tables": [
- {
- "table": "`t2`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 3,
- "cost": 2.0213,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recalculate_access_paths_and_cost */,
- "cost": 3.8213,
- "rows": 1,
- "chosen": true
- },
- {
- "strategy": "MaterializeLookup",
- "cost": 6.6212,
- "rows": 1,
- "duplicate_tables_left": false,
- "chosen": false
- },
- {
- "strategy": "DuplicatesWeedout",
- "cost": 6.6213,
- "rows": 1,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- },
- {
- "final_semijoin_strategy": "FirstMatch",
- "recalculate_access_paths_and_cost": {
- "tables": [
- {
- "table": "`t2`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 3,
- "cost": 2.0215,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recalculate_access_paths_and_cost */
+ "cost_for_plan": 2.4154,
+ "rows_for_plan": 2,
+ "chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
- "original_condition": "((`t6`.`d` = `f1`()) and (`t2`.`s` = arg@0))",
+ "original_condition": null,
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
- "table": "`t6`",
+ "table": "`t1`",
"attached": null
- },
- {
- "table": "`t2`",
- "attached": "((`t6`.`d` = `f1`()) and (`t2`.`s` = arg@0))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
@@ -8543,10 +8752,7 @@ select d into res from t6 where d in (se
{
"refine_plan": [
{
- "table": "`t6`"
- },
- {
- "table": "`t2`",
+ "table": "`t1`",
"access_type": "table_scan"
}
] /* refine_plan */
@@ -8563,6 +8769,10 @@ select d into res from t6 where d in (se
}
] /* steps */
} 0 0
+freturn 3 ret@0 {
+ "steps": [
+ ] /* steps */
+} 0 0
set ret@0 NULL {
"steps": [
] /* steps */
@@ -9839,12 +10049,6 @@ select d into res from t6 where d in (se
},
{
"ref_optimizer_key_uses": [
- {
- "table": "`t6`",
- "field": "d",
- "equals": "`f1`()",
- "null_rejecting": false
- }
] /* ref_optimizer_key_uses */
},
{
@@ -9855,53 +10059,10 @@ select d into res from t6 where d in (se
"rows_estimation": [
{
"table": "`t6`",
- "range_analysis": {
- "table_scan": {
- "rows": 4,
- "cost": 4.9068
- } /* table_scan */,
- "potential_range_indices": [
- {
- "index": "d",
- "usable": true,
- "key_parts": [
- "d"
- ] /* key_parts */
- }
- ] /* potential_range_indices */,
- "best_covering_index_scan": {
- "index": "d",
- "cost": 1.8698,
- "chosen": true
- } /* best_covering_index_scan */,
- "setup_range_conditions": [
- ] /* setup_range_conditions */,
- "group_index_range": {
- "chosen": false,
- "cause": "not_single_table"
- } /* group_index_range */,
- "analyzing_range_alternatives": {
- "range_scan_alternatives": [
- {
- "index": "d",
- "index_dives_for_eq_ranges": true,
- "ranges": [
- "3 <= d <= 3"
- ] /* ranges */,
- "index_only": true,
- "rows": 1,
- "cost": 2.21,
- "rowid_ordered": true,
- "chosen": false,
- "cause": "cost"
- }
- ] /* range_scan_alternatives */,
- "analyzing_roworder_intersect": {
- "usable": false,
- "cause": "too_few_roworder_scans"
- } /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
- } /* range_analysis */
+ "table_scan": {
+ "rows": 4,
+ "cost": 2
+ } /* table_scan */
},
{
"table": "`t2`",
@@ -9949,23 +10110,15 @@ select d into res from t6 where d in (se
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "d",
- "rows": 1,
- "cost": 1.2,
- "chosen": true
- },
- {
"access_type": "scan",
- "cost": 2,
"rows": 4,
- "cause": "cost",
- "chosen": false
+ "cost": 2.0068,
+ "chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 1.2,
- "rows_for_plan": 1,
+ "cost_for_plan": 2.8068,
+ "rows_for_plan": 4,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
@@ -9980,13 +10133,13 @@ select d into res from t6 where d in (se
"access_type": "scan",
"using_join_cache": true,
"rows": 7,
- "cost": 2.0496,
+ "cost": 2.0499,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 4.6496,
- "rows_for_plan": 7,
+ "cost_for_plan": 10.457,
+ "rows_for_plan": 28,
"semijoin_strategy_choice": [
{
"strategy": "FirstMatch",
@@ -10000,7 +10153,7 @@ select d into res from t6 where d in (se
"access_type": "scan",
"using_join_cache": true,
"rows": 7,
- "cost": 2.0496,
+ "cost": 2.0499,
"chosen": true
}
] /* considered_access_paths */
@@ -10008,21 +10161,21 @@ select d into res from t6 where d in (se
}
] /* tables */
} /* recalculate_access_paths_and_cost */,
- "cost": 4.6496,
- "rows": 1,
+ "cost": 10.457,
+ "rows": 4,
"chosen": true
},
{
"strategy": "MaterializeLookup",
- "cost": 8.2496,
- "rows": 1,
+ "cost": 10.456,
+ "rows": 4,
"duplicate_tables_left": false,
- "chosen": false
+ "chosen": true
},
{
"strategy": "DuplicatesWeedout",
- "cost": 8.2496,
- "rows": 1,
+ "cost": 18.857,
+ "rows": 4,
"duplicate_tables_left": false,
"chosen": false
}
@@ -10052,25 +10205,7 @@ select d into res from t6 where d in (se
"pruned_by_heuristic": true
},
{
- "final_semijoin_strategy": "FirstMatch",
- "recalculate_access_paths_and_cost": {
- "tables": [
- {
- "table": "`t2`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 7,
- "cost": 2.0496,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recalculate_access_paths_and_cost */
+ "final_semijoin_strategy": "MaterializeLookup"
}
] /* considered_execution_plans */
},
@@ -10086,7 +10221,7 @@ select d into res from t6 where d in (se
},
{
"table": "`t2`",
- "attached": "((`t6`.`d` = `f1`()) and (`t2`.`s` = arg@0))"
+ "attached": "(`t2`.`s` = arg@0)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
@@ -10094,10 +10229,27 @@ select d into res from t6 where d in (se
{
"refine_plan": [
{
- "table": "`t6`"
+ "table": "`t6`",
+ "access_type": "index_scan"
},
{
"table": "`t2`",
+ "unknown_key_1": {
+ "creating_tmp_table": {
+ "tmp_table_info": {
+ "row_length": 5,
+ "key_length": 5,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 209715
+ } /* tmp_table_info */
+ } /* creating_tmp_table */
+ },
+ "creating_semijoin_materialization_conditions": {
+ "evaluating_constant_semijoin_conditions": [
+ ] /* evaluating_constant_semijoin_conditions */,
+ "resulting_condition": "(`t6`.`d` = `sj-materialize`.`f1()`)"
+ } /* creating_semijoin_materialization_conditions */,
"access_type": "table_scan"
}
] /* refine_plan */
=== modified file 'mysql-test/suite/opt_trace/r/general_ps_prot_none.result'
--- a/mysql-test/suite/opt_trace/r/general_ps_prot_none.result 2012-03-31 18:30:05 +0000
+++ b/mysql-test/suite/opt_trace/r/general_ps_prot_none.result 2012-05-21 09:17:07 +0000
@@ -6557,11 +6557,346 @@ freturn 3 ret@0 {
"steps": [
] /* steps */
} 0 0
-set optimizer_trace_offset=-20, optimizer_trace_limit=20|
+set optimizer_trace_offset=-60, optimizer_trace_limit=60|
select * from t6 where d in (select f1() from t2 where s="c")|
d
select * from information_schema.OPTIMIZER_TRACE|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+select * from t6 where d in (select f1() from t2 where s="c") {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = 'c')"
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": false
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "EXISTS (CORRELATED SELECT)",
+ "chosen": true,
+ "evaluating_constant_where_conditions": [
+ ] /* evaluating_constant_where_conditions */
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` where <in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ }
+ ] /* steps */
+} 0 0
+select * from t6 where d in (select f1() from t2 where s="c") {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))"
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": false
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` where <in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "<in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "subselect_evaluation": [
+ ] /* subselect_evaluation */,
+ "resulting_condition": "<in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "subselect_evaluation": [
+ ] /* subselect_evaluation */,
+ "resulting_condition": "<in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "subselect_evaluation": [
+ ] /* subselect_evaluation */,
+ "resulting_condition": "<in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "table_dependencies": [
+ {
+ "table": "`t6`",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": [
+ ] /* depends_on_map_bits */
+ }
+ ] /* table_dependencies */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "rows_estimation": [
+ {
+ "table": "`t6`",
+ "table_scan": {
+ "rows": 4,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* rows_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "plan_prefix": [
+ ] /* plan_prefix */,
+ "table": "`t6`",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "rows": 4,
+ "cost": 2.0068,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.8068,
+ "rows_for_plan": 4,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "table": "`t6`",
+ "attached": "<in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "table": "`t6`",
+ "access_type": "index_scan"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((<cache>(`t6`.`d`) = `f1`()) and multiple equal('c', `t2`.`s`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((<cache>(`t6`.`d`) = `f1`()) and multiple equal('c', `t2`.`s`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((<cache>(`t6`.`d`) = `f1`()) and multiple equal('c', `t2`.`s`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "table_dependencies": [
+ {
+ "table": "`t2`",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": [
+ ] /* depends_on_map_bits */
+ }
+ ] /* table_dependencies */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "rows_estimation": [
+ {
+ "table": "`t2`",
+ "table_scan": {
+ "rows": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* rows_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "plan_prefix": [
+ ] /* plan_prefix */,
+ "table": "`t2`",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "rows": 3,
+ "cost": 2.0212,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.6212,
+ "rows_for_plan": 3,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "table": "`t2`",
+ "attached": "((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "table": "`t2`",
+ "access_type": "table_scan"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
set ret@0 NULL {
"steps": [
] /* steps */
@@ -7036,7 +7371,7 @@ freturn 3 ret@0 {
} 0 0
select count(*) from information_schema.OPTIMIZER_TRACE|
count(*)
-20
+22
set optimizer_trace_offset=3, optimizer_trace_limit=1|
select * from t6 where d in (select f1() from t2 where s="c")|
d
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:3890 to 3891) Bug#13423851 | Jorgen Loland | 21 May |
