Debian Codegraph @

mysql-test/suite/ndb/r/ndb_update_no_read.result

raw
1 DROP TABLE IF EXISTS t1;
2 create table t1 (a int not null primary key, b int not null, c int,
3 unique index_b (b) using hash)
4 engine ndb;
5 insert into t1 values (1,10,1),(2,9,1),(3,8,1),(4,7,1),(5,6,1),(6,5,2),(7,4,2),(8,3,2),
6 (9,2,2),(10,1,2);
7 affected rows: 10
8 info: Records: 10 Duplicates: 0 Warnings: 0
9
10 # expected result 2 roundtrips
11 # 0 - info call
12 # 1 - read the row
13 # 0 - update the row (deferred to commit)
14 # 1 - update + commit the transaction
15
16 update t1 set c = 111, b = 20 where a = 1;
17 affected rows: 1
18 info: Rows matched: 1 Changed: 1 Warnings: 0
19 ndb_execute_count
20 2
21 affected rows: 1
22 select * from t1 where a = 1 order by a;
23 a b c
24 1 20 111
25 affected rows: 1
26
27 # expected result 1 roundtrips
28 # 0 - info call
29 # 0 - read the rows (1 with read before)
30 # 0 - delete the row (deferred to commit)
31 # 1 - delete + commit the transaction
32
33 delete from t1 where a = 1;
34 affected rows: 1
35 ndb_execute_count
36 1
37 affected rows: 1
38 select * from t1 where a = 1 order by a;
39 a b c
40 affected rows: 0
41
42 # expected result 1 roundtrips
43 # 0 - info call
44 # 0 - read the rows
45 # 0 - update the row (deferred to commit)
46 # 1 - update + commit the transaction
47
48 update t1 set c = 1111 where b = 2;
49 affected rows: 1
50 info: Rows matched: 1 Changed: 1 Warnings: 0
51 ndb_execute_count
52 1
53 affected rows: 1
54 select * from t1 where b = 2 order by a;
55 a b c
56 9 2 1111
57 affected rows: 1
58
59 # expected result 2 roundtrips
60 # 0 - info call
61 # 1 - read the rows
62 # 0 - update the row (deferred to commit)
63 # 1 - update + commit the transaction
64
65 update t1 set c = 12, b = 19 where b = 2;
66 affected rows: 1
67 info: Rows matched: 1 Changed: 1 Warnings: 0
68 ndb_execute_count
69 2
70 affected rows: 1
71 select * from t1 where b = 2 order by a;
72 a b c
73 affected rows: 0
74
75 # expected result 1 roundtrips
76 # 0 - info call
77 # 0 - read the rows (1 with read before)
78 # 0 - delete the row (deferred to commit)
79 # 1 - delete and commit the transaction
80
81 delete from t1 where b = 19;
82 affected rows: 1
83 ndb_execute_count
84 1
85 affected rows: 1
86 select * from t1 where b = 19 order by a;
87 a b c
88 affected rows: 0
89
90 # expected result 3 roundtrips
91 # 0 - info call
92 # 2 - read the rows
93 # 0 - update the rows (deferred to commit)
94 # 1 - update + commit the transaction
95
96 update t1 set c = 22 where a = 10 or a >= 10;
97 affected rows: 1
98 info: Rows matched: 1 Changed: 1 Warnings: 0
99 ndb_execute_count
100 3
101 affected rows: 1
102 select * from t1 order by a;
103 a b c
104 2 9 1
105 3 8 1
106 4 7 1
107 5 6 1
108 6 5 2
109 7 4 2
110 8 3 2
111 10 1 22
112 affected rows: 8
113
114 # expected result 1 roundtrips
115 # 0 - info call
116 # 0 - read the rows (1 with read before)
117 # 0 - update the rows (2 if no bulk update + 1 deferred to commit)
118 # 1 - update + commit the transaction
119
120 update t1 set c = 23 where a in (8,10);
121 affected rows: 2
122 info: Rows matched: 2 Changed: 2 Warnings: 0
123 ndb_execute_count
124 1
125 affected rows: 1
126 select * from t1 order by a;
127 a b c
128 2 9 1
129 3 8 1
130 4 7 1
131 5 6 1
132 6 5 2
133 7 4 2
134 8 3 23
135 10 1 23
136 affected rows: 8
137
138 # expected result 4 roundtrips
139 # 0 - info call
140 # 3 - read the rows
141 # 0 - update the rows (executed during read)
142 # 1 - commit the transaction
143
144 update t1 set c = 23 where a in (7,8) or a >= 10;
145 affected rows: 3
146 info: Rows matched: 3 Changed: 3 Warnings: 0
147 ndb_execute_count
148 4
149 affected rows: 1
150 select * from t1 order by a;
151 a b c
152 2 9 1
153 3 8 1
154 4 7 1
155 5 6 1
156 6 5 2
157 7 4 23
158 8 3 23
159 10 1 23
160 affected rows: 8
161
162 # expected result 5 roundtrips
163 # 0 - info call
164 # 2 - read the rows
165 # 2 - pk read in rnd_pos
166 # 0 - update the rows (deferred to commit)
167 # 1 - update + commit the transaction
168
169 update t1 set c = 11 where a = 3 or b = 7;
170 affected rows: 2
171 info: Rows matched: 2 Changed: 2 Warnings: 0
172 select * from t1 where a = 3 or b = 7 order by a;
173 a b c
174 3 8 11
175 4 7 11
176 affected rows: 2
177
178 # expected result 4 roundtrips
179 # 0 - info call
180 # 1 - read complete rows
181 # 0 - don't read the row _again_ - included in read above
182 # 1 - delete the row (pk update)
183 # 1 - insert the row (pk update)
184 # 1 - commit the transaction
185
186 update t1 set a = 13, b = 20 where a = 3;
187 affected rows: 1
188 info: Rows matched: 1 Changed: 1 Warnings: 0
189 ndb_execute_count
190 4
191 affected rows: 1
192 select * from t1 where a = 13 order by a;
193 a b c
194 13 20 11
195 affected rows: 1
196
197 # expected result 4 roundtrips
198 # 0 - info call
199 # 1 - read complete rows
200 # 0 - don't read the row _again_ - included in read above
201 # 1 - delete the row (pk update)
202 # 1 - insert the row (pk update)
203 # 1 - commit the transaction
204
205 update t1 set a = 12, b = 19 where b = 7;
206 affected rows: 1
207 info: Rows matched: 1 Changed: 1 Warnings: 0
208 ndb_execute_count
209 4
210 affected rows: 1
211 select * from t1 where b = 19 order by a;
212 a b c
213 12 19 11
214 affected rows: 1
215 select * from t1 where b = 7 order by a;
216 a b c
217 affected rows: 0
218
219 # expected result 3 roundtrips
220 # 1 - info call
221 # 1 - read the rows
222 # 0 - update the rows (deferred to commit)
223 # 1 - update + commit the transaction
224
225 update t1 set c = 12, b = 29 where a = 5 and b = 6;
226 affected rows: 1
227 info: Rows matched: 1 Changed: 1 Warnings: 0
228 ndb_execute_count
229 2
230 affected rows: 1
231 select * from t1 where b = 19 order by a;
232 a b c
233 12 19 11
234 affected rows: 1
235
236 # expected result 2 roundtrips
237 # 0 - info call
238 # 1 - read the rows
239 # 0 - delete the rows (no row found)
240 # 1 - commit the transaction
241
242 delete from t1 where b = 6 and c = 12;
243 affected rows: 0
244 ndb_execute_count
245 2
246 affected rows: 1
247 select * from t1 where b = 6 order by a;
248 a b c
249 affected rows: 0
250 drop table t1;
251 Warnings:
252 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
253 create table t1 (a int primary key,
254 b varchar(4))
255 engine=ndb;
256 insert into t1 values (1, '1'), (2, '2'), (3, '3');
257 affected rows: 3
258 info: Records: 3 Duplicates: 0 Warnings: 0
259
260 # expected result 1 roundtrip
261 # 0 - info call
262 # 0 - read the row
263 # 0 - update the row
264 # 1 - update+commit the row
265 # Rows matched=changed=affected=1
266
267 update t1 set b='two' where a=2;
268 affected rows: 1
269 info: Rows matched: 1 Changed: 1 Warnings: 0
270 ndb_execute_count
271 1
272 affected rows: 1
273
274 # expected result 1 roundtrip
275 # 0 - info call
276 # 0 - read the row
277 # 0 - update the row
278 # 1 - update+commit the row
279 # Rows matched=changed=affected=0
280
281 update t1 set b='lots' where a=2000;
282 affected rows: 0
283 info: Rows matched: 0 Changed: 0 Warnings: 0
284 ndb_execute_count
285 1
286 affected rows: 1
287
288 # expected result 1 roundtrip
289 # 0 - info call
290 # 0 - read the row
291 # 0 - update the row
292 # 1 - update+commit the row
293 # Rows matched=changed=affected=1
294 # 1 warning
295
296 update t1 set b='one plus one' where a=2;
297 affected rows: 1
298 info: Rows matched: 1 Changed: 1 Warnings: 1
299 Warnings:
300 Warning 1265 Data truncated for column 'b' at row 1
301 ndb_execute_count
302 1
303 affected rows: 1
304
305 # expected result 1 roundtrip
306 # 0 - info call
307 # 0 - read the row
308 # 0 - update the row
309 # 1 - update+commit the row
310 # Rows matched=changed=affected=0
311 # 1 warning
312
313 update t1 set b='two thousand' where a=2000;
314 affected rows: 0
315 info: Rows matched: 0 Changed: 0 Warnings: 1
316 Warnings:
317 Warning 1265 Data truncated for column 'b' at row 1
318 ndb_execute_count
319 1
320 affected rows: 1
321
322 # expected result 2 roundtrips
323 # 0 - info call
324 # 0 - read the row
325 # 1 - update the row
326 # 1 - commit
327 # Rows matched=changed=affected=1
328
329 begin;
330 affected rows: 0
331 update t1 set b='two' where a=2;
332 affected rows: 1
333 info: Rows matched: 1 Changed: 1 Warnings: 0
334 commit;
335 affected rows: 0
336 ndb_execute_count
337 2
338 affected rows: 1
339
340 # expected result 2 roundtrips
341 # 0 - info call
342 # 0 - read the row
343 # 1 - update the row
344 # 1 - commit
345 # Rows matched=changed=affected=0
346
347 begin;
348 affected rows: 0
349 update t1 set b='lots' where a=2000;
350 affected rows: 0
351 info: Rows matched: 0 Changed: 0 Warnings: 0
352 commit;
353 affected rows: 0
354 ndb_execute_count
355 2
356 affected rows: 1
357
358 # expected result 2 roundtrips
359 # 0 - info call
360 # 0 - read the row
361 # 1 - update the row
362 # 1 - commit
363 # Rows matched=changed=affected=1
364 1 warning
365
366 begin;
367 affected rows: 0
368 update t1 set b='one plus one' where a=2;
369 affected rows: 1
370 info: Rows matched: 1 Changed: 1 Warnings: 1
371 Warnings:
372 Warning 1265 Data truncated for column 'b' at row 1
373 commit;
374 affected rows: 0
375 ndb_execute_count
376 2
377 affected rows: 1
378
379 # expected result 2 roundtrips
380 # 0 - info call
381 # 0 - read the row
382 # 1 - update the row
383 # 1 - commit
384 # Rows matched=changed=affected=0
385 # 1 warning
386
387 begin;
388 affected rows: 0
389 update t1 set b='two thousand' where a=2000;
390 affected rows: 0
391 info: Rows matched: 0 Changed: 0 Warnings: 1
392 Warnings:
393 Warning 1265 Data truncated for column 'b' at row 1
394 commit;
395 affected rows: 0
396 ndb_execute_count
397 2
398 affected rows: 1
399 drop table t1;
400 create table t1
401 (a int not null primary key auto_increment, b int, c varchar(256))
402 character set latin1
403 engine = ndb;
404 insert into t1(b,c) values (1,'this is a test'),
405 (2,'this is a test'),(3,'this is a test'),
406 (4,'this is a test'),(5,'this is a test'),(6,'this is a test'),
407 (7,'this is a test'),(8,'this is a test'),(9,'this is a test');
408 affected rows: 9
409 info: Records: 9 Duplicates: 0 Warnings: 0
410 insert into t1(b,c) select b,c from t1;
411 affected rows: 9
412 info: Records: 9 Duplicates: 0 Warnings: 0
413 insert into t1(b,c) select b,c from t1;
414 affected rows: 18
415 info: Records: 18 Duplicates: 0 Warnings: 0
416 insert into t1(b,c) select b,c from t1;
417 affected rows: 36
418 info: Records: 36 Duplicates: 0 Warnings: 0
419 insert into t1(b,c) select b,c from t1;
420 affected rows: 72
421 info: Records: 72 Duplicates: 0 Warnings: 0
422 insert into t1(b,c) select b,c from t1;
423 affected rows: 144
424 info: Records: 144 Duplicates: 0 Warnings: 0
425 insert into t1(b,c) select b,c from t1;
426 affected rows: 288
427 info: Records: 288 Duplicates: 0 Warnings: 0
428 insert into t1(b,c) select b,c from t1;
429 affected rows: 576
430 info: Records: 576 Duplicates: 0 Warnings: 0
431 insert into t1(b,c) select b,c from t1;
432 affected rows: 1152
433 info: Records: 1152 Duplicates: 0 Warnings: 0
434 insert into t1(b,c) select b,c from t1;
435 affected rows: 2304
436 info: Records: 2304 Duplicates: 0 Warnings: 0
437 analyze table t1;
438 Table Op Msg_type Msg_text
439 test.t1 analyze status OK
440 affected rows: 1
441 set autocommit=off;
442 affected rows: 0
443 select count(*) from t1 where a in (...)
444 count(*)
445 1000
446 affected rows: 1
447 ndb_execute_count
448 9
449 affected rows: 1
450 set autocommit=on;
451 affected rows: 0
452 update t1 set c = 'kalle' where a in (...)
453 affected rows: 1000
454 info: Rows matched: 1000 Changed: 1000 Warnings: 0
455 ndb_execute_count
456 9
457 affected rows: 1
458 delete from t1 where a in (...)
459 affected rows: 1000
460 ndb_execute_count
461 3
462 affected rows: 1
463 delete from t1 where a in (...)
464 affected rows: 0
465 ndb_execute_count
466 3
467 affected rows: 1
468 update t1 set c = 'kalle' where a in (...)
469 affected rows: 0
470 info: Rows matched: 0 Changed: 0 Warnings: 0
471 ndb_execute_count
472 9
473 affected rows: 1
474 update t1 set c = 'kalle' where a in (...)
475 affected rows: 0
476 info: Rows matched: 0 Changed: 0 Warnings: 0
477 ndb_execute_count
478 9
479 affected rows: 1
480 drop table t1;