Merge bk-internal:/home/bk/mysql-4.0

into sergbook.mysql.com:/usr/home/serg/Abk/mysql-4.0

13 files changed, 263 insertions, 73 deletions

diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index f73c4d7f8af..94e1ce59585 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -27,6 +27,8 @@ PRIMARY KEY (userID)
 INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1');
 INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1');
 INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1');
+INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1');
+INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1');
 SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid;
 userid	MIN(t1.score)
 1	1
@@ -47,8 +49,12 @@ userid	MIN(t1.score+0.0)
 2	2.0
 SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid ORDER BY NULL;
 userid	MIN(t1.score+0.0)
-1	1.0
 2	2.0
+1	1.0
+EXPLAIN SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid ORDER BY NULL;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using temporary
+t2	eq_ref	PRIMARY	PRIMARY	4	t1.userID	1	Using index
 drop table test.t1,test.t2;
 CREATE TABLE t1 (
 PID int(10) unsigned NOT NULL auto_increment,
@@ -242,34 +248,32 @@ score smallint(5) unsigned,
 key (spid),
 key (score)
 );
-INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3);
+INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,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; Using filesort
+t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
 explain select userid,count(*) from t1 group by userid desc order by null;
 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	8	Using temporary
 select userid,count(*) from t1 group by userid desc;
 userid	count(*)
-3	3
+3	5
 2	1
 1	2
 select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3);
 userid	count(*)
-3	3
 1	2
 select userid,count(*) from t1 group by userid desc having 3  IN (1,COUNT(*));
 userid	count(*)
-3	3
 explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	spID	spID	5	NULL	2	Using where; Using index
+t1	range	spID	spID	5	NULL	3	Using where; Using index
 explain select spid,count(*) from t1 where spid between 1 and 2 group by spid;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	spID	spID	5	NULL	2	Using where; Using index
+t1	range	spID	spID	5	NULL	3	Using where; Using index
 explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	spID	spID	5	NULL	2	Using where; Using index
+t1	range	spID	spID	5	NULL	3	Using where; Using index
 select spid,count(*) from t1 where spid between 1 and 2 group by spid;
 spid	count(*)
 1	1
@@ -280,12 +284,14 @@ spid	count(*)
 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
+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	6	
+t1	ALL	NULL	NULL	NULL	NULL	8	
 select sql_big_result spid,sum(userid) from t1 group by spid desc;
 spid	sum(userid)
+7	3
+6	3
 5	3
 4	3
 3	3
@@ -293,13 +299,13 @@ spid	sum(userid)
 1	1
 explain select sql_big_result score,count(*) from t1 group by score desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	index	NULL	score	3	NULL	6	Using index
+t1	index	NULL	score	3	NULL	8	Using index
 explain select sql_big_result score,count(*) from t1 group by score desc order by null;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	index	NULL	score	3	NULL	6	Using index
+t1	index	NULL	score	3	NULL	8	Using index
 select sql_big_result score,count(*) from t1 group by score desc;
 score	count(*)
-3	3
+3	5
 2	1
 1	2
 drop table t1;
@@ -535,15 +541,7 @@ t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary
 t2	ALL	a	NULL	NULL	NULL	4	Using where
 drop table t1,t2;
 create table t1 (a int, b int);
-insert into t1 values (1, 4);
-insert into t1 values (10, 40);
-insert into t1 values (1, 4);
-insert into t1 values (10, 43);
-insert into t1 values (1, 4);
-insert into t1 values (10, 41);
-insert into t1 values (1, 4);
-insert into t1 values (10, 43);
-insert into t1 values (1, 4);
+insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
 select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;
 a	MAX(b)	INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000)
 1	4	2
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 48db5412da7..293519604dd 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1201,3 +1201,74 @@ a	b
 8	5
 9	5
 drop table t1,t2;
