#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.
Attachment: [text/bzr-bundle] bzr/martin.hansson@oracle.com-20100910084648-e8yxp9boeoed53sm.bundle
