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 b first; a b 14 aaa handler t2 read b next; a b 16 ccc handler t2 read b next; a b 16 xxx handler t2 read b prev; a b 16 ccc handler t2 read b last; a b 22 iii handler t2 read b prev; a b 21 hhh handler t2 read b prev; a b 20 ggg handler t2 read b first; a b 14 aaa handler t2 read b prev; a b handler t2 read b last; a b 22 iii handler t2 read b prev; a b 21 hhh handler t2 read b next; a b 22 iii handler t2 read b next; a b handler t2 read a=(15); a b handler t2 read a=(21); a b 21 hhh 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 b=(18); a b 18 eee handler t2 read b>=(18); a b 18 eee handler t2 read b>(18); a b 19 fff handler t2 read b<=(18); a b 18 eee handler t2 read b<(18); a b 17 ddd handler t2 read a=(15); a b handler t2 read a>=(15) limit 2; a b 16 ccc 16 xxx handler t2 read a>(15) limit 2; a b 16 ccc 16 xxx 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 b first limit 5; a b 14 aaa 16 ccc 16 xxx 17 ddd 18 eee handler t2 read b next limit 3; a b 19 fff 19 yyy 20 ggg handler t2 read b prev limit 10; a b 19 yyy 19 fff 18 eee 17 ddd 16 xxx 16 ccc 14 aaa handler t2 read b>=(16) limit 4; a b 16 ccc 16 xxx 17 ddd 18 eee handler t2 read b>=(16) limit 2,2; a b 17 ddd 18 eee select * from t1 where a>=16 order by a,b 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 b=(16) limit 1,3; a b 16 xxx handler t2 read b=(19); a b 19 fff handler t2 read b=(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 b next; a b 14 aaa handler t1 read b 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 b=(9); a b 9 j handler a2 read b next; a b 9 k handler a2 read b prev limit 2; a b 9 j 8 i handler a2 read b last; a b 9 k handler a2 read b 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; CREATE TABLE t1 (f1 integer, f2 integer, primary key (f1), key (f2)) engine=innodb; INSERT INTO t1 VALUES (1,1),(2,2),(3,3); HANDLER t1 OPEN; HANDLER t1 READ FIRST WHERE f2 <= 1; f1 f2 1 1 HANDLER t1 READ `PRIMARY` PREV; f1 f2 3 3 DROP TABLE t1;