From: Evgeny Potemkin Date: February 12 2010 8:51am Subject: bzr commit into mysql-5.5-trunk-bugfixing branch (epotemkin:2943) Bug#50539 WL#3220 List-Archive: http://lists.mysql.com/commits/100081 X-Bug: 50539 Message-Id: <0KXQ00LZV0MVTL00@fe-emea-09.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="Boundary_(ID_NLEkEFb5U5jXVw1dT+upoA)" --Boundary_(ID_NLEkEFb5U5jXVw1dT+upoA) MIME-version: 1.0 Content-type: text/plain; CHARSET=US-ASCII Content-transfer-encoding: 7BIT Content-disposition: inline #At file:///work/bzrroot/50539-bug-trunk-bugfixing/ based on revid:dao-gang.qu@stripped 2943 Evgeny Potemkin 2010-02-12 Bug#50539: Wrong result when loose index scan is used for an aggregate function with distinct. Loose index scan is used to find MIN/MAX values using appropriate index and thus allow to avoid grouping. For each found row it updates non-aggregated fields with values from row with found MIN/MAX value. Without loose index scan non-aggregated fields are copied by end_send_group function. With loose index scan there is no need in end_send_group and end_send is used instead. Non-aggregated fields still need to be copied and this was wrongly implemented in QUICK_GROUP_MIN_MAX_SELECT::get_next. WL#3220 added a case when loose index scan can be used with end_send_group to optimize calculation of aggregate functions with distinct. In this case the row found by QUICK_GROUP_MIN_MAX_SELECT::get_next might belong to a next group and copying it will produce wrong result. Update of non-aggregated fields is moved to the end_send function from QUICK_GROUP_MIN_MAX_SELECT::get_next. @ mysql-test/r/group_min_max.result Added a test case for the bug#50539. @ mysql-test/t/group_min_max.test Added a test case for the bug#50539. @ sql/opt_range.cc Bug#50539: Wrong result when loose index scan is used for an aggregate function with distinct. Update of non-aggregated fields is moved to the end_send function from QUICK_GROUP_MIN_MAX_SELECT::get_next. @ sql/sql_select.cc Bug#50539: Wrong result when loose index scan is used for an aggregate function with distinct. Update of non-aggregated fields is moved to the end_send function from QUICK_GROUP_MIN_MAX_SELECT::get_next. modified: mysql-test/r/group_min_max.result mysql-test/t/group_min_max.test sql/opt_range.cc sql/sql_select.cc === modified file 'mysql-test/r/group_min_max.result' --- a/mysql-test/r/group_min_max.result 2009-12-11 09:39:38 +0000 +++ b/mysql-test/r/group_min_max.result 2010-02-12 08:51:52 +0000 @@ -2686,7 +2686,7 @@ a c COUNT(DISTINCT c, a, b) 1 1 1 1 1 1 1 1 1 -2 1 1 +1 1 1 2 1 1 2 1 1 2 1 1 @@ -2714,7 +2714,7 @@ id select_type table type possible_keys 1 SIMPLE t2 range NULL a 10 NULL 9 Using index for group-by SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; a COUNT(DISTINCT b) SUM(DISTINCT b) -2 8 36 +1 8 36 2 8 36 EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra @@ -2761,7 +2761,7 @@ SELECT 42 * (a + c + COUNT(DISTINCT c, a 126 126 126 -168 +126 168 168 168 @@ -2779,3 +2779,24 @@ SELECT (SUM(DISTINCT a) + MAX(b)) FROM t 10 DROP TABLE t1,t2; # end of WL#3220 tests +# +# Bug#50539: Wrong result when loose index scan is used for an aggregate +# function with distinct +# +CREATE TABLE t1 ( +f1 int(11) NOT NULL DEFAULT '0', +f2 char(1) NOT NULL DEFAULT '', +PRIMARY KEY (f1,f2) +) ; +insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'), +(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D'); +SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; +f1 COUNT(DISTINCT f2) +1 3 +2 1 +3 4 +explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL PRIMARY 5 NULL 9 Using index for group-by (scanning) +drop table t1; +# End of test#50539. === modified file 'mysql-test/t/group_min_max.test' --- a/mysql-test/t/group_min_max.test 2009-12-11 09:39:38 +0000 +++ b/mysql-test/t/group_min_max.test 2010-02-12 08:51:52 +0000 @@ -1166,3 +1166,22 @@ SELECT (SUM(DISTINCT a) + MAX(b)) FROM t DROP TABLE t1,t2; --echo # end of WL#3220 tests + +--echo # +--echo # Bug#50539: Wrong result when loose index scan is used for an aggregate +--echo # function with distinct +--echo # +CREATE TABLE t1 ( + f1 int(11) NOT NULL DEFAULT '0', + f2 char(1) NOT NULL DEFAULT '', + PRIMARY KEY (f1,f2) +) ; +insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'), +(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D'); + +SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; +explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; + +drop table t1; +--echo # End of test#50539. + === modified file 'sql/opt_range.cc' --- a/sql/opt_range.cc 2009-12-11 09:39:38 +0000 +++ b/sql/opt_range.cc 2010-02-12 08:51:52 +0000 @@ -10959,17 +10959,7 @@ int QUICK_GROUP_MIN_MAX_SELECT::get_next } while ((result == HA_ERR_KEY_NOT_FOUND || result == HA_ERR_END_OF_FILE) && is_last_prefix != 0); - if (result == 0) - { - /* - Partially mimic the behavior of end_select_send. Copy the - field data from Item_field::field into Item_field::result_field - of each non-aggregated field (the group fields, and optionally - other fields in non-ANSI SQL mode). - */ - copy_fields(&join->tmp_table_param); - } - else if (result == HA_ERR_KEY_NOT_FOUND) + if (result == HA_ERR_KEY_NOT_FOUND) result= HA_ERR_END_OF_FILE; DBUG_RETURN(result); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-02-03 17:22:58 +0000 +++ b/sql/sql_select.cc 2010-02-12 08:51:52 +0000 @@ -12255,6 +12255,12 @@ end_send(JOIN *join, JOIN_TAB *join_tab if (!end_of_records) { int error; + if (join->tables && + join->join_tab->is_using_loose_index_scan()) + { + /* Copy non-aggregated fields when loose index scan is used. */ + copy_fields(&join->tmp_table_param); + } if (join->having && join->having->val_int() == 0) DBUG_RETURN(NESTED_LOOP_OK); // Didn't match having error=0; --Boundary_(ID_NLEkEFb5U5jXVw1dT+upoA) MIME-version: 1.0 Content-type: text/bzr-bundle; CHARSET=US-ASCII; name="bzr/epotemkin@stripped" Content-transfer-encoding: 7BIT Content-disposition: inline; filename="bzr/epotemkin@stripped" # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: epotemkin@stripped # target_branch: file:///work/bzrroot/50539-bug-trunk-bugfixing/ # testament_sha1: 89555fd5a15de9ad3ed107be92c7cef5b2557002 # timestamp: 2010-02-12 11:51:58 +0300 # base_revision_id: dao-gang.qu@stripped\ # m1550l172uuzwbtc # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWVreZpgABXTfgFTwWff//3/v /uC////6YAunz0DbfdshQAHDNPpqdFNVSVbC2tqyANEaJPSaJtRhqNDRtR5TaanpAPUDENAAAaHM JoDQGjRhGgxGmJkxNBhGgZAMmA0qaDRiABiAAZAaaNBo0DQAGgAkKggTAg0xJp6hpsUaNBkeoA00 A0aGgCKRGkDU9GpshMU1PaU9Gp5RiDEGgGgAA9QJJAQ0BGhpNMgnpMim2o0aYUBpo8oepo2p6jym lUICyLR3bnMug/bV6kTYZ+MTzzZsTBsurxLlOTREk1889hAsvV7ys930Iq7oFZ55bWGY4R9pM6+K oia1rXmn4nGeoURKIGFjM7odN1ZGV36qdIDmGQiq/qj9zZVwtDE+WrHq46b22d+qUjCbHZ1334Gt BkUcIGS0GCaOrn8AmJaxiNJOE0b1Bxo6Axg2222xW+lBmpre0yPdY2/lM0iMNGDnDO4SfFpVS6iz 3hoZh6vQDyQa8Q9rtxxIhvCHHebl4kPYirkxtkjjy+aO6X0d+sayrfzKhnxgfs4kIkvkK161Rhrz DaDnIqFKYXPRi8/55Sz6NasqlO911rlDUwh3A1ltajYiTFtTkp2tNWFYYf9ziVbVN1yuMbAruE4K mJS8dYinA+IYEzeYlCgF5JYDyV+vBGq9K1j55tsHwihzUQ6dTot5xIMO6qhKW0wCoJzxKqupAR/b hRWAgSlR6aERDf2gYM2PivmVLlq4OSLnPefAhCEPic+BusXKMFm7FBwkCBgOMhheKGFZ+HpXtzmP DoDXiks2/dRwWEziVEGQgKhJDBBlTO9QxcudQ9868H5GFytYl3p6O0ghYIyrMBSuAKQpxQwlAysC 7egD4AfeYtUPZbMjMGTNXJJgVS/AR7vy4o/Qn7LAwp8LWBzeITnpxzimrLhXyK5eKKVjBwQIQZjI bK5iH0Gw8RQbOspJgZ6Gyo84lRYeooTgaxbGbkGFBDWlZ9+YKil3fBjqbN+u9k5kSsiP8ww4Khi2 BcMQBhhywAOtgXtsGgq1nUUVypENUE+CSoorCzHbfdSyHpBwmr2IdUC8F5VvLO6qspkdKkpPP4dF UdplaKN5S3TeMtSKjpPANh5xyFcXfUFvu43bJhv1Y6kOJg5AY425iEHnqbyAbTZJqV5pFzVGs9y4 zGstVkip7PXNr3WCeDtx1fxmleaERj3VmAPX1Ukb/JfvsjoBkWCeN2TvD8mFAhWSNeZ1q7eKJuO1 5yWl2CHpkQm4ePHjjElpyHrkeM7VdkFkMdeDLoGE5HS/gXXnMJhUV7XyLR6o+B1h0m4yReXjvBVf Uis05lnnjjuP6cUHBdFAW4bYCzd27RGoiqXmhmeWG8v2lyeROJLGQ9ZlBVGZkUPuqHB2mB6GMkcp +xdyqwVzU1vu2kmMZixID6thqB8N0eVY3k1UIpiAqQsCw0Id/etDmphQrweznM/U1Iq5woFozogX OmvvGVPgyrJLqr1ztKEComeCnMlMiG+eWMBT2EwkWrI+xblsiLns36WrYrbnRH8hxAVxM4UOSiC0 haWTiWDiZA1XdnY81gso5TyGBzJgfat705PIDY7yAEJm6QmhKhUi8vvzLCQ4tNSLC1l7Uai4UC8L KIi8GRfF4qwstFNN+sruJWvJW2NrbM53Fl0xBpa9dJx2Y7BCLNMekGQGgdzJrRo4KKpQ7FMp75Qy lC1hxLkFvnMvcdARL79krDGrrYNj9hiC1khYLAdKNVIWSwHpQ0UVlEDWjnwDOosxBzB+0H+6laMt 68FdSEJHgmdyYlqQmdKNn4JaP/ft6C1C0TV4Bi9ZnTQQkwKTCckvJhUkXhinHSk0JhqqnbBDhyaR gqg+iDDpVJ1DXopejWGQXINKQlhmMUr/5KDUE1c7miyISpdFpBrCU9m5K8yc6fz/KeTteQeN7wkL V5ahPeErSuBn3/Vt1EDiI40gDYBj+9KAZB4IPGhoGAZA0NIHMuUEFIhnmfQXSozPkax1LQZrgXeg NFf3pZ9jiPyumiZQaSDeyHZ8zXvaXOehrqgH7bA/NzjpzMligX68tgjwgW/mUOBUfmf4cf1HkAqe XrRdh2HvNq7Chce81nJi92QY/TN3NZnNrxZTMHAHzi18xygdJccDWdnccTgDGh0mJaCYIlh8QInl HHAgYHNjIc44Yb91xREYFARBfo3gqhTEwienFVhCLAwNtpDUgQGLbB1wyYDCXzZN1QhbLYmYzoLV fQUqjkcCsFARy7zUGo5JvSZJ00KKNounqWBqySZm7Vj8Si50J9aOzvPPx871IIkhhEHKECMegfzs Q4OogMPO5jWoeWihI7Si68N7LpFMhQrauoL7qSeWrDuHCioVO8s5BWkbM2KG8qZRS3owmh5xkK1c 6AVi2X0EmxSglkuplYGzFOAz+X8zuEwbd993K9dQsXxZai3AUATNeCqgh6ekUQ6jEakMXJuzHUEo OKOTGID2EJL+CSUOs39K7iscODmortRcjQsFb/L/Ycnh5giLfyVqbQgIVw+B4ZGfi7LBJLEQG/pe lmVu4AkOAVQhzfciuCFshHaZKzUhbDFsc4ryOcnP+fia1acO0DzQRAbGVYkMxw71k+69LWk6nLfO 8GHK7iPMu3aBLu3AHYdAFh8t3gjw8nJeYNa1ArBBZmys3sdY2KehWiHRFOCUQWOXVQ27M/xhIqR5 +974XCIEjMD31Ax+vmBwU3DWYgfnEY1uEtAbaj06GUnwv8l3f1m3qC1PrOHVwahZr232K3mlQ2tQ 7fOoIsPRBesFz16wPVcDDCTDhVYPkiI1cuKCV29rU2RzFWROnbZ7ntseNKQocn3G0Sp/pgdPvB9v hmZ1COT/tWJWJXvKKpNqtRVeS7VpE+qFqLakW0tiNRKhJx/SIlKgGRJ8rRQhNMQ3fF5qTGgkmJRL IHsJ1LMSEbLHWk0msFjBCyz3ZKgsXn2ne6zLmPGw2pBDEAdjJls8+aWVIDmhJ2u40HMPu8QmHSiW iZk+uJKd1Y8onIfygJoWEena9fwflQvT9Idaw90nepL2QjLu4UV4Ay5BQws66sjLCAqgh5c7DFq0 CMutfZWrSPsLRZDEMV7SWv1dyFLnEqyyyTFc4oMS/S/vWwQxkKP2PXc5liCDZb1g3na3AyuyLk8u GZJ0nO5CgC9exGAM2zyPDFk/3KhnS9ZnSQy6w9yKyr1laWIAurZuhdYXIrW3aqzTdoigSQCOYHuM pOxlLMRFOPfxHVpEv6NDqEiLkKkvheWou9p9tqSpGJXsIK32nEioM+0LYVnUjfdySSSSajIkoTm1 VGkb4RyRhaOVZg/Asug144dmJ5DGmeiREVcWvT1tdJUTMLKJpiI7l6OuvIuC5FfFFwDrr7rh34x4 LBWiPMrIvSudfoJVRoeDDLgSHeRQAfiJQJp2wROPUZug0UMJDg5YWnPFE0kNFk16paYM0DNtmGGG 1+EGt3mx5PIx9EBy4H2pBf3a1CBMW+FcYq28qhNdfUd7udba1ticnuZtH4uc9fmCxTM8nT6NjnDm bd92d9qS2sCTYLOh5gdAl3VPxQczCt/F3+lqvxg4Z+07J9LTg1leSm52NWRoWVg2bkD5dDRptBw6 HAdLhSWfnvyvB3toPB9Z/4u5IpwoSC1vM0wA --Boundary_(ID_NLEkEFb5U5jXVw1dT+upoA)--