From: Oystein Grovlen Date: September 27 2010 10:56am Subject: bzr commit into mysql-next-mr-bugfixing branch (oystein.grovlen:3244) WL#5559 List-Archive: http://lists.mysql.com/commits/119160 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0769178670==" --===============0769178670== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/og136792/mysql/mysql-next-mr-wl5559/ based on revid:jorgen.loland@stripped 3244 Oystein Grovlen 2010-09-27 WL#5559 - Factor tmp table out of optimizer. Phase 1. Moved code to create temporary table from JOIN::optimize() to a new method JOIN::create_intermediate_table(). Moved the call of this method to JOIN::exec(). Some clean-up at end of JOIN::optimize that is possible when this code has been moved. @ sql/sql_select.cc - Added method JOIN::create_intermediate_table() with code moved from JOIN::optimize(). This method is now called from JOIN::exec. - Also moved code for setting tmp_table_param.precomputed_group_by from JOIN::optimize() to JOIN::exec() - Dropped local variable in JOIN::optimize(), need_distinct. JOIN::create_intermediate_table() instead calls is_using_agg_loose_index_scan() the same way as JOIN:exec() currently does. - Clean-up of code for returning from JOIN::optimize(). @ sql/sql_select.h Added method JOIN::create_intermediate_table_(). modified: sql/sql_select.cc sql/sql_select.h === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-09-15 08:53:08 +0000 +++ b/sql/sql_select.cc 2010-09-27 10:56:33 +0000 @@ -1752,7 +1752,6 @@ static int clear_sj_tmp_tables(JOIN *joi int JOIN::optimize() { - bool need_distinct; ulonglong select_opts_for_readinfo; uint no_jbuf_after; @@ -2458,148 +2457,9 @@ JOIN::optimize() } tmp_having= having; - if (select_options & SELECT_DESCRIBE) - { - error= 0; - DBUG_RETURN(0); - } - having= 0; - - /* - The loose index scan access method guarantees that all grouping or - duplicate row elimination (for distinct) is already performed - during data retrieval, and that all MIN/MAX functions are already - computed for each group. Thus all MIN/MAX functions should be - treated as regular functions, and there is no need to perform - grouping in the main execution loop. - Notice that currently loose index scan is applicable only for - single table queries, thus it is sufficient to test only the first - join_tab element of the plan for its access method. - */ - need_distinct= TRUE; - if (join_tab->is_using_loose_index_scan()) - { - tmp_table_param.precomputed_group_by= TRUE; - if (join_tab->is_using_agg_loose_index_scan()) - { - need_distinct= FALSE; - tmp_table_param.precomputed_group_by= FALSE; - } - } - - /* Create a tmp table if distinct or if the sort is too complicated */ - if (need_tmp) + if (!(select_options & SELECT_DESCRIBE)) { - DBUG_PRINT("info",("Creating tmp table")); - thd_proc_info(thd, "Creating tmp table"); - - init_items_ref_array(); - - tmp_table_param.hidden_field_count= (all_fields.elements - - fields_list.elements); - ORDER *tmp_group= ((!simple_group && !procedure && - !(test_flags & TEST_NO_KEY_GROUP)) ? group_list : - (ORDER*) 0); - /* - Pushing LIMIT to the temporary table creation is not applicable - when there is ORDER BY or GROUP BY or there is no GROUP BY, but - there are aggregate functions, because in all these cases we need - all result rows. - */ - ha_rows tmp_rows_limit= ((order == 0 || skip_sort_order) && - !tmp_group && - !thd->lex->current_select->with_sum_func) ? - select_limit : HA_POS_ERROR; - - if (!(exec_tmp_table1= - create_tmp_table(thd, &tmp_table_param, all_fields, - tmp_group, group_list ? 0 : select_distinct, - group_list && simple_group, - select_options, tmp_rows_limit, ""))) - DBUG_RETURN(1); - - /* - We don't have to store rows in temp table that doesn't match HAVING if: - - we are sorting the table and writing complete group rows to the - temp table. - - We are using DISTINCT without resolving the distinct as a GROUP BY - on all columns. - - If having is not handled here, it will be checked before the row - is sent to the client. - */ - if (tmp_having && - (sort_and_group || (exec_tmp_table1->distinct && !group_list))) - having= tmp_having; - - /* if group or order on first table, sort first */ - if (group_list && simple_group) - { - DBUG_PRINT("info",("Sorting for group")); - thd_proc_info(thd, "Sorting for group"); - if (create_sort_index(thd, this, group_list, - HA_POS_ERROR, HA_POS_ERROR, FALSE) || - alloc_group_fields(this, group_list) || - make_sum_func_list(all_fields, fields_list, 1) || - prepare_sum_aggregators(sum_funcs, need_distinct) || - setup_sum_funcs(thd, sum_funcs)) - { - DBUG_RETURN(1); - } - group_list=0; - } - else - { - if (make_sum_func_list(all_fields, fields_list, 0) || - prepare_sum_aggregators(sum_funcs, need_distinct) || - setup_sum_funcs(thd, sum_funcs)) - { - DBUG_RETURN(1); - } - - if (!group_list && ! exec_tmp_table1->distinct && order && simple_order) - { - thd_proc_info(thd, "Sorting for order"); - if (create_sort_index(thd, this, order, - HA_POS_ERROR, HA_POS_ERROR, TRUE)) - { - DBUG_RETURN(1); - } - order=0; - } - } - - /* - Optimize distinct when used on some of the tables - SELECT DISTINCT t1.a FROM t1,t2 WHERE t1.b=t2.b - In this case we can stop scanning t2 when we have found one t1.a - */ - - if (exec_tmp_table1->distinct) - { - table_map used_tables= thd->used_tables; - JOIN_TAB *last_join_tab= join_tab+tables-1; - do - { - if (used_tables & last_join_tab->table->map) - break; - last_join_tab->not_used_in_distinct=1; - } while (last_join_tab-- != join_tab); - /* Optimize "select distinct b from t1 order by key_part_1 limit #" */ - if (order && skip_sort_order) - { - /* Should always succeed */ - if (test_if_skip_sort_order(&join_tab[const_tables], - order, unit->select_limit_cnt, 0, - &join_tab[const_tables].table-> - keys_in_use_for_order_by)) - order=0; - } - } - - /* If this join belongs to an uncacheable query save the original join */ - if (select_lex->uncacheable && init_save_join_tab()) - DBUG_RETURN(-1); /* purecov: inspected */ + having= 0; } error= 0; @@ -2867,20 +2727,41 @@ JOIN::exec() */ curr_join->examined_rows= 0; + /* + The loose index scan access method guarantees that all grouping or + duplicate row elimination (for distinct) is already performed + during data retrieval, and that all MIN/MAX functions are already + computed for each group. Thus all MIN/MAX functions should be + treated as regular functions, and there is no need to perform + grouping in the main execution loop. + Notice that currently loose index scan is applicable only for + single table queries, thus it is sufficient to test only the first + join_tab element of the plan for its access method. + */ + if (join_tab && join_tab->is_using_loose_index_scan()) + tmp_table_param.precomputed_group_by= + !join_tab->is_using_agg_loose_index_scan(); + /* Create a tmp table if distinct or if the sort is too complicated */ if (need_tmp) { + if (!exec_tmp_table1) + { + if (create_intermediate_table()) + DBUG_VOID_RETURN; + } + curr_tmp_table= exec_tmp_table1; + if (tmp_join) { /* - We are in a non cacheable sub query. Get the saved join structure - after optimization. + We are in a non-cacheable subquery. Get the saved join structure + after creation of temporary table. (curr_join may have been modified during last exection and we need to reset it) */ curr_join= tmp_join; } - curr_tmp_table= exec_tmp_table1; /* Copy data to the temporary table */ thd_proc_info(thd, "Copying to tmp table"); @@ -3309,6 +3190,129 @@ JOIN::exec() DBUG_VOID_RETURN; } +/** + @todo Investigate how to unify this code with the code for creating a + second temporary table, @c exec_tmp_table2. @see JOIN::exec +*/ +int +JOIN::create_intermediate_table() +{ + DBUG_ENTER("JOIN::create_intermediate_table"); + thd_proc_info(thd, "Creating tmp table"); + + init_items_ref_array(); + + tmp_table_param.hidden_field_count= (all_fields.elements - + fields_list.elements); + ORDER *tmp_group= ((!simple_group && !procedure && + !(test_flags & TEST_NO_KEY_GROUP)) ? group_list : + (ORDER*) 0); + /* + Pushing LIMIT to the temporary table creation is not applicable + when there is ORDER BY or GROUP BY or there is no GROUP BY, but + there are aggregate functions, because in all these cases we need + all result rows. + */ + ha_rows tmp_rows_limit= ((order == 0 || skip_sort_order) && + !tmp_group && + !thd->lex->current_select->with_sum_func) ? + select_limit : HA_POS_ERROR; + + if (!(exec_tmp_table1= + create_tmp_table(thd, &tmp_table_param, all_fields, + tmp_group, group_list ? 0 : select_distinct, + group_list && simple_group, + select_options, tmp_rows_limit, ""))) + DBUG_RETURN(1); + + /* + We don't have to store rows in temp table that doesn't match HAVING if: + - we are sorting the table and writing complete group rows to the + temp table. + - We are using DISTINCT without resolving the distinct as a GROUP BY + on all columns. + + If having is not handled here, it will be checked before the row + is sent to the client. + */ + if (tmp_having && + (sort_and_group || (exec_tmp_table1->distinct && !group_list))) + having= tmp_having; + + /* if group or order on first table, sort first */ + if (group_list && simple_group) + { + DBUG_PRINT("info",("Sorting for group")); + thd_proc_info(thd, "Sorting for group"); + if (create_sort_index(thd, this, group_list, + HA_POS_ERROR, HA_POS_ERROR, FALSE) || + alloc_group_fields(this, group_list) || + make_sum_func_list(all_fields, fields_list, 1) || + prepare_sum_aggregators(sum_funcs, + !join_tab->is_using_agg_loose_index_scan()) || + setup_sum_funcs(thd, sum_funcs)) + { + DBUG_RETURN(1); + } + group_list= 0; + } + else + { + if (make_sum_func_list(all_fields, fields_list, 0) || + prepare_sum_aggregators(sum_funcs, + !join_tab->is_using_agg_loose_index_scan()) || + setup_sum_funcs(thd, sum_funcs)) + { + DBUG_RETURN(1); + } + + if (!group_list && ! exec_tmp_table1->distinct && order && simple_order) + { + thd_proc_info(thd, "Sorting for order"); + if (create_sort_index(thd, this, order, + HA_POS_ERROR, HA_POS_ERROR, TRUE)) + { + DBUG_RETURN(1); + } + order= 0; + } + } + + /* + Optimize distinct when used on some of the tables + SELECT DISTINCT t1.a FROM t1,t2 WHERE t1.b=t2.b + In this case we can stop scanning t2 when we have found one t1.a + */ + + if (exec_tmp_table1->distinct) + { + table_map used_tables= thd->used_tables; + JOIN_TAB *last_join_tab= join_tab+tables-1; + do + { + if (used_tables & last_join_tab->table->map) + break; + last_join_tab->not_used_in_distinct= 1; + } while (last_join_tab-- != join_tab); + /* Optimize "select distinct b from t1 order by key_part_1 limit #" */ + if (order && skip_sort_order) + { + /* Should always succeed */ + if (test_if_skip_sort_order(&join_tab[const_tables], + order, unit->select_limit_cnt, 0, + &join_tab[const_tables].table-> + keys_in_use_for_order_by)) + order= 0; + } + } + + /* If this join belongs to an uncacheable query save the original join */ + if (select_lex->uncacheable && init_save_join_tab()) + DBUG_RETURN(-1); /* purecov: inspected */ + + DBUG_RETURN(0); +} + /** Clean up join. === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2010-09-06 17:56:05 +0000 +++ b/sql/sql_select.h 2010-09-27 10:56:33 +0000 @@ -1935,6 +1935,14 @@ public: } private: /** + Create table to be used for processing DISTINCT/ORDER BY/GROUP BY. + This creates the table referred to by @c exec_tmp_table1. + + @returns 0 on success, non-zero on failure + */ + int create_intermediate_table(); + + /** TRUE if the query contains an aggregate function but has no GROUP BY clause. */ --===============0769178670== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/oystein.grovlen@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: oystein.grovlen@stripped\ # eir2zcfpuvqqllvu # target_branch: file:///home/og136792/mysql/mysql-next-mr-wl5559/ # testament_sha1: 249ca8824dabd6985973c75340be60dd96472f13 # timestamp: 2010-09-27 12:56:56 +0200 # base_revision_id: jorgen.loland@stripped\ # lygr5tud7qmeut4u # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQ754IUABe//gFQwM8F5//// ///f6r////5gDsdnzvXaPTt715FFFdHlzc67NAvYGiQUFO2UQe7veYKcMSRpNNqnpop4o9R6TzQp o2moYmhkZqaeoDIGjRo0eU8kDImiZDINTUxMqeGqG9JqaA0NpqNAHqAAAAaBE9CEaER+qaDQDEAA AAAAAAAAAkRI0JqeJU9B6KeQnponqeoepjU000M0Rp6QAB6gAeocNDRk0aNGmhkZDCAMgBkGmgAA ZAyAJEggEAAQI0xNTxQp/qj1Nqm00JtTyTNRkYR6hozUQEQRIA5UCAWBeWFm5sD/M11Pdfpx3XvD 1F+NWcfF+xjnqpCdRjI0H9VcWpn1KBnTLm6p36InfuENy7UisatPNv6z+1dbqka7O9Fgs6FQnGIp Fkxu3dNnjiGfmzf9zlBj2uC+vZvFyQp4lPDmyWEMM7Kp3kU8Ir0tFBEzSQb87g11uuvYuUOjdZIb lPUhFiABe1eMZ3WK7t0lFv7pnoAQDvDuzqlMPBh/ywQK8TbBAuTQoyaSEXtIkNJdjNzEK+TLwt32 dCl/XPlUlPyku4u/6DUmu97F21KgbVbgZplIhm+ti/ETUGEcYSSQA2n0V7ag4DPPLF0WktcMvpFJ bAadmHWGtInVyOY5FNGtWcjoq01GbZU274+1xFM4fekOk8Eahs1Z88g29t8uA67hvYkKqF+jSJXV ad9Uo9umso3BKbVcqSZscWDQeqIdjod9VxjGLSc2lug43githaqoEKUs6wkEVcphaaImNQ9u7iYY iUSgxyTOxmGRXD/HrcqvfP+Bg+qKNXXQ6SvwiEkzmJ3fYzHkwUQ383muZdFCQjeISplg6GsT0Ti1 83pe3KkgN1I+ARbDhYEVTHmukkcZZWd5Hw2auy5NWq0YSC5Cepsn7XF1MqwkrVlbF76Sm5hc8w73 W0vjRxbDod3os4vu686GcDbjW4dcg27aagHYPPmb4PTpnfdG5OekL3ys5LuV10BoYSwdbgJpc6io iUcleemNtjqrknFGtW3q8a4NOmADA38HAfOPhVguEZWd7RySd3JmveHeD8w8sUMVe0sDXqQJZLAQ cyLIFdD40mMbQxKn0R2hhPi4extWGmTZc11oUKX/kx4/eL9naXC/vaWdx2eBEt6vmeo5ncjSxvk7 yIwl9jru0aj2vDMw31mk0gQnJ1QbClWlBkRCU6qSqRiEENMf8QbR0s1IPravx14Rq/rIxn6A1vxD 8nrYflZgyutQsgFF9a4jw+Op9Uate4+kb6kGox5qGQGTLyI9ned73vn4AA5o8AFupYeFo7QZDanL iL4Zru3uag0dE9k75c765FjvRqzIWEs6Bo3wDOz2pHWjo8I+59bN/yHnhvznqaS1IP164O1n2Ckh ew3iXIUkuPddIVOooW0oB7JU7agw/TehSF+QL7lkFDwHKSb/u7Mtk9QeNGAZT1yJAqgOYA0HHxZ2 hzWxjnpGLu7QBB1W22Bswg8prjGH27zyqrdwtFOb0zrGg5GkzEbZCv12nQSs38TBxihWfbQ259F2 JYtz6TWBATdjDUayeyiLjOGFIKAX3NmrIDHNeXhqFTGIkpgWl0ULTceiJRFm/coedBbDIIiqsQEf kwndfYHzGW986jOqSo5cu6i7F+BZzC0059UsKuckde+aMDnibFMf1WSwJ7jACga6bXXaKOE85HQ9 50fKuQTlhdlI24zNAwM1jzWvW510SsBrDRdYaKJkhbnXNk5QKfO0DHssLz2BhCaIU50vqeByvEqG Nwz8lXATwGpc2AeWljBwGslkZtMHOoWFbhqVYZvHxIxvt0YFpfnrwaWPBhkwIwO05E9jFTLF3ZSU ybc5ED29s6XTGzCDjAL5Yk69YpOrUtpqtdrByUK2oqRMKEWuFXPSL0WXP1uZc/r+EHZSDMypKPBh JnetNPLx8D9GXcSOn8vZorS+o/lOlEjK3u+Po2H0MFRFtoNB8JHr/x9/2iIixLorXAikunpIEYPH oVJTp7LMEuIc/P3AOJ5gnTf3do5BEKlB66tOIZ0pOiY9ZHXk1iA4Do/LDVLprXQxAZL+1JZ1Thc3 x5wkpZ8eZPov1EjjcZChIGwudqW08oIEAYiXd9JDEa3x6NhEwWaZGBARifLbYTwuI/QiwjYm26+p aZDtWs0EEyTFYcxSRL3WhhToIECJyexM+zgaWMVAYd4ZBh2oW6COWiKYiMmmy3fAyCrl3e+55G+Q nmCmu5BSLWQQfHBJW0gdm3BKLUwIbBoZI7UUGpT1IssBvCA8Nyqc/ovLvsQ8whhnvKJljIMyShKB DYXqpLMBlWeKN70D7jpniLznmDfJy56gsxrOIXzTBLbSQcxiOY55kHwMBTz6l5HECSDXr3SQbJ92 UgHcVHqF1EcZhmuNdMzKtGRZJTDPurTOVK8dgftmiEGq+LQmXt3lwXyCbjg+srkC2ANXlINDKlbd LY0dG0+PLs7ZpMCOY9B6Vz5/OV0VxLwZcXGCuGuB9VdyolDQg5fP01ObZd3gv7HEkuUKPA2yjWTy Phl9wXFANBB9k866hsTKV3+LTezEdN0aOGEA/kA7rc90LKIeUIUjewMhLTHhVfgVidA97hwc5BgL zTCrBtiG0mGGrD/ywqLSvZDdjRJjYWtpuII7xHaSn2cvu2+j9HeHFXLK4NIUtpYiQVlDWSYZ/Urm S9Dw7iPIFsawQXgei3GKb6FsVeGVFrAzmmDxINfBXI2adeaFKyS/P7bvi2/koVd9ZJwWWmASQNJp mBjybBqABgc0yCRQwoIYKTzPSZKFhPWrLGNjG2qMQuB0QrT5VJvyKf51tMDXn4qKZmk0TAz4omKR mPYQg9prXOUUVRpBTrPmRuYG2ypk7RB2Vz6Z4yebQRdFMYu3lJYXQyo7SgVvAp51po93KrtHXR58 3ry4nVsNRRrQv3qMbREwL8fmdvdHuQbBBz9B1fJYAa4thdISQba8cMNMNHJTIF52BiwOxuIWKOkv 10Ar2UmZjRWzNPvYRfLa31mnco0py30SnBMgMIRRbmsIGj1EfTydVsbK5I6TDAA0D/PAN9otiPDh 32TeNf3Kqp7qrlzKeYrj9/4G7xGTRT/7I7VvFQ4MCujokXRHYAxeM6oJB7lrvEKG1dJosWTReElC U8bOtaj1C9YmI2NNh2dPwopffHxIMsZz3zqcDdQiDgURwiqueUQZt4kpB5WIqtx4eAKBc5nCVVtp HnmLbFSXSAwz8z3gd6u5qtOe35taXnE0Gq2y16DYf6twoHDbL8yxss9Flig18qmQmg0Bpy1NRVwS Qcc80k+rzuEURYUS3fTC3105GmRqa1++riJDVRYBg7wPZ4dVLQsQxZ1rA3q9Z0mLk4T+cMyOAlyE big9TGauQbaUtTA0Du4bqrdnwkP2qIX3m8Owb+pkYlf1xU3oyXEr0XtM90W4FnUYWStICwRxw7A9 Nnt0/z5qhtyQeRqYxgcWAQzehQQIyUBEMJf5VEGm+FzEVSs1Lq4fFTWFumgg47mZ2jtaWvEDRb4h fCvRYUn9NsuODgCvd2t+Ae6oqNCadqRj0/Vm3WlHYWqEGddCz1S1BCXtaDaVuzehatVG8YQjBXBb QMSFp6lOv6YpYQ1dKEyBksiYZPipBThlEDHYgoEKIQNxCAxHmRRigQwo6TU0NDQOKJvCYgJRTHdF ChZYQmGt5i0m2jV9CtkNYroZg1hMCENhoVOGoDp1ZKoMHUcS8CgTlYog6yaEwdIZkyPhEFwV1VFA l3phGXFeQmYYS2FlPHhB3VYKwoBhltg5it6jmo1tibB2R0IUI9wT9g1YD8vk7Z2XcAshEjelwTwk tCUbk4+vkW7dghYTlz/c26JGDsYvqOVM2cu+u2LPAO3KbUudsCAhDQM6lBAlo3h2mEhhJk1ZtoXe 8uPjqVLUWFICJYW5FankdBmX4rs/6y9BK1cgOBNagD6VrA/UGo8ALz1tNibbZsahkNEJZNbwH6B6 qFTqYgqFzgJaS2KSxtTDcM4y3l4AXCyGaF+G7pknqVEwtJDj3eWVmaBWMHlTHwwigG6U5hztIrvC vn06xC8zQZKxjL8sIW0bE6MVGjFxIRD3FGzTVNZrc5vkpSA10jsMbFR2eWLit7jrmw8DMYgvILjU 60buiy1QGFCiyvuRetZraaHfpnGmsczEYWB7OOUkS7ni8jZYr0GmDh41KIR0bLFzkRCwQ8vFQNK7 TorxlYQtR6aYKqxxG0ODOL1UrIHWm7AoyIhBBtC8h3iImIwwQxSxSgyJ69d1suY2TBAZUKTUMFQv 6jzkfIcTpKUHqRkCAMIDRMIGKjRkj9n0YBdWtcciyaWDoUgKIlTTALDKutJakDAZyb8EiN1hD6WF 1TWwwaQTlGa2qCMr8GGbv6LALqbxhtsJjkiYHTBd8YAIaPFqjNKVkfMXZjfo5niggRprdUIWt12A 9Ziau38+ma4CP0QbWJd+nFiyMVXbE3pelhE/qBlrPnSmtZ5XL337g+AW/iVbOR1yBdcL27YEUga8 RiRgMytlbLpcjUHwN/LAfPNL9Tmj8eY4BksNRyUgKUtxCU2WB4lJratRuK0RCgCgEqXWWjea95Na HudRYK1I9GJdy4qBcNefMdMiJyRFaUmnX/BVCkvU+OSC9qn0AXr2gtN3wW9aFrDcLz8//QJgQgYs qn/xdyRThQkA754IUA== --===============0769178670==--