From: Jorgen Loland Date: August 22 2011 6:52am Subject: bzr push into mysql-trunk branch (jorgen.loland:3427 to 3428) List-Archive: http://lists.mysql.com/commits/140739 Message-Id: <20110822065228.A913412E@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3428 Jorgen Loland 2011-08-22 Recorded result files after merge modified: mysql-test/suite/opt_trace/r/bugs_no_prot_all.result mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result mysql-test/suite/opt_trace/r/general_no_prot_all.result mysql-test/suite/opt_trace/r/general_ps_prot_all.result 3427 Jorgen Loland 2011-08-19 Recorded result files after merge modified: mysql-test/r/innodb_explain_non_select_all.result mysql-test/r/myisam_explain_non_select_all.result mysql-test/r/subquery_all.result mysql-test/r/subquery_all_bka.result mysql-test/r/subquery_all_bka_nobnl.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_bka.result mysql-test/r/subquery_sj_dupsweed_bka_nobnl.result mysql-test/r/subquery_sj_dupsweed_bkaunique.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_bka.result mysql-test/r/subquery_sj_loosescan_bka_nobnl.result mysql-test/r/subquery_sj_loosescan_bkaunique.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_bka.result mysql-test/r/subquery_sj_mat_bkaunique.result === modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_all.result' --- a/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result 2011-07-20 13:10:44 +0000 +++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result 2011-08-22 06:52:04 +0000 @@ -692,9 +692,9 @@ field1 ( SELECT COUNT( col_int_key ) FROM t1 ) 0 4 +1 4 8 4 8 4 -1 4 8 4 8 4 select * from information_schema.optimizer_trace; === modified file 'mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result' --- a/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result 2011-07-20 13:10:44 +0000 +++ b/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result 2011-08-22 06:52:04 +0000 @@ -692,9 +692,9 @@ field1 ( SELECT COUNT( col_int_key ) FROM t1 ) 0 4 +1 4 8 4 8 4 -1 4 8 4 8 4 select * from information_schema.optimizer_trace; === modified file 'mysql-test/suite/opt_trace/r/general_no_prot_all.result' --- a/mysql-test/suite/opt_trace/r/general_no_prot_all.result 2011-08-17 10:30:04 +0000 +++ b/mysql-test/suite/opt_trace/r/general_no_prot_all.result 2011-08-22 06:52:04 +0000 @@ -663,13 +663,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul } /* join_optimization */ }, { - "join_execution": { - "select#": 2, - "steps": [ - ] /* steps */ - } /* join_execution */ - }, - { "join_optimization": { "select#": 3, "steps": [ @@ -750,6 +743,13 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul }, { "join_execution": { + "select#": 2, + "steps": [ + ] /* steps */ + } /* join_execution */ + }, + { + "join_execution": { "select#": 3, "steps": [ ] /* steps */ @@ -1011,13 +1011,6 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR } /* join_optimization */ }, { - "join_execution": { - "select#": 2, - "steps": [ - ] /* steps */ - } /* join_execution */ - }, - { "join_optimization": { "select#": 3, "steps": [ @@ -1049,6 +1042,13 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR }, { "join_execution": { + "select#": 2, + "steps": [ + ] /* steps */ + } /* join_execution */ + }, + { + "join_execution": { "select#": 3, "steps": [ ] /* steps */ @@ -3702,13 +3702,6 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT } /* join_optimization */ }, { - "join_execution": { - "select#": 1, - "steps": [ - ] /* steps */ - } /* join_execution */ - }, - { "join_optimization": { "select#": 2, "steps": [ @@ -3797,6 +3790,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT }, { "join_execution": { + "select#": 1, + "steps": [ + ] /* steps */ + } /* join_execution */ + }, + { + "join_execution": { "select#": 2, "steps": [ ] /* steps */ @@ -9463,7 +9463,7 @@ drop view v1; create view v1 as select * from t1 where id < "c" limit 2; explain select * from v1 where id="b"; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY ref auto_key0 auto_key0 16 const 0 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES @@ -9489,114 +9489,107 @@ explain select * from v1 where id="b" { } /* join_preparation */ }, { + "join_preparation": { + "select#": 1, + "steps": [ + { + "expanded_query": "/* select#1 */ select `v1`.`id` AS `id`,`v1`.`data` AS `data` from `test`.`v1` where (`v1`.`id` = 'b')" + } + ] /* steps */ + } /* join_preparation */ + }, + { "join_optimization": { - "select#": 2, + "select#": 1, "steps": [ { - "condition_processing": { - "condition": "WHERE", - "original_condition": "(`test`.`t1`.`id` < 'c')", + "join_optimization": { + "select#": 2, "steps": [ { - "transformation": "equality_propagation", - "resulting_condition": "(`test`.`t1`.`id` < 'c')" + "condition_processing": { + "condition": "WHERE", + "original_condition": "(`test`.`t1`.`id` < 'c')", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "(`test`.`t1`.`id` < 'c')" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "(`test`.`t1`.`id` < 'c')" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "(`test`.`t1`.`id` < 'c')" + } + ] /* steps */ + } /* condition_processing */ }, { - "transformation": "constant_propagation", - "resulting_condition": "(`test`.`t1`.`id` < 'c')" + "ref_optimizer_key_uses": [ + ] /* ref_optimizer_key_uses */ }, { - "transformation": "trivial_condition_removal", - "resulting_condition": "(`test`.`t1`.`id` < 'c')" - } - ] /* 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": [ + "records_estimation": [ { - "access_type": "scan", - "records": 3, - "cost": 2.0154, + "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", + "records": 3, + "cost": 2.0154, + "chosen": true + } + ] /* considered_access_paths */ + } /* best_access_path */, + "cost_for_plan": 2.6154, + "records_for_plan": 3, "chosen": true } - ] /* considered_access_paths */ - } /* best_access_path */, - "cost_for_plan": 2.6154, - "records_for_plan": 3, - "chosen": true - } - ] /* considered_execution_plans */ - }, - { - "attaching_conditions_to_tables": { - "original_condition": "(`test`.`t1`.`id` < 'c')", - "attached_conditions_computation": [ - ] /* attached_conditions_computation */, - "attached_conditions_summary": [ + ] /* considered_execution_plans */ + }, { - "database": "test", - "table": "t1", - "attached": "(`test`.`t1`.`id` < 'c')" + "attaching_conditions_to_tables": { + "original_condition": "(`test`.`t1`.`id` < 'c')", + "attached_conditions_computation": [ + ] /* attached_conditions_computation */, + "attached_conditions_summary": [ + { + "database": "test", + "table": "t1", + "attached": "(`test`.`t1`.`id` < 'c')" + } + ] /* attached_conditions_summary */ + } /* attaching_conditions_to_tables */ + }, + { + "refine_plan": [ + { + "database": "test", + "table": "t1", + "scan_type": "table" + } + ] /* refine_plan */ } - ] /* attached_conditions_summary */ - } /* attaching_conditions_to_tables */ + ] /* steps */ + } /* join_optimization */ }, { - "refine_plan": [ - { - "database": "test", - "table": "t1", - "scan_type": "table" - } - ] /* refine_plan */ - } - ] /* steps */ - } /* join_optimization */ - }, - { - "join_execution": { - "select#": 2, - "steps": [ - ] /* steps */ - } /* join_execution */ - }, - { - "join_preparation": { - "select#": 1, - "steps": [ - { - "expanded_query": "/* select#1 */ select `v1`.`id` AS `id`,`v1`.`data` AS `data` from `test`.`v1` where (`v1`.`id` = 'b')" - } - ] /* steps */ - } /* join_preparation */ - }, - { - "join_optimization": { - "select#": 1, - "steps": [ - { "condition_processing": { "condition": "WHERE", "original_condition": "(`v1`.`id` = 'b')", @@ -9618,17 +9611,20 @@ explain select * from v1 where id="b" { }, { "ref_optimizer_key_uses": [ + { + "database": "", + "table": "v1", + "field": "id", + "equals": "'b'", + "null_rejecting": false + } ] /* ref_optimizer_key_uses */ }, { "records_estimation": [ { "database": "", - "table": "v1", - "table_scan": { - "records": 2, - "cost": 10 - } /* table_scan */ + "table": "v1" } ] /* records_estimation */ }, @@ -9640,15 +9636,23 @@ explain select * from v1 where id="b" { "best_access_path": { "considered_access_paths": [ { + "access_type": "ref", + "index": "auto_key0", + "records": 0.2, + "cost": 0.24, + "chosen": true + }, + { "access_type": "scan", + "cost": 10, "records": 2, - "cost": 10.1, - "chosen": true + "cause": "cost", + "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, - "cost_for_plan": 10.5, - "records_for_plan": 2, + "cost_for_plan": 0.24, + "records_for_plan": 0.2, "chosen": true } ] /* considered_execution_plans */ @@ -9671,8 +9675,7 @@ explain select * from v1 where id="b" { "refine_plan": [ { "database": "", - "table": "v1", - "scan_type": "table" + "table": "v1" } ] /* refine_plan */ } @@ -9847,15 +9850,15 @@ select TRACE into dumpfile 'MYSQLTEST_VA } }, { - "join_execution": { - "select#": 1, + "join_optimization": { + "select#": 2, "steps": [ ] } }, { - "join_optimization": { - "select#": 2, + "join_execution": { + "select#": 1, "steps": [ ] } === 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 2011-08-17 10:30:04 +0000 +++ b/mysql-test/suite/opt_trace/r/general_ps_prot_all.result 2011-08-22 06:52:04 +0000 @@ -663,13 +663,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul } /* join_optimization */ }, { - "join_execution": { - "select#": 2, - "steps": [ - ] /* steps */ - } /* join_execution */ - }, - { "join_optimization": { "select#": 3, "steps": [ @@ -750,6 +743,13 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul }, { "join_execution": { + "select#": 2, + "steps": [ + ] /* steps */ + } /* join_execution */ + }, + { + "join_execution": { "select#": 3, "steps": [ ] /* steps */ @@ -991,13 +991,6 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR } /* join_optimization */ }, { - "join_execution": { - "select#": 2, - "steps": [ - ] /* steps */ - } /* join_execution */ - }, - { "join_optimization": { "select#": 3, "steps": [ @@ -1029,6 +1022,13 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR }, { "join_execution": { + "select#": 2, + "steps": [ + ] /* steps */ + } /* join_execution */ + }, + { + "join_execution": { "select#": 3, "steps": [ ] /* steps */ @@ -3682,13 +3682,6 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT } /* join_optimization */ }, { - "join_execution": { - "select#": 1, - "steps": [ - ] /* steps */ - } /* join_execution */ - }, - { "join_optimization": { "select#": 2, "steps": [ @@ -3777,6 +3770,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT }, { "join_execution": { + "select#": 1, + "steps": [ + ] /* steps */ + } /* join_execution */ + }, + { + "join_execution": { "select#": 2, "steps": [ ] /* steps */ @@ -9421,7 +9421,7 @@ drop view v1; create view v1 as select * from t1 where id < "c" limit 2; explain select * from v1 where id="b"; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY ref auto_key0 auto_key0 16 const 0 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES @@ -9447,114 +9447,107 @@ explain select * from v1 where id="b" { } /* join_preparation */ }, { + "join_preparation": { + "select#": 1, + "steps": [ + { + "expanded_query": "/* select#1 */ select `v1`.`id` AS `id`,`v1`.`data` AS `data` from `test`.`v1` where (`v1`.`id` = 'b')" + } + ] /* steps */ + } /* join_preparation */ + }, + { "join_optimization": { - "select#": 2, + "select#": 1, "steps": [ { - "condition_processing": { - "condition": "WHERE", - "original_condition": "(`test`.`t1`.`id` < 'c')", + "join_optimization": { + "select#": 2, "steps": [ { - "transformation": "equality_propagation", - "resulting_condition": "(`test`.`t1`.`id` < 'c')" + "condition_processing": { + "condition": "WHERE", + "original_condition": "(`test`.`t1`.`id` < 'c')", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "(`test`.`t1`.`id` < 'c')" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "(`test`.`t1`.`id` < 'c')" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "(`test`.`t1`.`id` < 'c')" + } + ] /* steps */ + } /* condition_processing */ }, { - "transformation": "constant_propagation", - "resulting_condition": "(`test`.`t1`.`id` < 'c')" + "ref_optimizer_key_uses": [ + ] /* ref_optimizer_key_uses */ }, { - "transformation": "trivial_condition_removal", - "resulting_condition": "(`test`.`t1`.`id` < 'c')" - } - ] /* 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": [ + "records_estimation": [ { - "access_type": "scan", - "records": 3, - "cost": 2.0154, + "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", + "records": 3, + "cost": 2.0154, + "chosen": true + } + ] /* considered_access_paths */ + } /* best_access_path */, + "cost_for_plan": 2.6154, + "records_for_plan": 3, "chosen": true } - ] /* considered_access_paths */ - } /* best_access_path */, - "cost_for_plan": 2.6154, - "records_for_plan": 3, - "chosen": true - } - ] /* considered_execution_plans */ - }, - { - "attaching_conditions_to_tables": { - "original_condition": "(`test`.`t1`.`id` < 'c')", - "attached_conditions_computation": [ - ] /* attached_conditions_computation */, - "attached_conditions_summary": [ + ] /* considered_execution_plans */ + }, { - "database": "test", - "table": "t1", - "attached": "(`test`.`t1`.`id` < 'c')" + "attaching_conditions_to_tables": { + "original_condition": "(`test`.`t1`.`id` < 'c')", + "attached_conditions_computation": [ + ] /* attached_conditions_computation */, + "attached_conditions_summary": [ + { + "database": "test", + "table": "t1", + "attached": "(`test`.`t1`.`id` < 'c')" + } + ] /* attached_conditions_summary */ + } /* attaching_conditions_to_tables */ + }, + { + "refine_plan": [ + { + "database": "test", + "table": "t1", + "scan_type": "table" + } + ] /* refine_plan */ } - ] /* attached_conditions_summary */ - } /* attaching_conditions_to_tables */ + ] /* steps */ + } /* join_optimization */ }, { - "refine_plan": [ - { - "database": "test", - "table": "t1", - "scan_type": "table" - } - ] /* refine_plan */ - } - ] /* steps */ - } /* join_optimization */ - }, - { - "join_execution": { - "select#": 2, - "steps": [ - ] /* steps */ - } /* join_execution */ - }, - { - "join_preparation": { - "select#": 1, - "steps": [ - { - "expanded_query": "/* select#1 */ select `v1`.`id` AS `id`,`v1`.`data` AS `data` from `test`.`v1` where (`v1`.`id` = 'b')" - } - ] /* steps */ - } /* join_preparation */ - }, - { - "join_optimization": { - "select#": 1, - "steps": [ - { "condition_processing": { "condition": "WHERE", "original_condition": "(`v1`.`id` = 'b')", @@ -9576,17 +9569,20 @@ explain select * from v1 where id="b" { }, { "ref_optimizer_key_uses": [ + { + "database": "", + "table": "v1", + "field": "id", + "equals": "'b'", + "null_rejecting": false + } ] /* ref_optimizer_key_uses */ }, { "records_estimation": [ { "database": "", - "table": "v1", - "table_scan": { - "records": 2, - "cost": 10 - } /* table_scan */ + "table": "v1" } ] /* records_estimation */ }, @@ -9598,15 +9594,23 @@ explain select * from v1 where id="b" { "best_access_path": { "considered_access_paths": [ { + "access_type": "ref", + "index": "auto_key0", + "records": 0.2, + "cost": 0.24, + "chosen": true + }, + { "access_type": "scan", + "cost": 10, "records": 2, - "cost": 10.1, - "chosen": true + "cause": "cost", + "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, - "cost_for_plan": 10.5, - "records_for_plan": 2, + "cost_for_plan": 0.24, + "records_for_plan": 0.2, "chosen": true } ] /* considered_execution_plans */ @@ -9629,8 +9633,7 @@ explain select * from v1 where id="b" { "refine_plan": [ { "database": "", - "table": "v1", - "scan_type": "table" + "table": "v1" } ] /* refine_plan */ } @@ -9805,15 +9808,15 @@ select TRACE into dumpfile 'MYSQLTEST_VA } }, { - "join_execution": { - "select#": 1, + "join_optimization": { + "select#": 2, "steps": [ ] } }, { - "join_optimization": { - "select#": 2, + "join_execution": { + "select#": 1, "steps": [ ] } No bundle (reason: useless for push emails).