Debian Codegraph @

mysql-test/suite/parts/inc/partition_check.inc

raw
1 ################################################################################
2 # inc/partition_check.inc #
3 # #
4 # Purpose: #
5 # Do some basic usability checks on table t1. #
6 # This routine is only useful for the partition_<feature>_<engine> tests. #
7 # #
8 # Some Notes: #
9 # It is intended that in many testcases (statements) more than one partition #
10 # or subpartition is affected. #
11 # Without analysis of the partitioning function used during CREATE TABLE #
12 # we cannot be 100% sure that this goal is reached. #
13 # But statements affecting many rows give a good probability that this #
14 # appears. #
15 # #
16 # It is expected that the table to be checked contains at the beginning #
17 # of this script records following the scheme #
18 # f_int1 f_int2 f_char1 f_char2 f_charbig #
19 # 1 1 '1' '1' '###1###' #
20 # 2 2 '2' '1' '###2###' #
21 # ... ... ... ... ... #
22 # x x 'x' 'x' '###x###' #
23 # x = @max_row #
24 # #
25 # The table content must be equal to the content of the table t0_template. #
26 # Attention: Please be careful when modiying the data. #
27 # Records can be deleted or inserted, but the content of the #
28 # records after a test/testsequence should follow this scheme. #
29 # #
30 # All checks of preceding statements via Select are so written, #
31 # that they deliver a #
32 # # check <n> success: 1 #
33 # when everything is like expected. #
34 # - f_charbig is typically used for showing if something was changed. #
35 # This is useful for cleanups. #
36 # #
37 #------------------------------------------------------------------------------#
38 # Original Author: mleich #
39 # Original Date: 2006-03-05 #
40 # Change Author: mleich #
41 # Change Date: 2007-10-08 #
42 # Change: Around fix for #
43 # Bug#31243 Test "partition_basic_myisam" truncates path names#
44 # Adjustments of expected error codes: #
45 # ER_NO_PARTITION_FOR_GIVEN_VALUE is now 1525 #
46 # ER_SAME_NAME_PARTITION is now 1516 #
47 ################################################################################
48
49
50 --echo # Start usability test (inc/partition_check.inc)
51 # Print the CREATE TABLE STATEMENT and store the current layout of the table
52 --source suite/parts/inc/partition_layout_check1.inc
53
54
55 #-------------------------------------------------------------------------------
56 ## 1. Check the prerequisites for the following tests
57 # (sideeffect some SELECT functionality is also tested)
58 # Determine if we have PRIMARY KEYs or UNIQUE INDEXes
59 ## 1.1 Check if the content of the records is like expected
60 # Sideeffect: mass SELECT, all records/partitions/subpartitions have to be
61 # read, because at least f_charbig is not part of any
62 # INDEX/PRIMARY KEY
63 if ($no_debug)
64 {
65 --disable_query_log
66 }
67 --disable_cursor_protocol
68 let $my_stmt= SELECT COUNT(*) <> 0 INTO @aux FROM t1
69 WHERE f_int1 <> f_int2 OR f_char1 <> CAST(f_int1 AS CHAR) OR f_char1 <> f_char2
70 OR f_charbig <> CONCAT('===',f_char1,'===')
71 OR f_int1 IS NULL OR f_int2 IS NULL OR f_char1 IS NULL OR f_char2 IS NULL
72 OR f_charbig IS NULL;
73 eval $my_stmt;
74 let $run= `SELECT @aux`;
75 --enable_query_log
76 if ($run)
77 {
78 --echo # Prerequisites for following tests not fullfilled.
79 --echo # The content of the table t1 is unexpected
80 eval $my_stmt;
81 SELECT @aux;
82 --echo # Sorry, have to abort.
83 exit;
84 }
85 # Give a success message like in the other following tests
86 --echo # check prerequisites-1 success: 1
87 #-------------------------------------------------------------------------------
88 ## 1.2 Check if the number of records and the maximum and minimum values are
89 # like expected
90 # Sideeffect: Check
91 # - COUNT(*)
92 # - MIN/MAX on all columns possibly used in part. function
93 # The optimizer might decide to run on INDEX only, if available.
94 #
95 ## 1.2.1 Check COUNT(*)
96 if ($no_debug)
97 {
98 --disable_query_log
99 }
100 let $my_stmt= SELECT COUNT(*) <> @max_row INTO @aux FROM t1;
101 let $run= `SELECT @aux`;
102 --enable_query_log
103 if ($run)
104 {
105 --echo # Prerequisites for following tests not fullfilled.
106 --echo # The content of the table t1 is unexpected
107 eval $my_stmt;
108 SELECT @aux;
109 --echo # Sorry, have to abort.
110 exit;
111 }
112 # Give a success message like in the other following tests
113 --echo # check COUNT(*) success: 1
114 ## 1.2.2 Check MAX(f_int1),MIN(f_int1)
115 if ($no_debug)
116 {
117 --disable_query_log
118 }
119 let $my_stmt= SELECT MIN(f_int1) <> 1 AND MAX(f_int1) <> @max_row INTO @aux
120 FROM t1;
121 let $run= `SELECT @aux`;
122 --enable_query_log
123 if ($run)
124 {
125 --echo # Prerequisites for following tests not fullfilled.
126 --echo # The content of the table t1 is unexpected
127 eval $my_stmt;
128 SELECT @aux;
129 --echo # Sorry, have to abort.
130 exit;
131 }
132 # Give a success message like in the other following tests
133 --echo # check MIN/MAX(f_int1) success: 1
134 ## 1.2.3 Check MAX(f_int2),MIN(f_int2)
135 if ($no_debug)
136 {
137 --disable_query_log
138 }
139 let $my_stmt= SELECT MIN(f_int2) <> 1 AND MAX(f_int2) <> @max_row INTO @aux
140 FROM t1;
141 let $run= `SELECT @aux`;
142 --enable_query_log
143 if ($run)
144 {
145 --echo # Prerequisites for following tests not fullfilled.
146 --echo # The content of the table t1 is unexpected
147 eval $my_stmt;
148 SELECT @aux;
149 --echo # Sorry, have to abort.
150 exit;
151 }
152 --enable_cursor_protocol
153 # Give a success message like in the other following tests
154 --echo # check MIN/MAX(f_int2) success: 1
155
156 #-------------------------------------------------------------------------------
157 ## 1.3 Check, if f_int1 and/or f_char2 and/or (f_char1,f_char2) is UNIQUE
158 # by testing if any DUPLICATE KEY might appear
159 # Note: INFORMATION_SCHEMA SELECTs could be also used, but testing the
160 # table via INSERT and SELECT is better because is stresses the
161 # partitioning mechanism.
162 # Sideeffect: Attempt to INSERT one record
163 # DUPLICATE KEY will appear if we have UNIQUE columns
164 # ER_DUP_KEY, ER_DUP_ENTRY
165 --disable_abort_on_error
166 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
167 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
168 CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
169 WHERE f_int1 IN (2,3);
170 --enable_abort_on_error
171 if ($no_debug)
172 {
173 --disable_query_log
174 }
175 eval SET @my_errno = $mysql_errno;
176 let $run_delete= `SELECT @my_errno = 0`;
177 let $any_unique= `SELECT @my_errno IN ($ER_DUP_KEY,$ER_DUP_ENTRY)`;
178 # DEBUG eval SELECT $run_delete AS run_delete, $any_unique AS any_unique,
179 # @my_errno AS sql_errno;
180 if (`SELECT @my_errno NOT IN (0,$ER_DUP_KEY,$ER_DUP_ENTRY)`)
181 {
182 --echo # The last command got an unexpected error response.
183 --echo # Expected/handled SQL codes are 0,$ER_DUP_KEY,$ER_DUP_ENTRY
184 SELECT '# SQL code we got was: ' AS "", @my_errno AS "";
185 --echo # Sorry, have to abort.
186 exit;
187 --echo
188 }
189 # Give a success message like in the other following tests
190 --echo # check prerequisites-3 success: 1
191 --enable_query_log
192 # DEBUG eval SELECT $run_delete AS run_delete, $any_unique AS any_unique;
193 if ($run_delete)
194 {
195 # INSERT was successful -> DELETE this new record
196 DELETE FROM t1 WHERE f_charbig = 'delete me';
197 --echo # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
198 }
199 if ($any_unique)
200 {
201 --echo # INFO: f_int1 AND/OR f_int2 AND/OR (f_int1,f_int2) is UNIQUE
202
203 ## 1.3.1 Check, if f_int1 is UNIQUE
204 # Sideeffect: Attempt to INSERT one record
205 # DUPLICATE KEY will appear if we have UNIQUE columns
206 # ER_DUP_KEY, ER_DUP_ENTRY
207 --disable_abort_on_error
208 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
209 SELECT f_int1, 2 * @max_row + f_int1, CAST((2 * @max_row + f_int1) AS CHAR),
210 CAST((2 * @max_row + f_int1) AS CHAR), 'delete me' FROM t0_template
211 WHERE f_int1 IN (2,3);
212 --enable_abort_on_error
213 if ($no_debug)
214 {
215 --disable_query_log
216 }
217 eval SET @my_errno = $mysql_errno;
218 let $run_delete= `SELECT @my_errno = 0`;
219 let $f_int1_is_unique= `SELECT @my_errno IN ($ER_DUP_KEY,$ER_DUP_ENTRY)`;
220 # DEBUG eval SELECT $run_delete AS run_delete, $f_int1_is_unique AS any_unique,
221 # @my_errno AS sql_errno;
222 if (`SELECT @my_errno NOT IN (0,$ER_DUP_KEY,$ER_DUP_ENTRY)`)
223 {
224 --echo # The last command got an unexpected error response.
225 --echo # Expected/handled SQL codes are 0,$ER_DUP_KEY,$ER_DUP_ENTRY
226 SELECT '# SQL code we got was: ' AS "", @my_errno AS "";
227 --echo # Sorry, have to abort.
228 exit;
229 --echo
230 }
231 --enable_query_log
232 if ($f_int1_is_unique)
233 {
234 --echo # INFO: f_int1 is UNIQUE
235 }
236 if ($run_delete)
237 {
238 # INSERT was successful -> DELETE this new record
239 DELETE FROM t1 WHERE f_charbig = 'delete me';
240 }
241
242 ## 1.3.2 Check, if f_int2 is UNIQUE (get ER_DUP_KEY or ER_DUP_ENTRY
243 --disable_abort_on_error
244 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
245 SELECT 2 * @max_row + f_int1, f_int1, CAST((2 * @max_row + f_int1) AS CHAR),
246 CAST((2 * @max_row + f_int1) AS CHAR), 'delete me' FROM t0_template
247 WHERE f_int1 IN (2,3);
248 --enable_abort_on_error
249 if ($no_debug)
250 {
251 --disable_query_log
252 }
253 eval SET @my_errno = $mysql_errno;
254 let $run_delete= `SELECT @my_errno = 0`;
255 let $f_int1_is_unique= `SELECT @my_errno IN ($ER_DUP_KEY,$ER_DUP_ENTRY)`;
256 # DEBUG eval SELECT $run_delete AS run_delete, $f_int1_is_unique AS any_unique,
257 # @my_errno AS sql_errno;
258 if (`SELECT @my_errno NOT IN (0,$ER_DUP_KEY,$ER_DUP_ENTRY)`)
259 {
260 --echo # The last command got an unexpected error response.
261 --echo # Expected/handled SQL codes are 0,$ER_DUP_KEY,$ER_DUP_ENTRY
262 SELECT '# SQL code we got was: ' AS "", @my_errno AS "";
263 --echo # Sorry, have to abort.
264 exit;
265 --echo
266 }
267 if ($f_int2_is_unique)
268 {
269 --echo # INFO: f_int2 is UNIQUE
270 }
271 --enable_query_log
272 if ($run_delete)
273 {
274 # INSERT was successful -> DELETE this new record
275 DELETE FROM t1 WHERE f_charbig = 'delete me';
276 }
277 }
278
279
280 #-------------------------------------------------------------------------------
281 ## 2. Read the table row by row
282 # Note: There were crashes in history when reading a partitioned table
283 # PRIMARY KEY AND/OR UNIQUE INDEXes
284 ## 2.1 Read all existing and some not existing records of table
285 # per f_int1 used in partitioning function
286 let $col_to_check= f_int1;
287 --source suite/parts/inc/partition_check_read.inc
288 ## 2.2 Read all existing and some not existing records of table
289 # per f_int2 used in partitioning function
290 let $col_to_check= f_int2;
291 --source suite/parts/inc/partition_check_read.inc
292
293
294 #-------------------------------------------------------------------------------
295 # 3 Some operations with multiple records
296 # 3.1 Select on "full" table
297 if ($no_debug)
298 {
299 --disable_query_log
300 }
301 SELECT '# check multiple-1 success: ' AS "",COUNT(*) = @max_row AS "" FROM t1;
302 --enable_query_log
303 #
304 # 3.2 (mass) DELETE of @max_row_div3 records
305 DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
306 if ($no_debug)
307 {
308 --disable_query_log
309 }
310 SELECT '# check multiple-2 success: ' AS "",COUNT(*) = @max_row - @max_row_div3 AS "" FROM t1;
311 --enable_query_log
312 #
313 # 3.3 (mass) Insert of @max_row_div3 records
314 # (Insert the records deleted in 3.2)
315 INSERT INTO t1 SELECT * FROM t0_template
316 WHERE MOD(f_int1,3) = 0;
317 # Check of preceding statement via Select
318 if ($no_debug)
319 {
320 --disable_query_log
321 }
322 SELECT '# check multiple-3 success: ' AS "",
323 (COUNT(*) = @max_row) AND (MIN(f_int1) = 1) AND (MAX(f_int1) = @max_row) AS ""
324 FROM t1;
325 --enable_query_log
326 # DEBUG SELECT COUNT(*),MIN(f_int1),MAX(f_int1) FROM t1;
327 #
328 # 3.4 (mass) Update @max_row_div4 * 2 + 1 records
329 UPDATE t1 SET f_int1 = f_int1 + @max_row
330 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
331 AND @max_row_div2 + @max_row_div4;
332 # Check of preceding statement via Select
333 if ($no_debug)
334 {
335 --disable_query_log
336 }
337 SELECT '# check multiple-4 success: ' AS "",(COUNT(*) = @max_row) AND (MIN(f_int1) = 1) AND
338 (MAX(f_int1) = @max_row_div2 + @max_row_div4 + @max_row ) AS "" FROM t1;
339 --enable_query_log
340 # DEBUG SELECT COUNT(*),MIN(f_int1),MAX(f_int1) FROM t1;
341 #
342 # 3.5 (mass) Delete @max_row_div4 * 2 + 1 records
343 # (Delete the records updated in 3.4)
344 DELETE FROM t1
345 WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
346 AND @max_row_div2 + @max_row_div4 + @max_row;
347 # Check of preceding statement via Select
348 if ($no_debug)
349 {
350 --disable_query_log
351 }
352 SELECT '# check multiple-5 success: ' AS "",
353 (COUNT(*) = @max_row - @max_row_div4 - @max_row_div4 - 1)
354 AND (MIN(f_int1) = 1) AND (MAX(f_int1) = @max_row) AS "" FROM t1;
355 --enable_query_log
356 # DEBUG SELECT COUNT(*),MIN(f_int1),MAX(f_int1) FROM t1;
357
358 #-------------------------------------------------------------------------------
359 # Attention: After this section all modification on the table have to be reverted !
360 # Current content of t1 follows the rule:
361 # <value>,<value>,'<value>','<value>',===<value>===
362 # <value> contains all INTEGER values
363 # between 1 and @max_row_div2 - @max_row_div4 - 1
364 # and
365 # between @max_row_div2 + @max_row_div4 + 1 and @max_row
366 # With other words the values between @max_row_div2 - @max_row_div4
367 # and @max_row_div2 + @max_row_div4 are "missing".
368 #-------------------------------------------------------------------------------
369 # The following is only needed for tests of UNIQUE CONSTRAINTs.
370 if ($any_unique)
371 {
372 # Calculate the number of records, where we will try INSERT ..... or REPLACE
373 --disable_cursor_protocol
374 SELECT COUNT(*) INTO @try_count FROM t0_template
375 WHERE MOD(f_int1,3) = 0
376 AND f_int1 BETWEEN @max_row_div2 AND @max_row;
377 --enable_cursor_protocol
378 #
379 # Calculate the number of records, where we will get DUPLICATE KEY
380 # f_int1 is sufficient for calculating this, because 1.1
381 # checks, that f_int1 = f_int2 is valid for all rows.
382 --disable_cursor_protocol
383 SELECT COUNT(*) INTO @clash_count
384 FROM t1 INNER JOIN t0_template USING(f_int1)
385 WHERE MOD(f_int1,3) = 0
386 AND f_int1 BETWEEN @max_row_div2 AND @max_row;
387 --enable_cursor_protocol
388 if ($debug)
389 {
390 SELECT @try_count, @clash_count;
391 }
392 }
393
394
395 #-------------------------------------------------------------------------------
396 # 4 Some operations with single records
397 # 4.1 Insert one record with a value for f_int1 which is lower than in all
398 # existing records.
399 --disable_cursor_protocol
400 SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
401 --enable_cursor_protocol
402 INSERT INTO t1
403 SET f_int1 = @cur_value , f_int2 = @cur_value,
404 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
405 f_charbig = '#SINGLE#';
406 # Check of preceding statement via Select
407 if ($no_debug)
408 {
409 --disable_query_log
410 }
411 SELECT '# check single-1 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
412 WHERE f_int1 = @cur_value AND f_int2 = @cur_value
413 AND f_char1 = CAST(@cur_value AS CHAR)
414 AND f_char2 = CAST(@cur_value AS CHAR) AND f_charbig = '#SINGLE#';
415 --enable_query_log
416 #
417 # 4.2 Insert one record with a value for f_int1 which is higher than in all
418 # existing records.
419 --disable_cursor_protocol
420 SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
421 --enable_cursor_protocol
422 INSERT INTO t1
423 SET f_int1 = @cur_value , f_int2 = @cur_value,
424 f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
425 f_charbig = '#SINGLE#';
426 # Check of preceding statement via Select
427 if ($no_debug)
428 {
429 --disable_query_log
430 }
431 SELECT '# check single-2 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
432 WHERE f_int1 = @cur_value AND f_int2 = @cur_value
433 AND f_char1 = CAST(@cur_value AS CHAR)
434 AND f_char2 = CAST(@cur_value AS CHAR) AND f_charbig = '#SINGLE#';
435 --enable_query_log
436 #
437 # 4.3 Update one record. The value of f_int1 is altered from the lowest to
438 # the highest value of all existing records.
439 # If f_int1 is used for the partitioning expression a movement of the
440 # record to another partition/subpartition might appear.
441 --disable_cursor_protocol
442 SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
443 SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
444 --enable_cursor_protocol
445 UPDATE t1 SET f_int1 = @cur_value2
446 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
447 # Check of preceding statement via Select
448 if ($no_debug)
449 {
450 --disable_query_log
451 }
452 SELECT '# check single-3 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
453 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
454 --enable_query_log
455 #
456 # 4.4 Update one record. The value of f_int1 is altered from the highest value
457 # to a value lower than in all existing records.
458 # If f_int1 is used for the partitioning expression a movement of the
459 # record to another partition/subpartition might appear.
460 # f_int1 gets the delicate value '-1'.
461 SET @cur_value1= -1;
462 --disable_cursor_protocol
463 SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
464 --enable_cursor_protocol
465 # Bug#15968: Partitions: crash when INSERT with f_int1 = -1 into PARTITION BY HASH(f_int1)
466 UPDATE t1 SET f_int1 = @cur_value1
467 WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
468 # Check of preceding statement via Select
469 if ($no_debug)
470 {
471 --disable_query_log
472 }
473 SELECT '# check single-4 success: ' AS "",COUNT(*) AS "" FROM t1
474 WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
475 --enable_query_log
476 #
477 # 4.5 Delete the record with the highest value of f_int1.
478 --disable_cursor_protocol
479 SELECT MAX(f_int1) INTO @cur_value FROM t1;
480 --enable_cursor_protocol
481 DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
482 # Check of preceding statements via Select
483 if ($no_debug)
484 {
485 --disable_query_log
486 }
487 SELECT '# check single-5 success: ' AS "",COUNT(*) = 0 AS "" FROM t1
488 WHERE f_charbig = '#SINGLE#' AND f_int1 = f_int1 = @cur_value;
489 --enable_query_log
490 #
491 # 4.6 Delete the record with f_int1 = -1
492 DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
493 # Check of preceding statements via Select
494 if ($no_debug)
495 {
496 --disable_query_log
497 }
498 SELECT '# check single-6 success: ' AS "",COUNT(*) = 0 AS "" FROM t1
499 WHERE f_charbig = '#SINGLE#' AND f_int1 IN (-1,@cur_value);
500 --enable_query_log
501 #
502 # 4.7 Insert one record with such a big value for f_int1, so that in case
503 # - f_int1 is used within the partitioning algorithm
504 # - we use range partitioning
505 # we get error ER_NO_PARTITION_FOR_GIVEN_VALUE
506 # "Table has no partition for value ...."
507 # or ER_SAME_NAME_PARTITION
508 --disable_abort_on_error
509 eval INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#$max_int_4##';
510 --enable_abort_on_error
511 if ($no_debug)
512 {
513 --disable_query_log
514 }
515 eval SET @my_errno = $mysql_errno;
516 if (`SELECT @my_errno NOT IN (0,$ER_SAME_NAME_PARTITION,$ER_NO_PARTITION_FOR_GIVEN_VALUE)`)
517 {
518 --echo # The last command got an unexpected error response.
519 --echo # Expected/handled SQL codes are 0,$ER_SAME_NAME_PARTITION,$ER_NO_PARTITION_FOR_GIVEN_VALUE
520 SELECT '# SQL code we got was: ' AS "", @my_errno AS "";
521 --echo # Sorry, have to abort.
522 exit;
523 --echo
524 }
525 # Check of preceding statement via Select, if the INSERT was successful
526 let $run= `SELECT @my_errno = 0`;
527 if ($run)
528 {
529 # Attention: There are some tests where the column type is changed from
530 # INTEGER to MEDIUMINT. MEDIUMINT has a smaller range and the
531 # inserted value is automatically adjusted to the maximum value
532 # of the data type.
533 # that's the reason why we cannot use WHERE <column> = @max_int_4 here.
534 #
535 eval SELECT '# check single-7 success: ' AS "",
536 COUNT(*) = 1 AS "" FROM t1 WHERE f_charbig = '#$max_int_4##';
537 # Revert this modification
538 --enable_query_log
539 eval DELETE FROM t1 WHERE f_charbig = '#$max_int_4##';
540 }
541 --enable_query_log
542
543
544 #-------------------------------------------------------------------------------
545 # 5 Experiments with NULL
546 # If the result of the partitioning function IS NULL partitioning treats
547 # this record as if the the result of the partitioning function is
548 # MySQL 5.1 < March 2006 : zero
549 # MySQL 5.1 >= March 2006 : LONGLONG_MIN
550 # Let's INSERT a record where the result of the partitioning function is
551 # probably (depends on function currently used) zero and look if there are
552 # any strange effects during the execution of the next statements.
553 # Bug#18659: Partitions: wrong result on WHERE <col. used in part. function> IS NULL
554 DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
555 # Attention: Zero should be tested
556 INSERT t1 SET f_int1 = 0 , f_int2 = 0,
557 f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
558 f_charbig = '#NULL#';
559 # 5.1 Insert one record with f_int1 IS NULL.
560 # f1 "=" NULL is a delicate value which might stress the partitioning
561 # mechanism if the result of the expression in the partitioning algorithm
562 # becomes NULL.
563 # This INSERT will fail, if f_int1 is PRIMARY KEY or UNIQUE INDEX
564 # with ER_BAD_NULL_ERROR.
565 --disable_abort_on_error
566 INSERT INTO t1
567 SET f_int1 = NULL , f_int2 = -@max_row,
568 f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
569 f_charbig = '#NULL#';
570 # Some other NULL experiments if preceding INSERT was successfull
571 --enable_abort_on_error
572 if ($no_debug)
573 {
574 --disable_query_log
575 }
576 eval SET @my_errno = $mysql_errno;
577 let $run= `SELECT @my_errno = 0`;
578 if (`SELECT @my_errno NOT IN (0,$ER_BAD_NULL_ERROR)`)
579 {
580 --echo # The last command got an unexpected error response.
581 --echo # Expected/handled SQL codes are 0,$ER_BAD_NULL_ERROR
582 SELECT '# SQL code we got was: ' AS "", @my_errno AS "";
583 --echo # Sorry, have to abort.
584 --echo # Please check the error name to number mapping in inc/partition.pre.
585 exit;
586 --echo
587 }
588 --enable_query_log
589 # Give a success message like in the other following tests
590 --echo # check null success: 1
591 # The following checks do not make sense if f_int1 cannot be NULL
592 if ($run)
593 {
594 # Check of preceding statement via Select
595 if ($no_debug)
596 {
597 --disable_query_log
598 }
599 # Bug#17432: Partitions: wrong result, SELECT ... where <column> is null
600 SELECT '# check null-1 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
601 WHERE f_int1 IS NULL AND f_charbig = '#NULL#';
602 --enable_query_log
603 #
604 # 5.2 Update of f_int1 from NULL to negative value
605 # Bug#17432: Partitions: wrong result, SELECT ... where <column> is null
606 UPDATE t1 SET f_int1 = -@max_row
607 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
608 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
609 if ($no_debug)
610 {
611 --disable_query_log
612 }
613 # Check of preceding statement via Select
614 SELECT '# check null-2 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
615 WHERE f_int1 = -@max_row AND f_charbig = '#NULL#';
616 --enable_query_log
617 # 5.3 Update of f_int1 from negative value to NULL
618 UPDATE t1 SET f_int1 = NULL
619 WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
620 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
621 if ($no_debug)
622 {
623 --disable_query_log
624 }
625 # Check of preceding statement via Select
626 SELECT '# check null-3 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
627 WHERE f_int1 IS NULL AND f_charbig = '#NULL#';
628 --enable_query_log
629 # 5.4 DELETE of the record with f_int1 IS NULL
630 DELETE FROM t1
631 WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
632 AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
633 # Check of preceding statement via Select
634 if ($no_debug)
635 {
636 --disable_query_log
637 }
638 SELECT '# check null-4 success: ' AS "",COUNT(*) = 0 AS "" FROM t1
639 WHERE f_int1 IS NULL;
640 --enable_query_log
641 }
642 # Remove the "0" record
643 DELETE FROM t1
644 WHERE f_int1 = 0 AND f_int2 = 0
645 AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
646 AND f_charbig = '#NULL#';
647
648
649 #-------------------------------------------------------------------------------
650 ## 6. UPDATEs of columns used in the partitioning function and the PRIMARY KEY
651 # the UNIQUE INDEX without using straight forward UPDATE.
652 # INSERT .... ON DUPLICATE KEY UPDATE .... --> update existing record
653 # REPLACE --> delete existing record + insert new record
654 # Note:
655 # - This test is skipped for tables without any PRIMARY KEY or
656 # UNIQUE INDEX.
657 # - MOD(<column>,n) with n = prime number, n <> 2 is used to cause
658 # that many records and most probably more than one PARTITION/
659 # SUBPARTITION are affected.
660 # - Under certain circumstanditions a movement of one or more records
661 # to other PARTITIONs/SUBPARTITIONs might appear.
662 # - There are some storage engines, which are unable to revert changes
663 # of a failing statement. This has to be taken into account when
664 # checking if a DUPLICATE KEY might occur.
665 #
666 # What to test ?
667 # UNIQUE columns
668 # f_int1 IU f_int1 IU f_int1,f_int2 R
669 # f_int2 IU f_int2 IU f_int1,f_int2 R
670 # f_int1,f_int2 IU f_int1,f_int2 R
671 #
672 # IU column = INSERT .. ON DUPLICATE KEY UPDATE column
673 # R = REPLACE ..
674 #
675 # Current state of the data
676 # 1. f_int1 = f_int2, f_char1 = CAST(f_int1 AS CHAR), f_char2 = f_char1,
677 # f_charbig = CONCAT('===',f_char1,'===);
678 # 2. f_int1 FROM 1 TO @max_row_div4
679 # AND @max_row_div2 + @max_row_div4 TO @max_row
680 #
681 # Do not apply the following tests to tables without UNIQUE columns.
682 if ($any_unique)
683 {
684 let $num= 1;
685 if ($f_int1_is_unique)
686 {
687 ## 6.1 f_int1 is UNIQUE, UPDATE f_int1 when DUPLICATE KEY
688 # Bug#15236 Partitions: crash, if Insert .. on duplicate key causes update of existing row
689 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
690 SELECT f_int1, f_int1, '', '', 'was inserted'
691 FROM t0_template source_tab
692 WHERE MOD(f_int1,3) = 0
693 AND f_int1 BETWEEN @max_row_div2 AND @max_row
694 ON DUPLICATE KEY
695 UPDATE f_int1 = 2 * @max_row + source_tab.f_int1,
696 f_charbig = 'was updated';
697 --source suite/parts/inc/partition_20.inc
698 }
699
700 if ($f_int2_is_unique)
701 {
702 ## 6.2 f_int2 is UNIQUE, UPDATE f_int2 when DUPLICATE KEY
703 # Bug#15236 Partitions: crash, if Insert .. on duplicate key causes update of existing row
704 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
705 SELECT f_int1, f_int1, '', '', 'was inserted'
706 FROM t0_template source_tab
707 WHERE MOD(f_int1,3) = 0
708 AND f_int1 BETWEEN @max_row_div2 AND @max_row
709 ON DUPLICATE KEY
710 UPDATE f_int2 = 2 * @max_row + source_tab.f_int1,
711 f_charbig = 'was updated';
712 --source suite/parts/inc/partition_20.inc
713 }
714
715 ## 6.3 f_int1, f_int2 is UNIQUE, UPDATE f_int1, f_int2 when DUPLICATE KEY
716 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
717 SELECT f_int1, f_int1, '', '', 'was inserted'
718 FROM t0_template source_tab
719 WHERE MOD(f_int1,3) = 0
720 AND f_int1 BETWEEN @max_row_div2 AND @max_row
721 ON DUPLICATE KEY
722 UPDATE f_int1 = 2 * @max_row + source_tab.f_int1,
723 f_int2 = 2 * @max_row + source_tab.f_int1,
724 f_charbig = 'was updated';
725 --source suite/parts/inc/partition_20.inc
726
727 ## 6.4 REPLACE
728 # Bug#16782: Partitions: crash, REPLACE .. on table with PK, DUPLICATE KEY
729 REPLACE INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
730 SELECT f_int1, - f_int1, '', '', 'was inserted or replaced'
731 FROM t0_template source_tab
732 WHERE MOD(f_int1,3) = 0 AND f_int1 BETWEEN @max_row_div2 AND @max_row;
733 # DEBUG SELECT * FROM t1 ORDER BY f_int1, f_int2;
734 # Check of preceding statement via Select
735 if ($no_debug)
736 {
737 --disable_query_log
738 }
739 SELECT '# check replace success: ' AS "", COUNT(*) = @try_count AS ""
740 FROM t1 WHERE f_charbig = 'was inserted or replaced';
741 --enable_query_log
742 # Revert the modification
743 DELETE FROM t1
744 WHERE f_int1 BETWEEN @max_row_div2 AND @max_row_div2 + @max_row_div4;
745 # If there is only UNIQUE (f1,f2) we will have pairs f_int1,f_int2
746 # <n>, <n> and <n>, <-n>
747 # where MOD(f_int1,3) = 0
748 # and f_int1 between @max_row_div2 + @max_row_div4 and @max_row.
749 # Delete the <n>, <n> records.
750 DELETE FROM t1
751 WHERE f_int1 = f_int2 AND MOD(f_int1,3) = 0 AND
752 f_int1 BETWEEN @max_row_div2 + @max_row_div4 AND @max_row;
753 UPDATE t1 SET f_int2 = f_int1,
754 f_char1 = CAST(f_int1 AS CHAR),
755 f_char2 = CAST(f_int1 AS CHAR),
756 f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===')
757 WHERE f_charbig = 'was inserted or replaced' AND f_int1 = - f_int2;
758 # DEBUG SELECT * FROM t1 ORDER BY f_int1, f_int2;
759 }
760
761
762 #-------------------------------------------------------------------------------
763 # 7 Transactions
764 SET AUTOCOMMIT= 0;
765 # DEBUG SELECT @max_row_div4 , @max_row_div2 + @max_row_div4;
766 if ($no_debug)
767 {
768 --disable_query_log
769 }
770 --disable_cursor_protocol
771 SELECT COUNT(f_int1) INTO @start_count FROM t1
772 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
773 --enable_cursor_protocol
774 --enable_query_log
775 let $run= `SELECT @start_count <> 0`;
776 if ($run)
777 {
778 --echo # Prerequisites for following tests not fullfilled.
779 --echo # The content of the table t1 is unexpected
780 --echo # There must be no rows BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
781 SELECT COUNT(f_int1) FROM t1
782 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
783 --echo # Sorry, have to abort.
784 exit;
785 }
786 # Number of records to be inserted
787 if ($no_debug)
788 {
789 --disable_query_log
790 }
791 --disable_cursor_protocol
792 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
793 --enable_cursor_protocol
794 --enable_query_log
795 # 7.1 Successful INSERT + COMMIT
796 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
797 SELECT f_int1, f_int1, '', '', 'was inserted'
798 FROM t0_template source_tab
799 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
800 # The inserted records must be visible (at least for our current session)
801 if ($no_debug)
802 {
803 --disable_query_log
804 }
805 SELECT '# check transactions-1 success: ' AS "",
806 COUNT(*) = @exp_inserted_rows AS ""
807 FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
808 --enable_query_log
809 # Make the changes persistent for all storage engines
810 COMMIT WORK;
811 # The inserted records must be visible (for all open and future sessions)
812 if ($no_debug)
813 {
814 --disable_query_log
815 }
816 SELECT '# check transactions-2 success: ' AS "",
817 COUNT(*) = @exp_inserted_rows AS ""
818 FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
819 --enable_query_log
820 # Let's assume we have a transactional engine + COMMIT is ill.
821 # A correct working ROLLBACK might revert the INSERT.
822 ROLLBACK WORK;
823 if ($no_debug)
824 {
825 --disable_query_log
826 }
827 SELECT '# check transactions-3 success: ' AS "",
828 COUNT(*) = @exp_inserted_rows AS ""
829 FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
830 --enable_query_log
831 # Revert the changes
832 DELETE FROM t1 WHERE f_charbig = 'was inserted';
833 COMMIT WORK;
834 ROLLBACK WORK;
835 if ($no_debug)
836 {
837 --disable_query_log
838 }
839 SELECT '# check transactions-4 success: ' AS "",
840 COUNT(*) = 0 AS ""
841 FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
842 --enable_query_log
843 #
844 # 7.2 Successful INSERT + ROLLBACK
845 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
846 SELECT f_int1, f_int1, '', '', 'was inserted'
847 FROM t0_template source_tab
848 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
849 if ($no_debug)
850 {
851 --disable_query_log
852 }
853 SELECT '# check transactions-5 success: ' AS "",
854 COUNT(*) = @exp_inserted_rows AS ""
855 FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
856 --enable_query_log
857 ROLLBACK WORK;
858 if ($no_debug)
859 {
860 --disable_query_log
861 }
862 --disable_cursor_protocol
863 SELECT COUNT(*) INTO @my_count
864 FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
865 --enable_cursor_protocol
866 SELECT '# check transactions-6 success: ' AS "",
867 @my_count IN (0,@exp_inserted_rows) AS "";
868 let $run= `SELECT @my_count = 0`;
869 if ($run)
870 {
871 --echo # INFO: Storage engine used for t1 seems to be transactional.
872 }
873 let $run= `SELECT @my_count = @exp_inserted_rows`;
874 if ($run)
875 {
876 --echo # INFO: Storage engine used for t1 seems to be not transactional.
877 }
878 --enable_query_log
879 # Let's assume we have a transactional engine + ROLLBACK is ill.
880 # A correct working COMMIT might make the inserted records again visible.
881 COMMIT;
882 if ($no_debug)
883 {
884 --disable_query_log
885 }
886 SELECT '# check transactions-7 success: ' AS "",
887 COUNT(*) IN (0,@exp_inserted_rows) AS ""
888 FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
889 --enable_query_log
890 # Revert the changes
891 DELETE FROM t1 WHERE f_charbig = 'was inserted';
892 COMMIT WORK;
893 #
894 # 7.3 Failing INSERT (in mid of statement processing) + COMMIT
895 SET @@session.sql_mode = 'traditional';
896 # Number of records where a INSERT has to be tried
897 --disable_cursor_protocol
898 SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
899 --enable_cursor_protocol
900 #
901 --disable_abort_on_error
902 INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
903 SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
904 '', '', 'was inserted' FROM t0_template
905 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
906 --enable_abort_on_error
907 COMMIT;
908 # How many new records should be now visible ?
909 # 1. storage engine unable to revert changes made by the failing statement
910 # @max_row_div2 - 1 - @max_row_div4 + 1
911 # 2. storage engine able to revert changes made by the failing statement
912 # 0
913 if ($no_debug)
914 {
915 --disable_query_log
916 }
917 --disable_cursor_protocol
918 SELECT COUNT(*) INTO @my_count
919 FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
920 --enable_cursor_protocol
921 SELECT '# check transactions-8 success: ' AS "",
922 @my_count IN (@max_row_div2 - 1 - @max_row_div4 + 1,0) AS "";
923 let $run= `SELECT @my_count = @max_row_div2 - 1 - @max_row_div4 + 1`;
924 if ($run)
925 {
926 --echo # INFO: Storage engine used for t1 seems to be unable to revert
927 --echo # changes made by the failing statement.
928 }
929 let $run= `SELECT @my_count = 0`;
930 if ($run)
931 {
932 --echo # INFO: Storage engine used for t1 seems to be able to revert
933 --echo # changes made by the failing statement.
934 }
935 --enable_query_log
936 SET @@session.sql_mode = '';
937 SET AUTOCOMMIT= 1;
938 # Revert the changes
939 DELETE FROM t1 WHERE f_charbig = 'was inserted';
940 COMMIT WORK;
941
942 if ($debug)
943 {
944 SELECT * FROM t1 ORDER BY f_int1;
945 }
946
947
948 #-------------------------------------------------------------------------------
949 # 8 Some special cases
950 # 8.1 Dramatic increase of the record/partition/subpartition/table sizes
951 UPDATE t1 SET f_charbig = REPEAT('b', 1000);
952 # partial check of preceding statement via Select
953 if ($no_debug)
954 {
955 --disable_query_log
956 }
957 eval SELECT '# check special-1 success: ' AS "",1 AS "" FROM t1
958 WHERE f_int1 = 1 AND f_charbig = REPEAT('b', 1000);
959 --enable_query_log
960 #
961 # 8.2 Dramatic decrease of the record/partition/subpartition/table sizes
962 UPDATE t1 SET f_charbig = '';
963 # partial check of preceding statement via Select
964 if ($no_debug)
965 {
966 --disable_query_log
967 }
968 eval SELECT '# check special-2 success: ' AS "",1 AS "" FROM t1
969 WHERE f_int1 = 1 AND f_charbig = '';
970 --enable_query_log
971 # Revert the changes
972 UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
973
974 if ($debug)
975 {
976 SELECT * FROM t1 ORDER BY f_int1;
977 }
978
979
980 #-------------------------------------------------------------------------------
981 # 9 TRIGGERs
982 let $num= 1;
983 # 9.1 BEFORE/AFTER INSERT/UPDATE/DELETE TRIGGER on table t0_aux causes that
984 # column values used in partitioning function of t1 are changed.
985 let $tab_has_trigg= t0_aux;
986 let $tab_in_trigg= t1;
987
988 # Insert three records, which will be updated by the trigger
989 eval INSERT INTO $tab_in_trigg(f_int1,f_int2,f_char1,f_char2,f_charbig)
990 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
991 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
992
993 let $statement= INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
994 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
995 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
996 let $event= BEFORE INSERT;
997 --source suite/parts/inc/partition_trigg1.inc
998 let $event= AFTER INSERT;
999 --source suite/parts/inc/partition_trigg1.inc
1000
1001 let $statement= UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
1002 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1003 let $event= BEFORE UPDATE;
1004 --source suite/parts/inc/partition_trigg1.inc
1005 let $event= AFTER UPDATE;
1006 --source suite/parts/inc/partition_trigg1.inc
1007
1008 let $statement= DELETE FROM t0_aux
1009 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1010 let $event= BEFORE DELETE;
1011 --source suite/parts/inc/partition_trigg1.inc
1012 let $event= AFTER DELETE;
1013 --source suite/parts/inc/partition_trigg1.inc
1014
1015 # Cleanup
1016 eval DELETE FROM $tab_in_trigg
1017 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1018
1019 # Two currently (February 2006) impossible operations.
1020 # 1442: 'Can't update table 't1' in stored function/trigger because it is
1021 # already used by statement which invoked this stored function/trigger.'
1022 # 1362: 'Updating of OLD row is not allowed in trigger'
1023
1024 if ($debug)
1025 {
1026 SELECT * FROM t1 ORDER BY f_int1;
1027 }
1028
1029 if ($more_trigger_tests)
1030 {
1031 # 9.2 BEFORE/AFTER INSERT/UPDATE/DELETE TRIGGER on partitioned table t1 causes
1032 # that column values in not partitioned table t0_aux are changed.
1033 let $tab_has_trigg= t1;
1034 let $tab_in_trigg= t0_aux;
1035
1036 # Insert three records, which will be updated by the trigger
1037 eval INSERT INTO $tab_in_trigg(f_int1,f_int2,f_char1,f_char2,f_charbig)
1038 SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
1039 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1040
1041 let $statement= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1042 SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1043 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1044 let $event= BEFORE INSERT;
1045 --source suite/parts/inc/partition_trigg1.inc
1046 let $event= AFTER INSERT;
1047 --source suite/parts/inc/partition_trigg1.inc
1048
1049 let $statement= UPDATE t1 SET f_int1 = - f_int1, f_int2 = - f_int2
1050 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1051 let $event= BEFORE UPDATE;
1052 --source suite/parts/inc/partition_trigg1.inc
1053 let $event= AFTER UPDATE;
1054 --source suite/parts/inc/partition_trigg1.inc
1055
1056 let $statement= DELETE FROM t1
1057 WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1058 let $event= BEFORE DELETE;
1059 --source suite/parts/inc/partition_trigg1.inc
1060 let $event= AFTER DELETE;
1061 --source suite/parts/inc/partition_trigg1.inc
1062 eval DELETE FROM $tab_in_trigg
1063 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1064 }
1065
1066 if ($debug)
1067 {
1068 SELECT * FROM t1 ORDER BY f_int1;
1069 }
1070
1071 # 9.3 BEFORE/AFTER UPDATE TRIGGER on partitioned table causes that the value
1072 # of columns in partitioning function is recalculated
1073 if ($more_trigger_tests)
1074 {
1075 # 9.3.1 The UPDATE itself changes a column which is not used in the partitioning
1076 # function.
1077 # "old" values are used as source within the trigger.
1078 let $statement= UPDATE t1
1079 SET f_charbig = '####updated per update statement itself####';
1080 let $source= old;
1081 let $event= BEFORE UPDATE;
1082 --source suite/parts/inc/partition_trigg2.inc
1083 # FIXME when AFTER TRIGGER can be used
1084 # Currently (2006-02-23) a AFTER TRIGGER is not allowed to modify a row, which
1085 # was just modified: 1362: Updating of NEW row is not allowed in after trigger
1086 }
1087
1088 # 9.3.2 The UPDATE itself changes a column which is used in the partitioning
1089 # function.
1090 let $statement= UPDATE t1
1091 SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1092 f_charbig = '####updated per update statement itself####';
1093
1094 # 9.3.2.1 "old" values are used as source within the trigger.
1095 let $source= old;
1096 let $event= BEFORE UPDATE;
1097 --source suite/parts/inc/partition_trigg2.inc
1098 # FIXME when AFTER TRIGGER can be used
1099 # Currently (2006-02-23) a AFTER TRIGGER is not allowed to modify a row, which
1100 # was just modified: 1362: Updating of NEW row is not allowed in after trigger
1101 # 9.3.2.2 "new" values are used as source within the trigger.
1102 let $source= new;
1103 let $event= BEFORE UPDATE;
1104 --source suite/parts/inc/partition_trigg2.inc
1105 # FIXME when AFTER TRIGGER can be used
1106
1107 if ($debug)
1108 {
1109 SELECT * FROM t1 ORDER BY f_int1;
1110 }
1111
1112 # 9.4 BEFORE/AFTER INSERT TRIGGER on partitioned table causes that the value of
1113 # columns in partitioning function is recalculated.
1114 # 9.4.1 INSERT assigns values to the recalculate columns
1115 let $statement= INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1116 SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1117 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1118 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1119 ORDER BY f_int1;
1120 let $event= BEFORE INSERT;
1121 let $source= new;
1122 --source suite/parts/inc/partition_trigg3.inc
1123 # FIXME when AFTER TRIGGER can be used
1124
1125 # 9.4.2 INSERT assigns no values to the recalculate columns
1126 let $statement= INSERT INTO t1 (f_char1, f_char2, f_charbig)
1127 SELECT CAST(f_int1 AS CHAR),
1128 CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1129 WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1130 ORDER BY f_int1;
1131 let $event= BEFORE INSERT;
1132 let $source= new;
1133 --source suite/parts/inc/partition_trigg3.inc
1134 # FIXME when AFTER TRIGGER can be used
1135
1136 if ($debug)
1137 {
1138 SELECT * FROM t1 ORDER BY f_int1;
1139 }
1140
1141
1142 #-------------------------------------------------------------------------------
1143 # 10 ANALYZE/CHECK/CHECKSUM
1144 ANALYZE TABLE t1;
1145 CHECK TABLE t1 EXTENDED;
1146 # Checksum depends on @max_row so we have to unify the value
1147 --replace_column 2 <some_value>
1148 CHECKSUM TABLE t1 EXTENDED;
1149
1150
1151 #-------------------------------------------------------------------------------
1152 # 11 Some special statements, which may lead to a rebuild of the trees
1153 # depending on the storage engine and some particular conditions
1154 # 11.1 OPTIMIZE TABLE
1155 # Manual about OPTIMIZE <InnoDB table>:
1156 # ... , it is mapped to ALTER TABLE, which rebuilds the table.
1157 # Rebuilding updates index statistics and frees unused space in the
1158 # clustered index.
1159 OPTIMIZE TABLE t1;
1160 --source suite/parts/inc/partition_layout_check2.inc
1161 # 10.2 REPAIR TABLE
1162 REPAIR TABLE t1 EXTENDED;
1163 --source suite/parts/inc/partition_layout_check2.inc
1164 #
1165 # 11.3 Truncate
1166 # Manual about TRUNCATE on tables ( != InnoDB table with FOREIGN KEY ):
1167 # Truncate operations drop and re-create the table ....
1168 TRUNCATE t1;
1169 # Check of preceding statement via Select
1170 if ($no_debug)
1171 {
1172 --disable_query_log
1173 }
1174 SELECT '# check TRUNCATE success: ' AS "",COUNT(*) = 0 AS "" FROM t1;
1175 --enable_query_log
1176 --source suite/parts/inc/partition_layout_check2.inc
1177 --echo # End usability test (inc/partition_check.inc)
1178