partition_1.inc

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

-- source include/have_partition.inc

# include/partition_1.inc
#
# Partitionong tests
#
# Attention: The variable
#                 $engine            -- Storage engine to be tested.
#            must be set within the script sourcing this file.
#
--disable_abort_on_error
SET AUTOCOMMIT= 1;

##### Disabled testcases, because of open bugs #####
--echo
--echo #------------------------------------------------------------------------
--echo # There are several testcases disabled because ouf the open bugs 
--echo # #15407 , #15408 , #15890 , #15961 , #13447 , #15966 , #15968, #16370
--echo #------------------------------------------------------------------------
# Bug#15407 Partitions: crash if subpartition
let $fixed_bug15407= 0;
# Bug#15408 Partitions: subpartition names are not unique
let $fixed_bug15408= 0;
# Bug#15890 Partitions: Strange interpretation of partition number
let $fixed_bug15890= 0;
# Bug#15961 Partitions: Creation of subpart. table without subpart. rule not rejected
let $fixed_bug15961= 0;
# Bug#13447 Partitions: crash with alter table
let $fixed_bug13447= 0;
# Bug#15966 Partitions: crash if session default engine <> engine used in create table
let $fixed_bug15966= 0;
# Bug#15968 Partitions: crash when INSERT with f1 = -1 into PARTITION BY HASH(f1)
let $fixed_bug15968= 0;
# Bug #16370  	Partitions: subpartitions names not mentioned in SHOW CREATE TABLE output
let $fixed_bug16370= 0;

##### Option, for displaying files #####
#
# Attention: Displaying the directory content via "ls var/master-data/test/t*"
#            is probably not portable.
#            let $ls= 0; disables the execution of "ls ....."
let $ls= 0;

################################################################################
#                     Partitioning syntax
#
# CREATE TABLE .... (column-list ..)
# PARTITION BY
#     KEY '(' ( column-list ) ')'
#   | RANGE '(' ( expr ) ')'
#   | LIST '(' ( expr ) ')'
#   | HASH '(' ( expr ) ')'
# [PARTITIONS num ]
# [SUBPARTITION BY
#   KEY '(' ( column-list ) ')'
#   | HASH '(' ( expr ) ')'
#   [SUBPARTITIONS num ]
# ]
# [ '('
#      ( PARTITION logical-name
#       [ VALUES LESS THAN '(' ( expr | MAX_VALUE ) ')' ]
#       [ VALUES IN '(' (expr)+ ')' ]
#       [ TABLESPACE tablespace-name ]
#       [ [ STORAGE ] ENGINE [ '=' ] storage-engine-name ]
#       [ NODEGROUP nodegroup-id ]
#       [ '('
#            ( SUBPARTITION logical-name
#              [ TABLESPACE tablespace-name ]
#              [ STORAGE ENGINE = storage-engine-name ]
#              [ NODEGROUP nodegroup-id ]
#            )+
#         ')'
#      )+
#   ')'
# ]
################################################################################

--echo
--echo #------------------------------------------------------------------------
--echo #  0. Setting of auxiliary variables + Creation of an auxiliary table
--echo #     needed in all testcases
--echo #------------------------------------------------------------------------
let $max_row= `SELECT @max_row`;
let $max_row_div2= `SELECT @max_row DIV 2`;
let $max_row_div3= `SELECT @max_row DIV 3`;
let $max_row_div4= `SELECT @max_row DIV 4`;
let $max_int_4= 2147483647;
--disable_warnings
DROP TABLE IF EXISTS t0_template;
--enable_warnings
CREATE TABLE t0_template ( f1 INTEGER, f2 char(20), PRIMARY KEY(f1))
ENGINE = MEMORY;
--echo #     Logging of <max_row> INSERTs into t0_template suppressed
--disable_query_log
let $num= $max_row;
while ($num)
{
  eval INSERT INTO t0_template SET f1 = $num, f2 = '---$num---';

  dec $num;
}
--enable_query_log

--echo
--echo #------------------------------------------------------------------------
--echo #  1. Some syntax checks
--echo #------------------------------------------------------------------------
--echo #  1.1 Subpartioned table without subpartitioning rule must be rejected
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
if ($fixed_bug15961)
{
# Bug#15961 Partitions: Creation of subpart. table without subpart. rule not rejected
--error 9999
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
( PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11));
}
--echo #  FIXME Implement testcases, where it is checked that all create and
--echo #  alter table statements
--echo #  - with missing mandatory parameters are rejected
--echo #  - with optional parameters are accepted
--echo #  - with wrong combinations of optional parameters are rejected
--echo #  - ............

