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