From: Martin Hansson Date: September 10 2010 8:46am Subject: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3505) Bug#50394 List-Archive: http://lists.mysql.com/commits/117975 X-Bug: 50394 Message-Id: <20100910084650.EC2BB13FA79@scott> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============4791424589925605660==" --===============4791424589925605660== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///data0/martin/bzr/bug50394/5.1bt-commit/ based on revid:alexey.kopytov@stripped 3505 Martin Hansson 2010-09-10 Bug #50394: Regression in EXPLAIN with index scan, LIMIT, GROUP BY and ORDER BY computed col GROUP BY implies ORDER BY in the MySQL dialect of SQL. Therefore, when an index on the first table in the query is used, and that index satisfies ordering according to the GROUP BY clause, the query optimizer estimates the number of tuples that need to be read from this index. If there is a LIMIT clause, table statistics on tables following this 'sort table' are employed. There may be a separate ORDER BY clause however, which mandates reading the whole 'sort table' anyway. But the previous estimate was left untouched. Fixed by removing the estimate if GROUP BY is followed by an unresolved ORDER BY clause. modified: mysql-test/r/order_by.result mysql-test/t/order_by.test sql/sql_select.cc === modified file 'mysql-test/r/order_by.result' --- a/mysql-test/r/order_by.result 2009-12-10 15:38:01 +0000 +++ b/mysql-test/r/order_by.result 2010-09-10 08:46:48 +0000 @@ -1617,4 +1617,25 @@ id select_type table type possible_keys 1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer DROP TABLE t1, t2; +# +# Bug #50394: Regression in EXPLAIN with index scan, LIMIT, GROUP BY and +# ORDER BY computed col +# +CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, KEY( a, b ) ); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; +CREATE TABLE t2( a INT PRIMARY KEY, b INT ); +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +INSERT INTO t2 SELECT a + 5, b + 5 FROM t2; +EXPLAIN +SELECT count(*) AS c, t1.a +FROM t1 JOIN t2 ON t1.b = t2.a +WHERE t2.b = 1 +GROUP BY t1.a +ORDER by c +LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 8 NULL 10 Using index; Using temporary; Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where +DROP TABLE t1, t2; End of 5.1 tests === modified file 'mysql-test/t/order_by.test' --- a/mysql-test/t/order_by.test 2009-12-10 15:38:01 +0000 +++ b/mysql-test/t/order_by.test 2010-09-10 08:46:48 +0000 @@ -1467,4 +1467,28 @@ SELECT * FROM t1 FORCE INDEX FOR JOIN (a DROP TABLE t1, t2; +--echo # +--echo # Bug #50394: Regression in EXPLAIN with index scan, LIMIT, GROUP BY and +--echo # ORDER BY computed col +--echo # +CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, KEY( a, b ) ); + +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; + +CREATE TABLE t2( a INT PRIMARY KEY, b INT ); + +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +INSERT INTO t2 SELECT a + 5, b + 5 FROM t2; + +EXPLAIN +SELECT count(*) AS c, t1.a +FROM t1 JOIN t2 ON t1.b = t2.a +WHERE t2.b = 1 +GROUP BY t1.a +ORDER by c +LIMIT 2; + +DROP TABLE t1, t2; + --echo End of 5.1 tests === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-09-07 07:58:05 +0000 +++ b/sql/sql_select.cc 2010-09-10 08:46:48 +0000 @@ -1486,6 +1486,7 @@ JOIN::optimize() } if (order) { + join_tab[const_tables].limit= 0; /* Force using of tmp table if sorting by a SP or UDF function due to their expensive and probably non-deterministic nature. --===============4791424589925605660== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/martin.hansson@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: martin.hansson@stripped\ # e8yxp9boeoed53sm # target_branch: file:///data0/martin/bzr/bug50394/5.1bt-commit/ # testament_sha1: a5a49803d8854f5e73246c7f6239dc0b5c4a1b78 # timestamp: 2010-09-10 10:46:50 +0200 # base_revision_id: alexey.kopytov@stripped\ # iv2v3a2j0uwxlxmn # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWfVPcEUABBFfgBAwWP///3// /+q////wYAlO+p3tbXY06aoJKSoFBQA6pmhqptTI9TJoGg0MhoGIA0ANANGQ0ABJIVPDTRMEU/VT 9NU8Rp6jE1GfqjRk0ynqaNoTTJ6mAaIaNJJ5pRoDRhDTIGg0GTQDIAAAYkhDU9TyMoeTRqPUGgyA ADQAAAA40ZMjCMQDCaDAJoNAyZNGTIYQGEkQIEwjQmmmmI0EaFPTU8poAeUyGgPUPUgt287fdT4b wTCZhZ5C/ePYrl4G1DLIFPqQEr2JFoUIQIH+YfFOHWzBHnzSuc6r6gIsLBe6bIllU6aEMLIcGRSu CJ8YyTjCPmwrT3d5g8dRb276pYcqn6KPWH5KM9R9A9wAa+LsyTAZQGE0GsoJJJGbe9SKH3DNaLJA TA00jjRCoheQAf3g4MD82GuxDI+i5syZmXM8ZrgFq3ecbA6gwAq1FR6EnVcwOR0hoOEj4qwoWchc MWbSYmKAzCMnHgw5cFfmMIftxyGebSdPRlON6cUfTCoNo+JouEWSJjbfuSxciuR20UBUS/BqE8Eo wCQdsus/A/EHNhoPeSGJBI9Bicma92MstALMpDdFGG84WnX1Y1HrSrzkV6zx74uzBiXmbtJWxDPI NZJebMYZmC0YJjWSHoRmxzloTBoDzBuHlGYb1jAe8AoFAoFAoHmvM3rh3kgxJG0tL14sQgYhqAOa L9QjoDmH2tgrjk9JpPCF55JnkrB2LLkruWgKQkEJDbE6KAKAMJ9kXkai8z5nNLJiyY72Fod3aTPc ESYSAUDcNIYSiRGBosRDJ1goV5m2NkZ1iIO4BJbRzsJoktGgptKSqP9LsP17LLGAcZMKxw5hAsGS n6TERAPVQSeWO4DT4+wc/vRKAjm445GyJesjJD4poC7q4igGnR99o3TgeJ2mw3GKpwMFxNzi8TTV XQ3wFs2OZhwBwxFJhLQSrKScMIEXN2e+6szJnI3hXbAaDFMFJDI6JxQK1S1F7SP8JHUwlGYCqBWJ RVCiQUGUiBAktNyleSIlDTn+agLLnSBgGAbS3LHQhmhs4XRWtdvWQgGkwLBGGEy4kwjZAG2p5pqy IaCmc78PsR+1DqpAYgwa8OpOiULQVfgrETEsFu666wyxHnXs0EihYeyuBy+RH5FZu5FdfA2mJ5UF ecCDri0RcNNPw41blchxvaakPKyYuVs7lvRLTK64p8WjbtwnFykjwM8OvCsq0o5kdWqh5jnfrta+ gjGBfrNQYtsr2G4rkwiyTDFB5HPtnnfr0BAwNoic7iL3EQ063SWJiMY3LToJhGwctHLx7fIRiYRr MTEtWDXDtN3grsIVzaSxThF0NNohQU6M8mPuJvBoCb2c/QNvJgamCY9qygMJufzXukC7jovgLNHc HQFh6wkWphmG7RQQ5gIMcLPB4usBxSRBhPFQ4XWL4AfIcB3BQRX/B5HjwZgZmPqPaFgqjA+gyoBM AyEyUz6sDfzS9otQpUGWAqwsF/sFrk5AZDgwCxHDWB9TITxhiLJjtB4FQFJ9UXkSgwNAEwDHcGkP qLcf8AkBmFacHHDExAuPKOcRjSA4XgxeKKkKsIgVw6ggKgELGZgkWosDaJOD0dXSLh413xF7vbN1 dCglOxop7RWgpFAElznSH7eGzOo7LAOc9RXZ7JwVNnOMVA+Atw7K02K6Syo/aqnD5fDYCh/C52Qe yYKn2eg0GP1MY3dfAI1c+EdAnAwOtNbG/S8699vca9mrcNnbWRLoFIxqKEbGJCMzh+60PsLsN1cT zEPDY0Rz5mx+yw7zn9MixZDnBMTLTrPwzLsejR2hXDTqYgCZXIDftgvtrcsK/WWoshcoCgzsbExx v1xiIpiURNDbCszJpR21YHbTolMD1ZyNXYFKabzkX/P+p66zxr7x2O2UYRTbpQiRUoRYkScE7LTq O7Ex7vOfo/juLzwIQ7xFxRbFltmybEKhcPIz1tyzHLwqR9NP4XrK/qu6dgjpkbn8uvzZpXmk97sM TNUS137zeP6uNIVbdFeqAa0yNQifPg8E2/CxxVh6VQ8IHkaKMzcSF8M3UhPioLmyVgE1NLr5pirM sz+9uQuZIYbkbTkRNOB5HiFwbNv/l7V1Ioaih3RMWPYzvibzcymgqmYHX5pcdPjNbOC2FCwfWB5A pGgZBlLiQ+g7kAfIzcx2pTWGrVfrOcMKVZ+9Jtm2WFwWl5MRJE2mhmGBcdgul0hNfipVXuf8v1yo ggn0Y1DNogJuG0atNAwX8RUMONehbT73D7+BeV9Rv6HqYcXwkdy+Bl4lix0B6SFq42EgmsQid1di cIgnE/jQOzGOTTmtMcocbTwkYjXkFgRl28s192skwMAMm09f5HxBk/TxNaaeHvM1bVRbxsNBMYwX pvjUFKETRUqdCNVJMEHB2aAPoRAE6Aa9RQ4mYC4g0YCMyrUxcV3cS1CriXJNY6N2RBT9VnmHUbjK 9WiO0RqOdhA5qy0vrigNQyIYKwZDgm5RmSsVZ9gWIuXG4ZjOy8MTBUqTbM4UMnWe7BtOp8CVyDsE ZzNnZBMjgUD4mWXaYHZNGfSELbYVxTL2aM7NhpFM5aLio4t4MrRhW8ysQc2owEnYeooVZSjNBK1e Cu08aBUWNaq/NzCuCGPOgNqQgtmhOCDq8V9+wlSmUFDi5g0dNq7M+JGL2IxWMddzcmUlX+IMpCgt 94ebdojcX1ITf9PBepJJRpCcP3UbJB02BZS22MoKOoQ0FJpkcJ6qnohxzu6o1KPXgJc2B2APblfU qeYmK7K60PEqOwrVWCjTfI0KzAkJppxL5T2XamWZxghSJUJi0hksLn0MiU9mDy/elyG/xED1qRu3 KxkWRGyfhBghRiuKg5lsvXEqypbs64Hcqg9LUauO3ZEnHrk5R702VKijFga2jBEXzECHicawUIXi kzi4Fw5nSiG+HbAvGfpERKu9ci2yOFZHI8R+RdyRThQkPVPcEUA= --===============4791424589925605660==--