+create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) type=innodb;
+create table t2 (a int not null auto_increment primary key, b int);
+insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
+insert into t2 (a) select b from t1;
+insert into t1 (b) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+select count(*) from t1;
+count(*)
+29267
+explain select a from t1 where a between 1 and 10000;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	range	PRIMARY	PRIMARY	4	NULL	14790	Using where; Using index
+explain select * from t1 where a between 1 and 10000;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	range	PRIMARY	PRIMARY	4	NULL	14790	Using where
+explain select * from t1 where c between 1 and 10000;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	range	c	c	5	NULL	1	Using where
+update t1 set c=a;
+explain select * from t1 where c between 1 and 10000;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	c	NULL	NULL	NULL	29537	Using where
+drop table t1,t2;
+create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) type=innodb;
+insert into t1 (id) values (null),(null),(null),(null),(null);
+update t1 set fk=69 where fk is null order by id limit 1;
+SELECT * from t1;
+id	fk
+2	NULL
+3	NULL
+4	NULL
+5	NULL
+1	69
+drop table t1;
+create table t1 (a int not null, b int not null, key (a));
+insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
+SET @tmp=0;
+update t1 set b=(@tmp:=@tmp+1) order by a;
+update t1 set b=99 where a=1 order by b asc limit 1;
+update t1 set b=100 where a=1 order by b desc limit 2;
+update t1 set a=a+10+b where a=1 order by b;
+select * from t1 order by a,b;
+a	b
+2	4
+2	5
+2	6
+3	7
+3	8
+3	9
+3	10
+3	11
+3	12
+13	2
+111	100
+111	100
+drop table t1;
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 6e69616b7c6..98f8882aa10 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -373,10 +373,10 @@ Thimble Smith	Happy	3	3	3
 drop table t1,t2;
 create table t1 (id int not null, str char(10), index(str));
 insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar");
-select * from t1 where str is not null;
+select * from t1 where str is not null order by id;
 id	str
-4	bar
 3	foo
+4	bar
 select * from t1 where str is null;
 id	str
 1	NULL
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 0f3644f15bc..daac5438eaa 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -286,7 +286,7 @@ a	b
 107	7
 108	8
 109	9
-update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t2.a=t1.a-100;
 select * from t1;
 a	b
 201	1
@@ -300,13 +300,13 @@ a	b
 109	9
 select * from t2;
 a	b
-1	3
-2	3
-3	3
-4	3
-5	3
-6	3
-7	3
-8	3
-9	3
+1	1
+2	2
+3	13
+4	14
+5	15
+6	6
+7	7
+8	8
+9	9
 drop table t1,t2;
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index 236def64b5e..009a3e06eb2 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -84,7 +84,7 @@ table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	range	a,b	a	5	NULL	5	Using where
 explain select * from t1 where (a is null or a = 7) and b=7 and c=0;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	a,b	a	5	NULL	4	Using where
+t1	ALL	a,b	NULL	NULL	NULL	12	Using where
 explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ref	a,b	a	5	const	3	Using where
diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result
index e225a0e3995..3303f19d9c7 100644
--- a/mysql-test/r/select_safe.result
+++ b/mysql-test/r/select_safe.result
@@ -1,6 +1,6 @@
 drop table if exists t1;
 SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9;
-create table t1 (a int primary key, b char(20));
+create table t1 (a int auto_increment primary key, b char(20));
 insert into t1 values(1,"test");
 SELECT SQL_BUFFER_RESULT * from t1;
 a	b
@@ -30,10 +30,34 @@ You are using safe update mode and you tried to update a table without a WHERE t
 delete from t1 where a+0=1;
 You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
 select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5;
-The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok
+The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok
 update t1 set b="a" limit 1;
 update t1 set b="a" where b="b" limit 2;
 delete from t1 where b="test" limit 1;
 delete from t1 where a+0=1 limit 2;
