Debian Codegraph @

mysql-test/suite/ndbcluster/peek_indexed_rows.result

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