From: Martin Hansson Date: December 17 2009 9:55am Subject: bzr push into mysql-5.1-bugteam branch (martin.hansson:3281 to 3282) Bug#47650 List-Archive: http://lists.mysql.com/commits/94695 X-Bug: 47650 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0069383282==" --===============0069383282== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline 3282 Martin Hansson 2009-12-17 Bug#47650: using group by with rollup without indexes returns incorrect results with where An outer join of a const table (outer) and a normal table (inner) with GROUP BY on a field from the outer table would optimize away GROUP BY, and thus trigger the optimization to do away with a temporary table if grouping was performed on columns from the const table, hence executing the query with filesort without temporary table. But this should not be done if there is a non-indexed access to the inner table, since filesort does not handle joins. It expects either ref access, range ditto or table scan. The join condition will thus not be applied. Fixed by always forcing execution with temporary table in the case of ROLLUP with a query involving an outer join. This is a slightly broader class of queries than need fixing, but it is hard to ascertain the position of a ROLLUP field wrt outer join with current query representation. @ mysql-test/r/join_outer.result Bug#47650: Test result @ mysql-test/t/join_outer.test Bug#47650: Test case @ sql/sql_select.cc Bug#47650: Fix modified: mysql-test/r/join_outer.result mysql-test/t/join_outer.test sql/sql_select.cc 3281 Ramil Kalimullin 2009-12-17 [merge] Auto-merge. modified: mysql-test/r/myisam.result mysql-test/t/myisam.test sql/sql_insert.cc === modified file 'mysql-test/r/join_outer.result' --- a/mysql-test/r/join_outer.result 2007-05-27 19:22:44 +0000 +++ b/mysql-test/r/join_outer.result 2009-12-17 09:55:18 +0000 @@ -1254,3 +1254,38 @@ SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WH c e d 1 0 NULL DROP TABLE t1,t2; +# +# Bug#47650: using group by with rollup without indexes returns incorrect +# results with where +# +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 ( a INT, b INT ); +INSERT INTO t2 VALUES (1, 1),(1, 2),(1, 3),(2, 4),(2, 5); +EXPLAIN +SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) +FROM t1 LEFT JOIN t2 USING( a ) +GROUP BY t1.a WITH ROLLUP; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 +SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) +FROM t1 LEFT JOIN t2 USING( a ) +GROUP BY t1.a WITH ROLLUP; +a COUNT( t2.b ) SUM( t2.b ) MAX( t2.b ) +1 3 6 3 +NULL 3 6 3 +EXPLAIN +SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) +FROM t1 JOIN t2 USING( a ) +GROUP BY t1.a WITH ROLLUP; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where +SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) +FROM t1 JOIN t2 USING( a ) +GROUP BY t1.a WITH ROLLUP; +a COUNT( t2.b ) SUM( t2.b ) MAX( t2.b ) +1 3 6 3 +NULL 3 6 3 +DROP TABLE t1, t2; === modified file 'mysql-test/t/join_outer.test' --- a/mysql-test/t/join_outer.test 2007-06-06 17:57:07 +0000 +++ b/mysql-test/t/join_outer.test 2009-12-17 09:55:18 +0000 @@ -867,3 +867,32 @@ SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WH DROP TABLE t1,t2; +--echo # +--echo # Bug#47650: using group by with rollup without indexes returns incorrect +--echo # results with where +--echo # +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 ( a INT, b INT ); +INSERT INTO t2 VALUES (1, 1),(1, 2),(1, 3),(2, 4),(2, 5); + +EXPLAIN +SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) +FROM t1 LEFT JOIN t2 USING( a ) +GROUP BY t1.a WITH ROLLUP; + +SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) +FROM t1 LEFT JOIN t2 USING( a ) +GROUP BY t1.a WITH ROLLUP; + +EXPLAIN +SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) +FROM t1 JOIN t2 USING( a ) +GROUP BY t1.a WITH ROLLUP; + +SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) +FROM t1 JOIN t2 USING( a ) +GROUP BY t1.a WITH ROLLUP; + +DROP TABLE t1, t2; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2009-12-16 09:03:24 +0000 +++ b/sql/sql_select.cc 2009-12-17 09:55:18 +0000 @@ -7124,7 +7124,19 @@ remove_const(JOIN *join,ORDER *first_ord for (order=first_order; order ; order=order->next) { table_map order_tables=order->item[0]->used_tables(); - if (order->item[0]->with_sum_func) + if (order->item[0]->with_sum_func || + /* + If the outer table of an outer join is const (either by itself or + after applying WHERE condition), grouping on a field from such a + table will be optimized away and filesort without temporary table + will be used unless we prevent that now. Filesort is not fit to + handle joins and the join condition is not applied. We can't detect + the case without an expensive test, however, so we force temporary + table for all queries containing more than one table, ROLLUP, and an + outer join. + */ + (join->tables > 1 && join->rollup.state == ROLLUP::STATE_INITED && + join->outer_join)) *simple_order=0; // Must do a temp table to sort else if (!(order_tables & not_const_tables)) { --===============0069383282== 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 # target_branch: file:///data0/martin/bzr/bug47650/5.1bt-commit/ # testament_sha1: 2ec6952f5fdf15d436f161d2c737b602517faeb7 # timestamp: 2009-12-17 10:55:42 +0100 # base_revision_id: ramil@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWZWvq/gABUbfgEcwWff//3/3 3+q////0YAvV1t8mboruNKqiaGJXpp1tgN77nlvWDuw1o8MkmUyYibTU2SehNNMBpPSaaBoNANAa GgaCSUxNMgTAQqfpNTJtGppkaeppiNNA009EDTTagNKI0eUAHqA00AAAGIAAAAADCUxEamaUzCYp mp6anpA0GgA0AAA0ANqSmJonqaeUwhkMgZMmT0hpoGhoABpoDCSIQATTQAQyMpgTTUZT1PU9TyJo HqZGhmppjFC28IaHAKDeG8+4/o0IAvaMVA4YlA7D1H7jQNAbG+AwBxL4NpBdnCUChp/PHUIJY8Ou 7sYHHoo5PupEnHwvkURjFBQZDlAQHGlyITqrEaMjomomAGgAwGg10vopMKMG4sAU0Bhbku3NMVNj l6NKAnCzFcRVvHBqroVIwhWIWQeflFjI/MKnbbd4S5wGAPUHjD6nSUkUlb9wNdtVu8G1pnUIbENi LmGBUo04c3tEdh20gN7+Pqw0GgY0BPldmZmZgpAjpf4feA4HsLxi4qMQOysc/OwCWHwBXGBm5Sdn 8s77p6/LGdj36WcUXnISG0VxKnOazSMByX+hN3UhQ8MUmMu5MzCtomK+fYKoUzh3a5QKzODUYpY7 akogYT3EGL9Qoo3Epsp1QGc+r+YQLc3JxZWpOM42WPvquefGSJolkpPnPzaHWB7CgxA7gFB1APOQ kqjDLGgyC90He7I+iOIsrxy6NNeeOmqE81Q8oVDVcFRXKxQnmu37nixRsssF8Kflv4N1lzLwPZpX w8OObicrlTHWkmICqUsiyCPs+0YZm2i8Rzg2z+wDh4ngf6EOA4EA7QGqb5exxdjHvYAxLHAG+w4G 00nCO1mnD4/OUhUB5AWgRYDu+3MbIy48stZIDQHq4qdJo7xLN56hFVU/OR2Q9wGYurcVgM+Fc8GY ZEUaIfLFttzRNBdaDFaZtADoEllRAuTIR5XI82R8ufIIUT+mK/miLSVcMMmO8QLkkA4azLETqLBn AAExBgoHRjB1Co5Q98H1kulq2CBAgVqo3xpEoiLE8XMRYdKHucZlJWSHFRDq7ovwMy6zMMWeMmEM eYCgQPMbRRzXXa0BKApR87ULSW2FGNXljWEswtjaVC0vGQq9PLXgIst287iiUFG8LC0q4rEiOI7F g5pLWLBhDZm8HCSuSRaX6bFAlWwnd4vszW0HFKm0GUikVurUSgK04lZIyXReN0piCY98jkv9Ea84 RYJshesyky2rFh6G0ko5joTZh+mbWNYUSUnzWw/g+BtUS4+LYRhvXaWN/LOzExpzwClPMuiCpbYd H7xSIExmKoTVbMvQwRGnPqrfr0t0Gm1tr4mB+GeQt7iHWdhbRVvEXwwFbYbFKcUkxxnqslzO4rdx WgXulZoGXCE0njMospHOQpKw47G6mRluPAQ/m64iNMyrDAY3c92KYwWLCKouOClJ5u9PXxdCehZq 14uhQpIPMDcmz4OQ8jEcbiZAyKC5QUpp90wUxVOYeYtC2mSbV+ZsPfe9QqZMJRMHbitoIWhEguRT cb+mm84lpiVmJcla37qXxrcyKfGaCcIxgiKYg4NCN88zCix7aRmGEwb+g8PIywcDky+flIytMM+y mkeU5QQ8+fxgYNcScKk5RLbCA5Nh2V8aBHhB+Ad5+gf0CPzEarhmYZmG/yLARNyW2CqGAmCSBisK g/64mCDgQYPoB9GD9AkEqRwKApH/wA7gNID9XoC4PxAcD6zhIRROB+cEFwI5ge4VoEQvEFpaCYJw TgQEXkgPgHaZCTxDCeXDCWAdgGYkfAQwiIiYwL+A4hfSFoLsA957hEALgNgvCsDlQSAeFwTCsBwJ 4uOgZMwDweGgUCCQjKCIAooKkwlOIuDQI6/dB+lcF4pw8WA7iIiLd3x7kRUmUGWsupGQvYAwDAV+ fxPieQmB5T0A4YyN3oPIUn/YIB5fhgr5dWq8TkQFIx+lRHUhlKBiMZiLogO3oyRBa1pheI77EigS 5nBHUMkHJyQmgxNSOAsdUERKBNGQRQ8GzOMQSzA24nOBwHOo93n1kCwyBcWlS3AHSNornmYXWB1V cKnXW6+4tJuCtMTALK2WsibETDlBR/W6liZQlj8uusISjTY2NqSk8CaiuYR31krEitosVnKTmYZq BmZyl4aE5kP5F9ZuM46rK0bU9JXQiY4rWUTFJQI/A2Gw0Ld0aM7aLA6AZPkrUDCLdEwYlLMzFr7H 0/NqOvTQpbVgUryDHEneRmonT/V0lWs9GAMwziqxdPUZFOIpn086aeY9FjHaiRsJaHLEERZuJDcd IR0T0uPrVrWcLs+2TNTdEjrk8uD2bl7e302q4C5BkMm9f4EZssmpaKRmZDQuubpKAQsrEyL3Wvob 0BOtV1/GA6yXyFQza0fcWoHaZBZe4cWEZyAMSS1Cn2K28GteHvjuEdZ3ktrnWRGXAJw1Xr8l3dq7 lEB2KDo6iAtLLlP+G9culUCXATbdCelVsB4qeOpZj9uWm3BDfr355izgYsIZllXqhDkAx7bI+iQ8 ZMjtZGyM0DtBsgTqeEgg4pplbomkb0mNdA2rTI3IXM1KYoPxuO4IzdV67lSgPmxlXNWI1r8rSHgH WyTpDCM8VvFsdZ39uTXOjX3PW7+VtfncN4XRJ6UmQYA57BRaIdnr16oXgGFymxOmiSfwJQHJq2WC E1XiLPwqogxsPqpRU0L0PCbAehNLOBRFvbKgcVS+aiVjSB4XEVpxNHxbRG+cNFM1e0KEYfTA5mJ6 BknO+ZLIQiLnG0uvkms6Q8XR7GymLqTh6sYUdcbMLiYQWplVBqPD68DrQygoobgod9RJdI6vsK/T speAwA22GeI78THKdorFls0gig02mx504CTbRSae4AWSGSAgQtwiO7SU0UQgRMsICbeRQESl6GN6 m7mSy6qVjPmdPb2K0FcoIZgiwRDkpc6mpUm22UD0USrtBGK+AqLyVi44cBKMqa2L0s2E5IYHqcIe B4VJP8bF7bhd0TjhjNwSTygibCMQCqbklPBGJyNBR4HElxCu30LPhnLUCQSz7qw7e3mB+9Vzh5h6 FGkWm0vVK1KkTT+h2KeWSWTImJWLcsRGGG6rJmEueHUr0YJ7C0pAiw7KCGDebHEYRThacVOLv8tp 43SFmTGpXvWhtC1k+SEwiGPsB2ZHGG14LMyhWqWMbEXdO5QwlSurkqrhbUYnrEVKtjkwXrMCdwd3 HBndoHUS2zDGVO6VVSKkpcNjEKKlQtz1mDwFTmEtG2sjFjtAr3UYkp8zMDwzrUqz4vWVpjERplNe G5E8lBUVgrxdQdDYO7u7ry1IeEFGJnmAuKkJxhhHU0ikVcEF/EFHSX8TOzmF0blD9pvhEktEZ8KK WJiDTTHrkXunXXkpRmiXmUSTDAkT+ve9l5eqPATVlRUvJgZkc8KJxqacI2MWUi54iDe22LLcUDwE 84G+VB1XMAQEEDD72/iEDiLANtBYmnPsajWcKxCzLTnIwhgnLbHQxnXJRLxDGRajcTqXndv7u1Hy Fqc1KDZsWBOzgDwYfv2WvfkNJNIO5IVbJBOMI4a1csdcE2Q8/MHgI0LipvTEs3vY+GdVuTMFqY4s C7GFMmJHARhRdkUGTYLLBB94xwr0tTAhlHFGCeAjOncmHzKeKVMijFLablwETSvIpCIFuz7ZsSQl RaOuP/F3JFOFCQla+r+A --===============0069383282==--