diff options
Diffstat (limited to 'mysql-test/suite/ndb/t/ndb_partition_range.test')
-rw-r--r-- | mysql-test/suite/ndb/t/ndb_partition_range.test | 265 |
1 files changed, 265 insertions, 0 deletions
diff --git a/mysql-test/suite/ndb/t/ndb_partition_range.test b/mysql-test/suite/ndb/t/ndb_partition_range.test new file mode 100644 index 00000000000..e34cb92771c --- /dev/null +++ b/mysql-test/suite/ndb/t/ndb_partition_range.test @@ -0,0 +1,265 @@ +-- source include/have_ndb.inc +-- source include/have_binlog_format_row.inc +#--disable_abort_on_error +# +# Simple test for the partition storage engine +# Focuses on range partitioning tests +# +#-- source include/have_partition.inc + +--disable_query_log +set new=on; +--enable_query_log + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# Partition by range, basic +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key(a,b), +index (a)) +engine = ndb +partition by range (a) +partitions 3 +(partition x1 values less than (5), + partition x2 values less than (10), + partition x3 values less than (20)); + +# Simple insert and verify test +INSERT into t1 values (1, 1, 1); +INSERT into t1 values (6, 1, 1); +INSERT into t1 values (10, 1, 1); +INSERT into t1 values (15, 1, 1); + +--replace_column 16 # 19 # 20 # +select * from information_schema.partitions where table_name= 't1'; + +select * from t1 order by a; + +select * from t1 where a=1 order by a; +select * from t1 where a=15 and b=1 order by a; +select * from t1 where a=21 and b=1 order by a; +select * from t1 where a=21 order by a; +select * from t1 where a in (1,6,10,21) order by a; +select * from t1 where b=1 and a in (1,6,10,21) order by a; + +drop table t1; + +# +# Partition by range, basic +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key(b), +unique (a)) +engine = ndb +partition by range (b) +partitions 3 +(partition x1 values less than (5), + partition x2 values less than (10), + partition x3 values less than (20)); + +# Simple insert and verify test +INSERT into t1 values (1, 1, 1); +INSERT into t1 values (2, 6, 1); +INSERT into t1 values (3, 10, 1); +INSERT into t1 values (4, 15, 1); + +select * from t1 order by a; +UPDATE t1 set a = 5 WHERE b = 15; +select * from t1 order by a; +UPDATE t1 set a = 6 WHERE a = 5; +select * from t1 order by a; + +select * from t1 where b=1 order by b; +select * from t1 where b=15 and a=1 order by b; +select * from t1 where b=21 and a=1 order by b; +select * from t1 where b=21 order by b; +select * from t1 where b in (1,6,10,21) order by b; +select * from t1 where a in (1,2,5,6) order by b; +select * from t1 where a=1 and b in (1,6,10,21) order by b; + +DELETE from t1 WHERE b = 6; +DELETE from t1 WHERE a = 6; + +# +# Test that explicit partition info _is_ shown in show create table +# result _should_ contain (PARTITION x1 ... etc) +# +show create table t1; + +drop table t1; + +# +# Bug #17499, #17687 +# Alter partitioned NDB table causes mysqld to core +# + +CREATE TABLE t1 + (id MEDIUMINT NOT NULL, + b1 BIT(8), + vc VARCHAR(255), + bc CHAR(255), + d DECIMAL(10,4) DEFAULT 0, + f FLOAT DEFAULT 0, + total BIGINT UNSIGNED, + y YEAR, + t DATE) ENGINE=NDB + PARTITION BY RANGE (YEAR(t)) + (PARTITION p0 VALUES LESS THAN (1901), + PARTITION p1 VALUES LESS THAN (1946), + PARTITION p2 VALUES LESS THAN (1966), + PARTITION p3 VALUES LESS THAN (1986), + PARTITION p4 VALUES LESS THAN (2005), + PARTITION p5 VALUES LESS THAN MAXVALUE); + +INSERT INTO t1 VALUES (0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); +SELECT * FROM t1; +ALTER TABLE t1 ENGINE=MYISAM; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE LOGFILE GROUP lg1 + ADD UNDOFILE 'undofile.dat' + INITIAL_SIZE 16M + UNDO_BUFFER_SIZE=1M + ENGINE=NDB; + +CREATE TABLESPACE ts1 + ADD DATAFILE 'datafile.dat' + USE LOGFILE GROUP lg1 + INITIAL_SIZE 12M + ENGINE NDB; + +CREATE TABLE test.t1 ( + a1 INT, + a2 TEXT NOT NULL, + a3 BIT NOT NULL, + a4 DECIMAL(8,3), + a5 INT NOT NULL, + a6 INT, + PRIMARY KEY(a1)) + TABLESPACE ts1 STORAGE DISK ENGINE=NDB + PARTITION BY LIST (a1) + (PARTITION p0 VALUES IN (1,2,3,4,5), + PARTITION p1 VALUES IN (6,7,8,9, 10), + PARTITION p2 VALUES IN (11, 12, 13, 14, 15)); + +# Alter table directly without any statements inbetween +ALTER TABLE test.t1 DROP COLUMN a6; +ALTER TABLE test.t1 ADD COLUMN a6 VARCHAR(255); + +let $j= 15; +--disable_query_log +while ($j) +{ +eval INSERT INTO test.t1 VALUES ($j, "Tested Remotely from Texas, USA", +b'1',$j.00,$j+1,"By NIK $j"); +dec $j; +} +--enable_query_log +SELECT COUNT(*) FROM test.t1; + +ALTER TABLE test.t1 DROP COLUMN a4; +SELECT COUNT(*) FROM test.t1; + +DROP TABLE t1; + +CREATE TABLE test.t1 ( + a1 INT, + a2 TEXT NOT NULL, + a3 BIT NOT NULL, + a4 DECIMAL(8,3), + a5 INT NOT NULL, + a6 VARCHAR(255), + PRIMARY KEY(a1)) + TABLESPACE ts1 STORAGE DISK ENGINE=NDB + PARTITION BY HASH(a1) + PARTITIONS 4; + +let $j= 15; +--disable_query_log +while ($j) +{ +eval INSERT INTO test.t1 VALUES ($j, "Tested Remotely from Texas, USA", +b'1',$j.00,$j+1,"By NIK $j"); +dec $j; +} +--enable_query_log +SELECT COUNT(*) FROM test.t1; + +ALTER TABLE test.t1 DROP COLUMN a4; +SELECT COUNT(*) FROM test.t1; + +DROP TABLE t1; + +ALTER TABLESPACE ts1 + DROP DATAFILE 'datafile.dat' + ENGINE=NDB; +DROP TABLESPACE ts1 ENGINE=NDB; +DROP LOGFILE GROUP lg1 ENGINE=NDB; + + +# +# Bug #17701 ALTER TABLE t1 ADD PARTITION for PARTITION BY LIST hangs test +# + +CREATE TABLE t1 + (id MEDIUMINT NOT NULL, + b1 BIT(8), + vc VARCHAR(255), + bc CHAR(255), + d DECIMAL(10,4) DEFAULT 0, + f FLOAT DEFAULT 0, + total BIGINT UNSIGNED, + y YEAR, + t DATE) ENGINE=NDB + PARTITION BY LIST(id) + (PARTITION p0 VALUES IN (2, 4), + PARTITION p1 VALUES IN (42, 142)); + +INSERT INTO t1 VALUES (2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); +SELECT * FROM t1; +ALTER TABLE t1 ADD PARTITION + (PARTITION p2 VALUES IN (412)); +SELECT * FROM t1; +DROP TABLE t1; + +# +# Bug #17806 Update on NDB table with list partition causes mysqld to core +# Bug #16385 Partitions: crash when updating a range partitioned NDB table +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null) +partition by list(a) +partitions 2 +(partition x123 values in (1,5,6), + partition x234 values in (4,7,8)); +INSERT into t1 VALUES (5,1,1); +select * from t1; +UPDATE t1 SET a=8 WHERE a=5 AND b=1; +select * from t1; +drop table t1; + +CREATE TABLE t1 ( f1 INTEGER, f2 char(20)) engine=ndb +PARTITION BY RANGE(f1) +( PARTITION part1 VALUES LESS THAN (2), +PARTITION part2 VALUES LESS THAN (1000)); +INSERT INTO t1 VALUES(1, '---1---'); +INSERT INTO t1 VALUES(2, '---2---'); +select * from t1 order by f1; +UPDATE t1 SET f1 = f1 + 4 WHERE f1 = 2; +select * from t1 order by f1; +UPDATE t1 SET f1 = f1 + 4 WHERE f1 = 1; +select * from t1 order by f1; +drop table t1; |