diff options
Diffstat (limited to 'mysql-test/suite/ndb/r/partition_03ndb.result')
| -rw-r--r-- | mysql-test/suite/ndb/r/partition_03ndb.result | 1361 |
1 files changed, 1361 insertions, 0 deletions
diff --git a/mysql-test/suite/ndb/r/partition_03ndb.result b/mysql-test/suite/ndb/r/partition_03ndb.result new file mode 100644 index 00000000000..28339cc7435 --- /dev/null +++ b/mysql-test/suite/ndb/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; |