1
################################################################################
2
# inc/partition_check.inc #
5
# Do some basic usability checks on table t1. #
6
# This routine is only useful for the partition_<feature>_<engine> tests. #
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 #
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###' #
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. #
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. #
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
################################################################################
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
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
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
73
let $run= `SELECT @aux`;
77
--echo # Prerequisites for following tests not fullfilled.
78
--echo # The content of the table t1 is unexpected
81
--echo # Sorry, have to abort.
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
91
# - MIN/MAX on all columns possibly used in part. function
92
# The optimizer might decide to run on INDEX only, if available.
94
## 1.2.1 Check COUNT(*)
99
let $my_stmt= SELECT COUNT(*) <> @max_row INTO @aux FROM t1;
100
let $run= `SELECT @aux`;
104
--echo # Prerequisites for following tests not fullfilled.
105
--echo # The content of the table t1 is unexpected
108
--echo # Sorry, have to abort.
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)
118
let $my_stmt= SELECT MIN(f_int1) <> 1 AND MAX(f_int1) <> @max_row INTO @aux
120
let $run= `SELECT @aux`;
124
--echo # Prerequisites for following tests not fullfilled.
125
--echo # The content of the table t1 is unexpected
128
--echo # Sorry, have to abort.
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)
138
let $my_stmt= SELECT MIN(f_int2) <> 1 AND MAX(f_int2) <> @max_row INTO @aux
140
let $run= `SELECT @aux`;
144
--echo # Prerequisites for following tests not fullfilled.
145
--echo # The content of the table t1 is unexpected
148
--echo # Sorry, have to abort.
151
# Give a success message like in the other following tests
152
--echo # check MIN/MAX(f_int2) success: 1
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
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
165
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
166
CAST(f_int1 AS CHAR), 'delete me' FROM t0_template
167
WHERE f_int1 IN (2,3);
168
--enable_abort_on_error
173
eval SET @my_errno = $mysql_errno;
174
let $run_delete= `SELECT @my_errno = 0`;
175
let $any_unique= `SELECT @my_errno IN ($ER_DUP_KEY,$ER_DUP_ENTRY)`;
176
# DEBUG eval SELECT $run_delete AS run_delete, $any_unique AS any_unique,
177
# @my_errno AS sql_errno;
178
if (`SELECT @my_errno NOT IN (0,$ER_DUP_KEY,$ER_DUP_ENTRY)`)
180
--echo # The last command got an unexepected error response.
181
--echo # Expected/handled SQL codes are 0,$ER_DUP_KEY,$ER_DUP_ENTRY
182
SELECT '# SQL code we got was: ' AS "", @my_errno AS "";
183
--echo # Sorry, have to abort.
187
# Give a success message like in the other following tests
188
--echo # check prerequisites-3 success: 1
190
# DEBUG eval SELECT $run_delete AS run_delete, $any_unique AS any_unique;
193
# INSERT was successful -> DELETE this new record
194
DELETE FROM t1 WHERE f_charbig = 'delete me';
195
--echo # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE
199
--echo # INFO: f_int1 AND/OR f_int2 AND/OR (f_int1,f_int2) is UNIQUE
201
## 1.3.1 Check, if f_int1 is UNIQUE
202
# Sideeffect: Attempt to INSERT one record
203
# DUPLICATE KEY will appear if we have UNIQUE columns
204
# ER_DUP_KEY, ER_DUP_ENTRY
205
--disable_abort_on_error
206
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
207
SELECT f_int1, 2 * @max_row + f_int1, CAST((2 * @max_row + f_int1) AS CHAR),
208
CAST((2 * @max_row + f_int1) AS CHAR), 'delete me' FROM t0_template
209
WHERE f_int1 IN (2,3);
210
--enable_abort_on_error
215
eval SET @my_errno = $mysql_errno;
216
let $run_delete= `SELECT @my_errno = 0`;
217
let $f_int1_is_unique= `SELECT @my_errno IN ($ER_DUP_KEY,$ER_DUP_ENTRY)`;
218
# DEBUG eval SELECT $run_delete AS run_delete, $f_int1_is_unique AS any_unique,
219
# @my_errno AS sql_errno;
220
if (`SELECT @my_errno NOT IN (0,$ER_DUP_KEY,$ER_DUP_ENTRY)`)
222
--echo # The last command got an unexepected error response.
223
--echo # Expected/handled SQL codes are 0,$ER_DUP_KEY,$ER_DUP_ENTRY
224
SELECT '# SQL code we got was: ' AS "", @my_errno AS "";
225
--echo # Sorry, have to abort.
230
if ($f_int1_is_unique)
232
--echo # INFO: f_int1 is UNIQUE
236
# INSERT was successful -> DELETE this new record
237
DELETE FROM t1 WHERE f_charbig = 'delete me';
240
## 1.3.2 Check, if f_int2 is UNIQUE (get ER_DUP_KEY or ER_DUP_ENTRY
241
--disable_abort_on_error
242
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
243
SELECT 2 * @max_row + f_int1, f_int1, CAST((2 * @max_row + f_int1) AS CHAR),
244
CAST((2 * @max_row + f_int1) AS CHAR), 'delete me' FROM t0_template
245
WHERE f_int1 IN (2,3);
246
--enable_abort_on_error
251
eval SET @my_errno = $mysql_errno;
252
let $run_delete= `SELECT @my_errno = 0`;
253
let $f_int1_is_unique= `SELECT @my_errno IN ($ER_DUP_KEY,$ER_DUP_ENTRY)`;
254
# DEBUG eval SELECT $run_delete AS run_delete, $f_int1_is_unique AS any_unique,
255
# @my_errno AS sql_errno;
256
if (`SELECT @my_errno NOT IN (0,$ER_DUP_KEY,$ER_DUP_ENTRY)`)
258
--echo # The last command got an unexepected error response.
259
--echo # Expected/handled SQL codes are 0,$ER_DUP_KEY,$ER_DUP_ENTRY
260
SELECT '# SQL code we got was: ' AS "", @my_errno AS "";
261
--echo # Sorry, have to abort.
265
if ($f_int2_is_unique)
267
--echo # INFO: f_int2 is UNIQUE
272
# INSERT was successful -> DELETE this new record
273
DELETE FROM t1 WHERE f_charbig = 'delete me';
278
#-------------------------------------------------------------------------------
279
## 2. Read the table row by row
280
# Note: There were crashes in history when reading a partitioned table
281
# PRIMARY KEY AND/OR UNIQUE INDEXes
282
## 2.1 Read all existing and some not existing records of table
283
# per f_int1 used in partitioning function
284
let $col_to_check= f_int1;
285
--source suite/parts/inc/partition_check_read.inc
286
## 2.2 Read all existing and some not existing records of table
287
# per f_int2 used in partitioning function
288
let $col_to_check= f_int2;
289
--source suite/parts/inc/partition_check_read.inc
292
#-------------------------------------------------------------------------------
293
# 3 Some operations with multiple records
294
# 3.1 Select on "full" table
299
SELECT '# check multiple-1 success: ' AS "",COUNT(*) = @max_row AS "" FROM t1;
302
# 3.2 (mass) DELETE of @max_row_div3 records
303
DELETE FROM t1 WHERE MOD(f_int1,3) = 0;
308
SELECT '# check multiple-2 success: ' AS "",COUNT(*) = @max_row - @max_row_div3 AS "" FROM t1;
311
# 3.3 (mass) Insert of @max_row_div3 records
312
# (Insert the records deleted in 3.2)
313
INSERT INTO t1 SELECT * FROM t0_template
314
WHERE MOD(f_int1,3) = 0;
315
# Check of preceding statement via Select
320
SELECT '# check multiple-3 success: ' AS "",
321
(COUNT(*) = @max_row) AND (MIN(f_int1) = 1) AND (MAX(f_int1) = @max_row) AS ""
324
# DEBUG SELECT COUNT(*),MIN(f_int1),MAX(f_int1) FROM t1;
326
# 3.4 (mass) Update @max_row_div4 * 2 + 1 records
327
# Bug#18735: Partitions: NDB, UNIQUE INDEX, UPDATE, strange server response
328
UPDATE t1 SET f_int1 = f_int1 + @max_row
329
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4
330
AND @max_row_div2 + @max_row_div4;
331
# Check of preceding statement via Select
336
SELECT '# check multiple-4 success: ' AS "",(COUNT(*) = @max_row) AND (MIN(f_int1) = 1) AND
337
(MAX(f_int1) = @max_row_div2 + @max_row_div4 + @max_row ) AS "" FROM t1;
339
# DEBUG SELECT COUNT(*),MIN(f_int1),MAX(f_int1) FROM t1;
341
# 3.5 (mass) Delete @max_row_div4 * 2 + 1 records
342
# (Delete the records updated in 3.4)
344
WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row
345
AND @max_row_div2 + @max_row_div4 + @max_row;
346
# Check of preceding statement via Select
351
SELECT '# check multiple-5 success: ' AS "",
352
(COUNT(*) = @max_row - @max_row_div4 - @max_row_div4 - 1)
353
AND (MIN(f_int1) = 1) AND (MAX(f_int1) = @max_row) AS "" FROM t1;
355
# DEBUG SELECT COUNT(*),MIN(f_int1),MAX(f_int1) FROM t1;
357
#-------------------------------------------------------------------------------
358
# Attention: After this section all modification on the table have to be reverted !
359
# Current content of t1 follows the rule:
360
# <value>,<value>,'<value>','<value>',===<value>===
361
# <value> contains all INTEGER values
362
# between 1 and @max_row_div2 - @max_row_div4 - 1
364
# between @max_row_div2 + @max_row_div4 + 1 and @max_row
365
# With other words the values between @max_row_div2 - @max_row_div4
366
# and @max_row_div2 + @max_row_div4 are "missing".
367
#-------------------------------------------------------------------------------
368
# The following is only needed for tests of UNIQUE CONSTRAINTs.
371
# Calculate the number of records, where we will try INSERT ..... or REPLACE
372
SELECT COUNT(*) INTO @try_count FROM t0_template
373
WHERE MOD(f_int1,3) = 0
374
AND f_int1 BETWEEN @max_row_div2 AND @max_row;
376
# Calculate the number of records, where we will get DUPLICATE KEY
377
# f_int1 is sufficient for calculating this, because 1.1
378
# checks, that f_int1 = f_int2 is valid for all rows.
379
SELECT COUNT(*) INTO @clash_count
380
FROM t1 INNER JOIN t0_template USING(f_int1)
381
WHERE MOD(f_int1,3) = 0
382
AND f_int1 BETWEEN @max_row_div2 AND @max_row;
385
SELECT @try_count, @clash_count;
390
#-------------------------------------------------------------------------------
391
# 4 Some operations with single records
392
# 4.1 Insert one record with a value for f_int1 which is lower than in all
394
SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1;
396
SET f_int1 = @cur_value , f_int2 = @cur_value,
397
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
398
f_charbig = '#SINGLE#';
399
# Check of preceding statement via Select
404
SELECT '# check single-1 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
405
WHERE f_int1 = @cur_value AND f_int2 = @cur_value
406
AND f_char1 = CAST(@cur_value AS CHAR)
407
AND f_char2 = CAST(@cur_value AS CHAR) AND f_charbig = '#SINGLE#';
410
# 4.2 Insert one record with a value for f_int1 which is higher than in all
412
SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1;
414
SET f_int1 = @cur_value , f_int2 = @cur_value,
415
f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR),
416
f_charbig = '#SINGLE#';
417
# Check of preceding statement via Select
422
SELECT '# check single-2 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
423
WHERE f_int1 = @cur_value AND f_int2 = @cur_value
424
AND f_char1 = CAST(@cur_value AS CHAR)
425
AND f_char2 = CAST(@cur_value AS CHAR) AND f_charbig = '#SINGLE#';
428
# 4.3 Update one record. The value of f_int1 is altered from the lowest to
429
# the highest value of all existing records.
430
# If f_int1 is used for the partitioning expression a movement of the
431
# record to another partition/subpartition might appear.
432
SELECT MIN(f_int1) INTO @cur_value1 FROM t1;
433
SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1;
434
# Bug#18735: Partitions: NDB, UNIQUE INDEX, UPDATE, strange server response
435
UPDATE t1 SET f_int1 = @cur_value2
436
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
437
# Check of preceding statement via Select
442
SELECT '# check single-3 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
443
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
446
# 4.4 Update one record. The value of f_int1 is altered from the highest value
447
# to a value lower than in all existing records.
448
# If f_int1 is used for the partitioning expression a movement of the
449
# record to another partition/subpartition might appear.
450
# f_int1 gets the delicate value '-1'.
452
SELECT MAX(f_int1) INTO @cur_value2 FROM t1;
453
# Bug#15968: Partitions: crash when INSERT with f_int1 = -1 into PARTITION BY HASH(f_int1)
454
# Bug#16385: Partitions: crash when updating a range partitioned NDB table
455
# Bug#18735: Partitions: NDB, UNIQUE INDEX, UPDATE, strange server response
456
UPDATE t1 SET f_int1 = @cur_value1
457
WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#';
458
# Check of preceding statement via Select
463
SELECT '# check single-4 success: ' AS "",COUNT(*) AS "" FROM t1
464
WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#';
467
# 4.5 Delete the record with the highest value of f_int1.
468
SELECT MAX(f_int1) INTO @cur_value FROM t1;
469
DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#';
470
# Check of preceding statements via Select
475
SELECT '# check single-5 success: ' AS "",COUNT(*) = 0 AS "" FROM t1
476
WHERE f_charbig = '#SINGLE#' AND f_int1 = f_int1 = @cur_value;
479
# 4.6 Delete the record with f_int1 = -1
480
DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#';
481
# Check of preceding statements via Select
486
SELECT '# check single-6 success: ' AS "",COUNT(*) = 0 AS "" FROM t1
487
WHERE f_charbig = '#SINGLE#' AND f_int1 IN (-1,@cur_value);
490
# 4.7 Insert one record with such a big value for f_int1, so that in case
491
# - f_int1 is used within the partitioning algorithm
492
# - we use range partitioning
493
# we get error ER_NO_PARTITION_FOR_GIVEN_VALUE
494
# "Table has no partition for value ...."
495
# or ER_SAME_NAME_PARTITION
496
--disable_abort_on_error
497
eval INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#$max_int_4##';
498
--enable_abort_on_error
503
eval SET @my_errno = $mysql_errno;
504
if (`SELECT @my_errno NOT IN (0,$ER_SAME_NAME_PARTITION,$ER_NO_PARTITION_FOR_GIVEN_VALUE)`)
506
--echo # The last command got an unexepected error response.
507
--echo # Expected/handled SQL codes are 0,$ER_SAME_NAME_PARTITION,$ER_NO_PARTITION_FOR_GIVEN_VALUE
508
SELECT '# SQL code we got was: ' AS "", @my_errno AS "";
509
--echo # Sorry, have to abort.
513
# Check of preceding statement via Select, if the INSERT was successful
514
let $run= `SELECT @my_errno = 0`;
517
# Attention: There are some tests where the column type is changed from
518
# INTEGER to MEDIUMINT. MEDIUMINT has a smaller range and the
519
# inserted value is automatically adjusted to the maximum value
521
# that's the reason why we cannot use WHERE <column> = @max_int_4 here.
523
eval SELECT '# check single-7 success: ' AS "",
524
COUNT(*) = 1 AS "" FROM t1 WHERE f_charbig = '#$max_int_4##';
525
# Revert this modification
527
eval DELETE FROM t1 WHERE f_charbig = '#$max_int_4##';
532
#-------------------------------------------------------------------------------
533
# 5 Experiments with NULL
534
# If the result of the partitioning function IS NULL partitioning treats
535
# this record as if the the result of the partitioning function is
536
# MySQL 5.1 < March 2006 : zero
537
# MySQL 5.1 >= March 2006 : LONGLONG_MIN
538
# Let's INSERT a record where the result of the partitioning function is
539
# probably (depends on function currently used) zero and look if there are
540
# any strange effects during the execution of the next statements.
541
# Bug#17891: Partitions: NDB, crash on select .. where col is null or col = value
542
# Bug#18659: Partitions: wrong result on WHERE <col. used in part. function> IS NULL
543
DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0;
544
# Attention: Zero should be tested
545
INSERT t1 SET f_int1 = 0 , f_int2 = 0,
546
f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR),
547
f_charbig = '#NULL#';
548
# 5.1 Insert one record with f_int1 IS NULL.
549
# f1 "=" NULL is a delicate value which might stress the partitioning
550
# mechanism if the result of the expression in the partitioning algorithm
552
# This INSERT will fail, if f_int1 is PRIMARY KEY or UNIQUE INDEX
553
# with ER_BAD_NULL_ERROR.
554
--disable_abort_on_error
556
SET f_int1 = NULL , f_int2 = -@max_row,
557
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR),
558
f_charbig = '#NULL#';
559
# Some other NULL experiments if preceding INSERT was successfull
560
--enable_abort_on_error
565
eval SET @my_errno = $mysql_errno;
566
let $run= `SELECT @my_errno = 0`;
567
if (`SELECT @my_errno NOT IN (0,$ER_BAD_NULL_ERROR)`)
569
--echo # The last command got an unexepected error response.
570
--echo # Expected/handled SQL codes are 0,$ER_BAD_NULL_ERROR
571
SELECT '# SQL code we got was: ' AS "", @my_errno AS "";
572
--echo # Sorry, have to abort.
573
--echo # Please check the error name to number mapping in inc/partition.pre.
578
# Give a success message like in the other following tests
579
--echo # check null success: 1
580
# The following checks do not make sense if f_int1 cannot be NULL
583
# Check of preceding statement via Select
588
# Bug#17432: Partitions: wrong result, SELECT ... where <column> is null
589
SELECT '# check null-1 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
590
WHERE f_int1 IS NULL AND f_charbig = '#NULL#';
593
# 5.2 Update of f_int1 from NULL to negative value
594
# Bug#17432: Partitions: wrong result, SELECT ... where <column> is null
595
UPDATE t1 SET f_int1 = -@max_row
596
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
597
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
602
# Check of preceding statement via Select
603
SELECT '# check null-2 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
604
WHERE f_int1 = -@max_row AND f_charbig = '#NULL#';
606
# 5.3 Update of f_int1 from negative value to NULL
607
UPDATE t1 SET f_int1 = NULL
608
WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
609
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
614
# Check of preceding statement via Select
615
SELECT '# check null-3 success: ' AS "",COUNT(*) = 1 AS "" FROM t1
616
WHERE f_int1 IS NULL AND f_charbig = '#NULL#';
618
# 5.4 DELETE of the record with f_int1 IS NULL
620
WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR)
621
AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#';
622
# Check of preceding statement via Select
627
SELECT '# check null-4 success: ' AS "",COUNT(*) = 0 AS "" FROM t1
628
WHERE f_int1 IS NULL;
631
# Remove the "0" record
633
WHERE f_int1 = 0 AND f_int2 = 0
634
AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR)
635
AND f_charbig = '#NULL#';
638
#-------------------------------------------------------------------------------
639
## 6. UPDATEs of columns used in the partitioning function and the PRIMARY KEY
640
# the UNIQUE INDEX without using straight forward UPDATE.
641
# INSERT .... ON DUPLICATE KEY UPDATE .... --> update existing record
642
# REPLACE --> delete existing record + insert new record
644
# - This test is skipped for tables without any PRIMARY KEY or
646
# - MOD(<column>,n) with n = prime number, n <> 2 is used to cause
647
# that many records and most probably more than one PARTITION/
648
# SUBPARTITION are affected.
649
# - Under certain circumstanditions a movement of one or more records
650
# to other PARTITIONs/SUBPARTITIONs might appear.
651
# - There are some storage engines, which are unable to revert changes
652
# of a failing statement. This has to be taken into account when
653
# checking if a DUPLICATE KEY might occur.
657
# f_int1 IU f_int1 IU f_int1,f_int2 R
658
# f_int2 IU f_int2 IU f_int1,f_int2 R
659
# f_int1,f_int2 IU f_int1,f_int2 R
661
# IU column = INSERT .. ON DUPLICATE KEY UPDATE column
664
# Current state of the data
665
# 1. f_int1 = f_int2, f_char1 = CAST(f_int1 AS CHAR), f_char2 = f_char1,
666
# f_charbig = CONCAT('===',f_char1,'===);
667
# 2. f_int1 FROM 1 TO @max_row_div4
668
# AND @max_row_div2 + @max_row_div4 TO @max_row
670
# Do not apply the following tests to tables without UNIQUE columns.
674
if ($f_int1_is_unique)
676
## 6.1 f_int1 is UNIQUE, UPDATE f_int1 when DUPLICATE KEY
677
# Bug#15236 Partitions: crash, if Insert .. on duplicate key causes update of existing row
678
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
679
SELECT f_int1, f_int1, '', '', 'was inserted'
680
FROM t0_template source_tab
681
WHERE MOD(f_int1,3) = 0
682
AND f_int1 BETWEEN @max_row_div2 AND @max_row
684
UPDATE f_int1 = 2 * @max_row + source_tab.f_int1,
685
f_charbig = 'was updated';
686
--source suite/parts/inc/partition_20.inc
689
if ($f_int2_is_unique)
691
## 6.2 f_int2 is UNIQUE, UPDATE f_int2 when DUPLICATE KEY
692
# Bug#15236 Partitions: crash, if Insert .. on duplicate key causes update of existing row
693
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
694
SELECT f_int1, f_int1, '', '', 'was inserted'
695
FROM t0_template source_tab
696
WHERE MOD(f_int1,3) = 0
697
AND f_int1 BETWEEN @max_row_div2 AND @max_row
699
UPDATE f_int2 = 2 * @max_row + source_tab.f_int1,
700
f_charbig = 'was updated';
701
--source suite/parts/inc/partition_20.inc
704
## 6.3 f_int1, f_int2 is UNIQUE, UPDATE f_int1, f_int2 when DUPLICATE KEY
705
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
706
SELECT f_int1, f_int1, '', '', 'was inserted'
707
FROM t0_template source_tab
708
WHERE MOD(f_int1,3) = 0
709
AND f_int1 BETWEEN @max_row_div2 AND @max_row
711
UPDATE f_int1 = 2 * @max_row + source_tab.f_int1,
712
f_int2 = 2 * @max_row + source_tab.f_int1,
713
f_charbig = 'was updated';
714
--source suite/parts/inc/partition_20.inc
717
# Bug#16782: Partitions: crash, REPLACE .. on table with PK, DUPLICATE KEY
718
REPLACE INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
719
SELECT f_int1, - f_int1, '', '', 'was inserted or replaced'
720
FROM t0_template source_tab
721
WHERE MOD(f_int1,3) = 0 AND f_int1 BETWEEN @max_row_div2 AND @max_row;
722
# DEBUG SELECT * FROM t1 ORDER BY f_int1, f_int2;
723
# Check of preceding statement via Select
728
SELECT '# check replace success: ' AS "", COUNT(*) = @try_count AS ""
729
FROM t1 WHERE f_charbig = 'was inserted or replaced';
731
# Revert the modification
733
WHERE f_int1 BETWEEN @max_row_div2 AND @max_row_div2 + @max_row_div4;
734
# If there is only UNIQUE (f1,f2) we will have pairs f_int1,f_int2
735
# <n>, <n> and <n>, <-n>
736
# where MOD(f_int1,3) = 0
737
# and f_int1 between @max_row_div2 + @max_row_div4 and @max_row.
738
# Delete the <n>, <n> records.
740
WHERE f_int1 = f_int2 AND MOD(f_int1,3) = 0 AND
741
f_int1 BETWEEN @max_row_div2 + @max_row_div4 AND @max_row;
742
UPDATE t1 SET f_int2 = f_int1,
743
f_char1 = CAST(f_int1 AS CHAR),
744
f_char2 = CAST(f_int1 AS CHAR),
745
f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===')
746
WHERE f_charbig = 'was inserted or replaced' AND f_int1 = - f_int2;
747
# DEBUG SELECT * FROM t1 ORDER BY f_int1, f_int2;
751
#-------------------------------------------------------------------------------
754
# DEBUG SELECT @max_row_div4 , @max_row_div2 + @max_row_div4;
759
SELECT COUNT(f_int1) INTO @start_count FROM t1
760
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
762
let $run= `SELECT @start_count <> 0`;
765
--echo # Prerequisites for following tests not fullfilled.
766
--echo # The content of the table t1 is unexpected
767
--echo # There must be no rows BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
768
SELECT COUNT(f_int1) FROM t1
769
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
770
--echo # Sorry, have to abort.
773
# Number of records to be inserted
778
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
780
# 7.1 Successful INSERT + COMMIT
781
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
782
SELECT f_int1, f_int1, '', '', 'was inserted'
783
FROM t0_template source_tab
784
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
785
# The inserted records must be visible (at least for our current session)
790
SELECT '# check transactions-1 success: ' AS "",
791
COUNT(*) = @exp_inserted_rows AS ""
792
FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
794
# Make the changes persistent for all storage engines
796
# The inserted records must be visible (for all open and future sessions)
801
SELECT '# check transactions-2 success: ' AS "",
802
COUNT(*) = @exp_inserted_rows AS ""
803
FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
805
# Let's assume we have a transactional engine + COMMIT is ill.
806
# A correct working ROLLBACK might revert the INSERT.
812
SELECT '# check transactions-3 success: ' AS "",
813
COUNT(*) = @exp_inserted_rows AS ""
814
FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
817
DELETE FROM t1 WHERE f_charbig = 'was inserted';
824
SELECT '# check transactions-4 success: ' AS "",
826
FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
829
# 7.2 Successful INSERT + ROLLBACK
830
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
831
SELECT f_int1, f_int1, '', '', 'was inserted'
832
FROM t0_template source_tab
833
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
838
SELECT '# check transactions-5 success: ' AS "",
839
COUNT(*) = @exp_inserted_rows AS ""
840
FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
847
SELECT COUNT(*) INTO @my_count
848
FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
849
SELECT '# check transactions-6 success: ' AS "",
850
@my_count IN (0,@exp_inserted_rows) AS "";
851
let $run= `SELECT @my_count = 0`;
854
--echo # INFO: Storage engine used for t1 seems to be transactional.
856
let $run= `SELECT @my_count = @exp_inserted_rows`;
859
--echo # INFO: Storage engine used for t1 seems to be not transactional.
862
# Let's assume we have a transactional engine + ROLLBACK is ill.
863
# A correct working COMMIT might make the inserted records again visible.
869
SELECT '# check transactions-7 success: ' AS "",
870
COUNT(*) IN (0,@exp_inserted_rows) AS ""
871
FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
874
DELETE FROM t1 WHERE f_charbig = 'was inserted';
877
# 7.3 Failing INSERT (in mid of statement processing) + COMMIT
878
SET @@session.sql_mode = 'traditional';
879
# Number of records where a INSERT has to be tried
880
SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows;
882
--disable_abort_on_error
883
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
884
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
885
'', '', 'was inserted' FROM t0_template
886
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
887
--enable_abort_on_error
889
# How many new records should be now visible ?
890
# 1. storage engine unable to revert changes made by the failing statement
891
# @max_row_div2 - 1 - @max_row_div4 + 1
892
# 2. storage engine able to revert changes made by the failing statement
898
SELECT COUNT(*) INTO @my_count
899
FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
900
SELECT '# check transactions-8 success: ' AS "",
901
@my_count IN (@max_row_div2 - 1 - @max_row_div4 + 1,0) AS "";
902
let $run= `SELECT @my_count = @max_row_div2 - 1 - @max_row_div4 + 1`;
905
--echo # INFO: Storage engine used for t1 seems to be unable to revert
906
--echo # changes made by the failing statement.
908
let $run= `SELECT @my_count = 0`;
911
--echo # INFO: Storage engine used for t1 seems to be able to revert
912
--echo # changes made by the failing statement.
915
SET @@session.sql_mode = '';
918
DELETE FROM t1 WHERE f_charbig = 'was inserted';
923
SELECT * FROM t1 ORDER BY f_int1;
927
#-------------------------------------------------------------------------------
928
# 8 Some special cases
929
# 8.1 Dramatic increase of the record/partition/subpartition/table sizes
930
UPDATE t1 SET f_charbig = REPEAT('b', 1000);
931
# partial check of preceding statement via Select
936
eval SELECT '# check special-1 success: ' AS "",1 AS "" FROM t1
937
WHERE f_int1 = 1 AND f_charbig = REPEAT('b', 1000);
940
# 8.2 Dramatic decrease of the record/partition/subpartition/table sizes
941
UPDATE t1 SET f_charbig = '';
942
# partial check of preceding statement via Select
947
eval SELECT '# check special-2 success: ' AS "",1 AS "" FROM t1
948
WHERE f_int1 = 1 AND f_charbig = '';
951
UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===');
955
SELECT * FROM t1 ORDER BY f_int1;
959
#-------------------------------------------------------------------------------
962
# 9.1 BEFORE/AFTER INSERT/UPDATE/DELETE TRIGGER on table t0_aux causes that
963
# column values used in partitioning function of t1 are changed.
964
let $tab_has_trigg= t0_aux;
965
let $tab_in_trigg= t1;
967
# Insert three records, which will be updated by the trigger
968
# Bug#18735: Partitions: NDB, UNIQUE INDEX, UPDATE, strange server response
969
eval INSERT INTO $tab_in_trigg(f_int1,f_int2,f_char1,f_char2,f_charbig)
970
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
971
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
973
let $statement= INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig)
974
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
975
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
976
let $event= BEFORE INSERT;
977
--source suite/parts/inc/partition_trigg1.inc
978
let $event= AFTER INSERT;
979
--source suite/parts/inc/partition_trigg1.inc
981
let $statement= UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2
982
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
983
let $event= BEFORE UPDATE;
984
--source suite/parts/inc/partition_trigg1.inc
985
let $event= AFTER UPDATE;
986
--source suite/parts/inc/partition_trigg1.inc
988
let $statement= DELETE FROM t0_aux
989
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
990
let $event= BEFORE DELETE;
991
--source suite/parts/inc/partition_trigg1.inc
992
let $event= AFTER DELETE;
993
--source suite/parts/inc/partition_trigg1.inc
996
eval DELETE FROM $tab_in_trigg
997
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
999
# Two currently (February 2006) impossible operations.
1000
# 1442: 'Can't update table 't1' in stored function/trigger because it is
1001
# already used by statement which invoked this stored function/trigger.'
1002
# 1362: 'Updating of OLD row is not allowed in trigger'
1006
SELECT * FROM t1 ORDER BY f_int1;
1009
if ($more_trigger_tests)
1011
# 9.2 BEFORE/AFTER INSERT/UPDATE/DELETE TRIGGER on partitioned table t1 causes
1012
# that column values in not partitioned table t0_aux are changed.
1013
let $tab_has_trigg= t1;
1014
let $tab_in_trigg= t0_aux;
1016
# Insert three records, which will be updated by the trigger
1017
eval INSERT INTO $tab_in_trigg(f_int1,f_int2,f_char1,f_char2,f_charbig)
1018
SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template
1019
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1021
let $statement= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
1022
SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template
1023
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1024
let $event= BEFORE INSERT;
1025
--source suite/parts/inc/partition_trigg1.inc
1026
let $event= AFTER INSERT;
1027
--source suite/parts/inc/partition_trigg1.inc
1029
let $statement= UPDATE t1 SET f_int1 = - f_int1, f_int2 = - f_int2
1030
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1031
let $event= BEFORE UPDATE;
1032
--source suite/parts/inc/partition_trigg1.inc
1033
let $event= AFTER UPDATE;
1034
--source suite/parts/inc/partition_trigg1.inc
1036
let $statement= DELETE FROM t1
1037
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));
1038
let $event= BEFORE DELETE;
1039
--source suite/parts/inc/partition_trigg1.inc
1040
let $event= AFTER DELETE;
1041
--source suite/parts/inc/partition_trigg1.inc
1042
eval DELETE FROM $tab_in_trigg
1043
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
1048
SELECT * FROM t1 ORDER BY f_int1;
1051
# 9.3 BEFORE/AFTER UPDATE TRIGGER on partitioned table causes that the value
1052
# of columns in partitioning function is recalculated
1053
if ($more_trigger_tests)
1055
# 9.3.1 The UPDATE itself changes a column which is not used in the partitioning
1057
# "old" values are used as source within the trigger.
1058
let $statement= UPDATE t1
1059
SET f_charbig = '####updated per update statement itself####';
1061
let $event= BEFORE UPDATE;
1062
--source suite/parts/inc/partition_trigg2.inc
1063
# FIXME when AFTER TRIGGER can be used
1064
# Currently (2006-02-23) a AFTER TRIGGER is not allowed to modify a row, which
1065
# was just modified: 1362: Updating of NEW row is not allowed in after trigger
1068
# 9.3.2 The UPDATE itself changes a column which is used in the partitioning
1070
let $statement= UPDATE t1
1071
SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row,
1072
f_charbig = '####updated per update statement itself####';
1074
# 9.3.2.1 "old" values are used as source within the trigger.
1076
let $event= BEFORE UPDATE;
1077
--source suite/parts/inc/partition_trigg2.inc
1078
# FIXME when AFTER TRIGGER can be used
1079
# Currently (2006-02-23) a AFTER TRIGGER is not allowed to modify a row, which
1080
# was just modified: 1362: Updating of NEW row is not allowed in after trigger
1081
# 9.3.2.2 "new" values are used as source within the trigger.
1083
let $event= BEFORE UPDATE;
1084
--source suite/parts/inc/partition_trigg2.inc
1085
# FIXME when AFTER TRIGGER can be used
1089
SELECT * FROM t1 ORDER BY f_int1;
1092
# 9.4 BEFORE/AFTER INSERT TRIGGER on partitioned table causes that the value of
1093
# columns in partitioning function is recalculated.
1094
# 9.4.1 INSERT assigns values to the recalculate columns
1095
let $statement= INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
1096
SELECT f_int1, f_int1, CAST(f_int1 AS CHAR),
1097
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1098
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1100
let $event= BEFORE INSERT;
1102
--source suite/parts/inc/partition_trigg3.inc
1103
# FIXME when AFTER TRIGGER can be used
1105
# 9.4.2 INSERT assigns no values to the recalculate columns
1106
let $statement= INSERT INTO t1 (f_char1, f_char2, f_charbig)
1107
SELECT CAST(f_int1 AS CHAR),
1108
CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template
1109
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1
1111
let $event= BEFORE INSERT;
1113
--source suite/parts/inc/partition_trigg3.inc
1114
# FIXME when AFTER TRIGGER can be used
1118
SELECT * FROM t1 ORDER BY f_int1;
1122
#-------------------------------------------------------------------------------
1123
# 10 ANALYZE/CHECK/CHECKSUM
1125
CHECK TABLE t1 EXTENDED;
1126
# Checksum depends on @max_row so we have to unify the value
1127
--replace_column 2 <some_value>
1128
CHECKSUM TABLE t1 EXTENDED;
1131
#-------------------------------------------------------------------------------
1132
# 11 Some special statements, which may lead to a rebuild of the trees
1133
# depending on the storage engine and some particular conditions
1134
# 11.1 OPTIMIZE TABLE
1135
# Manual about OPTIMIZE <InnoDB table>:
1136
# ... , it is mapped to ALTER TABLE, which rebuilds the table.
1137
# Rebuilding updates index statistics and frees unused space in the
1139
# FIXME What will happen with NDB ?
1141
--source suite/parts/inc/partition_layout_check2.inc
1143
REPAIR TABLE t1 EXTENDED;
1144
--source suite/parts/inc/partition_layout_check2.inc
1147
# Manual about TRUNCATE on tables ( != InnoDB table with FOREIGN KEY ):
1148
# Truncate operations drop and re-create the table ....
1150
# Check of preceding statement via Select
1155
SELECT '# check TRUNCATE success: ' AS "",COUNT(*) = 0 AS "" FROM t1;
1157
--source suite/parts/inc/partition_layout_check2.inc
1158
--echo # End usability test (inc/partition_check.inc)
added
removed
mysql-test/suite/parts/inc/partition_check.inc
