summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/bdb.result31
-rw-r--r--mysql-test/r/grant.result54
-rw-r--r--mysql-test/r/mix_innodb_myisam_binlog.result206
-rw-r--r--mysql-test/r/myisam.result22
-rw-r--r--mysql-test/r/range.result19
5 files changed, 202 insertions, 130 deletions
diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result
index fd2f7b7e49b..f619775aa97 100644
--- a/mysql-test/r/bdb.result
+++ b/mysql-test/r/bdb.result
@@ -1114,3 +1114,34 @@ a b
select * from t2;
a b
drop table t1,t2;
+create table t1 (x int not null, index(x)) type=bdb;
+insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+select * from t1 where x <= 10 and x >= 7;
+x
+7
+8
+9
+10
+select * from t1 where x <= 10 and x >= 7 order by x;
+x
+7
+8
+9
+10
+select * from t1 where x <= 10 and x >= 7 order by x desc;
+x
+10
+9
+8
+7
+select * from t1 where x <= 8 and x >= 5 order by x desc;
+x
+8
+7
+6
+5
+select * from t1 where x < 8 and x > 5 order by x desc;
+x
+7
+6
+drop table t1;
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index df6f8fe3615..0a54072d260 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -91,39 +91,39 @@ show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT, INSERT, INSERT (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
-REVOKE insert,insert (a) on t1 from mysqltest_1@localhost;
-GRANT references on t1 to mysqltest_1@localhost;
+REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
-GRANT SELECT, REFERENCES, REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
+GRANT REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
+GRANT select,references on t1 to mysqltest_1@localhost;
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
table_priv column_priv
Select,References References
-grant all on test.* to user1@localhost with grant option;
-revoke all on test.* from user1@localhost;
-show grants for user1@localhost;
-Grants for user1@localhost
-GRANT USAGE ON *.* TO 'user1'@'localhost'
-GRANT USAGE ON `test`.* TO 'user1'@'localhost' WITH GRANT OPTION
-revoke grant option on test.* from user1@localhost;
-show grants for user1@localhost;
-Grants for user1@localhost
-GRANT USAGE ON *.* TO 'user1'@'localhost'
-grant all on test.t1 to user2@localhost with grant option;
-revoke all on test.t1 from user2@localhost;
-show grants for user2@localhost;
-Grants for user2@localhost
-GRANT USAGE ON *.* TO 'user2'@'localhost'
-GRANT USAGE ON `test`.`t1` TO 'user2'@'localhost' WITH GRANT OPTION
-revoke grant option on test.t1 from user2@localhost;
-show grants for user2@localhost;
-Grants for user2@localhost
-GRANT USAGE ON *.* TO 'user2'@'localhost'
-delete from mysql.user where user='mysqltest_1';
-delete from mysql.db where user='mysqltest_1';
-delete from mysql.tables_priv where user='mysqltest_1';
-delete from mysql.columns_priv where user='mysqltest_1';
+grant all on test.* to mysqltest_3@localhost with grant option;
+revoke all on test.* from mysqltest_3@localhost;
+show grants for mysqltest_3@localhost;
+Grants for mysqltest_3@localhost
+GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
+GRANT USAGE ON `test`.* TO 'mysqltest_3'@'localhost' WITH GRANT OPTION
+revoke grant option on test.* from mysqltest_3@localhost;
+show grants for mysqltest_3@localhost;
+Grants for mysqltest_3@localhost
+GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
+grant all on test.t1 to mysqltest_2@localhost with grant option;
+revoke all on test.t1 from mysqltest_2@localhost;
+show grants for mysqltest_2@localhost;
+Grants for mysqltest_2@localhost
+GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost'
+GRANT USAGE ON `test`.`t1` TO 'mysqltest_2'@'localhost' WITH GRANT OPTION
+revoke grant option on test.t1 from mysqltest_2@localhost;
+show grants for mysqltest_2@localhost;
+Grants for mysqltest_2@localhost
+GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost'
+delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
+delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
+delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
+delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
flush privileges;
drop table t1;
GRANT FILE on mysqltest.* to mysqltest_1@localhost;
diff --git a/mysql-test/r/mix_innodb_myisam_binlog.result b/mysql-test/r/mix_innodb_myisam_binlog.result
index dd9876b4f90..85e92037ada 100644
--- a/mysql-test/r/mix_innodb_myisam_binlog.result
+++ b/mysql-test/r/mix_innodb_myisam_binlog.result
@@ -1,180 +1,180 @@
-drop table if exists ti, tm;
-create table ti (a int) type=innodb;
-create table tm (a int) type=myisam;
+drop table if exists t1, t2;
+create table t1 (a int) type=innodb;
+create table t2 (a int) type=myisam;
reset master;
begin;
-insert into ti values(1);
-insert into tm select * from ti;
+insert into t1 values(1);
+insert into t2 select * from t1;
commit;
show binlog events from 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
-master-bin.000001 79 Query 1 79 use ; BEGIN
-master-bin.000001 119 Query 1 79 use ; insert into ti values(1)
-master-bin.000001 178 Query 1 79 use ; insert into tm select * from ti
-master-bin.000001 244 Query 1 244 use ; COMMIT
-delete from ti;
-delete from tm;
+master-bin.000001 79 Query 1 79 use `test`; BEGIN
+master-bin.000001 119 Query 1 79 use `test`; insert into t1 values(1)
+master-bin.000001 178 Query 1 79 use `test`; insert into t2 select * from t1
+master-bin.000001 244 Query 1 244 use `test`; COMMIT
+delete from t1;
+delete from t2;
reset master;
begin;
-insert into ti values(2);
-insert into tm select * from ti;
+insert into t1 values(2);
+insert into t2 select * from t1;
rollback;
Warning: Some non-transactional changed tables couldn't be rolled back
show binlog events from 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
-master-bin.000001 79 Query 1 79 use ; BEGIN
-master-bin.000001 119 Query 1 79 use ; insert into ti values(2)
-master-bin.000001 178 Query 1 79 use ; insert into tm select * from ti
-master-bin.000001 244 Query 1 244 use ; ROLLBACK
-delete from ti;
-delete from tm;
+master-bin.000001 79 Query 1 79 use `test`; BEGIN
+master-bin.000001 119 Query 1 79 use `test`; insert into t1 values(2)
+master-bin.000001 178 Query 1 79 use `test`; insert into t2 select * from t1
+master-bin.000001 244 Query 1 244 use `test`; ROLLBACK
+delete from t1;
+delete from t2;
reset master;
begin;
-insert into ti values(3);
+insert into t1 values(3);
savepoint my_savepoint;
-insert into ti values(4);
-insert into tm select * from ti;
+insert into t1 values(4);
+insert into t2 select * from t1;
rollback to savepoint my_savepoint;
Warning: Some non-transactional changed tables couldn't be rolled back
commit;
show binlog events from 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
-master-bin.000001 79 Query 1 79 use ; BEGIN
-master-bin.000001 119 Query 1 79 use ; insert into ti values(3)
-master-bin.000001 178 Query 1 79 use ; savepoint my_savepoint
-master-bin.000001 235 Query 1 79 use ; insert into ti values(4)
-master-bin.000001 294 Query 1 79 use ; insert into tm select * from ti
-master-bin.000001 360 Query 1 79 use ; rollback to savepoint my_savepoint
-master-bin.000001 429 Query 1 429 use ; COMMIT
-delete from ti;
-delete from tm;
+master-bin.000001 79 Query 1 79 use `test`; BEGIN
+master-bin.000001 119 Query 1 79 use `test`; insert into t1 values(3)
+master-bin.000001 178 Query 1 79 use `test`; savepoint my_savepoint
+master-bin.000001 235 Query 1 79 use `test`; insert into t1 values(4)
+master-bin.000001 294 Query 1 79 use `test`; insert into t2 select * from t1
+master-bin.000001 360 Query 1 79 use `test`; rollback to savepoint my_savepoint
+master-bin.000001 429 Query 1 429 use `test`; COMMIT
+delete from t1;
+delete from t2;
reset master;
begin;
-insert into ti values(5);
+insert into t1 values(5);
savepoint my_savepoint;
-insert into ti values(6);
-insert into tm select * from ti;
+insert into t1 values(6);
+insert into t2 select * from t1;
rollback to savepoint my_savepoint;
Warning: Some non-transactional changed tables couldn't be rolled back
-insert into ti values(7);
+insert into t1 values(7);
commit;
-select a from ti order by a;
+select a from t1 order by a;
a
5
7
show binlog events from 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
-master-bin.000001 79 Query 1 79 use ; BEGIN
-master-bin.000001 119 Query 1 79 use ; insert into ti values(5)
-master-bin.000001 178 Query 1 79 use ; savepoint my_savepoint
-master-bin.000001 235 Query 1 79 use ; insert into ti values(6)
-master-bin.000001 294 Query 1 79 use ; insert into tm select * from ti
-master-bin.000001 360 Query 1 79 use ; rollback to savepoint my_savepoint
-master-bin.000001 429 Query 1 79 use ; insert into ti values(7)
-master-bin.000001 488 Query 1 488 use ; COMMIT
-delete from ti;
-delete from tm;
+master-bin.000001 79 Query 1 79 use `test`; BEGIN
+master-bin.000001 119 Query 1 79 use `test`; insert into t1 values(5)
+master-bin.000001 178 Query 1 79 use `test`; savepoint my_savepoint
+master-bin.000001 235 Query 1 79 use `test`; insert into t1 values(6)
+master-bin.000001 294 Query 1 79 use `test`; insert into t2 select * from t1
+master-bin.000001 360 Query 1 79 use `test`; rollback to savepoint my_savepoint
+master-bin.000001 429 Query 1 79 use `test`; insert into t1 values(7)
+master-bin.000001 488 Query 1 488 use `test`; COMMIT
+delete from t1;
+delete from t2;
reset master;
select get_lock("a",10);
get_lock("a",10)
1
begin;
-insert into ti values(8);
-insert into tm select * from ti;
+insert into t1 values(8);
+insert into t2 select * from t1;
select get_lock("a",10);
get_lock("a",10)
1
show binlog events from 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
-master-bin.000001 79 Query 1 79 use ; BEGIN
-master-bin.000001 119 Query 1 79 use ; insert into ti values(8)
-master-bin.000001 178 Query 1 79 use ; insert into tm select * from ti
-master-bin.000001 244 Query 1 244 use ; ROLLBACK
-delete from ti;
-delete from tm;
+master-bin.000001 79 Query 1 79 use `test`; BEGIN
+master-bin.000001 119 Query 1 79 use `test`; insert into t1 values(8)
+master-bin.000001 178 Query 1 79 use `test`; insert into t2 select * from t1
+master-bin.000001 244 Query 1 244 use `test`; ROLLBACK
+delete from t1;
+delete from t2;
reset master;
-insert into ti values(9);
-insert into tm select * from ti;
+insert into t1 values(9);
+insert into t2 select * from t1;
show binlog events from 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
-master-bin.000001 79 Query 1 79 use ; insert into ti values(9)
-master-bin.000001 138 Query 1 138 use ; insert into tm select * from ti
-delete from ti;
-delete from tm;
+master-bin.000001 79 Query 1 79 use `test`; insert into t1 values(9)
+master-bin.000001 138 Query 1 138 use `test`; insert into t2 select * from t1
+delete from t1;
+delete from t2;
reset master;
-insert into ti values(10);
+insert into t1 values(10);
begin;
-insert into tm select * from ti;
+insert into t2 select * from t1;
show binlog events from 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
-master-bin.000001 79 Query 1 79 use ; insert into ti values(10)
-master-bin.000001 139 Query 1 139 use ; insert into tm select * from ti
-insert into ti values(11);
+master-bin.000001 79 Query 1 79 use `test`; insert into t1 values(10)
+master-bin.000001 139 Query 1 139 use `test`; insert into t2 select * from t1
+insert into t1 values(11);
commit;
show binlog events from 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
-master-bin.000001 79 Query 1 79 use ; insert into ti values(10)
-master-bin.000001 139 Query 1 139 use ; insert into tm select * from ti
-master-bin.000001 205 Query 1 205 use ; BEGIN
-master-bin.000001 245 Query 1 205 use ; insert into ti values(11)
-master-bin.000001 305 Query 1 305 use ; COMMIT
-alter table tm type=INNODB;
-delete from ti;
-delete from tm;
+master-bin.000001 79 Query 1 79 use `test`; insert into t1 values(10)
+master-bin.000001 139 Query 1 139 use `test`; insert into t2 select * from t1
+master-bin.000001 205 Query 1 205 use `test`; BEGIN
+master-bin.000001 245 Query 1 205 use `test`; insert into t1 values(11)
+master-bin.000001 305 Query 1 305 use `test`; COMMIT
+alter table t2 type=INNODB;
+delete from t1;
+delete from t2;
reset master;
begin;
-insert into ti values(12);
-insert into tm select * from ti;
+insert into t1 values(12);
+insert into t2 select * from t1;
commit;
show binlog events from 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
-master-bin.000001 79 Query 1 79 use ; BEGIN
-master-bin.000001 119 Query 1 79 use ; insert into ti values(12)
-master-bin.000001 179 Query 1 79 use ; insert into tm select * from ti
-master-bin.000001 245 Query 1 245 use ; COMMIT
-delete from ti;
-delete from tm;
+master-bin.000001 79 Query 1 79 use `test`; BEGIN
+master-bin.000001 119 Query 1 79 use `test`; insert into t1 values(12)
+master-bin.000001 179 Query 1 79 use `test`; insert into t2 select * from t1
+master-bin.000001 245 Query 1 245 use `test`; COMMIT
+delete from t1;
+delete from t2;
reset master;
begin;
-insert into ti values(13);
-insert into tm select * from ti;
+insert into t1 values(13);
+insert into t2 select * from t1;
rollback;
show binlog events from 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
-delete from ti;
-delete from tm;
+delete from t1;
+delete from t2;
reset master;
begin;
-insert into ti values(14);
+insert into t1 values(14);
savepoint my_savepoint;
-insert into ti values(15);
-insert into tm select * from ti;
+insert into t1 values(15);
+insert into t2 select * from t1;
rollback to savepoint my_savepoint;
commit;
show binlog events from 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
-master-bin.000001 79 Query 1 79 use ; BEGIN
-master-bin.000001 119 Query 1 79 use ; insert into ti values(14)
-master-bin.000001 179 Query 1 179 use ; COMMIT
-delete from ti;
-delete from tm;
+master-bin.000001 79 Query 1 79 use `test`; BEGIN
+master-bin.000001 119 Query 1 79 use `test`; insert into t1 values(14)
+master-bin.000001 179 Query 1 179 use `test`; COMMIT
+delete from t1;
+delete from t2;
reset master;
begin;
-insert into ti values(16);
+insert into t1 values(16);
savepoint my_savepoint;
-insert into ti values(17);
-insert into tm select * from ti;
+insert into t1 values(17);
+insert into t2 select * from t1;
rollback to savepoint my_savepoint;
-insert into ti values(18);
+insert into t1 values(18);
commit;
-select a from ti order by a;
+select a from t1 order by a;
a
16
18
show binlog events from 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
-master-bin.000001 79 Query 1 79 use ; BEGIN
-master-bin.000001 119 Query 1 79 use ; insert into ti values(16)
-master-bin.000001 179 Query 1 79 use ; insert into ti values(18)
-master-bin.000001 239 Query 1 239 use ; COMMIT
-drop table ti,tm;
+master-bin.000001 79 Query 1 79 use `test`; BEGIN
+master-bin.000001 119 Query 1 79 use `test`; insert into t1 values(16)
+master-bin.000001 179 Query 1 79 use `test`; insert into t1 values(18)
+master-bin.000001 239 Query 1 239 use `test`; COMMIT
+drop table t1,t2;
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 4fa71dbaecc..49475c9cbec 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -376,6 +376,28 @@ explain select * from t1 use index() where c=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
drop table t1,t2;
+create table t1 (a int not null auto_increment primary key, b varchar(255));
+insert into t1 (b) values (repeat('a',100)),(repeat('b',100)),(repeat('c',100));
+update t1 set b=repeat(left(b,1),200) where a=1;
+delete from t1 where (a & 1)= 0;
+update t1 set b=repeat('e',200) where a=1;
+flush tables;
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+update t1 set b=repeat(left(b,1),255) where a between 1 and 5;
+update t1 set b=repeat(left(b,1),10) where a between 32 and 43;
+update t1 set b=repeat(left(b,1),2) where a between 64 and 66;
+update t1 set b=repeat(left(b,1),65) where a between 67 and 70;
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+insert into t1 (b) values (repeat('z',100));
+update t1 set b="test" where left(b,1) > 'n';
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+drop table t1;
CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)) TYPE=MyISAM;
ERROR 42000: This version of MySQL doesn't yet support 'RTREE INDEX'
create table t1 (a int, b varchar(200), c text not null) checksum=1;
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index df84dd55d04..ec090cf4b94 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -253,4 +253,23 @@ explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref y y 5 const 1 Using where
1 SIMPLE t2 range x x 5 NULL 2 Using where
+explain select count(*) from t1 where x in (1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range x x 5 NULL 1 Using where; Using index
+explain select count(*) from t1 where x in (1,2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range x x 5 NULL 2 Using where; Using index
drop table t1;
+CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1), KEY i2 (key1));
+INSERT INTO t1 VALUES (0),(0),(1),(1);
+CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya));
+INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2);
+explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
+table type possible_keys key key_len ref rows Extra
+t2 ref j1 j1 4 const 1 Using where; Using index
+t1 ALL i1,i2 NULL NULL NULL 4 Range checked for each record (index map: 3)
+explain select * from t1 force index(i2), t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
+table type possible_keys key key_len ref rows Extra
+t2 ref j1 j1 4 const 1 Using where; Using index
+t1 ALL i2 NULL NULL NULL 4 Range checked for each record (index map: 2)
+DROP TABLE t1,t2;
View Lorry Controller Status