From: Jorgen Loland Date: January 17 2011 12:19pm Subject: bzr commit into mysql-trunk branch (jorgen.loland:3520) Bug#47853 List-Archive: http://lists.mysql.com/commits/128947 X-Bug: 47853 Message-Id: <20110117121955.B6DF279E@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0361042563135530845==" --===============0361042563135530845== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/jl208045/mysql/mysql-trunk-47853/ based on revid:hezx@stripped 3520 Jorgen Loland 2011-01-17 BUG#47853: Implicitly grouped queries do not need to use temporary tables A query is implicitly grouped if it contains an aggregate function but no GROUP BY clause. Implicitly grouped queries return zero or one row, so ordering does not make sense. This patch removes the order by clause during the prepare() stage of the optimizer when implicitly ordered queries are detected. @ mysql-test/r/func_group.result Updated result file @ mysql-test/r/limit.result Updated result file @ mysql-test/r/subquery_nomat_nosj.result Updated result file @ mysql-test/r/subquery_none.result Updated result file @ sql/sql_select.cc Remove order by clause during prepare() if the query is implicitly grouped. modified: mysql-test/r/func_group.result mysql-test/r/limit.result mysql-test/r/subquery_nomat_nosj.result mysql-test/r/subquery_none.result sql/sql_select.cc === modified file 'mysql-test/r/func_group.result' --- a/mysql-test/r/func_group.result 2010-12-21 12:34:13 +0000 +++ b/mysql-test/r/func_group.result 2011-01-17 12:19:52 +0000 @@ -1507,7 +1507,7 @@ SELECT MAX(pk) as max, i FROM t1 ORDER BY max; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 # Only 11 is correct for collumn i in this result SELECT MAX(pk) as max, i === modified file 'mysql-test/r/limit.result' --- a/mysql-test/r/limit.result 2008-10-15 21:34:51 +0000 +++ b/mysql-test/r/limit.result 2011-01-17 12:19:52 +0000 @@ -80,13 +80,13 @@ create table t1 (a int); insert into t1 values (1),(2),(3),(4),(5),(6),(7); explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using temporary +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; c 7 explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using temporary +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; c 28 === modified file 'mysql-test/r/subquery_nomat_nosj.result' --- a/mysql-test/r/subquery_nomat_nosj.result 2011-01-12 12:21:16 +0000 +++ b/mysql-test/r/subquery_nomat_nosj.result 2011-01-17 12:19:52 +0000 @@ -5088,7 +5088,7 @@ SELECT 1 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY count(*); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL a 5 NULL 2 Using index; Using temporary +1 PRIMARY t1 index NULL a 5 NULL 2 Using index 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table # should not crash the next statement === modified file 'mysql-test/r/subquery_none.result' --- a/mysql-test/r/subquery_none.result 2011-01-12 12:21:16 +0000 +++ b/mysql-test/r/subquery_none.result 2011-01-17 12:19:52 +0000 @@ -5087,7 +5087,7 @@ SELECT 1 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY count(*); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL a 5 NULL 2 Using index; Using temporary +1 PRIMARY t1 index NULL a 5 NULL 2 Using index 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table # should not crash the next statement === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-01-14 08:20:08 +0000 +++ b/sql/sql_select.cc 2011-01-17 12:19:52 +0000 @@ -749,7 +749,11 @@ JOIN::prepare(Item ***rref_pointer_array unit= unit_arg; if (tmp_table_param.sum_func_count && !group_list) + { implicit_grouping= TRUE; + // Result will contain zero or one row - ordering is meaningless + order= NULL; + } #ifdef RESTRICTED_GROUP if (implicit_grouping) --===============0361042563135530845== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jorgen.loland@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jorgen.loland@stripped\ # d7ytfa2iqxf5d900 # target_branch: file:///export/home/jl208045/mysql/mysql-trunk-47853/ # testament_sha1: 80798d427a22dd5982e7d42b4944026be9d090ce # timestamp: 2011-01-17 13:19:55 +0100 # base_revision_id: hezx@stripped\ # h33ksz5wowjihijn # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWVZvA6wABLjfgECwWGf//3K3 3iC////6YAl/Xl6+cr3p0CjXHGFBVoenXoe7ZsAyVT9AmkepvU9SbTRNij9T0mkNGg0yGhoZMDmB MTQYTJkyZGEwTTTIxMAQwDIip/o0kJtR5TTQD0gABoAAAASIppolPyMqegRoDamh4o0ABoAaAIpI 0EyMjSYFPISeGgqfpBBgR6h6nlP1BJIINNCMmQT0JoKP0iep6jRtIMgBoQ9zGvwXoMYLIcC/sejI 3EB53r0P3sr4GmncYsrjfwUwa7lIWM/1I8+tcLkJGBbJUjoR8kGJ7E8VRK5UvwCx6IucXTdo0Qtn XWmRWov/neR70ZIY2DGNps+/0Bas+q28beTbWu2DuRrssd/FIXOrIeisFHV6tGC73Wu/LyXgm0bx VTLBrnCgEI/oSLVMIsqiD4T8sQTkKFzqsARQgmuWMiGZ6Hqh7wAYGB6aoAi+7rK9VEc0gcKL/VU2 WitosgOLkEYOKw52ZoIgQEjThDGZxkVu1EEYOkfpD5MkTZ4Xa3IXW63m7g435Uu/mH3VvI+NNTim D1BmPo4/Czni4pFYpTfJoViyf/ghlOZnd7e0jG6+1E3hgtTltnCXJDa4WmyGgmWKGiTmaRmKi41R BApLDmIOKYFROiYJCJIYhheoa4k56R5kystpzMcJFkJG9oAcKtCC7jJRLw62a6EM3GYLEYam4ka8 eKkgsBDebxYPWJkaoGkRI1swyA6Ro3KwkJNIIDDCL0I4mrxLarFpnwYUcmBOrfmZAIUCExrKSRsT liJhJiNxkdOlVZlUTDSyZpiJTWwIxk7FF53ncV5IuzOD0EiS+atIe/1KLythK7UKDayFGjQ3ldch d8PHmSGZVsiiNdGIVb9920ya8xHCwVduuii/e8Ck1lu97vELkgZY8jb87dw6YXq9ZW5jaIeExyeU 7jB+kiKsxO986uFrVTm9x0KislStJrqtQQMCV25FpKhTFvYe+I528fAJGxh0cRzPGv/O0jQMdKBF oxEY48ZkC5RCjMM10WoM/T05GxW8rsd0tiizIpc1LkvF5QJPbgXQLyzLCGBWKSpZc4uyMlaqb+BZ hGvC/wCkmri0SqMR5XSGarfd9WGuqy7Ew0SMOwyGZI2ydJDp1F9haWkqZ+NznsQJ2G3SkxptBhMq dbLsLx5kqFUsIrsH7Ky6OKgpsVvSRGNZrGNhdkagwwqJGzUbggNs34RpvO5bh5UduqRsM2RvwqGy dBK5zqR5nCZfBbR7LDWaGgJo4ZJAODasAXkK4oLBETzQmBVLMLBN8O0kSKykuLiqdE3uZnawZTQt BtY9WGBbGgptlgxJ3rChOdFcYlusrhdFpJYVDOo5NQTdlgjQ3kEXxERki/FhV0sFAra1vpRUhKqI rMSJ1pZeBQvvfTuFwbN3AtI2NvejghgNsfJ5Ud/mfepqVtv+L/wkThgIhj44fxpZ//p+p9mRXUCf u1C/qwz+iZwCVtOdpKz87DNZN0d/1QPtUEwvJ9E0pw1pJH+wkkPl7eaUCZjt9DNKoH0D1F/w+Y86 nkY+fmSJKg+qoxPs1wD8PNIHWlE7CnIVIHF57PhHgiEaN4BLdC5Je+hlYk/4+0HIfUvcElOjm8/F cjLJw40M/cWFV6ecDgUF48gMe9Sido5bbV6iO34LhqWjjqIKXgyvG7jD1KiZQdDsRsoOwiuQZo4d bxNeFRvNB2aO0aI6mShvzqm6kwfBgbCBGKGJm7dmcVcsQvcet00sFCwmHO1LbzEuRXoJbqjQsSIp LvC7UaDMFsQth40ieHYuBL4OPW6Zwbk4yR0t9WWo8qiikYMkwgihkc+T5BE0DmPuH41h4HXulCg4 lYZprB5y8kSRirrjNgdMRIDAyqyWw1Gh2pyrRPjtapnha5yMB8i3wzhX3PjK7Q082de2L/9y+Ceu bHbthWeTMebC0wdDjEWSsG6hWMnETqpQBb8Fztc/iKYDWheyAm3sTASCamNWLbdHFTs8zX4F03kd 4sFZBnFsOfa8tgsRcnUjbtRtCENGV6NhJYb+dfC7pVQOMMdw8ghCAC+JcHRXd5avq9fumQbuBwC1 jg0dAf6PtMOG1h2Pvrc03oHm8UbaIOPA8k39RlAsfJm17DiPTyfJzvJB02GhItDryA/EoMscOJDB lZ8+/jZfWB6C3OrUBLWnWkUO92NiU65JzDqb3tdrhNBaXCoFCOi7bmSOQzIV5KzlaAJzmArMRaw3 pkAcQmB1tzPuCqp+ck4AGtm7bhoUKWSGECWJ4bMk4d7x32d2rkrbIU0hlRsDrT2dQp0Y8dg5DXCf mK81sjm1lUjTaSrFT8RiE9FYUImFVVl0qLKPF1zVoXBJ0pXEVgQrATXwKolQofMZriSgseVLihAo EMEJuKncAt2sjhseThGhNs9sx+56mb/HNXe3qmCRpQtKE/GksxYn+DB8lCkDxoOkPvgrkzqd2dd9 6G569xQer1pRMXZ6ONxtkkLEJ/kKxsC8OmdpHGcrCygK9rDNxwZHjmDtNKxCF96chPgLpLzulW9w egUBNRVeaYpKrnUPRKF1iA3IDfwuRVC4AZg0SR90Fk+zyoOLKq25BYQlR7U55W4cI6jYtaxsTRHf xbXNWNu/QFmlujFMNZhA0JyDECSXJtdS8yyW1hisGuhHK8YiIiNO9ETKssDJZpLldQKVhw24iRI2 zavItWvnDWUgS09lFEdgPQi6tqHIgGgjF0BoELnBXIP+yyB4SENkJUoeH3yx8sjlf3OtLbHSBqDR mktWnJkxUJw7A6K5Z3zqVIG7UhRoCTaTerGb4vB7KNL3ZNLcSY9gqhqAyepmebQ3voxS5O93NoYb AuC9w2i7X+tilJRWEEQURud5ITM773yPVDIXM2thsrXWX7Q0snm0n8i7kinChIKzeB1g --===============0361042563135530845==--