--echo
--echo #------------------------------------------------------------------------
--echo #  2. Checks where the engine is assigned on all supported (CREATE TABLE
--echo #     statement) positions + basic operations on the tables
--echo #        Storage engine mixups are currently (2005-12-23) not supported
--echo #------------------------------------------------------------------------
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

--echo # 2.1 non partitioned table (for comparison)
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = $engine;
# MLML Full size (as check of check routine)
--source include/partition_10.inc
DROP TABLE t1;
#
--echo # 2.2 Assignment of storage engine just after column list only
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = $engine
     PARTITION BY HASH(f1) PARTITIONS 2;
--source include/partition_10.inc
DROP TABLE t1;
#
--echo # 2.3 Assignment of storage engine just after partition or subpartition
--echo #     name only
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
     PARTITION BY HASH(f1)
     ( PARTITION part1 STORAGE ENGINE = $engine,
       PARTITION part2 STORAGE ENGINE = $engine
     );
--source include/partition_10.inc
DROP TABLE t1;
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
     PARTITION BY RANGE(f1)
     SUBPARTITION BY HASH(f1)
     ( PARTITION part1 VALUES LESS THAN ($max_row_div2)
          (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
           SUBPARTITION subpart12 STORAGE ENGINE = $engine),
       PARTITION part2 VALUES LESS THAN ($max_int_4)
          (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
           SUBPARTITION subpart22 STORAGE ENGINE = $engine)
     );
--source include/partition_10.inc
DROP TABLE t1;
#
--echo # 2.4 Some but not all named partitions or subpartitions get a storage
--echo #     engine assigned
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
     PARTITION BY HASH(f1)
     ( PARTITION part1 STORAGE ENGINE = $engine,
       PARTITION part2
     );
--source include/partition_10.inc
DROP TABLE t1;
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
     PARTITION BY HASH(f1)
     ( PARTITION part1                         ,
       PARTITION part2 STORAGE ENGINE = $engine
     );
--source include/partition_10.inc
DROP TABLE t1;
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
     PARTITION BY RANGE(f1)
     SUBPARTITION BY HASH(f1)
     ( PARTITION part1 VALUES LESS THAN ($max_row_div2)
          (SUBPARTITION subpart11,
           SUBPARTITION subpart12 STORAGE ENGINE = $engine),
       PARTITION part2 VALUES LESS THAN ($max_int_4)
          (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
           SUBPARTITION subpart22 STORAGE ENGINE = $engine)
     );
--source include/partition_10.inc
DROP TABLE t1;
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
     PARTITION BY RANGE(f1)
     SUBPARTITION BY HASH(f1)
     ( PARTITION part1 VALUES LESS THAN ($max_row_div2)
          (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
           SUBPARTITION subpart12 STORAGE ENGINE = $engine),
       PARTITION part2 VALUES LESS THAN ($max_int_4)
          (SUBPARTITION subpart21,
           SUBPARTITION subpart22 )
     );
--source include/partition_10.inc
DROP TABLE t1;
#
--echo # 2.5 Storage engine assignment after partition name + after name of
--echo #     subpartitions belonging to another partition
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
     PARTITION BY RANGE(f1)
     SUBPARTITION BY HASH(f1)
     ( PARTITION part1 VALUES LESS THAN ($max_row_div2) ENGINE = $engine
          (SUBPARTITION subpart11,
           SUBPARTITION subpart12),
       PARTITION part2 VALUES LESS THAN ($max_int_4)
          (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
           SUBPARTITION subpart22 STORAGE ENGINE = $engine)
     );
--source include/partition_10.inc
DROP TABLE t1;
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
     PARTITION BY RANGE(f1)
     SUBPARTITION BY HASH(f1)
     ( PARTITION part1 VALUES LESS THAN ($max_row_div2)
          (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
           SUBPARTITION subpart12 STORAGE ENGINE = $engine),
       PARTITION part2 VALUES LESS THAN ($max_int_4) ENGINE = $engine
          (SUBPARTITION subpart21,
           SUBPARTITION subpart22)
     );
--source include/partition_10.inc
DROP TABLE t1;
#
--echo # 2.6 Precedence of storage engine assignments
--echo # 2.6.1 Storage engine assignment after column list + after partition
--echo #       or subpartition name
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = $engine
     PARTITION BY HASH(f1)
     ( PARTITION part1 STORAGE ENGINE = $engine,
       PARTITION part2 STORAGE ENGINE = $engine
     );
--source include/partition_10.inc
DROP TABLE t1;
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = $engine
     PARTITION BY RANGE(f1)
     SUBPARTITION BY HASH(f1)
     ( PARTITION part1 VALUES LESS THAN ($max_row_div2)
          (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
           SUBPARTITION subpart12 STORAGE ENGINE = $engine),
       PARTITION part2 VALUES LESS THAN ($max_int_4)
          (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
           SUBPARTITION subpart22 STORAGE ENGINE = $engine)
     );
