Fix sorting of NULL values (Should always be first)

Fix problem with HAVING and MAX() IS NOT NULL


Docs/manual.texi:
  Changelog & NULL usage with ORDER BY
client/mysqldump.c:
  Cleanup disable keys
mysql-test/r/distinct.result:
  Fix results after ORDER BY with NULL fix
mysql-test/r/group_by.result:
  Fix results after ORDER BY with NULL fix
mysql-test/r/having.result:
  Testcase for bug with HAVING
mysql-test/t/distinct.test:
  Test for DISTINCT + ORDER BY DESC bug
mysql-test/t/having.test:
  Test of HAVING and MAX IS NOT NULL
sql/filesort.cc:
  Fix sorting of NULL values (Should always be first)
sql/item.h:
  Fix problem with HAVING and MAX() IS NOT NULL
sql/item_sum.h:
  Fix problem with HAVING and MAX() IS NOT NULL
sql/opt_range.cc:
  Fix problem with HAVING and MAX() IS NOT NULL
sql/opt_range.h:
  Fix sorting of NULL values
sql/sql_select.cc:
  Fix sorting of ORDER BY ... DESC on NULL values.

5 files changed, 59 insertions, 2 deletions

diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 21f87a11a53..e347a95b037 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -77,6 +77,7 @@ NULL	NULL
 10	VMT
 select id+0 as a,max(id),concat(facility) as b from t1 group by a order by b desc,a;
 a	max(id)	b
+NULL	NULL	NULL
 10	10	VMT
 9	9	SRV
 8	8	RV
@@ -89,7 +90,6 @@ a	max(id)	b
 1	1	/L
 -1	-1	
 0	0	
-NULL	NULL	NULL
 select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp;
 grp	count(*)
 0	7
@@ -336,3 +336,16 @@ a	c
 4	NULL
 3	NULL
 drop table t1;
+create table t1 (a char(1), key(a)) type=myisam;
+insert into t1 values('1'),('1');
+select * from t1 where a >= '1';
+a
+1
+1
+select distinct a from t1 order by a desc;
+a
+1
+select distinct a from t1 where a >= '1' order by a desc;
+a
+1
+drop table t1;
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 9845e12ef5f..2affdc1b653 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -226,7 +226,7 @@ key (score)
 INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3);
 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	6	Using temporary
+t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
 select userid,count(*) from t1 group by userid desc;
 userid	count(*)
 3	3
@@ -244,6 +244,8 @@ spid	count(*)
 2	2
 select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
 spid	count(*)
+2	2
+1	1
 explain select sql_big_result spid,sum(userid) from t1 group by spid desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index df99f2545cb..a33ce457176 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -44,3 +44,22 @@ AND start <= 999660;
 id	start	end	chr_strand
 133197	813898	813898	-1.0000
 drop table t1,t2;
+CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL);
+INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50);
+select Fld1, max(Fld2) as q from t1 group by Fld1 having q is not null;
+Fld1	q
+1	20
+3	50
+select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null;
+Fld1	max(Fld2)
+1	20
+3	50
+select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null;
+Fld1	max(Fld2)
+1	20
+3	50
+select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null;
+Fld1	max(Fld2)
+1	20
+3	50
+drop table t1;
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index bf8a03ac40d..b850ec5d562 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -207,3 +207,14 @@ insert into t1 (a) values (1),(2),(3),(4),(1),(2),(3),(4);
 select distinct a from t1 group by b,a having a > 2 order by a desc;
 select distinct a,c from t1 group by b,c,a having a > 2 order by a desc;
 drop table t1;
+
+#
+# Test problem with DISTINCT and ORDER BY DESC
+#
+
+create table t1 (a char(1), key(a)) type=myisam;
+insert into t1 values('1'),('1');
+select * from t1 where a >= '1'; 
+select distinct a from t1 order by a desc;
+select distinct a from t1 where a >= '1' order by a desc;
+drop table t1;
diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test
index fff5415976c..8dd7606d82b 100644
--- a/mysql-test/t/having.test
+++ b/mysql-test/t/having.test
@@ -48,3 +48,15 @@ GROUP BY e.id
 HAVING chr_strand= -1 and end >= 0 
   AND start <= 999660;
 drop table t1,t2;
+
+#
+# Test problem with having and MAX() IS NOT NULL
+#
+
+CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL);
+INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50);
+select Fld1, max(Fld2) as q from t1 group by Fld1 having q is not null;
+select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null;
+select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null;
+select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null;
+drop table t1;