+SET MAX_JOIN_SIZE=2;
+SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS;
+@@max_join_size	@@sql_big_selects
+2	0
+insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
+SELECT * from t1;
+The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok
+SET SQL_BIG_SELECTS=1;
+SELECT * from t1;
+a	b
+3	a
+2	test2
+4	a
+5	a
+SET MAX_JOIN_SIZE=2;
+SELECT * from t1;
+The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok
+SET MAX_JOIN_SIZE=DEFAULT;
+SELECT * from t1;
+a	b
+3	a
+2	test2
+4	a
+5	a
 drop table t1;
 SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT;
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index db209c5855c..86647e845a9 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -102,13 +102,39 @@ select status from t1;
 status
 1
 drop table t1;
-create table t1 (a int not null, b int not null);
-insert into t1 values (1,1),(1,2),(1,3);
-update t1 set b=4 where a=1 order by b asc limit 1;
-update t1 set b=4 where a=1 order by b desc limit 1;
-select * from t1;
+create table t1 (a int not null, b int not null, key (a));
+insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
+SET @tmp=0;
+update t1 set b=(@tmp:=@tmp+1) order by a;
+update t1 set b=99 where a=1 order by b asc limit 1;
+select * from t1 order by a,b;
 a	b
-1	4
 1	2
-1	4
+1	3
+1	99
+2	4
+2	5
+2	6
+3	7
+3	8
+3	9
+3	10
+3	11
+3	12
+update t1 set b=100 where a=1 order by b desc limit 2;
+update t1 set a=a+10+b where a=1 order by b;
+select * from t1 order by a,b;
+a	b
+2	4
+2	5
+2	6
+3	7
+3	8
+3	9
+3	10
+3	11
+3	12
+13	2
+111	100
+111	100
 drop table t1;
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 9e5ee4b8b2a..67f4eb580a6 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -34,12 +34,15 @@ CREATE TABLE t2 (
 INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1');
 INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1');
 INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1');
+INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1');
+INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1');
 
 SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid;
 SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid ORDER BY NULL;
 SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid;
 SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid;
 SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid ORDER BY NULL;
+EXPLAIN SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid ORDER BY NULL;
 drop table test.t1,test.t2;
 
 #
@@ -236,7 +239,7 @@ CREATE TABLE t1 (
   key (score)
 );
 
-INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3);
+INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3);
 explain select userid,count(*) from t1 group by userid desc;
 explain select userid,count(*) from t1 group by userid desc order by null;
 select userid,count(*) from t1 group by userid desc;
@@ -255,8 +258,6 @@ explain select sql_big_result score,count(*) from t1 group by score desc order b
 select sql_big_result score,count(*) from t1 group by score desc;
 drop table t1;
 
-#
-
 # not purely group_by bug, but group_by is involved...
 
 create table t1 (a date default null, b date default null);
@@ -265,7 +266,6 @@ select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interva
 drop table t1;
 
 # Compare with hash keys
-#
 
 CREATE TABLE t1 (a char(1));
 INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL);
@@ -401,15 +401,7 @@ drop table t1,t2;
 #
 
 create table t1 (a int, b int);
-insert into t1 values (1, 4);
-insert into t1 values (10, 40);
-insert into t1 values (1, 4);
-insert into t1 values (10, 43);
-insert into t1 values (1, 4);
-insert into t1 values (10, 41);
-insert into t1 values (1, 4);
-insert into t1 values (10, 43);
-insert into t1 values (1, 4);
+insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
 select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;
 select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;
 select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index 2f7285e7898..88edaac190a 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -795,5 +795,58 @@ select * from t1;
 update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
 select * from t1;
 select * from t2;
+drop table t1,t2;
 
+#
+# Test that MySQL priorities clustered indexes
+#
+
+create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) type=innodb;
+create table t2 (a int not null auto_increment primary key, b int);
+insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
+insert into t2 (a) select b from t1;
+insert into t1 (b) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+select count(*) from t1;
+explain select a from t1 where a between 1 and 10000;
+explain select * from t1 where a between 1 and 10000;
+explain select * from t1 where c between 1 and 10000;
+update t1 set c=a;
+explain select * from t1 where c between 1 and 10000;
 drop table t1,t2;
