[tomato.git] / release / src / router / mysql / mysql-test / suite / parts / r / partition_engine_ndb.result
| blob | 6662032ef53802881f573d3bd431223cfcb3b777 |
1 SET @max_row = 20;
2 SET @@session.storage_engine = 'ndbcluster';
4 #------------------------------------------------------------------------
5 # 0. Setting of auxiliary variables + Creation of an auxiliary tables
6 # needed in many testcases
7 #------------------------------------------------------------------------
8 SELECT @max_row DIV 2 INTO @max_row_div2;
9 SELECT @max_row DIV 3 INTO @max_row_div3;
10 SELECT @max_row DIV 4 INTO @max_row_div4;
11 SET @max_int_4 = 2147483647;
12 DROP TABLE IF EXISTS t0_template;
13 CREATE TABLE t0_template (
14 f_int1 INTEGER,
15 f_int2 INTEGER,
16 f_char1 CHAR(20),
17 f_char2 CHAR(20),
18 f_charbig VARCHAR(1000) ,
19 PRIMARY KEY(f_int1))
20 ENGINE = MEMORY;
21 # Logging of <max_row> INSERTs into t0_template suppressed
22 DROP TABLE IF EXISTS t0_definition;
23 CREATE TABLE t0_definition (
24 state CHAR(3),
25 create_command VARBINARY(5000),
26 file_list VARBINARY(10000),
27 PRIMARY KEY (state)
28 ) ENGINE = MEMORY;
29 DROP TABLE IF EXISTS t0_aux;
30 CREATE TABLE t0_aux ( f_int1 INTEGER,
31 f_int2 INTEGER,
32 f_char1 CHAR(20),
33 f_char2 CHAR(20),
34 f_charbig VARCHAR(1000) )
35 ENGINE = MEMORY;
36 SET AUTOCOMMIT= 1;
37 SET @@session.sql_mode= '';
38 # End of basic preparations needed for all tests
39 #-----------------------------------------------
41 #========================================================================
42 # Checks where the engine is assigned on all supported (CREATE TABLE
43 # statement) positions + basic operations on the tables
44 # Storage engine mixups are currently (2005-12-23) not supported
45 #========================================================================
46 DROP TABLE IF EXISTS t1;
47 #------------------------------------------------------------------------
48 # 1 Assignment of storage engine just after column list only
49 #------------------------------------------------------------------------
50 CREATE TABLE t1 (
51 f_int1 INTEGER,
52 f_int2 INTEGER,
53 f_char1 CHAR(20),
54 f_char2 CHAR(20),
55 f_charbig VARCHAR(1000)
56 ) ENGINE = 'ndbcluster'
57 PARTITION BY HASH(f_int1) PARTITIONS 2;
58 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
59 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
60 # Start usability test (inc/partition_check.inc)
61 create_command
62 SHOW CREATE TABLE t1;
63 Table Create Table
64 t1 CREATE TABLE `t1` (
65 `f_int1` int(11) DEFAULT NULL,
66 `f_int2` int(11) DEFAULT NULL,
67 `f_char1` char(20) DEFAULT NULL,
68 `f_char2` char(20) DEFAULT NULL,
69 `f_charbig` varchar(1000) DEFAULT NULL
70 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
71 /*!50100 PARTITION BY HASH (f_int1)
72 PARTITIONS 2 */
74 # check prerequisites-1 success: 1
75 # check COUNT(*) success: 1
76 # check MIN/MAX(f_int1) success: 1
77 # check MIN/MAX(f_int2) success: 1
78 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
79 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
80 CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
81 WHERE f_int1 IN (2,3);
82 # check prerequisites-3 success: 1
83 DELETE FROM t1 WHERE f_charbig = 'delete me';
84 # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
85 # check read via f_int1 success: 1
86 # check read via f_int2 success: 1
88 # check multiple-1 success: 1
89 DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
91 # check multiple-2 success: 1
92 INSERT INTO t1 SELECT * FROM t0_template
93 WHERE MOD(f_int1,3) = 0;
95 # check multiple-3 success: 1
96 UPDATE t1 SET f_int1 = f_int1 + @max_row
97 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
98 AND @max_row_div2 + @max_row_div4;
100 # check multiple-4 success: 1
101 DELETE FROM t1
102 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
103 AND @max_row_div2 + @max_row_div4 + @max_row;
105 # check multiple-5 success: 1
106 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
107 INSERT INTO t1
108 SET f_int1 = @cur_value , f_int2 = @cur_value,
109 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
110 f_charbig = '#SINGLE#';
112 # check single-1 success: 1
113 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
114 INSERT INTO t1
115 SET f_int1 = @cur_value , f_int2 = @cur_value,
116 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
117 f_charbig = '#SINGLE#';
119 # check single-2 success: 1
120 SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
121 SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
122 UPDATE t1 SET f_int1 = @cur_value2
123 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
125 # check single-3 success: 1
126 SET @cur_value1= -1;
127 SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
128 UPDATE t1 SET f_int1 = @cur_value1
129 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
131 # check single-4 success: 1
132 SELECT MAX(f_int1) INTO @cur_value FROM t1;
133 DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
135 # check single-5 success: 1
136 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
138 # check single-6 success: 1
139 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
141 # check single-7 success: 1
142 DELETE FROM t1 WHERE f_charbig = '#2147483647##';
143 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
144 INSERT t1 SET f_int1 = 0 , f_int2 = 0,
145 f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
146 f_charbig = '#NULL#';
147 INSERT INTO t1
148 SET f_int1 = NULL , f_int2 = -@max_row,
149 f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
150 f_charbig = '#NULL#';
151 # check null success: 1
153 # check null-1 success: 1
154 UPDATE t1 SET f_int1 = -@max_row
155 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
156 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
158 # check null-2 success: 1
159 UPDATE t1 SET f_int1 = NULL
160 WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
161 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
163 # check null-3 success: 1
164 DELETE FROM t1
165 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
166 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
168 # check null-4 success: 1
169 DELETE FROM t1
170 WHERE f_int1 = 0 AND f_int2 = 0
171 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
172 AND f_charbig = '#NULL#';
173 SET AUTOCOMMIT= 0;
174 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
175 SELECT f_int1, f_int1, '', '', 'was inserted'
176 FROM t0_template source_tab
177 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
179 # check transactions-1 success: 1
180 COMMIT WORK;
182 # check transactions-2 success: 1
183 ROLLBACK WORK;
185 # check transactions-3 success: 1
186 DELETE FROM t1 WHERE f_charbig = 'was inserted';
187 COMMIT WORK;
188 ROLLBACK WORK;
190 # check transactions-4 success: 1
191 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
192 SELECT f_int1, f_int1, '', '', 'was inserted'
193 FROM t0_template source_tab
194 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
196 # check transactions-5 success: 1
197 ROLLBACK WORK;
199 # check transactions-6 success: 1
200 # INFO: Storage engine used for t1 seems to be transactional.
201 COMMIT;
203 # check transactions-7 success: 1
204 DELETE FROM t1 WHERE f_charbig = 'was inserted';
205 COMMIT WORK;
206 SET @@session.sql_mode = 'traditional';
207 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
208 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
209 SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
210 '', '', 'was inserted' FROM t0_template
211 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
212 ERROR 22012: Division by 0
213 COMMIT;
215 # check transactions-8 success: 1
216 # INFO: Storage engine used for t1 seems to be unable to revert
217 # changes made by the failing statement.
218 SET @@session.sql_mode = '';
219 SET AUTOCOMMIT= 1;
220 DELETE FROM t1 WHERE f_charbig = 'was inserted';
221 COMMIT WORK;
222 UPDATE t1 SET f_charbig = REPEAT('b', 1000);
224 # check special-1 success: 1
225 UPDATE t1 SET f_charbig = '';
227 # check special-2 success: 1
228 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
229 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
230 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
231 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
232 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
233 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
234 'just inserted' FROM t0_template
235 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
236 CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
237 BEGIN
238 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
239 f_charbig = 'updated by trigger'
240 WHERE f_int1 = new.f_int1;
241 END|
242 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
243 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
244 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
246 # check trigger-1 success: 1
247 DROP TRIGGER trg_1;
248 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
249 f_int2 = CAST(f_char1 AS SIGNED INT),
250 f_charbig = 'just inserted'
251 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
252 DELETE FROM t0_aux
253 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
254 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
255 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
256 'just inserted' FROM t0_template
257 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
258 CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
259 BEGIN
260 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
261 f_charbig = 'updated by trigger'
262 WHERE f_int1 = new.f_int1;
263 END|
264 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
265 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
266 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
268 # check trigger-2 success: 1
269 DROP TRIGGER trg_1;
270 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
271 f_int2 = CAST(f_char1 AS SIGNED INT),
272 f_charbig = 'just inserted'
273 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
274 DELETE FROM t0_aux
275 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
276 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
277 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
278 'just inserted' FROM t0_template
279 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
280 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
281 BEGIN
282 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
283 f_charbig = 'updated by trigger'
284 WHERE f_int1 = new.f_int1;
285 END|
286 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
287 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
289 # check trigger-3 success: 1
290 DROP TRIGGER trg_1;
291 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
292 f_int2 = CAST(f_char1 AS SIGNED INT),
293 f_charbig = 'just inserted'
294 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
295 DELETE FROM t0_aux
296 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
297 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
298 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
299 'just inserted' FROM t0_template
300 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
301 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
302 BEGIN
303 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
304 f_charbig = 'updated by trigger'
305 WHERE f_int1 = - old.f_int1;
306 END|
307 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
308 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
310 # check trigger-4 success: 1
311 DROP TRIGGER trg_1;
312 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
313 f_int2 = CAST(f_char1 AS SIGNED INT),
314 f_charbig = 'just inserted'
315 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
316 DELETE FROM t0_aux
317 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
318 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
319 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
320 'just inserted' FROM t0_template
321 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
322 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
323 BEGIN
324 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
325 f_charbig = 'updated by trigger'
326 WHERE f_int1 = new.f_int1;
327 END|
328 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
329 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
331 # check trigger-5 success: 1
332 DROP TRIGGER trg_1;
333 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
334 f_int2 = CAST(f_char1 AS SIGNED INT),
335 f_charbig = 'just inserted'
336 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
337 DELETE FROM t0_aux
338 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
339 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
340 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
341 'just inserted' FROM t0_template
342 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
343 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
344 BEGIN
345 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
346 f_charbig = 'updated by trigger'
347 WHERE f_int1 = - old.f_int1;
348 END|
349 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
350 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
352 # check trigger-6 success: 1
353 DROP TRIGGER trg_1;
354 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
355 f_int2 = CAST(f_char1 AS SIGNED INT),
356 f_charbig = 'just inserted'
357 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
358 DELETE FROM t0_aux
359 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
360 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
361 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
362 'just inserted' FROM t0_template
363 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
364 CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
365 BEGIN
366 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
367 f_charbig = 'updated by trigger'
368 WHERE f_int1 = - old.f_int1;
369 END|
370 DELETE FROM t0_aux
371 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
373 # check trigger-7 success: 1
374 DROP TRIGGER trg_1;
375 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
376 f_int2 = CAST(f_char1 AS SIGNED INT),
377 f_charbig = 'just inserted'
378 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
379 DELETE FROM t0_aux
380 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
381 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
382 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
383 'just inserted' FROM t0_template
384 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
385 CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
386 BEGIN
387 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
388 f_charbig = 'updated by trigger'
389 WHERE f_int1 = - old.f_int1;
390 END|
391 DELETE FROM t0_aux
392 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
394 # check trigger-8 success: 1
395 DROP TRIGGER trg_1;
396 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
397 f_int2 = CAST(f_char1 AS SIGNED INT),
398 f_charbig = 'just inserted'
399 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
400 DELETE FROM t0_aux
401 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
402 DELETE FROM t1
403 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
404 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
405 BEGIN
406 SET new.f_int1 = old.f_int1 + @max_row,
407 new.f_int2 = old.f_int2 - @max_row,
408 new.f_charbig = '####updated per update trigger####';
409 END|
410 UPDATE t1
411 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
412 f_charbig = '####updated per update statement itself####';
414 # check trigger-9 success: 1
415 DROP TRIGGER trg_2;
416 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
417 f_int2 = CAST(f_char1 AS SIGNED INT),
418 f_charbig = CONCAT('===',f_char1,'===');
419 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
420 BEGIN
421 SET new.f_int1 = new.f_int1 + @max_row,
422 new.f_int2 = new.f_int2 - @max_row,
423 new.f_charbig = '####updated per update trigger####';
424 END|
425 UPDATE t1
426 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
427 f_charbig = '####updated per update statement itself####';
429 # check trigger-10 success: 1
430 DROP TRIGGER trg_2;
431 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
432 f_int2 = CAST(f_char1 AS SIGNED INT),
433 f_charbig = CONCAT('===',f_char1,'===');
434 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
435 BEGIN
436 SET new.f_int1 = @my_max1 + @counter,
437 new.f_int2 = @my_min2 - @counter,
438 new.f_charbig = '####updated per insert trigger####';
439 SET @counter = @counter + 1;
440 END|
441 SET @counter = 1;
442 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
443 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
444 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
445 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
446 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
447 ORDER BY f_int1;
448 DROP TRIGGER trg_3;
450 # check trigger-11 success: 1
451 DELETE FROM t1
452 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
453 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
454 AND f_charbig = '####updated per insert trigger####';
455 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
456 BEGIN
457 SET new.f_int1 = @my_max1 + @counter,
458 new.f_int2 = @my_min2 - @counter,
459 new.f_charbig = '####updated per insert trigger####';
460 SET @counter = @counter + 1;
461 END|
462 SET @counter = 1;
463 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
464 INSERT INTO t1 (f_char1, f_char2, f_charbig)
465 SELECT CAST(f_int1 AS CHAR),
466 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
467 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
468 ORDER BY f_int1;
469 DROP TRIGGER trg_3;
471 # check trigger-12 success: 1
472 DELETE FROM t1
473 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
474 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
475 AND f_charbig = '####updated per insert trigger####';
476 ANALYZE TABLE t1;
477 Table Op Msg_type Msg_text
478 test.t1 analyze note The storage engine for the table doesn't support analyze
479 CHECK TABLE t1 EXTENDED;
480 Table Op Msg_type Msg_text
481 test.t1 check note The storage engine for the table doesn't support check
482 CHECKSUM TABLE t1 EXTENDED;
483 Table Checksum
484 test.t1 <some_value>
485 OPTIMIZE TABLE t1;
486 Table Op Msg_type Msg_text
487 test.t1 optimize note The storage engine for the table doesn't support optimize
488 # check layout success: 1
489 REPAIR TABLE t1 EXTENDED;
490 Table Op Msg_type Msg_text
491 test.t1 repair note The storage engine for the table doesn't support repair
492 # check layout success: 1
493 TRUNCATE t1;
495 # check TRUNCATE success: 1
496 # check layout success: 1
497 # End usability test (inc/partition_check.inc)
498 DROP TABLE t1;
499 #------------------------------------------------------------------------
500 # 2 Assignment of storage engine just after partition or subpartition
501 # name only
502 #------------------------------------------------------------------------
503 CREATE TABLE t1 (
504 f_int1 INTEGER,
505 f_int2 INTEGER,
506 f_char1 CHAR(20),
507 f_char2 CHAR(20),
508 f_charbig VARCHAR(1000)
509 )
510 PARTITION BY HASH(f_int1)
511 ( PARTITION part1 STORAGE ENGINE = 'ndbcluster',
512 PARTITION part2 STORAGE ENGINE = 'ndbcluster'
513 );
514 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
515 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
516 # Start usability test (inc/partition_check.inc)
517 create_command
518 SHOW CREATE TABLE t1;
519 Table Create Table
520 t1 CREATE TABLE `t1` (
521 `f_int1` int(11) DEFAULT NULL,
522 `f_int2` int(11) DEFAULT NULL,
523 `f_char1` char(20) DEFAULT NULL,
524 `f_char2` char(20) DEFAULT NULL,
525 `f_charbig` varchar(1000) DEFAULT NULL
526 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
527 /*!50100 PARTITION BY HASH (f_int1)
528 (PARTITION part1 ENGINE = ndbcluster,
529 PARTITION part2 ENGINE = ndbcluster) */
531 # check prerequisites-1 success: 1
532 # check COUNT(*) success: 1
533 # check MIN/MAX(f_int1) success: 1
534 # check MIN/MAX(f_int2) success: 1
535 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
536 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
537 CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
538 WHERE f_int1 IN (2,3);
539 # check prerequisites-3 success: 1
540 DELETE FROM t1 WHERE f_charbig = 'delete me';
541 # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
542 # check read via f_int1 success: 1
543 # check read via f_int2 success: 1
545 # check multiple-1 success: 1
546 DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
548 # check multiple-2 success: 1
549 INSERT INTO t1 SELECT * FROM t0_template
550 WHERE MOD(f_int1,3) = 0;
552 # check multiple-3 success: 1
553 UPDATE t1 SET f_int1 = f_int1 + @max_row
554 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
555 AND @max_row_div2 + @max_row_div4;
557 # check multiple-4 success: 1
558 DELETE FROM t1
559 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
560 AND @max_row_div2 + @max_row_div4 + @max_row;
562 # check multiple-5 success: 1
563 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
564 INSERT INTO t1
565 SET f_int1 = @cur_value , f_int2 = @cur_value,
566 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
567 f_charbig = '#SINGLE#';
569 # check single-1 success: 1
570 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
571 INSERT INTO t1
572 SET f_int1 = @cur_value , f_int2 = @cur_value,
573 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
574 f_charbig = '#SINGLE#';
576 # check single-2 success: 1
577 SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
578 SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
579 UPDATE t1 SET f_int1 = @cur_value2
580 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
582 # check single-3 success: 1
583 SET @cur_value1= -1;
584 SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
585 UPDATE t1 SET f_int1 = @cur_value1
586 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
588 # check single-4 success: 1
589 SELECT MAX(f_int1) INTO @cur_value FROM t1;
590 DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
592 # check single-5 success: 1
593 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
595 # check single-6 success: 1
596 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
598 # check single-7 success: 1
599 DELETE FROM t1 WHERE f_charbig = '#2147483647##';
600 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
601 INSERT t1 SET f_int1 = 0 , f_int2 = 0,
602 f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
603 f_charbig = '#NULL#';
604 INSERT INTO t1
605 SET f_int1 = NULL , f_int2 = -@max_row,
606 f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
607 f_charbig = '#NULL#';
608 # check null success: 1
610 # check null-1 success: 1
611 UPDATE t1 SET f_int1 = -@max_row
612 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
613 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
615 # check null-2 success: 1
616 UPDATE t1 SET f_int1 = NULL
617 WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
618 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
620 # check null-3 success: 1
621 DELETE FROM t1
622 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
623 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
625 # check null-4 success: 1
626 DELETE FROM t1
627 WHERE f_int1 = 0 AND f_int2 = 0
628 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
629 AND f_charbig = '#NULL#';
630 SET AUTOCOMMIT= 0;
631 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
632 SELECT f_int1, f_int1, '', '', 'was inserted'
633 FROM t0_template source_tab
634 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
636 # check transactions-1 success: 1
637 COMMIT WORK;
639 # check transactions-2 success: 1
640 ROLLBACK WORK;
642 # check transactions-3 success: 1
643 DELETE FROM t1 WHERE f_charbig = 'was inserted';
644 COMMIT WORK;
645 ROLLBACK WORK;
647 # check transactions-4 success: 1
648 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
649 SELECT f_int1, f_int1, '', '', 'was inserted'
650 FROM t0_template source_tab
651 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
653 # check transactions-5 success: 1
654 ROLLBACK WORK;
656 # check transactions-6 success: 1
657 # INFO: Storage engine used for t1 seems to be transactional.
658 COMMIT;
660 # check transactions-7 success: 1
661 DELETE FROM t1 WHERE f_charbig = 'was inserted';
662 COMMIT WORK;
663 SET @@session.sql_mode = 'traditional';
664 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
665 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
666 SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
667 '', '', 'was inserted' FROM t0_template
668 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
669 ERROR 22012: Division by 0
670 COMMIT;
672 # check transactions-8 success: 1
673 # INFO: Storage engine used for t1 seems to be unable to revert
674 # changes made by the failing statement.
675 SET @@session.sql_mode = '';
676 SET AUTOCOMMIT= 1;
677 DELETE FROM t1 WHERE f_charbig = 'was inserted';
678 COMMIT WORK;
679 UPDATE t1 SET f_charbig = REPEAT('b', 1000);
681 # check special-1 success: 1
682 UPDATE t1 SET f_charbig = '';
684 # check special-2 success: 1
685 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
686 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
687 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
688 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
689 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
690 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
691 'just inserted' FROM t0_template
692 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
693 CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
694 BEGIN
695 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
696 f_charbig = 'updated by trigger'
697 WHERE f_int1 = new.f_int1;
698 END|
699 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
700 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
701 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
703 # check trigger-1 success: 1
704 DROP TRIGGER trg_1;
705 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
706 f_int2 = CAST(f_char1 AS SIGNED INT),
707 f_charbig = 'just inserted'
708 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
709 DELETE FROM t0_aux
710 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
711 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
712 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
713 'just inserted' FROM t0_template
714 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
715 CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
716 BEGIN
717 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
718 f_charbig = 'updated by trigger'
719 WHERE f_int1 = new.f_int1;
720 END|
721 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
722 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
723 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
725 # check trigger-2 success: 1
726 DROP TRIGGER trg_1;
727 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
728 f_int2 = CAST(f_char1 AS SIGNED INT),
729 f_charbig = 'just inserted'
730 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
731 DELETE FROM t0_aux
732 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
733 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
734 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
735 'just inserted' FROM t0_template
736 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
737 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
738 BEGIN
739 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
740 f_charbig = 'updated by trigger'
741 WHERE f_int1 = new.f_int1;
742 END|
743 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
744 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
746 # check trigger-3 success: 1
747 DROP TRIGGER trg_1;
748 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
749 f_int2 = CAST(f_char1 AS SIGNED INT),
750 f_charbig = 'just inserted'
751 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
752 DELETE FROM t0_aux
753 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
754 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
755 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
756 'just inserted' FROM t0_template
757 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
758 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
759 BEGIN
760 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
761 f_charbig = 'updated by trigger'
762 WHERE f_int1 = - old.f_int1;
763 END|
764 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
765 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
767 # check trigger-4 success: 1
768 DROP TRIGGER trg_1;
769 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
770 f_int2 = CAST(f_char1 AS SIGNED INT),
771 f_charbig = 'just inserted'
772 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
773 DELETE FROM t0_aux
774 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
775 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
776 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
777 'just inserted' FROM t0_template
778 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
779 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
780 BEGIN
781 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
782 f_charbig = 'updated by trigger'
783 WHERE f_int1 = new.f_int1;
784 END|
785 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
786 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
788 # check trigger-5 success: 1
789 DROP TRIGGER trg_1;
790 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
791 f_int2 = CAST(f_char1 AS SIGNED INT),
792 f_charbig = 'just inserted'
793 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
794 DELETE FROM t0_aux
795 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
796 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
797 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
798 'just inserted' FROM t0_template
799 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
800 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
801 BEGIN
802 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
803 f_charbig = 'updated by trigger'
804 WHERE f_int1 = - old.f_int1;
805 END|
806 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
807 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
809 # check trigger-6 success: 1
810 DROP TRIGGER trg_1;
811 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
812 f_int2 = CAST(f_char1 AS SIGNED INT),
813 f_charbig = 'just inserted'
814 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
815 DELETE FROM t0_aux
816 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
817 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
818 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
819 'just inserted' FROM t0_template
820 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
821 CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
822 BEGIN
823 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
824 f_charbig = 'updated by trigger'
825 WHERE f_int1 = - old.f_int1;
826 END|
827 DELETE FROM t0_aux
828 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
830 # check trigger-7 success: 1
831 DROP TRIGGER trg_1;
832 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
833 f_int2 = CAST(f_char1 AS SIGNED INT),
834 f_charbig = 'just inserted'
835 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
836 DELETE FROM t0_aux
837 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
838 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
839 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
840 'just inserted' FROM t0_template
841 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
842 CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
843 BEGIN
844 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
845 f_charbig = 'updated by trigger'
846 WHERE f_int1 = - old.f_int1;
847 END|
848 DELETE FROM t0_aux
849 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
851 # check trigger-8 success: 1
852 DROP TRIGGER trg_1;
853 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
854 f_int2 = CAST(f_char1 AS SIGNED INT),
855 f_charbig = 'just inserted'
856 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
857 DELETE FROM t0_aux
858 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
859 DELETE FROM t1
860 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
861 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
862 BEGIN
863 SET new.f_int1 = old.f_int1 + @max_row,
864 new.f_int2 = old.f_int2 - @max_row,
865 new.f_charbig = '####updated per update trigger####';
866 END|
867 UPDATE t1
868 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
869 f_charbig = '####updated per update statement itself####';
871 # check trigger-9 success: 1
872 DROP TRIGGER trg_2;
873 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
874 f_int2 = CAST(f_char1 AS SIGNED INT),
875 f_charbig = CONCAT('===',f_char1,'===');
876 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
877 BEGIN
878 SET new.f_int1 = new.f_int1 + @max_row,
879 new.f_int2 = new.f_int2 - @max_row,
880 new.f_charbig = '####updated per update trigger####';
881 END|
882 UPDATE t1
883 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
884 f_charbig = '####updated per update statement itself####';
886 # check trigger-10 success: 1
887 DROP TRIGGER trg_2;
888 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
889 f_int2 = CAST(f_char1 AS SIGNED INT),
890 f_charbig = CONCAT('===',f_char1,'===');
891 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
892 BEGIN
893 SET new.f_int1 = @my_max1 + @counter,
894 new.f_int2 = @my_min2 - @counter,
895 new.f_charbig = '####updated per insert trigger####';
896 SET @counter = @counter + 1;
897 END|
898 SET @counter = 1;
899 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
900 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
901 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
902 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
903 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
904 ORDER BY f_int1;
905 DROP TRIGGER trg_3;
907 # check trigger-11 success: 1
908 DELETE FROM t1
909 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
910 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
911 AND f_charbig = '####updated per insert trigger####';
912 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
913 BEGIN
914 SET new.f_int1 = @my_max1 + @counter,
915 new.f_int2 = @my_min2 - @counter,
916 new.f_charbig = '####updated per insert trigger####';
917 SET @counter = @counter + 1;
918 END|
919 SET @counter = 1;
920 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
921 INSERT INTO t1 (f_char1, f_char2, f_charbig)
922 SELECT CAST(f_int1 AS CHAR),
923 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
924 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
925 ORDER BY f_int1;
926 DROP TRIGGER trg_3;
928 # check trigger-12 success: 1
929 DELETE FROM t1
930 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
931 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
932 AND f_charbig = '####updated per insert trigger####';
933 ANALYZE TABLE t1;
934 Table Op Msg_type Msg_text
935 test.t1 analyze note The storage engine for the table doesn't support analyze
936 CHECK TABLE t1 EXTENDED;
937 Table Op Msg_type Msg_text
938 test.t1 check note The storage engine for the table doesn't support check
939 CHECKSUM TABLE t1 EXTENDED;
940 Table Checksum
941 test.t1 <some_value>
942 OPTIMIZE TABLE t1;
943 Table Op Msg_type Msg_text
944 test.t1 optimize note The storage engine for the table doesn't support optimize
945 # check layout success: 1
946 REPAIR TABLE t1 EXTENDED;
947 Table Op Msg_type Msg_text
948 test.t1 repair note The storage engine for the table doesn't support repair
949 # check layout success: 1
950 TRUNCATE t1;
952 # check TRUNCATE success: 1
953 # check layout success: 1
954 # End usability test (inc/partition_check.inc)
955 DROP TABLE t1;
956 CREATE TABLE t1 (
957 f_int1 INTEGER,
958 f_int2 INTEGER,
959 f_char1 CHAR(20),
960 f_char2 CHAR(20),
961 f_charbig VARCHAR(1000)
962 )
963 PARTITION BY RANGE(f_int1)
964 SUBPARTITION BY HASH(f_int1)
965 ( PARTITION part1 VALUES LESS THAN (10)
966 (SUBPARTITION subpart11 STORAGE ENGINE = 'ndbcluster',
967 SUBPARTITION subpart12 STORAGE ENGINE = 'ndbcluster'),
968 PARTITION part2 VALUES LESS THAN (2147483646)
969 (SUBPARTITION subpart21 STORAGE ENGINE = 'ndbcluster',
970 SUBPARTITION subpart22 STORAGE ENGINE = 'ndbcluster')
971 );
972 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
973 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
974 # Start usability test (inc/partition_check.inc)
975 create_command
976 SHOW CREATE TABLE t1;
977 Table Create Table
978 t1 CREATE TABLE `t1` (
979 `f_int1` int(11) DEFAULT NULL,
980 `f_int2` int(11) DEFAULT NULL,
981 `f_char1` char(20) DEFAULT NULL,
982 `f_char2` char(20) DEFAULT NULL,
983 `f_charbig` varchar(1000) DEFAULT NULL
984 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
985 /*!50100 PARTITION BY RANGE (f_int1)
986 SUBPARTITION BY HASH (f_int1)
987 (PARTITION part1 VALUES LESS THAN (10)
988 (SUBPARTITION subpart11 ENGINE = ndbcluster,
989 SUBPARTITION subpart12 ENGINE = ndbcluster),
990 PARTITION part2 VALUES LESS THAN (2147483646)
991 (SUBPARTITION subpart21 ENGINE = ndbcluster,
992 SUBPARTITION subpart22 ENGINE = ndbcluster)) */
994 # check prerequisites-1 success: 1
995 # check COUNT(*) success: 1
996 # check MIN/MAX(f_int1) success: 1
997 # check MIN/MAX(f_int2) success: 1
998 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
999 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1000 CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
1001 WHERE f_int1 IN (2,3);
1002 # check prerequisites-3 success: 1
1003 DELETE FROM t1 WHERE f_charbig = 'delete me';
1004 # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
1005 # check read via f_int1 success: 1
1006 # check read via f_int2 success: 1
1008 # check multiple-1 success: 1
1009 DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
1011 # check multiple-2 success: 1
1012 INSERT INTO t1 SELECT * FROM t0_template
1013 WHERE MOD(f_int1,3) = 0;
1015 # check multiple-3 success: 1
1016 UPDATE t1 SET f_int1 = f_int1 + @max_row
1017 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
1018 AND @max_row_div2 + @max_row_div4;
1020 # check multiple-4 success: 1
1021 DELETE FROM t1
1022 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
1023 AND @max_row_div2 + @max_row_div4 + @max_row;
1025 # check multiple-5 success: 1
1026 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
1027 INSERT INTO t1
1028 SET f_int1 = @cur_value , f_int2 = @cur_value,
1029 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1030 f_charbig = '#SINGLE#';
1032 # check single-1 success: 1
1033 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
1034 INSERT INTO t1
1035 SET f_int1 = @cur_value , f_int2 = @cur_value,
1036 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1037 f_charbig = '#SINGLE#';
1039 # check single-2 success: 1
1040 SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
1041 SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
1042 UPDATE t1 SET f_int1 = @cur_value2
1043 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
1045 # check single-3 success: 1
1046 SET @cur_value1= -1;
1047 SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
1048 UPDATE t1 SET f_int1 = @cur_value1
1049 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
1051 # check single-4 success: 1
1052 SELECT MAX(f_int1) INTO @cur_value FROM t1;
1053 DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
1055 # check single-5 success: 1
1056 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
1058 # check single-6 success: 1
1059 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
1060 ERROR HY000: Table has no partition for value 2147483647
1061 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
1062 INSERT t1 SET f_int1 = 0 , f_int2 = 0,
1063 f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
1064 f_charbig = '#NULL#';
1065 INSERT INTO t1
1066 SET f_int1 = NULL , f_int2 = -@max_row,
1067 f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
1068 f_charbig = '#NULL#';
1069 # check null success: 1
1071 # check null-1 success: 1
1072 UPDATE t1 SET f_int1 = -@max_row
1073 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1074 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1076 # check null-2 success: 1
1077 UPDATE t1 SET f_int1 = NULL
1078 WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1079 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1081 # check null-3 success: 1
1082 DELETE FROM t1
1083 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1084 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1086 # check null-4 success: 1
1087 DELETE FROM t1
1088 WHERE f_int1 = 0 AND f_int2 = 0
1089 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
1090 AND f_charbig = '#NULL#';
1091 SET AUTOCOMMIT= 0;
1092 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1093 SELECT f_int1, f_int1, '', '', 'was inserted'
1094 FROM t0_template source_tab
1095 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1097 # check transactions-1 success: 1
1098 COMMIT WORK;
1100 # check transactions-2 success: 1
1101 ROLLBACK WORK;
1103 # check transactions-3 success: 1
1104 DELETE FROM t1 WHERE f_charbig = 'was inserted';
1105 COMMIT WORK;
1106 ROLLBACK WORK;
1108 # check transactions-4 success: 1
1109 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1110 SELECT f_int1, f_int1, '', '', 'was inserted'
1111 FROM t0_template source_tab
1112 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1114 # check transactions-5 success: 1
1115 ROLLBACK WORK;
1117 # check transactions-6 success: 1
1118 # INFO: Storage engine used for t1 seems to be transactional.
1119 COMMIT;
1121 # check transactions-7 success: 1
1122 DELETE FROM t1 WHERE f_charbig = 'was inserted';
1123 COMMIT WORK;
1124 SET @@session.sql_mode = 'traditional';
1125 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
1126 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1127 SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
1128 '', '', 'was inserted' FROM t0_template
1129 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1130 ERROR 22012: Division by 0
1131 COMMIT;
1133 # check transactions-8 success: 1
1134 # INFO: Storage engine used for t1 seems to be unable to revert
1135 # changes made by the failing statement.
1136 SET @@session.sql_mode = '';
1137 SET AUTOCOMMIT= 1;
1138 DELETE FROM t1 WHERE f_charbig = 'was inserted';
1139 COMMIT WORK;
1140 UPDATE t1 SET f_charbig = REPEAT('b', 1000);
1142 # check special-1 success: 1
1143 UPDATE t1 SET f_charbig = '';
1145 # check special-2 success: 1
1146 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
1147 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1148 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
1149 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1150 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1151 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1152 'just inserted' FROM t0_template
1153 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1154 CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
1155 BEGIN
1156 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1157 f_charbig = 'updated by trigger'
1158 WHERE f_int1 = new.f_int1;
1159 END|
1160 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1161 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1162 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1164 # check trigger-1 success: 1
1165 DROP TRIGGER trg_1;
1166 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1167 f_int2 = CAST(f_char1 AS SIGNED INT),
1168 f_charbig = 'just inserted'
1169 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1170 DELETE FROM t0_aux
1171 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1172 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1173 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1174 'just inserted' FROM t0_template
1175 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1176 CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
1177 BEGIN
1178 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1179 f_charbig = 'updated by trigger'
1180 WHERE f_int1 = new.f_int1;
1181 END|
1182 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1183 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1184 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1186 # check trigger-2 success: 1
1187 DROP TRIGGER trg_1;
1188 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1189 f_int2 = CAST(f_char1 AS SIGNED INT),
1190 f_charbig = 'just inserted'
1191 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1192 DELETE FROM t0_aux
1193 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1194 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1195 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1196 'just inserted' FROM t0_template
1197 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1198 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1199 BEGIN
1200 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1201 f_charbig = 'updated by trigger'
1202 WHERE f_int1 = new.f_int1;
1203 END|
1204 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1205 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1207 # check trigger-3 success: 1
1208 DROP TRIGGER trg_1;
1209 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1210 f_int2 = CAST(f_char1 AS SIGNED INT),
1211 f_charbig = 'just inserted'
1212 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1213 DELETE FROM t0_aux
1214 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1215 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1216 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1217 'just inserted' FROM t0_template
1218 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1219 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1220 BEGIN
1221 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1222 f_charbig = 'updated by trigger'
1223 WHERE f_int1 = - old.f_int1;
1224 END|
1225 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1226 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1228 # check trigger-4 success: 1
1229 DROP TRIGGER trg_1;
1230 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1231 f_int2 = CAST(f_char1 AS SIGNED INT),
1232 f_charbig = 'just inserted'
1233 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1234 DELETE FROM t0_aux
1235 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1236 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1237 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1238 'just inserted' FROM t0_template
1239 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1240 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1241 BEGIN
1242 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1243 f_charbig = 'updated by trigger'
1244 WHERE f_int1 = new.f_int1;
1245 END|
1246 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1247 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1249 # check trigger-5 success: 1
1250 DROP TRIGGER trg_1;
1251 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1252 f_int2 = CAST(f_char1 AS SIGNED INT),
1253 f_charbig = 'just inserted'
1254 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1255 DELETE FROM t0_aux
1256 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1257 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1258 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1259 'just inserted' FROM t0_template
1260 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1261 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1262 BEGIN
1263 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1264 f_charbig = 'updated by trigger'
1265 WHERE f_int1 = - old.f_int1;
1266 END|
1267 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1268 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1270 # check trigger-6 success: 1
1271 DROP TRIGGER trg_1;
1272 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1273 f_int2 = CAST(f_char1 AS SIGNED INT),
1274 f_charbig = 'just inserted'
1275 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1276 DELETE FROM t0_aux
1277 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1278 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1279 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1280 'just inserted' FROM t0_template
1281 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1282 CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
1283 BEGIN
1284 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1285 f_charbig = 'updated by trigger'
1286 WHERE f_int1 = - old.f_int1;
1287 END|
1288 DELETE FROM t0_aux
1289 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1291 # check trigger-7 success: 1
1292 DROP TRIGGER trg_1;
1293 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1294 f_int2 = CAST(f_char1 AS SIGNED INT),
1295 f_charbig = 'just inserted'
1296 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1297 DELETE FROM t0_aux
1298 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1299 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1300 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1301 'just inserted' FROM t0_template
1302 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1303 CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
1304 BEGIN
1305 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1306 f_charbig = 'updated by trigger'
1307 WHERE f_int1 = - old.f_int1;
1308 END|
1309 DELETE FROM t0_aux
1310 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1312 # check trigger-8 success: 1
1313 DROP TRIGGER trg_1;
1314 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1315 f_int2 = CAST(f_char1 AS SIGNED INT),
1316 f_charbig = 'just inserted'
1317 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1318 DELETE FROM t0_aux
1319 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1320 DELETE FROM t1
1321 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1322 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1323 BEGIN
1324 SET new.f_int1 = old.f_int1 + @max_row,
1325 new.f_int2 = old.f_int2 - @max_row,
1326 new.f_charbig = '####updated per update trigger####';
1327 END|
1328 UPDATE t1
1329 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1330 f_charbig = '####updated per update statement itself####';
1332 # check trigger-9 success: 1
1333 DROP TRIGGER trg_2;
1334 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1335 f_int2 = CAST(f_char1 AS SIGNED INT),
1336 f_charbig = CONCAT('===',f_char1,'===');
1337 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1338 BEGIN
1339 SET new.f_int1 = new.f_int1 + @max_row,
1340 new.f_int2 = new.f_int2 - @max_row,
1341 new.f_charbig = '####updated per update trigger####';
1342 END|
1343 UPDATE t1
1344 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1345 f_charbig = '####updated per update statement itself####';
1347 # check trigger-10 success: 1
1348 DROP TRIGGER trg_2;
1349 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1350 f_int2 = CAST(f_char1 AS SIGNED INT),
1351 f_charbig = CONCAT('===',f_char1,'===');
1352 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1353 BEGIN
1354 SET new.f_int1 = @my_max1 + @counter,
1355 new.f_int2 = @my_min2 - @counter,
1356 new.f_charbig = '####updated per insert trigger####';
1357 SET @counter = @counter + 1;
1358 END|
1359 SET @counter = 1;
1360 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1361 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1362 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1363 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1364 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1365 ORDER BY f_int1;
1366 DROP TRIGGER trg_3;
1368 # check trigger-11 success: 1
1369 DELETE FROM t1
1370 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1371 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1372 AND f_charbig = '####updated per insert trigger####';
1373 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1374 BEGIN
1375 SET new.f_int1 = @my_max1 + @counter,
1376 new.f_int2 = @my_min2 - @counter,
1377 new.f_charbig = '####updated per insert trigger####';
1378 SET @counter = @counter + 1;
1379 END|
1380 SET @counter = 1;
1381 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1382 INSERT INTO t1 (f_char1, f_char2, f_charbig)
1383 SELECT CAST(f_int1 AS CHAR),
1384 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1385 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1386 ORDER BY f_int1;
1387 DROP TRIGGER trg_3;
1389 # check trigger-12 success: 1
1390 DELETE FROM t1
1391 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1392 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1393 AND f_charbig = '####updated per insert trigger####';
1394 ANALYZE TABLE t1;
1395 Table Op Msg_type Msg_text
1396 test.t1 analyze note The storage engine for the table doesn't support analyze
1397 CHECK TABLE t1 EXTENDED;
1398 Table Op Msg_type Msg_text
1399 test.t1 check note The storage engine for the table doesn't support check
1400 CHECKSUM TABLE t1 EXTENDED;
1401 Table Checksum
1402 test.t1 <some_value>
1403 OPTIMIZE TABLE t1;
1404 Table Op Msg_type Msg_text
1405 test.t1 optimize note The storage engine for the table doesn't support optimize
1406 # check layout success: 1
1407 REPAIR TABLE t1 EXTENDED;
1408 Table Op Msg_type Msg_text
1409 test.t1 repair note The storage engine for the table doesn't support repair
1410 # check layout success: 1
1411 TRUNCATE t1;
1413 # check TRUNCATE success: 1
1414 # check layout success: 1
1415 # End usability test (inc/partition_check.inc)
1416 DROP TABLE t1;
1417 #------------------------------------------------------------------------
1418 # 3 Some but not all named partitions or subpartitions get a storage
1419 # engine assigned
1420 #------------------------------------------------------------------------
1421 CREATE TABLE t1 (
1422 f_int1 INTEGER,
1423 f_int2 INTEGER,
1424 f_char1 CHAR(20),
1425 f_char2 CHAR(20),
1426 f_charbig VARCHAR(1000)
1427 )
1428 PARTITION BY HASH(f_int1)
1429 ( PARTITION part1 STORAGE ENGINE = 'ndbcluster',
1430 PARTITION part2
1431 );
1432 ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1433 CREATE TABLE t1 (
1434 f_int1 INTEGER,
1435 f_int2 INTEGER,
1436 f_char1 CHAR(20),
1437 f_char2 CHAR(20),
1438 f_charbig VARCHAR(1000)
1439 )
1440 PARTITION BY HASH(f_int1)
1441 ( PARTITION part1 ,
1442 PARTITION part2 STORAGE ENGINE = 'ndbcluster'
1443 );
1444 ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1445 CREATE TABLE t1 (
1446 f_int1 INTEGER,
1447 f_int2 INTEGER,
1448 f_char1 CHAR(20),
1449 f_char2 CHAR(20),
1450 f_charbig VARCHAR(1000)
1451 )
1452 PARTITION BY RANGE(f_int1)
1453 SUBPARTITION BY HASH(f_int1)
1454 ( PARTITION part1 VALUES LESS THAN (10)
1455 (SUBPARTITION subpart11,
1456 SUBPARTITION subpart12 STORAGE ENGINE = 'ndbcluster'),
1457 PARTITION part2 VALUES LESS THAN (2147483646)
1458 (SUBPARTITION subpart21 STORAGE ENGINE = 'ndbcluster',
1459 SUBPARTITION subpart22 STORAGE ENGINE = 'ndbcluster')
1460 );
1461 ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1462 CREATE TABLE t1 (
1463 f_int1 INTEGER,
1464 f_int2 INTEGER,
1465 f_char1 CHAR(20),
1466 f_char2 CHAR(20),
1467 f_charbig VARCHAR(1000)
1468 )
1469 PARTITION BY RANGE(f_int1)
1470 SUBPARTITION BY HASH(f_int1)
1471 ( PARTITION part1 VALUES LESS THAN (10)
1472 (SUBPARTITION subpart11 STORAGE ENGINE = 'ndbcluster',
1473 SUBPARTITION subpart12 STORAGE ENGINE = 'ndbcluster'),
1474 PARTITION part2 VALUES LESS THAN (2147483646)
1475 (SUBPARTITION subpart21,
1476 SUBPARTITION subpart22 )
1477 );
1478 ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1479 CREATE TABLE t1 (
1480 f_int1 INTEGER,
1481 f_int2 INTEGER,
1482 f_char1 CHAR(20),
1483 f_char2 CHAR(20),
1484 f_charbig VARCHAR(1000)
1485 )
1486 ENGINE = 'ndbcluster'
1487 PARTITION BY RANGE(f_int1)
1488 SUBPARTITION BY HASH(f_int1)
1489 ( PARTITION part1 VALUES LESS THAN (10)
1490 (SUBPARTITION subpart11 STORAGE ENGINE = 'ndbcluster',
1491 SUBPARTITION subpart12 STORAGE ENGINE = 'ndbcluster'),
1492 PARTITION part2 VALUES LESS THAN (2147483646)
1493 (SUBPARTITION subpart21,
1494 SUBPARTITION subpart22 )
1495 );
1496 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1497 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
1498 # Start usability test (inc/partition_check.inc)
1499 create_command
1500 SHOW CREATE TABLE t1;
1501 Table Create Table
1502 t1 CREATE TABLE `t1` (
1503 `f_int1` int(11) DEFAULT NULL,
1504 `f_int2` int(11) DEFAULT NULL,
1505 `f_char1` char(20) DEFAULT NULL,
1506 `f_char2` char(20) DEFAULT NULL,
1507 `f_charbig` varchar(1000) DEFAULT NULL
1508 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1509 /*!50100 PARTITION BY RANGE (f_int1)
1510 SUBPARTITION BY HASH (f_int1)
1511 (PARTITION part1 VALUES LESS THAN (10)
1512 (SUBPARTITION subpart11 ENGINE = ndbcluster,
1513 SUBPARTITION subpart12 ENGINE = ndbcluster),
1514 PARTITION part2 VALUES LESS THAN (2147483646)
1515 (SUBPARTITION subpart21 ENGINE = ndbcluster,
1516 SUBPARTITION subpart22 ENGINE = ndbcluster)) */
1518 # check prerequisites-1 success: 1
1519 # check COUNT(*) success: 1
1520 # check MIN/MAX(f_int1) success: 1
1521 # check MIN/MAX(f_int2) success: 1
1522 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1523 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1524 CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
1525 WHERE f_int1 IN (2,3);
1526 # check prerequisites-3 success: 1
1527 DELETE FROM t1 WHERE f_charbig = 'delete me';
1528 # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
1529 # check read via f_int1 success: 1
1530 # check read via f_int2 success: 1
1532 # check multiple-1 success: 1
1533 DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
1535 # check multiple-2 success: 1
1536 INSERT INTO t1 SELECT * FROM t0_template
1537 WHERE MOD(f_int1,3) = 0;
1539 # check multiple-3 success: 1
1540 UPDATE t1 SET f_int1 = f_int1 + @max_row
1541 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
1542 AND @max_row_div2 + @max_row_div4;
1544 # check multiple-4 success: 1
1545 DELETE FROM t1
1546 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
1547 AND @max_row_div2 + @max_row_div4 + @max_row;
1549 # check multiple-5 success: 1
1550 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
1551 INSERT INTO t1
1552 SET f_int1 = @cur_value , f_int2 = @cur_value,
1553 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1554 f_charbig = '#SINGLE#';
1556 # check single-1 success: 1
1557 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
1558 INSERT INTO t1
1559 SET f_int1 = @cur_value , f_int2 = @cur_value,
1560 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1561 f_charbig = '#SINGLE#';
1563 # check single-2 success: 1
1564 SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
1565 SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
1566 UPDATE t1 SET f_int1 = @cur_value2
1567 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
1569 # check single-3 success: 1
1570 SET @cur_value1= -1;
1571 SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
1572 UPDATE t1 SET f_int1 = @cur_value1
1573 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
1575 # check single-4 success: 1
1576 SELECT MAX(f_int1) INTO @cur_value FROM t1;
1577 DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
1579 # check single-5 success: 1
1580 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
1582 # check single-6 success: 1
1583 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
1584 ERROR HY000: Table has no partition for value 2147483647
1585 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
1586 INSERT t1 SET f_int1 = 0 , f_int2 = 0,
1587 f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
1588 f_charbig = '#NULL#';
1589 INSERT INTO t1
1590 SET f_int1 = NULL , f_int2 = -@max_row,
1591 f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
1592 f_charbig = '#NULL#';
1593 # check null success: 1
1595 # check null-1 success: 1
1596 UPDATE t1 SET f_int1 = -@max_row
1597 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1598 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1600 # check null-2 success: 1
1601 UPDATE t1 SET f_int1 = NULL
1602 WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1603 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1605 # check null-3 success: 1
1606 DELETE FROM t1
1607 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1608 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1610 # check null-4 success: 1
1611 DELETE FROM t1
1612 WHERE f_int1 = 0 AND f_int2 = 0
1613 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
1614 AND f_charbig = '#NULL#';
1615 SET AUTOCOMMIT= 0;
1616 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1617 SELECT f_int1, f_int1, '', '', 'was inserted'
1618 FROM t0_template source_tab
1619 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1621 # check transactions-1 success: 1
1622 COMMIT WORK;
1624 # check transactions-2 success: 1
1625 ROLLBACK WORK;
1627 # check transactions-3 success: 1
1628 DELETE FROM t1 WHERE f_charbig = 'was inserted';
1629 COMMIT WORK;
1630 ROLLBACK WORK;
1632 # check transactions-4 success: 1
1633 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1634 SELECT f_int1, f_int1, '', '', 'was inserted'
1635 FROM t0_template source_tab
1636 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1638 # check transactions-5 success: 1
1639 ROLLBACK WORK;
1641 # check transactions-6 success: 1
1642 # INFO: Storage engine used for t1 seems to be transactional.
1643 COMMIT;
1645 # check transactions-7 success: 1
1646 DELETE FROM t1 WHERE f_charbig = 'was inserted';
1647 COMMIT WORK;
1648 SET @@session.sql_mode = 'traditional';
1649 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
1650 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1651 SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
1652 '', '', 'was inserted' FROM t0_template
1653 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1654 ERROR 22012: Division by 0
1655 COMMIT;
1657 # check transactions-8 success: 1
1658 # INFO: Storage engine used for t1 seems to be unable to revert
1659 # changes made by the failing statement.
1660 SET @@session.sql_mode = '';
1661 SET AUTOCOMMIT= 1;
1662 DELETE FROM t1 WHERE f_charbig = 'was inserted';
1663 COMMIT WORK;
1664 UPDATE t1 SET f_charbig = REPEAT('b', 1000);
1666 # check special-1 success: 1
1667 UPDATE t1 SET f_charbig = '';
1669 # check special-2 success: 1
1670 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
1671 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1672 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
1673 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1674 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1675 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1676 'just inserted' FROM t0_template
1677 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1678 CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
1679 BEGIN
1680 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1681 f_charbig = 'updated by trigger'
1682 WHERE f_int1 = new.f_int1;
1683 END|
1684 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1685 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1686 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1688 # check trigger-1 success: 1
1689 DROP TRIGGER trg_1;
1690 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1691 f_int2 = CAST(f_char1 AS SIGNED INT),
1692 f_charbig = 'just inserted'
1693 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1694 DELETE FROM t0_aux
1695 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1696 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1697 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1698 'just inserted' FROM t0_template
1699 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1700 CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
1701 BEGIN
1702 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1703 f_charbig = 'updated by trigger'
1704 WHERE f_int1 = new.f_int1;
1705 END|
1706 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1707 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1708 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1710 # check trigger-2 success: 1
1711 DROP TRIGGER trg_1;
1712 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1713 f_int2 = CAST(f_char1 AS SIGNED INT),
1714 f_charbig = 'just inserted'
1715 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1716 DELETE FROM t0_aux
1717 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1718 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1719 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1720 'just inserted' FROM t0_template
1721 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1722 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1723 BEGIN
1724 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1725 f_charbig = 'updated by trigger'
1726 WHERE f_int1 = new.f_int1;
1727 END|
1728 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1729 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1731 # check trigger-3 success: 1
1732 DROP TRIGGER trg_1;
1733 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1734 f_int2 = CAST(f_char1 AS SIGNED INT),
1735 f_charbig = 'just inserted'
1736 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1737 DELETE FROM t0_aux
1738 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1739 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1740 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1741 'just inserted' FROM t0_template
1742 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1743 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1744 BEGIN
1745 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1746 f_charbig = 'updated by trigger'
1747 WHERE f_int1 = - old.f_int1;
1748 END|
1749 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1750 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1752 # check trigger-4 success: 1
1753 DROP TRIGGER trg_1;
1754 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1755 f_int2 = CAST(f_char1 AS SIGNED INT),
1756 f_charbig = 'just inserted'
1757 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1758 DELETE FROM t0_aux
1759 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1760 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1761 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1762 'just inserted' FROM t0_template
1763 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1764 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1765 BEGIN
1766 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1767 f_charbig = 'updated by trigger'
1768 WHERE f_int1 = new.f_int1;
1769 END|
1770 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1771 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1773 # check trigger-5 success: 1
1774 DROP TRIGGER trg_1;
1775 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1776 f_int2 = CAST(f_char1 AS SIGNED INT),
1777 f_charbig = 'just inserted'
1778 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1779 DELETE FROM t0_aux
1780 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1781 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1782 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1783 'just inserted' FROM t0_template
1784 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1785 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1786 BEGIN
1787 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1788 f_charbig = 'updated by trigger'
1789 WHERE f_int1 = - old.f_int1;
1790 END|
1791 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1792 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1794 # check trigger-6 success: 1
1795 DROP TRIGGER trg_1;
1796 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1797 f_int2 = CAST(f_char1 AS SIGNED INT),
1798 f_charbig = 'just inserted'
1799 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1800 DELETE FROM t0_aux
1801 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1802 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1803 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1804 'just inserted' FROM t0_template
1805 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1806 CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
1807 BEGIN
1808 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1809 f_charbig = 'updated by trigger'
1810 WHERE f_int1 = - old.f_int1;
1811 END|
1812 DELETE FROM t0_aux
1813 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1815 # check trigger-7 success: 1
1816 DROP TRIGGER trg_1;
1817 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1818 f_int2 = CAST(f_char1 AS SIGNED INT),
1819 f_charbig = 'just inserted'
1820 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1821 DELETE FROM t0_aux
1822 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1823 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1824 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1825 'just inserted' FROM t0_template
1826 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1827 CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
1828 BEGIN
1829 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1830 f_charbig = 'updated by trigger'
1831 WHERE f_int1 = - old.f_int1;
1832 END|
1833 DELETE FROM t0_aux
1834 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1836 # check trigger-8 success: 1
1837 DROP TRIGGER trg_1;
1838 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1839 f_int2 = CAST(f_char1 AS SIGNED INT),
1840 f_charbig = 'just inserted'
1841 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1842 DELETE FROM t0_aux
1843 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1844 DELETE FROM t1
1845 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1846 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1847 BEGIN
1848 SET new.f_int1 = old.f_int1 + @max_row,
1849 new.f_int2 = old.f_int2 - @max_row,
1850 new.f_charbig = '####updated per update trigger####';
1851 END|
1852 UPDATE t1
1853 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1854 f_charbig = '####updated per update statement itself####';
1856 # check trigger-9 success: 1
1857 DROP TRIGGER trg_2;
1858 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1859 f_int2 = CAST(f_char1 AS SIGNED INT),
1860 f_charbig = CONCAT('===',f_char1,'===');
1861 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1862 BEGIN
1863 SET new.f_int1 = new.f_int1 + @max_row,
1864 new.f_int2 = new.f_int2 - @max_row,
1865 new.f_charbig = '####updated per update trigger####';
1866 END|
1867 UPDATE t1
1868 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1869 f_charbig = '####updated per update statement itself####';
1871 # check trigger-10 success: 1
1872 DROP TRIGGER trg_2;
1873 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1874 f_int2 = CAST(f_char1 AS SIGNED INT),
1875 f_charbig = CONCAT('===',f_char1,'===');
1876 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1877 BEGIN
1878 SET new.f_int1 = @my_max1 + @counter,
1879 new.f_int2 = @my_min2 - @counter,
1880 new.f_charbig = '####updated per insert trigger####';
1881 SET @counter = @counter + 1;
1882 END|
1883 SET @counter = 1;
1884 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1885 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1886 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1887 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1888 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1889 ORDER BY f_int1;
1890 DROP TRIGGER trg_3;
1892 # check trigger-11 success: 1
1893 DELETE FROM t1
1894 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1895 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1896 AND f_charbig = '####updated per insert trigger####';
1897 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1898 BEGIN
1899 SET new.f_int1 = @my_max1 + @counter,
1900 new.f_int2 = @my_min2 - @counter,
1901 new.f_charbig = '####updated per insert trigger####';
1902 SET @counter = @counter + 1;
1903 END|
1904 SET @counter = 1;
1905 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1906 INSERT INTO t1 (f_char1, f_char2, f_charbig)
1907 SELECT CAST(f_int1 AS CHAR),
1908 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1909 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1910 ORDER BY f_int1;
1911 DROP TRIGGER trg_3;
1913 # check trigger-12 success: 1
1914 DELETE FROM t1
1915 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1916 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1917 AND f_charbig = '####updated per insert trigger####';
1918 ANALYZE TABLE t1;
1919 Table Op Msg_type Msg_text
1920 test.t1 analyze note The storage engine for the table doesn't support analyze
1921 CHECK TABLE t1 EXTENDED;
1922 Table Op Msg_type Msg_text
1923 test.t1 check note The storage engine for the table doesn't support check
1924 CHECKSUM TABLE t1 EXTENDED;
1925 Table Checksum
1926 test.t1 <some_value>
1927 OPTIMIZE TABLE t1;
1928 Table Op Msg_type Msg_text
1929 test.t1 optimize note The storage engine for the table doesn't support optimize
1930 # check layout success: 1
1931 REPAIR TABLE t1 EXTENDED;
1932 Table Op Msg_type Msg_text
1933 test.t1 repair note The storage engine for the table doesn't support repair
1934 # check layout success: 1
1935 TRUNCATE t1;
1937 # check TRUNCATE success: 1
1938 # check layout success: 1
1939 # End usability test (inc/partition_check.inc)
1940 DROP TABLE t1;
1941 #------------------------------------------------------------------------
1942 # 4 Storage engine assignment after partition name + after name of
1943 # subpartitions belonging to another partition
1944 #------------------------------------------------------------------------
1945 CREATE TABLE t1 (
1946 f_int1 INTEGER,
1947 f_int2 INTEGER,
1948 f_char1 CHAR(20),
1949 f_char2 CHAR(20),
1950 f_charbig VARCHAR(1000)
1951 )
1952 PARTITION BY RANGE(f_int1)
1953 SUBPARTITION BY HASH(f_int1)
1954 ( PARTITION part1 VALUES LESS THAN (10)
1955 (SUBPARTITION subpart11,
1956 SUBPARTITION subpart12),
1957 PARTITION part2 VALUES LESS THAN (2147483646)
1958 (SUBPARTITION subpart21 STORAGE ENGINE = 'ndbcluster',
1959 SUBPARTITION subpart22 STORAGE ENGINE = 'ndbcluster')
1960 );
1961 ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1962 CREATE TABLE t1 (
1963 f_int1 INTEGER,
1964 f_int2 INTEGER,
1965 f_char1 CHAR(20),
1966 f_char2 CHAR(20),
1967 f_charbig VARCHAR(1000)
1968 )
1969 ENGINE = 'ndbcluster'
1970 PARTITION BY RANGE(f_int1)
1971 SUBPARTITION BY HASH(f_int1)
1972 ( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'ndbcluster'
1973 (SUBPARTITION subpart11,
1974 SUBPARTITION subpart12),
1975 PARTITION part2 VALUES LESS THAN (2147483646)
1976 (SUBPARTITION subpart21,
1977 SUBPARTITION subpart22 STORAGE ENGINE = 'ndbcluster')
1978 );
1979 DROP TABLE t1;
1980 CREATE TABLE t1 (
1981 f_int1 INTEGER,
1982 f_int2 INTEGER,
1983 f_char1 CHAR(20),
1984 f_char2 CHAR(20),
1985 f_charbig VARCHAR(1000)
1986 )
1987 PARTITION BY RANGE(f_int1)
1988 SUBPARTITION BY HASH(f_int1)
1989 ( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'ndbcluster'
1990 (SUBPARTITION subpart11,
1991 SUBPARTITION subpart12),
1992 PARTITION part2 VALUES LESS THAN (2147483646)
1993 (SUBPARTITION subpart21 STORAGE ENGINE = 'ndbcluster',
1994 SUBPARTITION subpart22 STORAGE ENGINE = 'ndbcluster')
1995 );
1996 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1997 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
1998 # Start usability test (inc/partition_check.inc)
1999 create_command
2000 SHOW CREATE TABLE t1;
2001 Table Create Table
2002 t1 CREATE TABLE `t1` (
2003 `f_int1` int(11) DEFAULT NULL,
2004 `f_int2` int(11) DEFAULT NULL,
2005 `f_char1` char(20) DEFAULT NULL,
2006 `f_char2` char(20) DEFAULT NULL,
2007 `f_charbig` varchar(1000) DEFAULT NULL
2008 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
2009 /*!50100 PARTITION BY RANGE (f_int1)
2010 SUBPARTITION BY HASH (f_int1)
2011 (PARTITION part1 VALUES LESS THAN (10)
2012 (SUBPARTITION subpart11 ENGINE = ndbcluster,
2013 SUBPARTITION subpart12 ENGINE = ndbcluster),
2014 PARTITION part2 VALUES LESS THAN (2147483646)
2015 (SUBPARTITION subpart21 ENGINE = ndbcluster,
2016 SUBPARTITION subpart22 ENGINE = ndbcluster)) */
2018 # check prerequisites-1 success: 1
2019 # check COUNT(*) success: 1
2020 # check MIN/MAX(f_int1) success: 1
2021 # check MIN/MAX(f_int2) success: 1
2022 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2023 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2024 CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2025 WHERE f_int1 IN (2,3);
2026 # check prerequisites-3 success: 1
2027 DELETE FROM t1 WHERE f_charbig = 'delete me';
2028 # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2029 # check read via f_int1 success: 1
2030 # check read via f_int2 success: 1
2032 # check multiple-1 success: 1
2033 DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2035 # check multiple-2 success: 1
2036 INSERT INTO t1 SELECT * FROM t0_template
2037 WHERE MOD(f_int1,3) = 0;
2039 # check multiple-3 success: 1
2040 UPDATE t1 SET f_int1 = f_int1 + @max_row
2041 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2042 AND @max_row_div2 + @max_row_div4;
2044 # check multiple-4 success: 1
2045 DELETE FROM t1
2046 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2047 AND @max_row_div2 + @max_row_div4 + @max_row;
2049 # check multiple-5 success: 1
2050 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2051 INSERT INTO t1
2052 SET f_int1 = @cur_value , f_int2 = @cur_value,
2053 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2054 f_charbig = '#SINGLE#';
2056 # check single-1 success: 1
2057 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2058 INSERT INTO t1
2059 SET f_int1 = @cur_value , f_int2 = @cur_value,
2060 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2061 f_charbig = '#SINGLE#';
2063 # check single-2 success: 1
2064 SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2065 SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2066 UPDATE t1 SET f_int1 = @cur_value2
2067 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2069 # check single-3 success: 1
2070 SET @cur_value1= -1;
2071 SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2072 UPDATE t1 SET f_int1 = @cur_value1
2073 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2075 # check single-4 success: 1
2076 SELECT MAX(f_int1) INTO @cur_value FROM t1;
2077 DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
2079 # check single-5 success: 1
2080 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
2082 # check single-6 success: 1
2083 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
2084 ERROR HY000: Table has no partition for value 2147483647
2085 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
2086 INSERT t1 SET f_int1 = 0 , f_int2 = 0,
2087 f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
2088 f_charbig = '#NULL#';
2089 INSERT INTO t1
2090 SET f_int1 = NULL , f_int2 = -@max_row,
2091 f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
2092 f_charbig = '#NULL#';
2093 # check null success: 1
2095 # check null-1 success: 1
2096 UPDATE t1 SET f_int1 = -@max_row
2097 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2098 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2100 # check null-2 success: 1
2101 UPDATE t1 SET f_int1 = NULL
2102 WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2103 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2105 # check null-3 success: 1
2106 DELETE FROM t1
2107 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2108 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2110 # check null-4 success: 1
2111 DELETE FROM t1
2112 WHERE f_int1 = 0 AND f_int2 = 0
2113 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
2114 AND f_charbig = '#NULL#';
2115 SET AUTOCOMMIT= 0;
2116 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2117 SELECT f_int1, f_int1, '', '', 'was inserted'
2118 FROM t0_template source_tab
2119 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2121 # check transactions-1 success: 1
2122 COMMIT WORK;
2124 # check transactions-2 success: 1
2125 ROLLBACK WORK;
2127 # check transactions-3 success: 1
2128 DELETE FROM t1 WHERE f_charbig = 'was inserted';
2129 COMMIT WORK;
2130 ROLLBACK WORK;
2132 # check transactions-4 success: 1
2133 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2134 SELECT f_int1, f_int1, '', '', 'was inserted'
2135 FROM t0_template source_tab
2136 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2138 # check transactions-5 success: 1
2139 ROLLBACK WORK;
2141 # check transactions-6 success: 1
2142 # INFO: Storage engine used for t1 seems to be transactional.
2143 COMMIT;
2145 # check transactions-7 success: 1
2146 DELETE FROM t1 WHERE f_charbig = 'was inserted';
2147 COMMIT WORK;
2148 SET @@session.sql_mode = 'traditional';
2149 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
2150 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2151 SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
2152 '', '', 'was inserted' FROM t0_template
2153 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2154 ERROR 22012: Division by 0
2155 COMMIT;
2157 # check transactions-8 success: 1
2158 # INFO: Storage engine used for t1 seems to be unable to revert
2159 # changes made by the failing statement.
2160 SET @@session.sql_mode = '';
2161 SET AUTOCOMMIT= 1;
2162 DELETE FROM t1 WHERE f_charbig = 'was inserted';
2163 COMMIT WORK;
2164 UPDATE t1 SET f_charbig = REPEAT('b', 1000);
2166 # check special-1 success: 1
2167 UPDATE t1 SET f_charbig = '';
2169 # check special-2 success: 1
2170 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
2171 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2172 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
2173 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2174 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2175 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2176 'just inserted' FROM t0_template
2177 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2178 CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
2179 BEGIN
2180 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2181 f_charbig = 'updated by trigger'
2182 WHERE f_int1 = new.f_int1;
2183 END|
2184 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2185 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2186 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2188 # check trigger-1 success: 1
2189 DROP TRIGGER trg_1;
2190 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2191 f_int2 = CAST(f_char1 AS SIGNED INT),
2192 f_charbig = 'just inserted'
2193 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2194 DELETE FROM t0_aux
2195 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2196 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2197 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2198 'just inserted' FROM t0_template
2199 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2200 CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
2201 BEGIN
2202 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2203 f_charbig = 'updated by trigger'
2204 WHERE f_int1 = new.f_int1;
2205 END|
2206 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2207 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2208 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2210 # check trigger-2 success: 1
2211 DROP TRIGGER trg_1;
2212 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2213 f_int2 = CAST(f_char1 AS SIGNED INT),
2214 f_charbig = 'just inserted'
2215 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2216 DELETE FROM t0_aux
2217 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2218 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2219 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2220 'just inserted' FROM t0_template
2221 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2222 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2223 BEGIN
2224 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2225 f_charbig = 'updated by trigger'
2226 WHERE f_int1 = new.f_int1;
2227 END|
2228 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2229 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2231 # check trigger-3 success: 1
2232 DROP TRIGGER trg_1;
2233 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2234 f_int2 = CAST(f_char1 AS SIGNED INT),
2235 f_charbig = 'just inserted'
2236 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2237 DELETE FROM t0_aux
2238 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2239 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2240 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2241 'just inserted' FROM t0_template
2242 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2243 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2244 BEGIN
2245 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2246 f_charbig = 'updated by trigger'
2247 WHERE f_int1 = - old.f_int1;
2248 END|
2249 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2250 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2252 # check trigger-4 success: 1
2253 DROP TRIGGER trg_1;
2254 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2255 f_int2 = CAST(f_char1 AS SIGNED INT),
2256 f_charbig = 'just inserted'
2257 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2258 DELETE FROM t0_aux
2259 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2260 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2261 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2262 'just inserted' FROM t0_template
2263 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2264 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2265 BEGIN
2266 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2267 f_charbig = 'updated by trigger'
2268 WHERE f_int1 = new.f_int1;
2269 END|
2270 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2271 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2273 # check trigger-5 success: 1
2274 DROP TRIGGER trg_1;
2275 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2276 f_int2 = CAST(f_char1 AS SIGNED INT),
2277 f_charbig = 'just inserted'
2278 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2279 DELETE FROM t0_aux
2280 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2281 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2282 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2283 'just inserted' FROM t0_template
2284 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2285 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2286 BEGIN
2287 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2288 f_charbig = 'updated by trigger'
2289 WHERE f_int1 = - old.f_int1;
2290 END|
2291 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2292 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2294 # check trigger-6 success: 1
2295 DROP TRIGGER trg_1;
2296 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2297 f_int2 = CAST(f_char1 AS SIGNED INT),
2298 f_charbig = 'just inserted'
2299 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2300 DELETE FROM t0_aux
2301 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2302 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2303 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2304 'just inserted' FROM t0_template
2305 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2306 CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
2307 BEGIN
2308 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2309 f_charbig = 'updated by trigger'
2310 WHERE f_int1 = - old.f_int1;
2311 END|
2312 DELETE FROM t0_aux
2313 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2315 # check trigger-7 success: 1
2316 DROP TRIGGER trg_1;
2317 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2318 f_int2 = CAST(f_char1 AS SIGNED INT),
2319 f_charbig = 'just inserted'
2320 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2321 DELETE FROM t0_aux
2322 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2323 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2324 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2325 'just inserted' FROM t0_template
2326 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2327 CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
2328 BEGIN
2329 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2330 f_charbig = 'updated by trigger'
2331 WHERE f_int1 = - old.f_int1;
2332 END|
2333 DELETE FROM t0_aux
2334 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2336 # check trigger-8 success: 1
2337 DROP TRIGGER trg_1;
2338 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2339 f_int2 = CAST(f_char1 AS SIGNED INT),
2340 f_charbig = 'just inserted'
2341 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2342 DELETE FROM t0_aux
2343 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2344 DELETE FROM t1
2345 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2346 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2347 BEGIN
2348 SET new.f_int1 = old.f_int1 + @max_row,
2349 new.f_int2 = old.f_int2 - @max_row,
2350 new.f_charbig = '####updated per update trigger####';
2351 END|
2352 UPDATE t1
2353 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2354 f_charbig = '####updated per update statement itself####';
2356 # check trigger-9 success: 1
2357 DROP TRIGGER trg_2;
2358 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2359 f_int2 = CAST(f_char1 AS SIGNED INT),
2360 f_charbig = CONCAT('===',f_char1,'===');
2361 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2362 BEGIN
2363 SET new.f_int1 = new.f_int1 + @max_row,
2364 new.f_int2 = new.f_int2 - @max_row,
2365 new.f_charbig = '####updated per update trigger####';
2366 END|
2367 UPDATE t1
2368 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2369 f_charbig = '####updated per update statement itself####';
2371 # check trigger-10 success: 1
2372 DROP TRIGGER trg_2;
2373 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2374 f_int2 = CAST(f_char1 AS SIGNED INT),
2375 f_charbig = CONCAT('===',f_char1,'===');
2376 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2377 BEGIN
2378 SET new.f_int1 = @my_max1 + @counter,
2379 new.f_int2 = @my_min2 - @counter,
2380 new.f_charbig = '####updated per insert trigger####';
2381 SET @counter = @counter + 1;
2382 END|
2383 SET @counter = 1;
2384 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2385 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2386 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2387 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2388 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2389 ORDER BY f_int1;
2390 DROP TRIGGER trg_3;
2392 # check trigger-11 success: 1
2393 DELETE FROM t1
2394 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2395 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2396 AND f_charbig = '####updated per insert trigger####';
2397 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2398 BEGIN
2399 SET new.f_int1 = @my_max1 + @counter,
2400 new.f_int2 = @my_min2 - @counter,
2401 new.f_charbig = '####updated per insert trigger####';
2402 SET @counter = @counter + 1;
2403 END|
2404 SET @counter = 1;
2405 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2406 INSERT INTO t1 (f_char1, f_char2, f_charbig)
2407 SELECT CAST(f_int1 AS CHAR),
2408 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2409 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2410 ORDER BY f_int1;
2411 DROP TRIGGER trg_3;
2413 # check trigger-12 success: 1
2414 DELETE FROM t1
2415 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2416 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2417 AND f_charbig = '####updated per insert trigger####';
2418 ANALYZE TABLE t1;
2419 Table Op Msg_type Msg_text
2420 test.t1 analyze note The storage engine for the table doesn't support analyze
2421 CHECK TABLE t1 EXTENDED;
2422 Table Op Msg_type Msg_text
2423 test.t1 check note The storage engine for the table doesn't support check
2424 CHECKSUM TABLE t1 EXTENDED;
2425 Table Checksum
2426 test.t1 <some_value>
2427 OPTIMIZE TABLE t1;
2428 Table Op Msg_type Msg_text
2429 test.t1 optimize note The storage engine for the table doesn't support optimize
2430 # check layout success: 1
2431 REPAIR TABLE t1 EXTENDED;
2432 Table Op Msg_type Msg_text
2433 test.t1 repair note The storage engine for the table doesn't support repair
2434 # check layout success: 1
2435 TRUNCATE t1;
2437 # check TRUNCATE success: 1
2438 # check layout success: 1
2439 # End usability test (inc/partition_check.inc)
2440 DROP TABLE t1;
2441 CREATE TABLE t1 (
2442 f_int1 INTEGER,
2443 f_int2 INTEGER,
2444 f_char1 CHAR(20),
2445 f_char2 CHAR(20),
2446 f_charbig VARCHAR(1000)
2447 )
2448 PARTITION BY RANGE(f_int1)
2449 SUBPARTITION BY HASH(f_int1)
2450 ( PARTITION part1 VALUES LESS THAN (10)
2451 (SUBPARTITION subpart11 STORAGE ENGINE = 'ndbcluster',
2452 SUBPARTITION subpart12 STORAGE ENGINE = 'ndbcluster'),
2453 PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = 'ndbcluster'
2454 (SUBPARTITION subpart21 ENGINE = 'ndbcluster',
2455 SUBPARTITION subpart22)
2456 );
2457 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2458 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2459 # Start usability test (inc/partition_check.inc)
2460 create_command
2461 SHOW CREATE TABLE t1;
2462 Table Create Table
2463 t1 CREATE TABLE `t1` (
2464 `f_int1` int(11) DEFAULT NULL,
2465 `f_int2` int(11) DEFAULT NULL,
2466 `f_char1` char(20) DEFAULT NULL,
2467 `f_char2` char(20) DEFAULT NULL,
2468 `f_charbig` varchar(1000) DEFAULT NULL
2469 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
2470 /*!50100 PARTITION BY RANGE (f_int1)
2471 SUBPARTITION BY HASH (f_int1)
2472 (PARTITION part1 VALUES LESS THAN (10)
2473 (SUBPARTITION subpart11 ENGINE = ndbcluster,
2474 SUBPARTITION subpart12 ENGINE = ndbcluster),
2475 PARTITION part2 VALUES LESS THAN (2147483646)
2476 (SUBPARTITION subpart21 ENGINE = ndbcluster,
2477 SUBPARTITION subpart22 ENGINE = ndbcluster)) */
2479 # check prerequisites-1 success: 1
2480 # check COUNT(*) success: 1
2481 # check MIN/MAX(f_int1) success: 1
2482 # check MIN/MAX(f_int2) success: 1
2483 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2484 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2485 CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2486 WHERE f_int1 IN (2,3);
2487 # check prerequisites-3 success: 1
2488 DELETE FROM t1 WHERE f_charbig = 'delete me';
2489 # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2490 # check read via f_int1 success: 1
2491 # check read via f_int2 success: 1
2493 # check multiple-1 success: 1
2494 DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2496 # check multiple-2 success: 1
2497 INSERT INTO t1 SELECT * FROM t0_template
2498 WHERE MOD(f_int1,3) = 0;
2500 # check multiple-3 success: 1
2501 UPDATE t1 SET f_int1 = f_int1 + @max_row
2502 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2503 AND @max_row_div2 + @max_row_div4;
2505 # check multiple-4 success: 1
2506 DELETE FROM t1
2507 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2508 AND @max_row_div2 + @max_row_div4 + @max_row;
2510 # check multiple-5 success: 1
2511 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2512 INSERT INTO t1
2513 SET f_int1 = @cur_value , f_int2 = @cur_value,
2514 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2515 f_charbig = '#SINGLE#';
2517 # check single-1 success: 1
2518 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2519 INSERT INTO t1
2520 SET f_int1 = @cur_value , f_int2 = @cur_value,
2521 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2522 f_charbig = '#SINGLE#';
2524 # check single-2 success: 1
2525 SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2526 SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2527 UPDATE t1 SET f_int1 = @cur_value2
2528 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2530 # check single-3 success: 1
2531 SET @cur_value1= -1;
2532 SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2533 UPDATE t1 SET f_int1 = @cur_value1
2534 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2536 # check single-4 success: 1
2537 SELECT MAX(f_int1) INTO @cur_value FROM t1;
2538 DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
2540 # check single-5 success: 1
2541 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
2543 # check single-6 success: 1
2544 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
2545 ERROR HY000: Table has no partition for value 2147483647
2546 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
2547 INSERT t1 SET f_int1 = 0 , f_int2 = 0,
2548 f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
2549 f_charbig = '#NULL#';
2550 INSERT INTO t1
2551 SET f_int1 = NULL , f_int2 = -@max_row,
2552 f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
2553 f_charbig = '#NULL#';
2554 # check null success: 1
2556 # check null-1 success: 1
2557 UPDATE t1 SET f_int1 = -@max_row
2558 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2559 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2561 # check null-2 success: 1
2562 UPDATE t1 SET f_int1 = NULL
2563 WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2564 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2566 # check null-3 success: 1
2567 DELETE FROM t1
2568 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2569 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2571 # check null-4 success: 1
2572 DELETE FROM t1
2573 WHERE f_int1 = 0 AND f_int2 = 0
2574 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
2575 AND f_charbig = '#NULL#';
2576 SET AUTOCOMMIT= 0;
2577 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2578 SELECT f_int1, f_int1, '', '', 'was inserted'
2579 FROM t0_template source_tab
2580 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2582 # check transactions-1 success: 1
2583 COMMIT WORK;
2585 # check transactions-2 success: 1
2586 ROLLBACK WORK;
2588 # check transactions-3 success: 1
2589 DELETE FROM t1 WHERE f_charbig = 'was inserted';
2590 COMMIT WORK;
2591 ROLLBACK WORK;
2593 # check transactions-4 success: 1
2594 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2595 SELECT f_int1, f_int1, '', '', 'was inserted'
2596 FROM t0_template source_tab
2597 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2599 # check transactions-5 success: 1
2600 ROLLBACK WORK;
2602 # check transactions-6 success: 1
2603 # INFO: Storage engine used for t1 seems to be transactional.
2604 COMMIT;
2606 # check transactions-7 success: 1
2607 DELETE FROM t1 WHERE f_charbig = 'was inserted';
2608 COMMIT WORK;
2609 SET @@session.sql_mode = 'traditional';
2610 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
2611 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2612 SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
2613 '', '', 'was inserted' FROM t0_template
2614 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2615 ERROR 22012: Division by 0
2616 COMMIT;
2618 # check transactions-8 success: 1
2619 # INFO: Storage engine used for t1 seems to be unable to revert
2620 # changes made by the failing statement.
2621 SET @@session.sql_mode = '';
2622 SET AUTOCOMMIT= 1;
2623 DELETE FROM t1 WHERE f_charbig = 'was inserted';
2624 COMMIT WORK;
2625 UPDATE t1 SET f_charbig = REPEAT('b', 1000);
2627 # check special-1 success: 1
2628 UPDATE t1 SET f_charbig = '';
2630 # check special-2 success: 1
2631 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
2632 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2633 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
2634 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2635 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2636 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2637 'just inserted' FROM t0_template
2638 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2639 CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
2640 BEGIN
2641 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2642 f_charbig = 'updated by trigger'
2643 WHERE f_int1 = new.f_int1;
2644 END|
2645 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2646 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2647 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2649 # check trigger-1 success: 1
2650 DROP TRIGGER trg_1;
2651 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2652 f_int2 = CAST(f_char1 AS SIGNED INT),
2653 f_charbig = 'just inserted'
2654 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2655 DELETE FROM t0_aux
2656 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2657 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2658 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2659 'just inserted' FROM t0_template
2660 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2661 CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
2662 BEGIN
2663 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2664 f_charbig = 'updated by trigger'
2665 WHERE f_int1 = new.f_int1;
2666 END|
2667 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2668 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2669 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2671 # check trigger-2 success: 1
2672 DROP TRIGGER trg_1;
2673 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2674 f_int2 = CAST(f_char1 AS SIGNED INT),
2675 f_charbig = 'just inserted'
2676 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2677 DELETE FROM t0_aux
2678 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2679 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2680 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2681 'just inserted' FROM t0_template
2682 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2683 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2684 BEGIN
2685 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2686 f_charbig = 'updated by trigger'
2687 WHERE f_int1 = new.f_int1;
2688 END|
2689 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2690 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2692 # check trigger-3 success: 1
2693 DROP TRIGGER trg_1;
2694 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2695 f_int2 = CAST(f_char1 AS SIGNED INT),
2696 f_charbig = 'just inserted'
2697 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2698 DELETE FROM t0_aux
2699 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2700 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2701 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2702 'just inserted' FROM t0_template
2703 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2704 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2705 BEGIN
2706 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2707 f_charbig = 'updated by trigger'
2708 WHERE f_int1 = - old.f_int1;
2709 END|
2710 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2711 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2713 # check trigger-4 success: 1
2714 DROP TRIGGER trg_1;
2715 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2716 f_int2 = CAST(f_char1 AS SIGNED INT),
2717 f_charbig = 'just inserted'
2718 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2719 DELETE FROM t0_aux
2720 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2721 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2722 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2723 'just inserted' FROM t0_template
2724 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2725 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2726 BEGIN
2727 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2728 f_charbig = 'updated by trigger'
2729 WHERE f_int1 = new.f_int1;
2730 END|
2731 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2732 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2734 # check trigger-5 success: 1
2735 DROP TRIGGER trg_1;
2736 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2737 f_int2 = CAST(f_char1 AS SIGNED INT),
2738 f_charbig = 'just inserted'
2739 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2740 DELETE FROM t0_aux
2741 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2742 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2743 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2744 'just inserted' FROM t0_template
2745 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2746 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2747 BEGIN
2748 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2749 f_charbig = 'updated by trigger'
2750 WHERE f_int1 = - old.f_int1;
2751 END|
2752 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2753 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2755 # check trigger-6 success: 1
2756 DROP TRIGGER trg_1;
2757 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2758 f_int2 = CAST(f_char1 AS SIGNED INT),
2759 f_charbig = 'just inserted'
2760 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2761 DELETE FROM t0_aux
2762 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2763 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2764 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2765 'just inserted' FROM t0_template
2766 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2767 CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
2768 BEGIN
2769 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2770 f_charbig = 'updated by trigger'
2771 WHERE f_int1 = - old.f_int1;
2772 END|
2773 DELETE FROM t0_aux
2774 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2776 # check trigger-7 success: 1
2777 DROP TRIGGER trg_1;
2778 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2779 f_int2 = CAST(f_char1 AS SIGNED INT),
2780 f_charbig = 'just inserted'
2781 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2782 DELETE FROM t0_aux
2783 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2784 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2785 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2786 'just inserted' FROM t0_template
2787 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2788 CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
2789 BEGIN
2790 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2791 f_charbig = 'updated by trigger'
2792 WHERE f_int1 = - old.f_int1;
2793 END|
2794 DELETE FROM t0_aux
2795 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2797 # check trigger-8 success: 1
2798 DROP TRIGGER trg_1;
2799 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2800 f_int2 = CAST(f_char1 AS SIGNED INT),
2801 f_charbig = 'just inserted'
2802 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2803 DELETE FROM t0_aux
2804 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2805 DELETE FROM t1
2806 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2807 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2808 BEGIN
2809 SET new.f_int1 = old.f_int1 + @max_row,
2810 new.f_int2 = old.f_int2 - @max_row,
2811 new.f_charbig = '####updated per update trigger####';
2812 END|
2813 UPDATE t1
2814 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2815 f_charbig = '####updated per update statement itself####';
2817 # check trigger-9 success: 1
2818 DROP TRIGGER trg_2;
2819 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2820 f_int2 = CAST(f_char1 AS SIGNED INT),
2821 f_charbig = CONCAT('===',f_char1,'===');
2822 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2823 BEGIN
2824 SET new.f_int1 = new.f_int1 + @max_row,
2825 new.f_int2 = new.f_int2 - @max_row,
2826 new.f_charbig = '####updated per update trigger####';
2827 END|
2828 UPDATE t1
2829 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2830 f_charbig = '####updated per update statement itself####';
2832 # check trigger-10 success: 1
2833 DROP TRIGGER trg_2;
2834 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2835 f_int2 = CAST(f_char1 AS SIGNED INT),
2836 f_charbig = CONCAT('===',f_char1,'===');
2837 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2838 BEGIN
2839 SET new.f_int1 = @my_max1 + @counter,
2840 new.f_int2 = @my_min2 - @counter,
2841 new.f_charbig = '####updated per insert trigger####';
2842 SET @counter = @counter + 1;
2843 END|
2844 SET @counter = 1;
2845 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2846 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2847 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2848 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2849 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2850 ORDER BY f_int1;
2851 DROP TRIGGER trg_3;
2853 # check trigger-11 success: 1
2854 DELETE FROM t1
2855 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2856 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2857 AND f_charbig = '####updated per insert trigger####';
2858 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2859 BEGIN
2860 SET new.f_int1 = @my_max1 + @counter,
2861 new.f_int2 = @my_min2 - @counter,
2862 new.f_charbig = '####updated per insert trigger####';
2863 SET @counter = @counter + 1;
2864 END|
2865 SET @counter = 1;
2866 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2867 INSERT INTO t1 (f_char1, f_char2, f_charbig)
2868 SELECT CAST(f_int1 AS CHAR),
2869 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2870 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2871 ORDER BY f_int1;
2872 DROP TRIGGER trg_3;
2874 # check trigger-12 success: 1
2875 DELETE FROM t1
2876 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2877 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2878 AND f_charbig = '####updated per insert trigger####';
2879 ANALYZE TABLE t1;
2880 Table Op Msg_type Msg_text
2881 test.t1 analyze note The storage engine for the table doesn't support analyze
2882 CHECK TABLE t1 EXTENDED;
2883 Table Op Msg_type Msg_text
2884 test.t1 check note The storage engine for the table doesn't support check
2885 CHECKSUM TABLE t1 EXTENDED;
2886 Table Checksum
2887 test.t1 <some_value>
2888 OPTIMIZE TABLE t1;
2889 Table Op Msg_type Msg_text
2890 test.t1 optimize note The storage engine for the table doesn't support optimize
2891 # check layout success: 1
2892 REPAIR TABLE t1 EXTENDED;
2893 Table Op Msg_type Msg_text
2894 test.t1 repair note The storage engine for the table doesn't support repair
2895 # check layout success: 1
2896 TRUNCATE t1;
2898 # check TRUNCATE success: 1
2899 # check layout success: 1
2900 # End usability test (inc/partition_check.inc)
2901 DROP TABLE t1;
2902 #------------------------------------------------------------------------
2903 # 5 Precedence of storage engine assignments (if there is any)
2904 #------------------------------------------------------------------------
2905 # 5.1 Storage engine assignment after column list + after partition
2906 # or subpartition name
2907 CREATE TABLE t1 (
2908 f_int1 INTEGER,
2909 f_int2 INTEGER,
2910 f_char1 CHAR(20),
2911 f_char2 CHAR(20),
2912 f_charbig VARCHAR(1000)
2913 ) ENGINE = 'ndbcluster'
2914 PARTITION BY HASH(f_int1)
2915 ( PARTITION part1 STORAGE ENGINE = 'ndbcluster',
2916 PARTITION part2 STORAGE ENGINE = 'ndbcluster'
2917 );
2918 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2919 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2920 # Start usability test (inc/partition_check.inc)
2921 create_command
2922 SHOW CREATE TABLE t1;
2923 Table Create Table
2924 t1 CREATE TABLE `t1` (
2925 `f_int1` int(11) DEFAULT NULL,
2926 `f_int2` int(11) DEFAULT NULL,
2927 `f_char1` char(20) DEFAULT NULL,
2928 `f_char2` char(20) DEFAULT NULL,
2929 `f_charbig` varchar(1000) DEFAULT NULL
2930 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
2931 /*!50100 PARTITION BY HASH (f_int1)
2932 (PARTITION part1 ENGINE = ndbcluster,
2933 PARTITION part2 ENGINE = ndbcluster) */
2935 # check prerequisites-1 success: 1
2936 # check COUNT(*) success: 1
2937 # check MIN/MAX(f_int1) success: 1
2938 # check MIN/MAX(f_int2) success: 1
2939 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2940 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2941 CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2942 WHERE f_int1 IN (2,3);
2943 # check prerequisites-3 success: 1
2944 DELETE FROM t1 WHERE f_charbig = 'delete me';
2945 # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2946 # check read via f_int1 success: 1
2947 # check read via f_int2 success: 1
2949 # check multiple-1 success: 1
2950 DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2952 # check multiple-2 success: 1
2953 INSERT INTO t1 SELECT * FROM t0_template
2954 WHERE MOD(f_int1,3) = 0;
2956 # check multiple-3 success: 1
2957 UPDATE t1 SET f_int1 = f_int1 + @max_row
2958 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2959 AND @max_row_div2 + @max_row_div4;
2961 # check multiple-4 success: 1
2962 DELETE FROM t1
2963 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2964 AND @max_row_div2 + @max_row_div4 + @max_row;
2966 # check multiple-5 success: 1
2967 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2968 INSERT INTO t1
2969 SET f_int1 = @cur_value , f_int2 = @cur_value,
2970 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2971 f_charbig = '#SINGLE#';
2973 # check single-1 success: 1
2974 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2975 INSERT INTO t1
2976 SET f_int1 = @cur_value , f_int2 = @cur_value,
2977 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2978 f_charbig = '#SINGLE#';
2980 # check single-2 success: 1
2981 SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2982 SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2983 UPDATE t1 SET f_int1 = @cur_value2
2984 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2986 # check single-3 success: 1
2987 SET @cur_value1= -1;
2988 SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2989 UPDATE t1 SET f_int1 = @cur_value1
2990 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2992 # check single-4 success: 1
2993 SELECT MAX(f_int1) INTO @cur_value FROM t1;
2994 DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
2996 # check single-5 success: 1
2997 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
2999 # check single-6 success: 1
3000 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3002 # check single-7 success: 1
3003 DELETE FROM t1 WHERE f_charbig = '#2147483647##';
3004 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3005 INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3006 f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3007 f_charbig = '#NULL#';
3008 INSERT INTO t1
3009 SET f_int1 = NULL , f_int2 = -@max_row,
3010 f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3011 f_charbig = '#NULL#';
3012 # check null success: 1
3014 # check null-1 success: 1
3015 UPDATE t1 SET f_int1 = -@max_row
3016 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3017 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3019 # check null-2 success: 1
3020 UPDATE t1 SET f_int1 = NULL
3021 WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3022 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3024 # check null-3 success: 1
3025 DELETE FROM t1
3026 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3027 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3029 # check null-4 success: 1
3030 DELETE FROM t1
3031 WHERE f_int1 = 0 AND f_int2 = 0
3032 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
3033 AND f_charbig = '#NULL#';
3034 SET AUTOCOMMIT= 0;
3035 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3036 SELECT f_int1, f_int1, '', '', 'was inserted'
3037 FROM t0_template source_tab
3038 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3040 # check transactions-1 success: 1
3041 COMMIT WORK;
3043 # check transactions-2 success: 1
3044 ROLLBACK WORK;
3046 # check transactions-3 success: 1
3047 DELETE FROM t1 WHERE f_charbig = 'was inserted';
3048 COMMIT WORK;
3049 ROLLBACK WORK;
3051 # check transactions-4 success: 1
3052 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3053 SELECT f_int1, f_int1, '', '', 'was inserted'
3054 FROM t0_template source_tab
3055 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3057 # check transactions-5 success: 1
3058 ROLLBACK WORK;
3060 # check transactions-6 success: 1
3061 # INFO: Storage engine used for t1 seems to be transactional.
3062 COMMIT;
3064 # check transactions-7 success: 1
3065 DELETE FROM t1 WHERE f_charbig = 'was inserted';
3066 COMMIT WORK;
3067 SET @@session.sql_mode = 'traditional';
3068 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
3069 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3070 SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
3071 '', '', 'was inserted' FROM t0_template
3072 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3073 ERROR 22012: Division by 0
3074 COMMIT;
3076 # check transactions-8 success: 1
3077 # INFO: Storage engine used for t1 seems to be unable to revert
3078 # changes made by the failing statement.
3079 SET @@session.sql_mode = '';
3080 SET AUTOCOMMIT= 1;
3081 DELETE FROM t1 WHERE f_charbig = 'was inserted';
3082 COMMIT WORK;
3083 UPDATE t1 SET f_charbig = REPEAT('b', 1000);
3085 # check special-1 success: 1
3086 UPDATE t1 SET f_charbig = '';
3088 # check special-2 success: 1
3089 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
3090 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3091 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
3092 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3093 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3094 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3095 'just inserted' FROM t0_template
3096 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3097 CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
3098 BEGIN
3099 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3100 f_charbig = 'updated by trigger'
3101 WHERE f_int1 = new.f_int1;
3102 END|
3103 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3104 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3105 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3107 # check trigger-1 success: 1
3108 DROP TRIGGER trg_1;
3109 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3110 f_int2 = CAST(f_char1 AS SIGNED INT),
3111 f_charbig = 'just inserted'
3112 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3113 DELETE FROM t0_aux
3114 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3115 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3116 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3117 'just inserted' FROM t0_template
3118 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3119 CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
3120 BEGIN
3121 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3122 f_charbig = 'updated by trigger'
3123 WHERE f_int1 = new.f_int1;
3124 END|
3125 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3126 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3127 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3129 # check trigger-2 success: 1
3130 DROP TRIGGER trg_1;
3131 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3132 f_int2 = CAST(f_char1 AS SIGNED INT),
3133 f_charbig = 'just inserted'
3134 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3135 DELETE FROM t0_aux
3136 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3137 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3138 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3139 'just inserted' FROM t0_template
3140 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3141 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3142 BEGIN
3143 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3144 f_charbig = 'updated by trigger'
3145 WHERE f_int1 = new.f_int1;
3146 END|
3147 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3148 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3150 # check trigger-3 success: 1
3151 DROP TRIGGER trg_1;
3152 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3153 f_int2 = CAST(f_char1 AS SIGNED INT),
3154 f_charbig = 'just inserted'
3155 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3156 DELETE FROM t0_aux
3157 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3158 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3159 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3160 'just inserted' FROM t0_template
3161 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3162 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3163 BEGIN
3164 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3165 f_charbig = 'updated by trigger'
3166 WHERE f_int1 = - old.f_int1;
3167 END|
3168 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3169 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3171 # check trigger-4 success: 1
3172 DROP TRIGGER trg_1;
3173 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3174 f_int2 = CAST(f_char1 AS SIGNED INT),
3175 f_charbig = 'just inserted'
3176 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3177 DELETE FROM t0_aux
3178 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3179 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3180 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3181 'just inserted' FROM t0_template
3182 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3183 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3184 BEGIN
3185 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3186 f_charbig = 'updated by trigger'
3187 WHERE f_int1 = new.f_int1;
3188 END|
3189 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3190 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3192 # check trigger-5 success: 1
3193 DROP TRIGGER trg_1;
3194 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3195 f_int2 = CAST(f_char1 AS SIGNED INT),
3196 f_charbig = 'just inserted'
3197 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3198 DELETE FROM t0_aux
3199 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3200 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3201 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3202 'just inserted' FROM t0_template
3203 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3204 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3205 BEGIN
3206 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3207 f_charbig = 'updated by trigger'
3208 WHERE f_int1 = - old.f_int1;
3209 END|
3210 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3211 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3213 # check trigger-6 success: 1
3214 DROP TRIGGER trg_1;
3215 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3216 f_int2 = CAST(f_char1 AS SIGNED INT),
3217 f_charbig = 'just inserted'
3218 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3219 DELETE FROM t0_aux
3220 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3221 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3222 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3223 'just inserted' FROM t0_template
3224 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3225 CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
3226 BEGIN
3227 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3228 f_charbig = 'updated by trigger'
3229 WHERE f_int1 = - old.f_int1;
3230 END|
3231 DELETE FROM t0_aux
3232 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3234 # check trigger-7 success: 1
3235 DROP TRIGGER trg_1;
3236 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3237 f_int2 = CAST(f_char1 AS SIGNED INT),
3238 f_charbig = 'just inserted'
3239 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3240 DELETE FROM t0_aux
3241 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3242 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3243 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3244 'just inserted' FROM t0_template
3245 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3246 CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
3247 BEGIN
3248 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3249 f_charbig = 'updated by trigger'
3250 WHERE f_int1 = - old.f_int1;
3251 END|
3252 DELETE FROM t0_aux
3253 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3255 # check trigger-8 success: 1
3256 DROP TRIGGER trg_1;
3257 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3258 f_int2 = CAST(f_char1 AS SIGNED INT),
3259 f_charbig = 'just inserted'
3260 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3261 DELETE FROM t0_aux
3262 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3263 DELETE FROM t1
3264 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3265 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3266 BEGIN
3267 SET new.f_int1 = old.f_int1 + @max_row,
3268 new.f_int2 = old.f_int2 - @max_row,
3269 new.f_charbig = '####updated per update trigger####';
3270 END|
3271 UPDATE t1
3272 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3273 f_charbig = '####updated per update statement itself####';
3275 # check trigger-9 success: 1
3276 DROP TRIGGER trg_2;
3277 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3278 f_int2 = CAST(f_char1 AS SIGNED INT),
3279 f_charbig = CONCAT('===',f_char1,'===');
3280 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3281 BEGIN
3282 SET new.f_int1 = new.f_int1 + @max_row,
3283 new.f_int2 = new.f_int2 - @max_row,
3284 new.f_charbig = '####updated per update trigger####';
3285 END|
3286 UPDATE t1
3287 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3288 f_charbig = '####updated per update statement itself####';
3290 # check trigger-10 success: 1
3291 DROP TRIGGER trg_2;
3292 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3293 f_int2 = CAST(f_char1 AS SIGNED INT),
3294 f_charbig = CONCAT('===',f_char1,'===');
3295 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3296 BEGIN
3297 SET new.f_int1 = @my_max1 + @counter,
3298 new.f_int2 = @my_min2 - @counter,
3299 new.f_charbig = '####updated per insert trigger####';
3300 SET @counter = @counter + 1;
3301 END|
3302 SET @counter = 1;
3303 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3304 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3305 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3306 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3307 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3308 ORDER BY f_int1;
3309 DROP TRIGGER trg_3;
3311 # check trigger-11 success: 1
3312 DELETE FROM t1
3313 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3314 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3315 AND f_charbig = '####updated per insert trigger####';
3316 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3317 BEGIN
3318 SET new.f_int1 = @my_max1 + @counter,
3319 new.f_int2 = @my_min2 - @counter,
3320 new.f_charbig = '####updated per insert trigger####';
3321 SET @counter = @counter + 1;
3322 END|
3323 SET @counter = 1;
3324 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3325 INSERT INTO t1 (f_char1, f_char2, f_charbig)
3326 SELECT CAST(f_int1 AS CHAR),
3327 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3328 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3329 ORDER BY f_int1;
3330 DROP TRIGGER trg_3;
3332 # check trigger-12 success: 1
3333 DELETE FROM t1
3334 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3335 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3336 AND f_charbig = '####updated per insert trigger####';
3337 ANALYZE TABLE t1;
3338 Table Op Msg_type Msg_text
3339 test.t1 analyze note The storage engine for the table doesn't support analyze
3340 CHECK TABLE t1 EXTENDED;
3341 Table Op Msg_type Msg_text
3342 test.t1 check note The storage engine for the table doesn't support check
3343 CHECKSUM TABLE t1 EXTENDED;
3344 Table Checksum
3345 test.t1 <some_value>
3346 OPTIMIZE TABLE t1;
3347 Table Op Msg_type Msg_text
3348 test.t1 optimize note The storage engine for the table doesn't support optimize
3349 # check layout success: 1
3350 REPAIR TABLE t1 EXTENDED;
3351 Table Op Msg_type Msg_text
3352 test.t1 repair note The storage engine for the table doesn't support repair
3353 # check layout success: 1
3354 TRUNCATE t1;
3356 # check TRUNCATE success: 1
3357 # check layout success: 1
3358 # End usability test (inc/partition_check.inc)
3359 DROP TABLE t1;
3360 CREATE TABLE t1 (
3361 f_int1 INTEGER,
3362 f_int2 INTEGER,
3363 f_char1 CHAR(20),
3364 f_char2 CHAR(20),
3365 f_charbig VARCHAR(1000)
3366 )
3367 PARTITION BY RANGE(f_int1)
3368 SUBPARTITION BY HASH(f_int1)
3369 ( PARTITION part1 VALUES LESS THAN (10)
3370 (SUBPARTITION subpart11 STORAGE ENGINE = 'ndbcluster',
3371 SUBPARTITION subpart12 STORAGE ENGINE = 'ndbcluster'),
3372 PARTITION part2 VALUES LESS THAN (2147483646)
3373 (SUBPARTITION subpart21 STORAGE ENGINE = 'ndbcluster',
3374 SUBPARTITION subpart22 STORAGE ENGINE = 'ndbcluster')
3375 );
3376 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3377 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
3378 # Start usability test (inc/partition_check.inc)
3379 create_command
3380 SHOW CREATE TABLE t1;
3381 Table Create Table
3382 t1 CREATE TABLE `t1` (
3383 `f_int1` int(11) DEFAULT NULL,
3384 `f_int2` int(11) DEFAULT NULL,
3385 `f_char1` char(20) DEFAULT NULL,
3386 `f_char2` char(20) DEFAULT NULL,
3387 `f_charbig` varchar(1000) DEFAULT NULL
3388 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
3389 /*!50100 PARTITION BY RANGE (f_int1)
3390 SUBPARTITION BY HASH (f_int1)
3391 (PARTITION part1 VALUES LESS THAN (10)
3392 (SUBPARTITION subpart11 ENGINE = ndbcluster,
3393 SUBPARTITION subpart12 ENGINE = ndbcluster),
3394 PARTITION part2 VALUES LESS THAN (2147483646)
3395 (SUBPARTITION subpart21 ENGINE = ndbcluster,
3396 SUBPARTITION subpart22 ENGINE = ndbcluster)) */
3398 # check prerequisites-1 success: 1
3399 # check COUNT(*) success: 1
3400 # check MIN/MAX(f_int1) success: 1
3401 # check MIN/MAX(f_int2) success: 1
3402 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3403 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3404 CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
3405 WHERE f_int1 IN (2,3);
3406 # check prerequisites-3 success: 1
3407 DELETE FROM t1 WHERE f_charbig = 'delete me';
3408 # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
3409 # check read via f_int1 success: 1
3410 # check read via f_int2 success: 1
3412 # check multiple-1 success: 1
3413 DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
3415 # check multiple-2 success: 1
3416 INSERT INTO t1 SELECT * FROM t0_template
3417 WHERE MOD(f_int1,3) = 0;
3419 # check multiple-3 success: 1
3420 UPDATE t1 SET f_int1 = f_int1 + @max_row
3421 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
3422 AND @max_row_div2 + @max_row_div4;
3424 # check multiple-4 success: 1
3425 DELETE FROM t1
3426 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
3427 AND @max_row_div2 + @max_row_div4 + @max_row;
3429 # check multiple-5 success: 1
3430 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
3431 INSERT INTO t1
3432 SET f_int1 = @cur_value , f_int2 = @cur_value,
3433 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3434 f_charbig = '#SINGLE#';
3436 # check single-1 success: 1
3437 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
3438 INSERT INTO t1
3439 SET f_int1 = @cur_value , f_int2 = @cur_value,
3440 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3441 f_charbig = '#SINGLE#';
3443 # check single-2 success: 1
3444 SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
3445 SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
3446 UPDATE t1 SET f_int1 = @cur_value2
3447 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
3449 # check single-3 success: 1
3450 SET @cur_value1= -1;
3451 SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
3452 UPDATE t1 SET f_int1 = @cur_value1
3453 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
3455 # check single-4 success: 1
3456 SELECT MAX(f_int1) INTO @cur_value FROM t1;
3457 DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
3459 # check single-5 success: 1
3460 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
3462 # check single-6 success: 1
3463 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3464 ERROR HY000: Table has no partition for value 2147483647
3465 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3466 INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3467 f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3468 f_charbig = '#NULL#';
3469 INSERT INTO t1
3470 SET f_int1 = NULL , f_int2 = -@max_row,
3471 f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3472 f_charbig = '#NULL#';
3473 # check null success: 1
3475 # check null-1 success: 1
3476 UPDATE t1 SET f_int1 = -@max_row
3477 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3478 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3480 # check null-2 success: 1
3481 UPDATE t1 SET f_int1 = NULL
3482 WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3483 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3485 # check null-3 success: 1
3486 DELETE FROM t1
3487 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3488 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3490 # check null-4 success: 1
3491 DELETE FROM t1
3492 WHERE f_int1 = 0 AND f_int2 = 0
3493 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
3494 AND f_charbig = '#NULL#';
3495 SET AUTOCOMMIT= 0;
3496 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3497 SELECT f_int1, f_int1, '', '', 'was inserted'
3498 FROM t0_template source_tab
3499 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3501 # check transactions-1 success: 1
3502 COMMIT WORK;
3504 # check transactions-2 success: 1
3505 ROLLBACK WORK;
3507 # check transactions-3 success: 1
3508 DELETE FROM t1 WHERE f_charbig = 'was inserted';
3509 COMMIT WORK;
3510 ROLLBACK WORK;
3512 # check transactions-4 success: 1
3513 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3514 SELECT f_int1, f_int1, '', '', 'was inserted'
3515 FROM t0_template source_tab
3516 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3518 # check transactions-5 success: 1
3519 ROLLBACK WORK;
3521 # check transactions-6 success: 1
3522 # INFO: Storage engine used for t1 seems to be transactional.
3523 COMMIT;
3525 # check transactions-7 success: 1
3526 DELETE FROM t1 WHERE f_charbig = 'was inserted';
3527 COMMIT WORK;
3528 SET @@session.sql_mode = 'traditional';
3529 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
3530 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3531 SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
3532 '', '', 'was inserted' FROM t0_template
3533 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3534 ERROR 22012: Division by 0
3535 COMMIT;
3537 # check transactions-8 success: 1
3538 # INFO: Storage engine used for t1 seems to be unable to revert
3539 # changes made by the failing statement.
3540 SET @@session.sql_mode = '';
3541 SET AUTOCOMMIT= 1;
3542 DELETE FROM t1 WHERE f_charbig = 'was inserted';
3543 COMMIT WORK;
3544 UPDATE t1 SET f_charbig = REPEAT('b', 1000);
3546 # check special-1 success: 1
3547 UPDATE t1 SET f_charbig = '';
3549 # check special-2 success: 1
3550 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
3551 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3552 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
3553 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3554 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3555 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3556 'just inserted' FROM t0_template
3557 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3558 CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
3559 BEGIN
3560 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3561 f_charbig = 'updated by trigger'
3562 WHERE f_int1 = new.f_int1;
3563 END|
3564 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3565 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3566 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3568 # check trigger-1 success: 1
3569 DROP TRIGGER trg_1;
3570 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3571 f_int2 = CAST(f_char1 AS SIGNED INT),
3572 f_charbig = 'just inserted'
3573 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3574 DELETE FROM t0_aux
3575 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3576 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3577 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3578 'just inserted' FROM t0_template
3579 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3580 CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
3581 BEGIN
3582 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3583 f_charbig = 'updated by trigger'
3584 WHERE f_int1 = new.f_int1;
3585 END|
3586 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3587 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3588 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3590 # check trigger-2 success: 1
3591 DROP TRIGGER trg_1;
3592 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3593 f_int2 = CAST(f_char1 AS SIGNED INT),
3594 f_charbig = 'just inserted'
3595 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3596 DELETE FROM t0_aux
3597 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3598 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3599 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3600 'just inserted' FROM t0_template
3601 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3602 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3603 BEGIN
3604 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3605 f_charbig = 'updated by trigger'
3606 WHERE f_int1 = new.f_int1;
3607 END|
3608 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3609 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3611 # check trigger-3 success: 1
3612 DROP TRIGGER trg_1;
3613 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3614 f_int2 = CAST(f_char1 AS SIGNED INT),
3615 f_charbig = 'just inserted'
3616 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3617 DELETE FROM t0_aux
3618 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3619 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3620 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3621 'just inserted' FROM t0_template
3622 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3623 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3624 BEGIN
3625 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3626 f_charbig = 'updated by trigger'
3627 WHERE f_int1 = - old.f_int1;
3628 END|
3629 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3630 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3632 # check trigger-4 success: 1
3633 DROP TRIGGER trg_1;
3634 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3635 f_int2 = CAST(f_char1 AS SIGNED INT),
3636 f_charbig = 'just inserted'
3637 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3638 DELETE FROM t0_aux
3639 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3640 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3641 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3642 'just inserted' FROM t0_template
3643 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3644 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3645 BEGIN
3646 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3647 f_charbig = 'updated by trigger'
3648 WHERE f_int1 = new.f_int1;
3649 END|
3650 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3651 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3653 # check trigger-5 success: 1
3654 DROP TRIGGER trg_1;
3655 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3656 f_int2 = CAST(f_char1 AS SIGNED INT),
3657 f_charbig = 'just inserted'
3658 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3659 DELETE FROM t0_aux
3660 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3661 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3662 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3663 'just inserted' FROM t0_template
3664 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3665 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3666 BEGIN
3667 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3668 f_charbig = 'updated by trigger'
3669 WHERE f_int1 = - old.f_int1;
3670 END|
3671 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3672 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3674 # check trigger-6 success: 1
3675 DROP TRIGGER trg_1;
3676 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3677 f_int2 = CAST(f_char1 AS SIGNED INT),
3678 f_charbig = 'just inserted'
3679 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3680 DELETE FROM t0_aux
3681 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3682 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3683 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3684 'just inserted' FROM t0_template
3685 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3686 CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
3687 BEGIN
3688 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3689 f_charbig = 'updated by trigger'
3690 WHERE f_int1 = - old.f_int1;
3691 END|
3692 DELETE FROM t0_aux
3693 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3695 # check trigger-7 success: 1
3696 DROP TRIGGER trg_1;
3697 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3698 f_int2 = CAST(f_char1 AS SIGNED INT),
3699 f_charbig = 'just inserted'
3700 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3701 DELETE FROM t0_aux
3702 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3703 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3704 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3705 'just inserted' FROM t0_template
3706 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3707 CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
3708 BEGIN
3709 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3710 f_charbig = 'updated by trigger'
3711 WHERE f_int1 = - old.f_int1;
3712 END|
3713 DELETE FROM t0_aux
3714 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3716 # check trigger-8 success: 1
3717 DROP TRIGGER trg_1;
3718 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3719 f_int2 = CAST(f_char1 AS SIGNED INT),
3720 f_charbig = 'just inserted'
3721 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3722 DELETE FROM t0_aux
3723 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3724 DELETE FROM t1
3725 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3726 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3727 BEGIN
3728 SET new.f_int1 = old.f_int1 + @max_row,
3729 new.f_int2 = old.f_int2 - @max_row,
3730 new.f_charbig = '####updated per update trigger####';
3731 END|
3732 UPDATE t1
3733 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3734 f_charbig = '####updated per update statement itself####';
3736 # check trigger-9 success: 1
3737 DROP TRIGGER trg_2;
3738 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3739 f_int2 = CAST(f_char1 AS SIGNED INT),
3740 f_charbig = CONCAT('===',f_char1,'===');
3741 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3742 BEGIN
3743 SET new.f_int1 = new.f_int1 + @max_row,
3744 new.f_int2 = new.f_int2 - @max_row,
3745 new.f_charbig = '####updated per update trigger####';
3746 END|
3747 UPDATE t1
3748 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3749 f_charbig = '####updated per update statement itself####';
3751 # check trigger-10 success: 1
3752 DROP TRIGGER trg_2;
3753 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3754 f_int2 = CAST(f_char1 AS SIGNED INT),
3755 f_charbig = CONCAT('===',f_char1,'===');
3756 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3757 BEGIN
3758 SET new.f_int1 = @my_max1 + @counter,
3759 new.f_int2 = @my_min2 - @counter,
3760 new.f_charbig = '####updated per insert trigger####';
3761 SET @counter = @counter + 1;
3762 END|
3763 SET @counter = 1;
3764 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3765 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3766 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3767 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3768 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3769 ORDER BY f_int1;
3770 DROP TRIGGER trg_3;
3772 # check trigger-11 success: 1
3773 DELETE FROM t1
3774 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3775 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3776 AND f_charbig = '####updated per insert trigger####';
3777 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3778 BEGIN
3779 SET new.f_int1 = @my_max1 + @counter,
3780 new.f_int2 = @my_min2 - @counter,
3781 new.f_charbig = '####updated per insert trigger####';
3782 SET @counter = @counter + 1;
3783 END|
3784 SET @counter = 1;
3785 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3786 INSERT INTO t1 (f_char1, f_char2, f_charbig)
3787 SELECT CAST(f_int1 AS CHAR),
3788 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3789 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3790 ORDER BY f_int1;
3791 DROP TRIGGER trg_3;
3793 # check trigger-12 success: 1
3794 DELETE FROM t1
3795 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3796 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3797 AND f_charbig = '####updated per insert trigger####';
3798 ANALYZE TABLE t1;
3799 Table Op Msg_type Msg_text
3800 test.t1 analyze note The storage engine for the table doesn't support analyze
3801 CHECK TABLE t1 EXTENDED;
3802 Table Op Msg_type Msg_text
3803 test.t1 check note The storage engine for the table doesn't support check
3804 CHECKSUM TABLE t1 EXTENDED;
3805 Table Checksum
3806 test.t1 <some_value>
3807 OPTIMIZE TABLE t1;
3808 Table Op Msg_type Msg_text
3809 test.t1 optimize note The storage engine for the table doesn't support optimize
3810 # check layout success: 1
3811 REPAIR TABLE t1 EXTENDED;
3812 Table Op Msg_type Msg_text
3813 test.t1 repair note The storage engine for the table doesn't support repair
3814 # check layout success: 1
3815 TRUNCATE t1;
3817 # check TRUNCATE success: 1
3818 # check layout success: 1
3819 # End usability test (inc/partition_check.inc)
3820 DROP TABLE t1;
3821 # 6.2 Storage engine assignment after partition name + after
3822 # subpartition name
3823 # in partition part + in sub partition part
3824 CREATE TABLE t1 (
3825 f_int1 INTEGER,
3826 f_int2 INTEGER,
3827 f_char1 CHAR(20),
3828 f_char2 CHAR(20),
3829 f_charbig VARCHAR(1000)
3830 )
3831 PARTITION BY RANGE(f_int1)
3832 SUBPARTITION BY HASH(f_int1)
3833 ( PARTITION part1 VALUES LESS THAN (10) STORAGE ENGINE = 'ndbcluster'
3834 (SUBPARTITION subpart11 STORAGE ENGINE = 'ndbcluster',
3835 SUBPARTITION subpart12 STORAGE ENGINE = 'ndbcluster'),
3836 PARTITION part2 VALUES LESS THAN (2147483646)
3837 (SUBPARTITION subpart21 STORAGE ENGINE = 'ndbcluster',
3838 SUBPARTITION subpart22 STORAGE ENGINE = 'ndbcluster')
3839 );
3840 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3841 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
3842 # Start usability test (inc/partition_check.inc)
3843 create_command
3844 SHOW CREATE TABLE t1;
3845 Table Create Table
3846 t1 CREATE TABLE `t1` (
3847 `f_int1` int(11) DEFAULT NULL,
3848 `f_int2` int(11) DEFAULT NULL,
3849 `f_char1` char(20) DEFAULT NULL,
3850 `f_char2` char(20) DEFAULT NULL,
3851 `f_charbig` varchar(1000) DEFAULT NULL
3852 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
3853 /*!50100 PARTITION BY RANGE (f_int1)
3854 SUBPARTITION BY HASH (f_int1)
3855 (PARTITION part1 VALUES LESS THAN (10)
3856 (SUBPARTITION subpart11 ENGINE = ndbcluster,
3857 SUBPARTITION subpart12 ENGINE = ndbcluster),
3858 PARTITION part2 VALUES LESS THAN (2147483646)
3859 (SUBPARTITION subpart21 ENGINE = ndbcluster,
3860 SUBPARTITION subpart22 ENGINE = ndbcluster)) */
3862 # check prerequisites-1 success: 1
3863 # check COUNT(*) success: 1
3864 # check MIN/MAX(f_int1) success: 1
3865 # check MIN/MAX(f_int2) success: 1
3866 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3867 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3868 CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
3869 WHERE f_int1 IN (2,3);
3870 # check prerequisites-3 success: 1
3871 DELETE FROM t1 WHERE f_charbig = 'delete me';
3872 # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
3873 # check read via f_int1 success: 1
3874 # check read via f_int2 success: 1
3876 # check multiple-1 success: 1
3877 DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
3879 # check multiple-2 success: 1
3880 INSERT INTO t1 SELECT * FROM t0_template
3881 WHERE MOD(f_int1,3) = 0;
3883 # check multiple-3 success: 1
3884 UPDATE t1 SET f_int1 = f_int1 + @max_row
3885 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
3886 AND @max_row_div2 + @max_row_div4;
3888 # check multiple-4 success: 1
3889 DELETE FROM t1
3890 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
3891 AND @max_row_div2 + @max_row_div4 + @max_row;
3893 # check multiple-5 success: 1
3894 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
3895 INSERT INTO t1
3896 SET f_int1 = @cur_value , f_int2 = @cur_value,
3897 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3898 f_charbig = '#SINGLE#';
3900 # check single-1 success: 1
3901 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
3902 INSERT INTO t1
3903 SET f_int1 = @cur_value , f_int2 = @cur_value,
3904 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3905 f_charbig = '#SINGLE#';
3907 # check single-2 success: 1
3908 SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
3909 SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
3910 UPDATE t1 SET f_int1 = @cur_value2
3911 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
3913 # check single-3 success: 1
3914 SET @cur_value1= -1;
3915 SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
3916 UPDATE t1 SET f_int1 = @cur_value1
3917 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
3919 # check single-4 success: 1
3920 SELECT MAX(f_int1) INTO @cur_value FROM t1;
3921 DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
3923 # check single-5 success: 1
3924 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
3926 # check single-6 success: 1
3927 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3928 ERROR HY000: Table has no partition for value 2147483647
3929 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3930 INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3931 f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3932 f_charbig = '#NULL#';
3933 INSERT INTO t1
3934 SET f_int1 = NULL , f_int2 = -@max_row,
3935 f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3936 f_charbig = '#NULL#';
3937 # check null success: 1
3939 # check null-1 success: 1
3940 UPDATE t1 SET f_int1 = -@max_row
3941 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3942 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3944 # check null-2 success: 1
3945 UPDATE t1 SET f_int1 = NULL
3946 WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3947 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3949 # check null-3 success: 1
3950 DELETE FROM t1
3951 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3952 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3954 # check null-4 success: 1
3955 DELETE FROM t1
3956 WHERE f_int1 = 0 AND f_int2 = 0
3957 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
3958 AND f_charbig = '#NULL#';
3959 SET AUTOCOMMIT= 0;
3960 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3961 SELECT f_int1, f_int1, '', '', 'was inserted'
3962 FROM t0_template source_tab
3963 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3965 # check transactions-1 success: 1
3966 COMMIT WORK;
3968 # check transactions-2 success: 1
3969 ROLLBACK WORK;
3971 # check transactions-3 success: 1
3972 DELETE FROM t1 WHERE f_charbig = 'was inserted';
3973 COMMIT WORK;
3974 ROLLBACK WORK;
3976 # check transactions-4 success: 1
3977 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3978 SELECT f_int1, f_int1, '', '', 'was inserted'
3979 FROM t0_template source_tab
3980 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3982 # check transactions-5 success: 1
3983 ROLLBACK WORK;
3985 # check transactions-6 success: 1
3986 # INFO: Storage engine used for t1 seems to be transactional.
3987 COMMIT;
3989 # check transactions-7 success: 1
3990 DELETE FROM t1 WHERE f_charbig = 'was inserted';
3991 COMMIT WORK;
3992 SET @@session.sql_mode = 'traditional';
3993 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
3994 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3995 SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
3996 '', '', 'was inserted' FROM t0_template
3997 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3998 ERROR 22012: Division by 0
3999 COMMIT;
4001 # check transactions-8 success: 1
4002 # INFO: Storage engine used for t1 seems to be unable to revert
4003 # changes made by the failing statement.
4004 SET @@session.sql_mode = '';
4005 SET AUTOCOMMIT= 1;
4006 DELETE FROM t1 WHERE f_charbig = 'was inserted';
4007 COMMIT WORK;
4008 UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4010 # check special-1 success: 1
4011 UPDATE t1 SET f_charbig = '';
4013 # check special-2 success: 1
4014 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4015 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4016 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4017 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4018 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4019 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4020 'just inserted' FROM t0_template
4021 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4022 CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
4023 BEGIN
4024 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4025 f_charbig = 'updated by trigger'
4026 WHERE f_int1 = new.f_int1;
4027 END|
4028 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4029 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4030 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4032 # check trigger-1 success: 1
4033 DROP TRIGGER trg_1;
4034 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4035 f_int2 = CAST(f_char1 AS SIGNED INT),
4036 f_charbig = 'just inserted'
4037 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4038 DELETE FROM t0_aux
4039 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4040 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4041 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4042 'just inserted' FROM t0_template
4043 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4044 CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
4045 BEGIN
4046 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4047 f_charbig = 'updated by trigger'
4048 WHERE f_int1 = new.f_int1;
4049 END|
4050 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4051 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4052 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4054 # check trigger-2 success: 1
4055 DROP TRIGGER trg_1;
4056 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4057 f_int2 = CAST(f_char1 AS SIGNED INT),
4058 f_charbig = 'just inserted'
4059 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4060 DELETE FROM t0_aux
4061 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4062 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4063 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4064 'just inserted' FROM t0_template
4065 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4066 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4067 BEGIN
4068 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4069 f_charbig = 'updated by trigger'
4070 WHERE f_int1 = new.f_int1;
4071 END|
4072 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4073 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4075 # check trigger-3 success: 1
4076 DROP TRIGGER trg_1;
4077 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4078 f_int2 = CAST(f_char1 AS SIGNED INT),
4079 f_charbig = 'just inserted'
4080 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4081 DELETE FROM t0_aux
4082 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4083 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4084 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4085 'just inserted' FROM t0_template
4086 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4087 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4088 BEGIN
4089 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4090 f_charbig = 'updated by trigger'
4091 WHERE f_int1 = - old.f_int1;
4092 END|
4093 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4094 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4096 # check trigger-4 success: 1
4097 DROP TRIGGER trg_1;
4098 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4099 f_int2 = CAST(f_char1 AS SIGNED INT),
4100 f_charbig = 'just inserted'
4101 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4102 DELETE FROM t0_aux
4103 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4104 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4105 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4106 'just inserted' FROM t0_template
4107 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4108 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4109 BEGIN
4110 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4111 f_charbig = 'updated by trigger'
4112 WHERE f_int1 = new.f_int1;
4113 END|
4114 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4115 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4117 # check trigger-5 success: 1
4118 DROP TRIGGER trg_1;
4119 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4120 f_int2 = CAST(f_char1 AS SIGNED INT),
4121 f_charbig = 'just inserted'
4122 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4123 DELETE FROM t0_aux
4124 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4125 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4126 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4127 'just inserted' FROM t0_template
4128 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4129 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4130 BEGIN
4131 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4132 f_charbig = 'updated by trigger'
4133 WHERE f_int1 = - old.f_int1;
4134 END|
4135 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4136 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4138 # check trigger-6 success: 1
4139 DROP TRIGGER trg_1;
4140 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4141 f_int2 = CAST(f_char1 AS SIGNED INT),
4142 f_charbig = 'just inserted'
4143 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4144 DELETE FROM t0_aux
4145 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4146 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4147 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4148 'just inserted' FROM t0_template
4149 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4150 CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
4151 BEGIN
4152 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4153 f_charbig = 'updated by trigger'
4154 WHERE f_int1 = - old.f_int1;
4155 END|
4156 DELETE FROM t0_aux
4157 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4159 # check trigger-7 success: 1
4160 DROP TRIGGER trg_1;
4161 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4162 f_int2 = CAST(f_char1 AS SIGNED INT),
4163 f_charbig = 'just inserted'
4164 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4165 DELETE FROM t0_aux
4166 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4167 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4168 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4169 'just inserted' FROM t0_template
4170 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4171 CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
4172 BEGIN
4173 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4174 f_charbig = 'updated by trigger'
4175 WHERE f_int1 = - old.f_int1;
4176 END|
4177 DELETE FROM t0_aux
4178 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4180 # check trigger-8 success: 1
4181 DROP TRIGGER trg_1;
4182 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4183 f_int2 = CAST(f_char1 AS SIGNED INT),
4184 f_charbig = 'just inserted'
4185 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4186 DELETE FROM t0_aux
4187 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4188 DELETE FROM t1
4189 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4190 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4191 BEGIN
4192 SET new.f_int1 = old.f_int1 + @max_row,
4193 new.f_int2 = old.f_int2 - @max_row,
4194 new.f_charbig = '####updated per update trigger####';
4195 END|
4196 UPDATE t1
4197 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4198 f_charbig = '####updated per update statement itself####';
4200 # check trigger-9 success: 1
4201 DROP TRIGGER trg_2;
4202 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4203 f_int2 = CAST(f_char1 AS SIGNED INT),
4204 f_charbig = CONCAT('===',f_char1,'===');
4205 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4206 BEGIN
4207 SET new.f_int1 = new.f_int1 + @max_row,
4208 new.f_int2 = new.f_int2 - @max_row,
4209 new.f_charbig = '####updated per update trigger####';
4210 END|
4211 UPDATE t1
4212 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4213 f_charbig = '####updated per update statement itself####';
4215 # check trigger-10 success: 1
4216 DROP TRIGGER trg_2;
4217 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4218 f_int2 = CAST(f_char1 AS SIGNED INT),
4219 f_charbig = CONCAT('===',f_char1,'===');
4220 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4221 BEGIN
4222 SET new.f_int1 = @my_max1 + @counter,
4223 new.f_int2 = @my_min2 - @counter,
4224 new.f_charbig = '####updated per insert trigger####';
4225 SET @counter = @counter + 1;
4226 END|
4227 SET @counter = 1;
4228 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4229 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4230 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4231 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4232 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4233 ORDER BY f_int1;
4234 DROP TRIGGER trg_3;
4236 # check trigger-11 success: 1
4237 DELETE FROM t1
4238 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4239 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4240 AND f_charbig = '####updated per insert trigger####';
4241 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4242 BEGIN
4243 SET new.f_int1 = @my_max1 + @counter,
4244 new.f_int2 = @my_min2 - @counter,
4245 new.f_charbig = '####updated per insert trigger####';
4246 SET @counter = @counter + 1;
4247 END|
4248 SET @counter = 1;
4249 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4250 INSERT INTO t1 (f_char1, f_char2, f_charbig)
4251 SELECT CAST(f_int1 AS CHAR),
4252 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4253 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4254 ORDER BY f_int1;
4255 DROP TRIGGER trg_3;
4257 # check trigger-12 success: 1
4258 DELETE FROM t1
4259 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4260 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4261 AND f_charbig = '####updated per insert trigger####';
4262 ANALYZE TABLE t1;
4263 Table Op Msg_type Msg_text
4264 test.t1 analyze note The storage engine for the table doesn't support analyze
4265 CHECK TABLE t1 EXTENDED;
4266 Table Op Msg_type Msg_text
4267 test.t1 check note The storage engine for the table doesn't support check
4268 CHECKSUM TABLE t1 EXTENDED;
4269 Table Checksum
4270 test.t1 <some_value>
4271 OPTIMIZE TABLE t1;
4272 Table Op Msg_type Msg_text
4273 test.t1 optimize note The storage engine for the table doesn't support optimize
4274 # check layout success: 1
4275 REPAIR TABLE t1 EXTENDED;
4276 Table Op Msg_type Msg_text
4277 test.t1 repair note The storage engine for the table doesn't support repair
4278 # check layout success: 1
4279 TRUNCATE t1;
4281 # check TRUNCATE success: 1
4282 # check layout success: 1
4283 # End usability test (inc/partition_check.inc)
4284 DROP TABLE t1;
4285 #------------------------------------------------------------------------
4286 # 6 Session default engine differs from engine used within create table
4287 #------------------------------------------------------------------------
4288 SET SESSION storage_engine='MEMORY';
4289 CREATE TABLE t1 (
4290 f_int1 INTEGER,
4291 f_int2 INTEGER,
4292 f_char1 CHAR(20),
4293 f_char2 CHAR(20),
4294 f_charbig VARCHAR(1000)
4295 )
4296 PARTITION BY HASH(f_int1) ( PARTITION part1 ENGINE = 'ndbcluster');
4297 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4298 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
4299 # Start usability test (inc/partition_check.inc)
4300 create_command
4301 SHOW CREATE TABLE t1;
4302 Table Create Table
4303 t1 CREATE TABLE `t1` (
4304 `f_int1` int(11) DEFAULT NULL,
4305 `f_int2` int(11) DEFAULT NULL,
4306 `f_char1` char(20) DEFAULT NULL,
4307 `f_char2` char(20) DEFAULT NULL,
4308 `f_charbig` varchar(1000) DEFAULT NULL
4309 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
4310 /*!50100 PARTITION BY HASH (f_int1)
4311 (PARTITION part1 ENGINE = ndbcluster) */
4313 # check prerequisites-1 success: 1
4314 # check COUNT(*) success: 1
4315 # check MIN/MAX(f_int1) success: 1
4316 # check MIN/MAX(f_int2) success: 1
4317 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4318 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4319 CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
4320 WHERE f_int1 IN (2,3);
4321 # check prerequisites-3 success: 1
4322 DELETE FROM t1 WHERE f_charbig = 'delete me';
4323 # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
4324 # check read via f_int1 success: 1
4325 # check read via f_int2 success: 1
4327 # check multiple-1 success: 1
4328 DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
4330 # check multiple-2 success: 1
4331 INSERT INTO t1 SELECT * FROM t0_template
4332 WHERE MOD(f_int1,3) = 0;
4334 # check multiple-3 success: 1
4335 UPDATE t1 SET f_int1 = f_int1 + @max_row
4336 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
4337 AND @max_row_div2 + @max_row_div4;
4339 # check multiple-4 success: 1
4340 DELETE FROM t1
4341 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
4342 AND @max_row_div2 + @max_row_div4 + @max_row;
4344 # check multiple-5 success: 1
4345 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
4346 INSERT INTO t1
4347 SET f_int1 = @cur_value , f_int2 = @cur_value,
4348 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4349 f_charbig = '#SINGLE#';
4351 # check single-1 success: 1
4352 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
4353 INSERT INTO t1
4354 SET f_int1 = @cur_value , f_int2 = @cur_value,
4355 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4356 f_charbig = '#SINGLE#';
4358 # check single-2 success: 1
4359 SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
4360 SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
4361 UPDATE t1 SET f_int1 = @cur_value2
4362 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
4364 # check single-3 success: 1
4365 SET @cur_value1= -1;
4366 SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
4367 UPDATE t1 SET f_int1 = @cur_value1
4368 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
4370 # check single-4 success: 1
4371 SELECT MAX(f_int1) INTO @cur_value FROM t1;
4372 DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
4374 # check single-5 success: 1
4375 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
4377 # check single-6 success: 1
4378 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
4380 # check single-7 success: 1
4381 DELETE FROM t1 WHERE f_charbig = '#2147483647##';
4382 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
4383 INSERT t1 SET f_int1 = 0 , f_int2 = 0,
4384 f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
4385 f_charbig = '#NULL#';
4386 INSERT INTO t1
4387 SET f_int1 = NULL , f_int2 = -@max_row,
4388 f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
4389 f_charbig = '#NULL#';
4390 # check null success: 1
4392 # check null-1 success: 1
4393 UPDATE t1 SET f_int1 = -@max_row
4394 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4395 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4397 # check null-2 success: 1
4398 UPDATE t1 SET f_int1 = NULL
4399 WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4400 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4402 # check null-3 success: 1
4403 DELETE FROM t1
4404 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4405 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4407 # check null-4 success: 1
4408 DELETE FROM t1
4409 WHERE f_int1 = 0 AND f_int2 = 0
4410 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
4411 AND f_charbig = '#NULL#';
4412 SET AUTOCOMMIT= 0;
4413 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4414 SELECT f_int1, f_int1, '', '', 'was inserted'
4415 FROM t0_template source_tab
4416 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4418 # check transactions-1 success: 1
4419 COMMIT WORK;
4421 # check transactions-2 success: 1
4422 ROLLBACK WORK;
4424 # check transactions-3 success: 1
4425 DELETE FROM t1 WHERE f_charbig = 'was inserted';
4426 COMMIT WORK;
4427 ROLLBACK WORK;
4429 # check transactions-4 success: 1
4430 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4431 SELECT f_int1, f_int1, '', '', 'was inserted'
4432 FROM t0_template source_tab
4433 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4435 # check transactions-5 success: 1
4436 ROLLBACK WORK;
4437 Warnings:
4438 Warning 1196 Some non-transactional changed tables couldn't be rolled back
4440 # check transactions-6 success: 1
4441 # INFO: Storage engine used for t1 seems to be not transactional.
4442 COMMIT;
4444 # check transactions-7 success: 1
4445 DELETE FROM t1 WHERE f_charbig = 'was inserted';
4446 COMMIT WORK;
4447 SET @@session.sql_mode = 'traditional';
4448 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
4449 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4450 SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
4451 '', '', 'was inserted' FROM t0_template
4452 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4453 ERROR 22012: Division by 0
4454 COMMIT;
4456 # check transactions-8 success: 1
4457 # INFO: Storage engine used for t1 seems to be unable to revert
4458 # changes made by the failing statement.
4459 SET @@session.sql_mode = '';
4460 SET AUTOCOMMIT= 1;
4461 DELETE FROM t1 WHERE f_charbig = 'was inserted';
4462 COMMIT WORK;
4463 UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4465 # check special-1 success: 1
4466 UPDATE t1 SET f_charbig = '';
4468 # check special-2 success: 1
4469 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4470 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4471 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4472 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4473 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4474 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4475 'just inserted' FROM t0_template
4476 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4477 CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
4478 BEGIN
4479 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4480 f_charbig = 'updated by trigger'
4481 WHERE f_int1 = new.f_int1;
4482 END|
4483 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4484 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4485 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4487 # check trigger-1 success: 1
4488 DROP TRIGGER trg_1;
4489 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4490 f_int2 = CAST(f_char1 AS SIGNED INT),
4491 f_charbig = 'just inserted'
4492 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4493 DELETE FROM t0_aux
4494 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4495 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4496 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4497 'just inserted' FROM t0_template
4498 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4499 CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
4500 BEGIN
4501 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4502 f_charbig = 'updated by trigger'
4503 WHERE f_int1 = new.f_int1;
4504 END|
4505 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4506 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4507 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4509 # check trigger-2 success: 1
4510 DROP TRIGGER trg_1;
4511 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4512 f_int2 = CAST(f_char1 AS SIGNED INT),
4513 f_charbig = 'just inserted'
4514 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4515 DELETE FROM t0_aux
4516 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4517 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4518 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4519 'just inserted' FROM t0_template
4520 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4521 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4522 BEGIN
4523 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4524 f_charbig = 'updated by trigger'
4525 WHERE f_int1 = new.f_int1;
4526 END|
4527 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4528 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4530 # check trigger-3 success: 1
4531 DROP TRIGGER trg_1;
4532 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4533 f_int2 = CAST(f_char1 AS SIGNED INT),
4534 f_charbig = 'just inserted'
4535 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4536 DELETE FROM t0_aux
4537 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4538 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4539 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4540 'just inserted' FROM t0_template
4541 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4542 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4543 BEGIN
4544 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4545 f_charbig = 'updated by trigger'
4546 WHERE f_int1 = - old.f_int1;
4547 END|
4548 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4549 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4551 # check trigger-4 success: 1
4552 DROP TRIGGER trg_1;
4553 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4554 f_int2 = CAST(f_char1 AS SIGNED INT),
4555 f_charbig = 'just inserted'
4556 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4557 DELETE FROM t0_aux
4558 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4559 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4560 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4561 'just inserted' FROM t0_template
4562 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4563 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4564 BEGIN
4565 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4566 f_charbig = 'updated by trigger'
4567 WHERE f_int1 = new.f_int1;
4568 END|
4569 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4570 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4572 # check trigger-5 success: 1
4573 DROP TRIGGER trg_1;
4574 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4575 f_int2 = CAST(f_char1 AS SIGNED INT),
4576 f_charbig = 'just inserted'
4577 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4578 DELETE FROM t0_aux
4579 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4580 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4581 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4582 'just inserted' FROM t0_template
4583 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4584 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4585 BEGIN
4586 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4587 f_charbig = 'updated by trigger'
4588 WHERE f_int1 = - old.f_int1;
4589 END|
4590 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4591 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4593 # check trigger-6 success: 1
4594 DROP TRIGGER trg_1;
4595 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4596 f_int2 = CAST(f_char1 AS SIGNED INT),
4597 f_charbig = 'just inserted'
4598 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4599 DELETE FROM t0_aux
4600 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4601 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4602 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4603 'just inserted' FROM t0_template
4604 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4605 CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
4606 BEGIN
4607 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4608 f_charbig = 'updated by trigger'
4609 WHERE f_int1 = - old.f_int1;
4610 END|
4611 DELETE FROM t0_aux
4612 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4614 # check trigger-7 success: 1
4615 DROP TRIGGER trg_1;
4616 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4617 f_int2 = CAST(f_char1 AS SIGNED INT),
4618 f_charbig = 'just inserted'
4619 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4620 DELETE FROM t0_aux
4621 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4622 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4623 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4624 'just inserted' FROM t0_template
4625 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4626 CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
4627 BEGIN
4628 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4629 f_charbig = 'updated by trigger'
4630 WHERE f_int1 = - old.f_int1;
4631 END|
4632 DELETE FROM t0_aux
4633 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4635 # check trigger-8 success: 1
4636 DROP TRIGGER trg_1;
4637 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4638 f_int2 = CAST(f_char1 AS SIGNED INT),
4639 f_charbig = 'just inserted'
4640 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4641 DELETE FROM t0_aux
4642 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4643 DELETE FROM t1
4644 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4645 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4646 BEGIN
4647 SET new.f_int1 = old.f_int1 + @max_row,
4648 new.f_int2 = old.f_int2 - @max_row,
4649 new.f_charbig = '####updated per update trigger####';
4650 END|
4651 UPDATE t1
4652 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4653 f_charbig = '####updated per update statement itself####';
4655 # check trigger-9 success: 1
4656 DROP TRIGGER trg_2;
4657 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4658 f_int2 = CAST(f_char1 AS SIGNED INT),
4659 f_charbig = CONCAT('===',f_char1,'===');
4660 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4661 BEGIN
4662 SET new.f_int1 = new.f_int1 + @max_row,
4663 new.f_int2 = new.f_int2 - @max_row,
4664 new.f_charbig = '####updated per update trigger####';
4665 END|
4666 UPDATE t1
4667 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4668 f_charbig = '####updated per update statement itself####';
4670 # check trigger-10 success: 1
4671 DROP TRIGGER trg_2;
4672 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4673 f_int2 = CAST(f_char1 AS SIGNED INT),
4674 f_charbig = CONCAT('===',f_char1,'===');
4675 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4676 BEGIN
4677 SET new.f_int1 = @my_max1 + @counter,
4678 new.f_int2 = @my_min2 - @counter,
4679 new.f_charbig = '####updated per insert trigger####';
4680 SET @counter = @counter + 1;
4681 END|
4682 SET @counter = 1;
4683 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4684 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4685 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4686 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4687 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4688 ORDER BY f_int1;
4689 DROP TRIGGER trg_3;
4691 # check trigger-11 success: 1
4692 DELETE FROM t1
4693 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4694 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4695 AND f_charbig = '####updated per insert trigger####';
4696 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4697 BEGIN
4698 SET new.f_int1 = @my_max1 + @counter,
4699 new.f_int2 = @my_min2 - @counter,
4700 new.f_charbig = '####updated per insert trigger####';
4701 SET @counter = @counter + 1;
4702 END|
4703 SET @counter = 1;
4704 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4705 INSERT INTO t1 (f_char1, f_char2, f_charbig)
4706 SELECT CAST(f_int1 AS CHAR),
4707 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4708 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4709 ORDER BY f_int1;
4710 DROP TRIGGER trg_3;
4712 # check trigger-12 success: 1
4713 DELETE FROM t1
4714 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4715 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4716 AND f_charbig = '####updated per insert trigger####';
4717 ANALYZE TABLE t1;
4718 Table Op Msg_type Msg_text
4719 test.t1 analyze note The storage engine for the table doesn't support analyze
4720 CHECK TABLE t1 EXTENDED;
4721 Table Op Msg_type Msg_text
4722 test.t1 check note The storage engine for the table doesn't support check
4723 CHECKSUM TABLE t1 EXTENDED;
4724 Table Checksum
4725 test.t1 <some_value>
4726 OPTIMIZE TABLE t1;
4727 Table Op Msg_type Msg_text
4728 test.t1 optimize note The storage engine for the table doesn't support optimize
4729 # check layout success: 1
4730 REPAIR TABLE t1 EXTENDED;
4731 Table Op Msg_type Msg_text
4732 test.t1 repair note The storage engine for the table doesn't support repair
4733 # check layout success: 1
4734 TRUNCATE t1;
4736 # check TRUNCATE success: 1
4737 # check layout success: 1
4738 # End usability test (inc/partition_check.inc)
4739 DROP TABLE t1;
4740 CREATE TABLE t1 (
4741 f_int1 INTEGER,
4742 f_int2 INTEGER,
4743 f_char1 CHAR(20),
4744 f_char2 CHAR(20),
4745 f_charbig VARCHAR(1000)
4746 )
4747 PARTITION BY RANGE(f_int1)
4748 SUBPARTITION BY HASH(f_int1)
4749 ( PARTITION part1 VALUES LESS THAN (1000)
4750 (SUBPARTITION subpart11 STORAGE ENGINE = 'ndbcluster',
4751 SUBPARTITION subpart12 STORAGE ENGINE = 'ndbcluster'));
4752 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4753 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
4754 # Start usability test (inc/partition_check.inc)
4755 create_command
4756 SHOW CREATE TABLE t1;
4757 Table Create Table
4758 t1 CREATE TABLE `t1` (
4759 `f_int1` int(11) DEFAULT NULL,
4760 `f_int2` int(11) DEFAULT NULL,
4761 `f_char1` char(20) DEFAULT NULL,
4762 `f_char2` char(20) DEFAULT NULL,
4763 `f_charbig` varchar(1000) DEFAULT NULL
4764 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
4765 /*!50100 PARTITION BY RANGE (f_int1)
4766 SUBPARTITION BY HASH (f_int1)
4767 (PARTITION part1 VALUES LESS THAN (1000)
4768 (SUBPARTITION subpart11 ENGINE = ndbcluster,
4769 SUBPARTITION subpart12 ENGINE = ndbcluster)) */
4771 # check prerequisites-1 success: 1
4772 # check COUNT(*) success: 1
4773 # check MIN/MAX(f_int1) success: 1
4774 # check MIN/MAX(f_int2) success: 1
4775 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4776 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4777 CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
4778 WHERE f_int1 IN (2,3);
4779 # check prerequisites-3 success: 1
4780 DELETE FROM t1 WHERE f_charbig = 'delete me';
4781 # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
4782 # check read via f_int1 success: 1
4783 # check read via f_int2 success: 1
4785 # check multiple-1 success: 1
4786 DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
4788 # check multiple-2 success: 1
4789 INSERT INTO t1 SELECT * FROM t0_template
4790 WHERE MOD(f_int1,3) = 0;
4792 # check multiple-3 success: 1
4793 UPDATE t1 SET f_int1 = f_int1 + @max_row
4794 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
4795 AND @max_row_div2 + @max_row_div4;
4797 # check multiple-4 success: 1
4798 DELETE FROM t1
4799 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
4800 AND @max_row_div2 + @max_row_div4 + @max_row;
4802 # check multiple-5 success: 1
4803 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
4804 INSERT INTO t1
4805 SET f_int1 = @cur_value , f_int2 = @cur_value,
4806 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4807 f_charbig = '#SINGLE#';
4809 # check single-1 success: 1
4810 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
4811 INSERT INTO t1
4812 SET f_int1 = @cur_value , f_int2 = @cur_value,
4813 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4814 f_charbig = '#SINGLE#';
4816 # check single-2 success: 1
4817 SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
4818 SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
4819 UPDATE t1 SET f_int1 = @cur_value2
4820 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
4822 # check single-3 success: 1
4823 SET @cur_value1= -1;
4824 SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
4825 UPDATE t1 SET f_int1 = @cur_value1
4826 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
4828 # check single-4 success: 1
4829 SELECT MAX(f_int1) INTO @cur_value FROM t1;
4830 DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
4832 # check single-5 success: 1
4833 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
4835 # check single-6 success: 1
4836 INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
4838 # check single-7 success: 1
4839 DELETE FROM t1 WHERE f_charbig = '#2147483647##';
4840 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
4841 INSERT t1 SET f_int1 = 0 , f_int2 = 0,
4842 f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
4843 f_charbig = '#NULL#';
4844 INSERT INTO t1
4845 SET f_int1 = NULL , f_int2 = -@max_row,
4846 f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
4847 f_charbig = '#NULL#';
4848 # check null success: 1
4850 # check null-1 success: 1
4851 UPDATE t1 SET f_int1 = -@max_row
4852 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4853 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4855 # check null-2 success: 1
4856 UPDATE t1 SET f_int1 = NULL
4857 WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4858 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4860 # check null-3 success: 1
4861 DELETE FROM t1
4862 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4863 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4865 # check null-4 success: 1
4866 DELETE FROM t1
4867 WHERE f_int1 = 0 AND f_int2 = 0
4868 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
4869 AND f_charbig = '#NULL#';
4870 SET AUTOCOMMIT= 0;
4871 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4872 SELECT f_int1, f_int1, '', '', 'was inserted'
4873 FROM t0_template source_tab
4874 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4876 # check transactions-1 success: 1
4877 COMMIT WORK;
4879 # check transactions-2 success: 1
4880 ROLLBACK WORK;
4882 # check transactions-3 success: 1
4883 DELETE FROM t1 WHERE f_charbig = 'was inserted';
4884 COMMIT WORK;
4885 ROLLBACK WORK;
4887 # check transactions-4 success: 1
4888 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4889 SELECT f_int1, f_int1, '', '', 'was inserted'
4890 FROM t0_template source_tab
4891 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4893 # check transactions-5 success: 1
4894 ROLLBACK WORK;
4895 Warnings:
4896 Warning 1196 Some non-transactional changed tables couldn't be rolled back
4898 # check transactions-6 success: 1
4899 # INFO: Storage engine used for t1 seems to be not transactional.
4900 COMMIT;
4902 # check transactions-7 success: 1
4903 DELETE FROM t1 WHERE f_charbig = 'was inserted';
4904 COMMIT WORK;
4905 SET @@session.sql_mode = 'traditional';
4906 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
4907 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4908 SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
4909 '', '', 'was inserted' FROM t0_template
4910 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4911 ERROR 22012: Division by 0
4912 COMMIT;
4914 # check transactions-8 success: 1
4915 # INFO: Storage engine used for t1 seems to be unable to revert
4916 # changes made by the failing statement.
4917 SET @@session.sql_mode = '';
4918 SET AUTOCOMMIT= 1;
4919 DELETE FROM t1 WHERE f_charbig = 'was inserted';
4920 COMMIT WORK;
4921 UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4923 # check special-1 success: 1
4924 UPDATE t1 SET f_charbig = '';
4926 # check special-2 success: 1
4927 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4928 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4929 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4930 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4931 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4932 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4933 'just inserted' FROM t0_template
4934 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4935 CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
4936 BEGIN
4937 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4938 f_charbig = 'updated by trigger'
4939 WHERE f_int1 = new.f_int1;
4940 END|
4941 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4942 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4943 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4945 # check trigger-1 success: 1
4946 DROP TRIGGER trg_1;
4947 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4948 f_int2 = CAST(f_char1 AS SIGNED INT),
4949 f_charbig = 'just inserted'
4950 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4951 DELETE FROM t0_aux
4952 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4953 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4954 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4955 'just inserted' FROM t0_template
4956 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4957 CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
4958 BEGIN
4959 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4960 f_charbig = 'updated by trigger'
4961 WHERE f_int1 = new.f_int1;
4962 END|
4963 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4964 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4965 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4967 # check trigger-2 success: 1
4968 DROP TRIGGER trg_1;
4969 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4970 f_int2 = CAST(f_char1 AS SIGNED INT),
4971 f_charbig = 'just inserted'
4972 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4973 DELETE FROM t0_aux
4974 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4975 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4976 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4977 'just inserted' FROM t0_template
4978 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4979 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4980 BEGIN
4981 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4982 f_charbig = 'updated by trigger'
4983 WHERE f_int1 = new.f_int1;
4984 END|
4985 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4986 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4988 # check trigger-3 success: 1
4989 DROP TRIGGER trg_1;
4990 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4991 f_int2 = CAST(f_char1 AS SIGNED INT),
4992 f_charbig = 'just inserted'
4993 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4994 DELETE FROM t0_aux
4995 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4996 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4997 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4998 'just inserted' FROM t0_template
4999 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5000 CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
5001 BEGIN
5002 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5003 f_charbig = 'updated by trigger'
5004 WHERE f_int1 = - old.f_int1;
5005 END|
5006 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5007 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5009 # check trigger-4 success: 1
5010 DROP TRIGGER trg_1;
5011 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5012 f_int2 = CAST(f_char1 AS SIGNED INT),
5013 f_charbig = 'just inserted'
5014 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5015 DELETE FROM t0_aux
5016 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5017 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5018 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5019 'just inserted' FROM t0_template
5020 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5021 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
5022 BEGIN
5023 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5024 f_charbig = 'updated by trigger'
5025 WHERE f_int1 = new.f_int1;
5026 END|
5027 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5028 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5030 # check trigger-5 success: 1
5031 DROP TRIGGER trg_1;
5032 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5033 f_int2 = CAST(f_char1 AS SIGNED INT),
5034 f_charbig = 'just inserted'
5035 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5036 DELETE FROM t0_aux
5037 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5038 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5039 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5040 'just inserted' FROM t0_template
5041 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5042 CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
5043 BEGIN
5044 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5045 f_charbig = 'updated by trigger'
5046 WHERE f_int1 = - old.f_int1;
5047 END|
5048 UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5049 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5051 # check trigger-6 success: 1
5052 DROP TRIGGER trg_1;
5053 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5054 f_int2 = CAST(f_char1 AS SIGNED INT),
5055 f_charbig = 'just inserted'
5056 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5057 DELETE FROM t0_aux
5058 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5059 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5060 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5061 'just inserted' FROM t0_template
5062 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5063 CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
5064 BEGIN
5065 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5066 f_charbig = 'updated by trigger'
5067 WHERE f_int1 = - old.f_int1;
5068 END|
5069 DELETE FROM t0_aux
5070 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5072 # check trigger-7 success: 1
5073 DROP TRIGGER trg_1;
5074 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5075 f_int2 = CAST(f_char1 AS SIGNED INT),
5076 f_charbig = 'just inserted'
5077 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5078 DELETE FROM t0_aux
5079 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5080 INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5081 SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5082 'just inserted' FROM t0_template
5083 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5084 CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
5085 BEGIN
5086 UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5087 f_charbig = 'updated by trigger'
5088 WHERE f_int1 = - old.f_int1;
5089 END|
5090 DELETE FROM t0_aux
5091 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5093 # check trigger-8 success: 1
5094 DROP TRIGGER trg_1;
5095 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5096 f_int2 = CAST(f_char1 AS SIGNED INT),
5097 f_charbig = 'just inserted'
5098 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5099 DELETE FROM t0_aux
5100 WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5101 DELETE FROM t1
5102 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5103 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
5104 BEGIN
5105 SET new.f_int1 = old.f_int1 + @max_row,
5106 new.f_int2 = old.f_int2 - @max_row,
5107 new.f_charbig = '####updated per update trigger####';
5108 END|
5109 UPDATE t1
5110 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
5111 f_charbig = '####updated per update statement itself####';
5113 # check trigger-9 success: 1
5114 DROP TRIGGER trg_2;
5115 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5116 f_int2 = CAST(f_char1 AS SIGNED INT),
5117 f_charbig = CONCAT('===',f_char1,'===');
5118 CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
5119 BEGIN
5120 SET new.f_int1 = new.f_int1 + @max_row,
5121 new.f_int2 = new.f_int2 - @max_row,
5122 new.f_charbig = '####updated per update trigger####';
5123 END|
5124 UPDATE t1
5125 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
5126 f_charbig = '####updated per update statement itself####';
5128 # check trigger-10 success: 1
5129 DROP TRIGGER trg_2;
5130 UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5131 f_int2 = CAST(f_char1 AS SIGNED INT),
5132 f_charbig = CONCAT('===',f_char1,'===');
5133 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
5134 BEGIN
5135 SET new.f_int1 = @my_max1 + @counter,
5136 new.f_int2 = @my_min2 - @counter,
5137 new.f_charbig = '####updated per insert trigger####';
5138 SET @counter = @counter + 1;
5139 END|
5140 SET @counter = 1;
5141 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
5142 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
5143 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
5144 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
5145 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
5146 ORDER BY f_int1;
5147 DROP TRIGGER trg_3;
5149 # check trigger-11 success: 1
5150 DELETE FROM t1
5151 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
5152 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
5153 AND f_charbig = '####updated per insert trigger####';
5154 CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
5155 BEGIN
5156 SET new.f_int1 = @my_max1 + @counter,
5157 new.f_int2 = @my_min2 - @counter,
5158 new.f_charbig = '####updated per insert trigger####';
5159 SET @counter = @counter + 1;
5160 END|
5161 SET @counter = 1;
5162 SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
5163 INSERT INTO t1 (f_char1, f_char2, f_charbig)
5164 SELECT CAST(f_int1 AS CHAR),
5165 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
5166 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
5167 ORDER BY f_int1;
5168 DROP TRIGGER trg_3;
5170 # check trigger-12 success: 1
5171 DELETE FROM t1
5172 WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
5173 AND f_int2 <> CAST(f_char1 AS SIGNED INT)
5174 AND f_charbig = '####updated per insert trigger####';
5175 ANALYZE TABLE t1;
5176 Table Op Msg_type Msg_text
5177 test.t1 analyze note The storage engine for the table doesn't support analyze
5178 CHECK TABLE t1 EXTENDED;
5179 Table Op Msg_type Msg_text
5180 test.t1 check note The storage engine for the table doesn't support check
5181 CHECKSUM TABLE t1 EXTENDED;
5182 Table Checksum
5183 test.t1 <some_value>
5184 OPTIMIZE TABLE t1;
5185 Table Op Msg_type Msg_text
5186 test.t1 optimize note The storage engine for the table doesn't support optimize
5187 # check layout success: 1
5188 REPAIR TABLE t1 EXTENDED;
5189 Table Op Msg_type Msg_text
5190 test.t1 repair note The storage engine for the table doesn't support repair
5191 # check layout success: 1
5192 TRUNCATE t1;
5194 # check TRUNCATE success: 1
5195 # check layout success: 1
5196 # End usability test (inc/partition_check.inc)
5197 DROP TABLE t1;
5198 SET SESSION storage_engine='ndbcluster';
5199 DROP VIEW IF EXISTS v1;
5200 DROP TABLE IF EXISTS t1;
5201 DROP TABLE IF EXISTS t0_aux;
5202 DROP TABLE IF EXISTS t0_definition;
5203 DROP TABLE IF EXISTS t0_template;