2
SET @@session.storage_engine = 'InnoDB';
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 (
18
f_charbig VARCHAR(1000) ,
21
# Logging of <max_row> INSERTs into t0_template suppressed
22
DROP TABLE IF EXISTS t0_definition;
23
CREATE TABLE t0_definition (
25
create_command VARBINARY(5000),
26
file_list VARBINARY(10000),
29
DROP TABLE IF EXISTS t0_aux;
30
CREATE TABLE t0_aux ( f_int1 INTEGER,
34
f_charbig VARCHAR(1000) )
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
#------------------------------------------------------------------------
55
f_charbig VARCHAR(1000)
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)
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=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) PARTITIONS 2 */
72
# check prerequisites-1 success: 1
73
# check COUNT(*) success: 1
74
# check MIN/MAX(f_int1) success: 1
75
# check MIN/MAX(f_int2) success: 1
76
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
77
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
78
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
79
WHERE f_int1 IN (2,3);
80
# check prerequisites-3 success: 1
81
DELETE FROM t1 WHERE f_charbig = 'delete me';
82
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
83
# check read via f_int1 success: 1
84
# check read via f_int2 success: 1
86
# check multiple-1 success: 1
87
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
89
# check multiple-2 success: 1
90
INSERT INTO t1 SELECT * FROM t0_template
91
WHERE MOD(f_int1,3) = 0;
93
# check multiple-3 success: 1
94
UPDATE t1 SET f_int1 = f_int1 + @max_row
95
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
96
AND @max_row_div2 + @max_row_div4;
98
# check multiple-4 success: 1
100
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
101
AND @max_row_div2 + @max_row_div4 + @max_row;
103
# check multiple-5 success: 1
104
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
106
SET f_int1 = @cur_value , f_int2 = @cur_value,
107
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
108
f_charbig = '#SINGLE#';
110
# check single-1 success: 1
111
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
113
SET f_int1 = @cur_value , f_int2 = @cur_value,
114
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
115
f_charbig = '#SINGLE#';
117
# check single-2 success: 1
118
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
119
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
120
UPDATE t1 SET f_int1 = @cur_value2
121
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
123
# check single-3 success: 1
125
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
126
UPDATE t1 SET f_int1 = @cur_value1
127
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
129
# check single-4 success: 1
130
SELECT MAX(f_int1) INTO @cur_value FROM t1;
131
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
133
# check single-5 success: 1
134
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
136
# check single-6 success: 1
137
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
139
# check single-7 success: 1
140
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
141
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
142
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
143
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
144
f_charbig = '#NULL#';
146
SET f_int1 = NULL , f_int2 = -@max_row,
147
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
148
f_charbig = '#NULL#';
149
# check null success: 1
151
# check null-1 success: 1
152
UPDATE t1 SET f_int1 = -@max_row
153
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
154
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
156
# check null-2 success: 1
157
UPDATE t1 SET f_int1 = NULL
158
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
159
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
161
# check null-3 success: 1
163
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
164
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
166
# check null-4 success: 1
168
WHERE f_int1 = 0 AND f_int2 = 0
169
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
170
AND f_charbig = '#NULL#';
172
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
173
SELECT f_int1, f_int1, '', '', 'was inserted'
174
FROM t0_template source_tab
175
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
177
# check transactions-1 success: 1
180
# check transactions-2 success: 1
183
# check transactions-3 success: 1
184
DELETE FROM t1 WHERE f_charbig = 'was inserted';
188
# check transactions-4 success: 1
189
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
190
SELECT f_int1, f_int1, '', '', 'was inserted'
191
FROM t0_template source_tab
192
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
194
# check transactions-5 success: 1
197
# check transactions-6 success: 1
198
# INFO: Storage engine used for t1 seems to be transactional.
201
# check transactions-7 success: 1
202
DELETE FROM t1 WHERE f_charbig = 'was inserted';
204
SET @@session.sql_mode = 'traditional';
205
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
206
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
207
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
208
'', '', 'was inserted' FROM t0_template
209
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
210
ERROR 22012: Division by 0
213
# check transactions-8 success: 1
214
# INFO: Storage engine used for t1 seems to be able to revert
215
# changes made by the failing statement.
216
SET @@session.sql_mode = '';
218
DELETE FROM t1 WHERE f_charbig = 'was inserted';
220
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
222
# check special-1 success: 1
223
UPDATE t1 SET f_charbig = '';
225
# check special-2 success: 1
226
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
227
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
228
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
229
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
230
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
231
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
232
'just inserted' FROM t0_template
233
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
234
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
236
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
237
f_charbig = 'updated by trigger'
238
WHERE f_int1 = new.f_int1;
240
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
241
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
242
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
244
# check trigger-1 success: 1
246
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
247
f_int2 = CAST(f_char1 AS SIGNED INT),
248
f_charbig = 'just inserted'
249
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
251
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
252
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
253
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
254
'just inserted' FROM t0_template
255
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
256
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
258
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
259
f_charbig = 'updated by trigger'
260
WHERE f_int1 = new.f_int1;
262
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
263
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
264
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
266
# check trigger-2 success: 1
268
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
269
f_int2 = CAST(f_char1 AS SIGNED INT),
270
f_charbig = 'just inserted'
271
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
273
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
274
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
275
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
276
'just inserted' FROM t0_template
277
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
278
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
280
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
281
f_charbig = 'updated by trigger'
282
WHERE f_int1 = new.f_int1;
284
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
285
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
287
# check trigger-3 success: 1
289
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
290
f_int2 = CAST(f_char1 AS SIGNED INT),
291
f_charbig = 'just inserted'
292
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
294
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
295
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
296
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
297
'just inserted' FROM t0_template
298
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
299
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
301
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
302
f_charbig = 'updated by trigger'
303
WHERE f_int1 = - old.f_int1;
305
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
306
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
308
# check trigger-4 success: 1
310
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
311
f_int2 = CAST(f_char1 AS SIGNED INT),
312
f_charbig = 'just inserted'
313
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
315
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
316
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
317
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
318
'just inserted' FROM t0_template
319
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
320
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
322
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
323
f_charbig = 'updated by trigger'
324
WHERE f_int1 = new.f_int1;
326
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
327
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
329
# check trigger-5 success: 1
331
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
332
f_int2 = CAST(f_char1 AS SIGNED INT),
333
f_charbig = 'just inserted'
334
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
336
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
337
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
338
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
339
'just inserted' FROM t0_template
340
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
341
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
343
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
344
f_charbig = 'updated by trigger'
345
WHERE f_int1 = - old.f_int1;
347
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
348
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
350
# check trigger-6 success: 1
352
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
353
f_int2 = CAST(f_char1 AS SIGNED INT),
354
f_charbig = 'just inserted'
355
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
357
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
358
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
359
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
360
'just inserted' FROM t0_template
361
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
362
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
364
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
365
f_charbig = 'updated by trigger'
366
WHERE f_int1 = - old.f_int1;
369
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
371
# check trigger-7 success: 1
373
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
374
f_int2 = CAST(f_char1 AS SIGNED INT),
375
f_charbig = 'just inserted'
376
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
378
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
379
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
380
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
381
'just inserted' FROM t0_template
382
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
383
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
385
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
386
f_charbig = 'updated by trigger'
387
WHERE f_int1 = - old.f_int1;
390
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
392
# check trigger-8 success: 1
394
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
395
f_int2 = CAST(f_char1 AS SIGNED INT),
396
f_charbig = 'just inserted'
397
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
399
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
401
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
402
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
404
SET new.f_int1 = old.f_int1 + @max_row,
405
new.f_int2 = old.f_int2 - @max_row,
406
new.f_charbig = '####updated per update trigger####';
409
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
410
f_charbig = '####updated per update statement itself####';
412
# check trigger-9 success: 1
414
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
415
f_int2 = CAST(f_char1 AS SIGNED INT),
416
f_charbig = CONCAT('===',f_char1,'===');
417
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
419
SET new.f_int1 = new.f_int1 + @max_row,
420
new.f_int2 = new.f_int2 - @max_row,
421
new.f_charbig = '####updated per update trigger####';
424
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
425
f_charbig = '####updated per update statement itself####';
427
# check trigger-10 success: 1
429
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
430
f_int2 = CAST(f_char1 AS SIGNED INT),
431
f_charbig = CONCAT('===',f_char1,'===');
432
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
434
SET new.f_int1 = @my_max1 + @counter,
435
new.f_int2 = @my_min2 - @counter,
436
new.f_charbig = '####updated per insert trigger####';
437
SET @counter = @counter + 1;
440
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
441
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
442
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
443
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
444
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
448
# check trigger-11 success: 1
450
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
451
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
452
AND f_charbig = '####updated per insert trigger####';
453
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
455
SET new.f_int1 = @my_max1 + @counter,
456
new.f_int2 = @my_min2 - @counter,
457
new.f_charbig = '####updated per insert trigger####';
458
SET @counter = @counter + 1;
461
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
462
INSERT INTO t1 (f_char1, f_char2, f_charbig)
463
SELECT CAST(f_int1 AS CHAR),
464
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
465
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
469
# check trigger-12 success: 1
471
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
472
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
473
AND f_charbig = '####updated per insert trigger####';
475
Table Op Msg_type Msg_text
476
test.t1 analyze note The storage engine for the table doesn't support analyze
477
CHECK TABLE t1 EXTENDED;
478
Table Op Msg_type Msg_text
479
test.t1 check note The storage engine for the table doesn't support check
480
CHECKSUM TABLE t1 EXTENDED;
484
Table Op Msg_type Msg_text
485
test.t1 optimize note The storage engine for the table doesn't support optimize
486
# check layout success: 1
487
REPAIR TABLE t1 EXTENDED;
488
Table Op Msg_type Msg_text
489
test.t1 repair note The storage engine for the table doesn't support repair
490
# check layout success: 1
493
# check TRUNCATE success: 1
494
# check layout success: 1
495
# End usability test (inc/partition_check.inc)
497
#------------------------------------------------------------------------
498
# 2 Assignment of storage engine just after partition or subpartition
500
#------------------------------------------------------------------------
506
f_charbig VARCHAR(1000)
508
PARTITION BY HASH(f_int1)
509
( PARTITION part1 STORAGE ENGINE = 'InnoDB',
510
PARTITION part2 STORAGE ENGINE = 'InnoDB'
512
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
513
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
514
# Start usability test (inc/partition_check.inc)
516
SHOW CREATE TABLE t1;
518
t1 CREATE TABLE `t1` (
519
`f_int1` int(11) DEFAULT NULL,
520
`f_int2` int(11) DEFAULT NULL,
521
`f_char1` char(20) DEFAULT NULL,
522
`f_char2` char(20) DEFAULT NULL,
523
`f_charbig` varchar(1000) DEFAULT NULL
524
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB) */
526
# check prerequisites-1 success: 1
527
# check COUNT(*) success: 1
528
# check MIN/MAX(f_int1) success: 1
529
# check MIN/MAX(f_int2) success: 1
530
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
531
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
532
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
533
WHERE f_int1 IN (2,3);
534
# check prerequisites-3 success: 1
535
DELETE FROM t1 WHERE f_charbig = 'delete me';
536
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
537
# check read via f_int1 success: 1
538
# check read via f_int2 success: 1
540
# check multiple-1 success: 1
541
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
543
# check multiple-2 success: 1
544
INSERT INTO t1 SELECT * FROM t0_template
545
WHERE MOD(f_int1,3) = 0;
547
# check multiple-3 success: 1
548
UPDATE t1 SET f_int1 = f_int1 + @max_row
549
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
550
AND @max_row_div2 + @max_row_div4;
552
# check multiple-4 success: 1
554
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
555
AND @max_row_div2 + @max_row_div4 + @max_row;
557
# check multiple-5 success: 1
558
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
560
SET f_int1 = @cur_value , f_int2 = @cur_value,
561
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
562
f_charbig = '#SINGLE#';
564
# check single-1 success: 1
565
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
567
SET f_int1 = @cur_value , f_int2 = @cur_value,
568
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
569
f_charbig = '#SINGLE#';
571
# check single-2 success: 1
572
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
573
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
574
UPDATE t1 SET f_int1 = @cur_value2
575
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
577
# check single-3 success: 1
579
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
580
UPDATE t1 SET f_int1 = @cur_value1
581
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
583
# check single-4 success: 1
584
SELECT MAX(f_int1) INTO @cur_value FROM t1;
585
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
587
# check single-5 success: 1
588
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
590
# check single-6 success: 1
591
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
593
# check single-7 success: 1
594
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
595
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
596
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
597
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
598
f_charbig = '#NULL#';
600
SET f_int1 = NULL , f_int2 = -@max_row,
601
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
602
f_charbig = '#NULL#';
603
# check null success: 1
605
# check null-1 success: 1
606
UPDATE t1 SET f_int1 = -@max_row
607
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
608
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
610
# check null-2 success: 1
611
UPDATE t1 SET f_int1 = NULL
612
WHERE f_int1 = -@max_row 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-3 success: 1
617
WHERE f_int1 IS NULL 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-4 success: 1
622
WHERE f_int1 = 0 AND f_int2 = 0
623
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
624
AND f_charbig = '#NULL#';
626
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
627
SELECT f_int1, f_int1, '', '', 'was inserted'
628
FROM t0_template source_tab
629
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
631
# check transactions-1 success: 1
634
# check transactions-2 success: 1
637
# check transactions-3 success: 1
638
DELETE FROM t1 WHERE f_charbig = 'was inserted';
642
# check transactions-4 success: 1
643
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
644
SELECT f_int1, f_int1, '', '', 'was inserted'
645
FROM t0_template source_tab
646
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
648
# check transactions-5 success: 1
651
# check transactions-6 success: 1
652
# INFO: Storage engine used for t1 seems to be transactional.
655
# check transactions-7 success: 1
656
DELETE FROM t1 WHERE f_charbig = 'was inserted';
658
SET @@session.sql_mode = 'traditional';
659
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
660
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
661
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
662
'', '', 'was inserted' FROM t0_template
663
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
664
ERROR 22012: Division by 0
667
# check transactions-8 success: 1
668
# INFO: Storage engine used for t1 seems to be able to revert
669
# changes made by the failing statement.
670
SET @@session.sql_mode = '';
672
DELETE FROM t1 WHERE f_charbig = 'was inserted';
674
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
676
# check special-1 success: 1
677
UPDATE t1 SET f_charbig = '';
679
# check special-2 success: 1
680
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
681
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
682
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
683
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
684
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
685
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
686
'just inserted' FROM t0_template
687
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
688
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
690
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
691
f_charbig = 'updated by trigger'
692
WHERE f_int1 = new.f_int1;
694
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
695
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
696
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
698
# check trigger-1 success: 1
700
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
701
f_int2 = CAST(f_char1 AS SIGNED INT),
702
f_charbig = 'just inserted'
703
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
705
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
706
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
707
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
708
'just inserted' FROM t0_template
709
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
710
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
712
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
713
f_charbig = 'updated by trigger'
714
WHERE f_int1 = new.f_int1;
716
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
717
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
718
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
720
# check trigger-2 success: 1
722
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
723
f_int2 = CAST(f_char1 AS SIGNED INT),
724
f_charbig = 'just inserted'
725
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
727
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
728
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
729
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
730
'just inserted' FROM t0_template
731
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
732
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
734
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
735
f_charbig = 'updated by trigger'
736
WHERE f_int1 = new.f_int1;
738
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
739
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
741
# check trigger-3 success: 1
743
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
744
f_int2 = CAST(f_char1 AS SIGNED INT),
745
f_charbig = 'just inserted'
746
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
748
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
749
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
750
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
751
'just inserted' FROM t0_template
752
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
753
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
755
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
756
f_charbig = 'updated by trigger'
757
WHERE f_int1 = - old.f_int1;
759
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
760
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
762
# check trigger-4 success: 1
764
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
765
f_int2 = CAST(f_char1 AS SIGNED INT),
766
f_charbig = 'just inserted'
767
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
769
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
770
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
771
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
772
'just inserted' FROM t0_template
773
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
774
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
776
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
777
f_charbig = 'updated by trigger'
778
WHERE f_int1 = new.f_int1;
780
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
781
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
783
# check trigger-5 success: 1
785
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
786
f_int2 = CAST(f_char1 AS SIGNED INT),
787
f_charbig = 'just inserted'
788
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
790
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
791
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
792
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
793
'just inserted' FROM t0_template
794
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
795
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
797
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
798
f_charbig = 'updated by trigger'
799
WHERE f_int1 = - old.f_int1;
801
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
802
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
804
# check trigger-6 success: 1
806
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
807
f_int2 = CAST(f_char1 AS SIGNED INT),
808
f_charbig = 'just inserted'
809
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
811
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
812
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
813
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
814
'just inserted' FROM t0_template
815
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
816
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
818
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
819
f_charbig = 'updated by trigger'
820
WHERE f_int1 = - old.f_int1;
823
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
825
# check trigger-7 success: 1
827
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
828
f_int2 = CAST(f_char1 AS SIGNED INT),
829
f_charbig = 'just inserted'
830
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
832
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
833
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
834
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
835
'just inserted' FROM t0_template
836
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
837
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
839
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
840
f_charbig = 'updated by trigger'
841
WHERE f_int1 = - old.f_int1;
844
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
846
# check trigger-8 success: 1
848
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
849
f_int2 = CAST(f_char1 AS SIGNED INT),
850
f_charbig = 'just inserted'
851
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
853
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
855
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
856
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
858
SET new.f_int1 = old.f_int1 + @max_row,
859
new.f_int2 = old.f_int2 - @max_row,
860
new.f_charbig = '####updated per update trigger####';
863
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
864
f_charbig = '####updated per update statement itself####';
866
# check trigger-9 success: 1
868
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
869
f_int2 = CAST(f_char1 AS SIGNED INT),
870
f_charbig = CONCAT('===',f_char1,'===');
871
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
873
SET new.f_int1 = new.f_int1 + @max_row,
874
new.f_int2 = new.f_int2 - @max_row,
875
new.f_charbig = '####updated per update trigger####';
878
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
879
f_charbig = '####updated per update statement itself####';
881
# check trigger-10 success: 1
883
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
884
f_int2 = CAST(f_char1 AS SIGNED INT),
885
f_charbig = CONCAT('===',f_char1,'===');
886
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
888
SET new.f_int1 = @my_max1 + @counter,
889
new.f_int2 = @my_min2 - @counter,
890
new.f_charbig = '####updated per insert trigger####';
891
SET @counter = @counter + 1;
894
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
895
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
896
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
897
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
898
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
902
# check trigger-11 success: 1
904
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
905
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
906
AND f_charbig = '####updated per insert trigger####';
907
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
909
SET new.f_int1 = @my_max1 + @counter,
910
new.f_int2 = @my_min2 - @counter,
911
new.f_charbig = '####updated per insert trigger####';
912
SET @counter = @counter + 1;
915
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
916
INSERT INTO t1 (f_char1, f_char2, f_charbig)
917
SELECT CAST(f_int1 AS CHAR),
918
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
919
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
923
# check trigger-12 success: 1
925
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
926
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
927
AND f_charbig = '####updated per insert trigger####';
929
Table Op Msg_type Msg_text
930
test.t1 analyze note The storage engine for the table doesn't support analyze
931
CHECK TABLE t1 EXTENDED;
932
Table Op Msg_type Msg_text
933
test.t1 check note The storage engine for the table doesn't support check
934
CHECKSUM TABLE t1 EXTENDED;
938
Table Op Msg_type Msg_text
939
test.t1 optimize note The storage engine for the table doesn't support optimize
940
# check layout success: 1
941
REPAIR TABLE t1 EXTENDED;
942
Table Op Msg_type Msg_text
943
test.t1 repair note The storage engine for the table doesn't support repair
944
# check layout success: 1
947
# check TRUNCATE success: 1
948
# check layout success: 1
949
# End usability test (inc/partition_check.inc)
956
f_charbig VARCHAR(1000)
958
PARTITION BY RANGE(f_int1)
959
SUBPARTITION BY HASH(f_int1)
960
( PARTITION part1 VALUES LESS THAN (10)
961
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
962
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
963
PARTITION part2 VALUES LESS THAN (2147483646)
964
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
965
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
967
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
968
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
969
# Start usability test (inc/partition_check.inc)
971
SHOW CREATE TABLE t1;
973
t1 CREATE TABLE `t1` (
974
`f_int1` int(11) DEFAULT NULL,
975
`f_int2` int(11) DEFAULT NULL,
976
`f_char1` char(20) DEFAULT NULL,
977
`f_char2` char(20) DEFAULT NULL,
978
`f_charbig` varchar(1000) DEFAULT NULL
979
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE = InnoDB)) */
981
# check prerequisites-1 success: 1
982
# check COUNT(*) success: 1
983
# check MIN/MAX(f_int1) success: 1
984
# check MIN/MAX(f_int2) success: 1
985
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
986
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
987
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
988
WHERE f_int1 IN (2,3);
989
# check prerequisites-3 success: 1
990
DELETE FROM t1 WHERE f_charbig = 'delete me';
991
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
992
# check read via f_int1 success: 1
993
# check read via f_int2 success: 1
995
# check multiple-1 success: 1
996
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
998
# check multiple-2 success: 1
999
INSERT INTO t1 SELECT * FROM t0_template
1000
WHERE MOD(f_int1,3) = 0;
1002
# check multiple-3 success: 1
1003
UPDATE t1 SET f_int1 = f_int1 + @max_row
1004
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
1005
AND @max_row_div2 + @max_row_div4;
1007
# check multiple-4 success: 1
1009
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
1010
AND @max_row_div2 + @max_row_div4 + @max_row;
1012
# check multiple-5 success: 1
1013
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
1015
SET f_int1 = @cur_value , f_int2 = @cur_value,
1016
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1017
f_charbig = '#SINGLE#';
1019
# check single-1 success: 1
1020
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
1022
SET f_int1 = @cur_value , f_int2 = @cur_value,
1023
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1024
f_charbig = '#SINGLE#';
1026
# check single-2 success: 1
1027
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
1028
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
1029
UPDATE t1 SET f_int1 = @cur_value2
1030
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
1032
# check single-3 success: 1
1033
SET @cur_value1= -1;
1034
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
1035
UPDATE t1 SET f_int1 = @cur_value1
1036
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
1038
# check single-4 success: 1
1039
SELECT MAX(f_int1) INTO @cur_value FROM t1;
1040
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
1042
# check single-5 success: 1
1043
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
1045
# check single-6 success: 1
1046
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
1047
ERROR HY000: Table has no partition for value 2147483647
1048
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
1049
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
1050
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
1051
f_charbig = '#NULL#';
1053
SET f_int1 = NULL , f_int2 = -@max_row,
1054
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
1055
f_charbig = '#NULL#';
1056
# check null success: 1
1058
# check null-1 success: 1
1059
UPDATE t1 SET f_int1 = -@max_row
1060
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1061
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1063
# check null-2 success: 1
1064
UPDATE t1 SET f_int1 = NULL
1065
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1066
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1068
# check null-3 success: 1
1070
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1071
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1073
# check null-4 success: 1
1075
WHERE f_int1 = 0 AND f_int2 = 0
1076
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
1077
AND f_charbig = '#NULL#';
1079
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1080
SELECT f_int1, f_int1, '', '', 'was inserted'
1081
FROM t0_template source_tab
1082
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1084
# check transactions-1 success: 1
1087
# check transactions-2 success: 1
1090
# check transactions-3 success: 1
1091
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1095
# check transactions-4 success: 1
1096
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1097
SELECT f_int1, f_int1, '', '', 'was inserted'
1098
FROM t0_template source_tab
1099
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1101
# check transactions-5 success: 1
1104
# check transactions-6 success: 1
1105
# INFO: Storage engine used for t1 seems to be transactional.
1108
# check transactions-7 success: 1
1109
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1111
SET @@session.sql_mode = 'traditional';
1112
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
1113
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1114
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
1115
'', '', 'was inserted' FROM t0_template
1116
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1117
ERROR 22012: Division by 0
1120
# check transactions-8 success: 1
1121
# INFO: Storage engine used for t1 seems to be able to revert
1122
# changes made by the failing statement.
1123
SET @@session.sql_mode = '';
1125
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1127
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
1129
# check special-1 success: 1
1130
UPDATE t1 SET f_charbig = '';
1132
# check special-2 success: 1
1133
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
1134
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1135
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
1136
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1137
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1138
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1139
'just inserted' FROM t0_template
1140
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1141
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
1143
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1144
f_charbig = 'updated by trigger'
1145
WHERE f_int1 = new.f_int1;
1147
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1148
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1149
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1151
# check trigger-1 success: 1
1153
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1154
f_int2 = CAST(f_char1 AS SIGNED INT),
1155
f_charbig = 'just inserted'
1156
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1158
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1159
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1160
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1161
'just inserted' FROM t0_template
1162
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1163
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
1165
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1166
f_charbig = 'updated by trigger'
1167
WHERE f_int1 = new.f_int1;
1169
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1170
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1171
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1173
# check trigger-2 success: 1
1175
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1176
f_int2 = CAST(f_char1 AS SIGNED INT),
1177
f_charbig = 'just inserted'
1178
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1180
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1181
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1182
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1183
'just inserted' FROM t0_template
1184
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1185
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1187
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1188
f_charbig = 'updated by trigger'
1189
WHERE f_int1 = new.f_int1;
1191
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1192
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1194
# check trigger-3 success: 1
1196
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1197
f_int2 = CAST(f_char1 AS SIGNED INT),
1198
f_charbig = 'just inserted'
1199
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1201
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1202
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1203
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1204
'just inserted' FROM t0_template
1205
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1206
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1208
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1209
f_charbig = 'updated by trigger'
1210
WHERE f_int1 = - old.f_int1;
1212
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1213
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1215
# check trigger-4 success: 1
1217
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1218
f_int2 = CAST(f_char1 AS SIGNED INT),
1219
f_charbig = 'just inserted'
1220
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1222
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1223
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1224
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1225
'just inserted' FROM t0_template
1226
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1227
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1229
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1230
f_charbig = 'updated by trigger'
1231
WHERE f_int1 = new.f_int1;
1233
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1234
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1236
# check trigger-5 success: 1
1238
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1239
f_int2 = CAST(f_char1 AS SIGNED INT),
1240
f_charbig = 'just inserted'
1241
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1243
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1244
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1245
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1246
'just inserted' FROM t0_template
1247
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1248
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1250
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1251
f_charbig = 'updated by trigger'
1252
WHERE f_int1 = - old.f_int1;
1254
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1255
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1257
# check trigger-6 success: 1
1259
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1260
f_int2 = CAST(f_char1 AS SIGNED INT),
1261
f_charbig = 'just inserted'
1262
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1264
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1265
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1266
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1267
'just inserted' FROM t0_template
1268
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1269
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
1271
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1272
f_charbig = 'updated by trigger'
1273
WHERE f_int1 = - old.f_int1;
1276
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1278
# check trigger-7 success: 1
1280
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1281
f_int2 = CAST(f_char1 AS SIGNED INT),
1282
f_charbig = 'just inserted'
1283
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1285
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1286
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1287
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1288
'just inserted' FROM t0_template
1289
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1290
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
1292
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1293
f_charbig = 'updated by trigger'
1294
WHERE f_int1 = - old.f_int1;
1297
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1299
# check trigger-8 success: 1
1301
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1302
f_int2 = CAST(f_char1 AS SIGNED INT),
1303
f_charbig = 'just inserted'
1304
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1306
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1308
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1309
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1311
SET new.f_int1 = old.f_int1 + @max_row,
1312
new.f_int2 = old.f_int2 - @max_row,
1313
new.f_charbig = '####updated per update trigger####';
1316
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1317
f_charbig = '####updated per update statement itself####';
1319
# check trigger-9 success: 1
1321
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1322
f_int2 = CAST(f_char1 AS SIGNED INT),
1323
f_charbig = CONCAT('===',f_char1,'===');
1324
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1326
SET new.f_int1 = new.f_int1 + @max_row,
1327
new.f_int2 = new.f_int2 - @max_row,
1328
new.f_charbig = '####updated per update trigger####';
1331
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1332
f_charbig = '####updated per update statement itself####';
1334
# check trigger-10 success: 1
1336
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1337
f_int2 = CAST(f_char1 AS SIGNED INT),
1338
f_charbig = CONCAT('===',f_char1,'===');
1339
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1341
SET new.f_int1 = @my_max1 + @counter,
1342
new.f_int2 = @my_min2 - @counter,
1343
new.f_charbig = '####updated per insert trigger####';
1344
SET @counter = @counter + 1;
1347
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1348
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1349
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1350
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1351
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1355
# check trigger-11 success: 1
1357
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1358
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1359
AND f_charbig = '####updated per insert trigger####';
1360
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1362
SET new.f_int1 = @my_max1 + @counter,
1363
new.f_int2 = @my_min2 - @counter,
1364
new.f_charbig = '####updated per insert trigger####';
1365
SET @counter = @counter + 1;
1368
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1369
INSERT INTO t1 (f_char1, f_char2, f_charbig)
1370
SELECT CAST(f_int1 AS CHAR),
1371
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1372
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1376
# check trigger-12 success: 1
1378
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1379
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1380
AND f_charbig = '####updated per insert trigger####';
1382
Table Op Msg_type Msg_text
1383
test.t1 analyze note The storage engine for the table doesn't support analyze
1384
CHECK TABLE t1 EXTENDED;
1385
Table Op Msg_type Msg_text
1386
test.t1 check note The storage engine for the table doesn't support check
1387
CHECKSUM TABLE t1 EXTENDED;
1389
test.t1 <some_value>
1391
Table Op Msg_type Msg_text
1392
test.t1 optimize note The storage engine for the table doesn't support optimize
1393
# check layout success: 1
1394
REPAIR TABLE t1 EXTENDED;
1395
Table Op Msg_type Msg_text
1396
test.t1 repair note The storage engine for the table doesn't support repair
1397
# check layout success: 1
1400
# check TRUNCATE success: 1
1401
# check layout success: 1
1402
# End usability test (inc/partition_check.inc)
1404
#------------------------------------------------------------------------
1405
# 3 Some but not all named partitions or subpartitions get a storage
1407
#------------------------------------------------------------------------
1413
f_charbig VARCHAR(1000)
1415
PARTITION BY HASH(f_int1)
1416
( PARTITION part1 STORAGE ENGINE = 'InnoDB',
1419
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1425
f_charbig VARCHAR(1000)
1427
PARTITION BY HASH(f_int1)
1429
PARTITION part2 STORAGE ENGINE = 'InnoDB'
1431
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1437
f_charbig VARCHAR(1000)
1439
PARTITION BY RANGE(f_int1)
1440
SUBPARTITION BY HASH(f_int1)
1441
( PARTITION part1 VALUES LESS THAN (10)
1442
(SUBPARTITION subpart11,
1443
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
1444
PARTITION part2 VALUES LESS THAN (2147483646)
1445
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
1446
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
1448
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1454
f_charbig VARCHAR(1000)
1456
PARTITION BY RANGE(f_int1)
1457
SUBPARTITION BY HASH(f_int1)
1458
( PARTITION part1 VALUES LESS THAN (10)
1459
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
1460
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
1461
PARTITION part2 VALUES LESS THAN (2147483646)
1462
(SUBPARTITION subpart21,
1463
SUBPARTITION subpart22 )
1465
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1471
f_charbig VARCHAR(1000)
1474
PARTITION BY RANGE(f_int1)
1475
SUBPARTITION BY HASH(f_int1)
1476
( PARTITION part1 VALUES LESS THAN (10)
1477
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
1478
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
1479
PARTITION part2 VALUES LESS THAN (2147483646)
1480
(SUBPARTITION subpart21,
1481
SUBPARTITION subpart22 )
1483
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1484
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
1485
# Start usability test (inc/partition_check.inc)
1487
SHOW CREATE TABLE t1;
1489
t1 CREATE TABLE `t1` (
1490
`f_int1` int(11) DEFAULT NULL,
1491
`f_int2` int(11) DEFAULT NULL,
1492
`f_char1` char(20) DEFAULT NULL,
1493
`f_char2` char(20) DEFAULT NULL,
1494
`f_charbig` varchar(1000) DEFAULT NULL
1495
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE = InnoDB)) */
1497
# check prerequisites-1 success: 1
1498
# check COUNT(*) success: 1
1499
# check MIN/MAX(f_int1) success: 1
1500
# check MIN/MAX(f_int2) success: 1
1501
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1502
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1503
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
1504
WHERE f_int1 IN (2,3);
1505
# check prerequisites-3 success: 1
1506
DELETE FROM t1 WHERE f_charbig = 'delete me';
1507
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
1508
# check read via f_int1 success: 1
1509
# check read via f_int2 success: 1
1511
# check multiple-1 success: 1
1512
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
1514
# check multiple-2 success: 1
1515
INSERT INTO t1 SELECT * FROM t0_template
1516
WHERE MOD(f_int1,3) = 0;
1518
# check multiple-3 success: 1
1519
UPDATE t1 SET f_int1 = f_int1 + @max_row
1520
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
1521
AND @max_row_div2 + @max_row_div4;
1523
# check multiple-4 success: 1
1525
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
1526
AND @max_row_div2 + @max_row_div4 + @max_row;
1528
# check multiple-5 success: 1
1529
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
1531
SET f_int1 = @cur_value , f_int2 = @cur_value,
1532
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1533
f_charbig = '#SINGLE#';
1535
# check single-1 success: 1
1536
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
1538
SET f_int1 = @cur_value , f_int2 = @cur_value,
1539
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1540
f_charbig = '#SINGLE#';
1542
# check single-2 success: 1
1543
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
1544
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
1545
UPDATE t1 SET f_int1 = @cur_value2
1546
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
1548
# check single-3 success: 1
1549
SET @cur_value1= -1;
1550
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
1551
UPDATE t1 SET f_int1 = @cur_value1
1552
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
1554
# check single-4 success: 1
1555
SELECT MAX(f_int1) INTO @cur_value FROM t1;
1556
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
1558
# check single-5 success: 1
1559
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
1561
# check single-6 success: 1
1562
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
1563
ERROR HY000: Table has no partition for value 2147483647
1564
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
1565
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
1566
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
1567
f_charbig = '#NULL#';
1569
SET f_int1 = NULL , f_int2 = -@max_row,
1570
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
1571
f_charbig = '#NULL#';
1572
# check null success: 1
1574
# check null-1 success: 1
1575
UPDATE t1 SET f_int1 = -@max_row
1576
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1577
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1579
# check null-2 success: 1
1580
UPDATE t1 SET f_int1 = NULL
1581
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1582
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1584
# check null-3 success: 1
1586
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1587
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1589
# check null-4 success: 1
1591
WHERE f_int1 = 0 AND f_int2 = 0
1592
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
1593
AND f_charbig = '#NULL#';
1595
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1596
SELECT f_int1, f_int1, '', '', 'was inserted'
1597
FROM t0_template source_tab
1598
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1600
# check transactions-1 success: 1
1603
# check transactions-2 success: 1
1606
# check transactions-3 success: 1
1607
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1611
# check transactions-4 success: 1
1612
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1613
SELECT f_int1, f_int1, '', '', 'was inserted'
1614
FROM t0_template source_tab
1615
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1617
# check transactions-5 success: 1
1620
# check transactions-6 success: 1
1621
# INFO: Storage engine used for t1 seems to be transactional.
1624
# check transactions-7 success: 1
1625
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1627
SET @@session.sql_mode = 'traditional';
1628
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
1629
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1630
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
1631
'', '', 'was inserted' FROM t0_template
1632
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1633
ERROR 22012: Division by 0
1636
# check transactions-8 success: 1
1637
# INFO: Storage engine used for t1 seems to be able to revert
1638
# changes made by the failing statement.
1639
SET @@session.sql_mode = '';
1641
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1643
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
1645
# check special-1 success: 1
1646
UPDATE t1 SET f_charbig = '';
1648
# check special-2 success: 1
1649
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
1650
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1651
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
1652
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1653
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1654
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1655
'just inserted' FROM t0_template
1656
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1657
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
1659
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1660
f_charbig = 'updated by trigger'
1661
WHERE f_int1 = new.f_int1;
1663
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1664
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1665
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1667
# check trigger-1 success: 1
1669
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1670
f_int2 = CAST(f_char1 AS SIGNED INT),
1671
f_charbig = 'just inserted'
1672
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1674
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1675
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1676
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1677
'just inserted' FROM t0_template
1678
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1679
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
1681
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1682
f_charbig = 'updated by trigger'
1683
WHERE f_int1 = new.f_int1;
1685
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1686
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1687
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1689
# check trigger-2 success: 1
1691
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1692
f_int2 = CAST(f_char1 AS SIGNED INT),
1693
f_charbig = 'just inserted'
1694
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1696
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1697
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1698
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1699
'just inserted' FROM t0_template
1700
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1701
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1703
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1704
f_charbig = 'updated by trigger'
1705
WHERE f_int1 = new.f_int1;
1707
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1708
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1710
# check trigger-3 success: 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);
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
1724
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1725
f_charbig = 'updated by trigger'
1726
WHERE f_int1 = - old.f_int1;
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-4 success: 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);
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 AFTER UPDATE ON t0_aux FOR EACH ROW
1745
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1746
f_charbig = 'updated by trigger'
1747
WHERE f_int1 = new.f_int1;
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-5 success: 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);
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
1766
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1767
f_charbig = 'updated by trigger'
1768
WHERE f_int1 = - old.f_int1;
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-6 success: 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);
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 BEFORE DELETE ON t0_aux FOR EACH ROW
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;
1792
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1794
# check trigger-7 success: 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);
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 AFTER DELETE ON t0_aux FOR EACH ROW
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;
1813
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1815
# check trigger-8 success: 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);
1822
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1824
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1825
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1827
SET new.f_int1 = old.f_int1 + @max_row,
1828
new.f_int2 = old.f_int2 - @max_row,
1829
new.f_charbig = '####updated per update trigger####';
1832
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1833
f_charbig = '####updated per update statement itself####';
1835
# check trigger-9 success: 1
1837
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1838
f_int2 = CAST(f_char1 AS SIGNED INT),
1839
f_charbig = CONCAT('===',f_char1,'===');
1840
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1842
SET new.f_int1 = new.f_int1 + @max_row,
1843
new.f_int2 = new.f_int2 - @max_row,
1844
new.f_charbig = '####updated per update trigger####';
1847
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1848
f_charbig = '####updated per update statement itself####';
1850
# check trigger-10 success: 1
1852
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1853
f_int2 = CAST(f_char1 AS SIGNED INT),
1854
f_charbig = CONCAT('===',f_char1,'===');
1855
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1857
SET new.f_int1 = @my_max1 + @counter,
1858
new.f_int2 = @my_min2 - @counter,
1859
new.f_charbig = '####updated per insert trigger####';
1860
SET @counter = @counter + 1;
1863
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1864
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1865
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1866
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1867
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1871
# check trigger-11 success: 1
1873
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1874
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1875
AND f_charbig = '####updated per insert trigger####';
1876
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
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;
1884
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1885
INSERT INTO t1 (f_char1, f_char2, f_charbig)
1886
SELECT 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
1892
# check trigger-12 success: 1
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####';
1898
Table Op Msg_type Msg_text
1899
test.t1 analyze note The storage engine for the table doesn't support analyze
1900
CHECK TABLE t1 EXTENDED;
1901
Table Op Msg_type Msg_text
1902
test.t1 check note The storage engine for the table doesn't support check
1903
CHECKSUM TABLE t1 EXTENDED;
1905
test.t1 <some_value>
1907
Table Op Msg_type Msg_text
1908
test.t1 optimize note The storage engine for the table doesn't support optimize
1909
# check layout success: 1
1910
REPAIR TABLE t1 EXTENDED;
1911
Table Op Msg_type Msg_text
1912
test.t1 repair note The storage engine for the table doesn't support repair
1913
# check layout success: 1
1916
# check TRUNCATE success: 1
1917
# check layout success: 1
1918
# End usability test (inc/partition_check.inc)
1920
#------------------------------------------------------------------------
1921
# 4 Storage engine assignment after partition name + after name of
1922
# subpartitions belonging to another partition
1923
#------------------------------------------------------------------------
1929
f_charbig VARCHAR(1000)
1931
PARTITION BY RANGE(f_int1)
1932
SUBPARTITION BY HASH(f_int1)
1933
( PARTITION part1 VALUES LESS THAN (10)
1934
(SUBPARTITION subpart11,
1935
SUBPARTITION subpart12),
1936
PARTITION part2 VALUES LESS THAN (2147483646)
1937
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
1938
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
1940
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1946
f_charbig VARCHAR(1000)
1949
PARTITION BY RANGE(f_int1)
1950
SUBPARTITION BY HASH(f_int1)
1951
( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'InnoDB'
1952
(SUBPARTITION subpart11,
1953
SUBPARTITION subpart12),
1954
PARTITION part2 VALUES LESS THAN (2147483646)
1955
(SUBPARTITION subpart21,
1956
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
1964
f_charbig VARCHAR(1000)
1966
PARTITION BY RANGE(f_int1)
1967
SUBPARTITION BY HASH(f_int1)
1968
( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'InnoDB'
1969
(SUBPARTITION subpart11,
1970
SUBPARTITION subpart12),
1971
PARTITION part2 VALUES LESS THAN (2147483646)
1972
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
1973
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
1975
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1976
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
1977
# Start usability test (inc/partition_check.inc)
1979
SHOW CREATE TABLE t1;
1981
t1 CREATE TABLE `t1` (
1982
`f_int1` int(11) DEFAULT NULL,
1983
`f_int2` int(11) DEFAULT NULL,
1984
`f_char1` char(20) DEFAULT NULL,
1985
`f_char2` char(20) DEFAULT NULL,
1986
`f_charbig` varchar(1000) DEFAULT NULL
1987
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE = InnoDB)) */
1989
# check prerequisites-1 success: 1
1990
# check COUNT(*) success: 1
1991
# check MIN/MAX(f_int1) success: 1
1992
# check MIN/MAX(f_int2) success: 1
1993
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1994
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1995
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
1996
WHERE f_int1 IN (2,3);
1997
# check prerequisites-3 success: 1
1998
DELETE FROM t1 WHERE f_charbig = 'delete me';
1999
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2000
# check read via f_int1 success: 1
2001
# check read via f_int2 success: 1
2003
# check multiple-1 success: 1
2004
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2006
# check multiple-2 success: 1
2007
INSERT INTO t1 SELECT * FROM t0_template
2008
WHERE MOD(f_int1,3) = 0;
2010
# check multiple-3 success: 1
2011
UPDATE t1 SET f_int1 = f_int1 + @max_row
2012
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2013
AND @max_row_div2 + @max_row_div4;
2015
# check multiple-4 success: 1
2017
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2018
AND @max_row_div2 + @max_row_div4 + @max_row;
2020
# check multiple-5 success: 1
2021
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2023
SET f_int1 = @cur_value , f_int2 = @cur_value,
2024
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2025
f_charbig = '#SINGLE#';
2027
# check single-1 success: 1
2028
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2030
SET f_int1 = @cur_value , f_int2 = @cur_value,
2031
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2032
f_charbig = '#SINGLE#';
2034
# check single-2 success: 1
2035
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2036
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2037
UPDATE t1 SET f_int1 = @cur_value2
2038
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2040
# check single-3 success: 1
2041
SET @cur_value1= -1;
2042
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2043
UPDATE t1 SET f_int1 = @cur_value1
2044
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2046
# check single-4 success: 1
2047
SELECT MAX(f_int1) INTO @cur_value FROM t1;
2048
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
2050
# check single-5 success: 1
2051
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
2053
# check single-6 success: 1
2054
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
2055
ERROR HY000: Table has no partition for value 2147483647
2056
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
2057
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
2058
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
2059
f_charbig = '#NULL#';
2061
SET f_int1 = NULL , f_int2 = -@max_row,
2062
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
2063
f_charbig = '#NULL#';
2064
# check null success: 1
2066
# check null-1 success: 1
2067
UPDATE t1 SET f_int1 = -@max_row
2068
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2069
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2071
# check null-2 success: 1
2072
UPDATE t1 SET f_int1 = NULL
2073
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2074
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2076
# check null-3 success: 1
2078
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2079
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2081
# check null-4 success: 1
2083
WHERE f_int1 = 0 AND f_int2 = 0
2084
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
2085
AND f_charbig = '#NULL#';
2087
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2088
SELECT f_int1, f_int1, '', '', 'was inserted'
2089
FROM t0_template source_tab
2090
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2092
# check transactions-1 success: 1
2095
# check transactions-2 success: 1
2098
# check transactions-3 success: 1
2099
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2103
# check transactions-4 success: 1
2104
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2105
SELECT f_int1, f_int1, '', '', 'was inserted'
2106
FROM t0_template source_tab
2107
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2109
# check transactions-5 success: 1
2112
# check transactions-6 success: 1
2113
# INFO: Storage engine used for t1 seems to be transactional.
2116
# check transactions-7 success: 1
2117
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2119
SET @@session.sql_mode = 'traditional';
2120
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
2121
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2122
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
2123
'', '', 'was inserted' FROM t0_template
2124
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2125
ERROR 22012: Division by 0
2128
# check transactions-8 success: 1
2129
# INFO: Storage engine used for t1 seems to be able to revert
2130
# changes made by the failing statement.
2131
SET @@session.sql_mode = '';
2133
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2135
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
2137
# check special-1 success: 1
2138
UPDATE t1 SET f_charbig = '';
2140
# check special-2 success: 1
2141
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
2142
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2143
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
2144
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2145
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2146
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2147
'just inserted' FROM t0_template
2148
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2149
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
2151
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2152
f_charbig = 'updated by trigger'
2153
WHERE f_int1 = new.f_int1;
2155
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2156
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2157
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2159
# check trigger-1 success: 1
2161
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2162
f_int2 = CAST(f_char1 AS SIGNED INT),
2163
f_charbig = 'just inserted'
2164
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2166
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2167
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2168
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2169
'just inserted' FROM t0_template
2170
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2171
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
2173
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2174
f_charbig = 'updated by trigger'
2175
WHERE f_int1 = new.f_int1;
2177
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2178
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2179
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2181
# check trigger-2 success: 1
2183
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2184
f_int2 = CAST(f_char1 AS SIGNED INT),
2185
f_charbig = 'just inserted'
2186
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2188
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2189
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2190
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2191
'just inserted' FROM t0_template
2192
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2193
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2195
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2196
f_charbig = 'updated by trigger'
2197
WHERE f_int1 = new.f_int1;
2199
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2200
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2202
# check trigger-3 success: 1
2204
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2205
f_int2 = CAST(f_char1 AS SIGNED INT),
2206
f_charbig = 'just inserted'
2207
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2209
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2210
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2211
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2212
'just inserted' FROM t0_template
2213
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2214
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2216
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2217
f_charbig = 'updated by trigger'
2218
WHERE f_int1 = - old.f_int1;
2220
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2221
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2223
# check trigger-4 success: 1
2225
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2226
f_int2 = CAST(f_char1 AS SIGNED INT),
2227
f_charbig = 'just inserted'
2228
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2230
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2231
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2232
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2233
'just inserted' FROM t0_template
2234
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2235
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2237
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2238
f_charbig = 'updated by trigger'
2239
WHERE f_int1 = new.f_int1;
2241
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2242
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2244
# check trigger-5 success: 1
2246
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2247
f_int2 = CAST(f_char1 AS SIGNED INT),
2248
f_charbig = 'just inserted'
2249
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2251
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2252
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2253
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2254
'just inserted' FROM t0_template
2255
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2256
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2258
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2259
f_charbig = 'updated by trigger'
2260
WHERE f_int1 = - old.f_int1;
2262
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2263
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2265
# check trigger-6 success: 1
2267
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2268
f_int2 = CAST(f_char1 AS SIGNED INT),
2269
f_charbig = 'just inserted'
2270
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2272
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2273
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2274
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2275
'just inserted' FROM t0_template
2276
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2277
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
2279
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2280
f_charbig = 'updated by trigger'
2281
WHERE f_int1 = - old.f_int1;
2284
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2286
# check trigger-7 success: 1
2288
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2289
f_int2 = CAST(f_char1 AS SIGNED INT),
2290
f_charbig = 'just inserted'
2291
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2293
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2294
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2295
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2296
'just inserted' FROM t0_template
2297
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2298
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
2300
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2301
f_charbig = 'updated by trigger'
2302
WHERE f_int1 = - old.f_int1;
2305
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2307
# check trigger-8 success: 1
2309
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2310
f_int2 = CAST(f_char1 AS SIGNED INT),
2311
f_charbig = 'just inserted'
2312
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2314
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2316
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2317
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2319
SET new.f_int1 = old.f_int1 + @max_row,
2320
new.f_int2 = old.f_int2 - @max_row,
2321
new.f_charbig = '####updated per update trigger####';
2324
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2325
f_charbig = '####updated per update statement itself####';
2327
# check trigger-9 success: 1
2329
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2330
f_int2 = CAST(f_char1 AS SIGNED INT),
2331
f_charbig = CONCAT('===',f_char1,'===');
2332
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2334
SET new.f_int1 = new.f_int1 + @max_row,
2335
new.f_int2 = new.f_int2 - @max_row,
2336
new.f_charbig = '####updated per update trigger####';
2339
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2340
f_charbig = '####updated per update statement itself####';
2342
# check trigger-10 success: 1
2344
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2345
f_int2 = CAST(f_char1 AS SIGNED INT),
2346
f_charbig = CONCAT('===',f_char1,'===');
2347
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2349
SET new.f_int1 = @my_max1 + @counter,
2350
new.f_int2 = @my_min2 - @counter,
2351
new.f_charbig = '####updated per insert trigger####';
2352
SET @counter = @counter + 1;
2355
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2356
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2357
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2358
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2359
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2363
# check trigger-11 success: 1
2365
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2366
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2367
AND f_charbig = '####updated per insert trigger####';
2368
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2370
SET new.f_int1 = @my_max1 + @counter,
2371
new.f_int2 = @my_min2 - @counter,
2372
new.f_charbig = '####updated per insert trigger####';
2373
SET @counter = @counter + 1;
2376
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2377
INSERT INTO t1 (f_char1, f_char2, f_charbig)
2378
SELECT CAST(f_int1 AS CHAR),
2379
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2380
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2384
# check trigger-12 success: 1
2386
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2387
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2388
AND f_charbig = '####updated per insert trigger####';
2390
Table Op Msg_type Msg_text
2391
test.t1 analyze note The storage engine for the table doesn't support analyze
2392
CHECK TABLE t1 EXTENDED;
2393
Table Op Msg_type Msg_text
2394
test.t1 check note The storage engine for the table doesn't support check
2395
CHECKSUM TABLE t1 EXTENDED;
2397
test.t1 <some_value>
2399
Table Op Msg_type Msg_text
2400
test.t1 optimize note The storage engine for the table doesn't support optimize
2401
# check layout success: 1
2402
REPAIR TABLE t1 EXTENDED;
2403
Table Op Msg_type Msg_text
2404
test.t1 repair note The storage engine for the table doesn't support repair
2405
# check layout success: 1
2408
# check TRUNCATE success: 1
2409
# check layout success: 1
2410
# End usability test (inc/partition_check.inc)
2417
f_charbig VARCHAR(1000)
2419
PARTITION BY RANGE(f_int1)
2420
SUBPARTITION BY HASH(f_int1)
2421
( PARTITION part1 VALUES LESS THAN (10)
2422
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
2423
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
2424
PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = 'InnoDB'
2425
(SUBPARTITION subpart21 ENGINE = 'InnoDB',
2426
SUBPARTITION subpart22)
2428
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2429
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2430
# Start usability test (inc/partition_check.inc)
2432
SHOW CREATE TABLE t1;
2434
t1 CREATE TABLE `t1` (
2435
`f_int1` int(11) DEFAULT NULL,
2436
`f_int2` int(11) DEFAULT NULL,
2437
`f_char1` char(20) DEFAULT NULL,
2438
`f_char2` char(20) DEFAULT NULL,
2439
`f_charbig` varchar(1000) DEFAULT NULL
2440
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE = InnoDB)) */
2442
# check prerequisites-1 success: 1
2443
# check COUNT(*) success: 1
2444
# check MIN/MAX(f_int1) success: 1
2445
# check MIN/MAX(f_int2) success: 1
2446
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2447
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2448
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2449
WHERE f_int1 IN (2,3);
2450
# check prerequisites-3 success: 1
2451
DELETE FROM t1 WHERE f_charbig = 'delete me';
2452
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2453
# check read via f_int1 success: 1
2454
# check read via f_int2 success: 1
2456
# check multiple-1 success: 1
2457
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2459
# check multiple-2 success: 1
2460
INSERT INTO t1 SELECT * FROM t0_template
2461
WHERE MOD(f_int1,3) = 0;
2463
# check multiple-3 success: 1
2464
UPDATE t1 SET f_int1 = f_int1 + @max_row
2465
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2466
AND @max_row_div2 + @max_row_div4;
2468
# check multiple-4 success: 1
2470
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2471
AND @max_row_div2 + @max_row_div4 + @max_row;
2473
# check multiple-5 success: 1
2474
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2476
SET f_int1 = @cur_value , f_int2 = @cur_value,
2477
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2478
f_charbig = '#SINGLE#';
2480
# check single-1 success: 1
2481
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2483
SET f_int1 = @cur_value , f_int2 = @cur_value,
2484
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2485
f_charbig = '#SINGLE#';
2487
# check single-2 success: 1
2488
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2489
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2490
UPDATE t1 SET f_int1 = @cur_value2
2491
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2493
# check single-3 success: 1
2494
SET @cur_value1= -1;
2495
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2496
UPDATE t1 SET f_int1 = @cur_value1
2497
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2499
# check single-4 success: 1
2500
SELECT MAX(f_int1) INTO @cur_value FROM t1;
2501
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
2503
# check single-5 success: 1
2504
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
2506
# check single-6 success: 1
2507
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
2508
ERROR HY000: Table has no partition for value 2147483647
2509
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
2510
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
2511
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
2512
f_charbig = '#NULL#';
2514
SET f_int1 = NULL , f_int2 = -@max_row,
2515
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
2516
f_charbig = '#NULL#';
2517
# check null success: 1
2519
# check null-1 success: 1
2520
UPDATE t1 SET f_int1 = -@max_row
2521
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2522
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2524
# check null-2 success: 1
2525
UPDATE t1 SET f_int1 = NULL
2526
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2527
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2529
# check null-3 success: 1
2531
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2532
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2534
# check null-4 success: 1
2536
WHERE f_int1 = 0 AND f_int2 = 0
2537
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
2538
AND f_charbig = '#NULL#';
2540
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2541
SELECT f_int1, f_int1, '', '', 'was inserted'
2542
FROM t0_template source_tab
2543
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2545
# check transactions-1 success: 1
2548
# check transactions-2 success: 1
2551
# check transactions-3 success: 1
2552
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2556
# check transactions-4 success: 1
2557
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2558
SELECT f_int1, f_int1, '', '', 'was inserted'
2559
FROM t0_template source_tab
2560
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2562
# check transactions-5 success: 1
2565
# check transactions-6 success: 1
2566
# INFO: Storage engine used for t1 seems to be transactional.
2569
# check transactions-7 success: 1
2570
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2572
SET @@session.sql_mode = 'traditional';
2573
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
2574
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2575
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
2576
'', '', 'was inserted' FROM t0_template
2577
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2578
ERROR 22012: Division by 0
2581
# check transactions-8 success: 1
2582
# INFO: Storage engine used for t1 seems to be able to revert
2583
# changes made by the failing statement.
2584
SET @@session.sql_mode = '';
2586
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2588
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
2590
# check special-1 success: 1
2591
UPDATE t1 SET f_charbig = '';
2593
# check special-2 success: 1
2594
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
2595
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2596
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
2597
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2598
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2599
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2600
'just inserted' FROM t0_template
2601
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2602
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
2604
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2605
f_charbig = 'updated by trigger'
2606
WHERE f_int1 = new.f_int1;
2608
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2609
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2610
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2612
# check trigger-1 success: 1
2614
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2615
f_int2 = CAST(f_char1 AS SIGNED INT),
2616
f_charbig = 'just inserted'
2617
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2619
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2620
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2621
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2622
'just inserted' FROM t0_template
2623
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2624
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
2626
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2627
f_charbig = 'updated by trigger'
2628
WHERE f_int1 = new.f_int1;
2630
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2631
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2632
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2634
# check trigger-2 success: 1
2636
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2637
f_int2 = CAST(f_char1 AS SIGNED INT),
2638
f_charbig = 'just inserted'
2639
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2641
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2642
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2643
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2644
'just inserted' FROM t0_template
2645
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2646
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2648
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2649
f_charbig = 'updated by trigger'
2650
WHERE f_int1 = new.f_int1;
2652
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2653
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2655
# check trigger-3 success: 1
2657
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2658
f_int2 = CAST(f_char1 AS SIGNED INT),
2659
f_charbig = 'just inserted'
2660
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2662
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2663
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2664
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2665
'just inserted' FROM t0_template
2666
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2667
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2669
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2670
f_charbig = 'updated by trigger'
2671
WHERE f_int1 = - old.f_int1;
2673
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2674
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2676
# check trigger-4 success: 1
2678
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2679
f_int2 = CAST(f_char1 AS SIGNED INT),
2680
f_charbig = 'just inserted'
2681
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2683
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2684
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2685
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2686
'just inserted' FROM t0_template
2687
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2688
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2690
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2691
f_charbig = 'updated by trigger'
2692
WHERE f_int1 = new.f_int1;
2694
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2695
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2697
# check trigger-5 success: 1
2699
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2700
f_int2 = CAST(f_char1 AS SIGNED INT),
2701
f_charbig = 'just inserted'
2702
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2704
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2705
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2706
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2707
'just inserted' FROM t0_template
2708
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2709
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2711
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2712
f_charbig = 'updated by trigger'
2713
WHERE f_int1 = - old.f_int1;
2715
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2716
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2718
# check trigger-6 success: 1
2720
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2721
f_int2 = CAST(f_char1 AS SIGNED INT),
2722
f_charbig = 'just inserted'
2723
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2725
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2726
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2727
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2728
'just inserted' FROM t0_template
2729
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2730
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
2732
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2733
f_charbig = 'updated by trigger'
2734
WHERE f_int1 = - old.f_int1;
2737
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2739
# check trigger-7 success: 1
2741
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2742
f_int2 = CAST(f_char1 AS SIGNED INT),
2743
f_charbig = 'just inserted'
2744
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2746
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2747
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2748
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2749
'just inserted' FROM t0_template
2750
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2751
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
2753
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2754
f_charbig = 'updated by trigger'
2755
WHERE f_int1 = - old.f_int1;
2758
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2760
# check trigger-8 success: 1
2762
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2763
f_int2 = CAST(f_char1 AS SIGNED INT),
2764
f_charbig = 'just inserted'
2765
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2767
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2769
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2770
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2772
SET new.f_int1 = old.f_int1 + @max_row,
2773
new.f_int2 = old.f_int2 - @max_row,
2774
new.f_charbig = '####updated per update trigger####';
2777
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2778
f_charbig = '####updated per update statement itself####';
2780
# check trigger-9 success: 1
2782
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2783
f_int2 = CAST(f_char1 AS SIGNED INT),
2784
f_charbig = CONCAT('===',f_char1,'===');
2785
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2787
SET new.f_int1 = new.f_int1 + @max_row,
2788
new.f_int2 = new.f_int2 - @max_row,
2789
new.f_charbig = '####updated per update trigger####';
2792
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2793
f_charbig = '####updated per update statement itself####';
2795
# check trigger-10 success: 1
2797
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2798
f_int2 = CAST(f_char1 AS SIGNED INT),
2799
f_charbig = CONCAT('===',f_char1,'===');
2800
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2802
SET new.f_int1 = @my_max1 + @counter,
2803
new.f_int2 = @my_min2 - @counter,
2804
new.f_charbig = '####updated per insert trigger####';
2805
SET @counter = @counter + 1;
2808
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2809
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2810
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2811
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2812
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2816
# check trigger-11 success: 1
2818
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2819
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2820
AND f_charbig = '####updated per insert trigger####';
2821
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2823
SET new.f_int1 = @my_max1 + @counter,
2824
new.f_int2 = @my_min2 - @counter,
2825
new.f_charbig = '####updated per insert trigger####';
2826
SET @counter = @counter + 1;
2829
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2830
INSERT INTO t1 (f_char1, f_char2, f_charbig)
2831
SELECT CAST(f_int1 AS CHAR),
2832
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2833
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2837
# check trigger-12 success: 1
2839
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2840
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2841
AND f_charbig = '####updated per insert trigger####';
2843
Table Op Msg_type Msg_text
2844
test.t1 analyze note The storage engine for the table doesn't support analyze
2845
CHECK TABLE t1 EXTENDED;
2846
Table Op Msg_type Msg_text
2847
test.t1 check note The storage engine for the table doesn't support check
2848
CHECKSUM TABLE t1 EXTENDED;
2850
test.t1 <some_value>
2852
Table Op Msg_type Msg_text
2853
test.t1 optimize note The storage engine for the table doesn't support optimize
2854
# check layout success: 1
2855
REPAIR TABLE t1 EXTENDED;
2856
Table Op Msg_type Msg_text
2857
test.t1 repair note The storage engine for the table doesn't support repair
2858
# check layout success: 1
2861
# check TRUNCATE success: 1
2862
# check layout success: 1
2863
# End usability test (inc/partition_check.inc)
2865
#------------------------------------------------------------------------
2866
# 5 Precedence of storage engine assignments (if there is any)
2867
#------------------------------------------------------------------------
2868
# 5.1 Storage engine assignment after column list + after partition
2869
# or subpartition name
2875
f_charbig VARCHAR(1000)
2877
PARTITION BY HASH(f_int1)
2878
( PARTITION part1 STORAGE ENGINE = 'InnoDB',
2879
PARTITION part2 STORAGE ENGINE = 'InnoDB'
2881
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2882
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2883
# Start usability test (inc/partition_check.inc)
2885
SHOW CREATE TABLE t1;
2887
t1 CREATE TABLE `t1` (
2888
`f_int1` int(11) DEFAULT NULL,
2889
`f_int2` int(11) DEFAULT NULL,
2890
`f_char1` char(20) DEFAULT NULL,
2891
`f_char2` char(20) DEFAULT NULL,
2892
`f_charbig` varchar(1000) DEFAULT NULL
2893
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = InnoDB, PARTITION part2 ENGINE = InnoDB) */
2895
# check prerequisites-1 success: 1
2896
# check COUNT(*) success: 1
2897
# check MIN/MAX(f_int1) success: 1
2898
# check MIN/MAX(f_int2) success: 1
2899
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2900
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2901
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2902
WHERE f_int1 IN (2,3);
2903
# check prerequisites-3 success: 1
2904
DELETE FROM t1 WHERE f_charbig = 'delete me';
2905
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2906
# check read via f_int1 success: 1
2907
# check read via f_int2 success: 1
2909
# check multiple-1 success: 1
2910
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2912
# check multiple-2 success: 1
2913
INSERT INTO t1 SELECT * FROM t0_template
2914
WHERE MOD(f_int1,3) = 0;
2916
# check multiple-3 success: 1
2917
UPDATE t1 SET f_int1 = f_int1 + @max_row
2918
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2919
AND @max_row_div2 + @max_row_div4;
2921
# check multiple-4 success: 1
2923
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2924
AND @max_row_div2 + @max_row_div4 + @max_row;
2926
# check multiple-5 success: 1
2927
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2929
SET f_int1 = @cur_value , f_int2 = @cur_value,
2930
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2931
f_charbig = '#SINGLE#';
2933
# check single-1 success: 1
2934
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2936
SET f_int1 = @cur_value , f_int2 = @cur_value,
2937
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2938
f_charbig = '#SINGLE#';
2940
# check single-2 success: 1
2941
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2942
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2943
UPDATE t1 SET f_int1 = @cur_value2
2944
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2946
# check single-3 success: 1
2947
SET @cur_value1= -1;
2948
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2949
UPDATE t1 SET f_int1 = @cur_value1
2950
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2952
# check single-4 success: 1
2953
SELECT MAX(f_int1) INTO @cur_value FROM t1;
2954
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
2956
# check single-5 success: 1
2957
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
2959
# check single-6 success: 1
2960
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
2962
# check single-7 success: 1
2963
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
2964
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
2965
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
2966
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
2967
f_charbig = '#NULL#';
2969
SET f_int1 = NULL , f_int2 = -@max_row,
2970
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
2971
f_charbig = '#NULL#';
2972
# check null success: 1
2974
# check null-1 success: 1
2975
UPDATE t1 SET f_int1 = -@max_row
2976
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2977
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2979
# check null-2 success: 1
2980
UPDATE t1 SET f_int1 = NULL
2981
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2982
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2984
# check null-3 success: 1
2986
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2987
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2989
# check null-4 success: 1
2991
WHERE f_int1 = 0 AND f_int2 = 0
2992
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
2993
AND f_charbig = '#NULL#';
2995
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2996
SELECT f_int1, f_int1, '', '', 'was inserted'
2997
FROM t0_template source_tab
2998
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3000
# check transactions-1 success: 1
3003
# check transactions-2 success: 1
3006
# check transactions-3 success: 1
3007
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3011
# check transactions-4 success: 1
3012
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3013
SELECT f_int1, f_int1, '', '', 'was inserted'
3014
FROM t0_template source_tab
3015
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3017
# check transactions-5 success: 1
3020
# check transactions-6 success: 1
3021
# INFO: Storage engine used for t1 seems to be transactional.
3024
# check transactions-7 success: 1
3025
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3027
SET @@session.sql_mode = 'traditional';
3028
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
3029
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3030
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
3031
'', '', 'was inserted' FROM t0_template
3032
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3033
ERROR 22012: Division by 0
3036
# check transactions-8 success: 1
3037
# INFO: Storage engine used for t1 seems to be able to revert
3038
# changes made by the failing statement.
3039
SET @@session.sql_mode = '';
3041
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3043
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
3045
# check special-1 success: 1
3046
UPDATE t1 SET f_charbig = '';
3048
# check special-2 success: 1
3049
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
3050
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3051
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
3052
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3053
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3054
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3055
'just inserted' FROM t0_template
3056
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3057
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
3059
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3060
f_charbig = 'updated by trigger'
3061
WHERE f_int1 = new.f_int1;
3063
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3064
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3065
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3067
# check trigger-1 success: 1
3069
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3070
f_int2 = CAST(f_char1 AS SIGNED INT),
3071
f_charbig = 'just inserted'
3072
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3074
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3075
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3076
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3077
'just inserted' FROM t0_template
3078
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3079
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
3081
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3082
f_charbig = 'updated by trigger'
3083
WHERE f_int1 = new.f_int1;
3085
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3086
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3087
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3089
# check trigger-2 success: 1
3091
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3092
f_int2 = CAST(f_char1 AS SIGNED INT),
3093
f_charbig = 'just inserted'
3094
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3096
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3097
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3098
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3099
'just inserted' FROM t0_template
3100
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3101
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3103
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3104
f_charbig = 'updated by trigger'
3105
WHERE f_int1 = new.f_int1;
3107
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3108
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3110
# check trigger-3 success: 1
3112
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3113
f_int2 = CAST(f_char1 AS SIGNED INT),
3114
f_charbig = 'just inserted'
3115
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3117
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3118
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3119
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3120
'just inserted' FROM t0_template
3121
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3122
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3124
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3125
f_charbig = 'updated by trigger'
3126
WHERE f_int1 = - old.f_int1;
3128
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3129
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3131
# check trigger-4 success: 1
3133
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3134
f_int2 = CAST(f_char1 AS SIGNED INT),
3135
f_charbig = 'just inserted'
3136
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3138
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3139
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3140
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3141
'just inserted' FROM t0_template
3142
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3143
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3145
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3146
f_charbig = 'updated by trigger'
3147
WHERE f_int1 = new.f_int1;
3149
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3150
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3152
# check trigger-5 success: 1
3154
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3155
f_int2 = CAST(f_char1 AS SIGNED INT),
3156
f_charbig = 'just inserted'
3157
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3159
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3160
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3161
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3162
'just inserted' FROM t0_template
3163
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3164
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3166
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3167
f_charbig = 'updated by trigger'
3168
WHERE f_int1 = - old.f_int1;
3170
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3171
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3173
# check trigger-6 success: 1
3175
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3176
f_int2 = CAST(f_char1 AS SIGNED INT),
3177
f_charbig = 'just inserted'
3178
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3180
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3181
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3182
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3183
'just inserted' FROM t0_template
3184
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3185
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
3187
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3188
f_charbig = 'updated by trigger'
3189
WHERE f_int1 = - old.f_int1;
3192
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3194
# check trigger-7 success: 1
3196
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3197
f_int2 = CAST(f_char1 AS SIGNED INT),
3198
f_charbig = 'just inserted'
3199
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3201
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3202
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3203
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3204
'just inserted' FROM t0_template
3205
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3206
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
3208
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3209
f_charbig = 'updated by trigger'
3210
WHERE f_int1 = - old.f_int1;
3213
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3215
# check trigger-8 success: 1
3217
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3218
f_int2 = CAST(f_char1 AS SIGNED INT),
3219
f_charbig = 'just inserted'
3220
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3222
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3224
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3225
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3227
SET new.f_int1 = old.f_int1 + @max_row,
3228
new.f_int2 = old.f_int2 - @max_row,
3229
new.f_charbig = '####updated per update trigger####';
3232
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3233
f_charbig = '####updated per update statement itself####';
3235
# check trigger-9 success: 1
3237
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3238
f_int2 = CAST(f_char1 AS SIGNED INT),
3239
f_charbig = CONCAT('===',f_char1,'===');
3240
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3242
SET new.f_int1 = new.f_int1 + @max_row,
3243
new.f_int2 = new.f_int2 - @max_row,
3244
new.f_charbig = '####updated per update trigger####';
3247
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3248
f_charbig = '####updated per update statement itself####';
3250
# check trigger-10 success: 1
3252
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3253
f_int2 = CAST(f_char1 AS SIGNED INT),
3254
f_charbig = CONCAT('===',f_char1,'===');
3255
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3257
SET new.f_int1 = @my_max1 + @counter,
3258
new.f_int2 = @my_min2 - @counter,
3259
new.f_charbig = '####updated per insert trigger####';
3260
SET @counter = @counter + 1;
3263
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3264
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3265
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3266
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3267
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3271
# check trigger-11 success: 1
3273
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3274
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3275
AND f_charbig = '####updated per insert trigger####';
3276
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3278
SET new.f_int1 = @my_max1 + @counter,
3279
new.f_int2 = @my_min2 - @counter,
3280
new.f_charbig = '####updated per insert trigger####';
3281
SET @counter = @counter + 1;
3284
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3285
INSERT INTO t1 (f_char1, f_char2, f_charbig)
3286
SELECT CAST(f_int1 AS CHAR),
3287
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3288
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3292
# check trigger-12 success: 1
3294
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3295
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3296
AND f_charbig = '####updated per insert trigger####';
3298
Table Op Msg_type Msg_text
3299
test.t1 analyze note The storage engine for the table doesn't support analyze
3300
CHECK TABLE t1 EXTENDED;
3301
Table Op Msg_type Msg_text
3302
test.t1 check note The storage engine for the table doesn't support check
3303
CHECKSUM TABLE t1 EXTENDED;
3305
test.t1 <some_value>
3307
Table Op Msg_type Msg_text
3308
test.t1 optimize note The storage engine for the table doesn't support optimize
3309
# check layout success: 1
3310
REPAIR TABLE t1 EXTENDED;
3311
Table Op Msg_type Msg_text
3312
test.t1 repair note The storage engine for the table doesn't support repair
3313
# check layout success: 1
3316
# check TRUNCATE success: 1
3317
# check layout success: 1
3318
# End usability test (inc/partition_check.inc)
3325
f_charbig VARCHAR(1000)
3327
PARTITION BY RANGE(f_int1)
3328
SUBPARTITION BY HASH(f_int1)
3329
( PARTITION part1 VALUES LESS THAN (10)
3330
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
3331
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
3332
PARTITION part2 VALUES LESS THAN (2147483646)
3333
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
3334
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
3336
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3337
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
3338
# Start usability test (inc/partition_check.inc)
3340
SHOW CREATE TABLE t1;
3342
t1 CREATE TABLE `t1` (
3343
`f_int1` int(11) DEFAULT NULL,
3344
`f_int2` int(11) DEFAULT NULL,
3345
`f_char1` char(20) DEFAULT NULL,
3346
`f_char2` char(20) DEFAULT NULL,
3347
`f_charbig` varchar(1000) DEFAULT NULL
3348
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE = InnoDB)) */
3350
# check prerequisites-1 success: 1
3351
# check COUNT(*) success: 1
3352
# check MIN/MAX(f_int1) success: 1
3353
# check MIN/MAX(f_int2) success: 1
3354
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3355
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3356
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
3357
WHERE f_int1 IN (2,3);
3358
# check prerequisites-3 success: 1
3359
DELETE FROM t1 WHERE f_charbig = 'delete me';
3360
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
3361
# check read via f_int1 success: 1
3362
# check read via f_int2 success: 1
3364
# check multiple-1 success: 1
3365
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
3367
# check multiple-2 success: 1
3368
INSERT INTO t1 SELECT * FROM t0_template
3369
WHERE MOD(f_int1,3) = 0;
3371
# check multiple-3 success: 1
3372
UPDATE t1 SET f_int1 = f_int1 + @max_row
3373
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
3374
AND @max_row_div2 + @max_row_div4;
3376
# check multiple-4 success: 1
3378
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
3379
AND @max_row_div2 + @max_row_div4 + @max_row;
3381
# check multiple-5 success: 1
3382
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
3384
SET f_int1 = @cur_value , f_int2 = @cur_value,
3385
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3386
f_charbig = '#SINGLE#';
3388
# check single-1 success: 1
3389
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
3391
SET f_int1 = @cur_value , f_int2 = @cur_value,
3392
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3393
f_charbig = '#SINGLE#';
3395
# check single-2 success: 1
3396
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
3397
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
3398
UPDATE t1 SET f_int1 = @cur_value2
3399
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
3401
# check single-3 success: 1
3402
SET @cur_value1= -1;
3403
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
3404
UPDATE t1 SET f_int1 = @cur_value1
3405
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
3407
# check single-4 success: 1
3408
SELECT MAX(f_int1) INTO @cur_value FROM t1;
3409
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
3411
# check single-5 success: 1
3412
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
3414
# check single-6 success: 1
3415
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3416
ERROR HY000: Table has no partition for value 2147483647
3417
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3418
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3419
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3420
f_charbig = '#NULL#';
3422
SET f_int1 = NULL , f_int2 = -@max_row,
3423
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3424
f_charbig = '#NULL#';
3425
# check null success: 1
3427
# check null-1 success: 1
3428
UPDATE t1 SET f_int1 = -@max_row
3429
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3430
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3432
# check null-2 success: 1
3433
UPDATE t1 SET f_int1 = NULL
3434
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3435
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3437
# check null-3 success: 1
3439
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3440
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3442
# check null-4 success: 1
3444
WHERE f_int1 = 0 AND f_int2 = 0
3445
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
3446
AND f_charbig = '#NULL#';
3448
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3449
SELECT f_int1, f_int1, '', '', 'was inserted'
3450
FROM t0_template source_tab
3451
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3453
# check transactions-1 success: 1
3456
# check transactions-2 success: 1
3459
# check transactions-3 success: 1
3460
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3464
# check transactions-4 success: 1
3465
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3466
SELECT f_int1, f_int1, '', '', 'was inserted'
3467
FROM t0_template source_tab
3468
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3470
# check transactions-5 success: 1
3473
# check transactions-6 success: 1
3474
# INFO: Storage engine used for t1 seems to be transactional.
3477
# check transactions-7 success: 1
3478
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3480
SET @@session.sql_mode = 'traditional';
3481
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
3482
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3483
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
3484
'', '', 'was inserted' FROM t0_template
3485
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3486
ERROR 22012: Division by 0
3489
# check transactions-8 success: 1
3490
# INFO: Storage engine used for t1 seems to be able to revert
3491
# changes made by the failing statement.
3492
SET @@session.sql_mode = '';
3494
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3496
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
3498
# check special-1 success: 1
3499
UPDATE t1 SET f_charbig = '';
3501
# check special-2 success: 1
3502
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
3503
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3504
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
3505
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3506
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3507
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3508
'just inserted' FROM t0_template
3509
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3510
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
3512
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3513
f_charbig = 'updated by trigger'
3514
WHERE f_int1 = new.f_int1;
3516
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3517
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3518
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3520
# check trigger-1 success: 1
3522
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3523
f_int2 = CAST(f_char1 AS SIGNED INT),
3524
f_charbig = 'just inserted'
3525
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3527
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3528
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3529
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3530
'just inserted' FROM t0_template
3531
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3532
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
3534
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3535
f_charbig = 'updated by trigger'
3536
WHERE f_int1 = new.f_int1;
3538
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3539
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3540
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3542
# check trigger-2 success: 1
3544
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3545
f_int2 = CAST(f_char1 AS SIGNED INT),
3546
f_charbig = 'just inserted'
3547
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3549
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3550
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3551
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3552
'just inserted' FROM t0_template
3553
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3554
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3556
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3557
f_charbig = 'updated by trigger'
3558
WHERE f_int1 = new.f_int1;
3560
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3561
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3563
# check trigger-3 success: 1
3565
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3566
f_int2 = CAST(f_char1 AS SIGNED INT),
3567
f_charbig = 'just inserted'
3568
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3570
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3571
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3572
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3573
'just inserted' FROM t0_template
3574
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3575
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3577
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3578
f_charbig = 'updated by trigger'
3579
WHERE f_int1 = - old.f_int1;
3581
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3582
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3584
# check trigger-4 success: 1
3586
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3587
f_int2 = CAST(f_char1 AS SIGNED INT),
3588
f_charbig = 'just inserted'
3589
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3591
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3592
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3593
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3594
'just inserted' FROM t0_template
3595
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3596
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3598
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3599
f_charbig = 'updated by trigger'
3600
WHERE f_int1 = new.f_int1;
3602
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3603
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3605
# check trigger-5 success: 1
3607
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3608
f_int2 = CAST(f_char1 AS SIGNED INT),
3609
f_charbig = 'just inserted'
3610
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3612
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3613
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3614
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3615
'just inserted' FROM t0_template
3616
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3617
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3619
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3620
f_charbig = 'updated by trigger'
3621
WHERE f_int1 = - old.f_int1;
3623
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3624
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3626
# check trigger-6 success: 1
3628
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3629
f_int2 = CAST(f_char1 AS SIGNED INT),
3630
f_charbig = 'just inserted'
3631
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3633
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3634
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3635
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3636
'just inserted' FROM t0_template
3637
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3638
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
3640
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3641
f_charbig = 'updated by trigger'
3642
WHERE f_int1 = - old.f_int1;
3645
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3647
# check trigger-7 success: 1
3649
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3650
f_int2 = CAST(f_char1 AS SIGNED INT),
3651
f_charbig = 'just inserted'
3652
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3654
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3655
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3656
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3657
'just inserted' FROM t0_template
3658
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3659
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
3661
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3662
f_charbig = 'updated by trigger'
3663
WHERE f_int1 = - old.f_int1;
3666
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3668
# check trigger-8 success: 1
3670
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3671
f_int2 = CAST(f_char1 AS SIGNED INT),
3672
f_charbig = 'just inserted'
3673
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3675
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3677
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3678
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3680
SET new.f_int1 = old.f_int1 + @max_row,
3681
new.f_int2 = old.f_int2 - @max_row,
3682
new.f_charbig = '####updated per update trigger####';
3685
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3686
f_charbig = '####updated per update statement itself####';
3688
# check trigger-9 success: 1
3690
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3691
f_int2 = CAST(f_char1 AS SIGNED INT),
3692
f_charbig = CONCAT('===',f_char1,'===');
3693
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3695
SET new.f_int1 = new.f_int1 + @max_row,
3696
new.f_int2 = new.f_int2 - @max_row,
3697
new.f_charbig = '####updated per update trigger####';
3700
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3701
f_charbig = '####updated per update statement itself####';
3703
# check trigger-10 success: 1
3705
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3706
f_int2 = CAST(f_char1 AS SIGNED INT),
3707
f_charbig = CONCAT('===',f_char1,'===');
3708
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3710
SET new.f_int1 = @my_max1 + @counter,
3711
new.f_int2 = @my_min2 - @counter,
3712
new.f_charbig = '####updated per insert trigger####';
3713
SET @counter = @counter + 1;
3716
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3717
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3718
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3719
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3720
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3724
# check trigger-11 success: 1
3726
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3727
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3728
AND f_charbig = '####updated per insert trigger####';
3729
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3731
SET new.f_int1 = @my_max1 + @counter,
3732
new.f_int2 = @my_min2 - @counter,
3733
new.f_charbig = '####updated per insert trigger####';
3734
SET @counter = @counter + 1;
3737
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3738
INSERT INTO t1 (f_char1, f_char2, f_charbig)
3739
SELECT CAST(f_int1 AS CHAR),
3740
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3741
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3745
# check trigger-12 success: 1
3747
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3748
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3749
AND f_charbig = '####updated per insert trigger####';
3751
Table Op Msg_type Msg_text
3752
test.t1 analyze note The storage engine for the table doesn't support analyze
3753
CHECK TABLE t1 EXTENDED;
3754
Table Op Msg_type Msg_text
3755
test.t1 check note The storage engine for the table doesn't support check
3756
CHECKSUM TABLE t1 EXTENDED;
3758
test.t1 <some_value>
3760
Table Op Msg_type Msg_text
3761
test.t1 optimize note The storage engine for the table doesn't support optimize
3762
# check layout success: 1
3763
REPAIR TABLE t1 EXTENDED;
3764
Table Op Msg_type Msg_text
3765
test.t1 repair note The storage engine for the table doesn't support repair
3766
# check layout success: 1
3769
# check TRUNCATE success: 1
3770
# check layout success: 1
3771
# End usability test (inc/partition_check.inc)
3773
# 6.2 Storage engine assignment after partition name + after
3775
# in partition part + in sub partition part
3781
f_charbig VARCHAR(1000)
3783
PARTITION BY RANGE(f_int1)
3784
SUBPARTITION BY HASH(f_int1)
3785
( PARTITION part1 VALUES LESS THAN (10) STORAGE ENGINE = 'InnoDB'
3786
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
3787
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
3788
PARTITION part2 VALUES LESS THAN (2147483646)
3789
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
3790
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
3792
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3793
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
3794
# Start usability test (inc/partition_check.inc)
3796
SHOW CREATE TABLE t1;
3798
t1 CREATE TABLE `t1` (
3799
`f_int1` int(11) DEFAULT NULL,
3800
`f_int2` int(11) DEFAULT NULL,
3801
`f_char1` char(20) DEFAULT NULL,
3802
`f_char2` char(20) DEFAULT NULL,
3803
`f_charbig` varchar(1000) DEFAULT NULL
3804
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = InnoDB, SUBPARTITION subpart22 ENGINE = InnoDB)) */
3806
# check prerequisites-1 success: 1
3807
# check COUNT(*) success: 1
3808
# check MIN/MAX(f_int1) success: 1
3809
# check MIN/MAX(f_int2) success: 1
3810
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3811
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3812
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
3813
WHERE f_int1 IN (2,3);
3814
# check prerequisites-3 success: 1
3815
DELETE FROM t1 WHERE f_charbig = 'delete me';
3816
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
3817
# check read via f_int1 success: 1
3818
# check read via f_int2 success: 1
3820
# check multiple-1 success: 1
3821
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
3823
# check multiple-2 success: 1
3824
INSERT INTO t1 SELECT * FROM t0_template
3825
WHERE MOD(f_int1,3) = 0;
3827
# check multiple-3 success: 1
3828
UPDATE t1 SET f_int1 = f_int1 + @max_row
3829
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
3830
AND @max_row_div2 + @max_row_div4;
3832
# check multiple-4 success: 1
3834
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
3835
AND @max_row_div2 + @max_row_div4 + @max_row;
3837
# check multiple-5 success: 1
3838
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
3840
SET f_int1 = @cur_value , f_int2 = @cur_value,
3841
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3842
f_charbig = '#SINGLE#';
3844
# check single-1 success: 1
3845
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
3847
SET f_int1 = @cur_value , f_int2 = @cur_value,
3848
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3849
f_charbig = '#SINGLE#';
3851
# check single-2 success: 1
3852
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
3853
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
3854
UPDATE t1 SET f_int1 = @cur_value2
3855
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
3857
# check single-3 success: 1
3858
SET @cur_value1= -1;
3859
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
3860
UPDATE t1 SET f_int1 = @cur_value1
3861
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
3863
# check single-4 success: 1
3864
SELECT MAX(f_int1) INTO @cur_value FROM t1;
3865
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
3867
# check single-5 success: 1
3868
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
3870
# check single-6 success: 1
3871
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3872
ERROR HY000: Table has no partition for value 2147483647
3873
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3874
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3875
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3876
f_charbig = '#NULL#';
3878
SET f_int1 = NULL , f_int2 = -@max_row,
3879
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3880
f_charbig = '#NULL#';
3881
# check null success: 1
3883
# check null-1 success: 1
3884
UPDATE t1 SET f_int1 = -@max_row
3885
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3886
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3888
# check null-2 success: 1
3889
UPDATE t1 SET f_int1 = NULL
3890
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3891
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3893
# check null-3 success: 1
3895
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3896
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3898
# check null-4 success: 1
3900
WHERE f_int1 = 0 AND f_int2 = 0
3901
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
3902
AND f_charbig = '#NULL#';
3904
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3905
SELECT f_int1, f_int1, '', '', 'was inserted'
3906
FROM t0_template source_tab
3907
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3909
# check transactions-1 success: 1
3912
# check transactions-2 success: 1
3915
# check transactions-3 success: 1
3916
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3920
# check transactions-4 success: 1
3921
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3922
SELECT f_int1, f_int1, '', '', 'was inserted'
3923
FROM t0_template source_tab
3924
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3926
# check transactions-5 success: 1
3929
# check transactions-6 success: 1
3930
# INFO: Storage engine used for t1 seems to be transactional.
3933
# check transactions-7 success: 1
3934
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3936
SET @@session.sql_mode = 'traditional';
3937
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
3938
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3939
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
3940
'', '', 'was inserted' FROM t0_template
3941
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3942
ERROR 22012: Division by 0
3945
# check transactions-8 success: 1
3946
# INFO: Storage engine used for t1 seems to be able to revert
3947
# changes made by the failing statement.
3948
SET @@session.sql_mode = '';
3950
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3952
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
3954
# check special-1 success: 1
3955
UPDATE t1 SET f_charbig = '';
3957
# check special-2 success: 1
3958
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
3959
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3960
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
3961
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3962
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3963
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3964
'just inserted' FROM t0_template
3965
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3966
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
3968
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3969
f_charbig = 'updated by trigger'
3970
WHERE f_int1 = new.f_int1;
3972
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3973
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3974
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3976
# check trigger-1 success: 1
3978
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3979
f_int2 = CAST(f_char1 AS SIGNED INT),
3980
f_charbig = 'just inserted'
3981
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3983
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3984
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3985
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3986
'just inserted' FROM t0_template
3987
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3988
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
3990
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3991
f_charbig = 'updated by trigger'
3992
WHERE f_int1 = new.f_int1;
3994
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3995
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3996
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3998
# check trigger-2 success: 1
4000
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4001
f_int2 = CAST(f_char1 AS SIGNED INT),
4002
f_charbig = 'just inserted'
4003
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4005
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4006
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4007
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4008
'just inserted' FROM t0_template
4009
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4010
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4012
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4013
f_charbig = 'updated by trigger'
4014
WHERE f_int1 = new.f_int1;
4016
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4017
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4019
# check trigger-3 success: 1
4021
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4022
f_int2 = CAST(f_char1 AS SIGNED INT),
4023
f_charbig = 'just inserted'
4024
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4026
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4027
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4028
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4029
'just inserted' FROM t0_template
4030
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4031
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4033
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4034
f_charbig = 'updated by trigger'
4035
WHERE f_int1 = - old.f_int1;
4037
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4038
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4040
# check trigger-4 success: 1
4042
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4043
f_int2 = CAST(f_char1 AS SIGNED INT),
4044
f_charbig = 'just inserted'
4045
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4047
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4048
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4049
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4050
'just inserted' FROM t0_template
4051
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4052
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4054
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4055
f_charbig = 'updated by trigger'
4056
WHERE f_int1 = new.f_int1;
4058
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4059
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4061
# check trigger-5 success: 1
4063
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4064
f_int2 = CAST(f_char1 AS SIGNED INT),
4065
f_charbig = 'just inserted'
4066
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4068
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4069
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4070
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4071
'just inserted' FROM t0_template
4072
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4073
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4075
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4076
f_charbig = 'updated by trigger'
4077
WHERE f_int1 = - old.f_int1;
4079
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4080
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4082
# check trigger-6 success: 1
4084
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4085
f_int2 = CAST(f_char1 AS SIGNED INT),
4086
f_charbig = 'just inserted'
4087
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4089
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4090
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4091
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4092
'just inserted' FROM t0_template
4093
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4094
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
4096
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4097
f_charbig = 'updated by trigger'
4098
WHERE f_int1 = - old.f_int1;
4101
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4103
# check trigger-7 success: 1
4105
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4106
f_int2 = CAST(f_char1 AS SIGNED INT),
4107
f_charbig = 'just inserted'
4108
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4110
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4111
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4112
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4113
'just inserted' FROM t0_template
4114
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4115
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
4117
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4118
f_charbig = 'updated by trigger'
4119
WHERE f_int1 = - old.f_int1;
4122
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4124
# check trigger-8 success: 1
4126
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4127
f_int2 = CAST(f_char1 AS SIGNED INT),
4128
f_charbig = 'just inserted'
4129
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4131
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4133
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4134
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4136
SET new.f_int1 = old.f_int1 + @max_row,
4137
new.f_int2 = old.f_int2 - @max_row,
4138
new.f_charbig = '####updated per update trigger####';
4141
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4142
f_charbig = '####updated per update statement itself####';
4144
# check trigger-9 success: 1
4146
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4147
f_int2 = CAST(f_char1 AS SIGNED INT),
4148
f_charbig = CONCAT('===',f_char1,'===');
4149
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4151
SET new.f_int1 = new.f_int1 + @max_row,
4152
new.f_int2 = new.f_int2 - @max_row,
4153
new.f_charbig = '####updated per update trigger####';
4156
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4157
f_charbig = '####updated per update statement itself####';
4159
# check trigger-10 success: 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 = CONCAT('===',f_char1,'===');
4164
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4166
SET new.f_int1 = @my_max1 + @counter,
4167
new.f_int2 = @my_min2 - @counter,
4168
new.f_charbig = '####updated per insert trigger####';
4169
SET @counter = @counter + 1;
4172
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4173
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4174
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4175
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4176
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4180
# check trigger-11 success: 1
4182
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4183
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4184
AND f_charbig = '####updated per insert trigger####';
4185
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4187
SET new.f_int1 = @my_max1 + @counter,
4188
new.f_int2 = @my_min2 - @counter,
4189
new.f_charbig = '####updated per insert trigger####';
4190
SET @counter = @counter + 1;
4193
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4194
INSERT INTO t1 (f_char1, f_char2, f_charbig)
4195
SELECT CAST(f_int1 AS CHAR),
4196
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4197
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4201
# check trigger-12 success: 1
4203
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4204
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4205
AND f_charbig = '####updated per insert trigger####';
4207
Table Op Msg_type Msg_text
4208
test.t1 analyze note The storage engine for the table doesn't support analyze
4209
CHECK TABLE t1 EXTENDED;
4210
Table Op Msg_type Msg_text
4211
test.t1 check note The storage engine for the table doesn't support check
4212
CHECKSUM TABLE t1 EXTENDED;
4214
test.t1 <some_value>
4216
Table Op Msg_type Msg_text
4217
test.t1 optimize note The storage engine for the table doesn't support optimize
4218
# check layout success: 1
4219
REPAIR TABLE t1 EXTENDED;
4220
Table Op Msg_type Msg_text
4221
test.t1 repair note The storage engine for the table doesn't support repair
4222
# check layout success: 1
4225
# check TRUNCATE success: 1
4226
# check layout success: 1
4227
# End usability test (inc/partition_check.inc)
4229
#------------------------------------------------------------------------
4230
# 6 Session default engine differs from engine used within create table
4231
#------------------------------------------------------------------------
4232
SET SESSION storage_engine='MEMORY';
4238
f_charbig VARCHAR(1000)
4240
PARTITION BY HASH(f_int1) ( PARTITION part1 ENGINE = 'InnoDB');
4241
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4242
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
4243
# Start usability test (inc/partition_check.inc)
4245
SHOW CREATE TABLE t1;
4247
t1 CREATE TABLE `t1` (
4248
`f_int1` int(11) DEFAULT NULL,
4249
`f_int2` int(11) DEFAULT NULL,
4250
`f_char1` char(20) DEFAULT NULL,
4251
`f_char2` char(20) DEFAULT NULL,
4252
`f_charbig` varchar(1000) DEFAULT NULL
4253
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = InnoDB) */
4255
# check prerequisites-1 success: 1
4256
# check COUNT(*) success: 1
4257
# check MIN/MAX(f_int1) success: 1
4258
# check MIN/MAX(f_int2) success: 1
4259
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4260
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4261
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
4262
WHERE f_int1 IN (2,3);
4263
# check prerequisites-3 success: 1
4264
DELETE FROM t1 WHERE f_charbig = 'delete me';
4265
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
4266
# check read via f_int1 success: 1
4267
# check read via f_int2 success: 1
4269
# check multiple-1 success: 1
4270
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
4272
# check multiple-2 success: 1
4273
INSERT INTO t1 SELECT * FROM t0_template
4274
WHERE MOD(f_int1,3) = 0;
4276
# check multiple-3 success: 1
4277
UPDATE t1 SET f_int1 = f_int1 + @max_row
4278
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
4279
AND @max_row_div2 + @max_row_div4;
4281
# check multiple-4 success: 1
4283
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
4284
AND @max_row_div2 + @max_row_div4 + @max_row;
4286
# check multiple-5 success: 1
4287
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
4289
SET f_int1 = @cur_value , f_int2 = @cur_value,
4290
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4291
f_charbig = '#SINGLE#';
4293
# check single-1 success: 1
4294
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
4296
SET f_int1 = @cur_value , f_int2 = @cur_value,
4297
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4298
f_charbig = '#SINGLE#';
4300
# check single-2 success: 1
4301
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
4302
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
4303
UPDATE t1 SET f_int1 = @cur_value2
4304
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
4306
# check single-3 success: 1
4307
SET @cur_value1= -1;
4308
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
4309
UPDATE t1 SET f_int1 = @cur_value1
4310
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
4312
# check single-4 success: 1
4313
SELECT MAX(f_int1) INTO @cur_value FROM t1;
4314
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
4316
# check single-5 success: 1
4317
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
4319
# check single-6 success: 1
4320
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
4322
# check single-7 success: 1
4323
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
4324
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
4325
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
4326
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
4327
f_charbig = '#NULL#';
4329
SET f_int1 = NULL , f_int2 = -@max_row,
4330
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
4331
f_charbig = '#NULL#';
4332
# check null success: 1
4334
# check null-1 success: 1
4335
UPDATE t1 SET f_int1 = -@max_row
4336
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4337
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4339
# check null-2 success: 1
4340
UPDATE t1 SET f_int1 = NULL
4341
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4342
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4344
# check null-3 success: 1
4346
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4347
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4349
# check null-4 success: 1
4351
WHERE f_int1 = 0 AND f_int2 = 0
4352
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
4353
AND f_charbig = '#NULL#';
4355
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4356
SELECT f_int1, f_int1, '', '', 'was inserted'
4357
FROM t0_template source_tab
4358
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4360
# check transactions-1 success: 1
4363
# check transactions-2 success: 1
4366
# check transactions-3 success: 1
4367
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4371
# check transactions-4 success: 1
4372
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4373
SELECT f_int1, f_int1, '', '', 'was inserted'
4374
FROM t0_template source_tab
4375
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4377
# check transactions-5 success: 1
4380
# check transactions-6 success: 1
4381
# INFO: Storage engine used for t1 seems to be transactional.
4384
# check transactions-7 success: 1
4385
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4387
SET @@session.sql_mode = 'traditional';
4388
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
4389
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4390
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
4391
'', '', 'was inserted' FROM t0_template
4392
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4393
ERROR 22012: Division by 0
4396
# check transactions-8 success: 1
4397
# INFO: Storage engine used for t1 seems to be able to revert
4398
# changes made by the failing statement.
4399
SET @@session.sql_mode = '';
4401
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4403
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4405
# check special-1 success: 1
4406
UPDATE t1 SET f_charbig = '';
4408
# check special-2 success: 1
4409
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4410
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4411
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4412
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4413
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4414
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4415
'just inserted' FROM t0_template
4416
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4417
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
4419
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4420
f_charbig = 'updated by trigger'
4421
WHERE f_int1 = new.f_int1;
4423
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4424
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4425
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4427
# check trigger-1 success: 1
4429
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4430
f_int2 = CAST(f_char1 AS SIGNED INT),
4431
f_charbig = 'just inserted'
4432
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4434
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4435
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4436
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4437
'just inserted' FROM t0_template
4438
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4439
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
4441
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4442
f_charbig = 'updated by trigger'
4443
WHERE f_int1 = new.f_int1;
4445
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4446
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4447
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4449
# check trigger-2 success: 1
4451
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4452
f_int2 = CAST(f_char1 AS SIGNED INT),
4453
f_charbig = 'just inserted'
4454
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4456
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4457
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4458
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4459
'just inserted' FROM t0_template
4460
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4461
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4463
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4464
f_charbig = 'updated by trigger'
4465
WHERE f_int1 = new.f_int1;
4467
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4468
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4470
# check trigger-3 success: 1
4472
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4473
f_int2 = CAST(f_char1 AS SIGNED INT),
4474
f_charbig = 'just inserted'
4475
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4477
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4478
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4479
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4480
'just inserted' FROM t0_template
4481
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4482
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4484
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4485
f_charbig = 'updated by trigger'
4486
WHERE f_int1 = - old.f_int1;
4488
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4489
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4491
# check trigger-4 success: 1
4493
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4494
f_int2 = CAST(f_char1 AS SIGNED INT),
4495
f_charbig = 'just inserted'
4496
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4498
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4499
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4500
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4501
'just inserted' FROM t0_template
4502
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4503
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4505
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4506
f_charbig = 'updated by trigger'
4507
WHERE f_int1 = new.f_int1;
4509
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4510
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4512
# check trigger-5 success: 1
4514
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4515
f_int2 = CAST(f_char1 AS SIGNED INT),
4516
f_charbig = 'just inserted'
4517
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4519
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4520
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4521
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4522
'just inserted' FROM t0_template
4523
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4524
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4526
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4527
f_charbig = 'updated by trigger'
4528
WHERE f_int1 = - old.f_int1;
4530
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4531
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4533
# check trigger-6 success: 1
4535
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4536
f_int2 = CAST(f_char1 AS SIGNED INT),
4537
f_charbig = 'just inserted'
4538
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4540
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4541
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4542
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4543
'just inserted' FROM t0_template
4544
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4545
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
4547
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4548
f_charbig = 'updated by trigger'
4549
WHERE f_int1 = - old.f_int1;
4552
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4554
# check trigger-7 success: 1
4556
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4557
f_int2 = CAST(f_char1 AS SIGNED INT),
4558
f_charbig = 'just inserted'
4559
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4561
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4562
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4563
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4564
'just inserted' FROM t0_template
4565
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4566
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
4568
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4569
f_charbig = 'updated by trigger'
4570
WHERE f_int1 = - old.f_int1;
4573
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4575
# check trigger-8 success: 1
4577
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4578
f_int2 = CAST(f_char1 AS SIGNED INT),
4579
f_charbig = 'just inserted'
4580
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4582
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4584
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4585
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4587
SET new.f_int1 = old.f_int1 + @max_row,
4588
new.f_int2 = old.f_int2 - @max_row,
4589
new.f_charbig = '####updated per update trigger####';
4592
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4593
f_charbig = '####updated per update statement itself####';
4595
# check trigger-9 success: 1
4597
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4598
f_int2 = CAST(f_char1 AS SIGNED INT),
4599
f_charbig = CONCAT('===',f_char1,'===');
4600
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4602
SET new.f_int1 = new.f_int1 + @max_row,
4603
new.f_int2 = new.f_int2 - @max_row,
4604
new.f_charbig = '####updated per update trigger####';
4607
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4608
f_charbig = '####updated per update statement itself####';
4610
# check trigger-10 success: 1
4612
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4613
f_int2 = CAST(f_char1 AS SIGNED INT),
4614
f_charbig = CONCAT('===',f_char1,'===');
4615
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4617
SET new.f_int1 = @my_max1 + @counter,
4618
new.f_int2 = @my_min2 - @counter,
4619
new.f_charbig = '####updated per insert trigger####';
4620
SET @counter = @counter + 1;
4623
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4624
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4625
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4626
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4627
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4631
# check trigger-11 success: 1
4633
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4634
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4635
AND f_charbig = '####updated per insert trigger####';
4636
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4638
SET new.f_int1 = @my_max1 + @counter,
4639
new.f_int2 = @my_min2 - @counter,
4640
new.f_charbig = '####updated per insert trigger####';
4641
SET @counter = @counter + 1;
4644
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4645
INSERT INTO t1 (f_char1, f_char2, f_charbig)
4646
SELECT CAST(f_int1 AS CHAR),
4647
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4648
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4652
# check trigger-12 success: 1
4654
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4655
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4656
AND f_charbig = '####updated per insert trigger####';
4658
Table Op Msg_type Msg_text
4659
test.t1 analyze note The storage engine for the table doesn't support analyze
4660
CHECK TABLE t1 EXTENDED;
4661
Table Op Msg_type Msg_text
4662
test.t1 check note The storage engine for the table doesn't support check
4663
CHECKSUM TABLE t1 EXTENDED;
4665
test.t1 <some_value>
4667
Table Op Msg_type Msg_text
4668
test.t1 optimize note The storage engine for the table doesn't support optimize
4669
# check layout success: 1
4670
REPAIR TABLE t1 EXTENDED;
4671
Table Op Msg_type Msg_text
4672
test.t1 repair note The storage engine for the table doesn't support repair
4673
# check layout success: 1
4676
# check TRUNCATE success: 1
4677
# check layout success: 1
4678
# End usability test (inc/partition_check.inc)
4685
f_charbig VARCHAR(1000)
4687
PARTITION BY RANGE(f_int1)
4688
SUBPARTITION BY HASH(f_int1)
4689
( PARTITION part1 VALUES LESS THAN (1000)
4690
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
4691
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'));
4692
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4693
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
4694
# Start usability test (inc/partition_check.inc)
4696
SHOW CREATE TABLE t1;
4698
t1 CREATE TABLE `t1` (
4699
`f_int1` int(11) DEFAULT NULL,
4700
`f_int2` int(11) DEFAULT NULL,
4701
`f_char1` char(20) DEFAULT NULL,
4702
`f_char2` char(20) DEFAULT NULL,
4703
`f_charbig` varchar(1000) DEFAULT NULL
4704
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = InnoDB, SUBPARTITION subpart12 ENGINE = InnoDB)) */
4706
# check prerequisites-1 success: 1
4707
# check COUNT(*) success: 1
4708
# check MIN/MAX(f_int1) success: 1
4709
# check MIN/MAX(f_int2) success: 1
4710
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4711
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4712
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
4713
WHERE f_int1 IN (2,3);
4714
# check prerequisites-3 success: 1
4715
DELETE FROM t1 WHERE f_charbig = 'delete me';
4716
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
4717
# check read via f_int1 success: 1
4718
# check read via f_int2 success: 1
4720
# check multiple-1 success: 1
4721
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
4723
# check multiple-2 success: 1
4724
INSERT INTO t1 SELECT * FROM t0_template
4725
WHERE MOD(f_int1,3) = 0;
4727
# check multiple-3 success: 1
4728
UPDATE t1 SET f_int1 = f_int1 + @max_row
4729
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
4730
AND @max_row_div2 + @max_row_div4;
4732
# check multiple-4 success: 1
4734
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
4735
AND @max_row_div2 + @max_row_div4 + @max_row;
4737
# check multiple-5 success: 1
4738
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
4740
SET f_int1 = @cur_value , f_int2 = @cur_value,
4741
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4742
f_charbig = '#SINGLE#';
4744
# check single-1 success: 1
4745
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
4747
SET f_int1 = @cur_value , f_int2 = @cur_value,
4748
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4749
f_charbig = '#SINGLE#';
4751
# check single-2 success: 1
4752
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
4753
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
4754
UPDATE t1 SET f_int1 = @cur_value2
4755
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
4757
# check single-3 success: 1
4758
SET @cur_value1= -1;
4759
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
4760
UPDATE t1 SET f_int1 = @cur_value1
4761
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
4763
# check single-4 success: 1
4764
SELECT MAX(f_int1) INTO @cur_value FROM t1;
4765
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
4767
# check single-5 success: 1
4768
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
4770
# check single-6 success: 1
4771
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
4772
ERROR HY000: Table has no partition for value 2147483647
4773
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
4774
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
4775
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
4776
f_charbig = '#NULL#';
4778
SET f_int1 = NULL , f_int2 = -@max_row,
4779
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
4780
f_charbig = '#NULL#';
4781
# check null success: 1
4783
# check null-1 success: 1
4784
UPDATE t1 SET f_int1 = -@max_row
4785
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4786
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4788
# check null-2 success: 1
4789
UPDATE t1 SET f_int1 = NULL
4790
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4791
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4793
# check null-3 success: 1
4795
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4796
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4798
# check null-4 success: 1
4800
WHERE f_int1 = 0 AND f_int2 = 0
4801
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
4802
AND f_charbig = '#NULL#';
4804
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4805
SELECT f_int1, f_int1, '', '', 'was inserted'
4806
FROM t0_template source_tab
4807
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4809
# check transactions-1 success: 1
4812
# check transactions-2 success: 1
4815
# check transactions-3 success: 1
4816
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4820
# check transactions-4 success: 1
4821
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4822
SELECT f_int1, f_int1, '', '', 'was inserted'
4823
FROM t0_template source_tab
4824
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4826
# check transactions-5 success: 1
4829
# check transactions-6 success: 1
4830
# INFO: Storage engine used for t1 seems to be transactional.
4833
# check transactions-7 success: 1
4834
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4836
SET @@session.sql_mode = 'traditional';
4837
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
4838
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4839
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
4840
'', '', 'was inserted' FROM t0_template
4841
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4842
ERROR 22012: Division by 0
4845
# check transactions-8 success: 1
4846
# INFO: Storage engine used for t1 seems to be able to revert
4847
# changes made by the failing statement.
4848
SET @@session.sql_mode = '';
4850
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4852
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4854
# check special-1 success: 1
4855
UPDATE t1 SET f_charbig = '';
4857
# check special-2 success: 1
4858
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4859
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4860
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4861
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4862
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4863
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4864
'just inserted' FROM t0_template
4865
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4866
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
4868
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4869
f_charbig = 'updated by trigger'
4870
WHERE f_int1 = new.f_int1;
4872
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4873
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4874
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4876
# check trigger-1 success: 1
4878
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4879
f_int2 = CAST(f_char1 AS SIGNED INT),
4880
f_charbig = 'just inserted'
4881
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4883
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4884
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4885
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4886
'just inserted' FROM t0_template
4887
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4888
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
4890
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4891
f_charbig = 'updated by trigger'
4892
WHERE f_int1 = new.f_int1;
4894
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4895
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4896
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4898
# check trigger-2 success: 1
4900
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4901
f_int2 = CAST(f_char1 AS SIGNED INT),
4902
f_charbig = 'just inserted'
4903
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4905
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4906
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4907
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4908
'just inserted' FROM t0_template
4909
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4910
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4912
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4913
f_charbig = 'updated by trigger'
4914
WHERE f_int1 = new.f_int1;
4916
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4917
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4919
# check trigger-3 success: 1
4921
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4922
f_int2 = CAST(f_char1 AS SIGNED INT),
4923
f_charbig = 'just inserted'
4924
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4926
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4927
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4928
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4929
'just inserted' FROM t0_template
4930
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4931
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4933
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4934
f_charbig = 'updated by trigger'
4935
WHERE f_int1 = - old.f_int1;
4937
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4938
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4940
# check trigger-4 success: 1
4942
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4943
f_int2 = CAST(f_char1 AS SIGNED INT),
4944
f_charbig = 'just inserted'
4945
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4947
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4948
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4949
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4950
'just inserted' FROM t0_template
4951
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4952
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4954
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4955
f_charbig = 'updated by trigger'
4956
WHERE f_int1 = new.f_int1;
4958
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4959
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4961
# check trigger-5 success: 1
4963
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4964
f_int2 = CAST(f_char1 AS SIGNED INT),
4965
f_charbig = 'just inserted'
4966
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4968
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4969
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4970
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4971
'just inserted' FROM t0_template
4972
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4973
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4975
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4976
f_charbig = 'updated by trigger'
4977
WHERE f_int1 = - old.f_int1;
4979
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4980
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4982
# check trigger-6 success: 1
4984
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4985
f_int2 = CAST(f_char1 AS SIGNED INT),
4986
f_charbig = 'just inserted'
4987
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4989
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4990
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4991
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4992
'just inserted' FROM t0_template
4993
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4994
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
4996
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4997
f_charbig = 'updated by trigger'
4998
WHERE f_int1 = - old.f_int1;
5001
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5003
# check trigger-7 success: 1
5005
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5006
f_int2 = CAST(f_char1 AS SIGNED INT),
5007
f_charbig = 'just inserted'
5008
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5010
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5011
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5012
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5013
'just inserted' FROM t0_template
5014
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5015
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
5017
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5018
f_charbig = 'updated by trigger'
5019
WHERE f_int1 = - old.f_int1;
5022
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5024
# check trigger-8 success: 1
5026
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5027
f_int2 = CAST(f_char1 AS SIGNED INT),
5028
f_charbig = 'just inserted'
5029
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5031
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5033
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5034
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
5036
SET new.f_int1 = old.f_int1 + @max_row,
5037
new.f_int2 = old.f_int2 - @max_row,
5038
new.f_charbig = '####updated per update trigger####';
5041
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
5042
f_charbig = '####updated per update statement itself####';
5044
# check trigger-9 success: 1
5046
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5047
f_int2 = CAST(f_char1 AS SIGNED INT),
5048
f_charbig = CONCAT('===',f_char1,'===');
5049
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
5051
SET new.f_int1 = new.f_int1 + @max_row,
5052
new.f_int2 = new.f_int2 - @max_row,
5053
new.f_charbig = '####updated per update trigger####';
5056
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
5057
f_charbig = '####updated per update statement itself####';
5059
# check trigger-10 success: 1
5061
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5062
f_int2 = CAST(f_char1 AS SIGNED INT),
5063
f_charbig = CONCAT('===',f_char1,'===');
5064
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
5066
SET new.f_int1 = @my_max1 + @counter,
5067
new.f_int2 = @my_min2 - @counter,
5068
new.f_charbig = '####updated per insert trigger####';
5069
SET @counter = @counter + 1;
5072
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
5073
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
5074
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
5075
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
5076
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
5080
# check trigger-11 success: 1
5082
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
5083
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
5084
AND f_charbig = '####updated per insert trigger####';
5085
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
5087
SET new.f_int1 = @my_max1 + @counter,
5088
new.f_int2 = @my_min2 - @counter,
5089
new.f_charbig = '####updated per insert trigger####';
5090
SET @counter = @counter + 1;
5093
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
5094
INSERT INTO t1 (f_char1, f_char2, f_charbig)
5095
SELECT CAST(f_int1 AS CHAR),
5096
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
5097
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
5101
# check trigger-12 success: 1
5103
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
5104
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
5105
AND f_charbig = '####updated per insert trigger####';
5107
Table Op Msg_type Msg_text
5108
test.t1 analyze note The storage engine for the table doesn't support analyze
5109
CHECK TABLE t1 EXTENDED;
5110
Table Op Msg_type Msg_text
5111
test.t1 check note The storage engine for the table doesn't support check
5112
CHECKSUM TABLE t1 EXTENDED;
5114
test.t1 <some_value>
5116
Table Op Msg_type Msg_text
5117
test.t1 optimize note The storage engine for the table doesn't support optimize
5118
# check layout success: 1
5119
REPAIR TABLE t1 EXTENDED;
5120
Table Op Msg_type Msg_text
5121
test.t1 repair note The storage engine for the table doesn't support repair
5122
# check layout success: 1
5125
# check TRUNCATE success: 1
5126
# check layout success: 1
5127
# End usability test (inc/partition_check.inc)
5129
SET SESSION storage_engine='InnoDB';
5130
DROP VIEW IF EXISTS v1;
5131
DROP TABLE IF EXISTS t1;
5132
DROP TABLE IF EXISTS t0_aux;
5133
DROP TABLE IF EXISTS t0_definition;
5134
DROP TABLE IF EXISTS t0_template;
added
removed
mysql-test/suite/parts/r/partition_engine_innodb.result
