List:Commits« Previous MessageNext Message »
From:Martin Hansson Date:September 10 2010 8:46am
Subject:bzr commit into mysql-5.1-bugteam branch (martin.hansson:3505) Bug#50394
View as plain text  
#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
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