1
SET SESSION STORAGE_ENGINE = InnoDB;
2
drop table if exists t1,t3,t4,t5;
3
create table t1 (a int, b char(10), key a(a), key b(a,b));
5
(17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"),
6
(14,"aaa"),(15,"bbb"),(16,"ccc"),(16,"xxx"),
7
(20,"ggg"),(21,"hhh"),(22,"iii");
9
handler t2 read a=(SELECT 1);
10
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1)' at line 1
11
handler t2 read a first;
14
handler t2 read a next;
17
handler t2 read a next;
20
handler t2 read a prev;
23
handler t2 read a last;
26
handler t2 read a prev;
29
handler t2 read a prev;
32
handler t2 read a first;
35
handler t2 read a prev;
37
handler t2 read a last;
40
handler t2 read a prev;
43
handler t2 read a next;
46
handler t2 read a next;
48
handler t2 read a=(15);
51
handler t2 read a=(16);
54
handler t2 read a=(19,"fff");
55
ERROR 42000: Too many key parts specified; max 1 parts allowed
56
handler t2 read b=(19,"fff");
59
handler t2 read b=(19,"yyy");
62
handler t2 read b=(19);
65
handler t1 read a last;
66
ERROR 42S02: Unknown table 't1' in HANDLER
67
handler t2 read a=(11);
69
handler t2 read a>=(11);
72
handler t2 read a=(18);
75
handler t2 read a>=(18);
78
handler t2 read a>(18);
81
handler t2 read a<=(18);
84
handler t2 read a<(18);
87
handler t2 read a first limit 5;
94
handler t2 read a next limit 3;
99
handler t2 read a prev limit 10;
108
handler t2 read a>=(16) limit 4;
114
handler t2 read a>=(16) limit 2,2;
118
handler t2 read a last limit 3;
123
handler t2 read a=(19);
126
handler t2 read a=(19) where b="yyy";
129
handler t2 read first;
132
handler t2 read next;
135
handler t2 read next;
138
handler t2 read last;
139
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
142
handler t1 read a next;
145
handler t1 read a next;
150
handler t1 read a prev;
153
handler t1 read a prev;
157
handler t1 open as t2;
158
handler t2 read first;
161
alter table t1 engine = InnoDB;
162
handler t2 read first;
163
ERROR 42S02: Unknown table 't2' in HANDLER
164
handler t1 open as t2;
166
create table t1 (a int);
167
insert into t1 values (17);
168
handler t2 read first;
169
ERROR 42S02: Unknown table 't2' in HANDLER
170
handler t1 open as t2;
171
alter table t1 engine=MEMORY;
172
handler t2 read first;
173
ERROR 42S02: Unknown table 't2' in HANDLER
175
create table t1 (a int);
176
insert into t1 values (1),(2),(3),(4),(5),(6);
177
delete from t1 limit 2;
179
handler t1 read first;
182
handler t1 read first limit 1,1;
185
handler t1 read first limit 2,2;
189
delete from t1 limit 3;
190
handler t1 read first;
194
create table t1(a int, index(a));
195
insert into t1 values (1), (2), (3);
197
handler t1 read a=(W);
198
ERROR 42S22: Unknown column 'W' in 'field list'
199
handler t1 read a=(a);
200
ERROR HY000: Incorrect arguments to HANDLER ... READ
202
create table t1 (a char(5));
203
insert into t1 values ("Ok");
204
handler t1 open as t;
205
handler t read first;
209
handler t read first;
213
handler test.t1 open as t;
214
handler t read first;
220
create table t1 ( a int, b int, INDEX a (a) );
221
insert into t1 values (1,2), (2,1);
223
handler t1 read a=(1) where b=2;
226
handler t1 read a=(1) where b=3;
228
handler t1 read a=(1) where b=1;
232
drop database if exists test_test;
233
create database test_test;
235
create table t1(table_id char(20) primary key);
236
insert into t1 values ('test_test.t1');
237
insert into t1 values ('');
239
handler t1 read first limit 9;
243
create table t2(table_id char(20) primary key);
244
insert into t2 values ('test_test.t2');
245
insert into t2 values ('');
247
handler t2 read first limit 9;
252
drop table if exists t1;
253
create table t1(table_id char(20) primary key);
254
insert into t1 values ('test.t1');
255
insert into t1 values ('');
257
ERROR 42000: Not unique table/alias: 't1'
259
handler test.t1 read first limit 9;
260
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read first limit 9' at line 1
261
handler test_test.t1 read first limit 9;
262
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read first limit 9' at line 1
263
handler t1 read first limit 9;
267
handler test_test.t2 read first limit 9;
268
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read first limit 9' at line 1
269
handler t2 read first limit 9;
273
handler test_test.t1 close;
274
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'close' at line 1
276
drop table test_test.t1;
277
handler test_test.t2 close;
278
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'close' at line 1
280
drop table test_test.t2;
281
drop database test_test;
283
handler test.t1 close;
284
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'close' at line 1
286
ERROR 42S02: Unknown table 't1' in HANDLER
288
drop database if exists test_test;
289
drop table if exists t1;
290
drop table if exists t2;
291
drop table if exists t3;
292
create database test_test;
294
create table t1 (c1 char(20));
295
insert into t1 values ('test_test.t1');
296
create table t3 (c1 char(20));
297
insert into t3 values ('test_test.t3');
299
handler t1 read first limit 9;
303
handler h1 read first limit 9;
307
create table t1 (c1 char(20));
308
create table t2 (c1 char(20));
309
create table t3 (c1 char(20));
310
insert into t1 values ('t1');
311
insert into t2 values ('t2');
312
insert into t3 values ('t3');
314
ERROR 42000: Not unique table/alias: 't1'
316
ERROR 42000: Not unique table/alias: 't1'
318
ERROR 42000: Not unique table/alias: 't1'
319
handler t1 read first limit 9;
322
handler test.t1 close;
323
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'close' at line 1
324
handler test.t1 open h1;
325
ERROR 42000: Not unique table/alias: 'h1'
326
handler test_test.t1 open h1;
327
ERROR 42000: Not unique table/alias: 'h1'
328
handler test_test.t3 open h3;
329
handler test.t1 open h2;
330
handler t1 read first limit 9;
333
handler h1 read first limit 9;
336
handler h2 read first limit 9;
339
handler h3 read first limit 9;
342
handler h2 read first limit 9;
345
handler test.h1 close;
346
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'close' at line 1
350
handler t1 read first limit 9;
351
ERROR 42S02: Unknown table 't1' in HANDLER
352
handler h1 read first limit 9;
353
ERROR 42S02: Unknown table 'h1' in HANDLER
354
handler h2 read first limit 9;
355
ERROR 42S02: Unknown table 'h2' in HANDLER
356
handler h3 read first limit 9;
359
handler h3 read first limit 9;
363
handler h3 read first limit 9;
366
handler test.h3 read first limit 9;
367
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read first limit 9' at line 1
373
drop database test_test;
374
create table t1 (c1 char(20));
375
insert into t1 values ("t1");
376
handler t1 open as h1;
377
handler h1 read first limit 9;
380
create table t2 (c1 char(20));
381
insert into t2 values ("t2");
382
handler t2 open as h2;
383
handler h2 read first limit 9;
386
create table t3 (c1 char(20));
387
insert into t3 values ("t3");
388
handler t3 open as h3;
389
handler h3 read first limit 9;
392
create table t4 (c1 char(20));
393
insert into t4 values ("t4");
394
handler t4 open as h4;
395
handler h4 read first limit 9;
398
create table t5 (c1 char(20));
399
insert into t5 values ("t5");
400
handler t5 open as h5;
401
handler h5 read first limit 9;
404
alter table t1 engine=MyISAM;
405
handler h1 read first limit 9;
406
ERROR 42S02: Unknown table 'h1' in HANDLER
407
handler h2 read first limit 9;
410
handler h3 read first limit 9;
413
handler h4 read first limit 9;
416
handler h5 read first limit 9;
419
alter table t5 engine=MyISAM;
420
handler h1 read first limit 9;
421
ERROR 42S02: Unknown table 'h1' in HANDLER
422
handler h2 read first limit 9;
425
handler h3 read first limit 9;
428
handler h4 read first limit 9;
431
handler h5 read first limit 9;
432
ERROR 42S02: Unknown table 'h5' in HANDLER
433
alter table t3 engine=MyISAM;
434
handler h1 read first limit 9;
435
ERROR 42S02: Unknown table 'h1' in HANDLER
436
handler h2 read first limit 9;
439
handler h3 read first limit 9;
440
ERROR 42S02: Unknown table 'h3' in HANDLER
441
handler h4 read first limit 9;
444
handler h5 read first limit 9;
445
ERROR 42S02: Unknown table 'h5' in HANDLER
448
handler t1 open as h1_1;
449
handler t1 open as h1_2;
450
handler t1 open as h1_3;
451
handler h1_1 read first limit 9;
454
handler h1_2 read first limit 9;
457
handler h1_3 read first limit 9;
460
alter table t1 engine=InnoDB;
461
handler h1_1 read first limit 9;
462
ERROR 42S02: Unknown table 'h1_1' in HANDLER
463
handler h1_2 read first limit 9;
464
ERROR 42S02: Unknown table 'h1_2' in HANDLER
465
handler h1_3 read first limit 9;
466
ERROR 42S02: Unknown table 'h1_3' in HANDLER
472
create table t1 (c1 int);
473
insert into t1 values (1);
475
handler t1 read first;
478
send the below to another connection, do not wait for the result
480
proceed with the normal connection
481
handler t1 read next;
485
read the result from the other connection
486
Table Op Msg_type Msg_text
487
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
488
test.t1 optimize status OK
489
proceed with the normal connection
491
CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2));
492
INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2);
494
HANDLER t1 READ `primary` = (1, 1000);
496
HANDLER t1 READ `primary` PREV;
500
create table t1 (c1 int);
501
insert into t1 values (14397);
502
flush tables with read lock;
504
ERROR HY000: Can't execute the query because you have a conflicting read lock
505
send the below to another connection, do not wait for the result
507
proceed with the normal connection
512
read the result from the other connection
513
proceed with the normal connection
515
ERROR 42S02: Table 'test.t1' doesn't exist
516
drop table if exists t1;
518
Note 1051 Unknown table 't1'
519
drop table if exists t1;
520
create table t1 (a int) ENGINE=MEMORY;
523
ERROR HY000: Table storage engine for 't1' doesn't have this option
526
drop table if exists t1;
527
create table t1 (a int);
528
handler t1 open as t1_alias;
529
handler t1_alias read a next;
530
ERROR 42000: Key 'a' doesn't exist in table 't1_alias'
531
handler t1_alias READ a next where inexistent > 0;
532
ERROR 42S22: Unknown column 'inexistent' in 'field list'
533
handler t1_alias read a next;
534
ERROR 42000: Key 'a' doesn't exist in table 't1_alias'
535
handler t1_alias READ a next where inexistent > 0;
536
ERROR 42S22: Unknown column 'inexistent' in 'field list'
537
handler t1_alias close;
539
drop table if exists t1,t2;
540
create table t1 (c1 int);
541
create table t2 (c1 int);
542
insert into t1 values (1);
543
insert into t2 values (2);
546
handler t1 read first;
553
handler t2 read first;
556
handler t1 read next;
562
drop table if exists t1,t2;
563
create table t1 (c1 int);
566
handler t1 read first;
569
rename table t1 to t2;;
572
handler t2 read first;
574
handler t1 read next;
575
ERROR 42S02: Table 'test.t1' doesn't exist
579
drop table if exists t1;
580
create temporary table t1 (a int, b char(1), key a(a), key b(a,b));
581
insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"),
582
(5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j");
595
handler t1 open as a1;
596
handler a1 read a first;
599
handler a1 read a next;
602
handler a1 read a next;
606
ERROR HY000: Can't reopen table: 'a1'
607
handler a1 read a prev;
610
handler a1 read a prev;
613
handler a1 read a=(6) where b="g";
629
handler t1 open as a2;
630
handler a2 read a first;
633
handler a2 read a last;
636
handler a2 read a prev;
641
drop table if exists t1,t2;
642
create table t1 (a int);
643
handler t1 open as t1_alias;
645
create table t1 (a int);
646
handler t1 open as t1_alias;
649
create table t1 (a int);
650
handler t1 open as t1_alias;
651
handler t1_alias close;
653
create table t1 (a int);
654
handler t1 open as t1_alias;
655
handler t1_alias read first;
658
handler t1_alias read next;
659
ERROR 42S02: Unknown table 't1_alias' in HANDLER
660
create table t1 (a int);
661
create temporary table t2 (a int, key(a));
662
handler t1 open as a1;
663
handler t2 open as a2;
664
handler a2 read a first;
667
handler a2 read a next;
668
ERROR 42S02: Unknown table 'a2' in HANDLER
670
ERROR 42S02: Unknown table 'a1' in HANDLER
671
create table t1 (a int, key(a));
672
create table t2 like t1;
673
handler t1 open as a1;
674
handler t2 open as a2;
675
handler a1 read a first;
677
handler a2 read a first;
679
alter table t1 add b int;
681
ERROR 42S02: Unknown table 'a1' in HANDLER
684
create table t1 (a int, key(a));
685
handler t1 open as a1;
686
handler a1 read a first;
688
rename table t1 to t2;
689
handler a1 read a first;
690
ERROR 42S02: Unknown table 'a1' in HANDLER
692
create table t1 (a int, key(a));
693
create table t2 like t1;
694
handler t1 open as a1;
695
handler t2 open as a2;
696
handler a1 read a first;
698
handler a2 read a first;
701
Table Op Msg_type Msg_text
702
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
703
test.t1 optimize status OK
705
ERROR 42S02: Unknown table 'a1' in HANDLER
708
create table t1 (a int, b char(1), key a(a), key b(a,b));
709
insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"),
710
(5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j");
712
handler t1 read a first;
715
handler t1 read a next;
719
handler t1 read a next;
722
handler t1 read a next;
725
flush tables with read lock;
726
handler t1 read a next;
731
handler t1 read a next;
732
ERROR 42S02: Unknown table 't1' in HANDLER
733
drop table if exists t1;
734
create table t1 (a int);
735
insert into t1 values (1);
737
alter table t1 engine=memory;
738
handler t1 read a next;
739
ERROR HY000: Table storage engine for 't1' doesn't have this option
742
USE information_schema;
743
HANDLER COLUMNS OPEN;
744
ERROR HY000: Incorrect usage of HANDLER OPEN and information_schema