Debian Codegraph @

mysql-test/suite/parts/r/partition-dml-1-12-innodb.result

raw
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;