| 1 |
# test handler operation with partitioned table |
| 2 |
CREATE TABLE t1 ( |
| 3 |
col_1_int int, |
| 4 |
col_2_int int, |
| 5 |
PRIMARY KEY(col_2_int), |
| 6 |
KEY idx1 (col_1_int, col_2_int)) |
| 7 |
ENGINE = InnoDB |
| 8 |
PARTITION BY KEY() partitions 4; |
| 9 |
SHOW CREATE TABLE t1; |
| 10 |
Table Create Table |
| 11 |
t1 CREATE TABLE `t1` ( |
| 12 |
`col_1_int` int DEFAULT NULL, |
| 13 |
`col_2_int` int NOT NULL, |
| 14 |
PRIMARY KEY (`col_2_int`), |
| 15 |
KEY `idx1` (`col_1_int`,`col_2_int`) |
| 16 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 17 |
/*!50100 PARTITION BY KEY () |
| 18 |
PARTITIONS 4 */ |
| 19 |
# test handler read forward |
| 20 |
# insert values in all partitions - 1st set |
| 21 |
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4); |
| 22 |
# insert values in all partitions - 2nd set |
| 23 |
INSERT INTO t1 VALUES (8, 5), (7, 6), (6, 7), (5, 8); |
| 24 |
HANDLER t1 OPEN; |
| 25 |
# read 2nd set of values in forward direction using handler |
| 26 |
HANDLER t1 READ idx1 > (4, 4) LIMIT 10; |
| 27 |
col_1_int col_2_int |
| 28 |
5 8 |
| 29 |
6 7 |
| 30 |
7 6 |
| 31 |
8 5 |
| 32 |
# replace 1st set of values to make them bigger in idx1 order |
| 33 |
REPLACE INTO t1 VALUES (9, 1), (10, 2), (11, 3), (12, 4); |
| 34 |
# try handler next- expect no rows |
| 35 |
HANDLER t1 READ idx1 next; |
| 36 |
col_1_int col_2_int |
| 37 |
HANDLER t1 CLOSE; |
| 38 |
# delete all rows |
| 39 |
DELETE FROM t1; |
| 40 |
# test handler read backward |
| 41 |
# insert values in all partitions - 1st set |
| 42 |
INSERT INTO t1 VALUES (8, 5), (7, 6), (6, 7), (5, 8); |
| 43 |
# insert values in all partitions - 2nd set |
| 44 |
INSERT INTO t1 VALUES (9, 9), (10, 10), (11, 11), (12, 12); |
| 45 |
HANDLER t1 OPEN; |
| 46 |
# read 1st set of values in backward direction using handler |
| 47 |
HANDLER t1 READ idx1 < (9, 9) LIMIT 10; |
| 48 |
col_1_int col_2_int |
| 49 |
8 5 |
| 50 |
7 6 |
| 51 |
6 7 |
| 52 |
5 8 |
| 53 |
# replace 2nd set of values to make them smaller in idx1 order |
| 54 |
REPLACE INTO t1 VALUES ( 1, 9), ( 2, 10), ( 3, 11), ( 4, 12); |
| 55 |
# try handler previous- expect no rows |
| 56 |
HANDLER t1 READ idx1 prev; |
| 57 |
col_1_int col_2_int |
| 58 |
HANDLER t1 CLOSE; |
| 59 |
DROP TABLE t1; |