handler_innodb.result 11.8 KB
Newer Older
unknown's avatar
unknown committed
1
SET SESSION STORAGE_ENGINE = InnoDB;
unknown's avatar
unknown committed
2
drop table if exists t1,t3,t4,t5;
unknown's avatar
unknown committed
3 4 5 6 7 8
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"),(15,"bbb"),(16,"ccc"),(16,"xxx"),
(20,"ggg"),(21,"hhh"),(22,"iii");
handler t1 open as t2;
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
11
handler t2 read a first;
12 13
a	b
14	aaa
unknown's avatar
unknown committed
14
handler t2 read a next;
15 16
a	b
15	bbb
unknown's avatar
unknown committed
17
handler t2 read a next;
18 19
a	b
16	ccc
unknown's avatar
unknown committed
20
handler t2 read a prev;
21 22
a	b
15	bbb
unknown's avatar
unknown committed
23
handler t2 read a last;
24 25
a	b
22	iii
unknown's avatar
unknown committed
26
handler t2 read a prev;
27 28
a	b
21	hhh
unknown's avatar
unknown committed
29
handler t2 read a prev;
30 31
a	b
20	ggg
unknown's avatar
unknown committed
32
handler t2 read a first;
33 34
a	b
14	aaa
unknown's avatar
unknown committed
35
handler t2 read a prev;
36
a	b
unknown's avatar
unknown committed
37
handler t2 read a last;
38 39
a	b
22	iii
unknown's avatar
unknown committed
40
handler t2 read a prev;
41 42
a	b
21	hhh
unknown's avatar
unknown committed
43
handler t2 read a next;
44 45
a	b
22	iii
unknown's avatar
unknown committed
46
handler t2 read a next;
47
a	b
unknown's avatar
unknown committed
48
handler t2 read a=(15);
49 50
a	b
15	bbb
unknown's avatar
unknown committed
51
handler t2 read a=(16);
unknown's avatar
unknown committed
52 53
a	b
16	ccc
unknown's avatar
unknown committed
54
handler t2 read a=(19,"fff");
55
ERROR 42000: Too many key parts specified; max 1 parts allowed
unknown's avatar
unknown committed
56
handler t2 read b=(19,"fff");
unknown's avatar
unknown committed
57 58
a	b
19	fff
unknown's avatar
unknown committed
59
handler t2 read b=(19,"yyy");
unknown's avatar
unknown committed
60 61
a	b
19	yyy
unknown's avatar
unknown committed
62
handler t2 read b=(19);
unknown's avatar
unknown committed
63 64
a	b
19	fff
unknown's avatar
unknown committed
65
handler t1 read a last;
66
ERROR 42S02: Unknown table 't1' in HANDLER
unknown's avatar
unknown committed
67
handler t2 read a=(11);
unknown's avatar
unknown committed
68
a	b
unknown's avatar
unknown committed
69
handler t2 read a>=(11);
unknown's avatar
unknown committed
70 71
a	b
14	aaa
unknown's avatar
unknown committed
72
handler t2 read a=(18);
unknown's avatar
unknown committed
73 74
a	b
18	eee
unknown's avatar
unknown committed
75
handler t2 read a>=(18);
unknown's avatar
unknown committed
76 77
a	b
18	eee
unknown's avatar
unknown committed
78
handler t2 read a>(18);
unknown's avatar
unknown committed
79 80
a	b
19	fff
unknown's avatar
unknown committed
81
handler t2 read a<=(18);
unknown's avatar
unknown committed
82 83
a	b
18	eee
unknown's avatar
unknown committed
84
handler t2 read a<(18);
unknown's avatar
unknown committed
85 86
a	b
17	ddd
unknown's avatar
unknown committed
87
handler t2 read a first limit 5;
unknown's avatar
unknown committed
88 89 90 91 92 93
a	b
14	aaa
15	bbb
16	ccc
16	xxx
17	ddd
unknown's avatar
unknown committed
94
handler t2 read a next  limit 3;
unknown's avatar
unknown committed
95 96 97 98
a	b
18	eee
19	fff
19	yyy
unknown's avatar
unknown committed
99
handler t2 read a prev  limit 10;
unknown's avatar
unknown committed
100 101 102 103 104 105 106 107
a	b
19	fff
18	eee
17	ddd
16	xxx
16	ccc
15	bbb
14	aaa
unknown's avatar
unknown committed
108
handler t2 read a>=(16) limit 4;
unknown's avatar
unknown committed
109 110 111 112 113
a	b
16	ccc
16	xxx
17	ddd
18	eee
unknown's avatar
unknown committed
114
handler t2 read a>=(16) limit 2,2;
unknown's avatar
unknown committed
115 116 117
a	b
17	ddd
18	eee
unknown's avatar
unknown committed
118
handler t2 read a last  limit 3;
unknown's avatar
unknown committed
119 120 121 122
a	b
22	iii
21	hhh
20	ggg
unknown's avatar
unknown committed
123
handler t2 read a=(19);
unknown's avatar
unknown committed
124 125
a	b
19	fff
unknown's avatar
unknown committed
126
handler t2 read a=(19) where b="yyy";
unknown's avatar
unknown committed
127 128
a	b
19	yyy
unknown's avatar
unknown committed
129
handler t2 read first;
unknown's avatar
unknown committed
130 131
a	b
17	ddd
unknown's avatar
unknown committed
132
handler t2 read next;
unknown's avatar
unknown committed
133 134
a	b
18	eee
unknown's avatar
unknown committed
135
handler t2 read next;
unknown's avatar
unknown committed
136 137
a	b
19	fff
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
140
handler t2 close;
unknown's avatar
unknown committed
141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163
handler t1 open;
handler t1 read a next;
a	b
14	aaa
handler t1 read a next;
a	b
15	bbb
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
164 165 166 167 168
handler t1 open as t2;
drop table t1;
create table t1 (a int);
insert into t1 values (17);
handler t2 read first;
169
ERROR 42S02: Unknown table 't2' in HANDLER
unknown's avatar
unknown committed
170
handler t1 open as t2;
unknown's avatar
unknown committed
171
alter table t1 engine=MEMORY;
unknown's avatar
unknown committed
172
handler t2 read first;
173
ERROR 42S02: Unknown table 't2' in HANDLER
174
drop table t1;
175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193
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;
unknown's avatar
unknown committed
194 195 196 197
create table t1(a int, index(a));
insert into t1 values (1), (2), (3);
handler t1 open;
handler t1 read a=(W);
unknown's avatar
unknown committed
198
ERROR 42S22: Unknown column 'W' in 'field list'
199
handler t1 read a=(a);
200
ERROR HY000: Incorrect arguments to HANDLER ... READ
unknown's avatar
unknown committed
201
drop table t1;
202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219
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;
unknown's avatar
unknown committed
220 221 222 223 224 225 226 227 228 229 230 231
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;
232 233 234 235 236 237 238 239 240 241
drop database if exists test_test;
create database test_test;
use test_test;
create table t1(table_id char(20) primary key);
insert into t1 values ('test_test.t1');
insert into t1 values ('');
handler t1 open;
handler t1 read first limit 9;
table_id

