Debian Codegraph @

mysql-test/suite/parts/r/partition_engine_innodb.result

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