summaryrefslogtreecommitdiff
path: root/mysql-test/t
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t')
-rw-r--r--mysql-test/t/backup.test2
-rw-r--r--mysql-test/t/count_distinct.test1
-rw-r--r--mysql-test/t/func_crypt.test2
-rw-r--r--mysql-test/t/grant_cache.test17
-rw-r--r--mysql-test/t/group_by.test21
-rw-r--r--mysql-test/t/rpl000009.test8
-rw-r--r--mysql-test/t/rpl000015.test4
-rw-r--r--mysql-test/t/rpl000017.test4
-rw-r--r--mysql-test/t/rpl_insert_id.test39
-rw-r--r--mysql-test/t/rpl_master_pos_wait.test9
-rw-r--r--mysql-test/t/rpl_rotate_logs.test4
11 files changed, 95 insertions, 16 deletions
diff --git a/mysql-test/t/backup.test b/mysql-test/t/backup.test
index ad332cde646..55fe140850d 100644
--- a/mysql-test/t/backup.test
+++ b/mysql-test/t/backup.test
@@ -4,7 +4,7 @@ connection con1;
set SQL_LOG_BIN=0;
drop table if exists t1;
create table t1(n int);
---replace_result "errno = 2" "errno = X" "errno = 22" "errno = X" "errno = 23" "errno = X"
+--replace_result "errno = 1" "errno = X" "errno = 2" "errno = X" "errno = 22" "errno = X" "errno = 23" "errno = X"
backup table t1 to '../bogus';
backup table t1 to '../tmp';
drop table t1;
diff --git a/mysql-test/t/count_distinct.test b/mysql-test/t/count_distinct.test
index 3d795d44821..f3584686f69 100644
--- a/mysql-test/t/count_distinct.test
+++ b/mysql-test/t/count_distinct.test
@@ -31,6 +31,7 @@ insert into t1 values ('Berkeley Public2','Berkeley');
insert into t1 values ('NYC Lib','New York');
select t2.isbn,city,t1.libname,count(t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city,t1.libname;
select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city having count(distinct t1.libname) > 1;
+select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city having count(distinct concat(t1.libname,'a')) > 1;
drop table t1, t2, t3;
#
diff --git a/mysql-test/t/func_crypt.test b/mysql-test/t/func_crypt.test
index f01504d3691..55c0d6d3b9d 100644
--- a/mysql-test/t/func_crypt.test
+++ b/mysql-test/t/func_crypt.test
@@ -1,3 +1,5 @@
+-- source include/have_crypt.inc
+
select length(encrypt('foo', 'ff')) <> 0;
--replace_result $1$aa$4OSUA5cjdx0RUQ08opV27/ aaqPiZY5xR5l.
select password('test'),length(encrypt('test')),encrypt('test','aa');
diff --git a/mysql-test/t/grant_cache.test b/mysql-test/t/grant_cache.test
index 09ce1021fbf..7dfbb3b8d83 100644
--- a/mysql-test/t/grant_cache.test
+++ b/mysql-test/t/grant_cache.test
@@ -6,7 +6,7 @@
drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
reset query cache;
flush status;
-connect (root,localhost,root,,test,0,master.sock);
+connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock);
connection root;
create database if not exists mysqltest;
@@ -17,7 +17,7 @@ insert into mysqltest.t2 values (3,3,3);
create table test.t1 (a char (10));
insert into test.t1 values ("test.t1");
select * from t1;
-connect (root2,localhost,root,,mysqltest,0,master.sock);
+connect (root2,localhost,root,,mysqltest,$MASTER_MYPORT,master.sock);
connection root2;
# put queries in cache
select * from t1;
@@ -35,7 +35,7 @@ grant SELECT on test.t1 to mysqltest_2@localhost;
grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost;
# The following queries should be fetched from cache
-connect (user1,localhost,mysqltest_1,,mysqltest,0,master.sock);
+connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,master.sock);
connection user1;
select "user1";
select * from t1;
@@ -47,13 +47,14 @@ show status like "Qcache_hits";
show status like "Qcache_not_cached";
# The following queries should be fetched from cache
-connect (user2,localhost,mysqltest_2,,mysqltest,0,master.sock);
+connect (user2,localhost,mysqltest_2,,mysqltest,$MASTER_MYPORT,master.sock);
connection user2;
select "user2";
select * from t1;
select a from t1;
select c from t1;
select * from mysqltest.t1,test.t1;
+--replace_result 127.0.0.1 localhost
--error 1142
select * from t2;
show status like "Qcache_queries_in_cache";
@@ -61,16 +62,20 @@ show status like "Qcache_hits";
show status like "Qcache_not_cached";
# The following queries should not be fetched from cache
-connect (user3,localhost,mysqltest_3,,mysqltest,0,master.sock);
+connect (user3,localhost,mysqltest_3,,mysqltest,$MASTER_MYPORT,master.sock);
connection user3;
select "user3";
+--replace_result 127.0.0.1 localhost
--error 1143
select * from t1;
select a from t1;
+--replace_result 127.0.0.1 localhost
--error 1143
select c from t1;
+--replace_result 127.0.0.1 localhost
--error 1142
select * from t2;
+--replace_result 127.0.0.1 localhost
--error 1143
select mysqltest.t1.c from test.t1,mysqltest.t1;
show status like "Qcache_queries_in_cache";
@@ -78,7 +83,7 @@ show status like "Qcache_hits";
show status like "Qcache_not_cached";
# Connect without a database
-connect (user4,localhost,mysqltest_1,,*NO-ONE*,0,master.sock);
+connect (user4,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,master.sock);
connection user4;
select "user4";
--error 1046
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index bc01577c249..98b1ee238b1 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -36,8 +36,10 @@ 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');
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;
drop table test.t1,test.t2;
#
@@ -100,6 +102,7 @@ INSERT INTO t2 VALUES (91,2);
INSERT INTO t2 VALUES (92,2);
SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid;
+SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY NULL;
SELECT HIGH_PRIORITY cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY surname, firstname;
drop table t1,t2;
@@ -234,16 +237,20 @@ CREATE TABLE t1 (
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;
+explain select userid,count(*) from t1 group by userid desc order by null;
select userid,count(*) from t1 group by userid desc;
select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3);
select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*));
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid;
+explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null;
select spid,count(*) from t1 where spid between 1 and 2 group by spid;
select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
explain select sql_big_result spid,sum(userid) from t1 group by spid desc;
+explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null;
select sql_big_result spid,sum(userid) from t1 group by spid desc;
explain select sql_big_result score,count(*) from t1 group by score desc;
+explain select sql_big_result score,count(*) from t1 group by score desc order by null;
select sql_big_result score,count(*) from t1 group by score desc;
drop table t1;
@@ -365,3 +372,17 @@ select a,count(*) from t1 group by a;
set option sql_big_tables=1;
select a,count(*) from t1 group by a;
drop table t1;
+
+#
+# Test of GROUP BY ... ORDER BY NULL optimization
+#
+
+create table t1 (a int not null, b int not null);
+insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1);
+create table t2 (a int not null, b int not null, key(a));
+insert into t2 values (1,3),(3,1),(2,2),(1,1);
+select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
+select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
+explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
+explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
+drop table t1,t2;
diff --git a/mysql-test/t/rpl000009.test b/mysql-test/t/rpl000009.test
index 3d0827718ec..5f55355271a 100644
--- a/mysql-test/t/rpl000009.test
+++ b/mysql-test/t/rpl000009.test
@@ -9,6 +9,7 @@ create database bar;
save_master_pos;
connection slave;
sync_with_master;
+create database foo;
drop table if exists foo.foo;
create table foo.foo (n int);
insert into foo.foo values(4);
@@ -24,13 +25,14 @@ connection slave;
sync_with_master;
select foo.foo.n,bar.bar.m from foo.foo,bar.bar;
connection master;
-drop database if exists bar;
+drop database bar;
drop database if exists foo;
save_master_pos;
connection slave;
sync_with_master;
-drop database if exists bar;
-drop database if exists foo;
+--error 1008
+drop database bar;
+drop database foo;
# Now let's test load data from master
diff --git a/mysql-test/t/rpl000015.test b/mysql-test/t/rpl000015.test
index c42e14699c5..26d32ea3e11 100644
--- a/mysql-test/t/rpl000015.test
+++ b/mysql-test/t/rpl000015.test
@@ -1,5 +1,5 @@
-connect (master,localhost,root,,test,0,master.sock);
-connect (slave,localhost,root,,test,0, slave.sock);
+connect (master,localhost,root,,test,$MASTER_MYPORT,master.sock);
+connect (slave,localhost,root,,test,$SLAVE_MYPORT,slave.sock);
connection master;
reset master;
show master status;
diff --git a/mysql-test/t/rpl000017.test b/mysql-test/t/rpl000017.test
index f346ca39ca0..d4f41f1e374 100644
--- a/mysql-test/t/rpl000017.test
+++ b/mysql-test/t/rpl000017.test
@@ -1,5 +1,5 @@
-connect (master,localhost,root,,test,0,master.sock);
-connect (slave,localhost,root,,test,0,slave.sock);
+connect (master,localhost,root,,test,$MASTER_MYPORT,master.sock);
+connect (slave,localhost,root,,test,$SLAVE_MYPORT,slave.sock);
connection master;
reset master;
grant replication slave on *.* to replicate@localhost identified by 'aaaaaaaaaaaaaaab';
diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test
new file mode 100644
index 00000000000..3f3636d3082
--- /dev/null
+++ b/mysql-test/t/rpl_insert_id.test
@@ -0,0 +1,39 @@
+#see if queries that use both
+#auto_increment and LAST_INSERT_ID()
+#are replicated well
+source include/master-slave.inc;
+connection master;
+drop table if exists t1;
+create table t1(a int auto_increment, key(a));
+create table t2(b int auto_increment, c int, key(b));
+insert into t1 values (1),(2),(3);
+insert into t1 values (null);
+insert into t2 values (null,last_insert_id());
+save_master_pos;
+connection slave;
+sync_with_master;
+select * from t1;
+select * from t2;
+connection master;
+#check if multi-line inserts,
+#which set last_insert_id to the first id inserted,
+#are replicated the same way
+drop table t1;
+drop table t2;
+create table t1(a int auto_increment, key(a));
+create table t2(b int auto_increment, c int, key(b));
+insert into t1 values (10);
+insert into t1 values (null),(null),(null);
+insert into t2 values (5,0);
+insert into t2 values (null,last_insert_id());
+save_master_pos;
+connection slave;
+sync_with_master;
+select * from t1;
+select * from t2;
+connection master;
+drop table t1;
+drop table t2;
+save_master_pos;
+connection slave;
+sync_with_master;
diff --git a/mysql-test/t/rpl_master_pos_wait.test b/mysql-test/t/rpl_master_pos_wait.test
new file mode 100644
index 00000000000..a6aae222a89
--- /dev/null
+++ b/mysql-test/t/rpl_master_pos_wait.test
@@ -0,0 +1,9 @@
+# See if master_pos_wait(,,timeout)
+# Terminates with "timeout expired" (-1)
+source include/master-slave.inc;
+save_master_pos;
+connection slave;
+sync_with_master;
+# Ask for a master log that has certainly not been reached yet
+# timeout= 10 seconds
+select master_pos_wait('master-bin.999999',0,10);
diff --git a/mysql-test/t/rpl_rotate_logs.test b/mysql-test/t/rpl_rotate_logs.test
index a9bb98932ed..6edbf363b1a 100644
--- a/mysql-test/t/rpl_rotate_logs.test
+++ b/mysql-test/t/rpl_rotate_logs.test
@@ -9,9 +9,9 @@
# changes
# - Test creating a duplicate key error and recover from it
#
-connect (master,localhost,root,,test,0,master.sock);
+connect (master,localhost,root,,test,$MASTER_MYPORT,master.sock);
drop table if exists t1, t2, t3, t4;
-connect (slave,localhost,root,,test,0,slave.sock);
+connect (slave,localhost,root,,test,$SLAVE_MYPORT,slave.sock);
system cat /dev/null > var/slave-data/master.info;
system chmod 000 var/slave-data/master.info;
connection slave;
View Lorry Controller Status