diff options
Diffstat (limited to 'mysql-test/suite/handler/innodb.result')
| -rw-r--r-- | mysql-test/suite/handler/innodb.result | 690 |
1 files changed, 690 insertions, 0 deletions
diff --git a/mysql-test/suite/handler/innodb.result b/mysql-test/suite/handler/innodb.result new file mode 100644 index 00000000000..a8700c8b4bb --- /dev/null +++ b/mysql-test/suite/handler/innodb.result @@ -0,0 +1,690 @@ +SET SESSION STORAGE_ENGINE = InnoDB; +drop table if exists t1,t3,t4,t5; +create table t1 (a int, b char(10), key a (a), key b (a,b)); +insert into t1 values +(17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), +(14,"aaa"),(16,"ccc"),(16,"xxx"), +(20,"ggg"),(21,"hhh"),(22,"iii"); +handler t1 open as t2; +handler t2 read a first; +a b +14 aaa +handler t2 read a next; +a b +16 ccc +handler t2 read a next; +a b +16 xxx +handler t2 read a prev; +a b +16 ccc +handler t2 read a last; +a b +22 iii +handler t2 read a prev; +a b +21 hhh +handler t2 read a prev; +a b +20 ggg +handler t2 read a first; +a b +14 aaa +handler t2 read a prev; +a b +handler t2 read a last; +a b +22 iii +handler t2 read a prev; +a b +21 hhh +handler t2 read a next; +a b +22 iii +handler t2 read a next; +a b +handler t2 read a=(15); +a b +handler t2 read a=(16); +a b +16 ccc +handler t2 read a=(19,"fff"); +ERROR 42000: Too many key parts specified; max 1 parts allowed +handler t2 read b=(19,"fff"); +a b +19 fff +handler t2 read b=(19,"yyy"); +a b +19 yyy +handler t2 read b=(19); +a b +19 fff +handler t1 read a last; +ERROR 42S02: Unknown table 't1' in HANDLER +handler t2 read a=(11); +a b +handler t2 read a>=(11); +a b +14 aaa +handler t2 read a=(18); +a b +18 eee +handler t2 read a>=(18); +a b +18 eee +handler t2 read a>(18); +a b +19 fff +handler t2 read a<=(18); +a b +18 eee +handler t2 read a<(18); +a b +17 ddd +handler t2 read a=(15); +a b +handler t2 read a>=(15); +a b +16 ccc +handler t2 read a>(15); +a b +16 ccc +handler t2 read a<=(15); +a b +14 aaa +handler t2 read a<(15); +a b +14 aaa +handler t2 read a=(54); +a b +handler t2 read a>=(54); +a b +handler t2 read a>(54); +a b +handler t2 read a<=(54); +a b +22 iii +handler t2 read a<(54); +a b +22 iii +handler t2 read a=(1); +a b +handler t2 read a>=(1); +a b +14 aaa +handler t2 read a>(1); +a b +14 aaa +handler t2 read a<=(1); +a b +handler t2 read a<(1); +a b +handler t2 read a first limit 5; +a b +14 aaa +16 ccc +16 xxx +17 ddd +18 eee +handler t2 read a next limit 3; +a b +19 fff +19 yyy +20 ggg +handler t2 read a prev limit 10; +a b +19 yyy +19 fff +18 eee +17 ddd +16 xxx +16 ccc +14 aaa +handler t2 read a>=(16) limit 4; +a b +16 ccc +16 xxx +17 ddd +18 eee +handler t2 read a>=(16) limit 2,2; +a b +17 ddd +18 eee +select * from t1 where a>=16 limit 2,2; +a b +17 ddd +18 eee +handler t2 read a last limit 3; +a b +22 iii +21 hhh +20 ggg +handler t2 read a=(16) limit 1,3; +a b +16 xxx +handler t2 read a=(19); +a b +19 fff +handler t2 read a=(19) where b="yyy"; +a b +19 yyy +handler t2 read first; +a b +17 ddd +handler t2 read next; +a b +18 eee +handler t2 read next; +a b +19 fff +handler t2 close; +handler t1 open; +handler t1 read a next; +a b +14 aaa +handler t1 read a next; +a b +16 ccc +handler t1 close; +handler t1 open; +handler t1 read a prev; +a b +22 iii +handler t1 read a prev; +a b +21 hhh +handler t1 close; +handler t1 open as t2; +handler t2 read first; +a b +17 ddd +alter table t1 engine = InnoDB; +handler t2 read first; +ERROR 42S02: Unknown table 't2' in HANDLER +handler t1 open; +handler t1 read a=(16) limit 1,3; +a b +16 xxx +flush tables; +handler t1 read a=(16) limit 1,3; +a b +16 xxx +handler t1 close; +handler t1 open; +prepare stmt from 'handler t1 read a=(?) limit ?,?'; +set @a=16,@b=1,@c=100; +execute stmt using @a,@b,@c; +a b +16 xxx +set @a=16,@b=2,@c=1; +execute stmt using @a,@b,@c; +a b +set @a=16,@b=0,@c=2; +execute stmt using @a,@b,@c; +a b +16 ccc +16 xxx +deallocate prepare stmt; +prepare stmt from 'handler t1 read a next limit ?'; +handler t1 read a>=(11); +a b +14 aaa +set @a=3; +execute stmt using @a; +a b +16 ccc +16 xxx +17 ddd +execute stmt using @a; +a b +18 eee +19 fff +19 yyy +execute stmt using @a; +a b +20 ggg +21 hhh +22 iii +deallocate prepare stmt; +prepare stmt from 'handler t1 read b prev limit ?'; +execute stmt using @a; +a b +22 iii +21 hhh +20 ggg +execute stmt using @a; +a b +19 yyy +19 fff +18 eee +execute stmt using @a; +a b +17 ddd +16 xxx +16 ccc +execute stmt using @a; +a b +14 aaa +deallocate prepare stmt; +prepare stmt from 'handler t1 read b=(?,?)'; +set @a=14, @b='aaa'; +execute stmt using @a,@b; +a b +14 aaa +set @a=14, @b='not found'; +execute stmt using @a,@b; +a b +deallocate prepare stmt; +prepare stmt from 'handler t1 read b=(1+?) limit 10'; +set @a=15; +execute stmt using @a; +a b +16 ccc +16 xxx +execute stmt using @a; +a b +16 ccc +16 xxx +deallocate prepare stmt; +prepare stmt from 'handler t1 read a>=(?) where a < ? limit 5'; +set @a=15, @b=20; +execute stmt using @a,@b; +a b +16 ccc +16 xxx +17 ddd +18 eee +19 fff +execute stmt using @a,@b; +a b +16 ccc +16 xxx +17 ddd +18 eee +19 fff +deallocate prepare stmt; +prepare stmt from 'handler t1 read a=(?)'; +set @a=16; +execute stmt using @a; +a b +16 ccc +alter table t1 add c int; +execute stmt using @a; +ERROR 42S02: Unknown table 't1' in HANDLER +deallocate prepare stmt; +handler t1 close; +ERROR 42S02: Unknown table 't1' in HANDLER +handler t1 open; +prepare stmt from 'handler t1 read a=(?)'; +flush tables; +set @a=16; +execute stmt using @a; +ERROR HY000: Prepared statement needs to be re-prepared +deallocate prepare stmt; +handler t1 close; +handler t1 open as t2; +drop table t1; +create table t1 (a int not null); +insert into t1 values (17); +handler t2 read first; +ERROR 42S02: Unknown table 't2' in HANDLER +handler t1 open as t2; +alter table t1 engine=CSV; +handler t2 read first; +ERROR 42S02: Unknown table 't2' in HANDLER +drop table t1; +create table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6); +delete from t1 limit 2; +handler t1 open; +handler t1 read first; +a +3 +handler t1 read first limit 1,1; +a +4 +handler t1 read first limit 2,2; +a +5 +6 +delete from t1 limit 3; +handler t1 read first; +a +6 +drop table t1; +create table t1(a int, index (a)); +insert into t1 values (1), (2), (3); +handler t1 open; +handler t1 read a=(W); +ERROR 42S22: Unknown column 'W' in 'field list' +handler t1 read a=(a); +ERROR HY000: Incorrect arguments to HANDLER ... READ +drop table t1; +create table t1 (a char(5)); +insert into t1 values ("Ok"); +handler t1 open as t; +handler t read first; +a +Ok +use mysql; +handler t read first; +a +Ok +handler t close; +handler test.t1 open as t; +handler t read first; +a +Ok +handler t close; +use test; +drop table t1; +create table t1 ( a int, b int, INDEX a (a) ); +insert into t1 values (1,2), (2,1); +handler t1 open; +handler t1 read a=(1) where b=2; +a b +1 2 +handler t1 read a=(1) where b=3; +a b +handler t1 read a=(1) where b=1; +a b +handler t1 close; +drop table t1; +create table t1 (c1 char(20)); +insert into t1 values ("t1"); +handler t1 open as h1; +handler h1 read first limit 9; +c1 +t1 +create table t2 (c1 char(20)); +insert into t2 values ("t2"); +handler t2 open as h2; +handler h2 read first limit 9; +c1 +t2 +create table t3 (c1 char(20)); +insert into t3 values ("t3"); +handler t3 open as h3; +handler h3 read first limit 9; +c1 +t3 +create table t4 (c1 char(20)); +insert into t4 values ("t4"); +handler t4 open as h4; +handler h4 read first limit 9; +c1 +t4 +create table t5 (c1 char(20)); +insert into t5 values ("t5"); +handler t5 open as h5; +handler h5 read first limit 9; +c1 +t5 +alter table t1 engine=MyISAM; +handler h1 read first limit 9; +ERROR 42S02: Unknown table 'h1' in HANDLER +handler h2 read first limit 9; +c1 +t2 +handler h3 read first limit 9; +c1 +t3 +handler h4 read first limit 9; +c1 +t4 +handler h5 read first limit 9; +c1 +t5 +alter table t5 engine=MyISAM; +handler h1 read first limit 9; +ERROR 42S02: Unknown table 'h1' in HANDLER +handler h2 read first limit 9; +c1 +t2 +handler h3 read first limit 9; +c1 +t3 +handler h4 read first limit 9; +c1 +t4 +handler h5 read first limit 9; +ERROR 42S02: Unknown table 'h5' in HANDLER +alter table t3 engine=MyISAM; +handler h1 read first limit 9; +ERROR 42S02: Unknown table 'h1' in HANDLER +handler h2 read first limit 9; +c1 +t2 +handler h3 read first limit 9; +ERROR 42S02: Unknown table 'h3' in HANDLER +handler h4 read first limit 9; +c1 +t4 +handler h5 read first limit 9; +ERROR 42S02: Unknown table 'h5' in HANDLER +handler h2 close; +handler h4 close; +handler t1 open as h1_1; +handler t1 open as h1_2; +handler t1 open as h1_3; +handler h1_1 read first limit 9; +c1 +t1 +handler h1_2 read first limit 9; +c1 +t1 +handler h1_3 read first limit 9; +c1 +t1 +alter table t1 engine=InnoDB; +handler h1_1 read first limit 9; +ERROR 42S02: Unknown table 'h1_1' in HANDLER +handler h1_2 read first limit 9; +ERROR 42S02: Unknown table 'h1_2' in HANDLER +handler h1_3 read first limit 9; +ERROR 42S02: Unknown table 'h1_3' in HANDLER +drop table t1; +drop table t2; +drop table t3; +drop table t4; +drop table t5; +create table t1 (c1 int); +insert into t1 values (1); +handler t1 open; +handler t1 read first; +c1 +1 +send the below to another connection, do not wait for the result +optimize table t1; +proceed with the normal connection +handler t1 read next; +c1 +1 +handler t1 close; +read the result from the other connection +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize, doing recreate + analyze instead +test.t1 optimize status OK +proceed with the normal connection +drop table t1; +CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2)); +INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2); +HANDLER t1 OPEN; +HANDLER t1 READ `primary` = (1, 1000); +no1 no2 +HANDLER t1 READ `primary` PREV; +no1 no2 +1 275 +HANDLER t1 READ `primary` = (1, 1000); +no1 no2 +HANDLER t1 READ `primary` NEXT; +no1 no2 +2 8 +DROP TABLE t1; +create table t1 (c1 int); +insert into t1 values (14397); +flush tables with read lock; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +send the below to another connection, do not wait for the result +drop table t1; +proceed with the normal connection +select * from t1; +c1 +14397 +unlock tables; +read the result from the other connection +proceed with the normal connection +select * from t1; +ERROR 42S02: Table 'test.t1' doesn't exist +drop table if exists t1; +Warnings: +Note 1051 Unknown table 't1' +create table t1 (a int not null) ENGINE=CSV; +--> client 2 +handler t1 open; +ERROR HY000: Table storage engine for 't1' doesn't have this option +--> client 1 +drop table t1; +create table t1 (a int); +handler t1 open as t1_alias; +handler t1_alias read a next; +ERROR 42000: Key 'a' doesn't exist in table 't1_alias' +handler t1_alias READ a next where inexistent > 0; +ERROR 42S22: Unknown column 'inexistent' in 'field list' +handler t1_alias read a next; +ERROR 42000: Key 'a' doesn't exist in table 't1_alias' +handler t1_alias READ a next where inexistent > 0; +ERROR 42S22: Unknown column 'inexistent' in 'field list' +handler t1_alias close; +drop table t1; +create temporary table t1 (a int, b char(1), key a (a), key b(a,b)); +insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), +(5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"),(9,'k'); +select a,b from t1; +a b +0 a +1 b +2 c +3 d +4 e +5 f +6 g +7 h +8 i +9 j +9 k +handler t1 open as a1; +handler a1 read a=(1); +a b +1 b +handler a1 read a next; +a b +2 c +handler a1 read a next; +a b +3 d +select a,b from t1; +ERROR HY000: Can't reopen table: 'a1' +handler a1 read a prev; +a b +2 c +handler a1 read a prev; +a b +1 b +handler a1 read a=(6) where b="g"; +a b +6 g +handler a1 close; +select a,b from t1; +a b +0 a +1 b +2 c +3 d +4 e +5 f +6 g +7 h +8 i +9 j +9 k +handler t1 open as a2; +handler a2 read a=(9); +a b +9 j +handler a2 read a next; +a b +9 k +handler a2 read a prev limit 2; +a b +9 j +8 i +handler a2 read a last; +a b +9 k +handler a2 read a prev; +a b +9 j +handler a2 close; +drop table t1; +create table t1 (a int); +create temporary table t2 (a int, key (a)); +handler t1 open as a1; +handler t2 open as a2; +handler a2 read a first; +a +drop table t1, t2; +handler a2 read a next; +ERROR 42S02: Unknown table 'a2' in HANDLER +handler a1 close; +ERROR 42S02: Unknown table 'a1' in HANDLER +create table t1 (a int, key (a)); +create table t2 like t1; +handler t1 open as a1; +handler t2 open as a2; +handler a1 read a first; +a +handler a2 read a first; +a +alter table t1 add b int; +handler a1 close; +ERROR 42S02: Unknown table 'a1' in HANDLER +handler a2 close; +drop table t1, t2; +create table t1 (a int, key (a)); +handler t1 open as a1; +handler a1 read a first; +a +rename table t1 to t2; +handler a1 read a first; +ERROR 42S02: Unknown table 'a1' in HANDLER +drop table t2; +create table t1 (a int, key (a)); +create table t2 like t1; +handler t1 open as a1; +handler t2 open as a2; +handler a1 read a first; +a +handler a2 read a first; +a +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize, doing recreate + analyze instead +test.t1 optimize status OK +handler a1 close; +ERROR 42S02: Unknown table 'a1' in HANDLER +handler a2 close; +drop table t1, t2; +# +# BUG#51877 - HANDLER interface causes invalid memory read +# +CREATE TABLE t1(a INT, KEY (a)); +HANDLER t1 OPEN; +HANDLER t1 READ a FIRST; +a +INSERT INTO t1 VALUES(1); +HANDLER t1 READ a NEXT; +a +HANDLER t1 CLOSE; +DROP TABLE t1; |