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
71
/*!50100 PARTITION BY HASH (f_int1)
74
# check prerequisites-1 success: 1
75
# check COUNT(*) success: 1
76
# check MIN/MAX(f_int1) success: 1
77
# check MIN/MAX(f_int2) success: 1
78
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
79
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
80
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
81
WHERE f_int1 IN (2,3);
82
# check prerequisites-3 success: 1
83
DELETE FROM t1 WHERE f_charbig = 'delete me';
84
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
85
# check read via f_int1 success: 1
86
# check read via f_int2 success: 1
88
# check multiple-1 success: 1
89
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
91
# check multiple-2 success: 1
92
INSERT INTO t1 SELECT * FROM t0_template
93
WHERE MOD(f_int1,3) = 0;
95
# check multiple-3 success: 1
96
UPDATE t1 SET f_int1 = f_int1 + @max_row
97
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
98
AND @max_row_div2 + @max_row_div4;
100
# check multiple-4 success: 1
102
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
103
AND @max_row_div2 + @max_row_div4 + @max_row;
105
# check multiple-5 success: 1
106
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
108
SET f_int1 = @cur_value , f_int2 = @cur_value,
109
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
110
f_charbig = '#SINGLE#';
112
# check single-1 success: 1
113
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
115
SET f_int1 = @cur_value , f_int2 = @cur_value,
116
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
117
f_charbig = '#SINGLE#';
119
# check single-2 success: 1
120
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
121
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
122
UPDATE t1 SET f_int1 = @cur_value2
123
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
125
# check single-3 success: 1
127
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
128
UPDATE t1 SET f_int1 = @cur_value1
129
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
131
# check single-4 success: 1
132
SELECT MAX(f_int1) INTO @cur_value FROM t1;
133
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
135
# check single-5 success: 1
136
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
138
# check single-6 success: 1
139
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
141
# check single-7 success: 1
142
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
143
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
144
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
145
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
146
f_charbig = '#NULL#';
148
SET f_int1 = NULL , f_int2 = -@max_row,
149
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
150
f_charbig = '#NULL#';
151
# check null success: 1
153
# check null-1 success: 1
154
UPDATE t1 SET f_int1 = -@max_row
155
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
156
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
158
# check null-2 success: 1
159
UPDATE t1 SET f_int1 = NULL
160
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
161
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
163
# check null-3 success: 1
165
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
166
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
168
# check null-4 success: 1
170
WHERE f_int1 = 0 AND f_int2 = 0
171
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
172
AND f_charbig = '#NULL#';
174
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
175
SELECT f_int1, f_int1, '', '', 'was inserted'
176
FROM t0_template source_tab
177
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
179
# check transactions-1 success: 1
182
# check transactions-2 success: 1
185
# check transactions-3 success: 1
186
DELETE FROM t1 WHERE f_charbig = 'was inserted';
190
# check transactions-4 success: 1
191
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
192
SELECT f_int1, f_int1, '', '', 'was inserted'
193
FROM t0_template source_tab
194
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
196
# check transactions-5 success: 1
199
# check transactions-6 success: 1
200
# INFO: Storage engine used for t1 seems to be transactional.
203
# check transactions-7 success: 1
204
DELETE FROM t1 WHERE f_charbig = 'was inserted';
206
SET @@session.sql_mode = 'traditional';
207
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
208
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
209
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
210
'', '', 'was inserted' FROM t0_template
211
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
212
ERROR 22012: Division by 0
215
# check transactions-8 success: 1
216
# INFO: Storage engine used for t1 seems to be able to revert
217
# changes made by the failing statement.
218
SET @@session.sql_mode = '';
220
DELETE FROM t1 WHERE f_charbig = 'was inserted';
222
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
224
# check special-1 success: 1
225
UPDATE t1 SET f_charbig = '';
227
# check special-2 success: 1
228
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
229
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
230
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
231
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
232
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
233
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
234
'just inserted' FROM t0_template
235
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
236
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
238
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
239
f_charbig = 'updated by trigger'
240
WHERE f_int1 = new.f_int1;
242
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
243
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
244
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
246
# check trigger-1 success: 1
248
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
249
f_int2 = CAST(f_char1 AS SIGNED INT),
250
f_charbig = 'just inserted'
251
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
253
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
254
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
255
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
256
'just inserted' FROM t0_template
257
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
258
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
260
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
261
f_charbig = 'updated by trigger'
262
WHERE f_int1 = new.f_int1;
264
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
265
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
266
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
268
# check trigger-2 success: 1
270
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
271
f_int2 = CAST(f_char1 AS SIGNED INT),
272
f_charbig = 'just inserted'
273
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
275
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
276
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
277
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
278
'just inserted' FROM t0_template
279
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
280
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
282
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
283
f_charbig = 'updated by trigger'
284
WHERE f_int1 = new.f_int1;
286
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
287
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
289
# check trigger-3 success: 1
291
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
292
f_int2 = CAST(f_char1 AS SIGNED INT),
293
f_charbig = 'just inserted'
294
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
296
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
297
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
298
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
299
'just inserted' FROM t0_template
300
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
301
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
303
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
304
f_charbig = 'updated by trigger'
305
WHERE f_int1 = - old.f_int1;
307
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
308
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
310
# check trigger-4 success: 1
312
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
313
f_int2 = CAST(f_char1 AS SIGNED INT),
314
f_charbig = 'just inserted'
315
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
317
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
318
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
319
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
320
'just inserted' FROM t0_template
321
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
322
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
324
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
325
f_charbig = 'updated by trigger'
326
WHERE f_int1 = new.f_int1;
328
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
329
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
331
# check trigger-5 success: 1
333
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
334
f_int2 = CAST(f_char1 AS SIGNED INT),
335
f_charbig = 'just inserted'
336
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
338
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
339
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
340
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
341
'just inserted' FROM t0_template
342
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
343
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
345
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
346
f_charbig = 'updated by trigger'
347
WHERE f_int1 = - old.f_int1;
349
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
350
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
352
# check trigger-6 success: 1
354
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
355
f_int2 = CAST(f_char1 AS SIGNED INT),
356
f_charbig = 'just inserted'
357
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
359
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
360
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
361
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
362
'just inserted' FROM t0_template
363
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
364
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
366
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
367
f_charbig = 'updated by trigger'
368
WHERE f_int1 = - old.f_int1;
371
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
373
# check trigger-7 success: 1
375
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
376
f_int2 = CAST(f_char1 AS SIGNED INT),
377
f_charbig = 'just inserted'
378
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
380
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
381
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
382
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
383
'just inserted' FROM t0_template
384
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
385
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
387
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
388
f_charbig = 'updated by trigger'
389
WHERE f_int1 = - old.f_int1;
392
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
394
# check trigger-8 success: 1
396
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
397
f_int2 = CAST(f_char1 AS SIGNED INT),
398
f_charbig = 'just inserted'
399
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
401
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
403
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
404
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
406
SET new.f_int1 = old.f_int1 + @max_row,
407
new.f_int2 = old.f_int2 - @max_row,
408
new.f_charbig = '####updated per update trigger####';
411
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
412
f_charbig = '####updated per update statement itself####';
414
# check trigger-9 success: 1
416
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
417
f_int2 = CAST(f_char1 AS SIGNED INT),
418
f_charbig = CONCAT('===',f_char1,'===');
419
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
421
SET new.f_int1 = new.f_int1 + @max_row,
422
new.f_int2 = new.f_int2 - @max_row,
423
new.f_charbig = '####updated per update trigger####';
426
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
427
f_charbig = '####updated per update statement itself####';
429
# check trigger-10 success: 1
431
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
432
f_int2 = CAST(f_char1 AS SIGNED INT),
433
f_charbig = CONCAT('===',f_char1,'===');
434
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
436
SET new.f_int1 = @my_max1 + @counter,
437
new.f_int2 = @my_min2 - @counter,
438
new.f_charbig = '####updated per insert trigger####';
439
SET @counter = @counter + 1;
442
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
443
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
444
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
445
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
446
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
450
# check trigger-11 success: 1
452
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
453
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
454
AND f_charbig = '####updated per insert trigger####';
455
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
457
SET new.f_int1 = @my_max1 + @counter,
458
new.f_int2 = @my_min2 - @counter,
459
new.f_charbig = '####updated per insert trigger####';
460
SET @counter = @counter + 1;
463
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
464
INSERT INTO t1 (f_char1, f_char2, f_charbig)
465
SELECT CAST(f_int1 AS CHAR),
466
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
467
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
471
# check trigger-12 success: 1
473
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
474
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
475
AND f_charbig = '####updated per insert trigger####';
477
Table Op Msg_type Msg_text
478
test.t1 analyze status OK
479
CHECK TABLE t1 EXTENDED;
480
Table Op Msg_type Msg_text
481
test.t1 check status OK
482
CHECKSUM TABLE t1 EXTENDED;
486
Table Op Msg_type Msg_text
487
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
488
test.t1 optimize status OK
489
# check layout success: 1
490
REPAIR TABLE t1 EXTENDED;
491
Table Op Msg_type Msg_text
492
test.t1 repair note The storage engine for the table doesn't support repair
493
# check layout success: 1
496
# check TRUNCATE success: 1
497
# check layout success: 1
498
# End usability test (inc/partition_check.inc)
500
#------------------------------------------------------------------------
501
# 2 Assignment of storage engine just after partition or subpartition
503
#------------------------------------------------------------------------
509
f_charbig VARCHAR(1000)
511
PARTITION BY HASH(f_int1)
512
( PARTITION part1 STORAGE ENGINE = 'InnoDB',
513
PARTITION part2 STORAGE ENGINE = 'InnoDB'
515
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
516
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
517
# Start usability test (inc/partition_check.inc)
519
SHOW CREATE TABLE t1;
521
t1 CREATE TABLE `t1` (
522
`f_int1` int(11) DEFAULT NULL,
523
`f_int2` int(11) DEFAULT NULL,
524
`f_char1` char(20) DEFAULT NULL,
525
`f_char2` char(20) DEFAULT NULL,
526
`f_charbig` varchar(1000) DEFAULT NULL
527
) ENGINE=InnoDB DEFAULT CHARSET=latin1
528
/*!50100 PARTITION BY HASH (f_int1)
529
(PARTITION part1 ENGINE = InnoDB,
530
PARTITION part2 ENGINE = InnoDB) */
532
# check prerequisites-1 success: 1
533
# check COUNT(*) success: 1
534
# check MIN/MAX(f_int1) success: 1
535
# check MIN/MAX(f_int2) success: 1
536
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
537
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
538
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
539
WHERE f_int1 IN (2,3);
540
# check prerequisites-3 success: 1
541
DELETE FROM t1 WHERE f_charbig = 'delete me';
542
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
543
# check read via f_int1 success: 1
544
# check read via f_int2 success: 1
546
# check multiple-1 success: 1
547
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
549
# check multiple-2 success: 1
550
INSERT INTO t1 SELECT * FROM t0_template
551
WHERE MOD(f_int1,3) = 0;
553
# check multiple-3 success: 1
554
UPDATE t1 SET f_int1 = f_int1 + @max_row
555
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
556
AND @max_row_div2 + @max_row_div4;
558
# check multiple-4 success: 1
560
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
561
AND @max_row_div2 + @max_row_div4 + @max_row;
563
# check multiple-5 success: 1
564
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
566
SET f_int1 = @cur_value , f_int2 = @cur_value,
567
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
568
f_charbig = '#SINGLE#';
570
# check single-1 success: 1
571
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
573
SET f_int1 = @cur_value , f_int2 = @cur_value,
574
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
575
f_charbig = '#SINGLE#';
577
# check single-2 success: 1
578
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
579
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
580
UPDATE t1 SET f_int1 = @cur_value2
581
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
583
# check single-3 success: 1
585
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
586
UPDATE t1 SET f_int1 = @cur_value1
587
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
589
# check single-4 success: 1
590
SELECT MAX(f_int1) INTO @cur_value FROM t1;
591
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
593
# check single-5 success: 1
594
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
596
# check single-6 success: 1
597
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
599
# check single-7 success: 1
600
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
601
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
602
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
603
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
604
f_charbig = '#NULL#';
606
SET f_int1 = NULL , f_int2 = -@max_row,
607
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
608
f_charbig = '#NULL#';
609
# check null success: 1
611
# check null-1 success: 1
612
UPDATE t1 SET f_int1 = -@max_row
613
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
614
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
616
# check null-2 success: 1
617
UPDATE t1 SET f_int1 = NULL
618
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
619
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
621
# check null-3 success: 1
623
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
624
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
626
# check null-4 success: 1
628
WHERE f_int1 = 0 AND f_int2 = 0
629
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
630
AND f_charbig = '#NULL#';
632
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
633
SELECT f_int1, f_int1, '', '', 'was inserted'
634
FROM t0_template source_tab
635
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
637
# check transactions-1 success: 1
640
# check transactions-2 success: 1
643
# check transactions-3 success: 1
644
DELETE FROM t1 WHERE f_charbig = 'was inserted';
648
# check transactions-4 success: 1
649
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
650
SELECT f_int1, f_int1, '', '', 'was inserted'
651
FROM t0_template source_tab
652
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
654
# check transactions-5 success: 1
657
# check transactions-6 success: 1
658
# INFO: Storage engine used for t1 seems to be transactional.
661
# check transactions-7 success: 1
662
DELETE FROM t1 WHERE f_charbig = 'was inserted';
664
SET @@session.sql_mode = 'traditional';
665
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
666
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
667
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
668
'', '', 'was inserted' FROM t0_template
669
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
670
ERROR 22012: Division by 0
673
# check transactions-8 success: 1
674
# INFO: Storage engine used for t1 seems to be able to revert
675
# changes made by the failing statement.
676
SET @@session.sql_mode = '';
678
DELETE FROM t1 WHERE f_charbig = 'was inserted';
680
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
682
# check special-1 success: 1
683
UPDATE t1 SET f_charbig = '';
685
# check special-2 success: 1
686
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
687
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
688
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
689
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
690
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
691
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
692
'just inserted' FROM t0_template
693
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
694
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
696
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
697
f_charbig = 'updated by trigger'
698
WHERE f_int1 = new.f_int1;
700
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
701
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
702
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
704
# check trigger-1 success: 1
706
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
707
f_int2 = CAST(f_char1 AS SIGNED INT),
708
f_charbig = 'just inserted'
709
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
711
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
712
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
713
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
714
'just inserted' FROM t0_template
715
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
716
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
718
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
719
f_charbig = 'updated by trigger'
720
WHERE f_int1 = new.f_int1;
722
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
723
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
724
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
726
# check trigger-2 success: 1
728
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
729
f_int2 = CAST(f_char1 AS SIGNED INT),
730
f_charbig = 'just inserted'
731
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
733
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
734
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
735
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
736
'just inserted' FROM t0_template
737
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
738
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
740
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
741
f_charbig = 'updated by trigger'
742
WHERE f_int1 = new.f_int1;
744
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
745
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
747
# check trigger-3 success: 1
749
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
750
f_int2 = CAST(f_char1 AS SIGNED INT),
751
f_charbig = 'just inserted'
752
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
754
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
755
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
756
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
757
'just inserted' FROM t0_template
758
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
759
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
761
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
762
f_charbig = 'updated by trigger'
763
WHERE f_int1 = - old.f_int1;
765
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
766
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
768
# check trigger-4 success: 1
770
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
771
f_int2 = CAST(f_char1 AS SIGNED INT),
772
f_charbig = 'just inserted'
773
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
775
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
776
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
777
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
778
'just inserted' FROM t0_template
779
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
780
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
782
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
783
f_charbig = 'updated by trigger'
784
WHERE f_int1 = new.f_int1;
786
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
787
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
789
# check trigger-5 success: 1
791
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
792
f_int2 = CAST(f_char1 AS SIGNED INT),
793
f_charbig = 'just inserted'
794
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
796
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
797
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
798
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
799
'just inserted' FROM t0_template
800
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
801
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
803
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
804
f_charbig = 'updated by trigger'
805
WHERE f_int1 = - old.f_int1;
807
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
808
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
810
# check trigger-6 success: 1
812
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
813
f_int2 = CAST(f_char1 AS SIGNED INT),
814
f_charbig = 'just inserted'
815
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
817
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
818
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
819
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
820
'just inserted' FROM t0_template
821
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
822
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
824
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
825
f_charbig = 'updated by trigger'
826
WHERE f_int1 = - old.f_int1;
829
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
831
# check trigger-7 success: 1
833
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
834
f_int2 = CAST(f_char1 AS SIGNED INT),
835
f_charbig = 'just inserted'
836
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
838
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
839
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
840
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
841
'just inserted' FROM t0_template
842
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
843
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
845
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
846
f_charbig = 'updated by trigger'
847
WHERE f_int1 = - old.f_int1;
850
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
852
# check trigger-8 success: 1
854
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
855
f_int2 = CAST(f_char1 AS SIGNED INT),
856
f_charbig = 'just inserted'
857
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
859
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
861
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
862
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
864
SET new.f_int1 = old.f_int1 + @max_row,
865
new.f_int2 = old.f_int2 - @max_row,
866
new.f_charbig = '####updated per update trigger####';
869
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
870
f_charbig = '####updated per update statement itself####';
872
# check trigger-9 success: 1
874
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
875
f_int2 = CAST(f_char1 AS SIGNED INT),
876
f_charbig = CONCAT('===',f_char1,'===');
877
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
879
SET new.f_int1 = new.f_int1 + @max_row,
880
new.f_int2 = new.f_int2 - @max_row,
881
new.f_charbig = '####updated per update trigger####';
884
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
885
f_charbig = '####updated per update statement itself####';
887
# check trigger-10 success: 1
889
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
890
f_int2 = CAST(f_char1 AS SIGNED INT),
891
f_charbig = CONCAT('===',f_char1,'===');
892
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
894
SET new.f_int1 = @my_max1 + @counter,
895
new.f_int2 = @my_min2 - @counter,
896
new.f_charbig = '####updated per insert trigger####';
897
SET @counter = @counter + 1;
900
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
901
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
902
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
903
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
904
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
908
# check trigger-11 success: 1
910
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
911
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
912
AND f_charbig = '####updated per insert trigger####';
913
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
915
SET new.f_int1 = @my_max1 + @counter,
916
new.f_int2 = @my_min2 - @counter,
917
new.f_charbig = '####updated per insert trigger####';
918
SET @counter = @counter + 1;
921
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
922
INSERT INTO t1 (f_char1, f_char2, f_charbig)
923
SELECT CAST(f_int1 AS CHAR),
924
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
925
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
929
# check trigger-12 success: 1
931
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
932
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
933
AND f_charbig = '####updated per insert trigger####';
935
Table Op Msg_type Msg_text
936
test.t1 analyze status OK
937
CHECK TABLE t1 EXTENDED;
938
Table Op Msg_type Msg_text
939
test.t1 check status OK
940
CHECKSUM TABLE t1 EXTENDED;
944
Table Op Msg_type Msg_text
945
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
946
test.t1 optimize status OK
947
# check layout success: 1
948
REPAIR TABLE t1 EXTENDED;
949
Table Op Msg_type Msg_text
950
test.t1 repair note The storage engine for the table doesn't support repair
951
# check layout success: 1
954
# check TRUNCATE success: 1
955
# check layout success: 1
956
# End usability test (inc/partition_check.inc)
963
f_charbig VARCHAR(1000)
965
PARTITION BY RANGE(f_int1)
966
SUBPARTITION BY HASH(f_int1)
967
( PARTITION part1 VALUES LESS THAN (10)
968
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
969
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
970
PARTITION part2 VALUES LESS THAN (2147483646)
971
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
972
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
974
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
975
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
976
# Start usability test (inc/partition_check.inc)
978
SHOW CREATE TABLE t1;
980
t1 CREATE TABLE `t1` (
981
`f_int1` int(11) DEFAULT NULL,
982
`f_int2` int(11) DEFAULT NULL,
983
`f_char1` char(20) DEFAULT NULL,
984
`f_char2` char(20) DEFAULT NULL,
985
`f_charbig` varchar(1000) DEFAULT NULL
986
) ENGINE=InnoDB DEFAULT CHARSET=latin1
987
/*!50100 PARTITION BY RANGE (f_int1)
988
SUBPARTITION BY HASH (f_int1)
989
(PARTITION part1 VALUES LESS THAN (10)
990
(SUBPARTITION subpart11 ENGINE = InnoDB,
991
SUBPARTITION subpart12 ENGINE = InnoDB),
992
PARTITION part2 VALUES LESS THAN (2147483646)
993
(SUBPARTITION subpart21 ENGINE = InnoDB,
994
SUBPARTITION subpart22 ENGINE = InnoDB)) */
996
# check prerequisites-1 success: 1
997
# check COUNT(*) success: 1
998
# check MIN/MAX(f_int1) success: 1
999
# check MIN/MAX(f_int2) success: 1
1000
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1001
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1002
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
1003
WHERE f_int1 IN (2,3);
1004
# check prerequisites-3 success: 1
1005
DELETE FROM t1 WHERE f_charbig = 'delete me';
1006
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
1007
# check read via f_int1 success: 1
1008
# check read via f_int2 success: 1
1010
# check multiple-1 success: 1
1011
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
1013
# check multiple-2 success: 1
1014
INSERT INTO t1 SELECT * FROM t0_template
1015
WHERE MOD(f_int1,3) = 0;
1017
# check multiple-3 success: 1
1018
UPDATE t1 SET f_int1 = f_int1 + @max_row
1019
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
1020
AND @max_row_div2 + @max_row_div4;
1022
# check multiple-4 success: 1
1024
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
1025
AND @max_row_div2 + @max_row_div4 + @max_row;
1027
# check multiple-5 success: 1
1028
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
1030
SET f_int1 = @cur_value , f_int2 = @cur_value,
1031
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1032
f_charbig = '#SINGLE#';
1034
# check single-1 success: 1
1035
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
1037
SET f_int1 = @cur_value , f_int2 = @cur_value,
1038
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1039
f_charbig = '#SINGLE#';
1041
# check single-2 success: 1
1042
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
1043
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
1044
UPDATE t1 SET f_int1 = @cur_value2
1045
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
1047
# check single-3 success: 1
1048
SET @cur_value1= -1;
1049
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
1050
UPDATE t1 SET f_int1 = @cur_value1
1051
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
1053
# check single-4 success: 1
1054
SELECT MAX(f_int1) INTO @cur_value FROM t1;
1055
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
1057
# check single-5 success: 1
1058
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
1060
# check single-6 success: 1
1061
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
1062
ERROR HY000: Table has no partition for value 2147483647
1063
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
1064
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
1065
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
1066
f_charbig = '#NULL#';
1068
SET f_int1 = NULL , f_int2 = -@max_row,
1069
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
1070
f_charbig = '#NULL#';
1071
# check null success: 1
1073
# check null-1 success: 1
1074
UPDATE t1 SET f_int1 = -@max_row
1075
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1076
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1078
# check null-2 success: 1
1079
UPDATE t1 SET f_int1 = NULL
1080
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1081
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1083
# check null-3 success: 1
1085
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1086
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1088
# check null-4 success: 1
1090
WHERE f_int1 = 0 AND f_int2 = 0
1091
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
1092
AND f_charbig = '#NULL#';
1094
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1095
SELECT f_int1, f_int1, '', '', 'was inserted'
1096
FROM t0_template source_tab
1097
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1099
# check transactions-1 success: 1
1102
# check transactions-2 success: 1
1105
# check transactions-3 success: 1
1106
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1110
# check transactions-4 success: 1
1111
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1112
SELECT f_int1, f_int1, '', '', 'was inserted'
1113
FROM t0_template source_tab
1114
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1116
# check transactions-5 success: 1
1119
# check transactions-6 success: 1
1120
# INFO: Storage engine used for t1 seems to be transactional.
1123
# check transactions-7 success: 1
1124
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1126
SET @@session.sql_mode = 'traditional';
1127
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
1128
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1129
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
1130
'', '', 'was inserted' FROM t0_template
1131
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1132
ERROR 22012: Division by 0
1135
# check transactions-8 success: 1
1136
# INFO: Storage engine used for t1 seems to be able to revert
1137
# changes made by the failing statement.
1138
SET @@session.sql_mode = '';
1140
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1142
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
1144
# check special-1 success: 1
1145
UPDATE t1 SET f_charbig = '';
1147
# check special-2 success: 1
1148
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
1149
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1150
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
1151
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1152
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1153
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1154
'just inserted' FROM t0_template
1155
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1156
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
1158
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1159
f_charbig = 'updated by trigger'
1160
WHERE f_int1 = new.f_int1;
1162
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1163
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1164
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1166
# check trigger-1 success: 1
1168
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1169
f_int2 = CAST(f_char1 AS SIGNED INT),
1170
f_charbig = 'just inserted'
1171
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1173
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1174
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1175
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1176
'just inserted' FROM t0_template
1177
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1178
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
1180
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1181
f_charbig = 'updated by trigger'
1182
WHERE f_int1 = new.f_int1;
1184
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1185
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1186
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1188
# check trigger-2 success: 1
1190
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1191
f_int2 = CAST(f_char1 AS SIGNED INT),
1192
f_charbig = 'just inserted'
1193
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1195
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1196
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1197
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1198
'just inserted' FROM t0_template
1199
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1200
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1202
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1203
f_charbig = 'updated by trigger'
1204
WHERE f_int1 = new.f_int1;
1206
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1207
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1209
# check trigger-3 success: 1
1211
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1212
f_int2 = CAST(f_char1 AS SIGNED INT),
1213
f_charbig = 'just inserted'
1214
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1216
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1217
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1218
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1219
'just inserted' FROM t0_template
1220
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1221
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1223
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1224
f_charbig = 'updated by trigger'
1225
WHERE f_int1 = - old.f_int1;
1227
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1228
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1230
# check trigger-4 success: 1
1232
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1233
f_int2 = CAST(f_char1 AS SIGNED INT),
1234
f_charbig = 'just inserted'
1235
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1237
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1238
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1239
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1240
'just inserted' FROM t0_template
1241
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1242
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1244
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1245
f_charbig = 'updated by trigger'
1246
WHERE f_int1 = new.f_int1;
1248
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1249
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1251
# check trigger-5 success: 1
1253
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1254
f_int2 = CAST(f_char1 AS SIGNED INT),
1255
f_charbig = 'just inserted'
1256
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1258
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1259
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1260
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1261
'just inserted' FROM t0_template
1262
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1263
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1265
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1266
f_charbig = 'updated by trigger'
1267
WHERE f_int1 = - old.f_int1;
1269
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1270
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1272
# check trigger-6 success: 1
1274
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1275
f_int2 = CAST(f_char1 AS SIGNED INT),
1276
f_charbig = 'just inserted'
1277
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1279
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1280
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1281
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1282
'just inserted' FROM t0_template
1283
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1284
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
1286
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1287
f_charbig = 'updated by trigger'
1288
WHERE f_int1 = - old.f_int1;
1291
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1293
# check trigger-7 success: 1
1295
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1296
f_int2 = CAST(f_char1 AS SIGNED INT),
1297
f_charbig = 'just inserted'
1298
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1300
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1301
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1302
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1303
'just inserted' FROM t0_template
1304
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1305
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
1307
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1308
f_charbig = 'updated by trigger'
1309
WHERE f_int1 = - old.f_int1;
1312
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1314
# check trigger-8 success: 1
1316
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1317
f_int2 = CAST(f_char1 AS SIGNED INT),
1318
f_charbig = 'just inserted'
1319
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1321
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1323
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1324
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1326
SET new.f_int1 = old.f_int1 + @max_row,
1327
new.f_int2 = old.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-9 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_2 BEFORE UPDATE ON t1 FOR EACH ROW
1341
SET new.f_int1 = new.f_int1 + @max_row,
1342
new.f_int2 = new.f_int2 - @max_row,
1343
new.f_charbig = '####updated per update trigger####';
1346
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1347
f_charbig = '####updated per update statement itself####';
1349
# check trigger-10 success: 1
1351
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1352
f_int2 = CAST(f_char1 AS SIGNED INT),
1353
f_charbig = CONCAT('===',f_char1,'===');
1354
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1356
SET new.f_int1 = @my_max1 + @counter,
1357
new.f_int2 = @my_min2 - @counter,
1358
new.f_charbig = '####updated per insert trigger####';
1359
SET @counter = @counter + 1;
1362
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1363
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1364
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1365
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1366
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1370
# check trigger-11 success: 1
1372
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1373
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1374
AND f_charbig = '####updated per insert trigger####';
1375
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1377
SET new.f_int1 = @my_max1 + @counter,
1378
new.f_int2 = @my_min2 - @counter,
1379
new.f_charbig = '####updated per insert trigger####';
1380
SET @counter = @counter + 1;
1383
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1384
INSERT INTO t1 (f_char1, f_char2, f_charbig)
1385
SELECT CAST(f_int1 AS CHAR),
1386
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1387
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1391
# check trigger-12 success: 1
1393
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1394
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1395
AND f_charbig = '####updated per insert trigger####';
1397
Table Op Msg_type Msg_text
1398
test.t1 analyze status OK
1399
CHECK TABLE t1 EXTENDED;
1400
Table Op Msg_type Msg_text
1401
test.t1 check status OK
1402
CHECKSUM TABLE t1 EXTENDED;
1404
test.t1 <some_value>
1406
Table Op Msg_type Msg_text
1407
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
1408
test.t1 optimize status OK
1409
# check layout success: 1
1410
REPAIR TABLE t1 EXTENDED;
1411
Table Op Msg_type Msg_text
1412
test.t1 repair note The storage engine for the table doesn't support repair
1413
# check layout success: 1
1416
# check TRUNCATE success: 1
1417
# check layout success: 1
1418
# End usability test (inc/partition_check.inc)
1420
#------------------------------------------------------------------------
1421
# 3 Some but not all named partitions or subpartitions get a storage
1423
#------------------------------------------------------------------------
1429
f_charbig VARCHAR(1000)
1431
PARTITION BY HASH(f_int1)
1432
( PARTITION part1 STORAGE ENGINE = 'InnoDB',
1435
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1441
f_charbig VARCHAR(1000)
1443
PARTITION BY HASH(f_int1)
1445
PARTITION part2 STORAGE ENGINE = 'InnoDB'
1447
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1453
f_charbig VARCHAR(1000)
1455
PARTITION BY RANGE(f_int1)
1456
SUBPARTITION BY HASH(f_int1)
1457
( PARTITION part1 VALUES LESS THAN (10)
1458
(SUBPARTITION subpart11,
1459
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
1460
PARTITION part2 VALUES LESS THAN (2147483646)
1461
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
1462
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
1464
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1470
f_charbig VARCHAR(1000)
1472
PARTITION BY RANGE(f_int1)
1473
SUBPARTITION BY HASH(f_int1)
1474
( PARTITION part1 VALUES LESS THAN (10)
1475
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
1476
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
1477
PARTITION part2 VALUES LESS THAN (2147483646)
1478
(SUBPARTITION subpart21,
1479
SUBPARTITION subpart22 )
1481
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1487
f_charbig VARCHAR(1000)
1490
PARTITION BY RANGE(f_int1)
1491
SUBPARTITION BY HASH(f_int1)
1492
( PARTITION part1 VALUES LESS THAN (10)
1493
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
1494
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
1495
PARTITION part2 VALUES LESS THAN (2147483646)
1496
(SUBPARTITION subpart21,
1497
SUBPARTITION subpart22 )
1499
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1500
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
1501
# Start usability test (inc/partition_check.inc)
1503
SHOW CREATE TABLE t1;
1505
t1 CREATE TABLE `t1` (
1506
`f_int1` int(11) DEFAULT NULL,
1507
`f_int2` int(11) DEFAULT NULL,
1508
`f_char1` char(20) DEFAULT NULL,
1509
`f_char2` char(20) DEFAULT NULL,
1510
`f_charbig` varchar(1000) DEFAULT NULL
1511
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1512
/*!50100 PARTITION BY RANGE (f_int1)
1513
SUBPARTITION BY HASH (f_int1)
1514
(PARTITION part1 VALUES LESS THAN (10)
1515
(SUBPARTITION subpart11 ENGINE = InnoDB,
1516
SUBPARTITION subpart12 ENGINE = InnoDB),
1517
PARTITION part2 VALUES LESS THAN (2147483646)
1518
(SUBPARTITION subpart21 ENGINE = InnoDB,
1519
SUBPARTITION subpart22 ENGINE = InnoDB)) */
1521
# check prerequisites-1 success: 1
1522
# check COUNT(*) success: 1
1523
# check MIN/MAX(f_int1) success: 1
1524
# check MIN/MAX(f_int2) success: 1
1525
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1526
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1527
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
1528
WHERE f_int1 IN (2,3);
1529
# check prerequisites-3 success: 1
1530
DELETE FROM t1 WHERE f_charbig = 'delete me';
1531
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
1532
# check read via f_int1 success: 1
1533
# check read via f_int2 success: 1
1535
# check multiple-1 success: 1
1536
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
1538
# check multiple-2 success: 1
1539
INSERT INTO t1 SELECT * FROM t0_template
1540
WHERE MOD(f_int1,3) = 0;
1542
# check multiple-3 success: 1
1543
UPDATE t1 SET f_int1 = f_int1 + @max_row
1544
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
1545
AND @max_row_div2 + @max_row_div4;
1547
# check multiple-4 success: 1
1549
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
1550
AND @max_row_div2 + @max_row_div4 + @max_row;
1552
# check multiple-5 success: 1
1553
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
1555
SET f_int1 = @cur_value , f_int2 = @cur_value,
1556
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1557
f_charbig = '#SINGLE#';
1559
# check single-1 success: 1
1560
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
1562
SET f_int1 = @cur_value , f_int2 = @cur_value,
1563
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1564
f_charbig = '#SINGLE#';
1566
# check single-2 success: 1
1567
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
1568
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
1569
UPDATE t1 SET f_int1 = @cur_value2
1570
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
1572
# check single-3 success: 1
1573
SET @cur_value1= -1;
1574
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
1575
UPDATE t1 SET f_int1 = @cur_value1
1576
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
1578
# check single-4 success: 1
1579
SELECT MAX(f_int1) INTO @cur_value FROM t1;
1580
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
1582
# check single-5 success: 1
1583
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
1585
# check single-6 success: 1
1586
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
1587
ERROR HY000: Table has no partition for value 2147483647
1588
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
1589
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
1590
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
1591
f_charbig = '#NULL#';
1593
SET f_int1 = NULL , f_int2 = -@max_row,
1594
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
1595
f_charbig = '#NULL#';
1596
# check null success: 1
1598
# check null-1 success: 1
1599
UPDATE t1 SET f_int1 = -@max_row
1600
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1601
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1603
# check null-2 success: 1
1604
UPDATE t1 SET f_int1 = NULL
1605
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1606
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1608
# check null-3 success: 1
1610
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1611
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1613
# check null-4 success: 1
1615
WHERE f_int1 = 0 AND f_int2 = 0
1616
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
1617
AND f_charbig = '#NULL#';
1619
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1620
SELECT f_int1, f_int1, '', '', 'was inserted'
1621
FROM t0_template source_tab
1622
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1624
# check transactions-1 success: 1
1627
# check transactions-2 success: 1
1630
# check transactions-3 success: 1
1631
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1635
# check transactions-4 success: 1
1636
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1637
SELECT f_int1, f_int1, '', '', 'was inserted'
1638
FROM t0_template source_tab
1639
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1641
# check transactions-5 success: 1
1644
# check transactions-6 success: 1
1645
# INFO: Storage engine used for t1 seems to be transactional.
1648
# check transactions-7 success: 1
1649
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1651
SET @@session.sql_mode = 'traditional';
1652
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
1653
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1654
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
1655
'', '', 'was inserted' FROM t0_template
1656
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1657
ERROR 22012: Division by 0
1660
# check transactions-8 success: 1
1661
# INFO: Storage engine used for t1 seems to be able to revert
1662
# changes made by the failing statement.
1663
SET @@session.sql_mode = '';
1665
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1667
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
1669
# check special-1 success: 1
1670
UPDATE t1 SET f_charbig = '';
1672
# check special-2 success: 1
1673
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
1674
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1675
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
1676
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1677
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1678
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1679
'just inserted' FROM t0_template
1680
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1681
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
1683
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1684
f_charbig = 'updated by trigger'
1685
WHERE f_int1 = new.f_int1;
1687
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1688
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1689
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1691
# check trigger-1 success: 1
1693
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1694
f_int2 = CAST(f_char1 AS SIGNED INT),
1695
f_charbig = 'just inserted'
1696
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1698
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1699
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1700
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1701
'just inserted' FROM t0_template
1702
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1703
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
1705
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1706
f_charbig = 'updated by trigger'
1707
WHERE f_int1 = new.f_int1;
1709
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1710
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1711
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1713
# check trigger-2 success: 1
1715
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1716
f_int2 = CAST(f_char1 AS SIGNED INT),
1717
f_charbig = 'just inserted'
1718
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1720
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1721
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1722
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1723
'just inserted' FROM t0_template
1724
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1725
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1727
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1728
f_charbig = 'updated by trigger'
1729
WHERE f_int1 = new.f_int1;
1731
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1732
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1734
# check trigger-3 success: 1
1736
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1737
f_int2 = CAST(f_char1 AS SIGNED INT),
1738
f_charbig = 'just inserted'
1739
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1741
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1742
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1743
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1744
'just inserted' FROM t0_template
1745
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1746
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1748
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1749
f_charbig = 'updated by trigger'
1750
WHERE f_int1 = - old.f_int1;
1752
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1753
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1755
# check trigger-4 success: 1
1757
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1758
f_int2 = CAST(f_char1 AS SIGNED INT),
1759
f_charbig = 'just inserted'
1760
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1762
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1763
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1764
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1765
'just inserted' FROM t0_template
1766
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1767
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1769
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1770
f_charbig = 'updated by trigger'
1771
WHERE f_int1 = new.f_int1;
1773
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1774
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1776
# check trigger-5 success: 1
1778
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1779
f_int2 = CAST(f_char1 AS SIGNED INT),
1780
f_charbig = 'just inserted'
1781
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1783
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1784
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1785
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1786
'just inserted' FROM t0_template
1787
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1788
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1790
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1791
f_charbig = 'updated by trigger'
1792
WHERE f_int1 = - old.f_int1;
1794
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1795
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1797
# check trigger-6 success: 1
1799
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1800
f_int2 = CAST(f_char1 AS SIGNED INT),
1801
f_charbig = 'just inserted'
1802
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1804
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1805
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1806
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1807
'just inserted' FROM t0_template
1808
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1809
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
1811
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1812
f_charbig = 'updated by trigger'
1813
WHERE f_int1 = - old.f_int1;
1816
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1818
# check trigger-7 success: 1
1820
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1821
f_int2 = CAST(f_char1 AS SIGNED INT),
1822
f_charbig = 'just inserted'
1823
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1825
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1826
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1827
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1828
'just inserted' FROM t0_template
1829
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1830
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
1832
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1833
f_charbig = 'updated by trigger'
1834
WHERE f_int1 = - old.f_int1;
1837
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1839
# check trigger-8 success: 1
1841
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1842
f_int2 = CAST(f_char1 AS SIGNED INT),
1843
f_charbig = 'just inserted'
1844
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1846
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1848
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1849
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1851
SET new.f_int1 = old.f_int1 + @max_row,
1852
new.f_int2 = old.f_int2 - @max_row,
1853
new.f_charbig = '####updated per update trigger####';
1856
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1857
f_charbig = '####updated per update statement itself####';
1859
# check trigger-9 success: 1
1861
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1862
f_int2 = CAST(f_char1 AS SIGNED INT),
1863
f_charbig = CONCAT('===',f_char1,'===');
1864
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1866
SET new.f_int1 = new.f_int1 + @max_row,
1867
new.f_int2 = new.f_int2 - @max_row,
1868
new.f_charbig = '####updated per update trigger####';
1871
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1872
f_charbig = '####updated per update statement itself####';
1874
# check trigger-10 success: 1
1876
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1877
f_int2 = CAST(f_char1 AS SIGNED INT),
1878
f_charbig = CONCAT('===',f_char1,'===');
1879
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1881
SET new.f_int1 = @my_max1 + @counter,
1882
new.f_int2 = @my_min2 - @counter,
1883
new.f_charbig = '####updated per insert trigger####';
1884
SET @counter = @counter + 1;
1887
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1888
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1889
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1890
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1891
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1895
# check trigger-11 success: 1
1897
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1898
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1899
AND f_charbig = '####updated per insert trigger####';
1900
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1902
SET new.f_int1 = @my_max1 + @counter,
1903
new.f_int2 = @my_min2 - @counter,
1904
new.f_charbig = '####updated per insert trigger####';
1905
SET @counter = @counter + 1;
1908
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1909
INSERT INTO t1 (f_char1, f_char2, f_charbig)
1910
SELECT CAST(f_int1 AS CHAR),
1911
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1912
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1916
# check trigger-12 success: 1
1918
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1919
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1920
AND f_charbig = '####updated per insert trigger####';
1922
Table Op Msg_type Msg_text
1923
test.t1 analyze status OK
1924
CHECK TABLE t1 EXTENDED;
1925
Table Op Msg_type Msg_text
1926
test.t1 check status OK
1927
CHECKSUM TABLE t1 EXTENDED;
1929
test.t1 <some_value>
1931
Table Op Msg_type Msg_text
1932
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
1933
test.t1 optimize status OK
1934
# check layout success: 1
1935
REPAIR TABLE t1 EXTENDED;
1936
Table Op Msg_type Msg_text
1937
test.t1 repair note The storage engine for the table doesn't support repair
1938
# check layout success: 1
1941
# check TRUNCATE success: 1
1942
# check layout success: 1
1943
# End usability test (inc/partition_check.inc)
1945
#------------------------------------------------------------------------
1946
# 4 Storage engine assignment after partition name + after name of
1947
# subpartitions belonging to another partition
1948
#------------------------------------------------------------------------
1954
f_charbig VARCHAR(1000)
1956
PARTITION BY RANGE(f_int1)
1957
SUBPARTITION BY HASH(f_int1)
1958
( PARTITION part1 VALUES LESS THAN (10)
1959
(SUBPARTITION subpart11,
1960
SUBPARTITION subpart12),
1961
PARTITION part2 VALUES LESS THAN (2147483646)
1962
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
1963
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
1965
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1971
f_charbig VARCHAR(1000)
1974
PARTITION BY RANGE(f_int1)
1975
SUBPARTITION BY HASH(f_int1)
1976
( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'InnoDB'
1977
(SUBPARTITION subpart11,
1978
SUBPARTITION subpart12),
1979
PARTITION part2 VALUES LESS THAN (2147483646)
1980
(SUBPARTITION subpart21,
1981
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
1989
f_charbig VARCHAR(1000)
1991
PARTITION BY RANGE(f_int1)
1992
SUBPARTITION BY HASH(f_int1)
1993
( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'InnoDB'
1994
(SUBPARTITION subpart11,
1995
SUBPARTITION subpart12),
1996
PARTITION part2 VALUES LESS THAN (2147483646)
1997
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
1998
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
2000
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2001
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2002
# Start usability test (inc/partition_check.inc)
2004
SHOW CREATE TABLE t1;
2006
t1 CREATE TABLE `t1` (
2007
`f_int1` int(11) DEFAULT NULL,
2008
`f_int2` int(11) DEFAULT NULL,
2009
`f_char1` char(20) DEFAULT NULL,
2010
`f_char2` char(20) DEFAULT NULL,
2011
`f_charbig` varchar(1000) DEFAULT NULL
2012
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2013
/*!50100 PARTITION BY RANGE (f_int1)
2014
SUBPARTITION BY HASH (f_int1)
2015
(PARTITION part1 VALUES LESS THAN (10)
2016
(SUBPARTITION subpart11 ENGINE = InnoDB,
2017
SUBPARTITION subpart12 ENGINE = InnoDB),
2018
PARTITION part2 VALUES LESS THAN (2147483646)
2019
(SUBPARTITION subpart21 ENGINE = InnoDB,
2020
SUBPARTITION subpart22 ENGINE = InnoDB)) */
2022
# check prerequisites-1 success: 1
2023
# check COUNT(*) success: 1
2024
# check MIN/MAX(f_int1) success: 1
2025
# check MIN/MAX(f_int2) success: 1
2026
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2027
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2028
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2029
WHERE f_int1 IN (2,3);
2030
# check prerequisites-3 success: 1
2031
DELETE FROM t1 WHERE f_charbig = 'delete me';
2032
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2033
# check read via f_int1 success: 1
2034
# check read via f_int2 success: 1
2036
# check multiple-1 success: 1
2037
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2039
# check multiple-2 success: 1
2040
INSERT INTO t1 SELECT * FROM t0_template
2041
WHERE MOD(f_int1,3) = 0;
2043
# check multiple-3 success: 1
2044
UPDATE t1 SET f_int1 = f_int1 + @max_row
2045
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2046
AND @max_row_div2 + @max_row_div4;
2048
# check multiple-4 success: 1
2050
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2051
AND @max_row_div2 + @max_row_div4 + @max_row;
2053
# check multiple-5 success: 1
2054
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2056
SET f_int1 = @cur_value , f_int2 = @cur_value,
2057
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2058
f_charbig = '#SINGLE#';
2060
# check single-1 success: 1
2061
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2063
SET f_int1 = @cur_value , f_int2 = @cur_value,
2064
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2065
f_charbig = '#SINGLE#';
2067
# check single-2 success: 1
2068
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2069
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2070
UPDATE t1 SET f_int1 = @cur_value2
2071
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2073
# check single-3 success: 1
2074
SET @cur_value1= -1;
2075
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2076
UPDATE t1 SET f_int1 = @cur_value1
2077
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2079
# check single-4 success: 1
2080
SELECT MAX(f_int1) INTO @cur_value FROM t1;
2081
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
2083
# check single-5 success: 1
2084
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
2086
# check single-6 success: 1
2087
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
2088
ERROR HY000: Table has no partition for value 2147483647
2089
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
2090
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
2091
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
2092
f_charbig = '#NULL#';
2094
SET f_int1 = NULL , f_int2 = -@max_row,
2095
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
2096
f_charbig = '#NULL#';
2097
# check null success: 1
2099
# check null-1 success: 1
2100
UPDATE t1 SET f_int1 = -@max_row
2101
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2102
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2104
# check null-2 success: 1
2105
UPDATE t1 SET f_int1 = NULL
2106
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2107
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2109
# check null-3 success: 1
2111
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2112
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2114
# check null-4 success: 1
2116
WHERE f_int1 = 0 AND f_int2 = 0
2117
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
2118
AND f_charbig = '#NULL#';
2120
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2121
SELECT f_int1, f_int1, '', '', 'was inserted'
2122
FROM t0_template source_tab
2123
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2125
# check transactions-1 success: 1
2128
# check transactions-2 success: 1
2131
# check transactions-3 success: 1
2132
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2136
# check transactions-4 success: 1
2137
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2138
SELECT f_int1, f_int1, '', '', 'was inserted'
2139
FROM t0_template source_tab
2140
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2142
# check transactions-5 success: 1
2145
# check transactions-6 success: 1
2146
# INFO: Storage engine used for t1 seems to be transactional.
2149
# check transactions-7 success: 1
2150
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2152
SET @@session.sql_mode = 'traditional';
2153
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
2154
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2155
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
2156
'', '', 'was inserted' FROM t0_template
2157
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2158
ERROR 22012: Division by 0
2161
# check transactions-8 success: 1
2162
# INFO: Storage engine used for t1 seems to be able to revert
2163
# changes made by the failing statement.
2164
SET @@session.sql_mode = '';
2166
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2168
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
2170
# check special-1 success: 1
2171
UPDATE t1 SET f_charbig = '';
2173
# check special-2 success: 1
2174
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
2175
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2176
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
2177
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2178
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2179
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2180
'just inserted' FROM t0_template
2181
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2182
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
2184
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2185
f_charbig = 'updated by trigger'
2186
WHERE f_int1 = new.f_int1;
2188
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2189
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2190
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2192
# check trigger-1 success: 1
2194
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2195
f_int2 = CAST(f_char1 AS SIGNED INT),
2196
f_charbig = 'just inserted'
2197
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2199
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2200
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2201
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2202
'just inserted' FROM t0_template
2203
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2204
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
2206
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2207
f_charbig = 'updated by trigger'
2208
WHERE f_int1 = new.f_int1;
2210
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2211
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2212
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2214
# check trigger-2 success: 1
2216
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2217
f_int2 = CAST(f_char1 AS SIGNED INT),
2218
f_charbig = 'just inserted'
2219
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2221
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2222
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2223
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2224
'just inserted' FROM t0_template
2225
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2226
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2228
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2229
f_charbig = 'updated by trigger'
2230
WHERE f_int1 = new.f_int1;
2232
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2233
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2235
# check trigger-3 success: 1
2237
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2238
f_int2 = CAST(f_char1 AS SIGNED INT),
2239
f_charbig = 'just inserted'
2240
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2242
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2243
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2244
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2245
'just inserted' FROM t0_template
2246
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2247
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2249
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2250
f_charbig = 'updated by trigger'
2251
WHERE f_int1 = - old.f_int1;
2253
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2254
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2256
# check trigger-4 success: 1
2258
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2259
f_int2 = CAST(f_char1 AS SIGNED INT),
2260
f_charbig = 'just inserted'
2261
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2263
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2264
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2265
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2266
'just inserted' FROM t0_template
2267
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2268
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2270
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2271
f_charbig = 'updated by trigger'
2272
WHERE f_int1 = new.f_int1;
2274
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2275
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2277
# check trigger-5 success: 1
2279
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2280
f_int2 = CAST(f_char1 AS SIGNED INT),
2281
f_charbig = 'just inserted'
2282
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2284
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2285
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2286
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2287
'just inserted' FROM t0_template
2288
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2289
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2291
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2292
f_charbig = 'updated by trigger'
2293
WHERE f_int1 = - old.f_int1;
2295
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2296
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2298
# check trigger-6 success: 1
2300
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2301
f_int2 = CAST(f_char1 AS SIGNED INT),
2302
f_charbig = 'just inserted'
2303
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2305
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2306
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2307
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2308
'just inserted' FROM t0_template
2309
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2310
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
2312
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2313
f_charbig = 'updated by trigger'
2314
WHERE f_int1 = - old.f_int1;
2317
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2319
# check trigger-7 success: 1
2321
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2322
f_int2 = CAST(f_char1 AS SIGNED INT),
2323
f_charbig = 'just inserted'
2324
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2326
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2327
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2328
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2329
'just inserted' FROM t0_template
2330
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2331
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
2333
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2334
f_charbig = 'updated by trigger'
2335
WHERE f_int1 = - old.f_int1;
2338
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2340
# check trigger-8 success: 1
2342
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2343
f_int2 = CAST(f_char1 AS SIGNED INT),
2344
f_charbig = 'just inserted'
2345
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2347
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2349
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2350
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2352
SET new.f_int1 = old.f_int1 + @max_row,
2353
new.f_int2 = old.f_int2 - @max_row,
2354
new.f_charbig = '####updated per update trigger####';
2357
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2358
f_charbig = '####updated per update statement itself####';
2360
# check trigger-9 success: 1
2362
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2363
f_int2 = CAST(f_char1 AS SIGNED INT),
2364
f_charbig = CONCAT('===',f_char1,'===');
2365
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2367
SET new.f_int1 = new.f_int1 + @max_row,
2368
new.f_int2 = new.f_int2 - @max_row,
2369
new.f_charbig = '####updated per update trigger####';
2372
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2373
f_charbig = '####updated per update statement itself####';
2375
# check trigger-10 success: 1
2377
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2378
f_int2 = CAST(f_char1 AS SIGNED INT),
2379
f_charbig = CONCAT('===',f_char1,'===');
2380
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2382
SET new.f_int1 = @my_max1 + @counter,
2383
new.f_int2 = @my_min2 - @counter,
2384
new.f_charbig = '####updated per insert trigger####';
2385
SET @counter = @counter + 1;
2388
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2389
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2390
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2391
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2392
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2396
# check trigger-11 success: 1
2398
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2399
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2400
AND f_charbig = '####updated per insert trigger####';
2401
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2403
SET new.f_int1 = @my_max1 + @counter,
2404
new.f_int2 = @my_min2 - @counter,
2405
new.f_charbig = '####updated per insert trigger####';
2406
SET @counter = @counter + 1;
2409
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2410
INSERT INTO t1 (f_char1, f_char2, f_charbig)
2411
SELECT CAST(f_int1 AS CHAR),
2412
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2413
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2417
# check trigger-12 success: 1
2419
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2420
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2421
AND f_charbig = '####updated per insert trigger####';
2423
Table Op Msg_type Msg_text
2424
test.t1 analyze status OK
2425
CHECK TABLE t1 EXTENDED;
2426
Table Op Msg_type Msg_text
2427
test.t1 check status OK
2428
CHECKSUM TABLE t1 EXTENDED;
2430
test.t1 <some_value>
2432
Table Op Msg_type Msg_text
2433
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
2434
test.t1 optimize status OK
2435
# check layout success: 1
2436
REPAIR TABLE t1 EXTENDED;
2437
Table Op Msg_type Msg_text
2438
test.t1 repair note The storage engine for the table doesn't support repair
2439
# check layout success: 1
2442
# check TRUNCATE success: 1
2443
# check layout success: 1
2444
# End usability test (inc/partition_check.inc)
2451
f_charbig VARCHAR(1000)
2453
PARTITION BY RANGE(f_int1)
2454
SUBPARTITION BY HASH(f_int1)
2455
( PARTITION part1 VALUES LESS THAN (10)
2456
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
2457
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
2458
PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = 'InnoDB'
2459
(SUBPARTITION subpart21 ENGINE = 'InnoDB',
2460
SUBPARTITION subpart22)
2462
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2463
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2464
# Start usability test (inc/partition_check.inc)
2466
SHOW CREATE TABLE t1;
2468
t1 CREATE TABLE `t1` (
2469
`f_int1` int(11) DEFAULT NULL,
2470
`f_int2` int(11) DEFAULT NULL,
2471
`f_char1` char(20) DEFAULT NULL,
2472
`f_char2` char(20) DEFAULT NULL,
2473
`f_charbig` varchar(1000) DEFAULT NULL
2474
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2475
/*!50100 PARTITION BY RANGE (f_int1)
2476
SUBPARTITION BY HASH (f_int1)
2477
(PARTITION part1 VALUES LESS THAN (10)
2478
(SUBPARTITION subpart11 ENGINE = InnoDB,
2479
SUBPARTITION subpart12 ENGINE = InnoDB),
2480
PARTITION part2 VALUES LESS THAN (2147483646)
2481
(SUBPARTITION subpart21 ENGINE = InnoDB,
2482
SUBPARTITION subpart22 ENGINE = InnoDB)) */
2484
# check prerequisites-1 success: 1
2485
# check COUNT(*) success: 1
2486
# check MIN/MAX(f_int1) success: 1
2487
# check MIN/MAX(f_int2) success: 1
2488
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2489
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2490
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2491
WHERE f_int1 IN (2,3);
2492
# check prerequisites-3 success: 1
2493
DELETE FROM t1 WHERE f_charbig = 'delete me';
2494
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2495
# check read via f_int1 success: 1
2496
# check read via f_int2 success: 1
2498
# check multiple-1 success: 1
2499
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2501
# check multiple-2 success: 1
2502
INSERT INTO t1 SELECT * FROM t0_template
2503
WHERE MOD(f_int1,3) = 0;
2505
# check multiple-3 success: 1
2506
UPDATE t1 SET f_int1 = f_int1 + @max_row
2507
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2508
AND @max_row_div2 + @max_row_div4;
2510
# check multiple-4 success: 1
2512
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2513
AND @max_row_div2 + @max_row_div4 + @max_row;
2515
# check multiple-5 success: 1
2516
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2518
SET f_int1 = @cur_value , f_int2 = @cur_value,
2519
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2520
f_charbig = '#SINGLE#';
2522
# check single-1 success: 1
2523
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2525
SET f_int1 = @cur_value , f_int2 = @cur_value,
2526
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2527
f_charbig = '#SINGLE#';
2529
# check single-2 success: 1
2530
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2531
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2532
UPDATE t1 SET f_int1 = @cur_value2
2533
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2535
# check single-3 success: 1
2536
SET @cur_value1= -1;
2537
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2538
UPDATE t1 SET f_int1 = @cur_value1
2539
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2541
# check single-4 success: 1
2542
SELECT MAX(f_int1) INTO @cur_value FROM t1;
2543
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
2545
# check single-5 success: 1
2546
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
2548
# check single-6 success: 1
2549
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
2550
ERROR HY000: Table has no partition for value 2147483647
2551
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
2552
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
2553
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
2554
f_charbig = '#NULL#';
2556
SET f_int1 = NULL , f_int2 = -@max_row,
2557
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
2558
f_charbig = '#NULL#';
2559
# check null success: 1
2561
# check null-1 success: 1
2562
UPDATE t1 SET f_int1 = -@max_row
2563
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2564
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2566
# check null-2 success: 1
2567
UPDATE t1 SET f_int1 = NULL
2568
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2569
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2571
# check null-3 success: 1
2573
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2574
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2576
# check null-4 success: 1
2578
WHERE f_int1 = 0 AND f_int2 = 0
2579
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
2580
AND f_charbig = '#NULL#';
2582
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2583
SELECT f_int1, f_int1, '', '', 'was inserted'
2584
FROM t0_template source_tab
2585
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2587
# check transactions-1 success: 1
2590
# check transactions-2 success: 1
2593
# check transactions-3 success: 1
2594
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2598
# check transactions-4 success: 1
2599
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2600
SELECT f_int1, f_int1, '', '', 'was inserted'
2601
FROM t0_template source_tab
2602
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2604
# check transactions-5 success: 1
2607
# check transactions-6 success: 1
2608
# INFO: Storage engine used for t1 seems to be transactional.
2611
# check transactions-7 success: 1
2612
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2614
SET @@session.sql_mode = 'traditional';
2615
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
2616
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2617
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
2618
'', '', 'was inserted' FROM t0_template
2619
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2620
ERROR 22012: Division by 0
2623
# check transactions-8 success: 1
2624
# INFO: Storage engine used for t1 seems to be able to revert
2625
# changes made by the failing statement.
2626
SET @@session.sql_mode = '';
2628
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2630
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
2632
# check special-1 success: 1
2633
UPDATE t1 SET f_charbig = '';
2635
# check special-2 success: 1
2636
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
2637
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2638
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
2639
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2640
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2641
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2642
'just inserted' FROM t0_template
2643
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2644
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
2646
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2647
f_charbig = 'updated by trigger'
2648
WHERE f_int1 = new.f_int1;
2650
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2651
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2652
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2654
# check trigger-1 success: 1
2656
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2657
f_int2 = CAST(f_char1 AS SIGNED INT),
2658
f_charbig = 'just inserted'
2659
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2661
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2662
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2663
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2664
'just inserted' FROM t0_template
2665
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2666
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
2668
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2669
f_charbig = 'updated by trigger'
2670
WHERE f_int1 = new.f_int1;
2672
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2673
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2674
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2676
# check trigger-2 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 BEFORE 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-3 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 BEFORE 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-4 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 AFTER UPDATE 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 = new.f_int1;
2736
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2737
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2739
# check trigger-5 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 UPDATE 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;
2757
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2758
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2760
# check trigger-6 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;
2768
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2769
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2770
'just inserted' FROM t0_template
2771
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2772
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
2774
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2775
f_charbig = 'updated by trigger'
2776
WHERE f_int1 = - old.f_int1;
2779
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2781
# check trigger-7 success: 1
2783
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2784
f_int2 = CAST(f_char1 AS SIGNED INT),
2785
f_charbig = 'just inserted'
2786
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2788
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2789
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2790
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2791
'just inserted' FROM t0_template
2792
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2793
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
2795
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2796
f_charbig = 'updated by trigger'
2797
WHERE f_int1 = - old.f_int1;
2800
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2802
# check trigger-8 success: 1
2804
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2805
f_int2 = CAST(f_char1 AS SIGNED INT),
2806
f_charbig = 'just inserted'
2807
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2809
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2811
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2812
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2814
SET new.f_int1 = old.f_int1 + @max_row,
2815
new.f_int2 = old.f_int2 - @max_row,
2816
new.f_charbig = '####updated per update trigger####';
2819
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2820
f_charbig = '####updated per update statement itself####';
2822
# check trigger-9 success: 1
2824
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2825
f_int2 = CAST(f_char1 AS SIGNED INT),
2826
f_charbig = CONCAT('===',f_char1,'===');
2827
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2829
SET new.f_int1 = new.f_int1 + @max_row,
2830
new.f_int2 = new.f_int2 - @max_row,
2831
new.f_charbig = '####updated per update trigger####';
2834
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2835
f_charbig = '####updated per update statement itself####';
2837
# check trigger-10 success: 1
2839
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2840
f_int2 = CAST(f_char1 AS SIGNED INT),
2841
f_charbig = CONCAT('===',f_char1,'===');
2842
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2844
SET new.f_int1 = @my_max1 + @counter,
2845
new.f_int2 = @my_min2 - @counter,
2846
new.f_charbig = '####updated per insert trigger####';
2847
SET @counter = @counter + 1;
2850
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2851
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2852
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2853
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2854
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2858
# check trigger-11 success: 1
2860
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2861
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2862
AND f_charbig = '####updated per insert trigger####';
2863
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2865
SET new.f_int1 = @my_max1 + @counter,
2866
new.f_int2 = @my_min2 - @counter,
2867
new.f_charbig = '####updated per insert trigger####';
2868
SET @counter = @counter + 1;
2871
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2872
INSERT INTO t1 (f_char1, f_char2, f_charbig)
2873
SELECT CAST(f_int1 AS CHAR),
2874
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2875
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2879
# check trigger-12 success: 1
2881
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2882
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2883
AND f_charbig = '####updated per insert trigger####';
2885
Table Op Msg_type Msg_text
2886
test.t1 analyze status OK
2887
CHECK TABLE t1 EXTENDED;
2888
Table Op Msg_type Msg_text
2889
test.t1 check status OK
2890
CHECKSUM TABLE t1 EXTENDED;
2892
test.t1 <some_value>
2894
Table Op Msg_type Msg_text
2895
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
2896
test.t1 optimize status OK
2897
# check layout success: 1
2898
REPAIR TABLE t1 EXTENDED;
2899
Table Op Msg_type Msg_text
2900
test.t1 repair note The storage engine for the table doesn't support repair
2901
# check layout success: 1
2904
# check TRUNCATE success: 1
2905
# check layout success: 1
2906
# End usability test (inc/partition_check.inc)
2908
#------------------------------------------------------------------------
2909
# 5 Precedence of storage engine assignments (if there is any)
2910
#------------------------------------------------------------------------
2911
# 5.1 Storage engine assignment after column list + after partition
2912
# or subpartition name
2918
f_charbig VARCHAR(1000)
2920
PARTITION BY HASH(f_int1)
2921
( PARTITION part1 STORAGE ENGINE = 'InnoDB',
2922
PARTITION part2 STORAGE ENGINE = 'InnoDB'
2924
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2925
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2926
# Start usability test (inc/partition_check.inc)
2928
SHOW CREATE TABLE t1;
2930
t1 CREATE TABLE `t1` (
2931
`f_int1` int(11) DEFAULT NULL,
2932
`f_int2` int(11) DEFAULT NULL,
2933
`f_char1` char(20) DEFAULT NULL,
2934
`f_char2` char(20) DEFAULT NULL,
2935
`f_charbig` varchar(1000) DEFAULT NULL
2936
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2937
/*!50100 PARTITION BY HASH (f_int1)
2938
(PARTITION part1 ENGINE = InnoDB,
2939
PARTITION part2 ENGINE = InnoDB) */
2941
# check prerequisites-1 success: 1
2942
# check COUNT(*) success: 1
2943
# check MIN/MAX(f_int1) success: 1
2944
# check MIN/MAX(f_int2) success: 1
2945
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2946
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2947
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2948
WHERE f_int1 IN (2,3);
2949
# check prerequisites-3 success: 1
2950
DELETE FROM t1 WHERE f_charbig = 'delete me';
2951
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2952
# check read via f_int1 success: 1
2953
# check read via f_int2 success: 1
2955
# check multiple-1 success: 1
2956
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2958
# check multiple-2 success: 1
2959
INSERT INTO t1 SELECT * FROM t0_template
2960
WHERE MOD(f_int1,3) = 0;
2962
# check multiple-3 success: 1
2963
UPDATE t1 SET f_int1 = f_int1 + @max_row
2964
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2965
AND @max_row_div2 + @max_row_div4;
2967
# check multiple-4 success: 1
2969
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2970
AND @max_row_div2 + @max_row_div4 + @max_row;
2972
# check multiple-5 success: 1
2973
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2975
SET f_int1 = @cur_value , f_int2 = @cur_value,
2976
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2977
f_charbig = '#SINGLE#';
2979
# check single-1 success: 1
2980
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2982
SET f_int1 = @cur_value , f_int2 = @cur_value,
2983
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2984
f_charbig = '#SINGLE#';
2986
# check single-2 success: 1
2987
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2988
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2989
UPDATE t1 SET f_int1 = @cur_value2
2990
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2992
# check single-3 success: 1
2993
SET @cur_value1= -1;
2994
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2995
UPDATE t1 SET f_int1 = @cur_value1
2996
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2998
# check single-4 success: 1
2999
SELECT MAX(f_int1) INTO @cur_value FROM t1;
3000
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
3002
# check single-5 success: 1
3003
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
3005
# check single-6 success: 1
3006
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3008
# check single-7 success: 1
3009
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
3010
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3011
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3012
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3013
f_charbig = '#NULL#';
3015
SET f_int1 = NULL , f_int2 = -@max_row,
3016
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3017
f_charbig = '#NULL#';
3018
# check null success: 1
3020
# check null-1 success: 1
3021
UPDATE t1 SET f_int1 = -@max_row
3022
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3023
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3025
# check null-2 success: 1
3026
UPDATE t1 SET f_int1 = NULL
3027
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3028
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3030
# check null-3 success: 1
3032
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3033
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3035
# check null-4 success: 1
3037
WHERE f_int1 = 0 AND f_int2 = 0
3038
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
3039
AND f_charbig = '#NULL#';
3041
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3042
SELECT f_int1, f_int1, '', '', 'was inserted'
3043
FROM t0_template source_tab
3044
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3046
# check transactions-1 success: 1
3049
# check transactions-2 success: 1
3052
# check transactions-3 success: 1
3053
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3057
# check transactions-4 success: 1
3058
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3059
SELECT f_int1, f_int1, '', '', 'was inserted'
3060
FROM t0_template source_tab
3061
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3063
# check transactions-5 success: 1
3066
# check transactions-6 success: 1
3067
# INFO: Storage engine used for t1 seems to be transactional.
3070
# check transactions-7 success: 1
3071
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3073
SET @@session.sql_mode = 'traditional';
3074
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
3075
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3076
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
3077
'', '', 'was inserted' FROM t0_template
3078
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3079
ERROR 22012: Division by 0
3082
# check transactions-8 success: 1
3083
# INFO: Storage engine used for t1 seems to be able to revert
3084
# changes made by the failing statement.
3085
SET @@session.sql_mode = '';
3087
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3089
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
3091
# check special-1 success: 1
3092
UPDATE t1 SET f_charbig = '';
3094
# check special-2 success: 1
3095
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
3096
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3097
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
3098
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3099
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3100
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3101
'just inserted' FROM t0_template
3102
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3103
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
3105
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3106
f_charbig = 'updated by trigger'
3107
WHERE f_int1 = new.f_int1;
3109
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3110
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3111
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3113
# check trigger-1 success: 1
3115
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3116
f_int2 = CAST(f_char1 AS SIGNED INT),
3117
f_charbig = 'just inserted'
3118
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3120
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3121
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3122
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3123
'just inserted' FROM t0_template
3124
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3125
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
3127
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3128
f_charbig = 'updated by trigger'
3129
WHERE f_int1 = new.f_int1;
3131
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3132
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3133
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3135
# check trigger-2 success: 1
3137
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3138
f_int2 = CAST(f_char1 AS SIGNED INT),
3139
f_charbig = 'just inserted'
3140
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3142
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3143
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3144
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3145
'just inserted' FROM t0_template
3146
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3147
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3149
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3150
f_charbig = 'updated by trigger'
3151
WHERE f_int1 = new.f_int1;
3153
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3154
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3156
# check trigger-3 success: 1
3158
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3159
f_int2 = CAST(f_char1 AS SIGNED INT),
3160
f_charbig = 'just inserted'
3161
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3163
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3164
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3165
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3166
'just inserted' FROM t0_template
3167
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3168
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3170
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3171
f_charbig = 'updated by trigger'
3172
WHERE f_int1 = - old.f_int1;
3174
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3175
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3177
# check trigger-4 success: 1
3179
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3180
f_int2 = CAST(f_char1 AS SIGNED INT),
3181
f_charbig = 'just inserted'
3182
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3184
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3185
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3186
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3187
'just inserted' FROM t0_template
3188
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3189
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3191
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3192
f_charbig = 'updated by trigger'
3193
WHERE f_int1 = new.f_int1;
3195
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3196
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3198
# check trigger-5 success: 1
3200
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3201
f_int2 = CAST(f_char1 AS SIGNED INT),
3202
f_charbig = 'just inserted'
3203
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3205
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3206
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3207
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3208
'just inserted' FROM t0_template
3209
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3210
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3212
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3213
f_charbig = 'updated by trigger'
3214
WHERE f_int1 = - old.f_int1;
3216
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3217
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3219
# check trigger-6 success: 1
3221
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3222
f_int2 = CAST(f_char1 AS SIGNED INT),
3223
f_charbig = 'just inserted'
3224
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3226
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3227
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3228
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3229
'just inserted' FROM t0_template
3230
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3231
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
3233
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3234
f_charbig = 'updated by trigger'
3235
WHERE f_int1 = - old.f_int1;
3238
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3240
# check trigger-7 success: 1
3242
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3243
f_int2 = CAST(f_char1 AS SIGNED INT),
3244
f_charbig = 'just inserted'
3245
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3247
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3248
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3249
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3250
'just inserted' FROM t0_template
3251
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3252
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
3254
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3255
f_charbig = 'updated by trigger'
3256
WHERE f_int1 = - old.f_int1;
3259
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3261
# check trigger-8 success: 1
3263
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3264
f_int2 = CAST(f_char1 AS SIGNED INT),
3265
f_charbig = 'just inserted'
3266
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3268
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3270
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3271
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3273
SET new.f_int1 = old.f_int1 + @max_row,
3274
new.f_int2 = old.f_int2 - @max_row,
3275
new.f_charbig = '####updated per update trigger####';
3278
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3279
f_charbig = '####updated per update statement itself####';
3281
# check trigger-9 success: 1
3283
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3284
f_int2 = CAST(f_char1 AS SIGNED INT),
3285
f_charbig = CONCAT('===',f_char1,'===');
3286
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3288
SET new.f_int1 = new.f_int1 + @max_row,
3289
new.f_int2 = new.f_int2 - @max_row,
3290
new.f_charbig = '####updated per update trigger####';
3293
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3294
f_charbig = '####updated per update statement itself####';
3296
# check trigger-10 success: 1
3298
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3299
f_int2 = CAST(f_char1 AS SIGNED INT),
3300
f_charbig = CONCAT('===',f_char1,'===');
3301
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3303
SET new.f_int1 = @my_max1 + @counter,
3304
new.f_int2 = @my_min2 - @counter,
3305
new.f_charbig = '####updated per insert trigger####';
3306
SET @counter = @counter + 1;
3309
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3310
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3311
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3312
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3313
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3317
# check trigger-11 success: 1
3319
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3320
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3321
AND f_charbig = '####updated per insert trigger####';
3322
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3324
SET new.f_int1 = @my_max1 + @counter,
3325
new.f_int2 = @my_min2 - @counter,
3326
new.f_charbig = '####updated per insert trigger####';
3327
SET @counter = @counter + 1;
3330
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3331
INSERT INTO t1 (f_char1, f_char2, f_charbig)
3332
SELECT CAST(f_int1 AS CHAR),
3333
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3334
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3338
# check trigger-12 success: 1
3340
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3341
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3342
AND f_charbig = '####updated per insert trigger####';
3344
Table Op Msg_type Msg_text
3345
test.t1 analyze status OK
3346
CHECK TABLE t1 EXTENDED;
3347
Table Op Msg_type Msg_text
3348
test.t1 check status OK
3349
CHECKSUM TABLE t1 EXTENDED;
3351
test.t1 <some_value>
3353
Table Op Msg_type Msg_text
3354
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
3355
test.t1 optimize status OK
3356
# check layout success: 1
3357
REPAIR TABLE t1 EXTENDED;
3358
Table Op Msg_type Msg_text
3359
test.t1 repair note The storage engine for the table doesn't support repair
3360
# check layout success: 1
3363
# check TRUNCATE success: 1
3364
# check layout success: 1
3365
# End usability test (inc/partition_check.inc)
3372
f_charbig VARCHAR(1000)
3374
PARTITION BY RANGE(f_int1)
3375
SUBPARTITION BY HASH(f_int1)
3376
( PARTITION part1 VALUES LESS THAN (10)
3377
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
3378
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
3379
PARTITION part2 VALUES LESS THAN (2147483646)
3380
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
3381
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
3383
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3384
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
3385
# Start usability test (inc/partition_check.inc)
3387
SHOW CREATE TABLE t1;
3389
t1 CREATE TABLE `t1` (
3390
`f_int1` int(11) DEFAULT NULL,
3391
`f_int2` int(11) DEFAULT NULL,
3392
`f_char1` char(20) DEFAULT NULL,
3393
`f_char2` char(20) DEFAULT NULL,
3394
`f_charbig` varchar(1000) DEFAULT NULL
3395
) ENGINE=InnoDB DEFAULT CHARSET=latin1
3396
/*!50100 PARTITION BY RANGE (f_int1)
3397
SUBPARTITION BY HASH (f_int1)
3398
(PARTITION part1 VALUES LESS THAN (10)
3399
(SUBPARTITION subpart11 ENGINE = InnoDB,
3400
SUBPARTITION subpart12 ENGINE = InnoDB),
3401
PARTITION part2 VALUES LESS THAN (2147483646)
3402
(SUBPARTITION subpart21 ENGINE = InnoDB,
3403
SUBPARTITION subpart22 ENGINE = InnoDB)) */
3405
# check prerequisites-1 success: 1
3406
# check COUNT(*) success: 1
3407
# check MIN/MAX(f_int1) success: 1
3408
# check MIN/MAX(f_int2) success: 1
3409
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3410
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3411
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
3412
WHERE f_int1 IN (2,3);
3413
# check prerequisites-3 success: 1
3414
DELETE FROM t1 WHERE f_charbig = 'delete me';
3415
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
3416
# check read via f_int1 success: 1
3417
# check read via f_int2 success: 1
3419
# check multiple-1 success: 1
3420
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
3422
# check multiple-2 success: 1
3423
INSERT INTO t1 SELECT * FROM t0_template
3424
WHERE MOD(f_int1,3) = 0;
3426
# check multiple-3 success: 1
3427
UPDATE t1 SET f_int1 = f_int1 + @max_row
3428
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
3429
AND @max_row_div2 + @max_row_div4;
3431
# check multiple-4 success: 1
3433
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
3434
AND @max_row_div2 + @max_row_div4 + @max_row;
3436
# check multiple-5 success: 1
3437
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
3439
SET f_int1 = @cur_value , f_int2 = @cur_value,
3440
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3441
f_charbig = '#SINGLE#';
3443
# check single-1 success: 1
3444
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
3446
SET f_int1 = @cur_value , f_int2 = @cur_value,
3447
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3448
f_charbig = '#SINGLE#';
3450
# check single-2 success: 1
3451
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
3452
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
3453
UPDATE t1 SET f_int1 = @cur_value2
3454
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
3456
# check single-3 success: 1
3457
SET @cur_value1= -1;
3458
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
3459
UPDATE t1 SET f_int1 = @cur_value1
3460
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
3462
# check single-4 success: 1
3463
SELECT MAX(f_int1) INTO @cur_value FROM t1;
3464
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
3466
# check single-5 success: 1
3467
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
3469
# check single-6 success: 1
3470
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3471
ERROR HY000: Table has no partition for value 2147483647
3472
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3473
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3474
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3475
f_charbig = '#NULL#';
3477
SET f_int1 = NULL , f_int2 = -@max_row,
3478
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3479
f_charbig = '#NULL#';
3480
# check null success: 1
3482
# check null-1 success: 1
3483
UPDATE t1 SET f_int1 = -@max_row
3484
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3485
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3487
# check null-2 success: 1
3488
UPDATE t1 SET f_int1 = NULL
3489
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3490
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3492
# check null-3 success: 1
3494
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3495
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3497
# check null-4 success: 1
3499
WHERE f_int1 = 0 AND f_int2 = 0
3500
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
3501
AND f_charbig = '#NULL#';
3503
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3504
SELECT f_int1, f_int1, '', '', 'was inserted'
3505
FROM t0_template source_tab
3506
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3508
# check transactions-1 success: 1
3511
# check transactions-2 success: 1
3514
# check transactions-3 success: 1
3515
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3519
# check transactions-4 success: 1
3520
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3521
SELECT f_int1, f_int1, '', '', 'was inserted'
3522
FROM t0_template source_tab
3523
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3525
# check transactions-5 success: 1
3528
# check transactions-6 success: 1
3529
# INFO: Storage engine used for t1 seems to be transactional.
3532
# check transactions-7 success: 1
3533
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3535
SET @@session.sql_mode = 'traditional';
3536
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
3537
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3538
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
3539
'', '', 'was inserted' FROM t0_template
3540
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3541
ERROR 22012: Division by 0
3544
# check transactions-8 success: 1
3545
# INFO: Storage engine used for t1 seems to be able to revert
3546
# changes made by the failing statement.
3547
SET @@session.sql_mode = '';
3549
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3551
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
3553
# check special-1 success: 1
3554
UPDATE t1 SET f_charbig = '';
3556
# check special-2 success: 1
3557
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
3558
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3559
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
3560
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3561
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3562
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3563
'just inserted' FROM t0_template
3564
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3565
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
3567
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3568
f_charbig = 'updated by trigger'
3569
WHERE f_int1 = new.f_int1;
3571
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3572
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3573
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3575
# check trigger-1 success: 1
3577
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3578
f_int2 = CAST(f_char1 AS SIGNED INT),
3579
f_charbig = 'just inserted'
3580
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3582
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3583
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3584
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3585
'just inserted' FROM t0_template
3586
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3587
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
3589
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3590
f_charbig = 'updated by trigger'
3591
WHERE f_int1 = new.f_int1;
3593
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3594
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3595
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3597
# check trigger-2 success: 1
3599
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3600
f_int2 = CAST(f_char1 AS SIGNED INT),
3601
f_charbig = 'just inserted'
3602
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3604
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3605
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3606
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3607
'just inserted' FROM t0_template
3608
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3609
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3611
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3612
f_charbig = 'updated by trigger'
3613
WHERE f_int1 = new.f_int1;
3615
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3616
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3618
# check trigger-3 success: 1
3620
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3621
f_int2 = CAST(f_char1 AS SIGNED INT),
3622
f_charbig = 'just inserted'
3623
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3625
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3626
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3627
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3628
'just inserted' FROM t0_template
3629
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3630
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3632
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3633
f_charbig = 'updated by trigger'
3634
WHERE f_int1 = - old.f_int1;
3636
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3637
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3639
# check trigger-4 success: 1
3641
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3642
f_int2 = CAST(f_char1 AS SIGNED INT),
3643
f_charbig = 'just inserted'
3644
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3646
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3647
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3648
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3649
'just inserted' FROM t0_template
3650
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3651
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3653
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3654
f_charbig = 'updated by trigger'
3655
WHERE f_int1 = new.f_int1;
3657
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3658
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3660
# check trigger-5 success: 1
3662
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3663
f_int2 = CAST(f_char1 AS SIGNED INT),
3664
f_charbig = 'just inserted'
3665
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3667
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3668
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3669
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3670
'just inserted' FROM t0_template
3671
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3672
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3674
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3675
f_charbig = 'updated by trigger'
3676
WHERE f_int1 = - old.f_int1;
3678
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3679
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3681
# check trigger-6 success: 1
3683
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3684
f_int2 = CAST(f_char1 AS SIGNED INT),
3685
f_charbig = 'just inserted'
3686
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3688
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3689
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3690
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3691
'just inserted' FROM t0_template
3692
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3693
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
3695
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3696
f_charbig = 'updated by trigger'
3697
WHERE f_int1 = - old.f_int1;
3700
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3702
# check trigger-7 success: 1
3704
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3705
f_int2 = CAST(f_char1 AS SIGNED INT),
3706
f_charbig = 'just inserted'
3707
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3709
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3710
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3711
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3712
'just inserted' FROM t0_template
3713
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3714
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
3716
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3717
f_charbig = 'updated by trigger'
3718
WHERE f_int1 = - old.f_int1;
3721
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3723
# check trigger-8 success: 1
3725
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3726
f_int2 = CAST(f_char1 AS SIGNED INT),
3727
f_charbig = 'just inserted'
3728
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3730
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3732
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3733
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3735
SET new.f_int1 = old.f_int1 + @max_row,
3736
new.f_int2 = old.f_int2 - @max_row,
3737
new.f_charbig = '####updated per update trigger####';
3740
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3741
f_charbig = '####updated per update statement itself####';
3743
# check trigger-9 success: 1
3745
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3746
f_int2 = CAST(f_char1 AS SIGNED INT),
3747
f_charbig = CONCAT('===',f_char1,'===');
3748
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3750
SET new.f_int1 = new.f_int1 + @max_row,
3751
new.f_int2 = new.f_int2 - @max_row,
3752
new.f_charbig = '####updated per update trigger####';
3755
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3756
f_charbig = '####updated per update statement itself####';
3758
# check trigger-10 success: 1
3760
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3761
f_int2 = CAST(f_char1 AS SIGNED INT),
3762
f_charbig = CONCAT('===',f_char1,'===');
3763
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3765
SET new.f_int1 = @my_max1 + @counter,
3766
new.f_int2 = @my_min2 - @counter,
3767
new.f_charbig = '####updated per insert trigger####';
3768
SET @counter = @counter + 1;
3771
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3772
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3773
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3774
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3775
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3779
# check trigger-11 success: 1
3781
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3782
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3783
AND f_charbig = '####updated per insert trigger####';
3784
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3786
SET new.f_int1 = @my_max1 + @counter,
3787
new.f_int2 = @my_min2 - @counter,
3788
new.f_charbig = '####updated per insert trigger####';
3789
SET @counter = @counter + 1;
3792
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3793
INSERT INTO t1 (f_char1, f_char2, f_charbig)
3794
SELECT CAST(f_int1 AS CHAR),
3795
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3796
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3800
# check trigger-12 success: 1
3802
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3803
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3804
AND f_charbig = '####updated per insert trigger####';
3806
Table Op Msg_type Msg_text
3807
test.t1 analyze status OK
3808
CHECK TABLE t1 EXTENDED;
3809
Table Op Msg_type Msg_text
3810
test.t1 check status OK
3811
CHECKSUM TABLE t1 EXTENDED;
3813
test.t1 <some_value>
3815
Table Op Msg_type Msg_text
3816
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
3817
test.t1 optimize status OK
3818
# check layout success: 1
3819
REPAIR TABLE t1 EXTENDED;
3820
Table Op Msg_type Msg_text
3821
test.t1 repair note The storage engine for the table doesn't support repair
3822
# check layout success: 1
3825
# check TRUNCATE success: 1
3826
# check layout success: 1
3827
# End usability test (inc/partition_check.inc)
3829
# 6.2 Storage engine assignment after partition name + after
3831
# in partition part + in sub partition part
3837
f_charbig VARCHAR(1000)
3839
PARTITION BY RANGE(f_int1)
3840
SUBPARTITION BY HASH(f_int1)
3841
( PARTITION part1 VALUES LESS THAN (10) STORAGE ENGINE = 'InnoDB'
3842
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
3843
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'),
3844
PARTITION part2 VALUES LESS THAN (2147483646)
3845
(SUBPARTITION subpart21 STORAGE ENGINE = 'InnoDB',
3846
SUBPARTITION subpart22 STORAGE ENGINE = 'InnoDB')
3848
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3849
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
3850
# Start usability test (inc/partition_check.inc)
3852
SHOW CREATE TABLE t1;
3854
t1 CREATE TABLE `t1` (
3855
`f_int1` int(11) DEFAULT NULL,
3856
`f_int2` int(11) DEFAULT NULL,
3857
`f_char1` char(20) DEFAULT NULL,
3858
`f_char2` char(20) DEFAULT NULL,
3859
`f_charbig` varchar(1000) DEFAULT NULL
3860
) ENGINE=InnoDB DEFAULT CHARSET=latin1
3861
/*!50100 PARTITION BY RANGE (f_int1)
3862
SUBPARTITION BY HASH (f_int1)
3863
(PARTITION part1 VALUES LESS THAN (10)
3864
(SUBPARTITION subpart11 ENGINE = InnoDB,
3865
SUBPARTITION subpart12 ENGINE = InnoDB),
3866
PARTITION part2 VALUES LESS THAN (2147483646)
3867
(SUBPARTITION subpart21 ENGINE = InnoDB,
3868
SUBPARTITION subpart22 ENGINE = InnoDB)) */
3870
# check prerequisites-1 success: 1
3871
# check COUNT(*) success: 1
3872
# check MIN/MAX(f_int1) success: 1
3873
# check MIN/MAX(f_int2) success: 1
3874
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3875
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3876
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
3877
WHERE f_int1 IN (2,3);
3878
# check prerequisites-3 success: 1
3879
DELETE FROM t1 WHERE f_charbig = 'delete me';
3880
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
3881
# check read via f_int1 success: 1
3882
# check read via f_int2 success: 1
3884
# check multiple-1 success: 1
3885
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
3887
# check multiple-2 success: 1
3888
INSERT INTO t1 SELECT * FROM t0_template
3889
WHERE MOD(f_int1,3) = 0;
3891
# check multiple-3 success: 1
3892
UPDATE t1 SET f_int1 = f_int1 + @max_row
3893
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
3894
AND @max_row_div2 + @max_row_div4;
3896
# check multiple-4 success: 1
3898
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
3899
AND @max_row_div2 + @max_row_div4 + @max_row;
3901
# check multiple-5 success: 1
3902
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
3904
SET f_int1 = @cur_value , f_int2 = @cur_value,
3905
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3906
f_charbig = '#SINGLE#';
3908
# check single-1 success: 1
3909
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
3911
SET f_int1 = @cur_value , f_int2 = @cur_value,
3912
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3913
f_charbig = '#SINGLE#';
3915
# check single-2 success: 1
3916
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
3917
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
3918
UPDATE t1 SET f_int1 = @cur_value2
3919
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
3921
# check single-3 success: 1
3922
SET @cur_value1= -1;
3923
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
3924
UPDATE t1 SET f_int1 = @cur_value1
3925
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
3927
# check single-4 success: 1
3928
SELECT MAX(f_int1) INTO @cur_value FROM t1;
3929
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
3931
# check single-5 success: 1
3932
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
3934
# check single-6 success: 1
3935
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3936
ERROR HY000: Table has no partition for value 2147483647
3937
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3938
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3939
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3940
f_charbig = '#NULL#';
3942
SET f_int1 = NULL , f_int2 = -@max_row,
3943
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3944
f_charbig = '#NULL#';
3945
# check null success: 1
3947
# check null-1 success: 1
3948
UPDATE t1 SET f_int1 = -@max_row
3949
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3950
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3952
# check null-2 success: 1
3953
UPDATE t1 SET f_int1 = NULL
3954
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3955
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3957
# check null-3 success: 1
3959
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3960
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3962
# check null-4 success: 1
3964
WHERE f_int1 = 0 AND f_int2 = 0
3965
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
3966
AND f_charbig = '#NULL#';
3968
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3969
SELECT f_int1, f_int1, '', '', 'was inserted'
3970
FROM t0_template source_tab
3971
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3973
# check transactions-1 success: 1
3976
# check transactions-2 success: 1
3979
# check transactions-3 success: 1
3980
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3984
# check transactions-4 success: 1
3985
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3986
SELECT f_int1, f_int1, '', '', 'was inserted'
3987
FROM t0_template source_tab
3988
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3990
# check transactions-5 success: 1
3993
# check transactions-6 success: 1
3994
# INFO: Storage engine used for t1 seems to be transactional.
3997
# check transactions-7 success: 1
3998
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4000
SET @@session.sql_mode = 'traditional';
4001
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
4002
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4003
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
4004
'', '', 'was inserted' FROM t0_template
4005
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4006
ERROR 22012: Division by 0
4009
# check transactions-8 success: 1
4010
# INFO: Storage engine used for t1 seems to be able to revert
4011
# changes made by the failing statement.
4012
SET @@session.sql_mode = '';
4014
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4016
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4018
# check special-1 success: 1
4019
UPDATE t1 SET f_charbig = '';
4021
# check special-2 success: 1
4022
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4023
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4024
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4025
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4026
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4027
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4028
'just inserted' FROM t0_template
4029
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4030
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
4032
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4033
f_charbig = 'updated by trigger'
4034
WHERE f_int1 = new.f_int1;
4036
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4037
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4038
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4040
# check trigger-1 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 INSERT 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
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4059
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4060
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4062
# check trigger-2 success: 1
4064
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4065
f_int2 = CAST(f_char1 AS SIGNED INT),
4066
f_charbig = 'just inserted'
4067
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4069
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4070
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4071
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4072
'just inserted' FROM t0_template
4073
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4074
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4076
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4077
f_charbig = 'updated by trigger'
4078
WHERE f_int1 = new.f_int1;
4080
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4081
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4083
# check trigger-3 success: 1
4085
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4086
f_int2 = CAST(f_char1 AS SIGNED INT),
4087
f_charbig = 'just inserted'
4088
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4090
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4091
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4092
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4093
'just inserted' FROM t0_template
4094
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4095
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4097
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4098
f_charbig = 'updated by trigger'
4099
WHERE f_int1 = - old.f_int1;
4101
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4102
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4104
# check trigger-4 success: 1
4106
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4107
f_int2 = CAST(f_char1 AS SIGNED INT),
4108
f_charbig = 'just inserted'
4109
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4111
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4112
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4113
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4114
'just inserted' FROM t0_template
4115
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4116
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4118
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4119
f_charbig = 'updated by trigger'
4120
WHERE f_int1 = new.f_int1;
4122
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4123
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4125
# check trigger-5 success: 1
4127
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4128
f_int2 = CAST(f_char1 AS SIGNED INT),
4129
f_charbig = 'just inserted'
4130
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4132
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4133
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4134
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4135
'just inserted' FROM t0_template
4136
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4137
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4139
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4140
f_charbig = 'updated by trigger'
4141
WHERE f_int1 = - old.f_int1;
4143
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4144
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4146
# check trigger-6 success: 1
4148
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4149
f_int2 = CAST(f_char1 AS SIGNED INT),
4150
f_charbig = 'just inserted'
4151
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4153
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4154
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4155
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4156
'just inserted' FROM t0_template
4157
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4158
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
4160
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4161
f_charbig = 'updated by trigger'
4162
WHERE f_int1 = - old.f_int1;
4165
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4167
# check trigger-7 success: 1
4169
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4170
f_int2 = CAST(f_char1 AS SIGNED INT),
4171
f_charbig = 'just inserted'
4172
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4174
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4175
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4176
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4177
'just inserted' FROM t0_template
4178
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4179
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
4181
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4182
f_charbig = 'updated by trigger'
4183
WHERE f_int1 = - old.f_int1;
4186
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4188
# check trigger-8 success: 1
4190
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4191
f_int2 = CAST(f_char1 AS SIGNED INT),
4192
f_charbig = 'just inserted'
4193
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4195
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4197
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4198
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4200
SET new.f_int1 = old.f_int1 + @max_row,
4201
new.f_int2 = old.f_int2 - @max_row,
4202
new.f_charbig = '####updated per update trigger####';
4205
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4206
f_charbig = '####updated per update statement itself####';
4208
# check trigger-9 success: 1
4210
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4211
f_int2 = CAST(f_char1 AS SIGNED INT),
4212
f_charbig = CONCAT('===',f_char1,'===');
4213
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4215
SET new.f_int1 = new.f_int1 + @max_row,
4216
new.f_int2 = new.f_int2 - @max_row,
4217
new.f_charbig = '####updated per update trigger####';
4220
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4221
f_charbig = '####updated per update statement itself####';
4223
# check trigger-10 success: 1
4225
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4226
f_int2 = CAST(f_char1 AS SIGNED INT),
4227
f_charbig = CONCAT('===',f_char1,'===');
4228
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4230
SET new.f_int1 = @my_max1 + @counter,
4231
new.f_int2 = @my_min2 - @counter,
4232
new.f_charbig = '####updated per insert trigger####';
4233
SET @counter = @counter + 1;
4236
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4237
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4238
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4239
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4240
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4244
# check trigger-11 success: 1
4246
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4247
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4248
AND f_charbig = '####updated per insert trigger####';
4249
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4251
SET new.f_int1 = @my_max1 + @counter,
4252
new.f_int2 = @my_min2 - @counter,
4253
new.f_charbig = '####updated per insert trigger####';
4254
SET @counter = @counter + 1;
4257
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4258
INSERT INTO t1 (f_char1, f_char2, f_charbig)
4259
SELECT CAST(f_int1 AS CHAR),
4260
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4261
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4265
# check trigger-12 success: 1
4267
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4268
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4269
AND f_charbig = '####updated per insert trigger####';
4271
Table Op Msg_type Msg_text
4272
test.t1 analyze status OK
4273
CHECK TABLE t1 EXTENDED;
4274
Table Op Msg_type Msg_text
4275
test.t1 check status OK
4276
CHECKSUM TABLE t1 EXTENDED;
4278
test.t1 <some_value>
4280
Table Op Msg_type Msg_text
4281
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
4282
test.t1 optimize status OK
4283
# check layout success: 1
4284
REPAIR TABLE t1 EXTENDED;
4285
Table Op Msg_type Msg_text
4286
test.t1 repair note The storage engine for the table doesn't support repair
4287
# check layout success: 1
4290
# check TRUNCATE success: 1
4291
# check layout success: 1
4292
# End usability test (inc/partition_check.inc)
4294
#------------------------------------------------------------------------
4295
# 6 Session default engine differs from engine used within create table
4296
#------------------------------------------------------------------------
4297
SET SESSION storage_engine='MEMORY';
4303
f_charbig VARCHAR(1000)
4305
PARTITION BY HASH(f_int1) ( PARTITION part1 ENGINE = 'InnoDB');
4306
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4307
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
4308
# Start usability test (inc/partition_check.inc)
4310
SHOW CREATE TABLE t1;
4312
t1 CREATE TABLE `t1` (
4313
`f_int1` int(11) DEFAULT NULL,
4314
`f_int2` int(11) DEFAULT NULL,
4315
`f_char1` char(20) DEFAULT NULL,
4316
`f_char2` char(20) DEFAULT NULL,
4317
`f_charbig` varchar(1000) DEFAULT NULL
4318
) ENGINE=InnoDB DEFAULT CHARSET=latin1
4319
/*!50100 PARTITION BY HASH (f_int1)
4320
(PARTITION part1 ENGINE = InnoDB) */
4322
# check prerequisites-1 success: 1
4323
# check COUNT(*) success: 1
4324
# check MIN/MAX(f_int1) success: 1
4325
# check MIN/MAX(f_int2) success: 1
4326
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4327
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4328
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
4329
WHERE f_int1 IN (2,3);
4330
# check prerequisites-3 success: 1
4331
DELETE FROM t1 WHERE f_charbig = 'delete me';
4332
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
4333
# check read via f_int1 success: 1
4334
# check read via f_int2 success: 1
4336
# check multiple-1 success: 1
4337
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
4339
# check multiple-2 success: 1
4340
INSERT INTO t1 SELECT * FROM t0_template
4341
WHERE MOD(f_int1,3) = 0;
4343
# check multiple-3 success: 1
4344
UPDATE t1 SET f_int1 = f_int1 + @max_row
4345
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
4346
AND @max_row_div2 + @max_row_div4;
4348
# check multiple-4 success: 1
4350
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
4351
AND @max_row_div2 + @max_row_div4 + @max_row;
4353
# check multiple-5 success: 1
4354
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
4356
SET f_int1 = @cur_value , f_int2 = @cur_value,
4357
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4358
f_charbig = '#SINGLE#';
4360
# check single-1 success: 1
4361
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
4363
SET f_int1 = @cur_value , f_int2 = @cur_value,
4364
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4365
f_charbig = '#SINGLE#';
4367
# check single-2 success: 1
4368
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
4369
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
4370
UPDATE t1 SET f_int1 = @cur_value2
4371
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
4373
# check single-3 success: 1
4374
SET @cur_value1= -1;
4375
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
4376
UPDATE t1 SET f_int1 = @cur_value1
4377
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
4379
# check single-4 success: 1
4380
SELECT MAX(f_int1) INTO @cur_value FROM t1;
4381
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
4383
# check single-5 success: 1
4384
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
4386
# check single-6 success: 1
4387
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
4389
# check single-7 success: 1
4390
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
4391
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
4392
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
4393
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
4394
f_charbig = '#NULL#';
4396
SET f_int1 = NULL , f_int2 = -@max_row,
4397
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
4398
f_charbig = '#NULL#';
4399
# check null success: 1
4401
# check null-1 success: 1
4402
UPDATE t1 SET f_int1 = -@max_row
4403
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4404
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4406
# check null-2 success: 1
4407
UPDATE t1 SET f_int1 = NULL
4408
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4409
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4411
# check null-3 success: 1
4413
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4414
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4416
# check null-4 success: 1
4418
WHERE f_int1 = 0 AND f_int2 = 0
4419
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
4420
AND f_charbig = '#NULL#';
4422
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4423
SELECT f_int1, f_int1, '', '', 'was inserted'
4424
FROM t0_template source_tab
4425
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4427
# check transactions-1 success: 1
4430
# check transactions-2 success: 1
4433
# check transactions-3 success: 1
4434
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4438
# check transactions-4 success: 1
4439
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4440
SELECT f_int1, f_int1, '', '', 'was inserted'
4441
FROM t0_template source_tab
4442
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4444
# check transactions-5 success: 1
4447
# check transactions-6 success: 1
4448
# INFO: Storage engine used for t1 seems to be transactional.
4451
# check transactions-7 success: 1
4452
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4454
SET @@session.sql_mode = 'traditional';
4455
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
4456
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4457
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
4458
'', '', 'was inserted' FROM t0_template
4459
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4460
ERROR 22012: Division by 0
4463
# check transactions-8 success: 1
4464
# INFO: Storage engine used for t1 seems to be able to revert
4465
# changes made by the failing statement.
4466
SET @@session.sql_mode = '';
4468
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4470
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4472
# check special-1 success: 1
4473
UPDATE t1 SET f_charbig = '';
4475
# check special-2 success: 1
4476
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4477
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4478
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4479
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4480
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4481
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4482
'just inserted' FROM t0_template
4483
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4484
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
4486
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4487
f_charbig = 'updated by trigger'
4488
WHERE f_int1 = new.f_int1;
4490
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4491
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4492
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4494
# check trigger-1 success: 1
4496
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4497
f_int2 = CAST(f_char1 AS SIGNED INT),
4498
f_charbig = 'just inserted'
4499
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4501
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4502
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4503
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4504
'just inserted' FROM t0_template
4505
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4506
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
4508
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4509
f_charbig = 'updated by trigger'
4510
WHERE f_int1 = new.f_int1;
4512
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4513
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4514
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4516
# check trigger-2 success: 1
4518
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4519
f_int2 = CAST(f_char1 AS SIGNED INT),
4520
f_charbig = 'just inserted'
4521
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4523
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4524
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4525
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4526
'just inserted' FROM t0_template
4527
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4528
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4530
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4531
f_charbig = 'updated by trigger'
4532
WHERE f_int1 = new.f_int1;
4534
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4535
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4537
# check trigger-3 success: 1
4539
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4540
f_int2 = CAST(f_char1 AS SIGNED INT),
4541
f_charbig = 'just inserted'
4542
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4544
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4545
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4546
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4547
'just inserted' FROM t0_template
4548
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4549
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4551
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4552
f_charbig = 'updated by trigger'
4553
WHERE f_int1 = - old.f_int1;
4555
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4556
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4558
# check trigger-4 success: 1
4560
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4561
f_int2 = CAST(f_char1 AS SIGNED INT),
4562
f_charbig = 'just inserted'
4563
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4565
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4566
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4567
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4568
'just inserted' FROM t0_template
4569
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4570
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4572
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4573
f_charbig = 'updated by trigger'
4574
WHERE f_int1 = new.f_int1;
4576
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4577
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4579
# check trigger-5 success: 1
4581
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4582
f_int2 = CAST(f_char1 AS SIGNED INT),
4583
f_charbig = 'just inserted'
4584
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4586
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4587
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4588
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4589
'just inserted' FROM t0_template
4590
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4591
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4593
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4594
f_charbig = 'updated by trigger'
4595
WHERE f_int1 = - old.f_int1;
4597
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4598
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4600
# check trigger-6 success: 1
4602
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4603
f_int2 = CAST(f_char1 AS SIGNED INT),
4604
f_charbig = 'just inserted'
4605
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4607
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4608
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4609
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4610
'just inserted' FROM t0_template
4611
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4612
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
4614
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4615
f_charbig = 'updated by trigger'
4616
WHERE f_int1 = - old.f_int1;
4619
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4621
# check trigger-7 success: 1
4623
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4624
f_int2 = CAST(f_char1 AS SIGNED INT),
4625
f_charbig = 'just inserted'
4626
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4628
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4629
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4630
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4631
'just inserted' FROM t0_template
4632
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4633
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
4635
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4636
f_charbig = 'updated by trigger'
4637
WHERE f_int1 = - old.f_int1;
4640
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4642
# check trigger-8 success: 1
4644
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4645
f_int2 = CAST(f_char1 AS SIGNED INT),
4646
f_charbig = 'just inserted'
4647
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4649
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4651
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4652
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4654
SET new.f_int1 = old.f_int1 + @max_row,
4655
new.f_int2 = old.f_int2 - @max_row,
4656
new.f_charbig = '####updated per update trigger####';
4659
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4660
f_charbig = '####updated per update statement itself####';
4662
# check trigger-9 success: 1
4664
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4665
f_int2 = CAST(f_char1 AS SIGNED INT),
4666
f_charbig = CONCAT('===',f_char1,'===');
4667
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4669
SET new.f_int1 = new.f_int1 + @max_row,
4670
new.f_int2 = new.f_int2 - @max_row,
4671
new.f_charbig = '####updated per update trigger####';
4674
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4675
f_charbig = '####updated per update statement itself####';
4677
# check trigger-10 success: 1
4679
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4680
f_int2 = CAST(f_char1 AS SIGNED INT),
4681
f_charbig = CONCAT('===',f_char1,'===');
4682
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4684
SET new.f_int1 = @my_max1 + @counter,
4685
new.f_int2 = @my_min2 - @counter,
4686
new.f_charbig = '####updated per insert trigger####';
4687
SET @counter = @counter + 1;
4690
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4691
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4692
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4693
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4694
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4698
# check trigger-11 success: 1
4700
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4701
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4702
AND f_charbig = '####updated per insert trigger####';
4703
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4705
SET new.f_int1 = @my_max1 + @counter,
4706
new.f_int2 = @my_min2 - @counter,
4707
new.f_charbig = '####updated per insert trigger####';
4708
SET @counter = @counter + 1;
4711
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4712
INSERT INTO t1 (f_char1, f_char2, f_charbig)
4713
SELECT CAST(f_int1 AS CHAR),
4714
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4715
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4719
# check trigger-12 success: 1
4721
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4722
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4723
AND f_charbig = '####updated per insert trigger####';
4725
Table Op Msg_type Msg_text
4726
test.t1 analyze status OK
4727
CHECK TABLE t1 EXTENDED;
4728
Table Op Msg_type Msg_text
4729
test.t1 check status OK
4730
CHECKSUM TABLE t1 EXTENDED;
4732
test.t1 <some_value>
4734
Table Op Msg_type Msg_text
4735
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
4736
test.t1 optimize status OK
4737
# check layout success: 1
4738
REPAIR TABLE t1 EXTENDED;
4739
Table Op Msg_type Msg_text
4740
test.t1 repair note The storage engine for the table doesn't support repair
4741
# check layout success: 1
4744
# check TRUNCATE success: 1
4745
# check layout success: 1
4746
# End usability test (inc/partition_check.inc)
4753
f_charbig VARCHAR(1000)
4755
PARTITION BY RANGE(f_int1)
4756
SUBPARTITION BY HASH(f_int1)
4757
( PARTITION part1 VALUES LESS THAN (1000)
4758
(SUBPARTITION subpart11 STORAGE ENGINE = 'InnoDB',
4759
SUBPARTITION subpart12 STORAGE ENGINE = 'InnoDB'));
4760
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4761
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
4762
# Start usability test (inc/partition_check.inc)
4764
SHOW CREATE TABLE t1;
4766
t1 CREATE TABLE `t1` (
4767
`f_int1` int(11) DEFAULT NULL,
4768
`f_int2` int(11) DEFAULT NULL,
4769
`f_char1` char(20) DEFAULT NULL,
4770
`f_char2` char(20) DEFAULT NULL,
4771
`f_charbig` varchar(1000) DEFAULT NULL
4772
) ENGINE=InnoDB DEFAULT CHARSET=latin1
4773
/*!50100 PARTITION BY RANGE (f_int1)
4774
SUBPARTITION BY HASH (f_int1)
4775
(PARTITION part1 VALUES LESS THAN (1000)
4776
(SUBPARTITION subpart11 ENGINE = InnoDB,
4777
SUBPARTITION subpart12 ENGINE = InnoDB)) */
4779
# check prerequisites-1 success: 1
4780
# check COUNT(*) success: 1
4781
# check MIN/MAX(f_int1) success: 1
4782
# check MIN/MAX(f_int2) success: 1
4783
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4784
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4785
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
4786
WHERE f_int1 IN (2,3);
4787
# check prerequisites-3 success: 1
4788
DELETE FROM t1 WHERE f_charbig = 'delete me';
4789
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
4790
# check read via f_int1 success: 1
4791
# check read via f_int2 success: 1
4793
# check multiple-1 success: 1
4794
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
4796
# check multiple-2 success: 1
4797
INSERT INTO t1 SELECT * FROM t0_template
4798
WHERE MOD(f_int1,3) = 0;
4800
# check multiple-3 success: 1
4801
UPDATE t1 SET f_int1 = f_int1 + @max_row
4802
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
4803
AND @max_row_div2 + @max_row_div4;
4805
# check multiple-4 success: 1
4807
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
4808
AND @max_row_div2 + @max_row_div4 + @max_row;
4810
# check multiple-5 success: 1
4811
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
4813
SET f_int1 = @cur_value , f_int2 = @cur_value,
4814
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4815
f_charbig = '#SINGLE#';
4817
# check single-1 success: 1
4818
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
4820
SET f_int1 = @cur_value , f_int2 = @cur_value,
4821
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4822
f_charbig = '#SINGLE#';
4824
# check single-2 success: 1
4825
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
4826
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
4827
UPDATE t1 SET f_int1 = @cur_value2
4828
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
4830
# check single-3 success: 1
4831
SET @cur_value1= -1;
4832
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
4833
UPDATE t1 SET f_int1 = @cur_value1
4834
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
4836
# check single-4 success: 1
4837
SELECT MAX(f_int1) INTO @cur_value FROM t1;
4838
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
4840
# check single-5 success: 1
4841
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
4843
# check single-6 success: 1
4844
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
4845
ERROR HY000: Table has no partition for value 2147483647
4846
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
4847
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
4848
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
4849
f_charbig = '#NULL#';
4851
SET f_int1 = NULL , f_int2 = -@max_row,
4852
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
4853
f_charbig = '#NULL#';
4854
# check null success: 1
4856
# check null-1 success: 1
4857
UPDATE t1 SET f_int1 = -@max_row
4858
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4859
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4861
# check null-2 success: 1
4862
UPDATE t1 SET f_int1 = NULL
4863
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4864
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4866
# check null-3 success: 1
4868
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4869
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4871
# check null-4 success: 1
4873
WHERE f_int1 = 0 AND f_int2 = 0
4874
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
4875
AND f_charbig = '#NULL#';
4877
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4878
SELECT f_int1, f_int1, '', '', 'was inserted'
4879
FROM t0_template source_tab
4880
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4882
# check transactions-1 success: 1
4885
# check transactions-2 success: 1
4888
# check transactions-3 success: 1
4889
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4893
# check transactions-4 success: 1
4894
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4895
SELECT f_int1, f_int1, '', '', 'was inserted'
4896
FROM t0_template source_tab
4897
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4899
# check transactions-5 success: 1
4902
# check transactions-6 success: 1
4903
# INFO: Storage engine used for t1 seems to be transactional.
4906
# check transactions-7 success: 1
4907
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4909
SET @@session.sql_mode = 'traditional';
4910
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
4911
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4912
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
4913
'', '', 'was inserted' FROM t0_template
4914
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4915
ERROR 22012: Division by 0
4918
# check transactions-8 success: 1
4919
# INFO: Storage engine used for t1 seems to be able to revert
4920
# changes made by the failing statement.
4921
SET @@session.sql_mode = '';
4923
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4925
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4927
# check special-1 success: 1
4928
UPDATE t1 SET f_charbig = '';
4930
# check special-2 success: 1
4931
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4932
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4933
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4934
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4935
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4936
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4937
'just inserted' FROM t0_template
4938
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4939
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
4941
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4942
f_charbig = 'updated by trigger'
4943
WHERE f_int1 = new.f_int1;
4945
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4946
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4947
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4949
# check trigger-1 success: 1
4951
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4952
f_int2 = CAST(f_char1 AS SIGNED INT),
4953
f_charbig = 'just inserted'
4954
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4956
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4957
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4958
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4959
'just inserted' FROM t0_template
4960
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4961
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
4963
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4964
f_charbig = 'updated by trigger'
4965
WHERE f_int1 = new.f_int1;
4967
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4968
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4969
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4971
# check trigger-2 success: 1
4973
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4974
f_int2 = CAST(f_char1 AS SIGNED INT),
4975
f_charbig = 'just inserted'
4976
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4978
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4979
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4980
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4981
'just inserted' FROM t0_template
4982
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4983
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4985
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4986
f_charbig = 'updated by trigger'
4987
WHERE f_int1 = new.f_int1;
4989
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4990
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4992
# check trigger-3 success: 1
4994
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4995
f_int2 = CAST(f_char1 AS SIGNED INT),
4996
f_charbig = 'just inserted'
4997
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4999
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5000
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5001
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5002
'just inserted' FROM t0_template
5003
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5004
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
5006
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5007
f_charbig = 'updated by trigger'
5008
WHERE f_int1 = - old.f_int1;
5010
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5011
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5013
# check trigger-4 success: 1
5015
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5016
f_int2 = CAST(f_char1 AS SIGNED INT),
5017
f_charbig = 'just inserted'
5018
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5020
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5021
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5022
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5023
'just inserted' FROM t0_template
5024
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5025
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
5027
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5028
f_charbig = 'updated by trigger'
5029
WHERE f_int1 = new.f_int1;
5031
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5032
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5034
# check trigger-5 success: 1
5036
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5037
f_int2 = CAST(f_char1 AS SIGNED INT),
5038
f_charbig = 'just inserted'
5039
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5041
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5042
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5043
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5044
'just inserted' FROM t0_template
5045
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5046
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
5048
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5049
f_charbig = 'updated by trigger'
5050
WHERE f_int1 = - old.f_int1;
5052
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5053
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5055
# check trigger-6 success: 1
5057
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5058
f_int2 = CAST(f_char1 AS SIGNED INT),
5059
f_charbig = 'just inserted'
5060
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5062
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5063
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5064
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5065
'just inserted' FROM t0_template
5066
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5067
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
5069
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5070
f_charbig = 'updated by trigger'
5071
WHERE f_int1 = - old.f_int1;
5074
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5076
# check trigger-7 success: 1
5078
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5079
f_int2 = CAST(f_char1 AS SIGNED INT),
5080
f_charbig = 'just inserted'
5081
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5083
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5084
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5085
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5086
'just inserted' FROM t0_template
5087
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5088
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
5090
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5091
f_charbig = 'updated by trigger'
5092
WHERE f_int1 = - old.f_int1;
5095
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5097
# check trigger-8 success: 1
5099
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5100
f_int2 = CAST(f_char1 AS SIGNED INT),
5101
f_charbig = 'just inserted'
5102
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5104
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5106
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5107
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
5109
SET new.f_int1 = old.f_int1 + @max_row,
5110
new.f_int2 = old.f_int2 - @max_row,
5111
new.f_charbig = '####updated per update trigger####';
5114
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
5115
f_charbig = '####updated per update statement itself####';
5117
# check trigger-9 success: 1
5119
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5120
f_int2 = CAST(f_char1 AS SIGNED INT),
5121
f_charbig = CONCAT('===',f_char1,'===');
5122
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
5124
SET new.f_int1 = new.f_int1 + @max_row,
5125
new.f_int2 = new.f_int2 - @max_row,
5126
new.f_charbig = '####updated per update trigger####';
5129
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
5130
f_charbig = '####updated per update statement itself####';
5132
# check trigger-10 success: 1
5134
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5135
f_int2 = CAST(f_char1 AS SIGNED INT),
5136
f_charbig = CONCAT('===',f_char1,'===');
5137
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
5139
SET new.f_int1 = @my_max1 + @counter,
5140
new.f_int2 = @my_min2 - @counter,
5141
new.f_charbig = '####updated per insert trigger####';
5142
SET @counter = @counter + 1;
5145
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
5146
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
5147
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
5148
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
5149
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
5153
# check trigger-11 success: 1
5155
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
5156
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
5157
AND f_charbig = '####updated per insert trigger####';
5158
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
5160
SET new.f_int1 = @my_max1 + @counter,
5161
new.f_int2 = @my_min2 - @counter,
5162
new.f_charbig = '####updated per insert trigger####';
5163
SET @counter = @counter + 1;
5166
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
5167
INSERT INTO t1 (f_char1, f_char2, f_charbig)
5168
SELECT CAST(f_int1 AS CHAR),
5169
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
5170
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
5174
# check trigger-12 success: 1
5176
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
5177
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
5178
AND f_charbig = '####updated per insert trigger####';
5180
Table Op Msg_type Msg_text
5181
test.t1 analyze status OK
5182
CHECK TABLE t1 EXTENDED;
5183
Table Op Msg_type Msg_text
5184
test.t1 check status OK
5185
CHECKSUM TABLE t1 EXTENDED;
5187
test.t1 <some_value>
5189
Table Op Msg_type Msg_text
5190
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
5191
test.t1 optimize status OK
5192
# check layout success: 1
5193
REPAIR TABLE t1 EXTENDED;
5194
Table Op Msg_type Msg_text
5195
test.t1 repair note The storage engine for the table doesn't support repair
5196
# check layout success: 1
5199
# check TRUNCATE success: 1
5200
# check layout success: 1
5201
# End usability test (inc/partition_check.inc)
5203
SET SESSION storage_engine='InnoDB';
5204
DROP VIEW IF EXISTS v1;
5205
DROP TABLE IF EXISTS t1;
5206
DROP TABLE IF EXISTS t0_aux;
5207
DROP TABLE IF EXISTS t0_definition;
5208
DROP TABLE IF EXISTS t0_template;