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