1drop table if exists t1,t3,t4,t5; 2drop database if exists test_test; 3SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM; 4drop table if exists t1,t3,t4,t5; 5create table t1 (a int, b char(10), key a using btree (a), key b using btree (a,b)); 6insert into t1 values 7(17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), 8(14,"aaa"),(16,"ccc"),(16,"xxx"), 9(20,"ggg"),(21,"hhh"),(22,"iii"),(23,"xxx"),(24,"xxx"),(25,"xxx"); 10handler t1 open; 11handler t1 read a=(SELECT 1); 12ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1)' at line 1 13handler t1 read a=(1) FIRST; 14ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FIRST' at line 1 15handler t1 read a=(1) NEXT; 16ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NEXT' at line 1 17handler t1 read last; 18ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 19handler t1 close; 20drop table t1; 21CREATE TABLE t1(a INT, PRIMARY KEY(a)); 22insert into t1 values(1),(2); 23handler t1 open; 24handler t1 read primary=(1); 25ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary=(1)' at line 1 26handler t1 read `primary`=(1); 27a 281 29handler t1 close; 30drop table t1; 31create database test_test; 32use test_test; 33create table t1(table_id char(20), primary key (table_id)); 34insert into t1 values ('test_test.t1'); 35insert into t1 values (''); 36handler t1 open; 37handler t1 read first limit 9; 38table_id 39test_test.t1 40 41create table t2(table_id char(20), primary key (table_id)); 42insert into t2 values ('test_test.t2'); 43insert into t2 values (''); 44handler t2 open; 45handler t2 read first limit 9; 46table_id 47test_test.t2 48 49use test; 50create table t1(table_id char(20), primary key (table_id)); 51insert into t1 values ('test.t1'); 52insert into t1 values (''); 53handler t1 open; 54ERROR 42000: Not unique table/alias: 't1' 55use test; 56handler test.t1 read first limit 9; 57ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'read first limit 9' at line 1 58handler test_test.t1 read first limit 9; 59ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'read first limit 9' at line 1 60handler t1 read first limit 9; 61table_id 62test_test.t1 63 64handler test_test.t2 read first limit 9; 65ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'read first limit 9' at line 1 66handler t2 read first limit 9; 67table_id 68test_test.t2 69 70handler test_test.t1 close; 71ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'close' at line 1 72handler t1 close; 73drop table test_test.t1; 74handler test_test.t2 close; 75ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'close' at line 1 76handler t2 close; 77drop table test_test.t2; 78drop database test_test; 79use test; 80handler test.t1 close; 81ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'close' at line 1 82handler t1 close; 83ERROR 42S02: Unknown table 't1' in HANDLER 84drop table test.t1; 85create database test_test; 86use test_test; 87create table t1 (c1 char(20)); 88insert into t1 values ('test_test.t1'); 89create table t3 (c1 char(20)); 90insert into t3 values ('test_test.t3'); 91handler t1 open; 92handler t1 read first limit 9; 93c1 94test_test.t1 95handler t1 open h1; 96handler h1 read first limit 9; 97c1 98test_test.t1 99use test; 100create table t1 (c1 char(20)); 101create table t2 (c1 char(20)); 102create table t3 (c1 char(20)); 103insert into t1 values ('t1'); 104insert into t2 values ('t2'); 105insert into t3 values ('t3'); 106handler t1 open; 107ERROR 42000: Not unique table/alias: 't1' 108handler t2 open t1; 109ERROR 42000: Not unique table/alias: 't1' 110handler t3 open t1; 111ERROR 42000: Not unique table/alias: 't1' 112handler t1 read first limit 9; 113c1 114test_test.t1 115handler test.t1 close; 116ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'close' at line 1 117handler test.t1 open h1; 118ERROR 42000: Not unique table/alias: 'h1' 119handler test_test.t1 open h1; 120ERROR 42000: Not unique table/alias: 'h1' 121handler test_test.t3 open h3; 122handler test.t1 open h2; 123handler t1 read first limit 9; 124c1 125test_test.t1 126handler h1 read first limit 9; 127c1 128test_test.t1 129handler h2 read first limit 9; 130c1 131t1 132handler h3 read first limit 9; 133c1 134test_test.t3 135handler h2 read first limit 9; 136c1 137t1 138handler test.h1 close; 139ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'close' at line 1 140handler t1 close; 141handler h1 close; 142handler h2 close; 143handler t1 read first limit 9; 144ERROR 42S02: Unknown table 't1' in HANDLER 145handler h1 read first limit 9; 146ERROR 42S02: Unknown table 'h1' in HANDLER 147handler h2 read first limit 9; 148ERROR 42S02: Unknown table 'h2' in HANDLER 149handler h3 read first limit 9; 150c1 151test_test.t3 152handler h3 read first limit 9; 153c1 154test_test.t3 155use test_test; 156handler h3 read first limit 9; 157c1 158test_test.t3 159handler test.h3 read first limit 9; 160ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'read first limit 9' at line 1 161handler h3 close; 162use test; 163drop table t3; 164drop table t2; 165drop table t1; 166drop database test_test; 167create table t1 (c1 int); 168create table t2 (c1 int); 169insert into t1 values (1); 170insert into t2 values (2); 171handler t1 open; 172handler t1 read first; 173c1 1741 175connect flush,localhost,root,,; 176connection flush; 177flush tables; 178flush table t1; 179connect waiter,localhost,root,,; 180connection waiter; 181connection default; 182handler t2 open; 183handler t2 read first; 184c1 1852 186handler t1 read next; 187c1 1881 189handler t1 close; 190handler t2 close; 191connection flush; 192connection default; 193drop table t1,t2; 194disconnect flush; 195drop table if exists t1, t0; 196create table t1 (c1 int); 197handler t1 open; 198handler t1 read first; 199c1 200connect flush,localhost,root,,; 201connection flush; 202rename table t1 to t0; 203connection waiter; 204connection default; 205# 206# RENAME placed two pending locks and waits. 207# When HANDLER t0 OPEN does open_tables(), it calls 208# mysql_ha_flush(), which in turn closes the open HANDLER for t1. 209# RENAME TABLE gets unblocked. If it gets scheduled quickly 210# and manages to complete before open_tables() 211# of HANDLER t0 OPEN, open_tables() and therefore the whole 212# HANDLER t0 OPEN succeeds. Otherwise open_tables() 213# notices a pending or active exclusive metadata lock on t2 214# and the whole HANDLER t0 OPEN fails with ER_LOCK_DEADLOCK 215# error. 216# 217handler t0 open; 218handler t0 close; 219connection flush; 220handler t1 read next; 221ERROR 42S02: Unknown table 't1' in HANDLER 222handler t1 close; 223ERROR 42S02: Unknown table 't1' in HANDLER 224connection default; 225drop table t0; 226connection flush; 227disconnect flush; 228connection waiter; 229disconnect waiter; 230connection default; 231create table t1 (a int); 232handler t1 open as t1_alias; 233drop table t1; 234create table t1 (a int); 235handler t1 open as t1_alias; 236flush tables; 237drop table t1; 238create table t1 (a int); 239handler t1 open as t1_alias; 240handler t1_alias close; 241drop table t1; 242create table t1 (a int); 243handler t1 open as t1_alias; 244handler t1_alias read first; 245a 246drop table t1; 247handler t1_alias read next; 248ERROR 42S02: Unknown table 't1_alias' in HANDLER 249create table t1 (a int, b char(1), key a (a), key b (a,b)); 250insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), 251(5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"); 252handler t1 open; 253handler t1 read a first; 254a b 2550 a 256handler t1 read a next; 257a b 2581 b 259flush tables; 260handler t1 read a next; 261a b 2622 c 263flush tables t1; 264handler t1 read a next; 265a b 2660 a 267flush tables with read lock; 268handler t1 read a next; 269a b 2700 a 271unlock tables; 272drop table t1; 273handler t1 read a next; 274ERROR 42S02: Unknown table 't1' in HANDLER 275connect con1,localhost,root,,; 276connect con2,localhost,root,,; 277# Now test case which was reported originally but which no longer 278# triggers execution path which has caused the problem. 279connection default; 280create table t1 (a int not null); 281insert into t1 values (1); 282handler t1 open; 283connection con1; 284alter table t1 engine=csv; 285connection con2; 286connection default; 287# Since S metadata lock was already acquired at HANDLER OPEN time 288# and TL_READ lock requested by HANDLER READ is compatible with 289# ALTER's TL_WRITE_ALLOW_READ the below statement should succeed 290# without waiting. The old version of table should be used in it. 291handler t1 read next; 292a 2931 294handler t1 close; 295connection con1; 296drop table t1; 297disconnect con1; 298connection con2; 299disconnect con2; 300connection default; 301USE information_schema; 302HANDLER COLUMNS OPEN; 303ERROR HY000: Incorrect usage of HANDLER OPEN and information_schema 304USE test; 305PREPARE h_r FROM 'HANDLER t1 READ `PRIMARY` LAST'; 306ERROR 42S02: Unknown table 't1' in HANDLER 307create view v as select 1; 308create temporary table v as select 2; 309handler v open; 310prepare stmt from 'create table if not exists v as select 3'; 311execute stmt; 312Warnings: 313Note 1050 Table 'v' already exists 314handler v read next; 315ERROR 42S02: Unknown table 'v' in HANDLER 316drop view v; 317# 318# MDEV-15813 ASAN use-after-poison in hp_hashnr upon 319# HANDLER READ on a versioned HEAP table 320# 321CREATE TABLE t1 (g GEOMETRY NOT NULL, SPATIAL gi(g)); 322INSERT INTO t1 VALUES (POINT(0,0)); 323HANDLER t1 OPEN AS h; 324HANDLER h READ `gi`= (10); 325ERROR HY000: SPATIAL index `gi` does not support this operation 326HANDLER h READ `gi`> (10); 327ERROR HY000: SPATIAL index `gi` does not support this operation 328HANDLER h CLOSE; 329DROP TABLE t1; 330CREATE TABLE t1 (w VARCHAR(100), FULLTEXT fk(w)); 331INSERT INTO t1 VALUES ('one two three'); 332HANDLER t1 OPEN AS h; 333HANDLER h READ `fk`= (10); 334ERROR HY000: FULLTEXT index `fk` does not support this operation 335HANDLER h READ `fk`> (10); 336ERROR HY000: FULLTEXT index `fk` does not support this operation 337HANDLER h CLOSE; 338DROP TABLE t1; 339