List:Commits« Previous MessageNext Message »
From:Øystein Grøvlen Date:September 10 2010 3:28pm
Subject:Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3505)
Bug#50394
View as plain text  
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
Thread
bzr commit into mysql-5.1-bugteam branch (martin.hansson:3505) Bug#50394Martin Hansson10 Sep
  • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3505)Bug#50394Øystein Grøvlen10 Sep
    • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3505)Bug#50394Martin Hansson10 Sep
      • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3505) Bug#50394Tor Didriksen13 Sep