From: Mattias Jonsson Date: February 23 2012 11:52am Subject: bzr push into mysql-trunk branch (mattias.jonsson:3948 to 3949) Bug#13769819 List-Archive: http://lists.mysql.com/commits/143042 X-Bug: 13769819 Message-Id: <201202231152.q1NBqHbi022460@acsmt356.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3949 Mattias Jonsson 2012-02-23 Bug#13769819: TIMEOUT ON PARTS.PARTITION_ALTER4_MYISAM TEST ON GCOV Fixed by only run the test if --big-test flag is given (will run on weekly tests) modified: mysql-test/suite/parts/t/partition_alter4_myisam.test 3948 Jorgen Loland 2012-02-23 WL#6158: SORTING OF TABLES BEFORE GREEDY_SEARCH DOES NOT TAKE KEY DEPENDENCY INTO ACCOUNT Earlier, the following algorithm was used to order tables before doing greedy search: 1) Outer tables before inner tables (as in "outer LEFT JOIN inner") 2) Increasing number of expected rows By taking key dependency into account when doing this pre-sorting, greedy search will be able to considerably reduce the number of plans because pruning is more likely to happen. In addition, merge and insert sort is implemented because the std::stable_sort implementation differs between platforms. This results in different QEPs which in turn is a problem for mtr testing. @ mysql-test/include/greedy_search_drop_tables.inc Add test for WL#6158 @ mysql-test/include/greedy_search_load_tables.inc Add test for WL#6158 @ mysql-test/include/print_greedy_search_count.inc Add test for WL#6158. Prints how many partial plans was generated by greedy search Add test for WL#6158 @ mysql-test/r/greedy_search.result Add test for WL#6158 @ mysql-test/r/join_cache_bka.result Plan changes. New plan has same cost as the previous cost so the plans are equal as far as greedy search is concerned. They also produce the same amount of handler calls. The new plan enables the use of range scan (which happens here) which is good. Note: Both the old and new greedy search algorithm depends on the name of the tables if they are otherwise equal. If t1 is renamed to tx1 in the existing test (without applying the changeset from this WL) you end up with the same plan recorded. @ mysql-test/r/join_cache_bka_nixbnl.result Plan changes. New plan has same cost as the previous cost so the plans are equal as far as greedy search is concerned. They also produce the same amount of handler calls. The new plan enables the use of range scan (which happens here) which is good. @ mysql-test/r/join_cache_bkaunique.result Plan changes. New plan has same cost as the previous cost so the plans are equal as far as greedy search is concerned. They also produce the same amount of handler calls. The new plan enables the use of range scan (which happens here) which is good. @ mysql-test/r/join_cache_bnl.result Plan changes. New plan has same cost as the previous cost so the plans are equal as far as greedy search is concerned. They also produce the same amount of handler calls. The new plan enables the use of range scan (which happens here) which is good. Note: Both the old and new greedy search algorithm depends on the name of the tables if they are otherwise equal. If t1 is renamed to tx1 in the existing test (without applying the changeset from this WL) you end up with the same plan recorded. @ mysql-test/r/join_cache_nojb.result Plan changes. New plan has same cost as the previous cost so the plans are equal as far as greedy search is concerned. They also produce the same amount of handler calls. The new plan enables the use of range scan (which happens here) which is good. @ mysql-test/r/join_nested.result Plan changes. New plan has same cost as the previous cost so the plans are equal as far as greedy search is concerned. @ mysql-test/r/join_nested_bka.result Plan changes. New plan has same cost as the previous cost so the plans are equal as far as greedy search is concerned. @ mysql-test/r/join_nested_bka_nixbnl.result Plan changes. New plan has same cost as the previous cost so the plans are equal as far as greedy search is concerned. @ mysql-test/r/subquery_all.result Plan changes. New plan has same cost as the previous cost so the plans are equal as far as greedy search is concerned. @ mysql-test/r/subquery_all_bka.result Plan changes. New plan has same cost as the previous cost so the plans are equal as far as greedy search is concerned. @ mysql-test/r/subquery_mat_all.result Plan changes. The new plan is not better than the old one, but Roy agrees that this is due to too agressive pruning for SJ. Will be fixed by WL 6156. @ mysql-test/r/subquery_sj_dupsweed.result Plan changes. * "During work with BUG#45863": The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan * bug 12797534: The new plan has the same number of expected rows, and the same calculated cost. Therefore, disabling pruning has no effect with or without this changeset. However, it makes more sense to do eq-ref early so the new plan is good. * BUG#13589848: The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan @ mysql-test/r/subquery_sj_dupsweed_bka.result Plan changes. * "During work with BUG#45863": The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan * bug 12797534: The new plan has the same number of expected rows, and the same calculated cost. Therefore, disabling pruning has no effect with or without this changeset. However, it makes more sense to do eq-ref early so the new plan is good. * BUG#13589848: The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan @ mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result Plan changes. * "During work with BUG#45863": The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan * bug 12797534: The new plan has the same number of expected rows, and the same calculated cost. Therefore, disabling pruning has no effect with or without this changeset. However, it makes more sense to do eq-ref early so the new plan is good. @ mysql-test/r/subquery_sj_dupsweed_bkaunique.result Plan changes. * "During work with BUG#45863": The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan * bug 12797534: The new plan has the same number of expected rows, and the same calculated cost. Therefore, disabling pruning has no effect with or without this changeset. However, it makes more sense to do eq-ref early so the new plan is good. * BUG#13589848: The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan @ mysql-test/r/subquery_sj_firstmatch.result Plan changes. * "During work with BUG#45863": The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan * bug 12797534: The new plan has the same number of expected rows, and the same calculated cost. Therefore, disabling pruning has no effect with or without this changeset. However, it makes more sense to do eq-ref early so the new plan is good. * BUG#13589848: The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan @ mysql-test/r/subquery_sj_firstmatch_bka.result Plan changes. * "During work with BUG#45863": The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan * bug 12797534: The new plan has the same number of expected rows, and the same calculated cost. Therefore, disabling pruning has no effect with or without this changeset. However, it makes more sense to do eq-ref early so the new plan is good. * BUG#13589848: The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan @ mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result Plan changes. * "During work with BUG#45863": The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan * bug 12797534: The new plan has the same number of expected rows, and the same calculated cost. Therefore, disabling pruning has no effect with or without this changeset. However, it makes more sense to do eq-ref early so the new plan is good. @ mysql-test/r/subquery_sj_firstmatch_bkaunique.result Plan changes. * "During work with BUG#45863": The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan * bug 12797534: The new plan has the same number of expected rows, and the same calculated cost. Therefore, disabling pruning has no effect with or without this changeset. However, it makes more sense to do eq-ref early so the new plan is good. * BUG#13589848: The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan @ mysql-test/r/subquery_sj_loosescan.result Plan changes. * "During work with BUG#45863": The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan * bug 12797534: The new plan has the same number of expected rows, and the same calculated cost. Therefore, disabling pruning has no effect with or without this changeset. However, it makes more sense to do eq-ref early so the new plan is good. @ mysql-test/r/subquery_sj_loosescan_bka.result Plan changes. * "During work with BUG#45863": The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan * bug 12797534: The new plan has the same number of expected rows, and the same calculated cost. Therefore, disabling pruning has no effect with or without this changeset. However, it makes more sense to do eq-ref early so the new plan is good. @ mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result Plan changes. * "During work with BUG#45863": The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan * bug 12797534: The new plan has the same number of expected rows, and the same calculated cost. Therefore, disabling pruning has no effect with or without this changeset. However, it makes more sense to do eq-ref early so the new plan is good. @ mysql-test/r/subquery_sj_loosescan_bkaunique.result Plan changes. * "During work with BUG#45863": The new plan is identical to the plan created without this changeset if pruning is disabled. Hence, this CS makes the optimizer find a better plan * bug 12797534: The new plan has the same number of expected rows, and the same calculated cost. Therefore, disabling pruning has no effect with or without this changeset. However, it makes more sense to do eq-ref early so the new plan is good. @ mysql-test/suite/opt_trace/r/bugs_no_prot_all.result Different trace due to initially different order of the tables (WL6158) @ mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result Different trace due to initially different order of the tables (WL6158) @ mysql-test/suite/opt_trace/r/subquery_no_prot.result Different trace due to initially different order of the tables (WL6158) @ mysql-test/suite/opt_trace/r/subquery_ps_prot.result Different trace due to initially different order of the tables (WL6158) @ mysql-test/t/greedy_search.test Add test for WL#6158 @ sql/merge_sort.h Implementation of STL compliant insert sort and merge sort @ sql/mysqld.cc Add system variable Opt_partial_plans: count number of partial plans explored by greedy search. @ sql/sql_class.h Add system variable Opt_partial_plans: count number of partial plans explored by greedy search. @ sql/sql_planner.cc Change pre-greedysort ordering of tables to merge_sort Add system variable Opt_partial_plans: count number of partial plans explored by greedy search. @ sql/sql_planner.h Updated comment @ sql/sql_select.h Implementation of JOIN_TAB comparison functions for use with sorting. The new comparison functions take key dependency into account in pre-greedysort ordering @ unittest/gunit/join_tab_sort-t.cc Add unit tests for JOIN_TAB sorting added: mysql-test/include/greedy_search_drop_tables.inc mysql-test/include/greedy_search_load_tables.inc mysql-test/include/print_greedy_search_count.inc mysql-test/r/greedy_search.result mysql-test/t/greedy_search.test sql/merge_sort.h unittest/gunit/join_tab_sort-t.cc modified: mysql-test/r/join_cache_bka.result mysql-test/r/join_cache_bka_nixbnl.result mysql-test/r/join_cache_bkaunique.result mysql-test/r/join_cache_bnl.result mysql-test/r/join_cache_nojb.result mysql-test/r/join_nested.result mysql-test/r/join_nested_bka.result mysql-test/r/join_nested_bka_nixbnl.result mysql-test/r/subquery_all.result mysql-test/r/subquery_all_bka.result mysql-test/r/subquery_mat_all.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_bka.result mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result mysql-test/r/subquery_sj_dupsweed_bkaunique.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_bka.result mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result mysql-test/r/subquery_sj_firstmatch_bkaunique.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_bka.result mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result mysql-test/r/subquery_sj_loosescan_bkaunique.result 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/subquery_no_prot.result mysql-test/suite/opt_trace/r/subquery_ps_prot.result sql/mysqld.cc sql/sql_class.h sql/sql_planner.cc sql/sql_planner.h sql/sql_select.h unittest/gunit/CMakeLists.txt === modified file 'mysql-test/suite/parts/t/partition_alter4_myisam.test' --- a/mysql-test/suite/parts/t/partition_alter4_myisam.test revid:jorgen.loland@stripped +++ b/mysql-test/suite/parts/t/partition_alter4_myisam.test revid:mattias.jonsson@stripped @@ -45,6 +45,8 @@ let $more_pk_ui_tests= 0; # Does not work with --embedded --source include/not_embedded.inc +# This test takes long time, so only run it with the --big mtr-flag. +--source include/big_test.inc #------------------------------------------------------------------------------# # Engine specific settings and requirements No bundle (reason: useless for push emails).