+
+#
+# Test of UPDATE ... ORDER BY
+#
+
+create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) type=innodb;
+
+insert into t1 (id) values (null),(null),(null),(null),(null);
+update t1 set fk=69 where fk is null order by id limit 1;
+SELECT * from t1;
+drop table t1;
+
+create table t1 (a int not null, b int not null, key (a));
+insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
+SET @tmp=0;
+update t1 set b=(@tmp:=@tmp+1) order by a;
+update t1 set b=99 where a=1 order by b asc limit 1;
+update t1 set b=100 where a=1 order by b desc limit 2;
+update t1 set a=a+10+b where a=1 order by b;
+select * from t1 order by a,b;
+drop table t1;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 71c3643b2f4..40e82fb8473 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -248,7 +248,7 @@ drop table t1,t2;
 
 create table t1 (id int not null, str char(10), index(str));
 insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar");
-select * from t1 where str is not null;
+select * from t1 where str is not null order by id;
 select * from t1 where str is null;
 drop table t1;
 
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index d5543f69bd5..74b7f83b3a6 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -247,7 +247,7 @@ update t1,t2 set t1.b=t1.b+10 where t1.b=2;
 select * from t1;
 
 # Range key (in t1)
-update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t2.a=t1.a-100;
 select * from t1;
 select * from t2;
 
diff --git a/mysql-test/t/select_safe.test b/mysql-test/t/select_safe.test
index e76b7558997..a085cfee29d 100644
--- a/mysql-test/t/select_safe.test
+++ b/mysql-test/t/select_safe.test
@@ -4,7 +4,7 @@
 
 drop table if exists t1;
 SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9;
-create table t1 (a int primary key, b char(20));
+create table t1 (a int auto_increment primary key, b char(20));
 insert into t1 values(1,"test");
 SELECT SQL_BUFFER_RESULT * from t1;
 update t1 set b="a" where a=1;
@@ -15,18 +15,40 @@ update t1 set b="a" where a=1;
 select 1 from t1,t1 as t2,t1 as t3,t1 as t4;
 
 # The following should give errors:
-!$1175 update t1 set b="a";
-!$1175 update t1 set b="a" where b="test";
-!$1175 delete from t1;
-!$1175 delete from t1 where b="test";
-!$1175 delete from t1 where a+0=1;
-!$1104 select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5;
+--error 1175
+update t1 set b="a";
+--error 1175
+update t1 set b="a" where b="test";
+--error 1175
+delete from t1;
+--error 1175
+delete from t1 where b="test";
+--error 1175
+delete from t1 where a+0=1;
+--error 1104
+select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5;
 
 # The following should be ok:
 update t1 set b="a" limit 1;
 update t1 set b="a" where b="b" limit 2; 
 delete from t1 where b="test" limit 1;
 delete from t1 where a+0=1 limit 2;
+
+# Test SQL_BIG_SELECTS
+
+SET MAX_JOIN_SIZE=2;
+SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS;
+insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
+--error 1104
+SELECT * from t1;
+SET SQL_BIG_SELECTS=1;
+SELECT * from t1;
+SET MAX_JOIN_SIZE=2;
+--error 1104
+SELECT * from t1;
+SET MAX_JOIN_SIZE=DEFAULT;
+SELECT * from t1;
+
 drop table t1;
 
 SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT;
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index 76a27b62206..359ae815197 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -81,9 +81,13 @@ drop table t1;
 # Test of ORDER BY
 #
 
-create table t1 (a int not null, b int not null);
-insert into t1 values (1,1),(1,2),(1,3);
-update t1 set b=4 where a=1 order by b asc limit 1;
-update t1 set b=4 where a=1 order by b desc limit 1;
-select * from t1;
+create table t1 (a int not null, b int not null, key (a));
+insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
+SET @tmp=0;
+update t1 set b=(@tmp:=@tmp+1) order by a;
+update t1 set b=99 where a=1 order by b asc limit 1;
+select * from t1 order by a,b;
+update t1 set b=100 where a=1 order by b desc limit 2;
+update t1 set a=a+10+b where a=1 order by b;
+select * from t1 order by a,b;
 drop table t1;