--source include/partition_10.inc
DROP TABLE t1;
--echo # 2.6.2 Storage engine assignment after partition name + after 
--echo #       subpartition name
#     in partition part + in sub partition part
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
     PARTITION BY RANGE(f1)
     SUBPARTITION BY HASH(f1)
     ( PARTITION part1 VALUES LESS THAN ($max_row_div2) STORAGE ENGINE = $engine
          (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
           SUBPARTITION subpart12 STORAGE ENGINE = $engine),
       PARTITION part2 VALUES LESS THAN ($max_int_4)
          (SUBPARTITION subpart21 STORAGE ENGINE = $engine,
           SUBPARTITION subpart22 STORAGE ENGINE = $engine)
     );
--source include/partition_10.inc
DROP TABLE t1;

--echo # 2.7 Session default engine differs from engine used within create table
eval SET SESSION storage_engine=$engine_other;
if ($fixed_bug15966)
{
# Bug#15966 Partitions: crash if session default engine <> engine used in create table
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) ( PARTITION part1 ENGINE = $engine);
--source include/partition_10.inc
DROP TABLE t1;
# Bug#15966 Partitions: crash if session default engine <> engine used in create table
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
     PARTITION BY RANGE(f1)
     SUBPARTITION BY HASH(f1)
     ( PARTITION part1 VALUES LESS THAN (1000)
          (SUBPARTITION subpart11 STORAGE ENGINE = $engine,
           SUBPARTITION subpart12 STORAGE ENGINE = $engine));
--source include/partition_10.inc
DROP TABLE t1;
}
eval SET SESSION storage_engine=$engine;


--echo
--echo #------------------------------------------------------------------------
--echo #  3. Check assigning the number of partitions and subpartitions
--echo #     with and without named partitions/subpartitions
--echo #------------------------------------------------------------------------
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
--echo # 3.1 (positive) without partition/subpartition number assignment
--echo # 3.1.1 no partition number, no named partitions
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1);
--source include/partition_10.inc
DROP TABLE t1;
--echo # 3.1.2 no partition number, named partitions
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part2);
--source include/partition_10.inc
DROP TABLE t1;
# Attention: Several combinations are impossible
#           If subpartitioning exists
#           - partitioning algorithm must be RANGE or LIST
#             This implies the assignment of named partitions.
#           - subpartitioning algorithm must be HASH or KEY
--echo # 3.1.3 variations on no partition/subpartition number, named partitions,
--echo #       different subpartitions are/are not named
#
# Partition name   -- "properties"
#    part1         --     first/non last
#    part2         -- non first/non last
#    part3         -- non first/    last
#
# Testpattern:
#         named subpartitions in
# Partition part1 part2 part3
#             N     N     N
#             N     N     Y
#             N     Y     N
#             N     Y     Y
#             Y     N     N
#             Y     N     Y
#             Y     Y     N
#             Y     Y     Y
--disable_query_log
let $part0= CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1);
#
eval SET @aux = '(PARTITION part1 VALUES LESS THAN ($max_row_div2),';
let $part1_N= `SELECT @AUX`;
eval SET @aux = '(PARTITION part1 VALUES LESS THAN ($max_row_div2)
(SUBPARTITION subpart11 , SUBPARTITION subpart12 ),';
let $part1_Y= `SELECT @AUX`;
#
eval SET @aux = 'PARTITION part2 VALUES LESS THAN ($max_row),';
let $part2_N= `SELECT @AUX`;
eval SET @aux = 'PARTITION part2 VALUES LESS THAN ($max_row)
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ),';
let $part2_Y= `SELECT @AUX`;
#
eval SET @aux = 'PARTITION part3 VALUES LESS THAN ($max_int_4))';
let $part3_N= `SELECT @AUX`;
eval SET @aux = 'PARTITION part3 VALUES LESS THAN ($max_int_4)
(SUBPARTITION subpart31 , SUBPARTITION subpart32 ))';
let $part3_Y= `SELECT @AUX`;
--enable_query_log

eval $part0 $part1_N $part2_N $part3_N ;
DROP TABLE t1;
# Bug#15407 Partitions: crash if subpartition
if ($fixed_bug15407)
{
eval $part0 $part1_N $part2_N $part3_Y ;
--source include/partition_10.inc
DROP TABLE t1;
eval $part0 $part1_N $part2_Y $part3_N ;
--source include/partition_10.inc
DROP TABLE t1;
eval $part0 $part1_N $part2_Y $part3_Y ;