summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/group_by.result42
-rw-r--r--mysql-test/r/innodb.result71
-rw-r--r--mysql-test/r/join_outer.result4
-rw-r--r--mysql-test/r/multi_update.result20
-rw-r--r--mysql-test/r/null_key.result2
-rw-r--r--mysql-test/r/update.result40
-rw-r--r--mysql-test/t/group_by.test16
-rw-r--r--mysql-test/t/innodb.test53
-rw-r--r--mysql-test/t/join_outer.test2
-rw-r--r--mysql-test/t/multi_update.test2
-rw-r--r--mysql-test/t/update.test14
11 files changed, 203 insertions, 63 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index f73c4d7f8af..43f1bf8f6d4 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -49,6 +49,10 @@ SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1
userid MIN(t1.score+0.0)
1 1.0
2 2.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 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
drop table test.t1,test.t2;
CREATE TABLE t1 (
PID int(10) unsigned NOT NULL auto_increment,
@@ -242,34 +246,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 +282,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 +297,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 +539,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/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..8c111430eea 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -40,6 +40,7 @@ SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t
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 +237,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 +256,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 +264,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 +399,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/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;
View Lorry Controller Status