2
SET @@session.storage_engine = 'MyISAM';
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=MyISAM DEFAULT CHARSET=latin1
71
/*!50100 PARTITION BY HASH (f_int1)
82
# check prerequisites-1 success: 1
83
# check COUNT(*) success: 1
84
# check MIN/MAX(f_int1) success: 1
85
# check MIN/MAX(f_int2) success: 1
86
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
87
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
88
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
89
WHERE f_int1 IN (2,3);
90
# check prerequisites-3 success: 1
91
DELETE FROM t1 WHERE f_charbig = 'delete me';
92
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
93
# check read via f_int1 success: 1
94
# check read via f_int2 success: 1
96
# check multiple-1 success: 1
97
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
99
# check multiple-2 success: 1
100
INSERT INTO t1 SELECT * FROM t0_template
101
WHERE MOD(f_int1,3) = 0;
103
# check multiple-3 success: 1
104
UPDATE t1 SET f_int1 = f_int1 + @max_row
105
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
106
AND @max_row_div2 + @max_row_div4;
108
# check multiple-4 success: 1
110
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
111
AND @max_row_div2 + @max_row_div4 + @max_row;
113
# check multiple-5 success: 1
114
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
116
SET f_int1 = @cur_value , f_int2 = @cur_value,
117
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
118
f_charbig = '#SINGLE#';
120
# check single-1 success: 1
121
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
123
SET f_int1 = @cur_value , f_int2 = @cur_value,
124
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
125
f_charbig = '#SINGLE#';
127
# check single-2 success: 1
128
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
129
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
130
UPDATE t1 SET f_int1 = @cur_value2
131
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
133
# check single-3 success: 1
135
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
136
UPDATE t1 SET f_int1 = @cur_value1
137
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
139
# check single-4 success: 1
140
SELECT MAX(f_int1) INTO @cur_value FROM t1;
141
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
143
# check single-5 success: 1
144
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
146
# check single-6 success: 1
147
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
149
# check single-7 success: 1
150
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
151
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
152
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
153
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
154
f_charbig = '#NULL#';
156
SET f_int1 = NULL , f_int2 = -@max_row,
157
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
158
f_charbig = '#NULL#';
159
# check null success: 1
161
# check null-1 success: 1
162
UPDATE t1 SET f_int1 = -@max_row
163
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
164
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
166
# check null-2 success: 1
167
UPDATE t1 SET f_int1 = NULL
168
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
169
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
171
# check null-3 success: 1
173
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
174
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
176
# check null-4 success: 1
178
WHERE f_int1 = 0 AND f_int2 = 0
179
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
180
AND f_charbig = '#NULL#';
182
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
183
SELECT f_int1, f_int1, '', '', 'was inserted'
184
FROM t0_template source_tab
185
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
187
# check transactions-1 success: 1
190
# check transactions-2 success: 1
193
# check transactions-3 success: 1
194
DELETE FROM t1 WHERE f_charbig = 'was inserted';
198
# check transactions-4 success: 1
199
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
200
SELECT f_int1, f_int1, '', '', 'was inserted'
201
FROM t0_template source_tab
202
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
204
# check transactions-5 success: 1
207
Warning 1196 Some non-transactional changed tables couldn't be rolled back
209
# check transactions-6 success: 1
210
# INFO: Storage engine used for t1 seems to be not transactional.
213
# check transactions-7 success: 1
214
DELETE FROM t1 WHERE f_charbig = 'was inserted';
216
SET @@session.sql_mode = 'traditional';
217
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
218
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
219
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
220
'', '', 'was inserted' FROM t0_template
221
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
222
ERROR 22012: Division by 0
225
# check transactions-8 success: 1
226
# INFO: Storage engine used for t1 seems to be unable to revert
227
# changes made by the failing statement.
228
SET @@session.sql_mode = '';
230
DELETE FROM t1 WHERE f_charbig = 'was inserted';
232
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
234
# check special-1 success: 1
235
UPDATE t1 SET f_charbig = '';
237
# check special-2 success: 1
238
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
239
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
240
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
241
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
242
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
243
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
244
'just inserted' FROM t0_template
245
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
246
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
248
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
249
f_charbig = 'updated by trigger'
250
WHERE f_int1 = new.f_int1;
252
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
253
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
254
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
256
# check trigger-1 success: 1
258
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
259
f_int2 = CAST(f_char1 AS SIGNED INT),
260
f_charbig = 'just inserted'
261
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
263
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
264
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
265
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
266
'just inserted' FROM t0_template
267
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
268
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
270
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
271
f_charbig = 'updated by trigger'
272
WHERE f_int1 = new.f_int1;
274
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
275
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
276
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
278
# check trigger-2 success: 1
280
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
281
f_int2 = CAST(f_char1 AS SIGNED INT),
282
f_charbig = 'just inserted'
283
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
285
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
286
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
287
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
288
'just inserted' FROM t0_template
289
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
290
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
292
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
293
f_charbig = 'updated by trigger'
294
WHERE f_int1 = new.f_int1;
296
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
297
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
299
# check trigger-3 success: 1
301
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
302
f_int2 = CAST(f_char1 AS SIGNED INT),
303
f_charbig = 'just inserted'
304
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
306
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
307
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
308
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
309
'just inserted' FROM t0_template
310
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
311
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
313
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
314
f_charbig = 'updated by trigger'
315
WHERE f_int1 = - old.f_int1;
317
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
318
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
320
# check trigger-4 success: 1
322
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
323
f_int2 = CAST(f_char1 AS SIGNED INT),
324
f_charbig = 'just inserted'
325
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
327
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
328
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
329
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
330
'just inserted' FROM t0_template
331
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
332
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
334
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
335
f_charbig = 'updated by trigger'
336
WHERE f_int1 = new.f_int1;
338
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
339
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
341
# check trigger-5 success: 1
343
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
344
f_int2 = CAST(f_char1 AS SIGNED INT),
345
f_charbig = 'just inserted'
346
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
348
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
349
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
350
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
351
'just inserted' FROM t0_template
352
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
353
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
355
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
356
f_charbig = 'updated by trigger'
357
WHERE f_int1 = - old.f_int1;
359
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
360
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
362
# check trigger-6 success: 1
364
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
365
f_int2 = CAST(f_char1 AS SIGNED INT),
366
f_charbig = 'just inserted'
367
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
369
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
370
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
371
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
372
'just inserted' FROM t0_template
373
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
374
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
376
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
377
f_charbig = 'updated by trigger'
378
WHERE f_int1 = - old.f_int1;
381
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
383
# check trigger-7 success: 1
385
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
386
f_int2 = CAST(f_char1 AS SIGNED INT),
387
f_charbig = 'just inserted'
388
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
390
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
391
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
392
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
393
'just inserted' FROM t0_template
394
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
395
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
397
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
398
f_charbig = 'updated by trigger'
399
WHERE f_int1 = - old.f_int1;
402
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
404
# check trigger-8 success: 1
406
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
407
f_int2 = CAST(f_char1 AS SIGNED INT),
408
f_charbig = 'just inserted'
409
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
411
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
413
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
414
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
416
SET new.f_int1 = old.f_int1 + @max_row,
417
new.f_int2 = old.f_int2 - @max_row,
418
new.f_charbig = '####updated per update trigger####';
421
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
422
f_charbig = '####updated per update statement itself####';
424
# check trigger-9 success: 1
426
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
427
f_int2 = CAST(f_char1 AS SIGNED INT),
428
f_charbig = CONCAT('===',f_char1,'===');
429
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
431
SET new.f_int1 = new.f_int1 + @max_row,
432
new.f_int2 = new.f_int2 - @max_row,
433
new.f_charbig = '####updated per update trigger####';
436
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
437
f_charbig = '####updated per update statement itself####';
439
# check trigger-10 success: 1
441
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
442
f_int2 = CAST(f_char1 AS SIGNED INT),
443
f_charbig = CONCAT('===',f_char1,'===');
444
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
446
SET new.f_int1 = @my_max1 + @counter,
447
new.f_int2 = @my_min2 - @counter,
448
new.f_charbig = '####updated per insert trigger####';
449
SET @counter = @counter + 1;
452
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
453
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
454
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
455
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
456
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
460
# check trigger-11 success: 1
462
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
463
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
464
AND f_charbig = '####updated per insert trigger####';
465
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
467
SET new.f_int1 = @my_max1 + @counter,
468
new.f_int2 = @my_min2 - @counter,
469
new.f_charbig = '####updated per insert trigger####';
470
SET @counter = @counter + 1;
473
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
474
INSERT INTO t1 (f_char1, f_char2, f_charbig)
475
SELECT CAST(f_int1 AS CHAR),
476
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
477
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
481
# check trigger-12 success: 1
483
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
484
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
485
AND f_charbig = '####updated per insert trigger####';
487
Table Op Msg_type Msg_text
488
test.t1 analyze status OK
489
CHECK TABLE t1 EXTENDED;
490
Table Op Msg_type Msg_text
491
test.t1 check status OK
492
CHECKSUM TABLE t1 EXTENDED;
496
Table Op Msg_type Msg_text
497
test.t1 optimize status OK
498
# check layout success: 1
499
REPAIR TABLE t1 EXTENDED;
500
Table Op Msg_type Msg_text
501
test.t1 repair status OK
502
# check layout success: 1
505
# check TRUNCATE success: 1
506
# check layout success: 1
507
# End usability test (inc/partition_check.inc)
509
#------------------------------------------------------------------------
510
# 2 Assignment of storage engine just after partition or subpartition
512
#------------------------------------------------------------------------
518
f_charbig VARCHAR(1000)
520
PARTITION BY HASH(f_int1)
521
( PARTITION part1 STORAGE ENGINE = 'MyISAM',
522
PARTITION part2 STORAGE ENGINE = 'MyISAM'
524
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
525
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
526
# Start usability test (inc/partition_check.inc)
528
SHOW CREATE TABLE t1;
530
t1 CREATE TABLE `t1` (
531
`f_int1` int(11) DEFAULT NULL,
532
`f_int2` int(11) DEFAULT NULL,
533
`f_char1` char(20) DEFAULT NULL,
534
`f_char2` char(20) DEFAULT NULL,
535
`f_charbig` varchar(1000) DEFAULT NULL
536
) ENGINE=MyISAM DEFAULT CHARSET=latin1
537
/*!50100 PARTITION BY HASH (f_int1)
538
(PARTITION part1 ENGINE = MyISAM,
539
PARTITION part2 ENGINE = MyISAM) */
549
# check prerequisites-1 success: 1
550
# check COUNT(*) success: 1
551
# check MIN/MAX(f_int1) success: 1
552
# check MIN/MAX(f_int2) success: 1
553
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
554
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
555
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
556
WHERE f_int1 IN (2,3);
557
# check prerequisites-3 success: 1
558
DELETE FROM t1 WHERE f_charbig = 'delete me';
559
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
560
# check read via f_int1 success: 1
561
# check read via f_int2 success: 1
563
# check multiple-1 success: 1
564
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
566
# check multiple-2 success: 1
567
INSERT INTO t1 SELECT * FROM t0_template
568
WHERE MOD(f_int1,3) = 0;
570
# check multiple-3 success: 1
571
UPDATE t1 SET f_int1 = f_int1 + @max_row
572
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
573
AND @max_row_div2 + @max_row_div4;
575
# check multiple-4 success: 1
577
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
578
AND @max_row_div2 + @max_row_div4 + @max_row;
580
# check multiple-5 success: 1
581
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
583
SET f_int1 = @cur_value , f_int2 = @cur_value,
584
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
585
f_charbig = '#SINGLE#';
587
# check single-1 success: 1
588
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
590
SET f_int1 = @cur_value , f_int2 = @cur_value,
591
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
592
f_charbig = '#SINGLE#';
594
# check single-2 success: 1
595
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
596
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
597
UPDATE t1 SET f_int1 = @cur_value2
598
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
600
# check single-3 success: 1
602
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
603
UPDATE t1 SET f_int1 = @cur_value1
604
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
606
# check single-4 success: 1
607
SELECT MAX(f_int1) INTO @cur_value FROM t1;
608
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
610
# check single-5 success: 1
611
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
613
# check single-6 success: 1
614
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
616
# check single-7 success: 1
617
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
618
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
619
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
620
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
621
f_charbig = '#NULL#';
623
SET f_int1 = NULL , f_int2 = -@max_row,
624
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
625
f_charbig = '#NULL#';
626
# check null success: 1
628
# check null-1 success: 1
629
UPDATE t1 SET f_int1 = -@max_row
630
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
631
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
633
# check null-2 success: 1
634
UPDATE t1 SET f_int1 = NULL
635
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
636
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
638
# check null-3 success: 1
640
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
641
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
643
# check null-4 success: 1
645
WHERE f_int1 = 0 AND f_int2 = 0
646
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
647
AND f_charbig = '#NULL#';
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-1 success: 1
657
# check transactions-2 success: 1
660
# check transactions-3 success: 1
661
DELETE FROM t1 WHERE f_charbig = 'was inserted';
665
# check transactions-4 success: 1
666
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
667
SELECT f_int1, f_int1, '', '', 'was inserted'
668
FROM t0_template source_tab
669
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
671
# check transactions-5 success: 1
674
Warning 1196 Some non-transactional changed tables couldn't be rolled back
676
# check transactions-6 success: 1
677
# INFO: Storage engine used for t1 seems to be not transactional.
680
# check transactions-7 success: 1
681
DELETE FROM t1 WHERE f_charbig = 'was inserted';
683
SET @@session.sql_mode = 'traditional';
684
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
685
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
686
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
687
'', '', 'was inserted' FROM t0_template
688
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
689
ERROR 22012: Division by 0
692
# check transactions-8 success: 1
693
# INFO: Storage engine used for t1 seems to be unable to revert
694
# changes made by the failing statement.
695
SET @@session.sql_mode = '';
697
DELETE FROM t1 WHERE f_charbig = 'was inserted';
699
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
701
# check special-1 success: 1
702
UPDATE t1 SET f_charbig = '';
704
# check special-2 success: 1
705
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
706
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
707
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
708
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
709
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
710
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
711
'just inserted' FROM t0_template
712
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
713
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
715
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
716
f_charbig = 'updated by trigger'
717
WHERE f_int1 = new.f_int1;
719
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
720
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
721
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
723
# check trigger-1 success: 1
725
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
726
f_int2 = CAST(f_char1 AS SIGNED INT),
727
f_charbig = 'just inserted'
728
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
730
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
731
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
732
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
733
'just inserted' FROM t0_template
734
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
735
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
737
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
738
f_charbig = 'updated by trigger'
739
WHERE f_int1 = new.f_int1;
741
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
742
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
743
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
745
# check trigger-2 success: 1
747
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
748
f_int2 = CAST(f_char1 AS SIGNED INT),
749
f_charbig = 'just inserted'
750
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
752
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
753
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
754
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
755
'just inserted' FROM t0_template
756
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
757
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
759
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
760
f_charbig = 'updated by trigger'
761
WHERE f_int1 = new.f_int1;
763
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
764
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
766
# check trigger-3 success: 1
768
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
769
f_int2 = CAST(f_char1 AS SIGNED INT),
770
f_charbig = 'just inserted'
771
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
773
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
774
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
775
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
776
'just inserted' FROM t0_template
777
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
778
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
780
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
781
f_charbig = 'updated by trigger'
782
WHERE f_int1 = - old.f_int1;
784
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
785
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
787
# check trigger-4 success: 1
789
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
790
f_int2 = CAST(f_char1 AS SIGNED INT),
791
f_charbig = 'just inserted'
792
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
794
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
795
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
796
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
797
'just inserted' FROM t0_template
798
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
799
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
801
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
802
f_charbig = 'updated by trigger'
803
WHERE f_int1 = new.f_int1;
805
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
806
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
808
# check trigger-5 success: 1
810
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
811
f_int2 = CAST(f_char1 AS SIGNED INT),
812
f_charbig = 'just inserted'
813
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
815
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
816
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
817
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
818
'just inserted' FROM t0_template
819
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
820
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
822
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
823
f_charbig = 'updated by trigger'
824
WHERE f_int1 = - old.f_int1;
826
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
827
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
829
# check trigger-6 success: 1
831
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
832
f_int2 = CAST(f_char1 AS SIGNED INT),
833
f_charbig = 'just inserted'
834
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
836
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
837
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
838
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
839
'just inserted' FROM t0_template
840
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
841
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
843
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
844
f_charbig = 'updated by trigger'
845
WHERE f_int1 = - old.f_int1;
848
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
850
# check trigger-7 success: 1
852
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
853
f_int2 = CAST(f_char1 AS SIGNED INT),
854
f_charbig = 'just inserted'
855
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
857
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
858
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
859
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
860
'just inserted' FROM t0_template
861
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
862
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
864
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
865
f_charbig = 'updated by trigger'
866
WHERE f_int1 = - old.f_int1;
869
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
871
# check trigger-8 success: 1
873
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
874
f_int2 = CAST(f_char1 AS SIGNED INT),
875
f_charbig = 'just inserted'
876
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
878
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
880
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
881
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
883
SET new.f_int1 = old.f_int1 + @max_row,
884
new.f_int2 = old.f_int2 - @max_row,
885
new.f_charbig = '####updated per update trigger####';
888
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
889
f_charbig = '####updated per update statement itself####';
891
# check trigger-9 success: 1
893
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
894
f_int2 = CAST(f_char1 AS SIGNED INT),
895
f_charbig = CONCAT('===',f_char1,'===');
896
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
898
SET new.f_int1 = new.f_int1 + @max_row,
899
new.f_int2 = new.f_int2 - @max_row,
900
new.f_charbig = '####updated per update trigger####';
903
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
904
f_charbig = '####updated per update statement itself####';
906
# check trigger-10 success: 1
908
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
909
f_int2 = CAST(f_char1 AS SIGNED INT),
910
f_charbig = CONCAT('===',f_char1,'===');
911
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
913
SET new.f_int1 = @my_max1 + @counter,
914
new.f_int2 = @my_min2 - @counter,
915
new.f_charbig = '####updated per insert trigger####';
916
SET @counter = @counter + 1;
919
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
920
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
921
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
922
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
923
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
927
# check trigger-11 success: 1
929
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
930
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
931
AND f_charbig = '####updated per insert trigger####';
932
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
934
SET new.f_int1 = @my_max1 + @counter,
935
new.f_int2 = @my_min2 - @counter,
936
new.f_charbig = '####updated per insert trigger####';
937
SET @counter = @counter + 1;
940
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
941
INSERT INTO t1 (f_char1, f_char2, f_charbig)
942
SELECT CAST(f_int1 AS CHAR),
943
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
944
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
948
# check trigger-12 success: 1
950
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
951
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
952
AND f_charbig = '####updated per insert trigger####';
954
Table Op Msg_type Msg_text
955
test.t1 analyze status OK
956
CHECK TABLE t1 EXTENDED;
957
Table Op Msg_type Msg_text
958
test.t1 check status OK
959
CHECKSUM TABLE t1 EXTENDED;
963
Table Op Msg_type Msg_text
964
test.t1 optimize status OK
965
# check layout success: 1
966
REPAIR TABLE t1 EXTENDED;
967
Table Op Msg_type Msg_text
968
test.t1 repair status OK
969
# check layout success: 1
972
# check TRUNCATE success: 1
973
# check layout success: 1
974
# End usability test (inc/partition_check.inc)
981
f_charbig VARCHAR(1000)
983
PARTITION BY RANGE(f_int1)
984
SUBPARTITION BY HASH(f_int1)
985
( PARTITION part1 VALUES LESS THAN (10)
986
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
987
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
988
PARTITION part2 VALUES LESS THAN (2147483646)
989
(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
990
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
992
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
993
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
994
# Start usability test (inc/partition_check.inc)
996
SHOW CREATE TABLE t1;
998
t1 CREATE TABLE `t1` (
999
`f_int1` int(11) DEFAULT NULL,
1000
`f_int2` int(11) DEFAULT NULL,
1001
`f_char1` char(20) DEFAULT NULL,
1002
`f_char2` char(20) DEFAULT NULL,
1003
`f_charbig` varchar(1000) DEFAULT NULL
1004
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1005
/*!50100 PARTITION BY RANGE (f_int1)
1006
SUBPARTITION BY HASH (f_int1)
1007
(PARTITION part1 VALUES LESS THAN (10)
1008
(SUBPARTITION subpart11 ENGINE = MyISAM,
1009
SUBPARTITION subpart12 ENGINE = MyISAM),
1010
PARTITION part2 VALUES LESS THAN (2147483646)
1011
(SUBPARTITION subpart21 ENGINE = MyISAM,
1012
SUBPARTITION subpart22 ENGINE = MyISAM)) */
1015
t1#P#part1#SP#subpart11.MYD
1016
t1#P#part1#SP#subpart11.MYI
1017
t1#P#part1#SP#subpart12.MYD
1018
t1#P#part1#SP#subpart12.MYI
1019
t1#P#part2#SP#subpart21.MYD
1020
t1#P#part2#SP#subpart21.MYI
1021
t1#P#part2#SP#subpart22.MYD
1022
t1#P#part2#SP#subpart22.MYI
1026
# check prerequisites-1 success: 1
1027
# check COUNT(*) success: 1
1028
# check MIN/MAX(f_int1) success: 1
1029
# check MIN/MAX(f_int2) success: 1
1030
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1031
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1032
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
1033
WHERE f_int1 IN (2,3);
1034
# check prerequisites-3 success: 1
1035
DELETE FROM t1 WHERE f_charbig = 'delete me';
1036
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
1037
# check read via f_int1 success: 1
1038
# check read via f_int2 success: 1
1040
# check multiple-1 success: 1
1041
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
1043
# check multiple-2 success: 1
1044
INSERT INTO t1 SELECT * FROM t0_template
1045
WHERE MOD(f_int1,3) = 0;
1047
# check multiple-3 success: 1
1048
UPDATE t1 SET f_int1 = f_int1 + @max_row
1049
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
1050
AND @max_row_div2 + @max_row_div4;
1052
# check multiple-4 success: 1
1054
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
1055
AND @max_row_div2 + @max_row_div4 + @max_row;
1057
# check multiple-5 success: 1
1058
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
1060
SET f_int1 = @cur_value , f_int2 = @cur_value,
1061
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1062
f_charbig = '#SINGLE#';
1064
# check single-1 success: 1
1065
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
1067
SET f_int1 = @cur_value , f_int2 = @cur_value,
1068
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1069
f_charbig = '#SINGLE#';
1071
# check single-2 success: 1
1072
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
1073
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
1074
UPDATE t1 SET f_int1 = @cur_value2
1075
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
1077
# check single-3 success: 1
1078
SET @cur_value1= -1;
1079
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
1080
UPDATE t1 SET f_int1 = @cur_value1
1081
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
1083
# check single-4 success: 1
1084
SELECT MAX(f_int1) INTO @cur_value FROM t1;
1085
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
1087
# check single-5 success: 1
1088
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
1090
# check single-6 success: 1
1091
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
1092
ERROR HY000: Table has no partition for value 2147483647
1093
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
1094
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
1095
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
1096
f_charbig = '#NULL#';
1098
SET f_int1 = NULL , f_int2 = -@max_row,
1099
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
1100
f_charbig = '#NULL#';
1101
# check null success: 1
1103
# check null-1 success: 1
1104
UPDATE t1 SET f_int1 = -@max_row
1105
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1106
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1108
# check null-2 success: 1
1109
UPDATE t1 SET f_int1 = NULL
1110
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1111
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1113
# check null-3 success: 1
1115
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1116
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1118
# check null-4 success: 1
1120
WHERE f_int1 = 0 AND f_int2 = 0
1121
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
1122
AND f_charbig = '#NULL#';
1124
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1125
SELECT f_int1, f_int1, '', '', 'was inserted'
1126
FROM t0_template source_tab
1127
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1129
# check transactions-1 success: 1
1132
# check transactions-2 success: 1
1135
# check transactions-3 success: 1
1136
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1140
# check transactions-4 success: 1
1141
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1142
SELECT f_int1, f_int1, '', '', 'was inserted'
1143
FROM t0_template source_tab
1144
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1146
# check transactions-5 success: 1
1149
Warning 1196 Some non-transactional changed tables couldn't be rolled back
1151
# check transactions-6 success: 1
1152
# INFO: Storage engine used for t1 seems to be not transactional.
1155
# check transactions-7 success: 1
1156
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1158
SET @@session.sql_mode = 'traditional';
1159
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
1160
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1161
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
1162
'', '', 'was inserted' FROM t0_template
1163
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1164
ERROR 22012: Division by 0
1167
# check transactions-8 success: 1
1168
# INFO: Storage engine used for t1 seems to be unable to revert
1169
# changes made by the failing statement.
1170
SET @@session.sql_mode = '';
1172
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1174
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
1176
# check special-1 success: 1
1177
UPDATE t1 SET f_charbig = '';
1179
# check special-2 success: 1
1180
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
1181
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1182
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
1183
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1184
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1185
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1186
'just inserted' FROM t0_template
1187
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1188
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
1190
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1191
f_charbig = 'updated by trigger'
1192
WHERE f_int1 = new.f_int1;
1194
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1195
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1196
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1198
# check trigger-1 success: 1
1200
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1201
f_int2 = CAST(f_char1 AS SIGNED INT),
1202
f_charbig = 'just inserted'
1203
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1205
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1206
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1207
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1208
'just inserted' FROM t0_template
1209
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1210
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
1212
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1213
f_charbig = 'updated by trigger'
1214
WHERE f_int1 = new.f_int1;
1216
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1217
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1218
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1220
# check trigger-2 success: 1
1222
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1223
f_int2 = CAST(f_char1 AS SIGNED INT),
1224
f_charbig = 'just inserted'
1225
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1227
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1228
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1229
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1230
'just inserted' FROM t0_template
1231
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1232
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1234
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1235
f_charbig = 'updated by trigger'
1236
WHERE f_int1 = new.f_int1;
1238
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1239
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1241
# check trigger-3 success: 1
1243
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1244
f_int2 = CAST(f_char1 AS SIGNED INT),
1245
f_charbig = 'just inserted'
1246
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1248
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1249
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1250
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1251
'just inserted' FROM t0_template
1252
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1253
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1255
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1256
f_charbig = 'updated by trigger'
1257
WHERE f_int1 = - old.f_int1;
1259
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1260
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1262
# check trigger-4 success: 1
1264
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1265
f_int2 = CAST(f_char1 AS SIGNED INT),
1266
f_charbig = 'just inserted'
1267
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1269
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1270
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1271
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1272
'just inserted' FROM t0_template
1273
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1274
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1276
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1277
f_charbig = 'updated by trigger'
1278
WHERE f_int1 = new.f_int1;
1280
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1281
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1283
# check trigger-5 success: 1
1285
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1286
f_int2 = CAST(f_char1 AS SIGNED INT),
1287
f_charbig = 'just inserted'
1288
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1290
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1291
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1292
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1293
'just inserted' FROM t0_template
1294
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1295
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1297
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1298
f_charbig = 'updated by trigger'
1299
WHERE f_int1 = - old.f_int1;
1301
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1302
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1304
# check trigger-6 success: 1
1306
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1307
f_int2 = CAST(f_char1 AS SIGNED INT),
1308
f_charbig = 'just inserted'
1309
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1311
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1312
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1313
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1314
'just inserted' FROM t0_template
1315
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1316
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
1318
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1319
f_charbig = 'updated by trigger'
1320
WHERE f_int1 = - old.f_int1;
1323
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1325
# check trigger-7 success: 1
1327
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1328
f_int2 = CAST(f_char1 AS SIGNED INT),
1329
f_charbig = 'just inserted'
1330
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1332
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1333
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1334
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1335
'just inserted' FROM t0_template
1336
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1337
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
1339
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1340
f_charbig = 'updated by trigger'
1341
WHERE f_int1 = - old.f_int1;
1344
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1346
# check trigger-8 success: 1
1348
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1349
f_int2 = CAST(f_char1 AS SIGNED INT),
1350
f_charbig = 'just inserted'
1351
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1353
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1355
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1356
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1358
SET new.f_int1 = old.f_int1 + @max_row,
1359
new.f_int2 = old.f_int2 - @max_row,
1360
new.f_charbig = '####updated per update trigger####';
1363
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1364
f_charbig = '####updated per update statement itself####';
1366
# check trigger-9 success: 1
1368
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1369
f_int2 = CAST(f_char1 AS SIGNED INT),
1370
f_charbig = CONCAT('===',f_char1,'===');
1371
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1373
SET new.f_int1 = new.f_int1 + @max_row,
1374
new.f_int2 = new.f_int2 - @max_row,
1375
new.f_charbig = '####updated per update trigger####';
1378
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1379
f_charbig = '####updated per update statement itself####';
1381
# check trigger-10 success: 1
1383
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1384
f_int2 = CAST(f_char1 AS SIGNED INT),
1385
f_charbig = CONCAT('===',f_char1,'===');
1386
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1388
SET new.f_int1 = @my_max1 + @counter,
1389
new.f_int2 = @my_min2 - @counter,
1390
new.f_charbig = '####updated per insert trigger####';
1391
SET @counter = @counter + 1;
1394
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1395
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1396
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1397
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1398
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1402
# check trigger-11 success: 1
1404
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1405
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1406
AND f_charbig = '####updated per insert trigger####';
1407
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1409
SET new.f_int1 = @my_max1 + @counter,
1410
new.f_int2 = @my_min2 - @counter,
1411
new.f_charbig = '####updated per insert trigger####';
1412
SET @counter = @counter + 1;
1415
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1416
INSERT INTO t1 (f_char1, f_char2, f_charbig)
1417
SELECT CAST(f_int1 AS CHAR),
1418
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1419
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1423
# check trigger-12 success: 1
1425
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1426
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1427
AND f_charbig = '####updated per insert trigger####';
1429
Table Op Msg_type Msg_text
1430
test.t1 analyze status OK
1431
CHECK TABLE t1 EXTENDED;
1432
Table Op Msg_type Msg_text
1433
test.t1 check status OK
1434
CHECKSUM TABLE t1 EXTENDED;
1436
test.t1 <some_value>
1438
Table Op Msg_type Msg_text
1439
test.t1 optimize status OK
1440
# check layout success: 1
1441
REPAIR TABLE t1 EXTENDED;
1442
Table Op Msg_type Msg_text
1443
test.t1 repair status OK
1444
# check layout success: 1
1447
# check TRUNCATE success: 1
1448
# check layout success: 1
1449
# End usability test (inc/partition_check.inc)
1451
#------------------------------------------------------------------------
1452
# 3 Some but not all named partitions or subpartitions get a storage
1454
#------------------------------------------------------------------------
1460
f_charbig VARCHAR(1000)
1462
PARTITION BY HASH(f_int1)
1463
( PARTITION part1 STORAGE ENGINE = 'MyISAM',
1466
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1472
f_charbig VARCHAR(1000)
1474
PARTITION BY HASH(f_int1)
1476
PARTITION part2 STORAGE ENGINE = 'MyISAM'
1478
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1484
f_charbig VARCHAR(1000)
1486
PARTITION BY RANGE(f_int1)
1487
SUBPARTITION BY HASH(f_int1)
1488
( PARTITION part1 VALUES LESS THAN (10)
1489
(SUBPARTITION subpart11,
1490
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
1491
PARTITION part2 VALUES LESS THAN (2147483646)
1492
(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
1493
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
1495
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1501
f_charbig VARCHAR(1000)
1503
PARTITION BY RANGE(f_int1)
1504
SUBPARTITION BY HASH(f_int1)
1505
( PARTITION part1 VALUES LESS THAN (10)
1506
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
1507
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
1508
PARTITION part2 VALUES LESS THAN (2147483646)
1509
(SUBPARTITION subpart21,
1510
SUBPARTITION subpart22 )
1512
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1518
f_charbig VARCHAR(1000)
1521
PARTITION BY RANGE(f_int1)
1522
SUBPARTITION BY HASH(f_int1)
1523
( PARTITION part1 VALUES LESS THAN (10)
1524
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
1525
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
1526
PARTITION part2 VALUES LESS THAN (2147483646)
1527
(SUBPARTITION subpart21,
1528
SUBPARTITION subpart22 )
1530
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1531
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
1532
# Start usability test (inc/partition_check.inc)
1534
SHOW CREATE TABLE t1;
1536
t1 CREATE TABLE `t1` (
1537
`f_int1` int(11) DEFAULT NULL,
1538
`f_int2` int(11) DEFAULT NULL,
1539
`f_char1` char(20) DEFAULT NULL,
1540
`f_char2` char(20) DEFAULT NULL,
1541
`f_charbig` varchar(1000) DEFAULT NULL
1542
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1543
/*!50100 PARTITION BY RANGE (f_int1)
1544
SUBPARTITION BY HASH (f_int1)
1545
(PARTITION part1 VALUES LESS THAN (10)
1546
(SUBPARTITION subpart11 ENGINE = MyISAM,
1547
SUBPARTITION subpart12 ENGINE = MyISAM),
1548
PARTITION part2 VALUES LESS THAN (2147483646)
1549
(SUBPARTITION subpart21 ENGINE = MyISAM,
1550
SUBPARTITION subpart22 ENGINE = MyISAM)) */
1553
t1#P#part1#SP#subpart11.MYD
1554
t1#P#part1#SP#subpart11.MYI
1555
t1#P#part1#SP#subpart12.MYD
1556
t1#P#part1#SP#subpart12.MYI
1557
t1#P#part2#SP#subpart21.MYD
1558
t1#P#part2#SP#subpart21.MYI
1559
t1#P#part2#SP#subpart22.MYD
1560
t1#P#part2#SP#subpart22.MYI
1564
# check prerequisites-1 success: 1
1565
# check COUNT(*) success: 1
1566
# check MIN/MAX(f_int1) success: 1
1567
# check MIN/MAX(f_int2) success: 1
1568
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1569
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1570
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
1571
WHERE f_int1 IN (2,3);
1572
# check prerequisites-3 success: 1
1573
DELETE FROM t1 WHERE f_charbig = 'delete me';
1574
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
1575
# check read via f_int1 success: 1
1576
# check read via f_int2 success: 1
1578
# check multiple-1 success: 1
1579
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
1581
# check multiple-2 success: 1
1582
INSERT INTO t1 SELECT * FROM t0_template
1583
WHERE MOD(f_int1,3) = 0;
1585
# check multiple-3 success: 1
1586
UPDATE t1 SET f_int1 = f_int1 + @max_row
1587
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
1588
AND @max_row_div2 + @max_row_div4;
1590
# check multiple-4 success: 1
1592
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
1593
AND @max_row_div2 + @max_row_div4 + @max_row;
1595
# check multiple-5 success: 1
1596
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
1598
SET f_int1 = @cur_value , f_int2 = @cur_value,
1599
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1600
f_charbig = '#SINGLE#';
1602
# check single-1 success: 1
1603
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
1605
SET f_int1 = @cur_value , f_int2 = @cur_value,
1606
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1607
f_charbig = '#SINGLE#';
1609
# check single-2 success: 1
1610
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
1611
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
1612
UPDATE t1 SET f_int1 = @cur_value2
1613
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
1615
# check single-3 success: 1
1616
SET @cur_value1= -1;
1617
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
1618
UPDATE t1 SET f_int1 = @cur_value1
1619
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
1621
# check single-4 success: 1
1622
SELECT MAX(f_int1) INTO @cur_value FROM t1;
1623
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
1625
# check single-5 success: 1
1626
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
1628
# check single-6 success: 1
1629
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
1630
ERROR HY000: Table has no partition for value 2147483647
1631
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
1632
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
1633
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
1634
f_charbig = '#NULL#';
1636
SET f_int1 = NULL , f_int2 = -@max_row,
1637
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
1638
f_charbig = '#NULL#';
1639
# check null success: 1
1641
# check null-1 success: 1
1642
UPDATE t1 SET f_int1 = -@max_row
1643
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1644
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1646
# check null-2 success: 1
1647
UPDATE t1 SET f_int1 = NULL
1648
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1649
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1651
# check null-3 success: 1
1653
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1654
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1656
# check null-4 success: 1
1658
WHERE f_int1 = 0 AND f_int2 = 0
1659
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
1660
AND f_charbig = '#NULL#';
1662
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1663
SELECT f_int1, f_int1, '', '', 'was inserted'
1664
FROM t0_template source_tab
1665
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1667
# check transactions-1 success: 1
1670
# check transactions-2 success: 1
1673
# check transactions-3 success: 1
1674
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1678
# check transactions-4 success: 1
1679
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1680
SELECT f_int1, f_int1, '', '', 'was inserted'
1681
FROM t0_template source_tab
1682
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1684
# check transactions-5 success: 1
1687
Warning 1196 Some non-transactional changed tables couldn't be rolled back
1689
# check transactions-6 success: 1
1690
# INFO: Storage engine used for t1 seems to be not transactional.
1693
# check transactions-7 success: 1
1694
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1696
SET @@session.sql_mode = 'traditional';
1697
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
1698
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1699
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
1700
'', '', 'was inserted' FROM t0_template
1701
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1702
ERROR 22012: Division by 0
1705
# check transactions-8 success: 1
1706
# INFO: Storage engine used for t1 seems to be unable to revert
1707
# changes made by the failing statement.
1708
SET @@session.sql_mode = '';
1710
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1712
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
1714
# check special-1 success: 1
1715
UPDATE t1 SET f_charbig = '';
1717
# check special-2 success: 1
1718
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
1719
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1720
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
1721
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1722
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1723
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1724
'just inserted' FROM t0_template
1725
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1726
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
1728
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1729
f_charbig = 'updated by trigger'
1730
WHERE f_int1 = new.f_int1;
1732
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1733
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1734
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1736
# check trigger-1 success: 1
1738
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1739
f_int2 = CAST(f_char1 AS SIGNED INT),
1740
f_charbig = 'just inserted'
1741
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1743
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1744
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1745
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1746
'just inserted' FROM t0_template
1747
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1748
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
1750
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1751
f_charbig = 'updated by trigger'
1752
WHERE f_int1 = new.f_int1;
1754
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1755
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1756
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1758
# check trigger-2 success: 1
1760
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1761
f_int2 = CAST(f_char1 AS SIGNED INT),
1762
f_charbig = 'just inserted'
1763
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1765
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1766
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1767
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1768
'just inserted' FROM t0_template
1769
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1770
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1772
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1773
f_charbig = 'updated by trigger'
1774
WHERE f_int1 = new.f_int1;
1776
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1777
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1779
# check trigger-3 success: 1
1781
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1782
f_int2 = CAST(f_char1 AS SIGNED INT),
1783
f_charbig = 'just inserted'
1784
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1786
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1787
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1788
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1789
'just inserted' FROM t0_template
1790
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1791
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1793
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1794
f_charbig = 'updated by trigger'
1795
WHERE f_int1 = - old.f_int1;
1797
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1798
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1800
# check trigger-4 success: 1
1802
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1803
f_int2 = CAST(f_char1 AS SIGNED INT),
1804
f_charbig = 'just inserted'
1805
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1807
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1808
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1809
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1810
'just inserted' FROM t0_template
1811
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1812
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1814
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1815
f_charbig = 'updated by trigger'
1816
WHERE f_int1 = new.f_int1;
1818
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1819
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1821
# check trigger-5 success: 1
1823
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1824
f_int2 = CAST(f_char1 AS SIGNED INT),
1825
f_charbig = 'just inserted'
1826
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1828
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1829
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1830
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1831
'just inserted' FROM t0_template
1832
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1833
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1835
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1836
f_charbig = 'updated by trigger'
1837
WHERE f_int1 = - old.f_int1;
1839
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1840
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1842
# check trigger-6 success: 1
1844
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1845
f_int2 = CAST(f_char1 AS SIGNED INT),
1846
f_charbig = 'just inserted'
1847
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1849
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1850
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1851
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1852
'just inserted' FROM t0_template
1853
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1854
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
1856
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1857
f_charbig = 'updated by trigger'
1858
WHERE f_int1 = - old.f_int1;
1861
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1863
# check trigger-7 success: 1
1865
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1866
f_int2 = CAST(f_char1 AS SIGNED INT),
1867
f_charbig = 'just inserted'
1868
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1870
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1871
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1872
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1873
'just inserted' FROM t0_template
1874
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1875
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
1877
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1878
f_charbig = 'updated by trigger'
1879
WHERE f_int1 = - old.f_int1;
1882
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1884
# check trigger-8 success: 1
1886
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1887
f_int2 = CAST(f_char1 AS SIGNED INT),
1888
f_charbig = 'just inserted'
1889
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1891
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1893
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1894
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1896
SET new.f_int1 = old.f_int1 + @max_row,
1897
new.f_int2 = old.f_int2 - @max_row,
1898
new.f_charbig = '####updated per update trigger####';
1901
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1902
f_charbig = '####updated per update statement itself####';
1904
# check trigger-9 success: 1
1906
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1907
f_int2 = CAST(f_char1 AS SIGNED INT),
1908
f_charbig = CONCAT('===',f_char1,'===');
1909
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1911
SET new.f_int1 = new.f_int1 + @max_row,
1912
new.f_int2 = new.f_int2 - @max_row,
1913
new.f_charbig = '####updated per update trigger####';
1916
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1917
f_charbig = '####updated per update statement itself####';
1919
# check trigger-10 success: 1
1921
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1922
f_int2 = CAST(f_char1 AS SIGNED INT),
1923
f_charbig = CONCAT('===',f_char1,'===');
1924
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1926
SET new.f_int1 = @my_max1 + @counter,
1927
new.f_int2 = @my_min2 - @counter,
1928
new.f_charbig = '####updated per insert trigger####';
1929
SET @counter = @counter + 1;
1932
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1933
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1934
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1935
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1936
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1940
# check trigger-11 success: 1
1942
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1943
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1944
AND f_charbig = '####updated per insert trigger####';
1945
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1947
SET new.f_int1 = @my_max1 + @counter,
1948
new.f_int2 = @my_min2 - @counter,
1949
new.f_charbig = '####updated per insert trigger####';
1950
SET @counter = @counter + 1;
1953
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1954
INSERT INTO t1 (f_char1, f_char2, f_charbig)
1955
SELECT CAST(f_int1 AS CHAR),
1956
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1957
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1961
# check trigger-12 success: 1
1963
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1964
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1965
AND f_charbig = '####updated per insert trigger####';
1967
Table Op Msg_type Msg_text
1968
test.t1 analyze status OK
1969
CHECK TABLE t1 EXTENDED;
1970
Table Op Msg_type Msg_text
1971
test.t1 check status OK
1972
CHECKSUM TABLE t1 EXTENDED;
1974
test.t1 <some_value>
1976
Table Op Msg_type Msg_text
1977
test.t1 optimize status OK
1978
# check layout success: 1
1979
REPAIR TABLE t1 EXTENDED;
1980
Table Op Msg_type Msg_text
1981
test.t1 repair status OK
1982
# check layout success: 1
1985
# check TRUNCATE success: 1
1986
# check layout success: 1
1987
# End usability test (inc/partition_check.inc)
1989
#------------------------------------------------------------------------
1990
# 4 Storage engine assignment after partition name + after name of
1991
# subpartitions belonging to another partition
1992
#------------------------------------------------------------------------
1998
f_charbig VARCHAR(1000)
2000
PARTITION BY RANGE(f_int1)
2001
SUBPARTITION BY HASH(f_int1)
2002
( PARTITION part1 VALUES LESS THAN (10)
2003
(SUBPARTITION subpart11,
2004
SUBPARTITION subpart12),
2005
PARTITION part2 VALUES LESS THAN (2147483646)
2006
(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
2007
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
2009
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
2015
f_charbig VARCHAR(1000)
2018
PARTITION BY RANGE(f_int1)
2019
SUBPARTITION BY HASH(f_int1)
2020
( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'MyISAM'
2021
(SUBPARTITION subpart11,
2022
SUBPARTITION subpart12),
2023
PARTITION part2 VALUES LESS THAN (2147483646)
2024
(SUBPARTITION subpart21,
2025
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
2033
f_charbig VARCHAR(1000)
2035
PARTITION BY RANGE(f_int1)
2036
SUBPARTITION BY HASH(f_int1)
2037
( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'MyISAM'
2038
(SUBPARTITION subpart11,
2039
SUBPARTITION subpart12),
2040
PARTITION part2 VALUES LESS THAN (2147483646)
2041
(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
2042
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
2044
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2045
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2046
# Start usability test (inc/partition_check.inc)
2048
SHOW CREATE TABLE t1;
2050
t1 CREATE TABLE `t1` (
2051
`f_int1` int(11) DEFAULT NULL,
2052
`f_int2` int(11) DEFAULT NULL,
2053
`f_char1` char(20) DEFAULT NULL,
2054
`f_char2` char(20) DEFAULT NULL,
2055
`f_charbig` varchar(1000) DEFAULT NULL
2056
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2057
/*!50100 PARTITION BY RANGE (f_int1)
2058
SUBPARTITION BY HASH (f_int1)
2059
(PARTITION part1 VALUES LESS THAN (10)
2060
(SUBPARTITION subpart11 ENGINE = MyISAM,
2061
SUBPARTITION subpart12 ENGINE = MyISAM),
2062
PARTITION part2 VALUES LESS THAN (2147483646)
2063
(SUBPARTITION subpart21 ENGINE = MyISAM,
2064
SUBPARTITION subpart22 ENGINE = MyISAM)) */
2067
t1#P#part1#SP#subpart11.MYD
2068
t1#P#part1#SP#subpart11.MYI
2069
t1#P#part1#SP#subpart12.MYD
2070
t1#P#part1#SP#subpart12.MYI
2071
t1#P#part2#SP#subpart21.MYD
2072
t1#P#part2#SP#subpart21.MYI
2073
t1#P#part2#SP#subpart22.MYD
2074
t1#P#part2#SP#subpart22.MYI
2078
# check prerequisites-1 success: 1
2079
# check COUNT(*) success: 1
2080
# check MIN/MAX(f_int1) success: 1
2081
# check MIN/MAX(f_int2) success: 1
2082
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2083
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2084
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2085
WHERE f_int1 IN (2,3);
2086
# check prerequisites-3 success: 1
2087
DELETE FROM t1 WHERE f_charbig = 'delete me';
2088
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2089
# check read via f_int1 success: 1
2090
# check read via f_int2 success: 1
2092
# check multiple-1 success: 1
2093
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2095
# check multiple-2 success: 1
2096
INSERT INTO t1 SELECT * FROM t0_template
2097
WHERE MOD(f_int1,3) = 0;
2099
# check multiple-3 success: 1
2100
UPDATE t1 SET f_int1 = f_int1 + @max_row
2101
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2102
AND @max_row_div2 + @max_row_div4;
2104
# check multiple-4 success: 1
2106
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2107
AND @max_row_div2 + @max_row_div4 + @max_row;
2109
# check multiple-5 success: 1
2110
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2112
SET f_int1 = @cur_value , f_int2 = @cur_value,
2113
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2114
f_charbig = '#SINGLE#';
2116
# check single-1 success: 1
2117
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2119
SET f_int1 = @cur_value , f_int2 = @cur_value,
2120
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2121
f_charbig = '#SINGLE#';
2123
# check single-2 success: 1
2124
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2125
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2126
UPDATE t1 SET f_int1 = @cur_value2
2127
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2129
# check single-3 success: 1
2130
SET @cur_value1= -1;
2131
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2132
UPDATE t1 SET f_int1 = @cur_value1
2133
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2135
# check single-4 success: 1
2136
SELECT MAX(f_int1) INTO @cur_value FROM t1;
2137
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
2139
# check single-5 success: 1
2140
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
2142
# check single-6 success: 1
2143
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
2144
ERROR HY000: Table has no partition for value 2147483647
2145
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
2146
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
2147
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
2148
f_charbig = '#NULL#';
2150
SET f_int1 = NULL , f_int2 = -@max_row,
2151
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
2152
f_charbig = '#NULL#';
2153
# check null success: 1
2155
# check null-1 success: 1
2156
UPDATE t1 SET f_int1 = -@max_row
2157
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2158
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2160
# check null-2 success: 1
2161
UPDATE t1 SET f_int1 = NULL
2162
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2163
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2165
# check null-3 success: 1
2167
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2168
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2170
# check null-4 success: 1
2172
WHERE f_int1 = 0 AND f_int2 = 0
2173
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
2174
AND f_charbig = '#NULL#';
2176
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2177
SELECT f_int1, f_int1, '', '', 'was inserted'
2178
FROM t0_template source_tab
2179
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2181
# check transactions-1 success: 1
2184
# check transactions-2 success: 1
2187
# check transactions-3 success: 1
2188
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2192
# check transactions-4 success: 1
2193
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2194
SELECT f_int1, f_int1, '', '', 'was inserted'
2195
FROM t0_template source_tab
2196
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2198
# check transactions-5 success: 1
2201
Warning 1196 Some non-transactional changed tables couldn't be rolled back
2203
# check transactions-6 success: 1
2204
# INFO: Storage engine used for t1 seems to be not transactional.
2207
# check transactions-7 success: 1
2208
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2210
SET @@session.sql_mode = 'traditional';
2211
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
2212
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2213
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
2214
'', '', 'was inserted' FROM t0_template
2215
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2216
ERROR 22012: Division by 0
2219
# check transactions-8 success: 1
2220
# INFO: Storage engine used for t1 seems to be unable to revert
2221
# changes made by the failing statement.
2222
SET @@session.sql_mode = '';
2224
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2226
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
2228
# check special-1 success: 1
2229
UPDATE t1 SET f_charbig = '';
2231
# check special-2 success: 1
2232
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
2233
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2234
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
2235
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2236
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2237
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2238
'just inserted' FROM t0_template
2239
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2240
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
2242
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2243
f_charbig = 'updated by trigger'
2244
WHERE f_int1 = new.f_int1;
2246
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2247
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2248
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2250
# check trigger-1 success: 1
2252
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2253
f_int2 = CAST(f_char1 AS SIGNED INT),
2254
f_charbig = 'just inserted'
2255
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2257
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2258
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2259
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2260
'just inserted' FROM t0_template
2261
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2262
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
2264
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2265
f_charbig = 'updated by trigger'
2266
WHERE f_int1 = new.f_int1;
2268
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2269
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2270
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2272
# check trigger-2 success: 1
2274
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2275
f_int2 = CAST(f_char1 AS SIGNED INT),
2276
f_charbig = 'just inserted'
2277
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2279
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2280
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2281
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2282
'just inserted' FROM t0_template
2283
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2284
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2286
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2287
f_charbig = 'updated by trigger'
2288
WHERE f_int1 = new.f_int1;
2290
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2291
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2293
# check trigger-3 success: 1
2295
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2296
f_int2 = CAST(f_char1 AS SIGNED INT),
2297
f_charbig = 'just inserted'
2298
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2300
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2301
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2302
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2303
'just inserted' FROM t0_template
2304
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2305
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2307
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2308
f_charbig = 'updated by trigger'
2309
WHERE f_int1 = - old.f_int1;
2311
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2312
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2314
# check trigger-4 success: 1
2316
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2317
f_int2 = CAST(f_char1 AS SIGNED INT),
2318
f_charbig = 'just inserted'
2319
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2321
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2322
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2323
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2324
'just inserted' FROM t0_template
2325
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2326
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2328
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2329
f_charbig = 'updated by trigger'
2330
WHERE f_int1 = new.f_int1;
2332
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2333
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2335
# check trigger-5 success: 1
2337
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2338
f_int2 = CAST(f_char1 AS SIGNED INT),
2339
f_charbig = 'just inserted'
2340
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2342
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2343
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2344
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2345
'just inserted' FROM t0_template
2346
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2347
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2349
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2350
f_charbig = 'updated by trigger'
2351
WHERE f_int1 = - old.f_int1;
2353
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2354
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2356
# check trigger-6 success: 1
2358
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2359
f_int2 = CAST(f_char1 AS SIGNED INT),
2360
f_charbig = 'just inserted'
2361
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2363
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2364
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2365
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2366
'just inserted' FROM t0_template
2367
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2368
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
2370
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2371
f_charbig = 'updated by trigger'
2372
WHERE f_int1 = - old.f_int1;
2375
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2377
# check trigger-7 success: 1
2379
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2380
f_int2 = CAST(f_char1 AS SIGNED INT),
2381
f_charbig = 'just inserted'
2382
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2384
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2385
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2386
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2387
'just inserted' FROM t0_template
2388
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2389
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
2391
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2392
f_charbig = 'updated by trigger'
2393
WHERE f_int1 = - old.f_int1;
2396
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2398
# check trigger-8 success: 1
2400
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2401
f_int2 = CAST(f_char1 AS SIGNED INT),
2402
f_charbig = 'just inserted'
2403
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2405
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2407
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2408
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2410
SET new.f_int1 = old.f_int1 + @max_row,
2411
new.f_int2 = old.f_int2 - @max_row,
2412
new.f_charbig = '####updated per update trigger####';
2415
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2416
f_charbig = '####updated per update statement itself####';
2418
# check trigger-9 success: 1
2420
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2421
f_int2 = CAST(f_char1 AS SIGNED INT),
2422
f_charbig = CONCAT('===',f_char1,'===');
2423
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2425
SET new.f_int1 = new.f_int1 + @max_row,
2426
new.f_int2 = new.f_int2 - @max_row,
2427
new.f_charbig = '####updated per update trigger####';
2430
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2431
f_charbig = '####updated per update statement itself####';
2433
# check trigger-10 success: 1
2435
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2436
f_int2 = CAST(f_char1 AS SIGNED INT),
2437
f_charbig = CONCAT('===',f_char1,'===');
2438
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2440
SET new.f_int1 = @my_max1 + @counter,
2441
new.f_int2 = @my_min2 - @counter,
2442
new.f_charbig = '####updated per insert trigger####';
2443
SET @counter = @counter + 1;
2446
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2447
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2448
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2449
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2450
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2454
# check trigger-11 success: 1
2456
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2457
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2458
AND f_charbig = '####updated per insert trigger####';
2459
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2461
SET new.f_int1 = @my_max1 + @counter,
2462
new.f_int2 = @my_min2 - @counter,
2463
new.f_charbig = '####updated per insert trigger####';
2464
SET @counter = @counter + 1;
2467
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2468
INSERT INTO t1 (f_char1, f_char2, f_charbig)
2469
SELECT CAST(f_int1 AS CHAR),
2470
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2471
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2475
# check trigger-12 success: 1
2477
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2478
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2479
AND f_charbig = '####updated per insert trigger####';
2481
Table Op Msg_type Msg_text
2482
test.t1 analyze status OK
2483
CHECK TABLE t1 EXTENDED;
2484
Table Op Msg_type Msg_text
2485
test.t1 check status OK
2486
CHECKSUM TABLE t1 EXTENDED;
2488
test.t1 <some_value>
2490
Table Op Msg_type Msg_text
2491
test.t1 optimize status OK
2492
# check layout success: 1
2493
REPAIR TABLE t1 EXTENDED;
2494
Table Op Msg_type Msg_text
2495
test.t1 repair status OK
2496
# check layout success: 1
2499
# check TRUNCATE success: 1
2500
# check layout success: 1
2501
# End usability test (inc/partition_check.inc)
2508
f_charbig VARCHAR(1000)
2510
PARTITION BY RANGE(f_int1)
2511
SUBPARTITION BY HASH(f_int1)
2512
( PARTITION part1 VALUES LESS THAN (10)
2513
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
2514
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
2515
PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = 'MyISAM'
2516
(SUBPARTITION subpart21 ENGINE = 'MyISAM',
2517
SUBPARTITION subpart22)
2519
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2520
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2521
# Start usability test (inc/partition_check.inc)
2523
SHOW CREATE TABLE t1;
2525
t1 CREATE TABLE `t1` (
2526
`f_int1` int(11) DEFAULT NULL,
2527
`f_int2` int(11) DEFAULT NULL,
2528
`f_char1` char(20) DEFAULT NULL,
2529
`f_char2` char(20) DEFAULT NULL,
2530
`f_charbig` varchar(1000) DEFAULT NULL
2531
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2532
/*!50100 PARTITION BY RANGE (f_int1)
2533
SUBPARTITION BY HASH (f_int1)
2534
(PARTITION part1 VALUES LESS THAN (10)
2535
(SUBPARTITION subpart11 ENGINE = MyISAM,
2536
SUBPARTITION subpart12 ENGINE = MyISAM),
2537
PARTITION part2 VALUES LESS THAN (2147483646)
2538
(SUBPARTITION subpart21 ENGINE = MyISAM,
2539
SUBPARTITION subpart22 ENGINE = MyISAM)) */
2542
t1#P#part1#SP#subpart11.MYD
2543
t1#P#part1#SP#subpart11.MYI
2544
t1#P#part1#SP#subpart12.MYD
2545
t1#P#part1#SP#subpart12.MYI
2546
t1#P#part2#SP#subpart21.MYD
2547
t1#P#part2#SP#subpart21.MYI
2548
t1#P#part2#SP#subpart22.MYD
2549
t1#P#part2#SP#subpart22.MYI
2553
# check prerequisites-1 success: 1
2554
# check COUNT(*) success: 1
2555
# check MIN/MAX(f_int1) success: 1
2556
# check MIN/MAX(f_int2) success: 1
2557
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2558
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2559
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2560
WHERE f_int1 IN (2,3);
2561
# check prerequisites-3 success: 1
2562
DELETE FROM t1 WHERE f_charbig = 'delete me';
2563
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2564
# check read via f_int1 success: 1
2565
# check read via f_int2 success: 1
2567
# check multiple-1 success: 1
2568
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2570
# check multiple-2 success: 1
2571
INSERT INTO t1 SELECT * FROM t0_template
2572
WHERE MOD(f_int1,3) = 0;
2574
# check multiple-3 success: 1
2575
UPDATE t1 SET f_int1 = f_int1 + @max_row
2576
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2577
AND @max_row_div2 + @max_row_div4;
2579
# check multiple-4 success: 1
2581
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2582
AND @max_row_div2 + @max_row_div4 + @max_row;
2584
# check multiple-5 success: 1
2585
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2587
SET f_int1 = @cur_value , f_int2 = @cur_value,
2588
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2589
f_charbig = '#SINGLE#';
2591
# check single-1 success: 1
2592
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2594
SET f_int1 = @cur_value , f_int2 = @cur_value,
2595
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2596
f_charbig = '#SINGLE#';
2598
# check single-2 success: 1
2599
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2600
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2601
UPDATE t1 SET f_int1 = @cur_value2
2602
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2604
# check single-3 success: 1
2605
SET @cur_value1= -1;
2606
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2607
UPDATE t1 SET f_int1 = @cur_value1
2608
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2610
# check single-4 success: 1
2611
SELECT MAX(f_int1) INTO @cur_value FROM t1;
2612
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
2614
# check single-5 success: 1
2615
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
2617
# check single-6 success: 1
2618
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
2619
ERROR HY000: Table has no partition for value 2147483647
2620
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
2621
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
2622
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
2623
f_charbig = '#NULL#';
2625
SET f_int1 = NULL , f_int2 = -@max_row,
2626
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
2627
f_charbig = '#NULL#';
2628
# check null success: 1
2630
# check null-1 success: 1
2631
UPDATE t1 SET f_int1 = -@max_row
2632
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2633
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2635
# check null-2 success: 1
2636
UPDATE t1 SET f_int1 = NULL
2637
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2638
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2640
# check null-3 success: 1
2642
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2643
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2645
# check null-4 success: 1
2647
WHERE f_int1 = 0 AND f_int2 = 0
2648
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
2649
AND f_charbig = '#NULL#';
2651
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2652
SELECT f_int1, f_int1, '', '', 'was inserted'
2653
FROM t0_template source_tab
2654
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2656
# check transactions-1 success: 1
2659
# check transactions-2 success: 1
2662
# check transactions-3 success: 1
2663
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2667
# check transactions-4 success: 1
2668
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2669
SELECT f_int1, f_int1, '', '', 'was inserted'
2670
FROM t0_template source_tab
2671
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2673
# check transactions-5 success: 1
2676
Warning 1196 Some non-transactional changed tables couldn't be rolled back
2678
# check transactions-6 success: 1
2679
# INFO: Storage engine used for t1 seems to be not transactional.
2682
# check transactions-7 success: 1
2683
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2685
SET @@session.sql_mode = 'traditional';
2686
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
2687
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2688
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
2689
'', '', 'was inserted' FROM t0_template
2690
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2691
ERROR 22012: Division by 0
2694
# check transactions-8 success: 1
2695
# INFO: Storage engine used for t1 seems to be unable to revert
2696
# changes made by the failing statement.
2697
SET @@session.sql_mode = '';
2699
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2701
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
2703
# check special-1 success: 1
2704
UPDATE t1 SET f_charbig = '';
2706
# check special-2 success: 1
2707
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
2708
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2709
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
2710
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2711
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2712
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2713
'just inserted' FROM t0_template
2714
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2715
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
2717
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2718
f_charbig = 'updated by trigger'
2719
WHERE f_int1 = new.f_int1;
2721
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2722
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2723
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2725
# check trigger-1 success: 1
2727
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2728
f_int2 = CAST(f_char1 AS SIGNED INT),
2729
f_charbig = 'just inserted'
2730
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2732
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2733
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2734
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2735
'just inserted' FROM t0_template
2736
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2737
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
2739
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2740
f_charbig = 'updated by trigger'
2741
WHERE f_int1 = new.f_int1;
2743
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2744
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2745
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2747
# check trigger-2 success: 1
2749
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2750
f_int2 = CAST(f_char1 AS SIGNED INT),
2751
f_charbig = 'just inserted'
2752
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2754
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2755
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2756
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2757
'just inserted' FROM t0_template
2758
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2759
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2761
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2762
f_charbig = 'updated by trigger'
2763
WHERE f_int1 = new.f_int1;
2765
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2766
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2768
# check trigger-3 success: 1
2770
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2771
f_int2 = CAST(f_char1 AS SIGNED INT),
2772
f_charbig = 'just inserted'
2773
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2775
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2776
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2777
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2778
'just inserted' FROM t0_template
2779
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2780
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2782
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2783
f_charbig = 'updated by trigger'
2784
WHERE f_int1 = - old.f_int1;
2786
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2787
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2789
# check trigger-4 success: 1
2791
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2792
f_int2 = CAST(f_char1 AS SIGNED INT),
2793
f_charbig = 'just inserted'
2794
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2796
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2797
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2798
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2799
'just inserted' FROM t0_template
2800
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2801
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2803
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2804
f_charbig = 'updated by trigger'
2805
WHERE f_int1 = new.f_int1;
2807
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2808
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2810
# check trigger-5 success: 1
2812
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2813
f_int2 = CAST(f_char1 AS SIGNED INT),
2814
f_charbig = 'just inserted'
2815
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2817
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2818
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2819
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2820
'just inserted' FROM t0_template
2821
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2822
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2824
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2825
f_charbig = 'updated by trigger'
2826
WHERE f_int1 = - old.f_int1;
2828
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2829
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2831
# check trigger-6 success: 1
2833
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2834
f_int2 = CAST(f_char1 AS SIGNED INT),
2835
f_charbig = 'just inserted'
2836
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2838
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2839
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2840
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2841
'just inserted' FROM t0_template
2842
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2843
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
2845
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2846
f_charbig = 'updated by trigger'
2847
WHERE f_int1 = - old.f_int1;
2850
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2852
# check trigger-7 success: 1
2854
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2855
f_int2 = CAST(f_char1 AS SIGNED INT),
2856
f_charbig = 'just inserted'
2857
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2859
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2860
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2861
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2862
'just inserted' FROM t0_template
2863
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2864
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
2866
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2867
f_charbig = 'updated by trigger'
2868
WHERE f_int1 = - old.f_int1;
2871
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2873
# check trigger-8 success: 1
2875
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2876
f_int2 = CAST(f_char1 AS SIGNED INT),
2877
f_charbig = 'just inserted'
2878
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2880
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2882
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2883
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2885
SET new.f_int1 = old.f_int1 + @max_row,
2886
new.f_int2 = old.f_int2 - @max_row,
2887
new.f_charbig = '####updated per update trigger####';
2890
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2891
f_charbig = '####updated per update statement itself####';
2893
# check trigger-9 success: 1
2895
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2896
f_int2 = CAST(f_char1 AS SIGNED INT),
2897
f_charbig = CONCAT('===',f_char1,'===');
2898
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2900
SET new.f_int1 = new.f_int1 + @max_row,
2901
new.f_int2 = new.f_int2 - @max_row,
2902
new.f_charbig = '####updated per update trigger####';
2905
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2906
f_charbig = '####updated per update statement itself####';
2908
# check trigger-10 success: 1
2910
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2911
f_int2 = CAST(f_char1 AS SIGNED INT),
2912
f_charbig = CONCAT('===',f_char1,'===');
2913
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2915
SET new.f_int1 = @my_max1 + @counter,
2916
new.f_int2 = @my_min2 - @counter,
2917
new.f_charbig = '####updated per insert trigger####';
2918
SET @counter = @counter + 1;
2921
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2922
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2923
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2924
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2925
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2929
# check trigger-11 success: 1
2931
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2932
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2933
AND f_charbig = '####updated per insert trigger####';
2934
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2936
SET new.f_int1 = @my_max1 + @counter,
2937
new.f_int2 = @my_min2 - @counter,
2938
new.f_charbig = '####updated per insert trigger####';
2939
SET @counter = @counter + 1;
2942
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2943
INSERT INTO t1 (f_char1, f_char2, f_charbig)
2944
SELECT CAST(f_int1 AS CHAR),
2945
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2946
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2950
# check trigger-12 success: 1
2952
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2953
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2954
AND f_charbig = '####updated per insert trigger####';
2956
Table Op Msg_type Msg_text
2957
test.t1 analyze status OK
2958
CHECK TABLE t1 EXTENDED;
2959
Table Op Msg_type Msg_text
2960
test.t1 check status OK
2961
CHECKSUM TABLE t1 EXTENDED;
2963
test.t1 <some_value>
2965
Table Op Msg_type Msg_text
2966
test.t1 optimize status OK
2967
# check layout success: 1
2968
REPAIR TABLE t1 EXTENDED;
2969
Table Op Msg_type Msg_text
2970
test.t1 repair status OK
2971
# check layout success: 1
2974
# check TRUNCATE success: 1
2975
# check layout success: 1
2976
# End usability test (inc/partition_check.inc)
2978
#------------------------------------------------------------------------
2979
# 5 Precedence of storage engine assignments (if there is any)
2980
#------------------------------------------------------------------------
2981
# 5.1 Storage engine assignment after column list + after partition
2982
# or subpartition name
2988
f_charbig VARCHAR(1000)
2990
PARTITION BY HASH(f_int1)
2991
( PARTITION part1 STORAGE ENGINE = 'MyISAM',
2992
PARTITION part2 STORAGE ENGINE = 'MyISAM'
2994
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2995
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2996
# Start usability test (inc/partition_check.inc)
2998
SHOW CREATE TABLE t1;
3000
t1 CREATE TABLE `t1` (
3001
`f_int1` int(11) DEFAULT NULL,
3002
`f_int2` int(11) DEFAULT NULL,
3003
`f_char1` char(20) DEFAULT NULL,
3004
`f_char2` char(20) DEFAULT NULL,
3005
`f_charbig` varchar(1000) DEFAULT NULL
3006
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3007
/*!50100 PARTITION BY HASH (f_int1)
3008
(PARTITION part1 ENGINE = MyISAM,
3009
PARTITION part2 ENGINE = MyISAM) */
3019
# check prerequisites-1 success: 1
3020
# check COUNT(*) success: 1
3021
# check MIN/MAX(f_int1) success: 1
3022
# check MIN/MAX(f_int2) success: 1
3023
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3024
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3025
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
3026
WHERE f_int1 IN (2,3);
3027
# check prerequisites-3 success: 1
3028
DELETE FROM t1 WHERE f_charbig = 'delete me';
3029
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
3030
# check read via f_int1 success: 1
3031
# check read via f_int2 success: 1
3033
# check multiple-1 success: 1
3034
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
3036
# check multiple-2 success: 1
3037
INSERT INTO t1 SELECT * FROM t0_template
3038
WHERE MOD(f_int1,3) = 0;
3040
# check multiple-3 success: 1
3041
UPDATE t1 SET f_int1 = f_int1 + @max_row
3042
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
3043
AND @max_row_div2 + @max_row_div4;
3045
# check multiple-4 success: 1
3047
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
3048
AND @max_row_div2 + @max_row_div4 + @max_row;
3050
# check multiple-5 success: 1
3051
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
3053
SET f_int1 = @cur_value , f_int2 = @cur_value,
3054
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3055
f_charbig = '#SINGLE#';
3057
# check single-1 success: 1
3058
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
3060
SET f_int1 = @cur_value , f_int2 = @cur_value,
3061
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3062
f_charbig = '#SINGLE#';
3064
# check single-2 success: 1
3065
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
3066
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
3067
UPDATE t1 SET f_int1 = @cur_value2
3068
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
3070
# check single-3 success: 1
3071
SET @cur_value1= -1;
3072
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
3073
UPDATE t1 SET f_int1 = @cur_value1
3074
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
3076
# check single-4 success: 1
3077
SELECT MAX(f_int1) INTO @cur_value FROM t1;
3078
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
3080
# check single-5 success: 1
3081
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
3083
# check single-6 success: 1
3084
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3086
# check single-7 success: 1
3087
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
3088
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3089
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3090
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3091
f_charbig = '#NULL#';
3093
SET f_int1 = NULL , f_int2 = -@max_row,
3094
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3095
f_charbig = '#NULL#';
3096
# check null success: 1
3098
# check null-1 success: 1
3099
UPDATE t1 SET f_int1 = -@max_row
3100
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3101
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3103
# check null-2 success: 1
3104
UPDATE t1 SET f_int1 = NULL
3105
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3106
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3108
# check null-3 success: 1
3110
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3111
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3113
# check null-4 success: 1
3115
WHERE f_int1 = 0 AND f_int2 = 0
3116
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
3117
AND f_charbig = '#NULL#';
3119
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3120
SELECT f_int1, f_int1, '', '', 'was inserted'
3121
FROM t0_template source_tab
3122
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3124
# check transactions-1 success: 1
3127
# check transactions-2 success: 1
3130
# check transactions-3 success: 1
3131
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3135
# check transactions-4 success: 1
3136
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3137
SELECT f_int1, f_int1, '', '', 'was inserted'
3138
FROM t0_template source_tab
3139
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3141
# check transactions-5 success: 1
3144
Warning 1196 Some non-transactional changed tables couldn't be rolled back
3146
# check transactions-6 success: 1
3147
# INFO: Storage engine used for t1 seems to be not transactional.
3150
# check transactions-7 success: 1
3151
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3153
SET @@session.sql_mode = 'traditional';
3154
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
3155
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3156
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
3157
'', '', 'was inserted' FROM t0_template
3158
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3159
ERROR 22012: Division by 0
3162
# check transactions-8 success: 1
3163
# INFO: Storage engine used for t1 seems to be unable to revert
3164
# changes made by the failing statement.
3165
SET @@session.sql_mode = '';
3167
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3169
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
3171
# check special-1 success: 1
3172
UPDATE t1 SET f_charbig = '';
3174
# check special-2 success: 1
3175
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
3176
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3177
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
3178
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3179
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3180
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3181
'just inserted' FROM t0_template
3182
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3183
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
3185
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3186
f_charbig = 'updated by trigger'
3187
WHERE f_int1 = new.f_int1;
3189
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3190
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3191
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3193
# check trigger-1 success: 1
3195
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3196
f_int2 = CAST(f_char1 AS SIGNED INT),
3197
f_charbig = 'just inserted'
3198
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3200
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3201
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3202
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3203
'just inserted' FROM t0_template
3204
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3205
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
3207
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3208
f_charbig = 'updated by trigger'
3209
WHERE f_int1 = new.f_int1;
3211
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3212
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3213
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3215
# check trigger-2 success: 1
3217
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3218
f_int2 = CAST(f_char1 AS SIGNED INT),
3219
f_charbig = 'just inserted'
3220
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3222
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3223
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3224
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3225
'just inserted' FROM t0_template
3226
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3227
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3229
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3230
f_charbig = 'updated by trigger'
3231
WHERE f_int1 = new.f_int1;
3233
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3234
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3236
# check trigger-3 success: 1
3238
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3239
f_int2 = CAST(f_char1 AS SIGNED INT),
3240
f_charbig = 'just inserted'
3241
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3243
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3244
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3245
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3246
'just inserted' FROM t0_template
3247
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3248
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3250
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3251
f_charbig = 'updated by trigger'
3252
WHERE f_int1 = - old.f_int1;
3254
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3255
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3257
# check trigger-4 success: 1
3259
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3260
f_int2 = CAST(f_char1 AS SIGNED INT),
3261
f_charbig = 'just inserted'
3262
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3264
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3265
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3266
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3267
'just inserted' FROM t0_template
3268
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3269
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3271
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3272
f_charbig = 'updated by trigger'
3273
WHERE f_int1 = new.f_int1;
3275
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3276
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3278
# check trigger-5 success: 1
3280
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3281
f_int2 = CAST(f_char1 AS SIGNED INT),
3282
f_charbig = 'just inserted'
3283
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3285
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3286
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3287
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3288
'just inserted' FROM t0_template
3289
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3290
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3292
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3293
f_charbig = 'updated by trigger'
3294
WHERE f_int1 = - old.f_int1;
3296
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3297
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3299
# check trigger-6 success: 1
3301
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3302
f_int2 = CAST(f_char1 AS SIGNED INT),
3303
f_charbig = 'just inserted'
3304
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3306
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3307
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3308
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3309
'just inserted' FROM t0_template
3310
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3311
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
3313
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3314
f_charbig = 'updated by trigger'
3315
WHERE f_int1 = - old.f_int1;
3318
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3320
# check trigger-7 success: 1
3322
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3323
f_int2 = CAST(f_char1 AS SIGNED INT),
3324
f_charbig = 'just inserted'
3325
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3327
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3328
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3329
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3330
'just inserted' FROM t0_template
3331
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3332
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
3334
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3335
f_charbig = 'updated by trigger'
3336
WHERE f_int1 = - old.f_int1;
3339
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3341
# check trigger-8 success: 1
3343
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3344
f_int2 = CAST(f_char1 AS SIGNED INT),
3345
f_charbig = 'just inserted'
3346
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3348
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3350
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3351
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3353
SET new.f_int1 = old.f_int1 + @max_row,
3354
new.f_int2 = old.f_int2 - @max_row,
3355
new.f_charbig = '####updated per update trigger####';
3358
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3359
f_charbig = '####updated per update statement itself####';
3361
# check trigger-9 success: 1
3363
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3364
f_int2 = CAST(f_char1 AS SIGNED INT),
3365
f_charbig = CONCAT('===',f_char1,'===');
3366
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3368
SET new.f_int1 = new.f_int1 + @max_row,
3369
new.f_int2 = new.f_int2 - @max_row,
3370
new.f_charbig = '####updated per update trigger####';
3373
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3374
f_charbig = '####updated per update statement itself####';
3376
# check trigger-10 success: 1
3378
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3379
f_int2 = CAST(f_char1 AS SIGNED INT),
3380
f_charbig = CONCAT('===',f_char1,'===');
3381
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3383
SET new.f_int1 = @my_max1 + @counter,
3384
new.f_int2 = @my_min2 - @counter,
3385
new.f_charbig = '####updated per insert trigger####';
3386
SET @counter = @counter + 1;
3389
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3390
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3391
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3392
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3393
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3397
# check trigger-11 success: 1
3399
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3400
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3401
AND f_charbig = '####updated per insert trigger####';
3402
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3404
SET new.f_int1 = @my_max1 + @counter,
3405
new.f_int2 = @my_min2 - @counter,
3406
new.f_charbig = '####updated per insert trigger####';
3407
SET @counter = @counter + 1;
3410
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3411
INSERT INTO t1 (f_char1, f_char2, f_charbig)
3412
SELECT CAST(f_int1 AS CHAR),
3413
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3414
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3418
# check trigger-12 success: 1
3420
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3421
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3422
AND f_charbig = '####updated per insert trigger####';
3424
Table Op Msg_type Msg_text
3425
test.t1 analyze status OK
3426
CHECK TABLE t1 EXTENDED;
3427
Table Op Msg_type Msg_text
3428
test.t1 check status OK
3429
CHECKSUM TABLE t1 EXTENDED;
3431
test.t1 <some_value>
3433
Table Op Msg_type Msg_text
3434
test.t1 optimize status OK
3435
# check layout success: 1
3436
REPAIR TABLE t1 EXTENDED;
3437
Table Op Msg_type Msg_text
3438
test.t1 repair status OK
3439
# check layout success: 1
3442
# check TRUNCATE success: 1
3443
# check layout success: 1
3444
# End usability test (inc/partition_check.inc)
3451
f_charbig VARCHAR(1000)
3453
PARTITION BY RANGE(f_int1)
3454
SUBPARTITION BY HASH(f_int1)
3455
( PARTITION part1 VALUES LESS THAN (10)
3456
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
3457
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
3458
PARTITION part2 VALUES LESS THAN (2147483646)
3459
(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
3460
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
3462
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3463
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
3464
# Start usability test (inc/partition_check.inc)
3466
SHOW CREATE TABLE t1;
3468
t1 CREATE TABLE `t1` (
3469
`f_int1` int(11) DEFAULT NULL,
3470
`f_int2` int(11) DEFAULT NULL,
3471
`f_char1` char(20) DEFAULT NULL,
3472
`f_char2` char(20) DEFAULT NULL,
3473
`f_charbig` varchar(1000) DEFAULT NULL
3474
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3475
/*!50100 PARTITION BY RANGE (f_int1)
3476
SUBPARTITION BY HASH (f_int1)
3477
(PARTITION part1 VALUES LESS THAN (10)
3478
(SUBPARTITION subpart11 ENGINE = MyISAM,
3479
SUBPARTITION subpart12 ENGINE = MyISAM),
3480
PARTITION part2 VALUES LESS THAN (2147483646)
3481
(SUBPARTITION subpart21 ENGINE = MyISAM,
3482
SUBPARTITION subpart22 ENGINE = MyISAM)) */
3485
t1#P#part1#SP#subpart11.MYD
3486
t1#P#part1#SP#subpart11.MYI
3487
t1#P#part1#SP#subpart12.MYD
3488
t1#P#part1#SP#subpart12.MYI
3489
t1#P#part2#SP#subpart21.MYD
3490
t1#P#part2#SP#subpart21.MYI
3491
t1#P#part2#SP#subpart22.MYD
3492
t1#P#part2#SP#subpart22.MYI
3496
# check prerequisites-1 success: 1
3497
# check COUNT(*) success: 1
3498
# check MIN/MAX(f_int1) success: 1
3499
# check MIN/MAX(f_int2) success: 1
3500
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3501
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3502
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
3503
WHERE f_int1 IN (2,3);
3504
# check prerequisites-3 success: 1
3505
DELETE FROM t1 WHERE f_charbig = 'delete me';
3506
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
3507
# check read via f_int1 success: 1
3508
# check read via f_int2 success: 1
3510
# check multiple-1 success: 1
3511
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
3513
# check multiple-2 success: 1
3514
INSERT INTO t1 SELECT * FROM t0_template
3515
WHERE MOD(f_int1,3) = 0;
3517
# check multiple-3 success: 1
3518
UPDATE t1 SET f_int1 = f_int1 + @max_row
3519
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
3520
AND @max_row_div2 + @max_row_div4;
3522
# check multiple-4 success: 1
3524
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
3525
AND @max_row_div2 + @max_row_div4 + @max_row;
3527
# check multiple-5 success: 1
3528
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
3530
SET f_int1 = @cur_value , f_int2 = @cur_value,
3531
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3532
f_charbig = '#SINGLE#';
3534
# check single-1 success: 1
3535
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
3537
SET f_int1 = @cur_value , f_int2 = @cur_value,
3538
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3539
f_charbig = '#SINGLE#';
3541
# check single-2 success: 1
3542
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
3543
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
3544
UPDATE t1 SET f_int1 = @cur_value2
3545
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
3547
# check single-3 success: 1
3548
SET @cur_value1= -1;
3549
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
3550
UPDATE t1 SET f_int1 = @cur_value1
3551
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
3553
# check single-4 success: 1
3554
SELECT MAX(f_int1) INTO @cur_value FROM t1;
3555
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
3557
# check single-5 success: 1
3558
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
3560
# check single-6 success: 1
3561
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3562
ERROR HY000: Table has no partition for value 2147483647
3563
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3564
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3565
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3566
f_charbig = '#NULL#';
3568
SET f_int1 = NULL , f_int2 = -@max_row,
3569
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3570
f_charbig = '#NULL#';
3571
# check null success: 1
3573
# check null-1 success: 1
3574
UPDATE t1 SET f_int1 = -@max_row
3575
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3576
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3578
# check null-2 success: 1
3579
UPDATE t1 SET f_int1 = NULL
3580
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3581
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3583
# check null-3 success: 1
3585
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3586
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3588
# check null-4 success: 1
3590
WHERE f_int1 = 0 AND f_int2 = 0
3591
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
3592
AND f_charbig = '#NULL#';
3594
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3595
SELECT f_int1, f_int1, '', '', 'was inserted'
3596
FROM t0_template source_tab
3597
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3599
# check transactions-1 success: 1
3602
# check transactions-2 success: 1
3605
# check transactions-3 success: 1
3606
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3610
# check transactions-4 success: 1
3611
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3612
SELECT f_int1, f_int1, '', '', 'was inserted'
3613
FROM t0_template source_tab
3614
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3616
# check transactions-5 success: 1
3619
Warning 1196 Some non-transactional changed tables couldn't be rolled back
3621
# check transactions-6 success: 1
3622
# INFO: Storage engine used for t1 seems to be not transactional.
3625
# check transactions-7 success: 1
3626
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3628
SET @@session.sql_mode = 'traditional';
3629
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
3630
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3631
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
3632
'', '', 'was inserted' FROM t0_template
3633
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3634
ERROR 22012: Division by 0
3637
# check transactions-8 success: 1
3638
# INFO: Storage engine used for t1 seems to be unable to revert
3639
# changes made by the failing statement.
3640
SET @@session.sql_mode = '';
3642
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3644
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
3646
# check special-1 success: 1
3647
UPDATE t1 SET f_charbig = '';
3649
# check special-2 success: 1
3650
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
3651
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3652
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
3653
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3654
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3655
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3656
'just inserted' FROM t0_template
3657
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3658
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
3660
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3661
f_charbig = 'updated by trigger'
3662
WHERE f_int1 = new.f_int1;
3664
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3665
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3666
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3668
# check trigger-1 success: 1
3670
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3671
f_int2 = CAST(f_char1 AS SIGNED INT),
3672
f_charbig = 'just inserted'
3673
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3675
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3676
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3677
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3678
'just inserted' FROM t0_template
3679
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3680
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
3682
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3683
f_charbig = 'updated by trigger'
3684
WHERE f_int1 = new.f_int1;
3686
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3687
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3688
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3690
# check trigger-2 success: 1
3692
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3693
f_int2 = CAST(f_char1 AS SIGNED INT),
3694
f_charbig = 'just inserted'
3695
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3697
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3698
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3699
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3700
'just inserted' FROM t0_template
3701
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3702
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3704
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3705
f_charbig = 'updated by trigger'
3706
WHERE f_int1 = new.f_int1;
3708
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3709
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3711
# check trigger-3 success: 1
3713
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3714
f_int2 = CAST(f_char1 AS SIGNED INT),
3715
f_charbig = 'just inserted'
3716
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3718
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3719
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3720
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3721
'just inserted' FROM t0_template
3722
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3723
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3725
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3726
f_charbig = 'updated by trigger'
3727
WHERE f_int1 = - old.f_int1;
3729
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3730
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3732
# check trigger-4 success: 1
3734
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3735
f_int2 = CAST(f_char1 AS SIGNED INT),
3736
f_charbig = 'just inserted'
3737
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3739
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3740
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3741
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3742
'just inserted' FROM t0_template
3743
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3744
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3746
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3747
f_charbig = 'updated by trigger'
3748
WHERE f_int1 = new.f_int1;
3750
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3751
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3753
# check trigger-5 success: 1
3755
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3756
f_int2 = CAST(f_char1 AS SIGNED INT),
3757
f_charbig = 'just inserted'
3758
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3760
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3761
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3762
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3763
'just inserted' FROM t0_template
3764
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3765
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3767
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3768
f_charbig = 'updated by trigger'
3769
WHERE f_int1 = - old.f_int1;
3771
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3772
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3774
# check trigger-6 success: 1
3776
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3777
f_int2 = CAST(f_char1 AS SIGNED INT),
3778
f_charbig = 'just inserted'
3779
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3781
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3782
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3783
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3784
'just inserted' FROM t0_template
3785
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3786
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
3788
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3789
f_charbig = 'updated by trigger'
3790
WHERE f_int1 = - old.f_int1;
3793
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3795
# check trigger-7 success: 1
3797
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3798
f_int2 = CAST(f_char1 AS SIGNED INT),
3799
f_charbig = 'just inserted'
3800
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3802
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3803
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3804
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3805
'just inserted' FROM t0_template
3806
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3807
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
3809
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3810
f_charbig = 'updated by trigger'
3811
WHERE f_int1 = - old.f_int1;
3814
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3816
# check trigger-8 success: 1
3818
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3819
f_int2 = CAST(f_char1 AS SIGNED INT),
3820
f_charbig = 'just inserted'
3821
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3823
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3825
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3826
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3828
SET new.f_int1 = old.f_int1 + @max_row,
3829
new.f_int2 = old.f_int2 - @max_row,
3830
new.f_charbig = '####updated per update trigger####';
3833
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3834
f_charbig = '####updated per update statement itself####';
3836
# check trigger-9 success: 1
3838
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3839
f_int2 = CAST(f_char1 AS SIGNED INT),
3840
f_charbig = CONCAT('===',f_char1,'===');
3841
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3843
SET new.f_int1 = new.f_int1 + @max_row,
3844
new.f_int2 = new.f_int2 - @max_row,
3845
new.f_charbig = '####updated per update trigger####';
3848
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3849
f_charbig = '####updated per update statement itself####';
3851
# check trigger-10 success: 1
3853
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3854
f_int2 = CAST(f_char1 AS SIGNED INT),
3855
f_charbig = CONCAT('===',f_char1,'===');
3856
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3858
SET new.f_int1 = @my_max1 + @counter,
3859
new.f_int2 = @my_min2 - @counter,
3860
new.f_charbig = '####updated per insert trigger####';
3861
SET @counter = @counter + 1;
3864
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3865
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3866
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3867
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3868
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3872
# check trigger-11 success: 1
3874
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3875
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3876
AND f_charbig = '####updated per insert trigger####';
3877
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3879
SET new.f_int1 = @my_max1 + @counter,
3880
new.f_int2 = @my_min2 - @counter,
3881
new.f_charbig = '####updated per insert trigger####';
3882
SET @counter = @counter + 1;
3885
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3886
INSERT INTO t1 (f_char1, f_char2, f_charbig)
3887
SELECT CAST(f_int1 AS CHAR),
3888
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3889
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3893
# check trigger-12 success: 1
3895
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3896
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3897
AND f_charbig = '####updated per insert trigger####';
3899
Table Op Msg_type Msg_text
3900
test.t1 analyze status OK
3901
CHECK TABLE t1 EXTENDED;
3902
Table Op Msg_type Msg_text
3903
test.t1 check status OK
3904
CHECKSUM TABLE t1 EXTENDED;
3906
test.t1 <some_value>
3908
Table Op Msg_type Msg_text
3909
test.t1 optimize status OK
3910
# check layout success: 1
3911
REPAIR TABLE t1 EXTENDED;
3912
Table Op Msg_type Msg_text
3913
test.t1 repair status OK
3914
# check layout success: 1
3917
# check TRUNCATE success: 1
3918
# check layout success: 1
3919
# End usability test (inc/partition_check.inc)
3921
# 6.2 Storage engine assignment after partition name + after
3923
# in partition part + in sub partition part
3929
f_charbig VARCHAR(1000)
3931
PARTITION BY RANGE(f_int1)
3932
SUBPARTITION BY HASH(f_int1)
3933
( PARTITION part1 VALUES LESS THAN (10) STORAGE ENGINE = 'MyISAM'
3934
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
3935
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
3936
PARTITION part2 VALUES LESS THAN (2147483646)
3937
(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
3938
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
3940
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3941
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
3942
# Start usability test (inc/partition_check.inc)
3944
SHOW CREATE TABLE t1;
3946
t1 CREATE TABLE `t1` (
3947
`f_int1` int(11) DEFAULT NULL,
3948
`f_int2` int(11) DEFAULT NULL,
3949
`f_char1` char(20) DEFAULT NULL,
3950
`f_char2` char(20) DEFAULT NULL,
3951
`f_charbig` varchar(1000) DEFAULT NULL
3952
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3953
/*!50100 PARTITION BY RANGE (f_int1)
3954
SUBPARTITION BY HASH (f_int1)
3955
(PARTITION part1 VALUES LESS THAN (10)
3956
(SUBPARTITION subpart11 ENGINE = MyISAM,
3957
SUBPARTITION subpart12 ENGINE = MyISAM),
3958
PARTITION part2 VALUES LESS THAN (2147483646)
3959
(SUBPARTITION subpart21 ENGINE = MyISAM,
3960
SUBPARTITION subpart22 ENGINE = MyISAM)) */
3963
t1#P#part1#SP#subpart11.MYD
3964
t1#P#part1#SP#subpart11.MYI
3965
t1#P#part1#SP#subpart12.MYD
3966
t1#P#part1#SP#subpart12.MYI
3967
t1#P#part2#SP#subpart21.MYD
3968
t1#P#part2#SP#subpart21.MYI
3969
t1#P#part2#SP#subpart22.MYD
3970
t1#P#part2#SP#subpart22.MYI
3974
# check prerequisites-1 success: 1
3975
# check COUNT(*) success: 1
3976
# check MIN/MAX(f_int1) success: 1
3977
# check MIN/MAX(f_int2) success: 1
3978
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3979
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3980
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
3981
WHERE f_int1 IN (2,3);
3982
# check prerequisites-3 success: 1
3983
DELETE FROM t1 WHERE f_charbig = 'delete me';
3984
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
3985
# check read via f_int1 success: 1
3986
# check read via f_int2 success: 1
3988
# check multiple-1 success: 1
3989
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
3991
# check multiple-2 success: 1
3992
INSERT INTO t1 SELECT * FROM t0_template
3993
WHERE MOD(f_int1,3) = 0;
3995
# check multiple-3 success: 1
3996
UPDATE t1 SET f_int1 = f_int1 + @max_row
3997
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
3998
AND @max_row_div2 + @max_row_div4;
4000
# check multiple-4 success: 1
4002
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
4003
AND @max_row_div2 + @max_row_div4 + @max_row;
4005
# check multiple-5 success: 1
4006
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
4008
SET f_int1 = @cur_value , f_int2 = @cur_value,
4009
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4010
f_charbig = '#SINGLE#';
4012
# check single-1 success: 1
4013
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
4015
SET f_int1 = @cur_value , f_int2 = @cur_value,
4016
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4017
f_charbig = '#SINGLE#';
4019
# check single-2 success: 1
4020
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
4021
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
4022
UPDATE t1 SET f_int1 = @cur_value2
4023
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
4025
# check single-3 success: 1
4026
SET @cur_value1= -1;
4027
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
4028
UPDATE t1 SET f_int1 = @cur_value1
4029
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
4031
# check single-4 success: 1
4032
SELECT MAX(f_int1) INTO @cur_value FROM t1;
4033
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
4035
# check single-5 success: 1
4036
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
4038
# check single-6 success: 1
4039
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
4040
ERROR HY000: Table has no partition for value 2147483647
4041
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
4042
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
4043
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
4044
f_charbig = '#NULL#';
4046
SET f_int1 = NULL , f_int2 = -@max_row,
4047
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
4048
f_charbig = '#NULL#';
4049
# check null success: 1
4051
# check null-1 success: 1
4052
UPDATE t1 SET f_int1 = -@max_row
4053
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4054
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4056
# check null-2 success: 1
4057
UPDATE t1 SET f_int1 = NULL
4058
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4059
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4061
# check null-3 success: 1
4063
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4064
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4066
# check null-4 success: 1
4068
WHERE f_int1 = 0 AND f_int2 = 0
4069
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
4070
AND f_charbig = '#NULL#';
4072
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4073
SELECT f_int1, f_int1, '', '', 'was inserted'
4074
FROM t0_template source_tab
4075
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4077
# check transactions-1 success: 1
4080
# check transactions-2 success: 1
4083
# check transactions-3 success: 1
4084
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4088
# check transactions-4 success: 1
4089
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4090
SELECT f_int1, f_int1, '', '', 'was inserted'
4091
FROM t0_template source_tab
4092
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4094
# check transactions-5 success: 1
4097
Warning 1196 Some non-transactional changed tables couldn't be rolled back
4099
# check transactions-6 success: 1
4100
# INFO: Storage engine used for t1 seems to be not transactional.
4103
# check transactions-7 success: 1
4104
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4106
SET @@session.sql_mode = 'traditional';
4107
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
4108
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4109
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
4110
'', '', 'was inserted' FROM t0_template
4111
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4112
ERROR 22012: Division by 0
4115
# check transactions-8 success: 1
4116
# INFO: Storage engine used for t1 seems to be unable to revert
4117
# changes made by the failing statement.
4118
SET @@session.sql_mode = '';
4120
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4122
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4124
# check special-1 success: 1
4125
UPDATE t1 SET f_charbig = '';
4127
# check special-2 success: 1
4128
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4129
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4130
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4131
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4132
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4133
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4134
'just inserted' FROM t0_template
4135
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4136
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
4138
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4139
f_charbig = 'updated by trigger'
4140
WHERE f_int1 = new.f_int1;
4142
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4143
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4144
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4146
# check trigger-1 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 AFTER INSERT 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 = new.f_int1;
4164
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4165
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4166
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4168
# check trigger-2 success: 1
4170
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4171
f_int2 = CAST(f_char1 AS SIGNED INT),
4172
f_charbig = 'just inserted'
4173
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4175
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4176
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4177
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4178
'just inserted' FROM t0_template
4179
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4180
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4182
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4183
f_charbig = 'updated by trigger'
4184
WHERE f_int1 = new.f_int1;
4186
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4187
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4189
# check trigger-3 success: 1
4191
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4192
f_int2 = CAST(f_char1 AS SIGNED INT),
4193
f_charbig = 'just inserted'
4194
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4196
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4197
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4198
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4199
'just inserted' FROM t0_template
4200
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4201
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4203
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4204
f_charbig = 'updated by trigger'
4205
WHERE f_int1 = - old.f_int1;
4207
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4208
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4210
# check trigger-4 success: 1
4212
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4213
f_int2 = CAST(f_char1 AS SIGNED INT),
4214
f_charbig = 'just inserted'
4215
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4217
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4218
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4219
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4220
'just inserted' FROM t0_template
4221
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4222
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4224
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4225
f_charbig = 'updated by trigger'
4226
WHERE f_int1 = new.f_int1;
4228
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4229
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4231
# check trigger-5 success: 1
4233
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4234
f_int2 = CAST(f_char1 AS SIGNED INT),
4235
f_charbig = 'just inserted'
4236
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4238
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4239
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4240
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4241
'just inserted' FROM t0_template
4242
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4243
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4245
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4246
f_charbig = 'updated by trigger'
4247
WHERE f_int1 = - old.f_int1;
4249
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4250
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4252
# check trigger-6 success: 1
4254
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4255
f_int2 = CAST(f_char1 AS SIGNED INT),
4256
f_charbig = 'just inserted'
4257
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4259
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4260
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4261
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4262
'just inserted' FROM t0_template
4263
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4264
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
4266
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4267
f_charbig = 'updated by trigger'
4268
WHERE f_int1 = - old.f_int1;
4271
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4273
# check trigger-7 success: 1
4275
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4276
f_int2 = CAST(f_char1 AS SIGNED INT),
4277
f_charbig = 'just inserted'
4278
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4280
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4281
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4282
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4283
'just inserted' FROM t0_template
4284
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4285
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
4287
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4288
f_charbig = 'updated by trigger'
4289
WHERE f_int1 = - old.f_int1;
4292
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4294
# check trigger-8 success: 1
4296
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4297
f_int2 = CAST(f_char1 AS SIGNED INT),
4298
f_charbig = 'just inserted'
4299
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4301
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4303
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4304
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4306
SET new.f_int1 = old.f_int1 + @max_row,
4307
new.f_int2 = old.f_int2 - @max_row,
4308
new.f_charbig = '####updated per update trigger####';
4311
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4312
f_charbig = '####updated per update statement itself####';
4314
# check trigger-9 success: 1
4316
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4317
f_int2 = CAST(f_char1 AS SIGNED INT),
4318
f_charbig = CONCAT('===',f_char1,'===');
4319
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4321
SET new.f_int1 = new.f_int1 + @max_row,
4322
new.f_int2 = new.f_int2 - @max_row,
4323
new.f_charbig = '####updated per update trigger####';
4326
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4327
f_charbig = '####updated per update statement itself####';
4329
# check trigger-10 success: 1
4331
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4332
f_int2 = CAST(f_char1 AS SIGNED INT),
4333
f_charbig = CONCAT('===',f_char1,'===');
4334
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4336
SET new.f_int1 = @my_max1 + @counter,
4337
new.f_int2 = @my_min2 - @counter,
4338
new.f_charbig = '####updated per insert trigger####';
4339
SET @counter = @counter + 1;
4342
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4343
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4344
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4345
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4346
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4350
# check trigger-11 success: 1
4352
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4353
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4354
AND f_charbig = '####updated per insert trigger####';
4355
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4357
SET new.f_int1 = @my_max1 + @counter,
4358
new.f_int2 = @my_min2 - @counter,
4359
new.f_charbig = '####updated per insert trigger####';
4360
SET @counter = @counter + 1;
4363
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4364
INSERT INTO t1 (f_char1, f_char2, f_charbig)
4365
SELECT CAST(f_int1 AS CHAR),
4366
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4367
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4371
# check trigger-12 success: 1
4373
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4374
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4375
AND f_charbig = '####updated per insert trigger####';
4377
Table Op Msg_type Msg_text
4378
test.t1 analyze status OK
4379
CHECK TABLE t1 EXTENDED;
4380
Table Op Msg_type Msg_text
4381
test.t1 check status OK
4382
CHECKSUM TABLE t1 EXTENDED;
4384
test.t1 <some_value>
4386
Table Op Msg_type Msg_text
4387
test.t1 optimize status OK
4388
# check layout success: 1
4389
REPAIR TABLE t1 EXTENDED;
4390
Table Op Msg_type Msg_text
4391
test.t1 repair status OK
4392
# check layout success: 1
4395
# check TRUNCATE success: 1
4396
# check layout success: 1
4397
# End usability test (inc/partition_check.inc)
4399
#------------------------------------------------------------------------
4400
# 6 Session default engine differs from engine used within create table
4401
#------------------------------------------------------------------------
4402
SET SESSION storage_engine='MEMORY';
4408
f_charbig VARCHAR(1000)
4410
PARTITION BY HASH(f_int1) ( PARTITION part1 ENGINE = 'MyISAM');
4411
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4412
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
4413
# Start usability test (inc/partition_check.inc)
4415
SHOW CREATE TABLE t1;
4417
t1 CREATE TABLE `t1` (
4418
`f_int1` int(11) DEFAULT NULL,
4419
`f_int2` int(11) DEFAULT NULL,
4420
`f_char1` char(20) DEFAULT NULL,
4421
`f_char2` char(20) DEFAULT NULL,
4422
`f_charbig` varchar(1000) DEFAULT NULL
4423
) ENGINE=MyISAM DEFAULT CHARSET=latin1
4424
/*!50100 PARTITION BY HASH (f_int1)
4425
(PARTITION part1 ENGINE = MyISAM) */
4433
# check prerequisites-1 success: 1
4434
# check COUNT(*) success: 1
4435
# check MIN/MAX(f_int1) success: 1
4436
# check MIN/MAX(f_int2) success: 1
4437
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4438
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4439
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
4440
WHERE f_int1 IN (2,3);
4441
# check prerequisites-3 success: 1
4442
DELETE FROM t1 WHERE f_charbig = 'delete me';
4443
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
4444
# check read via f_int1 success: 1
4445
# check read via f_int2 success: 1
4447
# check multiple-1 success: 1
4448
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
4450
# check multiple-2 success: 1
4451
INSERT INTO t1 SELECT * FROM t0_template
4452
WHERE MOD(f_int1,3) = 0;
4454
# check multiple-3 success: 1
4455
UPDATE t1 SET f_int1 = f_int1 + @max_row
4456
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
4457
AND @max_row_div2 + @max_row_div4;
4459
# check multiple-4 success: 1
4461
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
4462
AND @max_row_div2 + @max_row_div4 + @max_row;
4464
# check multiple-5 success: 1
4465
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
4467
SET f_int1 = @cur_value , f_int2 = @cur_value,
4468
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4469
f_charbig = '#SINGLE#';
4471
# check single-1 success: 1
4472
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
4474
SET f_int1 = @cur_value , f_int2 = @cur_value,
4475
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4476
f_charbig = '#SINGLE#';
4478
# check single-2 success: 1
4479
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
4480
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
4481
UPDATE t1 SET f_int1 = @cur_value2
4482
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
4484
# check single-3 success: 1
4485
SET @cur_value1= -1;
4486
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
4487
UPDATE t1 SET f_int1 = @cur_value1
4488
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
4490
# check single-4 success: 1
4491
SELECT MAX(f_int1) INTO @cur_value FROM t1;
4492
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
4494
# check single-5 success: 1
4495
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
4497
# check single-6 success: 1
4498
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
4500
# check single-7 success: 1
4501
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
4502
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
4503
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
4504
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
4505
f_charbig = '#NULL#';
4507
SET f_int1 = NULL , f_int2 = -@max_row,
4508
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
4509
f_charbig = '#NULL#';
4510
# check null success: 1
4512
# check null-1 success: 1
4513
UPDATE t1 SET f_int1 = -@max_row
4514
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4515
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4517
# check null-2 success: 1
4518
UPDATE t1 SET f_int1 = NULL
4519
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4520
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4522
# check null-3 success: 1
4524
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4525
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4527
# check null-4 success: 1
4529
WHERE f_int1 = 0 AND f_int2 = 0
4530
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
4531
AND f_charbig = '#NULL#';
4533
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4534
SELECT f_int1, f_int1, '', '', 'was inserted'
4535
FROM t0_template source_tab
4536
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4538
# check transactions-1 success: 1
4541
# check transactions-2 success: 1
4544
# check transactions-3 success: 1
4545
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4549
# check transactions-4 success: 1
4550
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4551
SELECT f_int1, f_int1, '', '', 'was inserted'
4552
FROM t0_template source_tab
4553
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4555
# check transactions-5 success: 1
4558
Warning 1196 Some non-transactional changed tables couldn't be rolled back
4560
# check transactions-6 success: 1
4561
# INFO: Storage engine used for t1 seems to be not transactional.
4564
# check transactions-7 success: 1
4565
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4567
SET @@session.sql_mode = 'traditional';
4568
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
4569
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4570
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
4571
'', '', 'was inserted' FROM t0_template
4572
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4573
ERROR 22012: Division by 0
4576
# check transactions-8 success: 1
4577
# INFO: Storage engine used for t1 seems to be unable to revert
4578
# changes made by the failing statement.
4579
SET @@session.sql_mode = '';
4581
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4583
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4585
# check special-1 success: 1
4586
UPDATE t1 SET f_charbig = '';
4588
# check special-2 success: 1
4589
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4590
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4591
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4592
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4593
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4594
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4595
'just inserted' FROM t0_template
4596
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4597
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
4599
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4600
f_charbig = 'updated by trigger'
4601
WHERE f_int1 = new.f_int1;
4603
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4604
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4605
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4607
# check trigger-1 success: 1
4609
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4610
f_int2 = CAST(f_char1 AS SIGNED INT),
4611
f_charbig = 'just inserted'
4612
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4614
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4615
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4616
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4617
'just inserted' FROM t0_template
4618
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4619
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
4621
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4622
f_charbig = 'updated by trigger'
4623
WHERE f_int1 = new.f_int1;
4625
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4626
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4627
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4629
# check trigger-2 success: 1
4631
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4632
f_int2 = CAST(f_char1 AS SIGNED INT),
4633
f_charbig = 'just inserted'
4634
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4636
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4637
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4638
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4639
'just inserted' FROM t0_template
4640
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4641
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4643
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4644
f_charbig = 'updated by trigger'
4645
WHERE f_int1 = new.f_int1;
4647
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4648
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4650
# check trigger-3 success: 1
4652
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4653
f_int2 = CAST(f_char1 AS SIGNED INT),
4654
f_charbig = 'just inserted'
4655
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4657
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4658
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4659
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4660
'just inserted' FROM t0_template
4661
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4662
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4664
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4665
f_charbig = 'updated by trigger'
4666
WHERE f_int1 = - old.f_int1;
4668
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4669
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4671
# check trigger-4 success: 1
4673
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4674
f_int2 = CAST(f_char1 AS SIGNED INT),
4675
f_charbig = 'just inserted'
4676
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4678
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4679
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4680
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4681
'just inserted' FROM t0_template
4682
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4683
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4685
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4686
f_charbig = 'updated by trigger'
4687
WHERE f_int1 = new.f_int1;
4689
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4690
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4692
# check trigger-5 success: 1
4694
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4695
f_int2 = CAST(f_char1 AS SIGNED INT),
4696
f_charbig = 'just inserted'
4697
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4699
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4700
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4701
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4702
'just inserted' FROM t0_template
4703
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4704
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4706
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4707
f_charbig = 'updated by trigger'
4708
WHERE f_int1 = - old.f_int1;
4710
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4711
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4713
# check trigger-6 success: 1
4715
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4716
f_int2 = CAST(f_char1 AS SIGNED INT),
4717
f_charbig = 'just inserted'
4718
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4720
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4721
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4722
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4723
'just inserted' FROM t0_template
4724
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4725
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
4727
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4728
f_charbig = 'updated by trigger'
4729
WHERE f_int1 = - old.f_int1;
4732
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4734
# check trigger-7 success: 1
4736
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4737
f_int2 = CAST(f_char1 AS SIGNED INT),
4738
f_charbig = 'just inserted'
4739
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4741
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4742
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4743
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4744
'just inserted' FROM t0_template
4745
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4746
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
4748
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4749
f_charbig = 'updated by trigger'
4750
WHERE f_int1 = - old.f_int1;
4753
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4755
# check trigger-8 success: 1
4757
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4758
f_int2 = CAST(f_char1 AS SIGNED INT),
4759
f_charbig = 'just inserted'
4760
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4762
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4764
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4765
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4767
SET new.f_int1 = old.f_int1 + @max_row,
4768
new.f_int2 = old.f_int2 - @max_row,
4769
new.f_charbig = '####updated per update trigger####';
4772
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4773
f_charbig = '####updated per update statement itself####';
4775
# check trigger-9 success: 1
4777
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4778
f_int2 = CAST(f_char1 AS SIGNED INT),
4779
f_charbig = CONCAT('===',f_char1,'===');
4780
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4782
SET new.f_int1 = new.f_int1 + @max_row,
4783
new.f_int2 = new.f_int2 - @max_row,
4784
new.f_charbig = '####updated per update trigger####';
4787
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4788
f_charbig = '####updated per update statement itself####';
4790
# check trigger-10 success: 1
4792
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4793
f_int2 = CAST(f_char1 AS SIGNED INT),
4794
f_charbig = CONCAT('===',f_char1,'===');
4795
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4797
SET new.f_int1 = @my_max1 + @counter,
4798
new.f_int2 = @my_min2 - @counter,
4799
new.f_charbig = '####updated per insert trigger####';
4800
SET @counter = @counter + 1;
4803
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4804
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4805
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4806
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4807
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4811
# check trigger-11 success: 1
4813
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4814
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4815
AND f_charbig = '####updated per insert trigger####';
4816
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4818
SET new.f_int1 = @my_max1 + @counter,
4819
new.f_int2 = @my_min2 - @counter,
4820
new.f_charbig = '####updated per insert trigger####';
4821
SET @counter = @counter + 1;
4824
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4825
INSERT INTO t1 (f_char1, f_char2, f_charbig)
4826
SELECT CAST(f_int1 AS CHAR),
4827
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4828
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4832
# check trigger-12 success: 1
4834
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4835
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4836
AND f_charbig = '####updated per insert trigger####';
4838
Table Op Msg_type Msg_text
4839
test.t1 analyze status OK
4840
CHECK TABLE t1 EXTENDED;
4841
Table Op Msg_type Msg_text
4842
test.t1 check status OK
4843
CHECKSUM TABLE t1 EXTENDED;
4845
test.t1 <some_value>
4847
Table Op Msg_type Msg_text
4848
test.t1 optimize status OK
4849
# check layout success: 1
4850
REPAIR TABLE t1 EXTENDED;
4851
Table Op Msg_type Msg_text
4852
test.t1 repair status OK
4853
# check layout success: 1
4856
# check TRUNCATE success: 1
4857
# check layout success: 1
4858
# End usability test (inc/partition_check.inc)
4865
f_charbig VARCHAR(1000)
4867
PARTITION BY RANGE(f_int1)
4868
SUBPARTITION BY HASH(f_int1)
4869
( PARTITION part1 VALUES LESS THAN (1000)
4870
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
4871
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'));
4872
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4873
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
4874
# Start usability test (inc/partition_check.inc)
4876
SHOW CREATE TABLE t1;
4878
t1 CREATE TABLE `t1` (
4879
`f_int1` int(11) DEFAULT NULL,
4880
`f_int2` int(11) DEFAULT NULL,
4881
`f_char1` char(20) DEFAULT NULL,
4882
`f_char2` char(20) DEFAULT NULL,
4883
`f_charbig` varchar(1000) DEFAULT NULL
4884
) ENGINE=MyISAM DEFAULT CHARSET=latin1
4885
/*!50100 PARTITION BY RANGE (f_int1)
4886
SUBPARTITION BY HASH (f_int1)
4887
(PARTITION part1 VALUES LESS THAN (1000)
4888
(SUBPARTITION subpart11 ENGINE = MyISAM,
4889
SUBPARTITION subpart12 ENGINE = MyISAM)) */
4892
t1#P#part1#SP#subpart11.MYD
4893
t1#P#part1#SP#subpart11.MYI
4894
t1#P#part1#SP#subpart12.MYD
4895
t1#P#part1#SP#subpart12.MYI
4899
# check prerequisites-1 success: 1
4900
# check COUNT(*) success: 1
4901
# check MIN/MAX(f_int1) success: 1
4902
# check MIN/MAX(f_int2) success: 1
4903
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4904
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4905
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
4906
WHERE f_int1 IN (2,3);
4907
# check prerequisites-3 success: 1
4908
DELETE FROM t1 WHERE f_charbig = 'delete me';
4909
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
4910
# check read via f_int1 success: 1
4911
# check read via f_int2 success: 1
4913
# check multiple-1 success: 1
4914
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
4916
# check multiple-2 success: 1
4917
INSERT INTO t1 SELECT * FROM t0_template
4918
WHERE MOD(f_int1,3) = 0;
4920
# check multiple-3 success: 1
4921
UPDATE t1 SET f_int1 = f_int1 + @max_row
4922
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
4923
AND @max_row_div2 + @max_row_div4;
4925
# check multiple-4 success: 1
4927
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
4928
AND @max_row_div2 + @max_row_div4 + @max_row;
4930
# check multiple-5 success: 1
4931
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
4933
SET f_int1 = @cur_value , f_int2 = @cur_value,
4934
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4935
f_charbig = '#SINGLE#';
4937
# check single-1 success: 1
4938
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
4940
SET f_int1 = @cur_value , f_int2 = @cur_value,
4941
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4942
f_charbig = '#SINGLE#';
4944
# check single-2 success: 1
4945
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
4946
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
4947
UPDATE t1 SET f_int1 = @cur_value2
4948
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
4950
# check single-3 success: 1
4951
SET @cur_value1= -1;
4952
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
4953
UPDATE t1 SET f_int1 = @cur_value1
4954
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
4956
# check single-4 success: 1
4957
SELECT MAX(f_int1) INTO @cur_value FROM t1;
4958
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
4960
# check single-5 success: 1
4961
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
4963
# check single-6 success: 1
4964
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
4965
ERROR HY000: Table has no partition for value 2147483647
4966
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
4967
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
4968
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
4969
f_charbig = '#NULL#';
4971
SET f_int1 = NULL , f_int2 = -@max_row,
4972
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
4973
f_charbig = '#NULL#';
4974
# check null success: 1
4976
# check null-1 success: 1
4977
UPDATE t1 SET f_int1 = -@max_row
4978
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4979
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4981
# check null-2 success: 1
4982
UPDATE t1 SET f_int1 = NULL
4983
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4984
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4986
# check null-3 success: 1
4988
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4989
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4991
# check null-4 success: 1
4993
WHERE f_int1 = 0 AND f_int2 = 0
4994
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
4995
AND f_charbig = '#NULL#';
4997
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4998
SELECT f_int1, f_int1, '', '', 'was inserted'
4999
FROM t0_template source_tab
5000
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
5002
# check transactions-1 success: 1
5005
# check transactions-2 success: 1
5008
# check transactions-3 success: 1
5009
DELETE FROM t1 WHERE f_charbig = 'was inserted';
5013
# check transactions-4 success: 1
5014
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
5015
SELECT f_int1, f_int1, '', '', 'was inserted'
5016
FROM t0_template source_tab
5017
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
5019
# check transactions-5 success: 1
5022
Warning 1196 Some non-transactional changed tables couldn't be rolled back
5024
# check transactions-6 success: 1
5025
# INFO: Storage engine used for t1 seems to be not transactional.
5028
# check transactions-7 success: 1
5029
DELETE FROM t1 WHERE f_charbig = 'was inserted';
5031
SET @@session.sql_mode = 'traditional';
5032
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
5033
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
5034
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
5035
'', '', 'was inserted' FROM t0_template
5036
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
5037
ERROR 22012: Division by 0
5040
# check transactions-8 success: 1
5041
# INFO: Storage engine used for t1 seems to be unable to revert
5042
# changes made by the failing statement.
5043
SET @@session.sql_mode = '';
5045
DELETE FROM t1 WHERE f_charbig = 'was inserted';
5047
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
5049
# check special-1 success: 1
5050
UPDATE t1 SET f_charbig = '';
5052
# check special-2 success: 1
5053
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
5054
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
5055
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
5056
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5057
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5058
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5059
'just inserted' FROM t0_template
5060
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5061
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
5063
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5064
f_charbig = 'updated by trigger'
5065
WHERE f_int1 = new.f_int1;
5067
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5068
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
5069
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5071
# check trigger-1 success: 1
5073
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5074
f_int2 = CAST(f_char1 AS SIGNED INT),
5075
f_charbig = 'just inserted'
5076
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5078
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5079
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5080
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5081
'just inserted' FROM t0_template
5082
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5083
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
5085
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5086
f_charbig = 'updated by trigger'
5087
WHERE f_int1 = new.f_int1;
5089
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5090
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
5091
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5093
# check trigger-2 success: 1
5095
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5096
f_int2 = CAST(f_char1 AS SIGNED INT),
5097
f_charbig = 'just inserted'
5098
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5100
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5101
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5102
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5103
'just inserted' FROM t0_template
5104
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5105
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
5107
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5108
f_charbig = 'updated by trigger'
5109
WHERE f_int1 = new.f_int1;
5111
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5112
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5114
# check trigger-3 success: 1
5116
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5117
f_int2 = CAST(f_char1 AS SIGNED INT),
5118
f_charbig = 'just inserted'
5119
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5121
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5122
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5123
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5124
'just inserted' FROM t0_template
5125
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5126
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
5128
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5129
f_charbig = 'updated by trigger'
5130
WHERE f_int1 = - old.f_int1;
5132
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5133
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5135
# check trigger-4 success: 1
5137
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5138
f_int2 = CAST(f_char1 AS SIGNED INT),
5139
f_charbig = 'just inserted'
5140
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5142
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5143
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5144
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5145
'just inserted' FROM t0_template
5146
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5147
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
5149
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5150
f_charbig = 'updated by trigger'
5151
WHERE f_int1 = new.f_int1;
5153
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5154
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5156
# check trigger-5 success: 1
5158
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5159
f_int2 = CAST(f_char1 AS SIGNED INT),
5160
f_charbig = 'just inserted'
5161
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5163
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5164
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5165
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5166
'just inserted' FROM t0_template
5167
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5168
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
5170
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5171
f_charbig = 'updated by trigger'
5172
WHERE f_int1 = - old.f_int1;
5174
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5175
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5177
# check trigger-6 success: 1
5179
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5180
f_int2 = CAST(f_char1 AS SIGNED INT),
5181
f_charbig = 'just inserted'
5182
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5184
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5185
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5186
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5187
'just inserted' FROM t0_template
5188
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5189
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
5191
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5192
f_charbig = 'updated by trigger'
5193
WHERE f_int1 = - old.f_int1;
5196
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5198
# check trigger-7 success: 1
5200
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5201
f_int2 = CAST(f_char1 AS SIGNED INT),
5202
f_charbig = 'just inserted'
5203
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5205
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5206
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5207
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5208
'just inserted' FROM t0_template
5209
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5210
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
5212
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5213
f_charbig = 'updated by trigger'
5214
WHERE f_int1 = - old.f_int1;
5217
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5219
# check trigger-8 success: 1
5221
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5222
f_int2 = CAST(f_char1 AS SIGNED INT),
5223
f_charbig = 'just inserted'
5224
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5226
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5228
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5229
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
5231
SET new.f_int1 = old.f_int1 + @max_row,
5232
new.f_int2 = old.f_int2 - @max_row,
5233
new.f_charbig = '####updated per update trigger####';
5236
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
5237
f_charbig = '####updated per update statement itself####';
5239
# check trigger-9 success: 1
5241
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5242
f_int2 = CAST(f_char1 AS SIGNED INT),
5243
f_charbig = CONCAT('===',f_char1,'===');
5244
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
5246
SET new.f_int1 = new.f_int1 + @max_row,
5247
new.f_int2 = new.f_int2 - @max_row,
5248
new.f_charbig = '####updated per update trigger####';
5251
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
5252
f_charbig = '####updated per update statement itself####';
5254
# check trigger-10 success: 1
5256
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5257
f_int2 = CAST(f_char1 AS SIGNED INT),
5258
f_charbig = CONCAT('===',f_char1,'===');
5259
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
5261
SET new.f_int1 = @my_max1 + @counter,
5262
new.f_int2 = @my_min2 - @counter,
5263
new.f_charbig = '####updated per insert trigger####';
5264
SET @counter = @counter + 1;
5267
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
5268
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
5269
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
5270
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
5271
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
5275
# check trigger-11 success: 1
5277
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
5278
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
5279
AND f_charbig = '####updated per insert trigger####';
5280
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
5282
SET new.f_int1 = @my_max1 + @counter,
5283
new.f_int2 = @my_min2 - @counter,
5284
new.f_charbig = '####updated per insert trigger####';
5285
SET @counter = @counter + 1;
5288
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
5289
INSERT INTO t1 (f_char1, f_char2, f_charbig)
5290
SELECT CAST(f_int1 AS CHAR),
5291
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
5292
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
5296
# check trigger-12 success: 1
5298
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
5299
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
5300
AND f_charbig = '####updated per insert trigger####';
5302
Table Op Msg_type Msg_text
5303
test.t1 analyze status OK
5304
CHECK TABLE t1 EXTENDED;
5305
Table Op Msg_type Msg_text
5306
test.t1 check status OK
5307
CHECKSUM TABLE t1 EXTENDED;
5309
test.t1 <some_value>
5311
Table Op Msg_type Msg_text
5312
test.t1 optimize status OK
5313
# check layout success: 1
5314
REPAIR TABLE t1 EXTENDED;
5315
Table Op Msg_type Msg_text
5316
test.t1 repair status OK
5317
# check layout success: 1
5320
# check TRUNCATE success: 1
5321
# check layout success: 1
5322
# End usability test (inc/partition_check.inc)
5324
SET SESSION storage_engine='MyISAM';
5325
DROP VIEW IF EXISTS v1;
5326
DROP TABLE IF EXISTS t1;
5327
DROP TABLE IF EXISTS t0_aux;
5328
DROP TABLE IF EXISTS t0_definition;
5329
DROP TABLE IF EXISTS t0_template;