partition_02myisam.result

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

SET SESSION storage_engine='MYISAM';
SET @max_row = 200;
SET AUTOCOMMIT= 1;

#------------------------------------------------------------------------
# There are several testcases disabled because ouf the open bugs 
# #15407 , #15408 , #15890 , #15961 , #13447 , #15966 , #15968, #16370
#------------------------------------------------------------------------

#------------------------------------------------------------------------
#  0. Setting of auxiliary variables + Creation of an auxiliary table
#     needed in all testcases
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t0_template;
CREATE TABLE t0_template ( f1 INTEGER, f2 char(20), PRIMARY KEY(f1))
ENGINE = MEMORY;
#     Logging of <max_row> INSERTs into t0_template suppressed

#------------------------------------------------------------------------
#  1. Some syntax checks
#------------------------------------------------------------------------
#  1.1 Subpartioned table without subpartitioning rule must be rejected
DROP TABLE IF EXISTS t1;
#  FIXME Implement testcases, where it is checked that all create and
#  alter table statements
#  - with missing mandatory parameters are rejected
#  - with optional parameters are accepted
#  - with wrong combinations of optional parameters are rejected
#  - ............

#------------------------------------------------------------------------
#  2. Checks where the engine is assigned on all supported (CREATE TABLE
#     statement) positions + basic operations on the tables
#        Storage engine mixups are currently (2005-12-23) not supported
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t1;
# 2.1 non partitioned table (for comparison)
CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM';
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
# 2.2 Assignment of storage engine just after column list only
CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM'
PARTITION BY HASH(f1) PARTITIONS 2;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) PARTITIONS 2 
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
# 2.3 Assignment of storage engine just after partition or subpartition
#     name only
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1)
( PARTITION part1 STORAGE ENGINE = 'MYISAM',
PARTITION part2 STORAGE ENGINE = 'MYISAM'
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part2  ENGINE = MyISAM)
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1)
( PARTITION part1 VALUES LESS THAN (100)
(SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM',
SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM'),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM',
SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM')
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (100)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
# 2.4 Some but not all named partitions or subpartitions get a storage
#     engine assigned
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1)
( PARTITION part1 STORAGE ENGINE = 'MYISAM',
PARTITION part2
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part2  ENGINE = MyISAM)
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1)
( PARTITION part1                         ,
PARTITION part2 STORAGE ENGINE = 'MYISAM'
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part2  ENGINE = MyISAM)
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';