diff options
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/ndb_autodiscover.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/ndb_bitfield.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/ndb_gis.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/ndb_partition_key.result | 9 | ||||
| -rw-r--r-- | mysql-test/r/partition.result | 129 | ||||
| -rw-r--r-- | mysql-test/r/partition_02myisam.result | 1724 | ||||
| -rw-r--r-- | mysql-test/r/partition_03ndb.result | 1361 | ||||
| -rw-r--r-- | mysql-test/r/partition_error.result | 5 | ||||
| -rw-r--r-- | mysql-test/r/partition_mgm_err.result | 41 |
9 files changed, 3252 insertions, 25 deletions
diff --git a/mysql-test/r/ndb_autodiscover.result b/mysql-test/r/ndb_autodiscover.result index 813e37e8892..cb85c4ac873 100644 --- a/mysql-test/r/ndb_autodiscover.result +++ b/mysql-test/r/ndb_autodiscover.result @@ -110,7 +110,7 @@ t3 CREATE TABLE `t3` ( `id` int(11) NOT NULL, `name` char(255) default NULL, PRIMARY KEY (`id`) -) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1 +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY () select * from t3; id name 1 Explorer diff --git a/mysql-test/r/ndb_bitfield.result b/mysql-test/r/ndb_bitfield.result index 0b7d09dd9b9..b719a2c220e 100644 --- a/mysql-test/r/ndb_bitfield.result +++ b/mysql-test/r/ndb_bitfield.result @@ -9,7 +9,7 @@ t1 CREATE TABLE `t1` ( `pk1` int(11) NOT NULL, `b` bit(64) default NULL, PRIMARY KEY (`pk1`) -) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1 +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY () insert into t1 values (0,b'1111111111111111111111111111111111111111111111111111111111111111'), (1,b'1000000000000000000000000000000000000000000000000000000000000000'), diff --git a/mysql-test/r/ndb_gis.result b/mysql-test/r/ndb_gis.result index f49572b893b..5f8eb299093 100644 --- a/mysql-test/r/ndb_gis.result +++ b/mysql-test/r/ndb_gis.result @@ -13,7 +13,7 @@ Table Create Table gis_point CREATE TABLE `gis_point` ( `fid` int(11) default NULL, `g` point default NULL -) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1 +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY () SHOW FIELDS FROM gis_point; Field Type Null Key Default Extra fid int(11) YES NULL @@ -471,7 +471,7 @@ Table Create Table gis_point CREATE TABLE `gis_point` ( `fid` int(11) default NULL, `g` point default NULL -) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1 +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY () SHOW FIELDS FROM gis_point; Field Type Null Key Default Extra fid int(11) YES NULL diff --git a/mysql-test/r/ndb_partition_key.result b/mysql-test/r/ndb_partition_key.result index 415b9d37b4d..3cfbca63c1f 100644 --- a/mysql-test/r/ndb_partition_key.result +++ b/mysql-test/r/ndb_partition_key.result @@ -80,3 +80,12 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY USING HASH (`a`,`b`,`c`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY (b) DROP TABLE t1; +CREATE TABLE t1 (a int not null primary key) +PARTITION BY KEY(a) +(PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB); +drop table t1; +CREATE TABLE t1 (a int not null primary key); +ALTER TABLE t1 +PARTITION BY KEY(a) +(PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB); +drop table t1; diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 58f02681682..7ceee9c17ff 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -65,6 +65,8 @@ partitions 3 (partition x1 tablespace ts1, partition x2 tablespace ts2, partition x3 tablespace ts3); +CREATE TABLE t2 LIKE t1; +drop table t2; drop table t1; CREATE TABLE t1 ( a int not null, @@ -108,6 +110,127 @@ insert into t1 values (3); insert into t1 values (4); UNLOCK TABLES; drop table t1; +CREATE TABLE t1 (a int, name VARCHAR(50), purchased DATE) +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (3), +PARTITION p1 VALUES LESS THAN (7), +PARTITION p2 VALUES LESS THAN (9), +PARTITION p3 VALUES LESS THAN (11)); +INSERT INTO t1 VALUES +(1, 'desk organiser', '2003-10-15'), +(2, 'CD player', '1993-11-05'), +(3, 'TV set', '1996-03-10'), +(4, 'bookcase', '1982-01-10'), +(5, 'exercise bike', '2004-05-09'), +(6, 'sofa', '1987-06-05'), +(7, 'popcorn maker', '2001-11-22'), +(8, 'acquarium', '1992-08-04'), +(9, 'study desk', '1984-09-16'), +(10, 'lava lamp', '1998-12-25'); +SELECT * from t1 ORDER BY a; +a name purchased +1 desk organiser 2003-10-15 +2 CD player 1993-11-05 +3 TV set 1996-03-10 +4 bookcase 1982-01-10 +5 exercise bike 2004-05-09 +6 sofa 1987-06-05 +7 popcorn maker 2001-11-22 +8 acquarium 1992-08-04 +9 study desk 1984-09-16 +10 lava lamp 1998-12-25 +ALTER TABLE t1 DROP PARTITION p0; +SELECT * from t1 ORDER BY a; +a name purchased +3 TV set 1996-03-10 +4 bookcase 1982-01-10 +5 exercise bike 2004-05-09 +6 sofa 1987-06-05 +7 popcorn maker 2001-11-22 +8 acquarium 1992-08-04 +9 study desk 1984-09-16 +10 lava lamp 1998-12-25 +drop table t1; +CREATE TABLE t1 (a int) +PARTITION BY LIST (a) +(PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (4,5,6)); +insert into t1 values (1),(2),(3),(4),(5),(6); +select * from t1; +a +1 +2 +3 +4 +5 +6 +truncate t1; +select * from t1; +a +truncate t1; +select * from t1; +a +drop table t1; +CREATE TABLE t1 (a int, b int, primary key(a,b)) +PARTITION BY KEY(b,a) PARTITIONS 4; +insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6); +select * from t1 where a = 4; +a b +4 4 +drop table t1; +CREATE TABLE t1 (a int) +PARTITION BY LIST (a) +PARTITIONS 1 +(PARTITION x1 VALUES IN (1) ENGINE=MEMORY); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) default NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1 PARTITION BY LIST (a) (PARTITION x1 VALUES IN (1) ENGINE = MEMORY) +drop table t1; +CREATE TABLE t1 (a int, unique(a)) +PARTITION BY LIST (a) +(PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20)); +REPLACE t1 SET a = 4; +ERROR HY000: Table has no partition for value 4 +drop table t1; +CREATE TABLE t1 (a int) +PARTITION BY LIST (a) +(PARTITION x1 VALUES IN (2), PARTITION x2 VALUES IN (3)); +insert into t1 values (2), (3); +insert into t1 values (4); +ERROR HY000: Table has no partition for value 4 +insert into t1 values (1); +ERROR HY000: Table has no partition for value 1 +drop table t1; +CREATE TABLE t1 (a int) +PARTITION BY HASH(a) +PARTITIONS 5; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (a) PARTITIONS 5 +drop table t1; +CREATE TABLE t1 (a int) +PARTITION BY RANGE (a) +(PARTITION x1 VALUES LESS THAN (2)); +insert into t1 values (1); +update t1 set a = 5; +ERROR HY000: Table has no partition for value 5 +drop table t1; +CREATE TABLE t1 (a int) +PARTITION BY LIST (a) +(PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20)); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +drop table t1; +CREATE TABLE `t1` ( +`id` int(11) default NULL +) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ; +SELECT * FROM t1; +id +drop table t1; CREATE TABLE `t1` ( `id` int(11) default NULL ) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ; @@ -119,8 +242,8 @@ create table t1 partition by range (a) ( partition p0 values less than(10), partition p1 values less than (20), -partition p2 values less than maxvalue); -alter table t1 reorganise partition p2 into (partition p2 values less than (30)); +partition p2 values less than (25)); +alter table t1 reorganize partition p2 into (partition p2 values less than (30)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -139,7 +262,7 @@ PARTITION x6 VALUES LESS THAN (14), PARTITION x7 VALUES LESS THAN (16), PARTITION x8 VALUES LESS THAN (18), PARTITION x9 VALUES LESS THAN (20)); -ALTER TABLE t1 REORGANISE PARTITION x0,x1,x2 INTO +ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO (PARTITION x1 VALUES LESS THAN (6)); show create table t1; Table Create Table diff --git a/mysql-test/r/partition_02myisam.result b/mysql-test/r/partition_02myisam.result new file mode 100644 index 00000000000..647e9379360 --- /dev/null +++ b/mysql-test/r/partition_02myisam.result @@ -0,0 +1,1724 @@ +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 = '#######'; +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, +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; +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, +SUBPARTITION subpart22 ) +); +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.5 Storage engine assignment after partition name + after name of +# subpartitions belonging to another partition +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) +SUBPARTITION BY HASH(f1) +( PARTITION part1 VALUES LESS THAN (100) ENGINE = 'MYISAM' +(SUBPARTITION subpart11, +SUBPARTITION subpart12), +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; +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) ENGINE = 'MYISAM' +(SUBPARTITION subpart21, +SUBPARTITION subpart22) +); +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.6 Precedence of storage engine assignments +# 2.6.1 Storage engine assignment after column list + after partition +# or subpartition name +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM' +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)) ENGINE = 'MYISAM' +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.6.2 Storage engine assignment after partition name + after +# subpartition name +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) +SUBPARTITION BY HASH(f1) +( PARTITION part1 VALUES LESS THAN (100) STORAGE ENGINE = 'MYISAM' +(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.7 Session default engine differs from engine used within create table +SET SESSION storage_engine='MEMORY'; +SET SESSION storage_engine='MYISAM'; + +#------------------------------------------------------------------------ +# 3. Check assigning the number of partitions and subpartitions +# with and without named partitions/subpartitions +#------------------------------------------------------------------------ +DROP TABLE IF EXISTS t1; +# 3.1 (positive) without partition/subpartition number assignment +# 3.1.1 no partition number, no named partitions +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1); +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) +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; +# 3.1.2 no partition number, named partitions +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) (PARTITION part1, 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; +# 3.1.3 variations on no partition/subpartition number, named partitions, +# different subpartitions are/are not named +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) (PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (200), PARTITION part3 VALUES LESS THAN (2147483647)) ; +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 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (200) +(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ; +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 (200) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM), PARTITION part3 VALUES LESS THAN (2147483647) (SUBPARTITION subpart31 ENGINE = MyISAM, SUBPARTITION subpart32 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; +# 3.2 partition/subpartition numbers good and bad values and notations +DROP TABLE IF EXISTS t1; +# 3.2.1 partition/subpartition numbers INTEGER notation +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +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; +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS 2 +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +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) SUBPARTITIONS 2 (PARTITION part1 VALUES LESS THAN (100) , PARTITION part2 VALUES LESS THAN (2147483647) ) +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) PARTITIONS 1; +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 1 +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) +SUBPARTITIONS 1 +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +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) SUBPARTITIONS 1 (PARTITION part1 VALUES LESS THAN (100) , PARTITION part2 VALUES LESS THAN (2147483647) ) +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) PARTITIONS 0; +ERROR HY000: Number of partitions = 0 is not an allowed value +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS 0 +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR HY000: Number of subpartitions = 0 is not an allowed value +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS -1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS -1 +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1 +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 1000000; +ERROR HY000: Too many partitions were defined +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS 1000000 +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR HY000: Too many partitions were defined +# 3.2.4 partition/subpartition numbers STRING notation +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS '2'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS '2' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS '2.0'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS '2.0' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN ' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS '0.2E+1'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS '0.2E+1' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS TH' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS '2A'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS '2A' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 'A2'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS 'A2' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS ''; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS '' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 'GARBAGE'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS 'GARBAGE' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE' +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS T' at line 3 +# 3.2.5 partition/subpartition numbers other notations +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 2A; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS 2A +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS A2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS A2 +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2 +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS GARBAGE; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS GARBAGE +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THA' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS "2"; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS "2" +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2" +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS "2A"; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS "2A" +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A" +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS "A2"; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS "A2" +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2" +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS "GARBAGE"; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS "GARBAGE" +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE" +(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS T' at line 3 +# 3.3 Mixups of assigned partition/subpartition numbers and names +# 3.3.1 (positive) number of partition/subpartition +# = number of named partition/subpartition +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1, 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) +DROP TABLE t1; +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) PARTITIONS 2 +SUBPARTITION BY HASH(f1) SUBPARTITIONS 2 +( PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11, SUBPARTITION subpart12), +PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21, SUBPARTITION subpart22) +); +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 (1000) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) +DROP TABLE t1; +# 3.3.2 (positive) number of partition/subpartition , +# 0 (= no) named partition/subpartition +# already checked above +# 3.3.3 (negative) number of partitions/subpartitions +# > number of named partitions/subpartitions +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1 ) ; +ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) +SUBPARTITION BY HASH(f1) SUBPARTITIONS 2 +( PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11 ), +PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21, SUBPARTITION subpart22) +); +ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), +PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21, SUBPAR' at line 5 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) +SUBPARTITION BY HASH(f1) SUBPARTITIONS 2 +( PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11, SUBPARTITION subpart12), +PARTITION part2 VALUES LESS THAN (2000) +(SUBPARTITION subpart21 ), +PARTITION part3 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart31, SUBPARTITION subpart32) +); +ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), +PARTITION part3 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart31, SUBPAR' at line 7 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) PARTITIONS 2 +SUBPARTITION BY HASH(f1) SUBPARTITIONS 2 +( PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11, SUBPARTITION subpart12), +PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21 ) +); +ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ') +)' at line 7 +# 3.3.4 (negative) number of partitions < number of named partitions +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ; +ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) +SUBPARTITION BY HASH(f1) SUBPARTITIONS 1 +( PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11, SUBPARTITION subpart12), +PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21, SUBPARTITION subpart22) +); +ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), +PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21, SUBPAR' at line 5 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) +SUBPARTITION BY HASH(f1) SUBPARTITIONS 1 +( PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11, SUBPARTITION subpart12), +PARTITION part2 VALUES LESS THAN (2000) +(SUBPARTITION subpart21 ), +PARTITION part3 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart31, SUBPARTITION subpart32) +); +ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), +PARTITION part2 VALUES LESS THAN (2000) +(SUBPARTITION subpart21 ' at line 5 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) +SUBPARTITION BY HASH(f1) SUBPARTITIONS 1 +( PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11, SUBPARTITION subpart12), +PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21, SUBPARTITION subpart22) +); +ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), +PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21, SUBPAR' at line 5 + +#------------------------------------------------------------------------ +# 4. Checks of logical partition/subpartition name +# file name clashes during CREATE TABLE +#------------------------------------------------------------------------ +DROP TABLE IF EXISTS t1; +# 4.1 (negative) A partition name used more than once +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) (PARTITION part1, PARTITION part1); +ERROR HY000: Duplicate partition name part1 +# FIXME Implement testcases with filename problems +# existing file of other table --- partition/subpartition file name +# partition/subpartition file name --- file of the same table + +#------------------------------------------------------------------------ +# 5. Alter table experiments +#------------------------------------------------------------------------ +DROP TABLE IF EXISTS t1; +# 5.1 alter table add partition +# 5.1.1 (negative) add partition to non partitioned table +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)); +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 +ALTER TABLE t1 ADD PARTITION (PARTITION part1); +Got one of the listed errors +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 +DROP TABLE t1; +# 5.1.2 Add one partition to a table with one partition +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1); +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) +INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1; +ALTER TABLE t1 ADD PARTITION (PARTITION part1); +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 p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM) +INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 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; +# 5.1.3 Several times add one partition to a table with some partitions +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) (PARTITION part1, PARTITION part3); +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 part3 ENGINE = MyISAM) +INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1; +ALTER TABLE t1 ADD PARTITION (PARTITION part0); +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 part3 ENGINE = MyISAM, PARTITION part0 ENGINE = MyISAM) +INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 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 +DELETE FROM t1; +INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1; +ALTER TABLE t1 ADD PARTITION (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 part3 ENGINE = MyISAM, PARTITION part0 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) +INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 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 +DELETE FROM t1; +INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1; +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 part3 ENGINE = MyISAM, PARTITION part0 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) +INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 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; +# 5.1.4 Add several partitions to a table with some partitions +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) (PARTITION part1, PARTITION part3); +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 part3 ENGINE = MyISAM) +INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1; +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 part3 ENGINE = MyISAM) +INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 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; +# 5.1.5 (negative) Add partitions to a table with some partitions +# clash on new and already existing partition names +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) (PARTITION part1, PARTITION part2, PARTITION part3); +ALTER TABLE t1 ADD PARTITION (PARTITION part1); +ERROR HY000: Duplicate partition name part1 +ALTER TABLE t1 ADD PARTITION (PARTITION part2); +ERROR HY000: Duplicate partition name part2 +ALTER TABLE t1 ADD PARTITION (PARTITION part3); +ERROR HY000: Duplicate partition name part3 +ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part2, PARTITION part3); +ERROR HY000: Duplicate partition name part1 +DROP TABLE t1; +# 5.2 alter table add subpartition +# 5.2.1 Add one subpartition to a table with subpartitioning rule and +# no explicit defined subpartitions +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) +SUBPARTITION BY HASH(f1) +(PARTITION part1 VALUES LESS THAN (100)); +INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1; +ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21)); +DROP TABLE t1; diff --git a/mysql-test/r/partition_03ndb.result b/mysql-test/r/partition_03ndb.result new file mode 100644 index 00000000000..28339cc7435 --- /dev/null +++ b/mysql-test/r/partition_03ndb.result @@ -0,0 +1,1361 @@ +SET SESSION storage_engine='NDB' ; +SET @max_row = 200; +SET AUTOCOMMIT= 1; +#------------------------------------------------------------------------ +# 0. 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 200 INSERTs into t0_template suppressed +#------------------------------------------------------------------------ +# 1. Some syntax checks +#------------------------------------------------------------------------ +# 1.1 Subpartioned table without subpartitioning rule must be rejected +DROP TABLE IF EXISTS t1; +#------------------------------------------------------------------------ +# 2. Checks where the engine is set on all supported CREATE TABLE +# statement positions + basic operations on the tables +#------------------------------------------------------------------------ +DROP TABLE IF EXISTS t1; +# 2.1 table (non partitioned) for comparison +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'NDB' ; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY () +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 table with engine setting just after column list +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'NDB' +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=ndbcluster 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 table with engine setting in the named partition part +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) +( PARTITION part1 STORAGE ENGINE = 'NDB' , +PARTITION part2 STORAGE ENGINE = 'NDB' +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = ndbcluster, PARTITION part2 ENGINE = ndbcluster) +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 table with engine setting in the named subpartition part +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 = 'NDB' , +SUBPARTITION subpart12 STORAGE ENGINE = 'NDB' ), +PARTITION part2 VALUES LESS THAN (2000) +(SUBPARTITION subpart21 STORAGE ENGINE = 'NDB' , +SUBPARTITION subpart22 STORAGE ENGINE = 'NDB' ) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (2000) (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster)) +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.5 Ugly "incomplete" storage engine assignments +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) +( PARTITION part1 STORAGE ENGINE = 'NDB' , +PARTITION part2 +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = ndbcluster, PARTITION part2 ENGINE = ndbcluster) +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 = 'NDB' +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = ndbcluster, PARTITION part2 ENGINE = ndbcluster) +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 (1000) +(SUBPARTITION subpart11, +SUBPARTITION subpart12 STORAGE ENGINE = 'NDB' ), +PARTITION part2 VALUES LESS THAN (2000) +(SUBPARTITION subpart21 STORAGE ENGINE = 'NDB' , +SUBPARTITION subpart22 STORAGE ENGINE = 'NDB' ) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (2000) (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster)) +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 (1000) +(SUBPARTITION subpart11 STORAGE ENGINE = 'NDB' , +SUBPARTITION subpart12 STORAGE ENGINE = 'NDB' ), +PARTITION part2 VALUES LESS THAN (2000) +(SUBPARTITION subpart21 , +SUBPARTITION subpart22 ) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (2000) (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster)) +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.6 Ugly "over determined" storage engine assignments +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) ENGINE = 'NDB' +PARTITION BY HASH(f1) +( PARTITION part1 STORAGE ENGINE = 'NDB' , +PARTITION part2 STORAGE ENGINE = 'NDB' +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = ndbcluster, PARTITION part2 ENGINE = ndbcluster) +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)) ENGINE = 'NDB' +PARTITION BY RANGE(f1) +SUBPARTITION BY HASH(f1) +( PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11 STORAGE ENGINE = 'NDB' , +SUBPARTITION subpart12 STORAGE ENGINE = 'NDB' ), +PARTITION part2 VALUES LESS THAN (2000) +(SUBPARTITION subpart21 STORAGE ENGINE = 'NDB' , +SUBPARTITION subpart22 STORAGE ENGINE = 'NDB' ) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (2000) (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster)) +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 (1000) STORAGE ENGINE = 'NDB' +(SUBPARTITION subpart11 STORAGE ENGINE = 'NDB' , +SUBPARTITION subpart12 STORAGE ENGINE = 'NDB' ), +PARTITION part2 VALUES LESS THAN (2000) +(SUBPARTITION subpart21 STORAGE ENGINE = 'NDB' , +SUBPARTITION subpart22 STORAGE ENGINE = 'NDB' ) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (2000) (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster)) +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.7 Ugly storage engine assignments mixups +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) +SUBPARTITION BY HASH(f1) +( PARTITION part1 VALUES LESS THAN (1000) ENGINE = 'NDB' +(SUBPARTITION subpart11 , +SUBPARTITION subpart12 ), +PARTITION part2 VALUES LESS THAN (2000) +(SUBPARTITION subpart21 STORAGE ENGINE = 'NDB' , +SUBPARTITION subpart22 STORAGE ENGINE = 'NDB' ) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (2000) (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster)) +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 (1000) +(SUBPARTITION subpart11 STORAGE ENGINE = 'NDB' , +SUBPARTITION subpart12 STORAGE ENGINE = 'NDB' ), +PARTITION part2 VALUES LESS THAN (2000) ENGINE = 'NDB' +(SUBPARTITION subpart21 , +SUBPARTITION subpart22 ) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (2000) (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster)) +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.8 Session default engine differs from engine used within create table +SET SESSION storage_engine='MEMORY'; +SET SESSION storage_engine='NDB' ; +#------------------------------------------------------------------------ +# 3. Check number of partitions and subpartitions +#------------------------------------------------------------------------ +DROP TABLE IF EXISTS t1; +# 3.1 (positive) without partition/subpartition number assignment +# 3.1.1 no partition number, no named partitions, no subpartitions mentioned +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) +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; +# 3.1.2 no partition number, named partitions, no subpartitions mentioned +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) (PARTITION part1, PARTITION part2); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = ndbcluster, PARTITION part2 ENGINE = ndbcluster) +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; +# 3.1.3 variations on no partition/subpartition number, named partitions, +# different subpartitions are/are not named +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) (PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2000), PARTITION part3 VALUES LESS THAN (2147483647)) ; +DROP TABLE t1; +# FIXME several subtestcases of 3.1.3 disabled because of server crashes +# Bug#15407 Partitions: crash if subpartition +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) (PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (2000) +(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21 , SUBPARTITION subpart22 )) ; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (2000) (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster), PARTITION part3 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster)) +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; +# 3.2 partition/subpartition numbers good and bad values and notations +DROP TABLE IF EXISTS t1; +# 3.2.1 partition/subpartition numbers INTEGER notation +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +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=ndbcluster 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; +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS 2 +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) SUBPARTITIONS 2 (PARTITION part1 VALUES LESS THAN (1000) , PARTITION part2 VALUES LESS THAN (2147483647) ) +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) PARTITIONS 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) PARTITIONS 1 +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) +SUBPARTITIONS 1 +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) SUBPARTITIONS 1 (PARTITION part1 VALUES LESS THAN (1000) , PARTITION part2 VALUES LESS THAN (2147483647) ) +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) PARTITIONS 0; +ERROR HY000: Number of partitions = 0 is not an allowed value +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS 0 +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR HY000: Number of subpartitions = 0 is not an allowed value +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS -1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS -1 +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1 +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 1000000; +ERROR HY000: Too many partitions were defined +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS 1000000 +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR HY000: Too many partitions were defined +# 3.2.4 partition/subpartition numbers STRING notation +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS '2'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS '2' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS '2.0'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS '2.0' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS '0.2E+1'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS '0.2E+1' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS T' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS '2A'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS '2A' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN ' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 'A2'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS 'A2' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN ' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS ''; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS '' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 'GARBAGE'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS 'GARBAGE' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE' +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS ' at line 3 +# 3.2.5 partition/subpartition numbers other notations +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 2A; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS 2A +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS A2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS A2 +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2 +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS GARBAGE; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS GARBAGE +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS TH' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS "2"; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS "2" +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2" +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS "2A"; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS "2A" +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A" +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN ' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS "A2"; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS "A2" +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2" +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN ' at line 3 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS "GARBAGE"; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) SUBPARTITION BY HASH(f1) +SUBPARTITIONS "GARBAGE" +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (2147483647)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE" +(PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS ' at line 3 +# 3.3 Mixups of number and names of partition/subpartition assigned +# 3.3.1 (positive) number of partition/subpartition = number of named partition/subpartition +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1 ENGINE = ndbcluster, PARTITION part2 ENGINE = ndbcluster) +DROP TABLE t1; +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) PARTITIONS 2 +SUBPARTITION BY HASH(f1) SUBPARTITIONS 2 +( PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11, SUBPARTITION subpart12), +PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21, SUBPARTITION subpart22) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) default NULL, + `f2` char(20) default NULL +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster), PARTITION part2 VALUES LESS THAN (2147483647) (SUBPARTITION subpart21 ENGINE = ndbcluster, SUBPARTITION subpart22 ENGINE = ndbcluster)) +DROP TABLE t1; +# 3.3.2 (positive) number of partition/subpartition , 0 (= no) named partition/subpartition +# already checked above +# 3.3.3 (negative) number of partitions > number of named partitions +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1 ) ; +ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 2 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) +SUBPARTITION BY HASH(f1) SUBPARTITIONS 2 +( PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11 ), +PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21, SUBPARTITION subpart22) +); +ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), +PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21, SUBPAR' at line 5 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) +SUBPARTITION BY HASH(f1) SUBPARTITIONS 2 +( PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11, SUBPARTITION subpart12), +PARTITION part2 VALUES LESS THAN (2000) +(SUBPARTITION subpart21 ), +PARTITION part3 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart31, SUBPARTITION subpart32) +); +ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), +PARTITION part3 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart31, SUBPAR' at line 7 +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY RANGE(f1) PARTITIONS 2 +SUBPARTITION BY HASH(f1) SUBPARTITIONS 2 +( PARTITION part1 VALUES LESS THAN (1000) +(SUBPARTITION subpart11, SUBPARTITION subpart12), +PARTITION part2 VALUES LESS THAN (2147483647) +(SUBPARTITION subpart21 ) +); +ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ') +)' at line 7 +#------------------------------------------------------------------------ +# 4. Checks of logical partition/subpartition name +# file name clashes during CREATE TABLE +#------------------------------------------------------------------------ +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1) (PARTITION part1, PARTITION part1); +ERROR HY000: Duplicate partition name part1 +#------------------------------------------------------------------------ +# 5. Alter table experiments +#------------------------------------------------------------------------ +# 5.1 alter table add partition +# 5.1.1 (negative) add partition to non partitioned table +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)); +# FIXME Why does the error numbers of MyISAM(1482) and NDB(1005) differ ? +ALTER TABLE t1 ADD PARTITION (PARTITION part1); +Got one of the listed errors +DROP TABLE t1; +# 5.1.2 Add one partition to a table with one partition +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) +PARTITION BY HASH(f1); +INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100; diff --git a/mysql-test/r/partition_error.result b/mysql-test/r/partition_error.result index 90faa3b20b8..1a0b1dd9b3a 100644 --- a/mysql-test/r/partition_error.result +++ b/mysql-test/r/partition_error.result @@ -1,3 +1,4 @@ +drop table if exists t1; partition by list (a) partitions 3 (partition x1 values in (1,2,9,4) tablespace ts1, @@ -544,6 +545,10 @@ partitions 2 partition x2 values in (5)); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4, partition x2 values in (5))' at line 8 +CREATE TABLE t1 (a int) +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (x1)); +ERROR 42S22: Unknown column 'x1' in 'partition function' CREATE TABLE t1(a int) PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN(5)); insert into t1 values (10); diff --git a/mysql-test/r/partition_mgm_err.result b/mysql-test/r/partition_mgm_err.result index 01709e726bd..3c2c50fc6f8 100644 --- a/mysql-test/r/partition_mgm_err.result +++ b/mysql-test/r/partition_mgm_err.result @@ -1,3 +1,4 @@ +drop table if exists t1; CREATE TABLE t1 (a int, b int) PARTITION BY RANGE (a) (PARTITION x0 VALUES LESS THAN (2), @@ -10,48 +11,52 @@ PARTITION x6 VALUES LESS THAN (14), PARTITION x7 VALUES LESS THAN (16), PARTITION x8 VALUES LESS THAN (18), PARTITION x9 VALUES LESS THAN (20)); -ALTER TABLE t1 REORGANISE PARTITION x0,x1 INTO +ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO (PARTITION x01 VALUES LESS THAN (2), PARTITION x11 VALUES LESS THAN (5)); -ERROR HY000: The new partitions cover a bigger range then the reorganised partitions do +ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range ALTER TABLE t1 DROP PARTITION x0, x1, x2, x3, x3; -ERROR HY000: Error in list of partitions to change +ERROR HY000: Error in list of partitions to DROP ALTER TABLE t1 DROP PARTITION x0, x1, x2, x10; -ERROR HY000: Error in list of partitions to change +ERROR HY000: Error in list of partitions to DROP ALTER TABLE t1 DROP PARTITION x10, x1, x2, x1; -ERROR HY000: Error in list of partitions to change +ERROR HY000: Error in list of partitions to DROP ALTER TABLE t1 DROP PARTITION x10, x1, x2, x3; -ERROR HY000: Error in list of partitions to change -ALTER TABLE t1 REORGANISE PARTITION x0,x1,x2,x3,x4,x5,x6,x7,x8,x9,x10 INTO +ERROR HY000: Error in list of partitions to DROP +ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2,x3,x4,x5,x6,x7,x8,x9,x10 INTO (PARTITION x11 VALUES LESS THAN (22)); ERROR HY000: More partitions to reorganise than there are partitions -ALTER TABLE t1 REORGANISE PARTITION x0,x1,x2 INTO +ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO (PARTITION x3 VALUES LESS THAN (6)); -ERROR HY000: All partitions must have unique names in the table -ALTER TABLE t1 REORGANISE PARTITION x0, x2 INTO +ERROR HY000: Duplicate partition name x3 +ALTER TABLE t1 REORGANIZE PARTITION x0, x2 INTO (PARTITION x11 VALUES LESS THAN (2)); ERROR HY000: When reorganising a set of partitions they must be in consecutive order -ALTER TABLE t1 REORGANISE PARTITION x0, x1, x1 INTO +ALTER TABLE t1 REORGANIZE PARTITION x0, x1, x1 INTO (PARTITION x11 VALUES LESS THAN (4)); -ERROR HY000: Error in list of partitions to change -ALTER TABLE t1 REORGANISE PARTITION x0,x1 INTO +ERROR HY000: Error in list of partitions to REORGANIZE +ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO (PARTITION x01 VALUES LESS THAN (5)); -ERROR HY000: The new partitions cover a bigger range then the reorganised partitions do -ALTER TABLE t1 REORGANISE PARTITION x0,x1 INTO +ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range +ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO (PARTITION x01 VALUES LESS THAN (4), PARTITION x11 VALUES LESS THAN (2)); +ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range +ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO +(PARTITION x01 VALUES LESS THAN (6), +PARTITION x11 VALUES LESS THAN (4)); ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition DROP TABLE t1; CREATE TABLE t1 (a int) PARTITION BY KEY (a) PARTITIONS 2; ALTER TABLE t1 ADD PARTITION (PARTITION p1); -ERROR HY000: All partitions must have unique names in the table +ERROR HY000: Duplicate partition name p1 DROP TABLE t1; CREATE TABLE t1 (a int) PARTITION BY KEY (a) (PARTITION x0, PARTITION x1, PARTITION x2, PARTITION x3, PARTITION x3); -ERROR HY000: All partitions must have unique names in the table +ERROR HY000: Duplicate partition name x3 CREATE TABLE t1 (a int) PARTITION BY RANGE (a) SUBPARTITION BY KEY (a) @@ -100,7 +105,7 @@ PARTITION x1 VALUES LESS THAN (8)); ALTER TABLE t1 ADD PARTITION PARTITIONS 1; ERROR HY000: For RANGE partitions each partition must be defined ALTER TABLE t1 DROP PARTITION x2; -ERROR HY000: Error in list of partitions to change +ERROR HY000: Error in list of partitions to DROP ALTER TABLE t1 COALESCE PARTITION 1; ERROR HY000: COALESCE PARTITION can only be used on HASH/KEY partitions ALTER TABLE t1 DROP PARTITION x1; |