Fix for bug #1335: filesort is missing in EXPLAIN if ORDER BY NULL is used

2 files changed, 44 insertions, 1 deletions

diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index f89cb8a6792..861f0f009cd 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -287,7 +287,7 @@ table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ALL	NULL	NULL	NULL	NULL	8	Using filesort
 explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	ALL	NULL	NULL	NULL	NULL	8	
+t1	ALL	NULL	NULL	NULL	NULL	8	Using filesort
 select sql_big_result spid,sum(userid) from t1 group by spid desc;
 spid	sum(userid)
 7	3
@@ -597,3 +597,18 @@ count(*)	category
 1	3
 1	4
 drop table t1;
+userid	count(*)
+3	5
+2	1
+1	2
+EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
+i	COUNT(DISTINCT(i))
+1	1
+2	1
+4	4
+explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
+DROP TABLE t1;
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index df99bc2a9dc..d28eeb27c15 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -423,3 +423,31 @@ select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(q
 select count(*), case interval(qty,2,3,4,5,6,7,8) when -1 then NULL when 0 then "zero" when 1 then "one" when 2 then "two" end as category from t1 group by category;
 select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category;
 drop table t1;
+#
+# Tests for bug #1355: 'Using filesort' is missing in EXPLAIN when ORDER BY
+# NULL is used.
+#
+--disable_query_log
+CREATE TABLE t1 (
+  userid int(10) unsigned,
+  score smallint(5) unsigned,
+  key (score)
+);
+INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3);
+# Here we select unordered GROUP BY into a temporary talbe, 
+# and then sort it with filesort (GROUP BY in MySQL 
+# implies sorted order of results)
+SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
+--enable_query_log
+EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
+--disable_query_log
+DROP TABLE t1;
+CREATE TABLE t1 (
+  i int(11) default NULL,
+  j int(11) default NULL
+);
+INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
+SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
+--enable_query_log
+explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
+DROP TABLE t1;