From: Tor Didriksen Date: December 9 2010 12:31pm Subject: bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258) Bug#58782 List-Archive: http://lists.mysql.com/commits/126411 X-Bug: 58782 Message-Id: <20101209123120.B6301ADC@atum07.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============2203550231898757029==" --===============2203550231898757029== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/didrik/repo/next-mr-opt-team-wl1393-merge/ based on revid:tor.didriksen@stripped 3258 Tor Didriksen 2010-12-09 Bug #58782 Missing rows with SELECT .. WHERE .. IN subquery with full GROUP BY and no aggr The GROUP BY is optimized away earlier than any example queries seen so far: In JOIN::optimize() We have found that grouping can be removed since groups correspond to only one row anyway, but we still have to guarantee correct result order. The line below effectively rewrites the query from GROUP BY to ORDER BY . So, the handling of GROUP BY to disable PQ in JOIN::exec does not catch it. This is a DEPENDENT SUBQUERY, which is executed once for each outer row. This means we have to return all rows each time we do filesort, i.e. we must set limit == HA_POS_ERROR, and disable PQ. @ mysql-test/r/group_by.result New test case. @ mysql-test/r/order_by_icp_mrr.result New (correct) result. @ mysql-test/r/order_by_none.result New (correct) result. @ mysql-test/t/group_by.test New test case. @ sql/sql_select.cc If the query as a GROUP BY, then remember that fact in JOIN::prepare. Use this fact in JOIN::exec, to disable PQ. @ sql/sql_select.h Rename group to has_group_by, for readability, add had_group_by. modified: mysql-test/r/group_by.result mysql-test/r/order_by_icp_mrr.result mysql-test/r/order_by_none.result mysql-test/t/group_by.test sql/sql_select.cc sql/sql_select.h === modified file 'mysql-test/r/group_by.result' --- a/mysql-test/r/group_by.result 2010-12-06 13:12:51 +0000 +++ b/mysql-test/r/group_by.result 2010-12-09 12:31:15 +0000 @@ -1886,3 +1886,48 @@ f1 MIN(f2) MAX(f2) 4 00:25:00 00:25:00 DROP TABLE t1; #End of test#49771 +# +# Bug #58782 +# Missing rows with SELECT .. WHERE .. IN subquery +# with full GROUP BY and no aggr +# +CREATE TABLE t1 ( +pk INT NOT NULL, +col_int_nokey INT, +PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (10,7); +INSERT INTO t1 VALUES (11,1); +INSERT INTO t1 VALUES (12,5); +INSERT INTO t1 VALUES (13,3); +SELECT pk AS field1, col_int_nokey AS field2 +FROM t1 +WHERE col_int_nokey > 0 +GROUP BY field1, field2; +field1 field2 +10 7 +11 1 +12 5 +13 3 +CREATE TABLE where_subselect +SELECT pk AS field1, col_int_nokey AS field2 +FROM t1 +WHERE col_int_nokey > 0 +GROUP BY field1, field2 +; +SELECT * +FROM where_subselect +WHERE (field1, field2) IN ( +SELECT pk AS field1, col_int_nokey AS field2 +FROM t1 +WHERE col_int_nokey > 0 +GROUP BY field1, field2 +); +field1 field2 +10 7 +11 1 +12 5 +13 3 +DROP TABLE t1; +DROP TABLE where_subselect; +# End of Bug #58782 === modified file 'mysql-test/r/order_by_icp_mrr.result' --- a/mysql-test/r/order_by_icp_mrr.result 2010-12-09 11:54:39 +0000 +++ b/mysql-test/r/order_by_icp_mrr.result 2010-12-09 12:31:15 +0000 @@ -2384,6 +2384,8 @@ GROUP BY field1, field2 ); field1 field2 27 27 +28 28 +29 29 DROP TABLE t1; DROP TABLE where_subselect; # End of Bug #58761 === modified file 'mysql-test/r/order_by_none.result' --- a/mysql-test/r/order_by_none.result 2010-12-09 11:54:39 +0000 +++ b/mysql-test/r/order_by_none.result 2010-12-09 12:31:15 +0000 @@ -2383,6 +2383,8 @@ GROUP BY field1, field2 ); field1 field2 27 27 +28 28 +29 29 DROP TABLE t1; DROP TABLE where_subselect; # End of Bug #58761 === modified file 'mysql-test/t/group_by.test' --- a/mysql-test/t/group_by.test 2010-12-06 13:12:51 +0000 +++ b/mysql-test/t/group_by.test 2010-12-09 12:31:15 +0000 @@ -1273,3 +1273,52 @@ SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP DROP TABLE t1; --echo #End of test#49771 + +--echo # +--echo # Bug #58782 +--echo # Missing rows with SELECT .. WHERE .. IN subquery +--echo # with full GROUP BY and no aggr +--echo # + +CREATE TABLE t1 ( + pk INT NOT NULL, + col_int_nokey INT, + PRIMARY KEY (pk) +); + +INSERT INTO t1 VALUES (10,7); +INSERT INTO t1 VALUES (11,1); +INSERT INTO t1 VALUES (12,5); +INSERT INTO t1 VALUES (13,3); + +## original query: + +SELECT pk AS field1, col_int_nokey AS field2 +FROM t1 +WHERE col_int_nokey > 0 +GROUP BY field1, field2; + +## store query results in a new table: + +CREATE TABLE where_subselect + SELECT pk AS field1, col_int_nokey AS field2 + FROM t1 + WHERE col_int_nokey > 0 + GROUP BY field1, field2 +; + +## query the new table and compare to original using WHERE ... IN(): + +SELECT * +FROM where_subselect +WHERE (field1, field2) IN ( + SELECT pk AS field1, col_int_nokey AS field2 + FROM t1 + WHERE col_int_nokey > 0 + GROUP BY field1, field2 +); + +DROP TABLE t1; +DROP TABLE where_subselect; + +--echo # End of Bug #58782 === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-12-09 11:54:39 +0000 +++ b/sql/sql_select.cc 2010-12-09 12:31:15 +0000 @@ -744,7 +744,8 @@ JOIN::prepare(Item ***rref_pointer_array /* Init join struct */ count_field_types(select_lex, &tmp_table_param, all_fields, 0); ref_pointer_array_size= all_fields.elements*sizeof(Item*); - this->group= group_list != 0; + this->has_group_by= group_list != 0; + this->had_group_by= this->has_group_by; unit= unit_arg; if (tmp_table_param.sum_func_count && !group_list) @@ -2118,7 +2119,7 @@ JOIN::optimize() join_tab->table->keys_in_use_for_order_by= join_tab->table->keys_in_use_for_group_by; group_list= 0; - group= 0; + has_group_by= false; } if (select_distinct && list_contains_unique_index(join_tab[const_tables].table, @@ -2183,7 +2184,8 @@ JOIN::optimize() } order=0; } - group=1; // For end_write_group + has_group_by= true; // For end_write_group + had_group_by= true; } else group_list= 0; @@ -2206,7 +2208,7 @@ JOIN::optimize() if (old_group_list && !group_list) select_distinct= 0; } - if (!group_list && group) + if (!group_list && has_group_by) { order=0; // The output has only one row simple_order=1; @@ -2740,8 +2742,6 @@ JOIN::exec() int tmp_error; DBUG_ENTER("JOIN::exec"); - const bool has_group_by= this->group; - thd_proc_info(thd, "executing"); error= 0; if (procedure) @@ -3127,7 +3127,7 @@ JOIN::exec() count_field_types(select_lex, &curr_join->tmp_table_param, *curr_all_fields, 0); - if (curr_join->group || curr_join->implicit_grouping || + if (curr_join->has_group_by || curr_join->implicit_grouping || curr_join->tmp_table_param.sum_func_count || (procedure && (procedure->flags & PROC_GROUP))) { @@ -3220,7 +3220,7 @@ JOIN::exec() } } { - if (group) + if (has_group_by) curr_join->m_select_limit= HA_POS_ERROR; else { @@ -3270,17 +3270,17 @@ JOIN::exec() unit->select_limit_cnt == 1 (we only need one row in the result set) */ const ha_rows filesort_limit_arg= - (has_group_by || curr_join->tables > 1) + (had_group_by || curr_join->tables > 1) ? curr_join->m_select_limit : unit->select_limit_cnt; const ha_rows select_limit_arg= select_options & OPTION_FOUND_ROWS ? HA_POS_ERROR : unit->select_limit_cnt; - DBUG_PRINT("info", ("has_group_by %d " + DBUG_PRINT("info", ("had_group_by %d " "curr_join->tables %d " "curr_join->m_select_limit %d " "unit->select_limit_cnt %d", - has_group_by, + had_group_by, curr_join->tables, (int) curr_join->m_select_limit, (int) unit->select_limit_cnt)); @@ -9089,7 +9089,7 @@ JOIN::make_simple_join(JOIN *parent, TAB tmp_table_param.copy_field= tmp_table_param.copy_field_end=0; first_record= sort_and_group=0; send_records= (ha_rows) 0; - group= 0; + has_group_by= 0; row_limit= unit->select_limit_cnt; do_send_rows= row_limit ? 1 : 0; @@ -18525,7 +18525,7 @@ end_send_group(JOIN *join, JOIN_TAB *joi (idx=test_if_item_cache_changed(join->group_fields)) >= 0) { if (join->first_record || - (end_of_records && !join->group && !join->group_optimized_away)) + (end_of_records && !join->has_group_by && !join->group_optimized_away)) { if (join->procedure) join->procedure->end_group(); @@ -18821,7 +18821,7 @@ end_write_group(JOIN *join, JOIN_TAB *jo if (!join->first_record || end_of_records || (idx=test_if_item_cache_changed(join->group_fields)) >= 0) { - if (join->first_record || (end_of_records && !join->group)) + if (join->first_record || (end_of_records && !join->has_group_by)) { if (join->procedure) join->procedure->end_group(); @@ -21216,7 +21216,10 @@ calc_group_buffer(JOIN *join,ORDER *grou uint key_length=0, parts=0, null_parts=0; if (group) - join->group= 1; + { + join->has_group_by= true; + join->had_group_by= true; + } for (; group ; group=group->next) { Item *group_item= *group->item; @@ -23622,7 +23625,7 @@ test_if_cheaper_ordering(const JOIN_TAB bool is_best_covering= FALSE; double fanout= 1; ha_rows table_records= table->file->stats.records; - bool group= join && join->group && order == join->group_list; + bool group= join && join->has_group_by && order == join->group_list; ha_rows ref_key_quick_rows= HA_POS_ERROR; /* === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2010-12-06 15:21:08 +0000 +++ b/sql/sql_select.h 2010-12-09 12:31:15 +0000 @@ -1638,7 +1638,8 @@ public: */ bool sort_and_group; bool first_record,full_join, no_field_update; - bool group; /**< If query contains GROUP BY clause */ + bool has_group_by; ///< If query contains GROUP BY clause. + bool had_group_by; ///< If query contained GROUP BY before optimizations. bool do_send_rows; table_map const_table_map,found_const_table_map; /* @@ -1978,7 +1979,7 @@ public: JOIN_TAB *get_sort_by_join_tab() { return (!sort_by_table || skip_sort_order || - ((group || tmp_table_param.sum_func_count) && !group_list)) ? + ((has_group_by || tmp_table_param.sum_func_count) && !group_list)) ? NULL : join_tab+const_tables; } private: --===============2203550231898757029== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/tor.didriksen@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: tor.didriksen@stripped\ # fduno7x6gpt9n2tp # target_branch: file:///export/home/didrik/repo/next-mr-opt-team-\ # wl1393-merge/ # testament_sha1: f1c6fc4a63da6d9c35b2eda239ea599a57c4775d # timestamp: 2010-12-09 13:31:20 +0100 # source_branch: bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-\ # next-mr-opt-team/ # base_revision_id: tor.didriksen@stripped\ # 5o7wqmgguqthpzet # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWXUlRAoAB6h/gHRwIQB7d/// ////oL////5gDqfG++e7xYvdexyABoBV3ve6S111Sj3bqOnLgKqS7BgCSSMhFNpiMNGVNp6mUHhT ZTJpo9TQeoAA9QaaGgklJkybamk9KaZT0ajCAG1PKA2iMgAADIZAc0xGRk0yaAZDRkMmQAAAyNMj QMIZAwoQIyTKep7Eo0bT0p6hoGgDRkAAAADQIpEJiCZPUwqfpqbTTR6k2qHlNNPSNNBoDRiAANAk iAQCaaTJoNEmCnqek9EeptAQyNAAAGaky9TCV84DEUFBQPKk9ozfqiGNr+ErLtLFSdubraJpTriK apoM233dP+/Nw+Ds88c3W5Hv3ZMG51QIigtZnvL8HPZSwj7H7O3vhLp6E+g04TaEmPiLyxA8DJOu yQ7vUpNX98b24s2RcYhvCUUvHfCG/aENzsoGhYiCL2QVZBOhsLeTxfMg+6Lyvs+fXJt85VljONdK 5pB7xzQOTgIpX5GBSYSNbSG5si+6H2G5ynmveyLNyaPWB7CfD6CVUXn4lDDHIe4N4/uPQMRBEMQM QkAEjteEfkk8iVc/Nhri8FrQhGUGtaEaSWHsdEzgqIsBufXENzCaJ2JNhxlSYCmlyqnKRaWfJZvU Cp7PUKIX12sJUGg6ZnGU2fzOsth5z1q3BZNIWi0ZAPUa4Yy4yUBnireZMT9g6AwxHaAZdy+a5HO6 meGah69eUIzsqXd6lmKIpVsVw9ZiSj6uk6ZweBjzt8IsxMZAwMF4os/O0G4p++10K5v7Iawozkv8 0My7Nry+Vpt+ITeFyixxbOUa9+mG14u4W6x4jFd9OyO7xM7HSmopqksOCUKX0rkF0jlAY6IWgole bDrW3rMWVsm1Nqoll8LEwX3V6r+8XBiZ6tXi1PZjURzMjVkO9yaZdbzx67YZqGOVmr8mEWXms8Pg h3cQiVkXRkkXedmGZmbAnOZMLQkyFo2cWeNI2MmrORxmIoj1FzDDlHEg0J8KxlZuE87lUuVmr0T8 DViTZc+2045hMxEUP4zM1S6GaHY+VB7jePB72mEghxvpGl1AFl41S3c1YesMtj23rlIvFVV0DvWt luyu91fa3ySj+UKsoeROwcuigrGBGoR7ZpIJ4QhiUsc083otJBA1YOs0mWBmD00VA3CgHiRJN6Fq JaDjtoQ1A3g7C5yFAnIUhMgURVGQKAmj3CcXRPiRIipiSVGjjL9DKQpMNIg4wkXGQqIJLI5Ei89p 6MMKzMxzwPIXgcBg/YMnKbnCCzhutICe1irUQEqS46xLeJIQXQFMJ4vsD7cCis+eTLlAICcxweTk C+gTgwqgDARulQTSx07PilYWDlxiGlAOemyXfpgUsQAGYW4TEYcC8kJkemIuY+J5b1Mx2GdbeMLC iZYHWSgdxgJ945gLsYFyLYKhkYYDDuMw4wwxli40dXiQiTM0IOF0FRkkdj3BJiPB4wcbiMKNipMi ATSuY6DOMdhsWXKhGByioYFCbnYP0NCmIpmmZa+4sXc5Hxbjqw4UQbG86vUTPuXA7lAc8oB1cry2 pxprZ+t3e2vwFxVasnOQrADlwjnYV5Dr7TO0133E2vLzoMamOhjRA5TAVSe61Izkcj4PgzNDfqeB svnknWu4T1rDMYg+gdzBCu8eBTQTpWN98SFzaeI+psULIqeJ4jNxSqaGBnmHA0DwJH3qsbLrPoU1 5DuU+s6GO1tJmG18H26DgEokjqjAszZkBaHEvpEXmQRjTbmfMjlqKGUC4964FTEYyJTPgWRiEBzj Majvg4A2o3WaKbWlZIpKIyjb0CIUyBmgW7YXqKZWOMFTdpq5A8GIHnqUIac3Ay03E8hHLU5BieQV IHEA3HlWKmaC6Fs6dUG1x6GqmLiPvFLiqXH4xpkTrXFRiWTmcMoG4Y8y9C6vCaMzdEaxYXDyPvPp 0gBDmetYEu0xLsSpukvMpLiJGZyGW7I55SaTMWvlAW+C7+Lcid9VIHaViw/hE0JydMXHZB2o2pYi TjWaOonGaYaBqTIQpuhwqX5amREgQMCw97l0I3aTMrSE4ojm4wBzOIkTvnAxnFowCERUKxPjNcBX PWCNtvVcRN7zfLjoKI/kJAuM/d08zOKLWOADRCORiEoLRlDLuBvtVFMSYExbe060K0AcUIWaCVaF 1HuQRs0BGXrlrA3D7sIJQrEiZ1OpTjfHBy5yVH4cTHYbIxY1UCgENSCKQdoysObGMl907+0WeAR8 wH+gecoB84fzj5EMzIZmb7gf39SAEjBVIESRtyP2PBzhWAYXpAYHmSPcke91aC/04FqD+kAcTAMz JlBveCwHF/EJAOQYFrBxYntA3pFjYCwHsMDAlMiQkoUkWBcB/wYkaLs5NXCraF2tAzeqylMYF9wR X3gdQX+9JTQajbmAcFgYoYMwHwhgiwEiI0BMpATBwJwYro9ecDLgLVcFYEEDAcKcZGMgWLcWTp7E A7xPl6dtPUxAREREBBER2cEoAkMBf8YtjNC5BISmipCMQ+Yt+iwiIKQmnTgvkKAQGFMwGpd9Q8Ze 4DApjjNVBiPLfPDORrUgsIksQhXiiQEtW0ilNBFbKqscD6pX3VklP7cNZVRCkIG7a7D62QEkJUlM TuGc8Trl5TwUmb0LE8gXjAbPKRlIUmx9Zbh9q2JSBiI50Ilx3LvXI+s4DNyyITX0rGKhanle2Blv NYpiYRN7wFcmWbKTlI1mHWSMTwNoBvIFiPAYieWZuDiYH7gax+mDUKHCfUmkar8pXO0YnGKQjCOf EI3TamPURprTU0hYQkrkz0oQ0+9yZs7oYY9EuQugBp2byAlwGPE6zVdwwYAedeQzRXLEDoXlbIVO AEFYeBXpQojiOIkcZRAmE1iPWul2NztctBmUbC4l9tQBr6ZBWHpgLx/h53p9U3ZsKOPwul3XSa7t tKWzkBCqhhanp2Y7GGXebdhINlNSDsT9o3ceFWORAcjjn5w3i2FzybNkO9AVUDcBZnaPznaOR+Ev Mz8ANJxAIoAxldkzY9KDQhcM0LmsMIbmfdnV/vFwt1ZDdWZb0+PdzNL9HWhAWACO9TQR78jhC1ZS 3BKVMg6ccMIHbFxGAvFZYvuYR4d19sE8XqlESZnAK3jISl7zQQTy9pFFaOwW3CGJ+d4+cHwS2m01 l56/MVIn69YTwIxZtQv7DxPMvKAYJaDaGqWfceYwE3ED6iBP2Ay0jAzXqyJmdjQYYKo3s2QT9/S8 Hub0sXn5m15k6GCIQkw7h0mV6+p5TpdohtzNrdnIB3Uv3SkympR6TlJTgrH2zCO8PEBMG0vpAmvD jq46+FVRZmabUn7ICaYRPqaRwse6HSxKkh4hmyHW3oMiINut8rN3fk9r4Z7r+Zd0IH19wjPydQgY K8Wo5fZYtKLyNYj11hZAeDNXQyRkOLGgkvT496GmDk6F0Sx8fFWkQWbdkwpmV1XEHyW6GeFU5UKN 8gq+tYU8sCYh4lpWFblxtHAzr7opFzf7zsAzwRkOcX4qGtCppfEo8cOA42I2xFwGC5NRBLJQ8GNL CVRGk1IAOjHEMRbmmITCgwJogDPReZT7eou2CmvWnfwhhB0leGr9vgvRTu2EKw1M1KODCzh1cEpG Qm62DE+dYfGWv0jclCuJhvRorg7VpYJhUwY+Vi68iQSMDIA9qcURCaI9wt8Y0+FMj7CUpIKKTwEz UMYCMYxVAB1uSCG4yKWnoq3pSEokoKDAdE6vNlI9Sfh6RN1piva4S+zmZioAR0v9Bbetii9gJhNz vFsgFuQwboqS/x8GrAXthqs2Rkm+K70RqEfeITIL65fL8eDOzM7yK0oOoB27SW3D00pqCRbtl2Qm ptOghGnc5BCgULEiize6p77qRgWartNCxNL1ew8yqZcKQLxwuyAGDZOVF9K9hxOHQ8djgcgh5GAr G3iMY8e4gYjiaXPhb4k3aWUxHWmxngYO5rkJS1KBPDEkCCgcNjUyKElj7lGLoGGTHLm4FUVax6Eh al8F1m/0nLhdpvBIxFwRVXB6Gza4PSoIgwMNNaD5Uzw0gXMVRBVQyZs63nbCxWuip2a1hwtvp4rD fm1hyCQMCQb49FkyBhRWbCBEHeygDNnNG1sB3FjbbbbeGJ8IQIwUndPEJJYIYyZ2SZl4MrWQMGQo AHhHntzDedV7nZAiWaONE+sR8voUlBMsp+Tsji1hG+oaiDRd/BWAKTbddgCIhgCa4x90iNKrVFbX 66KtqR66YnUFgsDwBP+RSFiKIL1C85DlZcs6dZwCm+skEkHI7EmE/usI4LzgFnqHHjmBm0QHKQ6S XVhQ21hpdKn1Je9YYECNFYBAKTjS2jsuZ3WdVGDAXOtraLqHPwHQhldDJqb5FZARjLTqyhawvjYH bkJj022hs+B1uxytjc4XCAWh7XQ52YthcyFk3WyLYiClKINcpThSNJJlCJzNLJ0trQ8oFvI25rWB MI4sdjyWOl8LmdX9JA/+LuSKcKEg6kqIFA== --===============2203550231898757029==--