Martin,
Approved, but I suggest that you add a comment in the code as to why you
set limit to 0.
--
Øystein
On 09/10/10 10:46 AM, Martin Hansson wrote:
> #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.
>
>
>
>
>
--
Øystein Grøvlen, Senior Staff Engineer
Sun Microsystems, Database Group
Trondheim, Norway
