Fix sorting of NULL values (Should always be first)

Fix problem with HAVING and MAX() IS NOT NULL

3 files changed, 36 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;