bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3508)

[Mattias Jonsson]

#At file:///Users/mattiasj/mysql-bzr/topush-51-bugteam_2/ based on revid:mattias.jonsson@stripped

 3508 Mattias Jonsson	2010-09-13 [merge]
      merge

    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-13 11:33:19 +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-13 11:33:19 +0000
@@ -1467,4 +1467,29 @@ 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-10 09:50:38 +0000
+++ b/sql/sql_select.cc	2010-09-13 14:07:50 +0000
@@ -1487,6 +1487,15 @@ JOIN::optimize()
     if (order)
     {
       /*
+        Do we need a temporary table due to the ORDER BY not being equal to
+        the GROUP BY? The call to test_if_skip_sort_order above tests for the
+        GROUP BY clause only and hence is not valid in this case. So the
+        estimated number of rows to be read from the first table is not valid.
+        We clear it here so that it doesn't show up in EXPLAIN.
+       */
+      if (need_tmp && (select_options & SELECT_DESCRIBE) != 0)
+        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/[e-mail]