unknown's avatar
unknown committed
242
test_test.t1
243 244 245 246 247 248 249
create table t2(table_id char(20) primary key);
insert into t2 values ('test_test.t2');
insert into t2 values ('');
handler t2 open;
handler t2 read first limit 9;
table_id

unknown's avatar
unknown committed
250
test_test.t2
251 252 253 254 255 256
use test;
drop table if exists t1;
create table t1(table_id char(20) primary key);
insert into t1 values ('test.t1');
insert into t1 values ('');
handler t1 open;
unknown's avatar
unknown committed
257
ERROR 42000: Not unique table/alias: 't1'
258 259
use test;
handler test.t1 read first limit 9;
unknown's avatar
unknown committed
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;
unknown's avatar
unknown committed
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 264 265
handler t1 read first limit 9;
table_id

unknown's avatar
unknown committed
266
test_test.t1
267
handler test_test.t2 read first limit 9;
unknown's avatar
unknown committed
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 270 271
handler t2 read first limit 9;
table_id

unknown's avatar
unknown committed
272
test_test.t2
273
handler test_test.t1 close;
unknown's avatar
unknown committed
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
275 276 277
handler t1 close;
drop table test_test.t1;
handler test_test.t2 close;
unknown's avatar
unknown committed
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
279 280 281 282 283
handler t2 close;
drop table test_test.t2;
drop database test_test;
use test;
handler test.t1 close;
unknown's avatar
unknown committed
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
285
handler t1 close;
unknown's avatar
unknown committed
286
ERROR 42S02: Unknown table 't1' in HANDLER
287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313
drop table test.t1;
drop database if exists test_test;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
create database test_test;
use test_test;
create table t1 (c1 char(20));
insert into t1 values ('test_test.t1');
create table t3 (c1 char(20));
insert into t3 values ('test_test.t3');
handler t1 open;
handler t1 read first limit 9;
c1
test_test.t1
handler t1 open h1;
handler h1 read first limit 9;
c1
test_test.t1
use test;
create table t1 (c1 char(20));
create table t2 (c1 char(20));
create table t3 (c1 char(20));
insert into t1 values ('t1');
insert into t2 values ('t2');
insert into t3 values ('t3');
handler t1 open;
unknown's avatar
unknown committed
314
ERROR 42000: Not unique table/alias: 't1'
315
handler t2 open t1;
unknown's avatar
unknown committed
316
ERROR 42000: Not unique table/alias: 't1'
317
handler t3 open t1;
unknown's avatar
unknown committed
318
ERROR 42000: Not unique table/alias: 't1'
319 320 321 322
handler t1 read first limit 9;
c1
test_test.t1
handler test.t1 close;
unknown's avatar
unknown committed
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;
unknown's avatar
unknown committed
325
ERROR 42000: Not unique table/alias: 'h1'
326
handler test_test.t1 open h1;
unknown's avatar
unknown committed
327
ERROR 42000: Not unique table/alias: 'h1'
328 329 330 331 332 333 334 335 336 337 338 339 340 341
handler test_test.t3 open h3;
handler test.t1 open h2;
handler t1 read first limit 9;
c1
test_test.t1
handler h1 read first limit 9;
c1
test_test.t1
handler h2 read first limit 9;
c1
t1
handler h3 read first limit 9;
c1
test_test.t3
unknown's avatar
unknown committed
342
handler h2 read first limit 9;
343 344 345
c1
t1
handler test.h1 close;
unknown's avatar
unknown committed
346 347 348
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
handler t1 close;
handler h1 close;
349 350
handler h2 close;
handler t1 read first limit 9;
unknown's avatar
unknown committed
351
ERROR 42S02: Unknown table 't1' in HANDLER
352
handler h1 read first limit 9;
unknown's avatar
unknown committed
353
ERROR 42S02: Unknown table 'h1' in HANDLER
354
handler h2 read first limit 9;
unknown's avatar
unknown committed
355
ERROR 42S02: Unknown table 'h2' in HANDLER
356 357 358
handler h3 read first limit 9;
c1
test_test.t3
unknown's avatar
unknown committed
359
handler h3 read first limit 9;
360 361 362 363 364 365 366
c1
test_test.t3
use test_test;
handler h3 read first limit 9;
c1
test_test.t3
handler test.h3 read first limit 9;
unknown's avatar
unknown committed
367 368
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
handler h3 close;
369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405
use test;
drop table t3;
drop table t2;
drop table t1;
drop database test_test;
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;
unknown's avatar
unknown committed
406
ERROR 42S02: Unknown table 'h1' in HANDLER
407 408 409 410 411 412 413 414 415 416 417 418 419 420
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;
unknown's avatar
unknown committed
421
ERROR 42S02: Unknown table 'h1' in HANDLER
422 423 424 425 426 427 428 429 430 431
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;
unknown's avatar
unknown committed
432
ERROR 42S02: Unknown table 'h5' in HANDLER
433 434
alter table t3 engine=MyISAM;
handler h1 read first limit 9;
unknown's avatar
unknown committed
435
ERROR 42S02: Unknown table 'h1' in HANDLER
436 437 438 439
handler h2 read first limit 9;
c1
t2
handler h3 read first limit 9;
unknown's avatar
unknown committed
440
ERROR 42S02: Unknown table 'h3' in HANDLER
441 442 443 444
handler h4 read first limit 9;
c1
t4
handler h5 read first limit 9;
unknown's avatar
unknown committed
445
ERROR 42S02: Unknown table 'h5' in HANDLER
446 447 448 449 450 451 452 453 454 455 456 457 458 459
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
unknown's avatar
unknown committed
460
alter table t1 engine=InnoDB;
461
handler h1_1 read first limit 9;
unknown's avatar
unknown committed
462
ERROR 42S02: Unknown table 'h1_1' in HANDLER
463
handler h1_2 read first limit 9;
unknown's avatar
unknown committed
464
ERROR 42S02: Unknown table 'h1_2' in HANDLER
465
handler h1_3 read first limit 9;
unknown's avatar
unknown committed
466
ERROR 42S02: Unknown table 'h1_3' in HANDLER
467 468 469 470 471
drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
472 473 474 475 476 477 478
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
479
optimize table t1;
480 481 482 483 484 485 486 487 488 489
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	status	OK
proceed with the normal connection
drop table t1;
unknown's avatar
unknown committed
490 491 492 493 494 495 496 497 498
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
DROP TABLE t1;
499 500 501 502 503 504
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
505
drop table t1;
506 507 508 509 510 511 512 513 514 515 516 517
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'
unknown's avatar
unknown committed
518 519 520 521 522 523 524
drop table if exists t1;
create table t1 (a int) ENGINE=MEMORY;
--> client 2
handler t1 open;
ERROR HY000: Table storage engine for 't1' doesn't have this option
--> client 1
drop table t1;