Debian Codegraph @

mysql-test/suite/ndbcluster/peek_indexed_rows.test

raw
1 --result_format 2
2 --source include/have_ndb.inc
3
4 #
5 # Test for the "peek indexed rows" functionality - ie. speculative reads
6 # to find duplicate rows in unique indexes.
7 #
8
9 CREATE TABLE t1(
10 a int,
11 b varchar(31),
12 c int,
13 d int,
14 e varchar(32),
15 f varchar(33),
16 g int,
17 h varchar(34),
18 i int,
19 j varchar(35),
20 k int,
21 l varchar(36),
22 m int NOT NULL,
23 n int NOT NULL,
24 o varchar(37) NOT NULL,
25 p varchar(38) NOT NULL,
26 PRIMARY KEY(a,b,c,d) USING HASH,
27 UNIQUE unique_efgh (e,f,g,h),
28 UNIQUE unique_ijklm (i,j,k,l,m),
29 UNIQUE unique_n (n),
30 UNIQUE unique_op (o,p),
31 UNIQUE unique_using_hash (f,g,h,i,j,k,l,m,n,o,p) USING HASH
32 ) ENGINE ndbcluster;
33
34 ## Load data rows
35 INSERT INTO t1 VALUES
36 (11, "BB1", 12, 13, "EEEEE1", "FFFFFF1" , 16, "HHHHHHH1", 17, "JJJJJJJJ1",
37 19, "LLLLLLLLLL1", 111, 112, "OOOOOOOOOOOOO1", "PPPPPPPPPPPPPP1");
38 INSERT INTO t1 VALUES
39 (21, "BB2", 22, 23, "EEEEE2", "FFFFFF2" , 26, "HHHHHHH2", 27, "JJJJJJJJ2",
40 29, "LLLLLLLLLL2", 211, 212, "OOOOOOOOOOOOO2", "PPPPPPPPPPPPPP2");
41
42 ##
43 ## Tests for INSERT IGNORE's usage of "peek indexed rows"
44 ##
45 ## - inserting row without any duplicates work, uses peek
46 INSERT IGNORE INTO t1 VALUES
47 (31, "BB3", 32, 33, "EEEEE3", "FFFFFF3" , 36, "HHHHHHH3", 37, "JJJJJJJJ3",
48 39, "LLLLLLLLLL3", 311, 312, "OOOOOOOOOOOOO3", "PPPPPPPPPPPPPP3");
49 ## - inserting row with same primary key fails
50 INSERT IGNORE INTO t1 VALUES
51 (31, "BB3", 32, 33, "EEEEE4", "FFFFFF4" , 46, "HHHHHHH4", 47, "JJJJJJJJ4",
52 49, "LLLLLLLLLL4", 411, 412, "OOOOOOOOOOOOO4", "PPPPPPPPPPPPPP4");
53 ## - inserting row with duplicate unique (e,f,g,h) fails
54 INSERT IGNORE INTO t1 VALUES
55 (41, "BB4", 42, 43, "EEEEE3", "FFFFFF3" , 36, "HHHHHHH3", 47, "JJJJJJJJ4",
56 49, "LLLLLLLLLL4", 411, 412, "OOOOOOOOOOOOO4", "PPPPPPPPPPPPPP4");
57 ## - inserting row with duplicate unique (n) fails
58 INSERT IGNORE INTO t1 VALUES
59 (41, "BB4", 42, 43, "EEEEE4", "FFFFFF4" , 46, "HHHHHHH4", 47, "JJJJJJJJ4",
60 49, "LLLLLLLLLL4", 411, 312, "OOOOOOOOOOOOO4", "PPPPPPPPPPPPPP4");
61 ## - inserting row with duplicate unique (o,p) fails
62 INSERT IGNORE INTO t1 VALUES
63 (41, "BB4", 42, 43, "EEEEE4", "FFFFFF4" , 46, "HHHHHHH4", 47, "JJJJJJJJ4",
64 49, "LLLLLLLLLL4", 411, 412, "OOOOOOOOOOOOO3", "PPPPPPPPPPPPPP3");
65
66 ##
67 ## Tests for INSERT .. ON DUPLICATE usage of "peek indexed rows"
68 ##
69 ## - insert with same primary key, updates j
70 INSERT INTO t1 VALUES
71 (31, "BB3", 32, 33, "EEEEE4", "FFFFFF4" , 46, "HHHHHHH4", 47, "JJJJJJJJ4",
72 49, "LLLLLLLLLL4", 411, 412, "OOOOOOOOOOOOO4", "PPPPPPPPPPPPPP4")
73 ON DUPLICATE KEY UPDATE j = "JJJJJJJJ99";
74 SELECT j, "JJJJJJJJ99" as expected FROM t1
75 WHERE a = 31 AND b = "BB3" AND c = 32 AND d = 33;
76 ## - insert with same unique key (o,p) updates j
77 INSERT INTO t1 VALUES
78 (41, "BBB", 42, 43, "EEEEE4", "FFFFFF4" , 46, "HHHHHHH4", 47, "JJJJJJJJ4",
79 49, "LLLLLLLLLL4", 411, 412, "OOOOOOOOOOOOO3", "PPPPPPPPPPPPPP3")
80 ON DUPLICATE KEY UPDATE j = "JJJJJJJJ3";
81 SELECT j, "JJJJJJJJ3" as expected FROM t1
82 WHERE a = 31 AND b = "BB3" AND c = 32 AND d = 33;
83
84 ##
85 ## Tests for UPDATE IGNORE's usage of "peek indexed rows"
86 ##
87 ## - insert a fourth row for the update ignore testing
88 INSERT INTO t1 VALUES
89 (91, "BB9", 92, 93, "EEEEE4", "FFFFFF4" , 46, "HHHHHHH4", 47, "JJJJJJJJ4",
90 49, "LLLLLLLLLL4", 411, 412, "OOOOOOOOOOOOO4", "PPPPPPPPPPPPPP4");
91 ## - updating row without any duplicates work, uses peek
92 UPDATE IGNORE t1
93 SET a = 41, b = "BB4", c = 42, d = 43
94 WHERE a = 91 AND b = "BB9" AND c = 92 AND d = 93;
95 ## - updating row with already existing primary key fails
96 UPDATE IGNORE t1
97 SET a = 31, b = "BB3", c = 32, d = 33
98 WHERE a = 41 AND b = "BB4" AND c = 42 AND d = 43;
99 ## - updating row with duplicate unique (e,f,g,h) fails
100 UPDATE IGNORE t1
101 SET e = "EEEEE1", f= "FFFFFF1" , g = 16, h = "HHHHHHH1"
102 WHERE a = 41 AND b = "BB4" AND c = 42 AND d = 43;
103 ## - updating row with duplicate unique (o,p) fails
104 UPDATE IGNORE t1
105 SET o = "OOOOOOOOOOOOO1", p= "PPPPPPPPPPPPPP1"
106 WHERE a = 41 AND b = "BB4" AND c = 42 AND d = 43;
107
108 ##
109 ## Tests for REPLACE's usage of "peek indexed rows"
110 ##
111 ## - use REPLACE to insert fifth row
112 REPLACE INTO t1 VALUES
113 (51, "BB5", 52, 53, "EEEEE5", "FFFFFF5" , 56, "HHHHHHH5", 57, "JJJJJJJJ5",
114 59, "LLLLLLLLLL5", 511, 512, "OOOOOOOOOOOOO5", "PPPPPPPPPPPPPP5");
115 ## - duplicate primary key of fifth row, updates with values for sixth
116 REPLACE INTO t1 VALUES
117 (51, "BB5", 52, 53, "EEEEE6", "FFFFFF6" , 66, "HHHHHHH6", 67, "JJJJJJJJ6",
118 69, "LLLLLLLLLL6", 611, 612, "OOOOOOOOOOOOO6", "PPPPPPPPPPPPPP6");
119 SELECT j, "JJJJJJJJ6" as expected FROM t1
120 WHERE a = 51 AND b = "BB5" AND c = 52 AND d = 53;
121 ## - use REPLACE of unique key (e,f,g,h) values for sixth with fifth
122 REPLACE INTO t1 VALUES
123 (51, "BB5", 52, 53, "EEEEE6", "FFFFFF6" , 66, "HHHHHHH6", 57, "JJJJJJJJ5",
124 59, "LLLLLLLLLL5", 511, 512, "OOOOOOOOOOOOO5", "PPPPPPPPPPPPPP5");
125 ## - check result with primary key read
126 SELECT j, "JJJJJJJJ5" as expected FROM t1
127 WHERE a = 51 AND b = "BB5" AND c = 52 AND d = 53;
128 ## - check result also with unique key read
129 SELECT j, "JJJJJJJJ5" as expected FROM t1
130 WHERE e = "EEEEE6" AND f = "FFFFFF6" AND g = 66 AND h = "HHHHHHH6";
131
132
133
134 DROP TABLE t1;
135