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 /*!50100 PARTITION BY HASH (f_int1) PARTITIONS 2 */
73
$MYSQLTEST_VARDIR/master-data/test/t1#P#p0.MYD
74
$MYSQLTEST_VARDIR/master-data/test/t1#P#p0.MYI
75
$MYSQLTEST_VARDIR/master-data/test/t1#P#p1.MYD
76
$MYSQLTEST_VARDIR/master-data/test/t1#P#p1.MYI
77
$MYSQLTEST_VARDIR/master-data/test/t1.frm
78
$MYSQLTEST_VARDIR/master-data/test/t1.par
80
# check prerequisites-1 success: 1
81
# check COUNT(*) success: 1
82
# check MIN/MAX(f_int1) success: 1
83
# check MIN/MAX(f_int2) success: 1
84
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
85
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
86
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
87
WHERE f_int1 IN (2,3);
88
# check prerequisites-3 success: 1
89
DELETE FROM t1 WHERE f_charbig = 'delete me';
90
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
91
# check read via f_int1 success: 1
92
# check read via f_int2 success: 1
94
# check multiple-1 success: 1
95
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
97
# check multiple-2 success: 1
98
INSERT INTO t1 SELECT * FROM t0_template
99
WHERE MOD(f_int1,3) = 0;
101
# check multiple-3 success: 1
102
UPDATE t1 SET f_int1 = f_int1 + @max_row
103
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
104
AND @max_row_div2 + @max_row_div4;
106
# check multiple-4 success: 1
108
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
109
AND @max_row_div2 + @max_row_div4 + @max_row;
111
# check multiple-5 success: 1
112
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
114
SET f_int1 = @cur_value , f_int2 = @cur_value,
115
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
116
f_charbig = '#SINGLE#';
118
# check single-1 success: 1
119
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
121
SET f_int1 = @cur_value , f_int2 = @cur_value,
122
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
123
f_charbig = '#SINGLE#';
125
# check single-2 success: 1
126
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
127
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
128
UPDATE t1 SET f_int1 = @cur_value2
129
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
131
# check single-3 success: 1
133
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
134
UPDATE t1 SET f_int1 = @cur_value1
135
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
137
# check single-4 success: 1
138
SELECT MAX(f_int1) INTO @cur_value FROM t1;
139
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
141
# check single-5 success: 1
142
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
144
# check single-6 success: 1
145
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
147
# check single-7 success: 1
148
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
149
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
150
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
151
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
152
f_charbig = '#NULL#';
154
SET f_int1 = NULL , f_int2 = -@max_row,
155
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
156
f_charbig = '#NULL#';
157
# check null success: 1
159
# check null-1 success: 1
160
UPDATE t1 SET f_int1 = -@max_row
161
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
162
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
164
# check null-2 success: 1
165
UPDATE t1 SET f_int1 = NULL
166
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
167
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
169
# check null-3 success: 1
171
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
172
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
174
# check null-4 success: 1
176
WHERE f_int1 = 0 AND f_int2 = 0
177
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
178
AND f_charbig = '#NULL#';
180
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
181
SELECT f_int1, f_int1, '', '', 'was inserted'
182
FROM t0_template source_tab
183
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
185
# check transactions-1 success: 1
188
# check transactions-2 success: 1
191
# check transactions-3 success: 1
192
DELETE FROM t1 WHERE f_charbig = 'was inserted';
196
# check transactions-4 success: 1
197
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
198
SELECT f_int1, f_int1, '', '', 'was inserted'
199
FROM t0_template source_tab
200
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
202
# check transactions-5 success: 1
205
Warning 1196 Some non-transactional changed tables couldn't be rolled back
207
# check transactions-6 success: 1
208
# INFO: Storage engine used for t1 seems to be not transactional.
211
# check transactions-7 success: 1
212
DELETE FROM t1 WHERE f_charbig = 'was inserted';
214
SET @@session.sql_mode = 'traditional';
215
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
216
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
217
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
218
'', '', 'was inserted' FROM t0_template
219
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
220
ERROR 22012: Division by 0
223
# check transactions-8 success: 1
224
# INFO: Storage engine used for t1 seems to be unable to revert
225
# changes made by the failing statement.
226
SET @@session.sql_mode = '';
228
DELETE FROM t1 WHERE f_charbig = 'was inserted';
230
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
232
# check special-1 success: 1
233
UPDATE t1 SET f_charbig = '';
235
# check special-2 success: 1
236
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
237
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
238
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
239
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
240
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
241
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
242
'just inserted' FROM t0_template
243
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
244
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
246
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
247
f_charbig = 'updated by trigger'
248
WHERE f_int1 = new.f_int1;
250
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
251
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
252
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
254
# check trigger-1 success: 1
256
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
257
f_int2 = CAST(f_char1 AS SIGNED INT),
258
f_charbig = 'just inserted'
259
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
261
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
262
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
263
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
264
'just inserted' FROM t0_template
265
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
266
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
268
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
269
f_charbig = 'updated by trigger'
270
WHERE f_int1 = new.f_int1;
272
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
273
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
274
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
276
# check trigger-2 success: 1
278
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
279
f_int2 = CAST(f_char1 AS SIGNED INT),
280
f_charbig = 'just inserted'
281
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
283
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
284
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
285
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
286
'just inserted' FROM t0_template
287
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
288
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
290
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
291
f_charbig = 'updated by trigger'
292
WHERE f_int1 = new.f_int1;
294
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
295
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
297
# check trigger-3 success: 1
299
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
300
f_int2 = CAST(f_char1 AS SIGNED INT),
301
f_charbig = 'just inserted'
302
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
304
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
305
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
306
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
307
'just inserted' FROM t0_template
308
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
309
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
311
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
312
f_charbig = 'updated by trigger'
313
WHERE f_int1 = - old.f_int1;
315
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
316
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
318
# check trigger-4 success: 1
320
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
321
f_int2 = CAST(f_char1 AS SIGNED INT),
322
f_charbig = 'just inserted'
323
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
325
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
326
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
327
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
328
'just inserted' FROM t0_template
329
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
330
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
332
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
333
f_charbig = 'updated by trigger'
334
WHERE f_int1 = new.f_int1;
336
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
337
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
339
# check trigger-5 success: 1
341
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
342
f_int2 = CAST(f_char1 AS SIGNED INT),
343
f_charbig = 'just inserted'
344
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
346
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
347
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
348
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
349
'just inserted' FROM t0_template
350
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
351
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
353
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
354
f_charbig = 'updated by trigger'
355
WHERE f_int1 = - old.f_int1;
357
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
358
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
360
# check trigger-6 success: 1
362
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
363
f_int2 = CAST(f_char1 AS SIGNED INT),
364
f_charbig = 'just inserted'
365
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
367
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
368
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
369
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
370
'just inserted' FROM t0_template
371
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
372
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
374
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
375
f_charbig = 'updated by trigger'
376
WHERE f_int1 = - old.f_int1;
379
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
381
# check trigger-7 success: 1
383
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
384
f_int2 = CAST(f_char1 AS SIGNED INT),
385
f_charbig = 'just inserted'
386
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
388
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
389
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
390
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
391
'just inserted' FROM t0_template
392
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
393
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
395
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
396
f_charbig = 'updated by trigger'
397
WHERE f_int1 = - old.f_int1;
400
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
402
# check trigger-8 success: 1
404
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
405
f_int2 = CAST(f_char1 AS SIGNED INT),
406
f_charbig = 'just inserted'
407
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
409
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
411
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
412
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
414
SET new.f_int1 = old.f_int1 + @max_row,
415
new.f_int2 = old.f_int2 - @max_row,
416
new.f_charbig = '####updated per update trigger####';
419
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
420
f_charbig = '####updated per update statement itself####';
422
# check trigger-9 success: 1
424
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
425
f_int2 = CAST(f_char1 AS SIGNED INT),
426
f_charbig = CONCAT('===',f_char1,'===');
427
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
429
SET new.f_int1 = new.f_int1 + @max_row,
430
new.f_int2 = new.f_int2 - @max_row,
431
new.f_charbig = '####updated per update trigger####';
434
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
435
f_charbig = '####updated per update statement itself####';
437
# check trigger-10 success: 1
439
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
440
f_int2 = CAST(f_char1 AS SIGNED INT),
441
f_charbig = CONCAT('===',f_char1,'===');
442
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
444
SET new.f_int1 = @my_max1 + @counter,
445
new.f_int2 = @my_min2 - @counter,
446
new.f_charbig = '####updated per insert trigger####';
447
SET @counter = @counter + 1;
450
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
451
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
452
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
453
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
454
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
458
# check trigger-11 success: 1
460
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
461
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
462
AND f_charbig = '####updated per insert trigger####';
463
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
465
SET new.f_int1 = @my_max1 + @counter,
466
new.f_int2 = @my_min2 - @counter,
467
new.f_charbig = '####updated per insert trigger####';
468
SET @counter = @counter + 1;
471
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
472
INSERT INTO t1 (f_char1, f_char2, f_charbig)
473
SELECT CAST(f_int1 AS CHAR),
474
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
475
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
479
# check trigger-12 success: 1
481
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
482
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
483
AND f_charbig = '####updated per insert trigger####';
485
Table Op Msg_type Msg_text
486
test.t1 analyze note The storage engine for the table doesn't support analyze
487
CHECK TABLE t1 EXTENDED;
488
Table Op Msg_type Msg_text
489
test.t1 check note The storage engine for the table doesn't support check
490
CHECKSUM TABLE t1 EXTENDED;
494
Table Op Msg_type Msg_text
495
test.t1 optimize note The storage engine for the table doesn't support optimize
496
# check layout success: 1
497
REPAIR TABLE t1 EXTENDED;
498
Table Op Msg_type Msg_text
499
test.t1 repair note The storage engine for the table doesn't support repair
500
# check layout success: 1
503
# check TRUNCATE success: 1
504
# check layout success: 1
505
# End usability test (inc/partition_check.inc)
507
#------------------------------------------------------------------------
508
# 2 Assignment of storage engine just after partition or subpartition
510
#------------------------------------------------------------------------
516
f_charbig VARCHAR(1000)
518
PARTITION BY HASH(f_int1)
519
( PARTITION part1 STORAGE ENGINE = 'MyISAM',
520
PARTITION part2 STORAGE ENGINE = 'MyISAM'
522
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
523
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
524
# Start usability test (inc/partition_check.inc)
526
SHOW CREATE TABLE t1;
528
t1 CREATE TABLE `t1` (
529
`f_int1` int(11) DEFAULT NULL,
530
`f_int2` int(11) DEFAULT NULL,
531
`f_char1` char(20) DEFAULT NULL,
532
`f_char2` char(20) DEFAULT NULL,
533
`f_charbig` varchar(1000) DEFAULT NULL
534
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */
537
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYD
538
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYI
539
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2.MYD
540
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2.MYI
541
$MYSQLTEST_VARDIR/master-data/test/t1.frm
542
$MYSQLTEST_VARDIR/master-data/test/t1.par
544
# check prerequisites-1 success: 1
545
# check COUNT(*) success: 1
546
# check MIN/MAX(f_int1) success: 1
547
# check MIN/MAX(f_int2) success: 1
548
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
549
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
550
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
551
WHERE f_int1 IN (2,3);
552
# check prerequisites-3 success: 1
553
DELETE FROM t1 WHERE f_charbig = 'delete me';
554
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
555
# check read via f_int1 success: 1
556
# check read via f_int2 success: 1
558
# check multiple-1 success: 1
559
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
561
# check multiple-2 success: 1
562
INSERT INTO t1 SELECT * FROM t0_template
563
WHERE MOD(f_int1,3) = 0;
565
# check multiple-3 success: 1
566
UPDATE t1 SET f_int1 = f_int1 + @max_row
567
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
568
AND @max_row_div2 + @max_row_div4;
570
# check multiple-4 success: 1
572
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
573
AND @max_row_div2 + @max_row_div4 + @max_row;
575
# check multiple-5 success: 1
576
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
578
SET f_int1 = @cur_value , f_int2 = @cur_value,
579
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
580
f_charbig = '#SINGLE#';
582
# check single-1 success: 1
583
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
585
SET f_int1 = @cur_value , f_int2 = @cur_value,
586
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
587
f_charbig = '#SINGLE#';
589
# check single-2 success: 1
590
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
591
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
592
UPDATE t1 SET f_int1 = @cur_value2
593
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
595
# check single-3 success: 1
597
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
598
UPDATE t1 SET f_int1 = @cur_value1
599
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
601
# check single-4 success: 1
602
SELECT MAX(f_int1) INTO @cur_value FROM t1;
603
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
605
# check single-5 success: 1
606
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
608
# check single-6 success: 1
609
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
611
# check single-7 success: 1
612
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
613
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
614
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
615
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
616
f_charbig = '#NULL#';
618
SET f_int1 = NULL , f_int2 = -@max_row,
619
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
620
f_charbig = '#NULL#';
621
# check null success: 1
623
# check null-1 success: 1
624
UPDATE t1 SET f_int1 = -@max_row
625
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
626
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
628
# check null-2 success: 1
629
UPDATE t1 SET f_int1 = NULL
630
WHERE f_int1 = -@max_row 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-3 success: 1
635
WHERE f_int1 IS NULL 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-4 success: 1
640
WHERE f_int1 = 0 AND f_int2 = 0
641
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
642
AND f_charbig = '#NULL#';
644
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
645
SELECT f_int1, f_int1, '', '', 'was inserted'
646
FROM t0_template source_tab
647
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
649
# check transactions-1 success: 1
652
# check transactions-2 success: 1
655
# check transactions-3 success: 1
656
DELETE FROM t1 WHERE f_charbig = 'was inserted';
660
# check transactions-4 success: 1
661
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
662
SELECT f_int1, f_int1, '', '', 'was inserted'
663
FROM t0_template source_tab
664
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
666
# check transactions-5 success: 1
669
Warning 1196 Some non-transactional changed tables couldn't be rolled back
671
# check transactions-6 success: 1
672
# INFO: Storage engine used for t1 seems to be not transactional.
675
# check transactions-7 success: 1
676
DELETE FROM t1 WHERE f_charbig = 'was inserted';
678
SET @@session.sql_mode = 'traditional';
679
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
680
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
681
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
682
'', '', 'was inserted' FROM t0_template
683
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
684
ERROR 22012: Division by 0
687
# check transactions-8 success: 1
688
# INFO: Storage engine used for t1 seems to be unable to revert
689
# changes made by the failing statement.
690
SET @@session.sql_mode = '';
692
DELETE FROM t1 WHERE f_charbig = 'was inserted';
694
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
696
# check special-1 success: 1
697
UPDATE t1 SET f_charbig = '';
699
# check special-2 success: 1
700
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
701
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
702
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
703
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
704
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
705
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
706
'just inserted' FROM t0_template
707
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
708
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
710
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
711
f_charbig = 'updated by trigger'
712
WHERE f_int1 = new.f_int1;
714
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
715
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
716
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
718
# check trigger-1 success: 1
720
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
721
f_int2 = CAST(f_char1 AS SIGNED INT),
722
f_charbig = 'just inserted'
723
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
725
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
726
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
727
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
728
'just inserted' FROM t0_template
729
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
730
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
732
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
733
f_charbig = 'updated by trigger'
734
WHERE f_int1 = new.f_int1;
736
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
737
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
738
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
740
# check trigger-2 success: 1
742
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
743
f_int2 = CAST(f_char1 AS SIGNED INT),
744
f_charbig = 'just inserted'
745
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
747
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
748
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
749
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
750
'just inserted' FROM t0_template
751
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
752
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
754
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
755
f_charbig = 'updated by trigger'
756
WHERE f_int1 = new.f_int1;
758
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
759
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
761
# check trigger-3 success: 1
763
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
764
f_int2 = CAST(f_char1 AS SIGNED INT),
765
f_charbig = 'just inserted'
766
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
768
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
769
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
770
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
771
'just inserted' FROM t0_template
772
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
773
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
775
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
776
f_charbig = 'updated by trigger'
777
WHERE f_int1 = - old.f_int1;
779
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
780
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
782
# check trigger-4 success: 1
784
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
785
f_int2 = CAST(f_char1 AS SIGNED INT),
786
f_charbig = 'just inserted'
787
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
789
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
790
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
791
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
792
'just inserted' FROM t0_template
793
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
794
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
796
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
797
f_charbig = 'updated by trigger'
798
WHERE f_int1 = new.f_int1;
800
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
801
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
803
# check trigger-5 success: 1
805
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
806
f_int2 = CAST(f_char1 AS SIGNED INT),
807
f_charbig = 'just inserted'
808
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
810
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
811
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
812
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
813
'just inserted' FROM t0_template
814
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
815
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
817
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
818
f_charbig = 'updated by trigger'
819
WHERE f_int1 = - old.f_int1;
821
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
822
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
824
# check trigger-6 success: 1
826
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
827
f_int2 = CAST(f_char1 AS SIGNED INT),
828
f_charbig = 'just inserted'
829
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
831
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
832
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
833
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
834
'just inserted' FROM t0_template
835
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
836
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
838
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
839
f_charbig = 'updated by trigger'
840
WHERE f_int1 = - old.f_int1;
843
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
845
# check trigger-7 success: 1
847
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
848
f_int2 = CAST(f_char1 AS SIGNED INT),
849
f_charbig = 'just inserted'
850
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
852
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
853
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
854
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
855
'just inserted' FROM t0_template
856
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
857
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
859
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
860
f_charbig = 'updated by trigger'
861
WHERE f_int1 = - old.f_int1;
864
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
866
# check trigger-8 success: 1
868
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
869
f_int2 = CAST(f_char1 AS SIGNED INT),
870
f_charbig = 'just inserted'
871
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
873
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
875
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
876
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
878
SET new.f_int1 = old.f_int1 + @max_row,
879
new.f_int2 = old.f_int2 - @max_row,
880
new.f_charbig = '####updated per update trigger####';
883
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
884
f_charbig = '####updated per update statement itself####';
886
# check trigger-9 success: 1
888
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
889
f_int2 = CAST(f_char1 AS SIGNED INT),
890
f_charbig = CONCAT('===',f_char1,'===');
891
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
893
SET new.f_int1 = new.f_int1 + @max_row,
894
new.f_int2 = new.f_int2 - @max_row,
895
new.f_charbig = '####updated per update trigger####';
898
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
899
f_charbig = '####updated per update statement itself####';
901
# check trigger-10 success: 1
903
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
904
f_int2 = CAST(f_char1 AS SIGNED INT),
905
f_charbig = CONCAT('===',f_char1,'===');
906
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
908
SET new.f_int1 = @my_max1 + @counter,
909
new.f_int2 = @my_min2 - @counter,
910
new.f_charbig = '####updated per insert trigger####';
911
SET @counter = @counter + 1;
914
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
915
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
916
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
917
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
918
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
922
# check trigger-11 success: 1
924
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
925
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
926
AND f_charbig = '####updated per insert trigger####';
927
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
929
SET new.f_int1 = @my_max1 + @counter,
930
new.f_int2 = @my_min2 - @counter,
931
new.f_charbig = '####updated per insert trigger####';
932
SET @counter = @counter + 1;
935
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
936
INSERT INTO t1 (f_char1, f_char2, f_charbig)
937
SELECT CAST(f_int1 AS CHAR),
938
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
939
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
943
# check trigger-12 success: 1
945
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
946
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
947
AND f_charbig = '####updated per insert trigger####';
949
Table Op Msg_type Msg_text
950
test.t1 analyze note The storage engine for the table doesn't support analyze
951
CHECK TABLE t1 EXTENDED;
952
Table Op Msg_type Msg_text
953
test.t1 check note The storage engine for the table doesn't support check
954
CHECKSUM TABLE t1 EXTENDED;
958
Table Op Msg_type Msg_text
959
test.t1 optimize note The storage engine for the table doesn't support optimize
960
# check layout success: 1
961
REPAIR TABLE t1 EXTENDED;
962
Table Op Msg_type Msg_text
963
test.t1 repair note The storage engine for the table doesn't support repair
964
# check layout success: 1
967
# check TRUNCATE success: 1
968
# check layout success: 1
969
# End usability test (inc/partition_check.inc)
976
f_charbig VARCHAR(1000)
978
PARTITION BY RANGE(f_int1)
979
SUBPARTITION BY HASH(f_int1)
980
( PARTITION part1 VALUES LESS THAN (10)
981
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
982
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
983
PARTITION part2 VALUES LESS THAN (2147483646)
984
(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
985
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
987
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
988
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
989
# Start usability test (inc/partition_check.inc)
991
SHOW CREATE TABLE t1;
993
t1 CREATE TABLE `t1` (
994
`f_int1` int(11) DEFAULT NULL,
995
`f_int2` int(11) DEFAULT NULL,
996
`f_char1` char(20) DEFAULT NULL,
997
`f_char2` char(20) DEFAULT NULL,
998
`f_charbig` varchar(1000) DEFAULT NULL
999
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
1002
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYD
1003
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYI
1004
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYD
1005
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYI
1006
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYD
1007
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYI
1008
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYD
1009
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYI
1010
$MYSQLTEST_VARDIR/master-data/test/t1.frm
1011
$MYSQLTEST_VARDIR/master-data/test/t1.par
1013
# check prerequisites-1 success: 1
1014
# check COUNT(*) success: 1
1015
# check MIN/MAX(f_int1) success: 1
1016
# check MIN/MAX(f_int2) success: 1
1017
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1018
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1019
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
1020
WHERE f_int1 IN (2,3);
1021
# check prerequisites-3 success: 1
1022
DELETE FROM t1 WHERE f_charbig = 'delete me';
1023
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
1024
# check read via f_int1 success: 1
1025
# check read via f_int2 success: 1
1027
# check multiple-1 success: 1
1028
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
1030
# check multiple-2 success: 1
1031
INSERT INTO t1 SELECT * FROM t0_template
1032
WHERE MOD(f_int1,3) = 0;
1034
# check multiple-3 success: 1
1035
UPDATE t1 SET f_int1 = f_int1 + @max_row
1036
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
1037
AND @max_row_div2 + @max_row_div4;
1039
# check multiple-4 success: 1
1041
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
1042
AND @max_row_div2 + @max_row_div4 + @max_row;
1044
# check multiple-5 success: 1
1045
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
1047
SET f_int1 = @cur_value , f_int2 = @cur_value,
1048
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1049
f_charbig = '#SINGLE#';
1051
# check single-1 success: 1
1052
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
1054
SET f_int1 = @cur_value , f_int2 = @cur_value,
1055
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1056
f_charbig = '#SINGLE#';
1058
# check single-2 success: 1
1059
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
1060
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
1061
UPDATE t1 SET f_int1 = @cur_value2
1062
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
1064
# check single-3 success: 1
1065
SET @cur_value1= -1;
1066
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
1067
UPDATE t1 SET f_int1 = @cur_value1
1068
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
1070
# check single-4 success: 1
1071
SELECT MAX(f_int1) INTO @cur_value FROM t1;
1072
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
1074
# check single-5 success: 1
1075
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
1077
# check single-6 success: 1
1078
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
1079
ERROR HY000: Table has no partition for value 2147483647
1080
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
1081
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
1082
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
1083
f_charbig = '#NULL#';
1085
SET f_int1 = NULL , f_int2 = -@max_row,
1086
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
1087
f_charbig = '#NULL#';
1088
# check null success: 1
1090
# check null-1 success: 1
1091
UPDATE t1 SET f_int1 = -@max_row
1092
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1093
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1095
# check null-2 success: 1
1096
UPDATE t1 SET f_int1 = NULL
1097
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1098
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1100
# check null-3 success: 1
1102
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1103
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1105
# check null-4 success: 1
1107
WHERE f_int1 = 0 AND f_int2 = 0
1108
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
1109
AND f_charbig = '#NULL#';
1111
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1112
SELECT f_int1, f_int1, '', '', 'was inserted'
1113
FROM t0_template source_tab
1114
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1116
# check transactions-1 success: 1
1119
# check transactions-2 success: 1
1122
# check transactions-3 success: 1
1123
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1127
# check transactions-4 success: 1
1128
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1129
SELECT f_int1, f_int1, '', '', 'was inserted'
1130
FROM t0_template source_tab
1131
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1133
# check transactions-5 success: 1
1136
Warning 1196 Some non-transactional changed tables couldn't be rolled back
1138
# check transactions-6 success: 1
1139
# INFO: Storage engine used for t1 seems to be not transactional.
1142
# check transactions-7 success: 1
1143
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1145
SET @@session.sql_mode = 'traditional';
1146
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
1147
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1148
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
1149
'', '', 'was inserted' FROM t0_template
1150
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1151
ERROR 22012: Division by 0
1154
# check transactions-8 success: 1
1155
# INFO: Storage engine used for t1 seems to be unable to revert
1156
# changes made by the failing statement.
1157
SET @@session.sql_mode = '';
1159
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1161
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
1163
# check special-1 success: 1
1164
UPDATE t1 SET f_charbig = '';
1166
# check special-2 success: 1
1167
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
1168
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1169
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
1170
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1171
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1172
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1173
'just inserted' FROM t0_template
1174
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1175
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
1177
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1178
f_charbig = 'updated by trigger'
1179
WHERE f_int1 = new.f_int1;
1181
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1182
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1183
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1185
# check trigger-1 success: 1
1187
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1188
f_int2 = CAST(f_char1 AS SIGNED INT),
1189
f_charbig = 'just inserted'
1190
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1192
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1193
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1194
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1195
'just inserted' FROM t0_template
1196
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1197
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
1199
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1200
f_charbig = 'updated by trigger'
1201
WHERE f_int1 = new.f_int1;
1203
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1204
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1205
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1207
# check trigger-2 success: 1
1209
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1210
f_int2 = CAST(f_char1 AS SIGNED INT),
1211
f_charbig = 'just inserted'
1212
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1214
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1215
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1216
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1217
'just inserted' FROM t0_template
1218
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1219
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1221
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1222
f_charbig = 'updated by trigger'
1223
WHERE f_int1 = new.f_int1;
1225
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1226
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1228
# check trigger-3 success: 1
1230
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1231
f_int2 = CAST(f_char1 AS SIGNED INT),
1232
f_charbig = 'just inserted'
1233
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1235
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1236
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1237
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1238
'just inserted' FROM t0_template
1239
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1240
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1242
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1243
f_charbig = 'updated by trigger'
1244
WHERE f_int1 = - old.f_int1;
1246
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1247
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1249
# check trigger-4 success: 1
1251
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1252
f_int2 = CAST(f_char1 AS SIGNED INT),
1253
f_charbig = 'just inserted'
1254
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1256
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1257
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1258
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1259
'just inserted' FROM t0_template
1260
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1261
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1263
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1264
f_charbig = 'updated by trigger'
1265
WHERE f_int1 = new.f_int1;
1267
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1268
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1270
# check trigger-5 success: 1
1272
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1273
f_int2 = CAST(f_char1 AS SIGNED INT),
1274
f_charbig = 'just inserted'
1275
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1277
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1278
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1279
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1280
'just inserted' FROM t0_template
1281
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1282
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
1284
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1285
f_charbig = 'updated by trigger'
1286
WHERE f_int1 = - old.f_int1;
1288
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1289
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1291
# check trigger-6 success: 1
1293
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1294
f_int2 = CAST(f_char1 AS SIGNED INT),
1295
f_charbig = 'just inserted'
1296
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1298
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1299
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1300
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1301
'just inserted' FROM t0_template
1302
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1303
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
1305
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1306
f_charbig = 'updated by trigger'
1307
WHERE f_int1 = - old.f_int1;
1310
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1312
# check trigger-7 success: 1
1314
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1315
f_int2 = CAST(f_char1 AS SIGNED INT),
1316
f_charbig = 'just inserted'
1317
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1319
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1320
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1321
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1322
'just inserted' FROM t0_template
1323
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1324
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
1326
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1327
f_charbig = 'updated by trigger'
1328
WHERE f_int1 = - old.f_int1;
1331
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1333
# check trigger-8 success: 1
1335
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1336
f_int2 = CAST(f_char1 AS SIGNED INT),
1337
f_charbig = 'just inserted'
1338
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1340
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1342
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1343
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1345
SET new.f_int1 = old.f_int1 + @max_row,
1346
new.f_int2 = old.f_int2 - @max_row,
1347
new.f_charbig = '####updated per update trigger####';
1350
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1351
f_charbig = '####updated per update statement itself####';
1353
# check trigger-9 success: 1
1355
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1356
f_int2 = CAST(f_char1 AS SIGNED INT),
1357
f_charbig = CONCAT('===',f_char1,'===');
1358
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1360
SET new.f_int1 = new.f_int1 + @max_row,
1361
new.f_int2 = new.f_int2 - @max_row,
1362
new.f_charbig = '####updated per update trigger####';
1365
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1366
f_charbig = '####updated per update statement itself####';
1368
# check trigger-10 success: 1
1370
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1371
f_int2 = CAST(f_char1 AS SIGNED INT),
1372
f_charbig = CONCAT('===',f_char1,'===');
1373
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1375
SET new.f_int1 = @my_max1 + @counter,
1376
new.f_int2 = @my_min2 - @counter,
1377
new.f_charbig = '####updated per insert trigger####';
1378
SET @counter = @counter + 1;
1381
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1382
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1383
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1384
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1385
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1389
# check trigger-11 success: 1
1391
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1392
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1393
AND f_charbig = '####updated per insert trigger####';
1394
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1396
SET new.f_int1 = @my_max1 + @counter,
1397
new.f_int2 = @my_min2 - @counter,
1398
new.f_charbig = '####updated per insert trigger####';
1399
SET @counter = @counter + 1;
1402
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1403
INSERT INTO t1 (f_char1, f_char2, f_charbig)
1404
SELECT CAST(f_int1 AS CHAR),
1405
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1406
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1410
# check trigger-12 success: 1
1412
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1413
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1414
AND f_charbig = '####updated per insert trigger####';
1416
Table Op Msg_type Msg_text
1417
test.t1 analyze note The storage engine for the table doesn't support analyze
1418
CHECK TABLE t1 EXTENDED;
1419
Table Op Msg_type Msg_text
1420
test.t1 check note The storage engine for the table doesn't support check
1421
CHECKSUM TABLE t1 EXTENDED;
1423
test.t1 <some_value>
1425
Table Op Msg_type Msg_text
1426
test.t1 optimize note The storage engine for the table doesn't support optimize
1427
# check layout success: 1
1428
REPAIR TABLE t1 EXTENDED;
1429
Table Op Msg_type Msg_text
1430
test.t1 repair note The storage engine for the table doesn't support repair
1431
# check layout success: 1
1434
# check TRUNCATE success: 1
1435
# check layout success: 1
1436
# End usability test (inc/partition_check.inc)
1438
#------------------------------------------------------------------------
1439
# 3 Some but not all named partitions or subpartitions get a storage
1441
#------------------------------------------------------------------------
1447
f_charbig VARCHAR(1000)
1449
PARTITION BY HASH(f_int1)
1450
( PARTITION part1 STORAGE ENGINE = 'MyISAM',
1453
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1459
f_charbig VARCHAR(1000)
1461
PARTITION BY HASH(f_int1)
1463
PARTITION part2 STORAGE ENGINE = 'MyISAM'
1465
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1471
f_charbig VARCHAR(1000)
1473
PARTITION BY RANGE(f_int1)
1474
SUBPARTITION BY HASH(f_int1)
1475
( PARTITION part1 VALUES LESS THAN (10)
1476
(SUBPARTITION subpart11,
1477
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
1478
PARTITION part2 VALUES LESS THAN (2147483646)
1479
(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
1480
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
1482
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1488
f_charbig VARCHAR(1000)
1490
PARTITION BY RANGE(f_int1)
1491
SUBPARTITION BY HASH(f_int1)
1492
( PARTITION part1 VALUES LESS THAN (10)
1493
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
1494
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
1495
PARTITION part2 VALUES LESS THAN (2147483646)
1496
(SUBPARTITION subpart21,
1497
SUBPARTITION subpart22 )
1499
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1505
f_charbig VARCHAR(1000)
1508
PARTITION BY RANGE(f_int1)
1509
SUBPARTITION BY HASH(f_int1)
1510
( PARTITION part1 VALUES LESS THAN (10)
1511
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
1512
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
1513
PARTITION part2 VALUES LESS THAN (2147483646)
1514
(SUBPARTITION subpart21,
1515
SUBPARTITION subpart22 )
1517
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1518
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
1519
# Start usability test (inc/partition_check.inc)
1521
SHOW CREATE TABLE t1;
1523
t1 CREATE TABLE `t1` (
1524
`f_int1` int(11) DEFAULT NULL,
1525
`f_int2` int(11) DEFAULT NULL,
1526
`f_char1` char(20) DEFAULT NULL,
1527
`f_char2` char(20) DEFAULT NULL,
1528
`f_charbig` varchar(1000) DEFAULT NULL
1529
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
1532
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYD
1533
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYI
1534
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYD
1535
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYI
1536
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYD
1537
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYI
1538
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYD
1539
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYI
1540
$MYSQLTEST_VARDIR/master-data/test/t1.frm
1541
$MYSQLTEST_VARDIR/master-data/test/t1.par
1543
# check prerequisites-1 success: 1
1544
# check COUNT(*) success: 1
1545
# check MIN/MAX(f_int1) success: 1
1546
# check MIN/MAX(f_int2) success: 1
1547
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1548
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1549
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
1550
WHERE f_int1 IN (2,3);
1551
# check prerequisites-3 success: 1
1552
DELETE FROM t1 WHERE f_charbig = 'delete me';
1553
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
1554
# check read via f_int1 success: 1
1555
# check read via f_int2 success: 1
1557
# check multiple-1 success: 1
1558
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
1560
# check multiple-2 success: 1
1561
INSERT INTO t1 SELECT * FROM t0_template
1562
WHERE MOD(f_int1,3) = 0;
1564
# check multiple-3 success: 1
1565
UPDATE t1 SET f_int1 = f_int1 + @max_row
1566
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
1567
AND @max_row_div2 + @max_row_div4;
1569
# check multiple-4 success: 1
1571
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
1572
AND @max_row_div2 + @max_row_div4 + @max_row;
1574
# check multiple-5 success: 1
1575
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
1577
SET f_int1 = @cur_value , f_int2 = @cur_value,
1578
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1579
f_charbig = '#SINGLE#';
1581
# check single-1 success: 1
1582
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
1584
SET f_int1 = @cur_value , f_int2 = @cur_value,
1585
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
1586
f_charbig = '#SINGLE#';
1588
# check single-2 success: 1
1589
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
1590
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
1591
UPDATE t1 SET f_int1 = @cur_value2
1592
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
1594
# check single-3 success: 1
1595
SET @cur_value1= -1;
1596
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
1597
UPDATE t1 SET f_int1 = @cur_value1
1598
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
1600
# check single-4 success: 1
1601
SELECT MAX(f_int1) INTO @cur_value FROM t1;
1602
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
1604
# check single-5 success: 1
1605
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
1607
# check single-6 success: 1
1608
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
1609
ERROR HY000: Table has no partition for value 2147483647
1610
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
1611
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
1612
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
1613
f_charbig = '#NULL#';
1615
SET f_int1 = NULL , f_int2 = -@max_row,
1616
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
1617
f_charbig = '#NULL#';
1618
# check null success: 1
1620
# check null-1 success: 1
1621
UPDATE t1 SET f_int1 = -@max_row
1622
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1623
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1625
# check null-2 success: 1
1626
UPDATE t1 SET f_int1 = NULL
1627
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1628
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1630
# check null-3 success: 1
1632
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
1633
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
1635
# check null-4 success: 1
1637
WHERE f_int1 = 0 AND f_int2 = 0
1638
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
1639
AND f_charbig = '#NULL#';
1641
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1642
SELECT f_int1, f_int1, '', '', 'was inserted'
1643
FROM t0_template source_tab
1644
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1646
# check transactions-1 success: 1
1649
# check transactions-2 success: 1
1652
# check transactions-3 success: 1
1653
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1657
# check transactions-4 success: 1
1658
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1659
SELECT f_int1, f_int1, '', '', 'was inserted'
1660
FROM t0_template source_tab
1661
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1663
# check transactions-5 success: 1
1666
Warning 1196 Some non-transactional changed tables couldn't be rolled back
1668
# check transactions-6 success: 1
1669
# INFO: Storage engine used for t1 seems to be not transactional.
1672
# check transactions-7 success: 1
1673
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1675
SET @@session.sql_mode = 'traditional';
1676
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
1677
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1678
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
1679
'', '', 'was inserted' FROM t0_template
1680
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
1681
ERROR 22012: Division by 0
1684
# check transactions-8 success: 1
1685
# INFO: Storage engine used for t1 seems to be unable to revert
1686
# changes made by the failing statement.
1687
SET @@session.sql_mode = '';
1689
DELETE FROM t1 WHERE f_charbig = 'was inserted';
1691
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
1693
# check special-1 success: 1
1694
UPDATE t1 SET f_charbig = '';
1696
# check special-2 success: 1
1697
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
1698
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1699
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
1700
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1701
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1702
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1703
'just inserted' FROM t0_template
1704
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1705
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
1707
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1708
f_charbig = 'updated by trigger'
1709
WHERE f_int1 = new.f_int1;
1711
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1712
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1713
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1715
# check trigger-1 success: 1
1717
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1718
f_int2 = CAST(f_char1 AS SIGNED INT),
1719
f_charbig = 'just inserted'
1720
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1722
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1723
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1724
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1725
'just inserted' FROM t0_template
1726
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1727
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
1729
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1730
f_charbig = 'updated by trigger'
1731
WHERE f_int1 = new.f_int1;
1733
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1734
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1735
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1737
# check trigger-2 success: 1
1739
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1740
f_int2 = CAST(f_char1 AS SIGNED INT),
1741
f_charbig = 'just inserted'
1742
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
1744
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1745
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
1746
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
1747
'just inserted' FROM t0_template
1748
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1749
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
1751
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
1752
f_charbig = 'updated by trigger'
1753
WHERE f_int1 = new.f_int1;
1755
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1756
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1758
# check trigger-3 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 = - old.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-4 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 AFTER 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 = new.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-5 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 = - old.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-6 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 BEFORE DELETE 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;
1840
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1842
# check trigger-7 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 AFTER 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-8 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;
1872
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1873
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1875
SET new.f_int1 = old.f_int1 + @max_row,
1876
new.f_int2 = old.f_int2 - @max_row,
1877
new.f_charbig = '####updated per update trigger####';
1880
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1881
f_charbig = '####updated per update statement itself####';
1883
# check trigger-9 success: 1
1885
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1886
f_int2 = CAST(f_char1 AS SIGNED INT),
1887
f_charbig = CONCAT('===',f_char1,'===');
1888
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
1890
SET new.f_int1 = new.f_int1 + @max_row,
1891
new.f_int2 = new.f_int2 - @max_row,
1892
new.f_charbig = '####updated per update trigger####';
1895
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1896
f_charbig = '####updated per update statement itself####';
1898
# check trigger-10 success: 1
1900
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
1901
f_int2 = CAST(f_char1 AS SIGNED INT),
1902
f_charbig = CONCAT('===',f_char1,'===');
1903
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
1905
SET new.f_int1 = @my_max1 + @counter,
1906
new.f_int2 = @my_min2 - @counter,
1907
new.f_charbig = '####updated per insert trigger####';
1908
SET @counter = @counter + 1;
1911
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
1912
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1913
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1914
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1915
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1919
# check trigger-11 success: 1
1921
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
1922
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
1923
AND f_charbig = '####updated per insert trigger####';
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_char1, f_char2, f_charbig)
1934
SELECT 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-12 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####';
1946
Table Op Msg_type Msg_text
1947
test.t1 analyze note The storage engine for the table doesn't support analyze
1948
CHECK TABLE t1 EXTENDED;
1949
Table Op Msg_type Msg_text
1950
test.t1 check note The storage engine for the table doesn't support check
1951
CHECKSUM TABLE t1 EXTENDED;
1953
test.t1 <some_value>
1955
Table Op Msg_type Msg_text
1956
test.t1 optimize note The storage engine for the table doesn't support optimize
1957
# check layout success: 1
1958
REPAIR TABLE t1 EXTENDED;
1959
Table Op Msg_type Msg_text
1960
test.t1 repair note The storage engine for the table doesn't support repair
1961
# check layout success: 1
1964
# check TRUNCATE success: 1
1965
# check layout success: 1
1966
# End usability test (inc/partition_check.inc)
1968
#------------------------------------------------------------------------
1969
# 4 Storage engine assignment after partition name + after name of
1970
# subpartitions belonging to another partition
1971
#------------------------------------------------------------------------
1977
f_charbig VARCHAR(1000)
1979
PARTITION BY RANGE(f_int1)
1980
SUBPARTITION BY HASH(f_int1)
1981
( PARTITION part1 VALUES LESS THAN (10)
1982
(SUBPARTITION subpart11,
1983
SUBPARTITION subpart12),
1984
PARTITION part2 VALUES LESS THAN (2147483646)
1985
(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
1986
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
1988
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
1994
f_charbig VARCHAR(1000)
1997
PARTITION BY RANGE(f_int1)
1998
SUBPARTITION BY HASH(f_int1)
1999
( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'MyISAM'
2000
(SUBPARTITION subpart11,
2001
SUBPARTITION subpart12),
2002
PARTITION part2 VALUES LESS THAN (2147483646)
2003
(SUBPARTITION subpart21,
2004
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
2012
f_charbig VARCHAR(1000)
2014
PARTITION BY RANGE(f_int1)
2015
SUBPARTITION BY HASH(f_int1)
2016
( PARTITION part1 VALUES LESS THAN (10) ENGINE = 'MyISAM'
2017
(SUBPARTITION subpart11,
2018
SUBPARTITION subpart12),
2019
PARTITION part2 VALUES LESS THAN (2147483646)
2020
(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
2021
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
2023
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2024
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2025
# Start usability test (inc/partition_check.inc)
2027
SHOW CREATE TABLE t1;
2029
t1 CREATE TABLE `t1` (
2030
`f_int1` int(11) DEFAULT NULL,
2031
`f_int2` int(11) DEFAULT NULL,
2032
`f_char1` char(20) DEFAULT NULL,
2033
`f_char2` char(20) DEFAULT NULL,
2034
`f_charbig` varchar(1000) DEFAULT NULL
2035
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
2038
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYD
2039
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYI
2040
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYD
2041
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYI
2042
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYD
2043
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYI
2044
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYD
2045
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYI
2046
$MYSQLTEST_VARDIR/master-data/test/t1.frm
2047
$MYSQLTEST_VARDIR/master-data/test/t1.par
2049
# check prerequisites-1 success: 1
2050
# check COUNT(*) success: 1
2051
# check MIN/MAX(f_int1) success: 1
2052
# check MIN/MAX(f_int2) success: 1
2053
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2054
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2055
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2056
WHERE f_int1 IN (2,3);
2057
# check prerequisites-3 success: 1
2058
DELETE FROM t1 WHERE f_charbig = 'delete me';
2059
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2060
# check read via f_int1 success: 1
2061
# check read via f_int2 success: 1
2063
# check multiple-1 success: 1
2064
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2066
# check multiple-2 success: 1
2067
INSERT INTO t1 SELECT * FROM t0_template
2068
WHERE MOD(f_int1,3) = 0;
2070
# check multiple-3 success: 1
2071
UPDATE t1 SET f_int1 = f_int1 + @max_row
2072
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2073
AND @max_row_div2 + @max_row_div4;
2075
# check multiple-4 success: 1
2077
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2078
AND @max_row_div2 + @max_row_div4 + @max_row;
2080
# check multiple-5 success: 1
2081
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2083
SET f_int1 = @cur_value , f_int2 = @cur_value,
2084
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2085
f_charbig = '#SINGLE#';
2087
# check single-1 success: 1
2088
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2090
SET f_int1 = @cur_value , f_int2 = @cur_value,
2091
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2092
f_charbig = '#SINGLE#';
2094
# check single-2 success: 1
2095
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2096
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2097
UPDATE t1 SET f_int1 = @cur_value2
2098
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2100
# check single-3 success: 1
2101
SET @cur_value1= -1;
2102
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2103
UPDATE t1 SET f_int1 = @cur_value1
2104
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2106
# check single-4 success: 1
2107
SELECT MAX(f_int1) INTO @cur_value FROM t1;
2108
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
2110
# check single-5 success: 1
2111
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
2113
# check single-6 success: 1
2114
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
2115
ERROR HY000: Table has no partition for value 2147483647
2116
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
2117
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
2118
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
2119
f_charbig = '#NULL#';
2121
SET f_int1 = NULL , f_int2 = -@max_row,
2122
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
2123
f_charbig = '#NULL#';
2124
# check null success: 1
2126
# check null-1 success: 1
2127
UPDATE t1 SET f_int1 = -@max_row
2128
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2129
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2131
# check null-2 success: 1
2132
UPDATE t1 SET f_int1 = NULL
2133
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2134
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2136
# check null-3 success: 1
2138
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2139
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2141
# check null-4 success: 1
2143
WHERE f_int1 = 0 AND f_int2 = 0
2144
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
2145
AND f_charbig = '#NULL#';
2147
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2148
SELECT f_int1, f_int1, '', '', 'was inserted'
2149
FROM t0_template source_tab
2150
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2152
# check transactions-1 success: 1
2155
# check transactions-2 success: 1
2158
# check transactions-3 success: 1
2159
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2163
# check transactions-4 success: 1
2164
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2165
SELECT f_int1, f_int1, '', '', 'was inserted'
2166
FROM t0_template source_tab
2167
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2169
# check transactions-5 success: 1
2172
Warning 1196 Some non-transactional changed tables couldn't be rolled back
2174
# check transactions-6 success: 1
2175
# INFO: Storage engine used for t1 seems to be not transactional.
2178
# check transactions-7 success: 1
2179
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2181
SET @@session.sql_mode = 'traditional';
2182
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
2183
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2184
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
2185
'', '', 'was inserted' FROM t0_template
2186
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2187
ERROR 22012: Division by 0
2190
# check transactions-8 success: 1
2191
# INFO: Storage engine used for t1 seems to be unable to revert
2192
# changes made by the failing statement.
2193
SET @@session.sql_mode = '';
2195
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2197
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
2199
# check special-1 success: 1
2200
UPDATE t1 SET f_charbig = '';
2202
# check special-2 success: 1
2203
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
2204
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2205
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
2206
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2207
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2208
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2209
'just inserted' FROM t0_template
2210
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2211
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
2213
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2214
f_charbig = 'updated by trigger'
2215
WHERE f_int1 = new.f_int1;
2217
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2218
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2219
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2221
# check trigger-1 success: 1
2223
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2224
f_int2 = CAST(f_char1 AS SIGNED INT),
2225
f_charbig = 'just inserted'
2226
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2228
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2229
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2230
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2231
'just inserted' FROM t0_template
2232
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2233
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
2235
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2236
f_charbig = 'updated by trigger'
2237
WHERE f_int1 = new.f_int1;
2239
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2240
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2241
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2243
# check trigger-2 success: 1
2245
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2246
f_int2 = CAST(f_char1 AS SIGNED INT),
2247
f_charbig = 'just inserted'
2248
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2250
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2251
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2252
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2253
'just inserted' FROM t0_template
2254
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2255
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2257
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2258
f_charbig = 'updated by trigger'
2259
WHERE f_int1 = new.f_int1;
2261
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2262
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2264
# check trigger-3 success: 1
2266
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2267
f_int2 = CAST(f_char1 AS SIGNED INT),
2268
f_charbig = 'just inserted'
2269
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2271
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2272
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2273
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2274
'just inserted' FROM t0_template
2275
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2276
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2278
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2279
f_charbig = 'updated by trigger'
2280
WHERE f_int1 = - old.f_int1;
2282
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2283
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2285
# check trigger-4 success: 1
2287
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2288
f_int2 = CAST(f_char1 AS SIGNED INT),
2289
f_charbig = 'just inserted'
2290
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2292
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2293
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2294
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2295
'just inserted' FROM t0_template
2296
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2297
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2299
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2300
f_charbig = 'updated by trigger'
2301
WHERE f_int1 = new.f_int1;
2303
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2304
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2306
# check trigger-5 success: 1
2308
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2309
f_int2 = CAST(f_char1 AS SIGNED INT),
2310
f_charbig = 'just inserted'
2311
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2313
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2314
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2315
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2316
'just inserted' FROM t0_template
2317
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2318
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2320
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2321
f_charbig = 'updated by trigger'
2322
WHERE f_int1 = - old.f_int1;
2324
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2325
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2327
# check trigger-6 success: 1
2329
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2330
f_int2 = CAST(f_char1 AS SIGNED INT),
2331
f_charbig = 'just inserted'
2332
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2334
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2335
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2336
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2337
'just inserted' FROM t0_template
2338
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2339
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
2341
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2342
f_charbig = 'updated by trigger'
2343
WHERE f_int1 = - old.f_int1;
2346
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2348
# check trigger-7 success: 1
2350
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2351
f_int2 = CAST(f_char1 AS SIGNED INT),
2352
f_charbig = 'just inserted'
2353
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2355
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2356
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2357
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2358
'just inserted' FROM t0_template
2359
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2360
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
2362
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2363
f_charbig = 'updated by trigger'
2364
WHERE f_int1 = - old.f_int1;
2367
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2369
# check trigger-8 success: 1
2371
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2372
f_int2 = CAST(f_char1 AS SIGNED INT),
2373
f_charbig = 'just inserted'
2374
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2376
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2378
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2379
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2381
SET new.f_int1 = old.f_int1 + @max_row,
2382
new.f_int2 = old.f_int2 - @max_row,
2383
new.f_charbig = '####updated per update trigger####';
2386
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2387
f_charbig = '####updated per update statement itself####';
2389
# check trigger-9 success: 1
2391
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2392
f_int2 = CAST(f_char1 AS SIGNED INT),
2393
f_charbig = CONCAT('===',f_char1,'===');
2394
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2396
SET new.f_int1 = new.f_int1 + @max_row,
2397
new.f_int2 = new.f_int2 - @max_row,
2398
new.f_charbig = '####updated per update trigger####';
2401
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2402
f_charbig = '####updated per update statement itself####';
2404
# check trigger-10 success: 1
2406
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2407
f_int2 = CAST(f_char1 AS SIGNED INT),
2408
f_charbig = CONCAT('===',f_char1,'===');
2409
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2411
SET new.f_int1 = @my_max1 + @counter,
2412
new.f_int2 = @my_min2 - @counter,
2413
new.f_charbig = '####updated per insert trigger####';
2414
SET @counter = @counter + 1;
2417
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2418
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2419
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2420
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2421
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2425
# check trigger-11 success: 1
2427
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2428
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2429
AND f_charbig = '####updated per insert trigger####';
2430
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2432
SET new.f_int1 = @my_max1 + @counter,
2433
new.f_int2 = @my_min2 - @counter,
2434
new.f_charbig = '####updated per insert trigger####';
2435
SET @counter = @counter + 1;
2438
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2439
INSERT INTO t1 (f_char1, f_char2, f_charbig)
2440
SELECT CAST(f_int1 AS CHAR),
2441
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2442
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2446
# check trigger-12 success: 1
2448
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2449
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2450
AND f_charbig = '####updated per insert trigger####';
2452
Table Op Msg_type Msg_text
2453
test.t1 analyze note The storage engine for the table doesn't support analyze
2454
CHECK TABLE t1 EXTENDED;
2455
Table Op Msg_type Msg_text
2456
test.t1 check note The storage engine for the table doesn't support check
2457
CHECKSUM TABLE t1 EXTENDED;
2459
test.t1 <some_value>
2461
Table Op Msg_type Msg_text
2462
test.t1 optimize note The storage engine for the table doesn't support optimize
2463
# check layout success: 1
2464
REPAIR TABLE t1 EXTENDED;
2465
Table Op Msg_type Msg_text
2466
test.t1 repair note The storage engine for the table doesn't support repair
2467
# check layout success: 1
2470
# check TRUNCATE success: 1
2471
# check layout success: 1
2472
# End usability test (inc/partition_check.inc)
2479
f_charbig VARCHAR(1000)
2481
PARTITION BY RANGE(f_int1)
2482
SUBPARTITION BY HASH(f_int1)
2483
( PARTITION part1 VALUES LESS THAN (10)
2484
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
2485
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
2486
PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = 'MyISAM'
2487
(SUBPARTITION subpart21 ENGINE = 'MyISAM',
2488
SUBPARTITION subpart22)
2490
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2491
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2492
# Start usability test (inc/partition_check.inc)
2494
SHOW CREATE TABLE t1;
2496
t1 CREATE TABLE `t1` (
2497
`f_int1` int(11) DEFAULT NULL,
2498
`f_int2` int(11) DEFAULT NULL,
2499
`f_char1` char(20) DEFAULT NULL,
2500
`f_char2` char(20) DEFAULT NULL,
2501
`f_charbig` varchar(1000) DEFAULT NULL
2502
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
2505
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYD
2506
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYI
2507
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYD
2508
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYI
2509
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYD
2510
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYI
2511
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYD
2512
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYI
2513
$MYSQLTEST_VARDIR/master-data/test/t1.frm
2514
$MYSQLTEST_VARDIR/master-data/test/t1.par
2516
# check prerequisites-1 success: 1
2517
# check COUNT(*) success: 1
2518
# check MIN/MAX(f_int1) success: 1
2519
# check MIN/MAX(f_int2) success: 1
2520
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2521
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2522
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2523
WHERE f_int1 IN (2,3);
2524
# check prerequisites-3 success: 1
2525
DELETE FROM t1 WHERE f_charbig = 'delete me';
2526
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2527
# check read via f_int1 success: 1
2528
# check read via f_int2 success: 1
2530
# check multiple-1 success: 1
2531
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2533
# check multiple-2 success: 1
2534
INSERT INTO t1 SELECT * FROM t0_template
2535
WHERE MOD(f_int1,3) = 0;
2537
# check multiple-3 success: 1
2538
UPDATE t1 SET f_int1 = f_int1 + @max_row
2539
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
2540
AND @max_row_div2 + @max_row_div4;
2542
# check multiple-4 success: 1
2544
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
2545
AND @max_row_div2 + @max_row_div4 + @max_row;
2547
# check multiple-5 success: 1
2548
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
2550
SET f_int1 = @cur_value , f_int2 = @cur_value,
2551
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2552
f_charbig = '#SINGLE#';
2554
# check single-1 success: 1
2555
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
2557
SET f_int1 = @cur_value , f_int2 = @cur_value,
2558
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
2559
f_charbig = '#SINGLE#';
2561
# check single-2 success: 1
2562
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
2563
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
2564
UPDATE t1 SET f_int1 = @cur_value2
2565
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
2567
# check single-3 success: 1
2568
SET @cur_value1= -1;
2569
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
2570
UPDATE t1 SET f_int1 = @cur_value1
2571
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
2573
# check single-4 success: 1
2574
SELECT MAX(f_int1) INTO @cur_value FROM t1;
2575
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
2577
# check single-5 success: 1
2578
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
2580
# check single-6 success: 1
2581
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
2582
ERROR HY000: Table has no partition for value 2147483647
2583
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
2584
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
2585
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
2586
f_charbig = '#NULL#';
2588
SET f_int1 = NULL , f_int2 = -@max_row,
2589
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
2590
f_charbig = '#NULL#';
2591
# check null success: 1
2593
# check null-1 success: 1
2594
UPDATE t1 SET f_int1 = -@max_row
2595
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2596
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2598
# check null-2 success: 1
2599
UPDATE t1 SET f_int1 = NULL
2600
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2601
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2603
# check null-3 success: 1
2605
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
2606
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
2608
# check null-4 success: 1
2610
WHERE f_int1 = 0 AND f_int2 = 0
2611
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
2612
AND f_charbig = '#NULL#';
2614
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2615
SELECT f_int1, f_int1, '', '', 'was inserted'
2616
FROM t0_template source_tab
2617
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2619
# check transactions-1 success: 1
2622
# check transactions-2 success: 1
2625
# check transactions-3 success: 1
2626
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2630
# check transactions-4 success: 1
2631
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2632
SELECT f_int1, f_int1, '', '', 'was inserted'
2633
FROM t0_template source_tab
2634
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2636
# check transactions-5 success: 1
2639
Warning 1196 Some non-transactional changed tables couldn't be rolled back
2641
# check transactions-6 success: 1
2642
# INFO: Storage engine used for t1 seems to be not transactional.
2645
# check transactions-7 success: 1
2646
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2648
SET @@session.sql_mode = 'traditional';
2649
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
2650
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2651
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
2652
'', '', 'was inserted' FROM t0_template
2653
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
2654
ERROR 22012: Division by 0
2657
# check transactions-8 success: 1
2658
# INFO: Storage engine used for t1 seems to be unable to revert
2659
# changes made by the failing statement.
2660
SET @@session.sql_mode = '';
2662
DELETE FROM t1 WHERE f_charbig = 'was inserted';
2664
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
2666
# check special-1 success: 1
2667
UPDATE t1 SET f_charbig = '';
2669
# check special-2 success: 1
2670
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
2671
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2672
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
2673
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2674
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2675
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2676
'just inserted' FROM t0_template
2677
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2678
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
2680
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2681
f_charbig = 'updated by trigger'
2682
WHERE f_int1 = new.f_int1;
2684
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2685
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2686
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2688
# check trigger-1 success: 1
2690
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2691
f_int2 = CAST(f_char1 AS SIGNED INT),
2692
f_charbig = 'just inserted'
2693
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2695
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2696
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2697
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2698
'just inserted' FROM t0_template
2699
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2700
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
2702
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2703
f_charbig = 'updated by trigger'
2704
WHERE f_int1 = new.f_int1;
2706
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2707
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
2708
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2710
# check trigger-2 success: 1
2712
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2713
f_int2 = CAST(f_char1 AS SIGNED INT),
2714
f_charbig = 'just inserted'
2715
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2717
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2718
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2719
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2720
'just inserted' FROM t0_template
2721
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2722
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2724
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2725
f_charbig = 'updated by trigger'
2726
WHERE f_int1 = new.f_int1;
2728
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2729
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2731
# check trigger-3 success: 1
2733
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2734
f_int2 = CAST(f_char1 AS SIGNED INT),
2735
f_charbig = 'just inserted'
2736
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2738
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2739
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2740
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2741
'just inserted' FROM t0_template
2742
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2743
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
2745
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2746
f_charbig = 'updated by trigger'
2747
WHERE f_int1 = - old.f_int1;
2749
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2750
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2752
# check trigger-4 success: 1
2754
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2755
f_int2 = CAST(f_char1 AS SIGNED INT),
2756
f_charbig = 'just inserted'
2757
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2759
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2760
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2761
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2762
'just inserted' FROM t0_template
2763
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2764
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2766
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2767
f_charbig = 'updated by trigger'
2768
WHERE f_int1 = new.f_int1;
2770
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2771
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2773
# check trigger-5 success: 1
2775
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2776
f_int2 = CAST(f_char1 AS SIGNED INT),
2777
f_charbig = 'just inserted'
2778
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2780
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2781
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2782
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2783
'just inserted' FROM t0_template
2784
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2785
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
2787
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2788
f_charbig = 'updated by trigger'
2789
WHERE f_int1 = - old.f_int1;
2791
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
2792
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2794
# check trigger-6 success: 1
2796
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2797
f_int2 = CAST(f_char1 AS SIGNED INT),
2798
f_charbig = 'just inserted'
2799
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2801
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2802
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2803
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2804
'just inserted' FROM t0_template
2805
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2806
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
2808
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2809
f_charbig = 'updated by trigger'
2810
WHERE f_int1 = - old.f_int1;
2813
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2815
# check trigger-7 success: 1
2817
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2818
f_int2 = CAST(f_char1 AS SIGNED INT),
2819
f_charbig = 'just inserted'
2820
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2822
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2823
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
2824
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
2825
'just inserted' FROM t0_template
2826
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2827
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
2829
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
2830
f_charbig = 'updated by trigger'
2831
WHERE f_int1 = - old.f_int1;
2834
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
2836
# check trigger-8 success: 1
2838
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2839
f_int2 = CAST(f_char1 AS SIGNED INT),
2840
f_charbig = 'just inserted'
2841
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
2843
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2845
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
2846
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2848
SET new.f_int1 = old.f_int1 + @max_row,
2849
new.f_int2 = old.f_int2 - @max_row,
2850
new.f_charbig = '####updated per update trigger####';
2853
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2854
f_charbig = '####updated per update statement itself####';
2856
# check trigger-9 success: 1
2858
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2859
f_int2 = CAST(f_char1 AS SIGNED INT),
2860
f_charbig = CONCAT('===',f_char1,'===');
2861
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
2863
SET new.f_int1 = new.f_int1 + @max_row,
2864
new.f_int2 = new.f_int2 - @max_row,
2865
new.f_charbig = '####updated per update trigger####';
2868
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
2869
f_charbig = '####updated per update statement itself####';
2871
# check trigger-10 success: 1
2873
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
2874
f_int2 = CAST(f_char1 AS SIGNED INT),
2875
f_charbig = CONCAT('===',f_char1,'===');
2876
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2878
SET new.f_int1 = @my_max1 + @counter,
2879
new.f_int2 = @my_min2 - @counter,
2880
new.f_charbig = '####updated per insert trigger####';
2881
SET @counter = @counter + 1;
2884
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2885
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2886
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2887
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2888
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2892
# check trigger-11 success: 1
2894
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2895
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2896
AND f_charbig = '####updated per insert trigger####';
2897
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
2899
SET new.f_int1 = @my_max1 + @counter,
2900
new.f_int2 = @my_min2 - @counter,
2901
new.f_charbig = '####updated per insert trigger####';
2902
SET @counter = @counter + 1;
2905
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
2906
INSERT INTO t1 (f_char1, f_char2, f_charbig)
2907
SELECT CAST(f_int1 AS CHAR),
2908
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
2909
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
2913
# check trigger-12 success: 1
2915
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
2916
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
2917
AND f_charbig = '####updated per insert trigger####';
2919
Table Op Msg_type Msg_text
2920
test.t1 analyze note The storage engine for the table doesn't support analyze
2921
CHECK TABLE t1 EXTENDED;
2922
Table Op Msg_type Msg_text
2923
test.t1 check note The storage engine for the table doesn't support check
2924
CHECKSUM TABLE t1 EXTENDED;
2926
test.t1 <some_value>
2928
Table Op Msg_type Msg_text
2929
test.t1 optimize note The storage engine for the table doesn't support optimize
2930
# check layout success: 1
2931
REPAIR TABLE t1 EXTENDED;
2932
Table Op Msg_type Msg_text
2933
test.t1 repair note The storage engine for the table doesn't support repair
2934
# check layout success: 1
2937
# check TRUNCATE success: 1
2938
# check layout success: 1
2939
# End usability test (inc/partition_check.inc)
2941
#------------------------------------------------------------------------
2942
# 5 Precedence of storage engine assignments (if there is any)
2943
#------------------------------------------------------------------------
2944
# 5.1 Storage engine assignment after column list + after partition
2945
# or subpartition name
2951
f_charbig VARCHAR(1000)
2953
PARTITION BY HASH(f_int1)
2954
( PARTITION part1 STORAGE ENGINE = 'MyISAM',
2955
PARTITION part2 STORAGE ENGINE = 'MyISAM'
2957
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
2958
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
2959
# Start usability test (inc/partition_check.inc)
2961
SHOW CREATE TABLE t1;
2963
t1 CREATE TABLE `t1` (
2964
`f_int1` int(11) DEFAULT NULL,
2965
`f_int2` int(11) DEFAULT NULL,
2966
`f_char1` char(20) DEFAULT NULL,
2967
`f_char2` char(20) DEFAULT NULL,
2968
`f_charbig` varchar(1000) DEFAULT NULL
2969
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */
2972
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYD
2973
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYI
2974
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2.MYD
2975
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2.MYI
2976
$MYSQLTEST_VARDIR/master-data/test/t1.frm
2977
$MYSQLTEST_VARDIR/master-data/test/t1.par
2979
# check prerequisites-1 success: 1
2980
# check COUNT(*) success: 1
2981
# check MIN/MAX(f_int1) success: 1
2982
# check MIN/MAX(f_int2) success: 1
2983
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
2984
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
2985
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
2986
WHERE f_int1 IN (2,3);
2987
# check prerequisites-3 success: 1
2988
DELETE FROM t1 WHERE f_charbig = 'delete me';
2989
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
2990
# check read via f_int1 success: 1
2991
# check read via f_int2 success: 1
2993
# check multiple-1 success: 1
2994
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
2996
# check multiple-2 success: 1
2997
INSERT INTO t1 SELECT * FROM t0_template
2998
WHERE MOD(f_int1,3) = 0;
3000
# check multiple-3 success: 1
3001
UPDATE t1 SET f_int1 = f_int1 + @max_row
3002
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
3003
AND @max_row_div2 + @max_row_div4;
3005
# check multiple-4 success: 1
3007
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
3008
AND @max_row_div2 + @max_row_div4 + @max_row;
3010
# check multiple-5 success: 1
3011
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
3013
SET f_int1 = @cur_value , f_int2 = @cur_value,
3014
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3015
f_charbig = '#SINGLE#';
3017
# check single-1 success: 1
3018
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
3020
SET f_int1 = @cur_value , f_int2 = @cur_value,
3021
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3022
f_charbig = '#SINGLE#';
3024
# check single-2 success: 1
3025
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
3026
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
3027
UPDATE t1 SET f_int1 = @cur_value2
3028
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
3030
# check single-3 success: 1
3031
SET @cur_value1= -1;
3032
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
3033
UPDATE t1 SET f_int1 = @cur_value1
3034
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
3036
# check single-4 success: 1
3037
SELECT MAX(f_int1) INTO @cur_value FROM t1;
3038
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
3040
# check single-5 success: 1
3041
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
3043
# check single-6 success: 1
3044
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3046
# check single-7 success: 1
3047
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
3048
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3049
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3050
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3051
f_charbig = '#NULL#';
3053
SET f_int1 = NULL , f_int2 = -@max_row,
3054
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3055
f_charbig = '#NULL#';
3056
# check null success: 1
3058
# check null-1 success: 1
3059
UPDATE t1 SET f_int1 = -@max_row
3060
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3061
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3063
# check null-2 success: 1
3064
UPDATE t1 SET f_int1 = NULL
3065
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3066
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3068
# check null-3 success: 1
3070
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3071
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3073
# check null-4 success: 1
3075
WHERE f_int1 = 0 AND f_int2 = 0
3076
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
3077
AND f_charbig = '#NULL#';
3079
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3080
SELECT f_int1, f_int1, '', '', 'was inserted'
3081
FROM t0_template source_tab
3082
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3084
# check transactions-1 success: 1
3087
# check transactions-2 success: 1
3090
# check transactions-3 success: 1
3091
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3095
# check transactions-4 success: 1
3096
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3097
SELECT f_int1, f_int1, '', '', 'was inserted'
3098
FROM t0_template source_tab
3099
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3101
# check transactions-5 success: 1
3104
Warning 1196 Some non-transactional changed tables couldn't be rolled back
3106
# check transactions-6 success: 1
3107
# INFO: Storage engine used for t1 seems to be not transactional.
3110
# check transactions-7 success: 1
3111
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3113
SET @@session.sql_mode = 'traditional';
3114
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
3115
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3116
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
3117
'', '', 'was inserted' FROM t0_template
3118
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3119
ERROR 22012: Division by 0
3122
# check transactions-8 success: 1
3123
# INFO: Storage engine used for t1 seems to be unable to revert
3124
# changes made by the failing statement.
3125
SET @@session.sql_mode = '';
3127
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3129
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
3131
# check special-1 success: 1
3132
UPDATE t1 SET f_charbig = '';
3134
# check special-2 success: 1
3135
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
3136
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3137
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
3138
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3139
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3140
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3141
'just inserted' FROM t0_template
3142
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3143
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
3145
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3146
f_charbig = 'updated by trigger'
3147
WHERE f_int1 = new.f_int1;
3149
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3150
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3151
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3153
# check trigger-1 success: 1
3155
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3156
f_int2 = CAST(f_char1 AS SIGNED INT),
3157
f_charbig = 'just inserted'
3158
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3160
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3161
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3162
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3163
'just inserted' FROM t0_template
3164
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3165
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
3167
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3168
f_charbig = 'updated by trigger'
3169
WHERE f_int1 = new.f_int1;
3171
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3172
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3173
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3175
# check trigger-2 success: 1
3177
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3178
f_int2 = CAST(f_char1 AS SIGNED INT),
3179
f_charbig = 'just inserted'
3180
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3182
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3183
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3184
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3185
'just inserted' FROM t0_template
3186
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3187
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3189
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3190
f_charbig = 'updated by trigger'
3191
WHERE f_int1 = new.f_int1;
3193
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3194
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3196
# check trigger-3 success: 1
3198
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3199
f_int2 = CAST(f_char1 AS SIGNED INT),
3200
f_charbig = 'just inserted'
3201
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3203
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3204
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3205
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3206
'just inserted' FROM t0_template
3207
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3208
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3210
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3211
f_charbig = 'updated by trigger'
3212
WHERE f_int1 = - old.f_int1;
3214
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3215
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3217
# check trigger-4 success: 1
3219
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3220
f_int2 = CAST(f_char1 AS SIGNED INT),
3221
f_charbig = 'just inserted'
3222
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3224
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3225
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3226
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3227
'just inserted' FROM t0_template
3228
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3229
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3231
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3232
f_charbig = 'updated by trigger'
3233
WHERE f_int1 = new.f_int1;
3235
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3236
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3238
# check trigger-5 success: 1
3240
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3241
f_int2 = CAST(f_char1 AS SIGNED INT),
3242
f_charbig = 'just inserted'
3243
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3245
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3246
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3247
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3248
'just inserted' FROM t0_template
3249
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3250
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3252
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3253
f_charbig = 'updated by trigger'
3254
WHERE f_int1 = - old.f_int1;
3256
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3257
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3259
# check trigger-6 success: 1
3261
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3262
f_int2 = CAST(f_char1 AS SIGNED INT),
3263
f_charbig = 'just inserted'
3264
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3266
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3267
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3268
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3269
'just inserted' FROM t0_template
3270
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3271
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
3273
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3274
f_charbig = 'updated by trigger'
3275
WHERE f_int1 = - old.f_int1;
3278
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3280
# check trigger-7 success: 1
3282
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3283
f_int2 = CAST(f_char1 AS SIGNED INT),
3284
f_charbig = 'just inserted'
3285
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3287
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3288
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3289
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3290
'just inserted' FROM t0_template
3291
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3292
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
3294
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3295
f_charbig = 'updated by trigger'
3296
WHERE f_int1 = - old.f_int1;
3299
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3301
# check trigger-8 success: 1
3303
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3304
f_int2 = CAST(f_char1 AS SIGNED INT),
3305
f_charbig = 'just inserted'
3306
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3308
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3310
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3311
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3313
SET new.f_int1 = old.f_int1 + @max_row,
3314
new.f_int2 = old.f_int2 - @max_row,
3315
new.f_charbig = '####updated per update trigger####';
3318
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3319
f_charbig = '####updated per update statement itself####';
3321
# check trigger-9 success: 1
3323
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3324
f_int2 = CAST(f_char1 AS SIGNED INT),
3325
f_charbig = CONCAT('===',f_char1,'===');
3326
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3328
SET new.f_int1 = new.f_int1 + @max_row,
3329
new.f_int2 = new.f_int2 - @max_row,
3330
new.f_charbig = '####updated per update trigger####';
3333
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3334
f_charbig = '####updated per update statement itself####';
3336
# check trigger-10 success: 1
3338
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3339
f_int2 = CAST(f_char1 AS SIGNED INT),
3340
f_charbig = CONCAT('===',f_char1,'===');
3341
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3343
SET new.f_int1 = @my_max1 + @counter,
3344
new.f_int2 = @my_min2 - @counter,
3345
new.f_charbig = '####updated per insert trigger####';
3346
SET @counter = @counter + 1;
3349
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3350
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3351
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3352
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3353
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3357
# check trigger-11 success: 1
3359
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3360
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3361
AND f_charbig = '####updated per insert trigger####';
3362
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3364
SET new.f_int1 = @my_max1 + @counter,
3365
new.f_int2 = @my_min2 - @counter,
3366
new.f_charbig = '####updated per insert trigger####';
3367
SET @counter = @counter + 1;
3370
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3371
INSERT INTO t1 (f_char1, f_char2, f_charbig)
3372
SELECT CAST(f_int1 AS CHAR),
3373
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3374
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3378
# check trigger-12 success: 1
3380
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3381
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3382
AND f_charbig = '####updated per insert trigger####';
3384
Table Op Msg_type Msg_text
3385
test.t1 analyze note The storage engine for the table doesn't support analyze
3386
CHECK TABLE t1 EXTENDED;
3387
Table Op Msg_type Msg_text
3388
test.t1 check note The storage engine for the table doesn't support check
3389
CHECKSUM TABLE t1 EXTENDED;
3391
test.t1 <some_value>
3393
Table Op Msg_type Msg_text
3394
test.t1 optimize note The storage engine for the table doesn't support optimize
3395
# check layout success: 1
3396
REPAIR TABLE t1 EXTENDED;
3397
Table Op Msg_type Msg_text
3398
test.t1 repair note The storage engine for the table doesn't support repair
3399
# check layout success: 1
3402
# check TRUNCATE success: 1
3403
# check layout success: 1
3404
# End usability test (inc/partition_check.inc)
3411
f_charbig VARCHAR(1000)
3413
PARTITION BY RANGE(f_int1)
3414
SUBPARTITION BY HASH(f_int1)
3415
( PARTITION part1 VALUES LESS THAN (10)
3416
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
3417
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
3418
PARTITION part2 VALUES LESS THAN (2147483646)
3419
(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
3420
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
3422
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3423
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
3424
# Start usability test (inc/partition_check.inc)
3426
SHOW CREATE TABLE t1;
3428
t1 CREATE TABLE `t1` (
3429
`f_int1` int(11) DEFAULT NULL,
3430
`f_int2` int(11) DEFAULT NULL,
3431
`f_char1` char(20) DEFAULT NULL,
3432
`f_char2` char(20) DEFAULT NULL,
3433
`f_charbig` varchar(1000) DEFAULT NULL
3434
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
3437
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYD
3438
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYI
3439
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYD
3440
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYI
3441
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYD
3442
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYI
3443
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYD
3444
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYI
3445
$MYSQLTEST_VARDIR/master-data/test/t1.frm
3446
$MYSQLTEST_VARDIR/master-data/test/t1.par
3448
# check prerequisites-1 success: 1
3449
# check COUNT(*) success: 1
3450
# check MIN/MAX(f_int1) success: 1
3451
# check MIN/MAX(f_int2) success: 1
3452
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3453
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3454
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
3455
WHERE f_int1 IN (2,3);
3456
# check prerequisites-3 success: 1
3457
DELETE FROM t1 WHERE f_charbig = 'delete me';
3458
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
3459
# check read via f_int1 success: 1
3460
# check read via f_int2 success: 1
3462
# check multiple-1 success: 1
3463
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
3465
# check multiple-2 success: 1
3466
INSERT INTO t1 SELECT * FROM t0_template
3467
WHERE MOD(f_int1,3) = 0;
3469
# check multiple-3 success: 1
3470
UPDATE t1 SET f_int1 = f_int1 + @max_row
3471
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
3472
AND @max_row_div2 + @max_row_div4;
3474
# check multiple-4 success: 1
3476
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
3477
AND @max_row_div2 + @max_row_div4 + @max_row;
3479
# check multiple-5 success: 1
3480
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
3482
SET f_int1 = @cur_value , f_int2 = @cur_value,
3483
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3484
f_charbig = '#SINGLE#';
3486
# check single-1 success: 1
3487
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
3489
SET f_int1 = @cur_value , f_int2 = @cur_value,
3490
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3491
f_charbig = '#SINGLE#';
3493
# check single-2 success: 1
3494
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
3495
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
3496
UPDATE t1 SET f_int1 = @cur_value2
3497
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
3499
# check single-3 success: 1
3500
SET @cur_value1= -1;
3501
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
3502
UPDATE t1 SET f_int1 = @cur_value1
3503
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
3505
# check single-4 success: 1
3506
SELECT MAX(f_int1) INTO @cur_value FROM t1;
3507
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
3509
# check single-5 success: 1
3510
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
3512
# check single-6 success: 1
3513
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3514
ERROR HY000: Table has no partition for value 2147483647
3515
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3516
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3517
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3518
f_charbig = '#NULL#';
3520
SET f_int1 = NULL , f_int2 = -@max_row,
3521
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3522
f_charbig = '#NULL#';
3523
# check null success: 1
3525
# check null-1 success: 1
3526
UPDATE t1 SET f_int1 = -@max_row
3527
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3528
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3530
# check null-2 success: 1
3531
UPDATE t1 SET f_int1 = NULL
3532
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3533
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3535
# check null-3 success: 1
3537
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3538
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
3540
# check null-4 success: 1
3542
WHERE f_int1 = 0 AND f_int2 = 0
3543
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
3544
AND f_charbig = '#NULL#';
3546
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3547
SELECT f_int1, f_int1, '', '', 'was inserted'
3548
FROM t0_template source_tab
3549
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3551
# check transactions-1 success: 1
3554
# check transactions-2 success: 1
3557
# check transactions-3 success: 1
3558
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3562
# check transactions-4 success: 1
3563
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3564
SELECT f_int1, f_int1, '', '', 'was inserted'
3565
FROM t0_template source_tab
3566
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3568
# check transactions-5 success: 1
3571
Warning 1196 Some non-transactional changed tables couldn't be rolled back
3573
# check transactions-6 success: 1
3574
# INFO: Storage engine used for t1 seems to be not transactional.
3577
# check transactions-7 success: 1
3578
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3580
SET @@session.sql_mode = 'traditional';
3581
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
3582
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3583
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
3584
'', '', 'was inserted' FROM t0_template
3585
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
3586
ERROR 22012: Division by 0
3589
# check transactions-8 success: 1
3590
# INFO: Storage engine used for t1 seems to be unable to revert
3591
# changes made by the failing statement.
3592
SET @@session.sql_mode = '';
3594
DELETE FROM t1 WHERE f_charbig = 'was inserted';
3596
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
3598
# check special-1 success: 1
3599
UPDATE t1 SET f_charbig = '';
3601
# check special-2 success: 1
3602
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
3603
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3604
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
3605
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3606
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3607
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3608
'just inserted' FROM t0_template
3609
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3610
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
3612
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3613
f_charbig = 'updated by trigger'
3614
WHERE f_int1 = new.f_int1;
3616
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3617
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3618
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3620
# check trigger-1 success: 1
3622
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3623
f_int2 = CAST(f_char1 AS SIGNED INT),
3624
f_charbig = 'just inserted'
3625
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3627
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3628
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3629
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3630
'just inserted' FROM t0_template
3631
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3632
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
3634
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3635
f_charbig = 'updated by trigger'
3636
WHERE f_int1 = new.f_int1;
3638
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3639
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
3640
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3642
# check trigger-2 success: 1
3644
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3645
f_int2 = CAST(f_char1 AS SIGNED INT),
3646
f_charbig = 'just inserted'
3647
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3649
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3650
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3651
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3652
'just inserted' FROM t0_template
3653
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3654
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3656
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3657
f_charbig = 'updated by trigger'
3658
WHERE f_int1 = new.f_int1;
3660
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3661
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3663
# check trigger-3 success: 1
3665
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3666
f_int2 = CAST(f_char1 AS SIGNED INT),
3667
f_charbig = 'just inserted'
3668
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3670
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3671
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3672
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3673
'just inserted' FROM t0_template
3674
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3675
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
3677
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3678
f_charbig = 'updated by trigger'
3679
WHERE f_int1 = - old.f_int1;
3681
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3682
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3684
# check trigger-4 success: 1
3686
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3687
f_int2 = CAST(f_char1 AS SIGNED INT),
3688
f_charbig = 'just inserted'
3689
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3691
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3692
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3693
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3694
'just inserted' FROM t0_template
3695
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3696
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3698
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3699
f_charbig = 'updated by trigger'
3700
WHERE f_int1 = new.f_int1;
3702
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3703
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3705
# check trigger-5 success: 1
3707
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3708
f_int2 = CAST(f_char1 AS SIGNED INT),
3709
f_charbig = 'just inserted'
3710
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3712
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3713
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3714
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3715
'just inserted' FROM t0_template
3716
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3717
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
3719
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3720
f_charbig = 'updated by trigger'
3721
WHERE f_int1 = - old.f_int1;
3723
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
3724
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3726
# check trigger-6 success: 1
3728
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3729
f_int2 = CAST(f_char1 AS SIGNED INT),
3730
f_charbig = 'just inserted'
3731
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3733
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3734
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3735
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3736
'just inserted' FROM t0_template
3737
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3738
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
3740
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3741
f_charbig = 'updated by trigger'
3742
WHERE f_int1 = - old.f_int1;
3745
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3747
# check trigger-7 success: 1
3749
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3750
f_int2 = CAST(f_char1 AS SIGNED INT),
3751
f_charbig = 'just inserted'
3752
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3754
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3755
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
3756
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
3757
'just inserted' FROM t0_template
3758
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3759
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
3761
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
3762
f_charbig = 'updated by trigger'
3763
WHERE f_int1 = - old.f_int1;
3766
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
3768
# check trigger-8 success: 1
3770
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3771
f_int2 = CAST(f_char1 AS SIGNED INT),
3772
f_charbig = 'just inserted'
3773
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
3775
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3777
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
3778
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3780
SET new.f_int1 = old.f_int1 + @max_row,
3781
new.f_int2 = old.f_int2 - @max_row,
3782
new.f_charbig = '####updated per update trigger####';
3785
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3786
f_charbig = '####updated per update statement itself####';
3788
# check trigger-9 success: 1
3790
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3791
f_int2 = CAST(f_char1 AS SIGNED INT),
3792
f_charbig = CONCAT('===',f_char1,'===');
3793
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
3795
SET new.f_int1 = new.f_int1 + @max_row,
3796
new.f_int2 = new.f_int2 - @max_row,
3797
new.f_charbig = '####updated per update trigger####';
3800
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
3801
f_charbig = '####updated per update statement itself####';
3803
# check trigger-10 success: 1
3805
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
3806
f_int2 = CAST(f_char1 AS SIGNED INT),
3807
f_charbig = CONCAT('===',f_char1,'===');
3808
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3810
SET new.f_int1 = @my_max1 + @counter,
3811
new.f_int2 = @my_min2 - @counter,
3812
new.f_charbig = '####updated per insert trigger####';
3813
SET @counter = @counter + 1;
3816
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3817
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3818
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3819
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3820
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3824
# check trigger-11 success: 1
3826
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3827
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3828
AND f_charbig = '####updated per insert trigger####';
3829
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
3831
SET new.f_int1 = @my_max1 + @counter,
3832
new.f_int2 = @my_min2 - @counter,
3833
new.f_charbig = '####updated per insert trigger####';
3834
SET @counter = @counter + 1;
3837
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
3838
INSERT INTO t1 (f_char1, f_char2, f_charbig)
3839
SELECT CAST(f_int1 AS CHAR),
3840
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
3841
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
3845
# check trigger-12 success: 1
3847
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
3848
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
3849
AND f_charbig = '####updated per insert trigger####';
3851
Table Op Msg_type Msg_text
3852
test.t1 analyze note The storage engine for the table doesn't support analyze
3853
CHECK TABLE t1 EXTENDED;
3854
Table Op Msg_type Msg_text
3855
test.t1 check note The storage engine for the table doesn't support check
3856
CHECKSUM TABLE t1 EXTENDED;
3858
test.t1 <some_value>
3860
Table Op Msg_type Msg_text
3861
test.t1 optimize note The storage engine for the table doesn't support optimize
3862
# check layout success: 1
3863
REPAIR TABLE t1 EXTENDED;
3864
Table Op Msg_type Msg_text
3865
test.t1 repair note The storage engine for the table doesn't support repair
3866
# check layout success: 1
3869
# check TRUNCATE success: 1
3870
# check layout success: 1
3871
# End usability test (inc/partition_check.inc)
3873
# 6.2 Storage engine assignment after partition name + after
3875
# in partition part + in sub partition part
3881
f_charbig VARCHAR(1000)
3883
PARTITION BY RANGE(f_int1)
3884
SUBPARTITION BY HASH(f_int1)
3885
( PARTITION part1 VALUES LESS THAN (10) STORAGE ENGINE = 'MyISAM'
3886
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
3887
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'),
3888
PARTITION part2 VALUES LESS THAN (2147483646)
3889
(SUBPARTITION subpart21 STORAGE ENGINE = 'MyISAM',
3890
SUBPARTITION subpart22 STORAGE ENGINE = 'MyISAM')
3892
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
3893
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
3894
# Start usability test (inc/partition_check.inc)
3896
SHOW CREATE TABLE t1;
3898
t1 CREATE TABLE `t1` (
3899
`f_int1` int(11) DEFAULT NULL,
3900
`f_int2` int(11) DEFAULT NULL,
3901
`f_char1` char(20) DEFAULT NULL,
3902
`f_char2` char(20) DEFAULT NULL,
3903
`f_charbig` varchar(1000) DEFAULT NULL
3904
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
3907
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYD
3908
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYI
3909
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYD
3910
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYI
3911
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYD
3912
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYI
3913
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYD
3914
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYI
3915
$MYSQLTEST_VARDIR/master-data/test/t1.frm
3916
$MYSQLTEST_VARDIR/master-data/test/t1.par
3918
# check prerequisites-1 success: 1
3919
# check COUNT(*) success: 1
3920
# check MIN/MAX(f_int1) success: 1
3921
# check MIN/MAX(f_int2) success: 1
3922
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
3923
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
3924
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
3925
WHERE f_int1 IN (2,3);
3926
# check prerequisites-3 success: 1
3927
DELETE FROM t1 WHERE f_charbig = 'delete me';
3928
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
3929
# check read via f_int1 success: 1
3930
# check read via f_int2 success: 1
3932
# check multiple-1 success: 1
3933
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
3935
# check multiple-2 success: 1
3936
INSERT INTO t1 SELECT * FROM t0_template
3937
WHERE MOD(f_int1,3) = 0;
3939
# check multiple-3 success: 1
3940
UPDATE t1 SET f_int1 = f_int1 + @max_row
3941
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
3942
AND @max_row_div2 + @max_row_div4;
3944
# check multiple-4 success: 1
3946
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
3947
AND @max_row_div2 + @max_row_div4 + @max_row;
3949
# check multiple-5 success: 1
3950
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
3952
SET f_int1 = @cur_value , f_int2 = @cur_value,
3953
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3954
f_charbig = '#SINGLE#';
3956
# check single-1 success: 1
3957
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
3959
SET f_int1 = @cur_value , f_int2 = @cur_value,
3960
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
3961
f_charbig = '#SINGLE#';
3963
# check single-2 success: 1
3964
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
3965
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
3966
UPDATE t1 SET f_int1 = @cur_value2
3967
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
3969
# check single-3 success: 1
3970
SET @cur_value1= -1;
3971
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
3972
UPDATE t1 SET f_int1 = @cur_value1
3973
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
3975
# check single-4 success: 1
3976
SELECT MAX(f_int1) INTO @cur_value FROM t1;
3977
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
3979
# check single-5 success: 1
3980
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
3982
# check single-6 success: 1
3983
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
3984
ERROR HY000: Table has no partition for value 2147483647
3985
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
3986
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
3987
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
3988
f_charbig = '#NULL#';
3990
SET f_int1 = NULL , f_int2 = -@max_row,
3991
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
3992
f_charbig = '#NULL#';
3993
# check null success: 1
3995
# check null-1 success: 1
3996
UPDATE t1 SET f_int1 = -@max_row
3997
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
3998
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4000
# check null-2 success: 1
4001
UPDATE t1 SET f_int1 = NULL
4002
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4003
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4005
# check null-3 success: 1
4007
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4008
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4010
# check null-4 success: 1
4012
WHERE f_int1 = 0 AND f_int2 = 0
4013
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
4014
AND f_charbig = '#NULL#';
4016
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4017
SELECT f_int1, f_int1, '', '', 'was inserted'
4018
FROM t0_template source_tab
4019
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4021
# check transactions-1 success: 1
4024
# check transactions-2 success: 1
4027
# check transactions-3 success: 1
4028
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4032
# check transactions-4 success: 1
4033
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4034
SELECT f_int1, f_int1, '', '', 'was inserted'
4035
FROM t0_template source_tab
4036
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4038
# check transactions-5 success: 1
4041
Warning 1196 Some non-transactional changed tables couldn't be rolled back
4043
# check transactions-6 success: 1
4044
# INFO: Storage engine used for t1 seems to be not transactional.
4047
# check transactions-7 success: 1
4048
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4050
SET @@session.sql_mode = 'traditional';
4051
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
4052
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4053
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
4054
'', '', 'was inserted' FROM t0_template
4055
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4056
ERROR 22012: Division by 0
4059
# check transactions-8 success: 1
4060
# INFO: Storage engine used for t1 seems to be unable to revert
4061
# changes made by the failing statement.
4062
SET @@session.sql_mode = '';
4064
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4066
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4068
# check special-1 success: 1
4069
UPDATE t1 SET f_charbig = '';
4071
# check special-2 success: 1
4072
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4073
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4074
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4075
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4076
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4077
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4078
'just inserted' FROM t0_template
4079
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4080
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
4082
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4083
f_charbig = 'updated by trigger'
4084
WHERE f_int1 = new.f_int1;
4086
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4087
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4088
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4090
# check trigger-1 success: 1
4092
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4093
f_int2 = CAST(f_char1 AS SIGNED INT),
4094
f_charbig = 'just inserted'
4095
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4097
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4098
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4099
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4100
'just inserted' FROM t0_template
4101
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4102
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
4104
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4105
f_charbig = 'updated by trigger'
4106
WHERE f_int1 = new.f_int1;
4108
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4109
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4110
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4112
# check trigger-2 success: 1
4114
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4115
f_int2 = CAST(f_char1 AS SIGNED INT),
4116
f_charbig = 'just inserted'
4117
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4119
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4120
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4121
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4122
'just inserted' FROM t0_template
4123
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4124
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4126
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4127
f_charbig = 'updated by trigger'
4128
WHERE f_int1 = new.f_int1;
4130
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4131
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4133
# check trigger-3 success: 1
4135
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4136
f_int2 = CAST(f_char1 AS SIGNED INT),
4137
f_charbig = 'just inserted'
4138
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4140
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4141
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4142
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4143
'just inserted' FROM t0_template
4144
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4145
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4147
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4148
f_charbig = 'updated by trigger'
4149
WHERE f_int1 = - old.f_int1;
4151
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4152
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4154
# check trigger-4 success: 1
4156
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4157
f_int2 = CAST(f_char1 AS SIGNED INT),
4158
f_charbig = 'just inserted'
4159
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4161
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4162
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4163
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4164
'just inserted' FROM t0_template
4165
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4166
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4168
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4169
f_charbig = 'updated by trigger'
4170
WHERE f_int1 = new.f_int1;
4172
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4173
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4175
# check trigger-5 success: 1
4177
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4178
f_int2 = CAST(f_char1 AS SIGNED INT),
4179
f_charbig = 'just inserted'
4180
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4182
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4183
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4184
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4185
'just inserted' FROM t0_template
4186
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4187
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4189
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4190
f_charbig = 'updated by trigger'
4191
WHERE f_int1 = - old.f_int1;
4193
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4194
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4196
# check trigger-6 success: 1
4198
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4199
f_int2 = CAST(f_char1 AS SIGNED INT),
4200
f_charbig = 'just inserted'
4201
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4203
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4204
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4205
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4206
'just inserted' FROM t0_template
4207
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4208
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
4210
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4211
f_charbig = 'updated by trigger'
4212
WHERE f_int1 = - old.f_int1;
4215
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4217
# check trigger-7 success: 1
4219
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4220
f_int2 = CAST(f_char1 AS SIGNED INT),
4221
f_charbig = 'just inserted'
4222
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4224
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4225
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4226
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4227
'just inserted' FROM t0_template
4228
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4229
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
4231
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4232
f_charbig = 'updated by trigger'
4233
WHERE f_int1 = - old.f_int1;
4236
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4238
# check trigger-8 success: 1
4240
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4241
f_int2 = CAST(f_char1 AS SIGNED INT),
4242
f_charbig = 'just inserted'
4243
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4245
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4247
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4248
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4250
SET new.f_int1 = old.f_int1 + @max_row,
4251
new.f_int2 = old.f_int2 - @max_row,
4252
new.f_charbig = '####updated per update trigger####';
4255
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4256
f_charbig = '####updated per update statement itself####';
4258
# check trigger-9 success: 1
4260
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4261
f_int2 = CAST(f_char1 AS SIGNED INT),
4262
f_charbig = CONCAT('===',f_char1,'===');
4263
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4265
SET new.f_int1 = new.f_int1 + @max_row,
4266
new.f_int2 = new.f_int2 - @max_row,
4267
new.f_charbig = '####updated per update trigger####';
4270
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4271
f_charbig = '####updated per update statement itself####';
4273
# check trigger-10 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 = CONCAT('===',f_char1,'===');
4278
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4280
SET new.f_int1 = @my_max1 + @counter,
4281
new.f_int2 = @my_min2 - @counter,
4282
new.f_charbig = '####updated per insert trigger####';
4283
SET @counter = @counter + 1;
4286
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4287
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4288
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4289
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4290
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4294
# check trigger-11 success: 1
4296
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4297
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4298
AND f_charbig = '####updated per insert trigger####';
4299
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4301
SET new.f_int1 = @my_max1 + @counter,
4302
new.f_int2 = @my_min2 - @counter,
4303
new.f_charbig = '####updated per insert trigger####';
4304
SET @counter = @counter + 1;
4307
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4308
INSERT INTO t1 (f_char1, f_char2, f_charbig)
4309
SELECT CAST(f_int1 AS CHAR),
4310
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4311
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4315
# check trigger-12 success: 1
4317
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4318
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4319
AND f_charbig = '####updated per insert trigger####';
4321
Table Op Msg_type Msg_text
4322
test.t1 analyze note The storage engine for the table doesn't support analyze
4323
CHECK TABLE t1 EXTENDED;
4324
Table Op Msg_type Msg_text
4325
test.t1 check note The storage engine for the table doesn't support check
4326
CHECKSUM TABLE t1 EXTENDED;
4328
test.t1 <some_value>
4330
Table Op Msg_type Msg_text
4331
test.t1 optimize note The storage engine for the table doesn't support optimize
4332
# check layout success: 1
4333
REPAIR TABLE t1 EXTENDED;
4334
Table Op Msg_type Msg_text
4335
test.t1 repair note The storage engine for the table doesn't support repair
4336
# check layout success: 1
4339
# check TRUNCATE success: 1
4340
# check layout success: 1
4341
# End usability test (inc/partition_check.inc)
4343
#------------------------------------------------------------------------
4344
# 6 Session default engine differs from engine used within create table
4345
#------------------------------------------------------------------------
4346
SET SESSION storage_engine='MEMORY';
4352
f_charbig VARCHAR(1000)
4354
PARTITION BY HASH(f_int1) ( PARTITION part1 ENGINE = 'MyISAM');
4355
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4356
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
4357
# Start usability test (inc/partition_check.inc)
4359
SHOW CREATE TABLE t1;
4361
t1 CREATE TABLE `t1` (
4362
`f_int1` int(11) DEFAULT NULL,
4363
`f_int2` int(11) DEFAULT NULL,
4364
`f_char1` char(20) DEFAULT NULL,
4365
`f_char2` char(20) DEFAULT NULL,
4366
`f_charbig` varchar(1000) DEFAULT NULL
4367
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = MyISAM) */
4370
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYD
4371
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYI
4372
$MYSQLTEST_VARDIR/master-data/test/t1.frm
4373
$MYSQLTEST_VARDIR/master-data/test/t1.par
4375
# check prerequisites-1 success: 1
4376
# check COUNT(*) success: 1
4377
# check MIN/MAX(f_int1) success: 1
4378
# check MIN/MAX(f_int2) success: 1
4379
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4380
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4381
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
4382
WHERE f_int1 IN (2,3);
4383
# check prerequisites-3 success: 1
4384
DELETE FROM t1 WHERE f_charbig = 'delete me';
4385
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
4386
# check read via f_int1 success: 1
4387
# check read via f_int2 success: 1
4389
# check multiple-1 success: 1
4390
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
4392
# check multiple-2 success: 1
4393
INSERT INTO t1 SELECT * FROM t0_template
4394
WHERE MOD(f_int1,3) = 0;
4396
# check multiple-3 success: 1
4397
UPDATE t1 SET f_int1 = f_int1 + @max_row
4398
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
4399
AND @max_row_div2 + @max_row_div4;
4401
# check multiple-4 success: 1
4403
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
4404
AND @max_row_div2 + @max_row_div4 + @max_row;
4406
# check multiple-5 success: 1
4407
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
4409
SET f_int1 = @cur_value , f_int2 = @cur_value,
4410
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4411
f_charbig = '#SINGLE#';
4413
# check single-1 success: 1
4414
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
4416
SET f_int1 = @cur_value , f_int2 = @cur_value,
4417
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4418
f_charbig = '#SINGLE#';
4420
# check single-2 success: 1
4421
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
4422
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
4423
UPDATE t1 SET f_int1 = @cur_value2
4424
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
4426
# check single-3 success: 1
4427
SET @cur_value1= -1;
4428
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
4429
UPDATE t1 SET f_int1 = @cur_value1
4430
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
4432
# check single-4 success: 1
4433
SELECT MAX(f_int1) INTO @cur_value FROM t1;
4434
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
4436
# check single-5 success: 1
4437
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
4439
# check single-6 success: 1
4440
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
4442
# check single-7 success: 1
4443
DELETE FROM t1 WHERE f_charbig = '#2147483647##';
4444
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
4445
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
4446
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
4447
f_charbig = '#NULL#';
4449
SET f_int1 = NULL , f_int2 = -@max_row,
4450
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
4451
f_charbig = '#NULL#';
4452
# check null success: 1
4454
# check null-1 success: 1
4455
UPDATE t1 SET f_int1 = -@max_row
4456
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4457
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4459
# check null-2 success: 1
4460
UPDATE t1 SET f_int1 = NULL
4461
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4462
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4464
# check null-3 success: 1
4466
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4467
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4469
# check null-4 success: 1
4471
WHERE f_int1 = 0 AND f_int2 = 0
4472
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
4473
AND f_charbig = '#NULL#';
4475
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4476
SELECT f_int1, f_int1, '', '', 'was inserted'
4477
FROM t0_template source_tab
4478
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4480
# check transactions-1 success: 1
4483
# check transactions-2 success: 1
4486
# check transactions-3 success: 1
4487
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4491
# check transactions-4 success: 1
4492
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4493
SELECT f_int1, f_int1, '', '', 'was inserted'
4494
FROM t0_template source_tab
4495
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4497
# check transactions-5 success: 1
4500
Warning 1196 Some non-transactional changed tables couldn't be rolled back
4502
# check transactions-6 success: 1
4503
# INFO: Storage engine used for t1 seems to be not transactional.
4506
# check transactions-7 success: 1
4507
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4509
SET @@session.sql_mode = 'traditional';
4510
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
4511
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4512
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
4513
'', '', 'was inserted' FROM t0_template
4514
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4515
ERROR 22012: Division by 0
4518
# check transactions-8 success: 1
4519
# INFO: Storage engine used for t1 seems to be unable to revert
4520
# changes made by the failing statement.
4521
SET @@session.sql_mode = '';
4523
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4525
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4527
# check special-1 success: 1
4528
UPDATE t1 SET f_charbig = '';
4530
# check special-2 success: 1
4531
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4532
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4533
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4534
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4535
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4536
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4537
'just inserted' FROM t0_template
4538
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4539
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
4541
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4542
f_charbig = 'updated by trigger'
4543
WHERE f_int1 = new.f_int1;
4545
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4546
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4547
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4549
# check trigger-1 success: 1
4551
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4552
f_int2 = CAST(f_char1 AS SIGNED INT),
4553
f_charbig = 'just inserted'
4554
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4556
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4557
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4558
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4559
'just inserted' FROM t0_template
4560
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4561
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
4563
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4564
f_charbig = 'updated by trigger'
4565
WHERE f_int1 = new.f_int1;
4567
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4568
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
4569
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4571
# check trigger-2 success: 1
4573
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4574
f_int2 = CAST(f_char1 AS SIGNED INT),
4575
f_charbig = 'just inserted'
4576
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4578
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4579
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4580
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4581
'just inserted' FROM t0_template
4582
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4583
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4585
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4586
f_charbig = 'updated by trigger'
4587
WHERE f_int1 = new.f_int1;
4589
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4590
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4592
# check trigger-3 success: 1
4594
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4595
f_int2 = CAST(f_char1 AS SIGNED INT),
4596
f_charbig = 'just inserted'
4597
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4599
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4600
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4601
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4602
'just inserted' FROM t0_template
4603
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4604
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
4606
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4607
f_charbig = 'updated by trigger'
4608
WHERE f_int1 = - old.f_int1;
4610
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4611
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4613
# check trigger-4 success: 1
4615
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4616
f_int2 = CAST(f_char1 AS SIGNED INT),
4617
f_charbig = 'just inserted'
4618
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4620
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4621
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4622
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4623
'just inserted' FROM t0_template
4624
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4625
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4627
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4628
f_charbig = 'updated by trigger'
4629
WHERE f_int1 = new.f_int1;
4631
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4632
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4634
# check trigger-5 success: 1
4636
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4637
f_int2 = CAST(f_char1 AS SIGNED INT),
4638
f_charbig = 'just inserted'
4639
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4641
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4642
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4643
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4644
'just inserted' FROM t0_template
4645
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4646
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
4648
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4649
f_charbig = 'updated by trigger'
4650
WHERE f_int1 = - old.f_int1;
4652
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
4653
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4655
# check trigger-6 success: 1
4657
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4658
f_int2 = CAST(f_char1 AS SIGNED INT),
4659
f_charbig = 'just inserted'
4660
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4662
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4663
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4664
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4665
'just inserted' FROM t0_template
4666
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4667
CREATE TRIGGER trg_1 BEFORE DELETE ON t0_aux FOR EACH ROW
4669
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4670
f_charbig = 'updated by trigger'
4671
WHERE f_int1 = - old.f_int1;
4674
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4676
# check trigger-7 success: 1
4678
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4679
f_int2 = CAST(f_char1 AS SIGNED INT),
4680
f_charbig = 'just inserted'
4681
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4683
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4684
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4685
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4686
'just inserted' FROM t0_template
4687
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4688
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
4690
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
4691
f_charbig = 'updated by trigger'
4692
WHERE f_int1 = - old.f_int1;
4695
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
4697
# check trigger-8 success: 1
4699
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4700
f_int2 = CAST(f_char1 AS SIGNED INT),
4701
f_charbig = 'just inserted'
4702
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
4704
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4706
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4707
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4709
SET new.f_int1 = old.f_int1 + @max_row,
4710
new.f_int2 = old.f_int2 - @max_row,
4711
new.f_charbig = '####updated per update trigger####';
4714
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4715
f_charbig = '####updated per update statement itself####';
4717
# check trigger-9 success: 1
4719
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4720
f_int2 = CAST(f_char1 AS SIGNED INT),
4721
f_charbig = CONCAT('===',f_char1,'===');
4722
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
4724
SET new.f_int1 = new.f_int1 + @max_row,
4725
new.f_int2 = new.f_int2 - @max_row,
4726
new.f_charbig = '####updated per update trigger####';
4729
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
4730
f_charbig = '####updated per update statement itself####';
4732
# check trigger-10 success: 1
4734
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
4735
f_int2 = CAST(f_char1 AS SIGNED INT),
4736
f_charbig = CONCAT('===',f_char1,'===');
4737
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4739
SET new.f_int1 = @my_max1 + @counter,
4740
new.f_int2 = @my_min2 - @counter,
4741
new.f_charbig = '####updated per insert trigger####';
4742
SET @counter = @counter + 1;
4745
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4746
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4747
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4748
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4749
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4753
# check trigger-11 success: 1
4755
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4756
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4757
AND f_charbig = '####updated per insert trigger####';
4758
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
4760
SET new.f_int1 = @my_max1 + @counter,
4761
new.f_int2 = @my_min2 - @counter,
4762
new.f_charbig = '####updated per insert trigger####';
4763
SET @counter = @counter + 1;
4766
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
4767
INSERT INTO t1 (f_char1, f_char2, f_charbig)
4768
SELECT CAST(f_int1 AS CHAR),
4769
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
4770
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
4774
# check trigger-12 success: 1
4776
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
4777
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
4778
AND f_charbig = '####updated per insert trigger####';
4780
Table Op Msg_type Msg_text
4781
test.t1 analyze note The storage engine for the table doesn't support analyze
4782
CHECK TABLE t1 EXTENDED;
4783
Table Op Msg_type Msg_text
4784
test.t1 check note The storage engine for the table doesn't support check
4785
CHECKSUM TABLE t1 EXTENDED;
4787
test.t1 <some_value>
4789
Table Op Msg_type Msg_text
4790
test.t1 optimize note The storage engine for the table doesn't support optimize
4791
# check layout success: 1
4792
REPAIR TABLE t1 EXTENDED;
4793
Table Op Msg_type Msg_text
4794
test.t1 repair note The storage engine for the table doesn't support repair
4795
# check layout success: 1
4798
# check TRUNCATE success: 1
4799
# check layout success: 1
4800
# End usability test (inc/partition_check.inc)
4807
f_charbig VARCHAR(1000)
4809
PARTITION BY RANGE(f_int1)
4810
SUBPARTITION BY HASH(f_int1)
4811
( PARTITION part1 VALUES LESS THAN (1000)
4812
(SUBPARTITION subpart11 STORAGE ENGINE = 'MyISAM',
4813
SUBPARTITION subpart12 STORAGE ENGINE = 'MyISAM'));
4814
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4815
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
4816
# Start usability test (inc/partition_check.inc)
4818
SHOW CREATE TABLE t1;
4820
t1 CREATE TABLE `t1` (
4821
`f_int1` int(11) DEFAULT NULL,
4822
`f_int2` int(11) DEFAULT NULL,
4823
`f_char1` char(20) DEFAULT NULL,
4824
`f_char2` char(20) DEFAULT NULL,
4825
`f_charbig` varchar(1000) DEFAULT NULL
4826
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM)) */
4829
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYD
4830
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYI
4831
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYD
4832
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYI
4833
$MYSQLTEST_VARDIR/master-data/test/t1.frm
4834
$MYSQLTEST_VARDIR/master-data/test/t1.par
4836
# check prerequisites-1 success: 1
4837
# check COUNT(*) success: 1
4838
# check MIN/MAX(f_int1) success: 1
4839
# check MIN/MAX(f_int2) success: 1
4840
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4841
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
4842
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
4843
WHERE f_int1 IN (2,3);
4844
# check prerequisites-3 success: 1
4845
DELETE FROM t1 WHERE f_charbig = 'delete me';
4846
# INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
4847
# check read via f_int1 success: 1
4848
# check read via f_int2 success: 1
4850
# check multiple-1 success: 1
4851
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
4853
# check multiple-2 success: 1
4854
INSERT INTO t1 SELECT * FROM t0_template
4855
WHERE MOD(f_int1,3) = 0;
4857
# check multiple-3 success: 1
4858
UPDATE t1 SET f_int1 = f_int1 + @max_row
4859
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
4860
AND @max_row_div2 + @max_row_div4;
4862
# check multiple-4 success: 1
4864
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
4865
AND @max_row_div2 + @max_row_div4 + @max_row;
4867
# check multiple-5 success: 1
4868
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
4870
SET f_int1 = @cur_value , f_int2 = @cur_value,
4871
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4872
f_charbig = '#SINGLE#';
4874
# check single-1 success: 1
4875
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
4877
SET f_int1 = @cur_value , f_int2 = @cur_value,
4878
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
4879
f_charbig = '#SINGLE#';
4881
# check single-2 success: 1
4882
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
4883
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
4884
UPDATE t1 SET f_int1 = @cur_value2
4885
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
4887
# check single-3 success: 1
4888
SET @cur_value1= -1;
4889
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
4890
UPDATE t1 SET f_int1 = @cur_value1
4891
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
4893
# check single-4 success: 1
4894
SELECT MAX(f_int1) INTO @cur_value FROM t1;
4895
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
4897
# check single-5 success: 1
4898
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
4900
# check single-6 success: 1
4901
INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#2147483647##';
4902
ERROR HY000: Table has no partition for value 2147483647
4903
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
4904
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
4905
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
4906
f_charbig = '#NULL#';
4908
SET f_int1 = NULL , f_int2 = -@max_row,
4909
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
4910
f_charbig = '#NULL#';
4911
# check null success: 1
4913
# check null-1 success: 1
4914
UPDATE t1 SET f_int1 = -@max_row
4915
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4916
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4918
# check null-2 success: 1
4919
UPDATE t1 SET f_int1 = NULL
4920
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4921
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4923
# check null-3 success: 1
4925
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
4926
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
4928
# check null-4 success: 1
4930
WHERE f_int1 = 0 AND f_int2 = 0
4931
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
4932
AND f_charbig = '#NULL#';
4934
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4935
SELECT f_int1, f_int1, '', '', 'was inserted'
4936
FROM t0_template source_tab
4937
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4939
# check transactions-1 success: 1
4942
# check transactions-2 success: 1
4945
# check transactions-3 success: 1
4946
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4950
# check transactions-4 success: 1
4951
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4952
SELECT f_int1, f_int1, '', '', 'was inserted'
4953
FROM t0_template source_tab
4954
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4956
# check transactions-5 success: 1
4959
Warning 1196 Some non-transactional changed tables couldn't be rolled back
4961
# check transactions-6 success: 1
4962
# INFO: Storage engine used for t1 seems to be not transactional.
4965
# check transactions-7 success: 1
4966
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4968
SET @@session.sql_mode = 'traditional';
4969
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
4970
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
4971
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
4972
'', '', 'was inserted' FROM t0_template
4973
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
4974
ERROR 22012: Division by 0
4977
# check transactions-8 success: 1
4978
# INFO: Storage engine used for t1 seems to be unable to revert
4979
# changes made by the failing statement.
4980
SET @@session.sql_mode = '';
4982
DELETE FROM t1 WHERE f_charbig = 'was inserted';
4984
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
4986
# check special-1 success: 1
4987
UPDATE t1 SET f_charbig = '';
4989
# check special-2 success: 1
4990
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
4991
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
4992
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
4993
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4994
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
4995
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
4996
'just inserted' FROM t0_template
4997
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
4998
CREATE TRIGGER trg_1 BEFORE INSERT ON t0_aux FOR EACH ROW
5000
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5001
f_charbig = 'updated by trigger'
5002
WHERE f_int1 = new.f_int1;
5004
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5005
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
5006
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5008
# check trigger-1 success: 1
5010
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5011
f_int2 = CAST(f_char1 AS SIGNED INT),
5012
f_charbig = 'just inserted'
5013
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5015
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5016
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5017
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5018
'just inserted' FROM t0_template
5019
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5020
CREATE TRIGGER trg_1 AFTER INSERT ON t0_aux FOR EACH ROW
5022
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5023
f_charbig = 'updated by trigger'
5024
WHERE f_int1 = new.f_int1;
5026
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5027
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
5028
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5030
# check trigger-2 success: 1
5032
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5033
f_int2 = CAST(f_char1 AS SIGNED INT),
5034
f_charbig = 'just inserted'
5035
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5037
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5038
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5039
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5040
'just inserted' FROM t0_template
5041
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5042
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
5044
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5045
f_charbig = 'updated by trigger'
5046
WHERE f_int1 = new.f_int1;
5048
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5049
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5051
# check trigger-3 success: 1
5053
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5054
f_int2 = CAST(f_char1 AS SIGNED INT),
5055
f_charbig = 'just inserted'
5056
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5058
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5059
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5060
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5061
'just inserted' FROM t0_template
5062
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5063
CREATE TRIGGER trg_1 BEFORE UPDATE ON t0_aux FOR EACH ROW
5065
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5066
f_charbig = 'updated by trigger'
5067
WHERE f_int1 = - old.f_int1;
5069
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5070
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5072
# check trigger-4 success: 1
5074
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5075
f_int2 = CAST(f_char1 AS SIGNED INT),
5076
f_charbig = 'just inserted'
5077
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
5079
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5080
INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
5081
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
5082
'just inserted' FROM t0_template
5083
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5084
CREATE TRIGGER trg_1 AFTER UPDATE ON t0_aux FOR EACH ROW
5086
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
5087
f_charbig = 'updated by trigger'
5088
WHERE f_int1 = new.f_int1;
5090
UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
5091
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5093
# check trigger-5 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 AFTER 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 = - old.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-6 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 DELETE 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;
5133
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5135
# check trigger-7 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 DELETE 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 = - old.f_int1;
5154
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
5156
# check trigger-8 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;
5165
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
5166
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
5168
SET new.f_int1 = old.f_int1 + @max_row,
5169
new.f_int2 = old.f_int2 - @max_row,
5170
new.f_charbig = '####updated per update trigger####';
5173
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
5174
f_charbig = '####updated per update statement itself####';
5176
# check trigger-9 success: 1
5178
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5179
f_int2 = CAST(f_char1 AS SIGNED INT),
5180
f_charbig = CONCAT('===',f_char1,'===');
5181
CREATE TRIGGER trg_2 BEFORE UPDATE ON t1 FOR EACH ROW
5183
SET new.f_int1 = new.f_int1 + @max_row,
5184
new.f_int2 = new.f_int2 - @max_row,
5185
new.f_charbig = '####updated per update trigger####';
5188
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
5189
f_charbig = '####updated per update statement itself####';
5191
# check trigger-10 success: 1
5193
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
5194
f_int2 = CAST(f_char1 AS SIGNED INT),
5195
f_charbig = CONCAT('===',f_char1,'===');
5196
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
5198
SET new.f_int1 = @my_max1 + @counter,
5199
new.f_int2 = @my_min2 - @counter,
5200
new.f_charbig = '####updated per insert trigger####';
5201
SET @counter = @counter + 1;
5204
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
5205
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
5206
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
5207
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
5208
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
5212
# check trigger-11 success: 1
5214
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
5215
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
5216
AND f_charbig = '####updated per insert trigger####';
5217
CREATE TRIGGER trg_3 BEFORE INSERT ON t1 FOR EACH ROW
5219
SET new.f_int1 = @my_max1 + @counter,
5220
new.f_int2 = @my_min2 - @counter,
5221
new.f_charbig = '####updated per insert trigger####';
5222
SET @counter = @counter + 1;
5225
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
5226
INSERT INTO t1 (f_char1, f_char2, f_charbig)
5227
SELECT CAST(f_int1 AS CHAR),
5228
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
5229
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
5233
# check trigger-12 success: 1
5235
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
5236
AND f_int2 <> CAST(f_char1 AS SIGNED INT)
5237
AND f_charbig = '####updated per insert trigger####';
5239
Table Op Msg_type Msg_text
5240
test.t1 analyze note The storage engine for the table doesn't support analyze
5241
CHECK TABLE t1 EXTENDED;
5242
Table Op Msg_type Msg_text
5243
test.t1 check note The storage engine for the table doesn't support check
5244
CHECKSUM TABLE t1 EXTENDED;
5246
test.t1 <some_value>
5248
Table Op Msg_type Msg_text
5249
test.t1 optimize note The storage engine for the table doesn't support optimize
5250
# check layout success: 1
5251
REPAIR TABLE t1 EXTENDED;
5252
Table Op Msg_type Msg_text
5253
test.t1 repair note The storage engine for the table doesn't support repair
5254
# check layout success: 1
5257
# check TRUNCATE success: 1
5258
# check layout success: 1
5259
# End usability test (inc/partition_check.inc)
5261
SET SESSION storage_engine='MyISAM';
5262
DROP VIEW IF EXISTS v1;
5263
DROP TABLE IF EXISTS t1;
5264
DROP TABLE IF EXISTS t0_aux;
5265
DROP TABLE IF EXISTS t0_definition;
5266
DROP TABLE IF EXISTS t0_template;
added
removed
mysql-test/suite/parts/r/partition_engine_myisam.result
