Fix ORDER BY ... DESC optimization

Docs/manual.texi:
  Update with changes from old version of the 4.0 manual.
mysql-test/r/order_by.result:
  New tests for ORDER BY ... DESC
mysql-test/t/order_by.test:
  New tests for ORDER BY ... DESC
sql/sql_delete.cc:
  Removed DEBUG code

2 files changed, 119 insertions, 32 deletions

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 5c9e20c35a3..a47fc950f0e 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -112,58 +112,118 @@ member_id	nickname	voornaam
 1		
 2		
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	index	NULL	a	20	NULL	10	Using index
+t1	range	a	a	20	NULL	2	where used; Using index
 a	b	c
-1	NULL	NULL
 1	NULL	b
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	range	a	a	4	NULL	10	where used; Using index
+a	b	c
+2	3	c
+2	2	b
+2	2	a
+2	1	b
+2	1	a
+1	3	b
+1	1	b
+1	1	b
+1	1	NULL
+1	NULL	b
+1	NULL	NULL
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ref	a	a	4	const	5	where used; Using index; Using filesort
+a	b	c
+1	3	b
 1	1	NULL
 1	1	b
 1	1	b
-2	0	a
-2	0	b
+1	NULL	NULL
+1	NULL	b
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ref	a	a	9	const,const	2	where used; Using index; Using filesort
+a	b	c
+1	NULL	NULL
+1	NULL	b
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	range	a	a	9	NULL	8	where used; Using index; Using filesort
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	range	a	a	9	NULL	5	where used; Using index
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ref	a	a	9	const,const	1	where used; Using index; Using filesort
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	range	a	a	9	NULL	6	where used; Using index
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	range	a	a	9	NULL	5	where used; Using index
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	range	a	a	9	NULL	2	where used; Using index; Using filesort
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	index	NULL	a	18	NULL	11	Using index
+a	b	c
+1	0	
+1	0	b
+1	1	
+1	1	b
+1	1	b
+1	3	b
 2	1	a
 2	1	b
-2	1	c
+2	2	a
+2	2	b
+2	3	c
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	index	NULL	a	20	NULL	10	Using index
+t1	index	NULL	a	18	NULL	11	Using index
 a	b	c
-2	1	c
+2	3	c
+2	2	b
+2	2	a
 2	1	b
 2	1	a
-2	0	b
-2	0	a
+1	3	b
 1	1	b
 1	1	b
-1	1	NULL
-1	NULL	b
-1	NULL	NULL
+1	1	
+1	0	b
+1	0	
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	a	a	20	NULL	2	where used; Using index
+t1	range	a	a	18	NULL	3	where used; Using index
 a	b	c
 1	1	b
 1	1	b
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	a	a	4	NULL	5	where used; Using index
+t1	range	a	a	4	NULL	6	where used; Using index
 a	b	c
 1	1	b
 1	1	b
-1	1	NULL
+1	1	
+1	0	b
+1	0	
+count(*)
+9
+a	b	c
+2	3	c
+2	2	b
+2	2	a
+2	1	b
+2	1	a
+1	3	b
+1	1	b
+1	1	b
+1	1	
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	a	a	9	NULL	7	where used; Using index
+t1	range	a	a	8	NULL	10	where used; Using index
 a	b	c
-2	1	c
 2	1	b
 2	1	a
-2	0	b
-2	0	a
 1	1	b
 1	1	b
-1	1	NULL
+1	1	
+1	0	b
+1	0	
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	a	a	4	NULL	4	where used; Using index
+t1	range	a	a	4	NULL	5	where used; Using index
 a	b	c
+1	3	b
 1	1	b
 1	1	b
-1	1	NULL
-1	NULL	b
-1	NULL	NULL
+1	1	
+1	0	b
+1	0	
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 7d1c6346aa1..08d26413761 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -168,8 +168,8 @@ drop table t1,t2,t3;
 
 #bug reported by Wouter de Jong
 
-drop table if exists members;
-CREATE TABLE members (
+drop table if exists t1;
+CREATE TABLE t1 (
   member_id int(11) NOT NULL auto_increment,
   inschrijf_datum varchar(20) NOT NULL default '',
   lastchange_datum varchar(20) NOT NULL default '',
@@ -200,24 +200,51 @@ CREATE TABLE members (
   PRIMARY KEY  (member_id)
 ) TYPE=MyISAM PACK_KEYS=1;
 
-insert into members (member_id) values (1),(2),(3);
-select member_id, nickname, voornaam FROM members
+insert into t1 (member_id) values (1),(2),(3);
+select member_id, nickname, voornaam FROM t1
 ORDER by lastchange_datum DESC LIMIT 2;
-drop table members;
+drop table t1;
 
+#
+# Test optimization of ORDER BY DESC
+#
 
 create table t1 (a int not null, b int, c varchar(10), key (a, b, c));
-insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 0, 'a'), (2, 0, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 1, 'c');
+insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b');
+
+explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
+select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
+explain select * from t1 where a >= 1 and a < 3 order by a desc;
+select * from t1 where a >= 1 and a < 3 order by a desc;
+explain select * from t1 where a = 1 order by a desc, b desc;
+select * from t1 where a = 1 order by a desc, b desc;
+explain select * from t1 where a = 1 and b is null order by a desc, b desc;
+select * from t1 where a = 1 and b is null order by a desc, b desc;
+explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc;
+explain select * from t1 where a = 2 and b >0 order by a desc,b desc;
+explain select * from t1 where a = 2 and b is null order by a desc,b desc;
+explain select * from t1 where a = 2 and (b is null or b > 0) order by a
+desc,b desc;
+explain select * from t1 where a = 2 and b > 0 order by a desc,b desc;
+explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;
+
+#
+# Test things when we don't have NULL keys
+#
+
+alter table t1 modify b int not null, modify c varchar(10) not null;
 explain select * from t1 order by a, b, c;
 select * from t1 order by a, b, c;
 explain select * from t1 order by a desc, b desc, c desc;
 select * from t1 order by a desc, b desc, c desc;
 # test multiple ranges, NO_MAX_RANGE and EQ_RANGE
-explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
+explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
 select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
 # test NEAR_MAX, NO_MIN_RANGE
 explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
 select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
+select count(*) from t1 where a < 5 and b > 0;
+select * from t1 where a < 5 and b > 0 order by a desc,b desc;
 # test HA_READ_AFTER_KEY (at the end of the file), NEAR_MIN
 explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
 select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
@@ -226,4 +253,4 @@ explain select * from t1 where a between 0 and 1 order by a desc, b desc;
 select * from t1 where a between 0 and 1 order by a desc, b desc;
 drop table t1;
 
-/* vim:set ft=sql sw=2 noet: */
+