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