mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
0a5e4a0191
Updated tests: cases with bugs or which cannot be run with the cursor-protocol were excluded with "--disable_cursor_protocol"/"--enable_cursor_protocol" Fix for v.10.5
203 lines
8.4 KiB
PHP
203 lines
8.4 KiB
PHP
################################################################################
|
|
# inc/partition_alter3.inc #
|
|
# #
|
|
# Purpose: #
|
|
# Tests for partition management commands for HASH and KEY partitioning #
|
|
# #
|
|
#------------------------------------------------------------------------------#
|
|
# Original Author: mleich #
|
|
# Original Date: 2006-04-11 #
|
|
# Change Author: #
|
|
# Change Date: #
|
|
# Change: #
|
|
################################################################################
|
|
|
|
--echo
|
|
--echo #========================================================================
|
|
--echo # 1. Partition management commands on HASH partitioned table
|
|
--echo # column in partitioning function is of type DATE
|
|
--echo #========================================================================
|
|
# 1. Create the table
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1;
|
|
--enable_warnings
|
|
eval CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30));
|
|
# 2. Fill the table t1 with records
|
|
INSERT INTO t1 (f_date, f_varchar)
|
|
SELECT CONCAT(CAST((f_int1 + 999) AS CHAR),'-02-10'), CAST(f_char1 AS CHAR)
|
|
FROM t0_template
|
|
WHERE f_int1 + 999 BETWEEN 1000 AND 9999;
|
|
# 3. Calculate the number of inserted records.
|
|
--disable_cursor_protocol
|
|
SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1)
|
|
INTO @exp_row_count;
|
|
--enable_cursor_protocol
|
|
# DEBUG SELECT @exp_row_count;
|
|
# 4. Print the layout, check Readability
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read1.inc
|
|
#
|
|
--echo #------------------------------------------------------------------------
|
|
--echo # 1.1 Increase number of PARTITIONS
|
|
--echo #------------------------------------------------------------------------
|
|
--echo # 1.1.1 ADD PARTITION to not partitioned table --> must fail
|
|
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
|
|
#
|
|
--echo # 1.1.2 Assign HASH partitioning
|
|
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read1.inc
|
|
#
|
|
--echo # 1.1.3 Assign other HASH partitioning to already partitioned table
|
|
--echo # + test and switch back + test
|
|
ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date));
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read1.inc
|
|
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read1.inc
|
|
#
|
|
--echo # 1.1.4 Add PARTITIONS not fitting to HASH --> must fail
|
|
--error ER_PARTITION_WRONG_VALUES_ERROR
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
|
|
--error ER_PARTITION_WRONG_VALUES_ERROR
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
|
|
#
|
|
--echo # 1.1.5 Add two named partitions + test
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read1.inc
|
|
#
|
|
--echo # 1.1.6 Add two named partitions, name clash --> must fail
|
|
--error ER_SAME_NAME_PARTITION
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
|
|
#
|
|
--echo # 1.1.7 Add one named partition + test
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read1.inc
|
|
#
|
|
--echo # 1.1.8 Add four not named partitions + test
|
|
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read1.inc
|
|
|
|
--echo #------------------------------------------------------------------------
|
|
--echo # 1.2 Decrease number of PARTITIONS
|
|
--echo #------------------------------------------------------------------------
|
|
--echo # 1.2.1 DROP PARTITION is not supported for HASH --> must fail
|
|
--error ER_ONLY_ON_RANGE_LIST_PARTITION
|
|
ALTER TABLE t1 DROP PARTITION part1;
|
|
#
|
|
--echo # 1.2.2 COALESCE PARTITION partitionname is not supported
|
|
--error ER_PARSE_ERROR
|
|
ALTER TABLE t1 COALESCE PARTITION part1;
|
|
#
|
|
--echo # 1.2.3 Decrease by 0 is non sense --> must fail
|
|
--error ER_COALESCE_PARTITION_NO_PARTITION
|
|
ALTER TABLE t1 COALESCE PARTITION 0;
|
|
#
|
|
--echo # 1.2.4 COALESCE one partition + test loop
|
|
let $loop= 7;
|
|
while ($loop)
|
|
{
|
|
ALTER TABLE t1 COALESCE PARTITION 1;
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read1.inc
|
|
dec $loop;
|
|
}
|
|
--echo # 1.2.5 COALESCE of last partition --> must fail
|
|
--error ER_DROP_LAST_PARTITION
|
|
ALTER TABLE t1 COALESCE PARTITION 1;
|
|
#
|
|
--echo # 1.2.6 Remove partitioning
|
|
ALTER TABLE t1 REMOVE PARTITIONING;
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read1.inc
|
|
#
|
|
--echo # 1.2.7 Remove partitioning from not partitioned table --> ????
|
|
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
|
|
ALTER TABLE t1 REMOVE PARTITIONING;
|
|
DROP TABLE t1;
|
|
--source suite/parts/inc/partition_check_drop.inc
|
|
|
|
--echo
|
|
--echo #========================================================================
|
|
--echo # 2. Partition management commands on KEY partitioned table
|
|
--echo #========================================================================
|
|
# 1. Create the table
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1;
|
|
--enable_warnings
|
|
eval CREATE TABLE t1 (
|
|
$column_list
|
|
);
|
|
# 2. Fill the table t1 with some records
|
|
eval $insert_all;
|
|
# 4. Print the layout, check Readability
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read2.inc
|
|
#
|
|
--echo #------------------------------------------------------------------------
|
|
--echo # 2.1 Increase number of PARTITIONS
|
|
--echo # Some negative testcases are omitted (already checked with HASH).
|
|
--echo #------------------------------------------------------------------------
|
|
--echo # 2.1.1 Assign KEY partitioning
|
|
ALTER TABLE t1 PARTITION BY KEY(f_int1);
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read2.inc
|
|
#
|
|
--echo # 2.1.2 Add PARTITIONS not fitting to KEY --> must fail
|
|
--error ER_PARTITION_WRONG_VALUES_ERROR
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
|
|
--error ER_PARTITION_WRONG_VALUES_ERROR
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
|
|
#
|
|
--echo # 2.1.3 Add two named partitions + test
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read2.inc
|
|
#
|
|
--echo # 2.1.4 Add one named partition + test
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read2.inc
|
|
#
|
|
--echo # 2.1.5 Add four not named partitions + test
|
|
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read2.inc
|
|
|
|
--echo #------------------------------------------------------------------------
|
|
--echo # 2.2 Decrease number of PARTITIONS
|
|
--echo # Some negative testcases are omitted (already checked with HASH).
|
|
--echo #------------------------------------------------------------------------
|
|
--echo # 2.2.1 DROP PARTITION is not supported for KEY --> must fail
|
|
--error ER_ONLY_ON_RANGE_LIST_PARTITION
|
|
ALTER TABLE t1 DROP PARTITION part1;
|
|
#
|
|
--echo # 2.2.4 COALESCE one partition + test loop
|
|
let $loop= 7;
|
|
while ($loop)
|
|
{
|
|
ALTER TABLE t1 COALESCE PARTITION 1;
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read2.inc
|
|
dec $loop;
|
|
}
|
|
--echo # 2.2.5 COALESCE of last partition --> must fail
|
|
--error ER_DROP_LAST_PARTITION
|
|
ALTER TABLE t1 COALESCE PARTITION 1;
|
|
#
|
|
--echo # 2.2.6 Remove partitioning
|
|
ALTER TABLE t1 REMOVE PARTITIONING;
|
|
--source suite/parts/inc/partition_layout.inc
|
|
--source suite/parts/inc/partition_check_read2.inc
|
|
#
|
|
--echo # 2.2.7 Remove partitioning from not partitioned table --> ????
|
|
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
|
|
ALTER TABLE t1 REMOVE PARTITIONING;
|
|
DROP TABLE t1;
|
|
--source suite/parts/inc/partition_check_drop.inc
|
|
|