diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/partition_range.test | 1056 |
1 files changed, 1056 insertions, 0 deletions
diff --git a/mysql-test/main/partition_range.test b/mysql-test/main/partition_range.test new file mode 100644 index 00000000..19050267 --- /dev/null +++ b/mysql-test/main/partition_range.test @@ -0,0 +1,1056 @@ +#--disable_abort_on_error +# +# Simple test for the partition storage engine +# Focuses on range partitioning tests +# +--source include/have_partition.inc +--source include/have_sequence.inc + +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +--echo # +--echo # Bug#48229: group by performance issue of partitioned table +--echo # + +CREATE TABLE t1 (a INT,b INT,KEY a (a,b)); +INSERT INTO `t1` VALUES (0,580092),(3000,894076),(4000,805483),(4000,913540),(6000,611137),(8000,171602),(9000,599495),(9000,746305),(10000,272829),(10000,847519),(12000,258869),(12000,929028),(13000,288970),(15000,20971),(15000,105839),(16000,788272),(17000,76914),(18000,827274),(19000,802258),(20000,123677),(20000,587729),(22000,701449),(25000,31565),(25000,230782),(25000,442887),(25000,733139),(25000,851020); + +EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10000, 1000000, 3000) GROUP BY a; + +alter table t1 partition by hash(a) partitions 1; +EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10000, 1000000, 3000) GROUP BY a; +alter table t1 remove partitioning; + +insert into t1 (a,b) select seq,seq from seq_4001_to_4100; +insert into t1 (a,b) select seq,seq from seq_10001_to_10100; + +EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10000, 1000000, 3000) GROUP BY a; + +alter table t1 partition by hash(a) partitions 1; +EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10000, 1000000, 3000) GROUP BY a; +DROP TABLE t1; + +# +#BUG#49591, Add proper version number to SHOW CREATE TABLE +# +create table t1 (a DATETIME) +partition by range (TO_DAYS(a)) +subpartition by hash(to_seconds(a)) +(partition p0 values less than (1)); +show create table t1; +drop table t1; + +--error ER_NULL_IN_VALUES_LESS_THAN +create table t1 (a int) +partition by range (a) +( partition p0 values less than (NULL), + partition p1 values less than (MAXVALUE)); +# +# Merge fix of bug#27927 for TO_SECONDS function +# +create table t1 (a datetime not null) +partition by range (TO_SECONDS(a)) +( partition p0 VALUES LESS THAN (TO_SECONDS('2007-03-08 00:00:00')), + partition p1 VALUES LESS THAN (TO_SECONDS('2007-04-01 00:00:00'))); +select partition_method, partition_expression, partition_description + from information_schema.partitions where table_name = "t1"; +INSERT INTO t1 VALUES ('2007-03-01 12:00:00'), ('2007-03-07 12:00:00'); +INSERT INTO t1 VALUES ('2007-03-08 12:00:00'), ('2007-03-15 12:00:00'); +explain partitions select * from t1 where a < '2007-03-08 00:00:00'; +explain partitions select * from t1 where a < '2007-03-08 00:00:01'; +explain partitions select * from t1 where a <= '2007-03-08 00:00:00'; +explain partitions select * from t1 where a <= '2007-03-07 23:59:59'; +explain partitions select * from t1 where a < '2007-03-07 23:59:59'; +show create table t1; +drop table t1; +# +# New test cases for new function to_seconds +# +create table t1 (a date) +partition by range(to_seconds(a)) +(partition p0 values less than (to_seconds('2004-01-01')), + partition p1 values less than (to_seconds('2005-01-01'))); +insert into t1 values ('2003-12-30'),('2004-12-31'); +select * from t1; +explain partitions select * from t1 where a <= '2003-12-31'; +select * from t1 where a <= '2003-12-31'; +explain partitions select * from t1 where a <= '2005-01-01'; +select * from t1 where a <= '2005-01-01'; +show create table t1; +drop table t1; + +create table t1 (a datetime) +partition by range(to_seconds(a)) +(partition p0 values less than (to_seconds('2004-01-01 12:00:00')), + partition p1 values less than (to_seconds('2005-01-01 12:00:00'))); +insert into t1 values ('2004-01-01 11:59:29'),('2005-01-01 11:59:59'); +select * from t1; +explain partitions select * from t1 where a <= '2004-01-01 11:59.59'; +select * from t1 where a <= '2004-01-01 11:59:59'; +explain partitions select * from t1 where a <= '2005-01-01'; +select * from t1 where a <= '2005-01-01'; +show create table t1; +drop table t1; + +# +# Adding new test cases for column list variant for partitioning +# +--error 1064 +create table t1 (a int, b char(20)) +partition by range columns(a,b) +(partition p0 values less than (1)); + +--error ER_TOO_MANY_VALUES_ERROR +create table t1 (a int, b char(20)) +partition by range(a) +(partition p0 values less than (1,"b")); + +--error ER_TOO_MANY_VALUES_ERROR +create table t1 (a int, b char(20)) +partition by range(a) +(partition p0 values less than (1,"b")); + +create table t1 (a int, b char(20)) +partition by range columns(b) +(partition p0 values less than ("b")); +drop table t1; + +# +# BUG 33429: Succeeds in adding partition when maxvalue on last partition +# +create table t1 (a int) +partition by range (a) +( partition p0 values less than (maxvalue)); +--error ER_PARTITION_MAXVALUE_ERROR +alter table t1 add partition (partition p1 values less than (100000)); +show create table t1; +drop table t1; + +# BUG 32943: +# Locking problems in relation to partitioning and triggers +# Also fixes and test cases of generic lock issues with +# partition change code. +# +create table t1 (a integer) +partition by range (a) +( partition p0 values less than (4), + partition p1 values less than (100)); + +delimiter |; +create trigger tr1 before insert on t1 +for each row begin + set @a = 1; +end| + +delimiter ;| +alter table t1 drop partition p0; + +drop table t1; + +create table t1 (a integer) +partition by range (a) +( partition p0 values less than (4), + partition p1 values less than (100)); +LOCK TABLES t1 WRITE; +alter table t1 drop partition p0; +alter table t1 reorganize partition p1 into +( partition p0 values less than (4), + partition p1 values less than (100)); +alter table t1 add partition ( partition p2 values less than (200)); +UNLOCK TABLES; +drop table t1; + +# +# BUG 18198: Various tests for partition functions +# +#create table t1 (a varchar(10) charset latin1 collate latin1_bin, b int) +#partition by range (ascii(a) * b) +#(partition p0 values less than (2), partition p1 values less than (4000)); +#insert into t1 values ('a ', 2),('a',3); +#drop table t1; + +#create table t1 (a varchar(10) charset latin1 collate latin1_bin, b int) +#partition by range (b* ascii(a) * b) +#(partition p0 values less than (2), partition p1 values less than (4000)); +#insert into t1 values ('a ', 2),('a',3); +#drop table t1; + +#create table t1 (a varchar(10) charset latin1 collate latin1_bin, +# b varchar(10) charset latin1 collate latin1_bin) +#partition by range (ascii(b) * ascii(a)) +#(partition p0 values less than (2), partition p1 values less than (40000)); +#insert into t1 values ('a ', 'b '),('a','b'); +#drop table t1; + +#create table t1 (a varchar(10) charset latin1 collate latin1_bin, +# b varchar(10) charset latin1 collate latin1_bin) +#partition by range (ascii(a) * ascii(b)) +#(partition p0 values less than (2), partition p1 values less than (40000)); +#insert into t1 values ('a ', 'b '),('a','b'); +#drop table t1; + +#create table t1 (a varchar(10) charset latin1 collate latin1_bin, +# b varchar(10) charset latin1 collate latin1_bin, c int) +#partition by range (ascii(a) * c) +#(partition p0 values less than (2), partition p1 values less than (4000)); +#insert into t1 values ('a ', 'b ', 2),('a','b', 3); +#drop table t1; + +#create table t1 (a varchar(10) charset latin1 collate latin1_bin, +# b varchar(10) charset latin1 collate latin1_bin, c int) +#partition by range (c * ascii(a)) +#(partition p0 values less than (2), partition p1 values less than (4000)); +#insert into t1 values ('a ', 'b ', 2),('a','b', 3); +#drop table t1; + +# +# More checks for partition pruning +# +create table t1 (a int unsigned) +partition by range (a) +(partition pnull values less than (0), + partition p0 values less than (1), + partition p1 values less than(2)); +insert into t1 values (null),(0),(1); + +select * from t1 where a is null; +select * from t1 where a >= 0; +select * from t1 where a < 0; +select * from t1 where a <= 0; +select * from t1 where a > 1; +explain partitions select * from t1 where a is null; +explain partitions select * from t1 where a >= 0; +explain partitions select * from t1 where a < 0; +explain partitions select * from t1 where a <= 0; +explain partitions select * from t1 where a > 1; +drop table t1; + +create table t1 (a int unsigned, b int unsigned) +partition by range (a) +subpartition by hash (b) +subpartitions 2 +(partition pnull values less than (0), + partition p0 values less than (1), + partition p1 values less than(2)); +insert into t1 values (null,0),(null,1),(0,0),(0,1),(1,0),(1,1); + +select * from t1 where a is null; +select * from t1 where a >= 0; +select * from t1 where a < 0; +select * from t1 where a <= 0; +select * from t1 where a > 1; +explain partitions select * from t1 where a is null; +explain partitions select * from t1 where a >= 0; +explain partitions select * from t1 where a < 0; +explain partitions select * from t1 where a <= 0; +explain partitions select * from t1 where a > 1; + +drop table t1; + +# +# Partition by range, basic +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key(a,b)) +partition by range (a) +partitions 3 +(partition x1 values less than (5) tablespace ts1, + partition x2 values less than (10) tablespace ts2, + partition x3 values less than maxvalue tablespace ts3); + +# 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); + +select * from t1; +show create table t1; + +ALTER TABLE t1 +partition by range (a) +partitions 3 +(partition x1 values less than (5) tablespace ts1, + partition x2 values less than (10) tablespace ts2, + partition x3 values less than maxvalue tablespace ts3); + +select * from t1; +show create table t1; + +drop table if exists t1; + +# +# Partition by range, basic +# No primary key +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null) +partition by range (a) +partitions 3 +(partition x1 values less than (5) tablespace ts1, + partition x2 values less than (10) tablespace ts2, + partition x3 values less than maxvalue tablespace ts3); + +# 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); + +select * from t1; + +ALTER TABLE t1 +partition by range (a) +partitions 3 +(partition x1 values less than (5) tablespace ts1, + partition x2 values less than (10) tablespace ts2, + partition x3 values less than maxvalue tablespace ts3); + +select * from t1; + +drop table if exists t1; + +# +# Partition by range, basic +# No max value used +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key(a,b)) +partition by range (a) +partitions 3 +(partition x1 values less than (5) tablespace ts1, + partition x2 values less than (10) tablespace ts2, + partition x3 values less than (15) tablespace ts3); + + +# 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); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT into t1 values (15, 1, 1); + +select * from t1; + +ALTER TABLE t1 +partition by range (a) +partitions 3 +(partition x1 values less than (5) tablespace ts1, + partition x2 values less than (10) tablespace ts2, + partition x3 values less than (15) tablespace ts3); + +select * from t1; + +drop table t1; + +# +# Partition by range, only one partition +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key(a,b)) +partition by range (a) +(partition x1 values less than (1)); + +drop table t1; + +# +# Subpartition by hash, two partitions and two subpartitions +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key (a,b)) +partition by range (a) +subpartition by hash (a+b) +( partition x1 values less than (1) + ( subpartition x11, + subpartition x12), + partition x2 values less than (5) + ( subpartition x21, + subpartition x22) +); + +SELECT * from t1; +show create table t1; + +ALTER TABLE t1 ADD COLUMN d int; +show create table t1; + +drop table t1; + +# +# Subpartition by hash, two partitions and two subpartitions +# Defined tablespace, engine and node group +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key (a,b)) +partition by range (a) +subpartition by hash (a+b) +( partition x1 values less than (1) + ( subpartition x11 tablespace t1 engine myisam nodegroup 0, + subpartition x12 tablespace t2 engine myisam nodegroup 1), + partition x2 values less than (5) + ( subpartition x21 tablespace t1 engine myisam nodegroup 0, + subpartition x22 tablespace t2 engine myisam nodegroup 1) +); + +SELECT * from t1; + +drop table t1; + +# +# Subpartition by hash, two partitions and two subpartitions +# Defined tablespace, node group +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key (a,b)) +partition by range (a) +subpartition by hash (a+b) +( partition x1 values less than (1) + ( subpartition x11 tablespace t1 nodegroup 0, + subpartition x12 tablespace t2 nodegroup 1), + partition x2 values less than (5) + ( subpartition x21 tablespace t1 nodegroup 0, + subpartition x22 tablespace t2 nodegroup 1) +); + +SELECT * from t1; + +drop table t1; + +# +# Subpartition by hash, two partitions and two subpartitions +# Defined engine and node group +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key (a,b)) +partition by range (a) +subpartition by hash (a+b) +( partition x1 values less than (1) + ( subpartition x11 engine myisam nodegroup 0, + subpartition x12 engine myisam nodegroup 1), + partition x2 values less than (5) + ( subpartition x21 engine myisam nodegroup 0, + subpartition x22 engine myisam nodegroup 1) +); + +INSERT into t1 VALUES (1,1,1); +INSERT into t1 VALUES (4,1,1); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT into t1 VALUES (5,1,1); + +SELECT * from t1; + +ALTER TABLE t1 +partition by range (a) +subpartition by hash (a+b) +( partition x1 values less than (1) + ( subpartition x11 engine myisam nodegroup 0, + subpartition x12 engine myisam nodegroup 1), + partition x2 values less than (5) + ( subpartition x21 engine myisam nodegroup 0, + subpartition x22 engine myisam nodegroup 1) +); + +SELECT * from t1; + +drop table t1; + +# +# Subpartition by hash, two partitions and two subpartitions +# Defined tablespace, engine +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key (a,b)) +partition by range (a) +subpartition by hash (a+b) +( partition x1 values less than (1) + ( subpartition x11 tablespace t1 engine myisam, + subpartition x12 tablespace t2 engine myisam), + partition x2 values less than (5) + ( subpartition x21 tablespace t1 engine myisam, + subpartition x22 tablespace t2 engine myisam) +); + +INSERT into t1 VALUES (1,1,1); +INSERT into t1 VALUES (4,1,1); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT into t1 VALUES (5,1,1); + +SELECT * from t1; + +ALTER TABLE t1 +partition by range (a) +subpartition by hash (a+b) +( partition x1 values less than (1) + ( subpartition x11 tablespace t1 engine myisam, + subpartition x12 tablespace t2 engine myisam), + partition x2 values less than (5) + ( subpartition x21 tablespace t1 engine myisam, + subpartition x22 tablespace t2 engine myisam) +); + +SELECT * from t1; + +drop table t1; + +# +# Subpartition by hash, two partitions and two subpartitions +# Defined tablespace +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key (a,b)) +partition by range (a) +subpartition by hash (a+b) +( partition x1 values less than (1) + ( subpartition x11 tablespace t1, + subpartition x12 tablespace t2), + partition x2 values less than (5) + ( subpartition x21 tablespace t1, + subpartition x22 tablespace t2) +); + +INSERT into t1 VALUES (1,1,1); +INSERT into t1 VALUES (4,1,1); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT into t1 VALUES (5,1,1); + +SELECT * from t1; + +ALTER TABLE t1 +partition by range (a) +subpartition by hash (a+b) +( partition x1 values less than (1) + ( subpartition x11 tablespace t1 engine myisam, + subpartition x12 tablespace t2 engine myisam), + partition x2 values less than (5) + ( subpartition x21 tablespace t1 engine myisam, + subpartition x22 tablespace t2 engine myisam) +); + +SELECT * from t1; + +drop table t1; + +# +# Subpartition by hash, two partitions and two subpartitions +# Defined engine +# +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key (a,b)) +partition by range (a) +subpartition by hash (a+b) +( partition x1 values less than (1) + ( subpartition x11 engine myisam, + subpartition x12 engine myisam), + partition x2 values less than (5) + ( subpartition x21 engine myisam, + subpartition x22 engine myisam) +); + +INSERT into t1 VALUES (1,1,1); +INSERT into t1 VALUES (4,1,1); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT into t1 VALUES (5,1,1); + +SELECT * from t1; + +ALTER TABLE t1 +partition by range (a) +subpartition by hash (a+b) +( partition x1 values less than (1) + ( subpartition x11 engine myisam, + subpartition x12 engine myisam), + partition x2 values less than (5) + ( subpartition x21 engine myisam, + subpartition x22 engine myisam) +); + +SELECT * from t1; + +drop table t1; + +# +# Bug #17894 Comparison with "less than" operator fails with Range partition +# +CREATE TABLE t1 (c1 int default NULL, c2 varchar(30) default NULL, +c3 date default NULL) engine=myisam +PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995), +PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , +PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , +PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , +PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , +PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), +PARTITION p11 VALUES LESS THAN MAXVALUE ); +INSERT INTO t1 VALUES (1, 'testing partitions', '1995-07-17'), +(3, 'testing partitions','1995-07-31'), +(5, 'testing partitions','1995-08-13'), +(7, 'testing partitions','1995-08-26'), +(9, 'testing partitions','1995-09-09'), +(0, 'testing partitions','2000-07-10'), +(2, 'testing partitions','2000-07-23'), +(4, 'testing partitions','2000-08-05'), +(6, 'testing partitions','2000-08-19'), +(8, 'testing partitions','2000-09-01'); +SELECT COUNT(*) FROM t1 WHERE c3 BETWEEN '1996-12-31' AND '2000-12-31'; +SELECT COUNT(*) FROM t1 WHERE c3 < '2000-12-31'; +DROP TABLE t1; + +# +# BUG 16002: Unsigned partition functions not handled correctly +# +--error ER_RANGE_NOT_INCREASING_ERROR +create table t1 (a bigint unsigned) +partition by range (a) +(partition p0 values less than (10), + partition p1 values less than (0)); + +create table t1 (a bigint unsigned) +partition by range (a) +(partition p0 values less than (0), + partition p1 values less than (10)); +show create table t1; +drop table t1; + +create table t1 (a bigint unsigned) +partition by range (a) +(partition p0 values less than (2), + partition p1 values less than (10)); +show create table t1; +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +insert into t1 values (0xFFFFFFFFFFFFFFFF); +drop table t1; + +# +# BUG 18962 Errors in DROP PARTITION +# +create table t1 (a int) +partition by range (MOD(a,3)) +subpartition by hash(a) +subpartitions 2 +(partition p0 values less than (1), + partition p1 values less than (2), + partition p2 values less than (3), + partition p3 values less than (4)); +ALTER TABLE t1 DROP PARTITION p3; +ALTER TABLE t1 DROP PARTITION p1; +ALTER TABLE t1 DROP PARTITION p2; +drop table t1; + +create table t1 (a int) +partition by range (MOD(a,3)) +subpartition by hash(a) +subpartitions 2 +(partition p0 values less than (1), + partition p1 values less than (2), + partition p2 values less than (3), + partition p3 values less than (4)); +ALTER TABLE t1 DROP PARTITION p0; +ALTER TABLE t1 DROP PARTITION p1; +ALTER TABLE t1 DROP PARTITION p2; +drop table t1; + +# +# Bug 19830: ALTER TABLE t1 REORGANIZE PARTITION crashes +# +create table t1 (a int DEFAULT NULL, + b varchar(30) DEFAULT NULL, + c date DEFAULT NULL) +ENGINE=MYISAM DEFAULT CHARSET=latin1; + +insert into t1 values (1, 'abc', '1995-01-01'); +insert into t1 values (1, 'abc', '1995-01-02'); +insert into t1 values (1, 'abc', '1995-01-03'); +insert into t1 values (1, 'abc', '1995-01-04'); +insert into t1 values (1, 'abc', '1995-01-05'); +insert into t1 values (1, 'abc', '1995-01-06'); +insert into t1 values (1, 'abc', '1995-01-07'); +insert into t1 values (1, 'abc', '1995-01-08'); +insert into t1 values (1, 'abc', '1995-01-09'); +insert into t1 values (1, 'abc', '1995-01-10'); +insert into t1 values (1, 'abc', '1995-01-11'); +insert into t1 values (1, 'abc', '1995-01-12'); +insert into t1 values (1, 'abc', '1995-01-13'); +insert into t1 values (1, 'abc', '1995-01-14'); +insert into t1 values (1, 'abc', '1995-01-15'); +insert into t1 values (1, 'abc', '1997-01-01'); +insert into t1 values (1, 'abc', '1997-01-02'); +insert into t1 values (1, 'abc', '1997-01-03'); +insert into t1 values (1, 'abc', '1997-01-04'); +insert into t1 values (1, 'abc', '1997-01-05'); +insert into t1 values (1, 'abc', '1997-01-06'); +insert into t1 values (1, 'abc', '1997-01-07'); +insert into t1 values (1, 'abc', '1997-01-08'); +insert into t1 values (1, 'abc', '1997-01-09'); +insert into t1 values (1, 'abc', '1997-01-10'); +insert into t1 values (1, 'abc', '1997-01-11'); +insert into t1 values (1, 'abc', '1997-01-12'); +insert into t1 values (1, 'abc', '1997-01-13'); +insert into t1 values (1, 'abc', '1997-01-14'); +insert into t1 values (1, 'abc', '1997-01-15'); +insert into t1 values (1, 'abc', '1998-01-01'); +insert into t1 values (1, 'abc', '1998-01-02'); +insert into t1 values (1, 'abc', '1998-01-03'); +insert into t1 values (1, 'abc', '1998-01-04'); +insert into t1 values (1, 'abc', '1998-01-05'); +insert into t1 values (1, 'abc', '1998-01-06'); +insert into t1 values (1, 'abc', '1998-01-07'); +insert into t1 values (1, 'abc', '1998-01-08'); +insert into t1 values (1, 'abc', '1998-01-09'); +insert into t1 values (1, 'abc', '1998-01-10'); +insert into t1 values (1, 'abc', '1998-01-11'); +insert into t1 values (1, 'abc', '1998-01-12'); +insert into t1 values (1, 'abc', '1998-01-13'); +insert into t1 values (1, 'abc', '1998-01-14'); +insert into t1 values (1, 'abc', '1998-01-15'); +insert into t1 values (1, 'abc', '1999-01-01'); +insert into t1 values (1, 'abc', '1999-01-02'); +insert into t1 values (1, 'abc', '1999-01-03'); +insert into t1 values (1, 'abc', '1999-01-04'); +insert into t1 values (1, 'abc', '1999-01-05'); +insert into t1 values (1, 'abc', '1999-01-06'); +insert into t1 values (1, 'abc', '1999-01-07'); +insert into t1 values (1, 'abc', '1999-01-08'); +insert into t1 values (1, 'abc', '1999-01-09'); +insert into t1 values (1, 'abc', '1999-01-10'); +insert into t1 values (1, 'abc', '1999-01-11'); +insert into t1 values (1, 'abc', '1999-01-12'); +insert into t1 values (1, 'abc', '1999-01-13'); +insert into t1 values (1, 'abc', '1999-01-14'); +insert into t1 values (1, 'abc', '1999-01-15'); +insert into t1 values (1, 'abc', '2000-01-01'); +insert into t1 values (1, 'abc', '2000-01-02'); +insert into t1 values (1, 'abc', '2000-01-03'); +insert into t1 values (1, 'abc', '2000-01-04'); +insert into t1 values (1, 'abc', '2000-01-05'); +insert into t1 values (1, 'abc', '2000-01-06'); +insert into t1 values (1, 'abc', '2000-01-07'); +insert into t1 values (1, 'abc', '2000-01-08'); +insert into t1 values (1, 'abc', '2000-01-09'); +insert into t1 values (1, 'abc', '2000-01-15'); +insert into t1 values (1, 'abc', '2000-01-11'); +insert into t1 values (1, 'abc', '2000-01-12'); +insert into t1 values (1, 'abc', '2000-01-13'); +insert into t1 values (1, 'abc', '2000-01-14'); +insert into t1 values (1, 'abc', '2000-01-15'); +insert into t1 values (1, 'abc', '2001-01-01'); +insert into t1 values (1, 'abc', '2001-01-02'); +insert into t1 values (1, 'abc', '2001-01-03'); +insert into t1 values (1, 'abc', '2001-01-04'); +insert into t1 values (1, 'abc', '2001-01-05'); +insert into t1 values (1, 'abc', '2001-01-06'); +insert into t1 values (1, 'abc', '2001-01-07'); +insert into t1 values (1, 'abc', '2001-01-08'); +insert into t1 values (1, 'abc', '2001-01-09'); +insert into t1 values (1, 'abc', '2001-01-15'); +insert into t1 values (1, 'abc', '2001-01-11'); +insert into t1 values (1, 'abc', '2001-01-12'); +insert into t1 values (1, 'abc', '2001-01-13'); +insert into t1 values (1, 'abc', '2001-01-14'); +insert into t1 values (1, 'abc', '2001-01-15'); + +alter table t1 +partition by range (year(c)) +(partition p5 values less than (2000), partition p10 values less than (2010)); + +alter table t1 +reorganize partition p5 into +(partition p1 values less than (1996), + partition p2 values less than (1997), + partition p3 values less than (1998), + partition p4 values less than (1999), + partition p5 values less than (2000)); + +drop table t1; + +# +# New test cases for date based partitioning +# +CREATE TABLE t1 (a date) +PARTITION BY RANGE (TO_DAYS(a)) +(PARTITION p3xx VALUES LESS THAN (TO_DAYS('2004-01-01')), + PARTITION p401 VALUES LESS THAN (TO_DAYS('2004-02-01')), + PARTITION p402 VALUES LESS THAN (TO_DAYS('2004-03-01')), + PARTITION p403 VALUES LESS THAN (TO_DAYS('2004-04-01')), + PARTITION p404 VALUES LESS THAN (TO_DAYS('2004-05-01')), + PARTITION p405 VALUES LESS THAN (TO_DAYS('2004-06-01')), + PARTITION p406 VALUES LESS THAN (TO_DAYS('2004-07-01')), + PARTITION p407 VALUES LESS THAN (TO_DAYS('2004-08-01')), + PARTITION p408 VALUES LESS THAN (TO_DAYS('2004-09-01')), + PARTITION p409 VALUES LESS THAN (TO_DAYS('2004-10-01')), + PARTITION p410 VALUES LESS THAN (TO_DAYS('2004-11-01')), + PARTITION p411 VALUES LESS THAN (TO_DAYS('2004-12-01')), + PARTITION p412 VALUES LESS THAN (TO_DAYS('2005-01-01')), + PARTITION p501 VALUES LESS THAN (TO_DAYS('2005-02-01')), + PARTITION p502 VALUES LESS THAN (TO_DAYS('2005-03-01')), + PARTITION p503 VALUES LESS THAN (TO_DAYS('2005-04-01')), + PARTITION p504 VALUES LESS THAN (TO_DAYS('2005-05-01')), + PARTITION p505 VALUES LESS THAN (TO_DAYS('2005-06-01')), + PARTITION p506 VALUES LESS THAN (TO_DAYS('2005-07-01')), + PARTITION p507 VALUES LESS THAN (TO_DAYS('2005-08-01')), + PARTITION p508 VALUES LESS THAN (TO_DAYS('2005-09-01')), + PARTITION p509 VALUES LESS THAN (TO_DAYS('2005-10-01')), + PARTITION p510 VALUES LESS THAN (TO_DAYS('2005-11-01')), + PARTITION p511 VALUES LESS THAN (TO_DAYS('2005-12-01')), + PARTITION p512 VALUES LESS THAN (TO_DAYS('2006-01-01')), + PARTITION p601 VALUES LESS THAN (TO_DAYS('2006-02-01')), + PARTITION p602 VALUES LESS THAN (TO_DAYS('2006-03-01')), + PARTITION p603 VALUES LESS THAN (TO_DAYS('2006-04-01')), + PARTITION p604 VALUES LESS THAN (TO_DAYS('2006-05-01')), + PARTITION p605 VALUES LESS THAN (TO_DAYS('2006-06-01')), + PARTITION p606 VALUES LESS THAN (TO_DAYS('2006-07-01')), + PARTITION p607 VALUES LESS THAN (TO_DAYS('2006-08-01'))); + +INSERT INTO t1 VALUES ('2003-01-13'),('2003-06-20'),('2003-08-30'); +INSERT INTO t1 VALUES ('2003-04-13'),('2003-07-20'),('2003-10-30'); +INSERT INTO t1 VALUES ('2003-05-13'),('2003-11-20'),('2003-12-30'); + +INSERT INTO t1 VALUES ('2004-01-13'),('2004-01-20'),('2004-01-30'); +INSERT INTO t1 VALUES ('2004-02-13'),('2004-02-20'),('2004-02-28'); +INSERT INTO t1 VALUES ('2004-03-13'),('2004-03-20'),('2004-03-30'); +INSERT INTO t1 VALUES ('2004-04-13'),('2004-04-20'),('2004-04-30'); +INSERT INTO t1 VALUES ('2004-05-13'),('2004-05-20'),('2004-05-30'); +INSERT INTO t1 VALUES ('2004-06-13'),('2004-06-20'),('2004-06-30'); +INSERT INTO t1 VALUES ('2004-07-13'),('2004-07-20'),('2004-07-30'); +INSERT INTO t1 VALUES ('2004-08-13'),('2004-08-20'),('2004-08-30'); +INSERT INTO t1 VALUES ('2004-09-13'),('2004-09-20'),('2004-09-30'); +INSERT INTO t1 VALUES ('2004-10-13'),('2004-10-20'),('2004-10-30'); +INSERT INTO t1 VALUES ('2004-11-13'),('2004-11-20'),('2004-11-30'); +INSERT INTO t1 VALUES ('2004-12-13'),('2004-12-20'),('2004-12-30'); + +INSERT INTO t1 VALUES ('2005-01-13'),('2005-01-20'),('2005-01-30'); +INSERT INTO t1 VALUES ('2005-02-13'),('2005-02-20'),('2005-02-28'); +INSERT INTO t1 VALUES ('2005-03-13'),('2005-03-20'),('2005-03-30'); +INSERT INTO t1 VALUES ('2005-04-13'),('2005-04-20'),('2005-04-30'); +INSERT INTO t1 VALUES ('2005-05-13'),('2005-05-20'),('2005-05-30'); +INSERT INTO t1 VALUES ('2005-06-13'),('2005-06-20'),('2005-06-30'); +INSERT INTO t1 VALUES ('2005-07-13'),('2005-07-20'),('2005-07-30'); +INSERT INTO t1 VALUES ('2005-08-13'),('2005-08-20'),('2005-08-30'); +INSERT INTO t1 VALUES ('2005-09-13'),('2005-09-20'),('2005-09-30'); +INSERT INTO t1 VALUES ('2005-10-13'),('2005-10-20'),('2005-10-30'); +INSERT INTO t1 VALUES ('2005-11-13'),('2005-11-20'),('2005-11-30'); +INSERT INTO t1 VALUES ('2005-12-13'),('2005-12-20'),('2005-12-30'); + +INSERT INTO t1 VALUES ('2006-01-13'),('2006-01-20'),('2006-01-30'); +INSERT INTO t1 VALUES ('2006-02-13'),('2006-02-20'),('2006-02-28'); +INSERT INTO t1 VALUES ('2006-03-13'),('2006-03-20'),('2006-03-30'); +INSERT INTO t1 VALUES ('2006-04-13'),('2006-04-20'),('2006-04-30'); +INSERT INTO t1 VALUES ('2006-05-13'),('2006-05-20'),('2006-05-30'); +INSERT INTO t1 VALUES ('2006-06-13'),('2006-06-20'),('2006-06-30'); +INSERT INTO t1 VALUES ('2006-07-13'),('2006-07-20'),('2006-07-30'); + +SELECT * FROM t1 +WHERE a >= '2004-07-01' AND a <= '2004-09-30'; +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE a >= '2004-07-01' AND a <= '2004-09-30'; +SELECT * from t1 +WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR + (a >= '2005-07-01' AND a <= '2005-09-30'); +EXPLAIN PARTITIONS SELECT * from t1 +WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR + (a >= '2005-07-01' AND a <= '2005-09-30'); +DROP TABLE t1; + +# +# Bug 18198: Try with a couple of cases using VARCHAR fields in +# partition function. +#create table t1 (a varchar(20)) +#partition by range (ascii(a)) +#(partition p0 values less than (100), +# partition p1 values less than maxvalue); + +#insert into t1 values ("12345678901234567890"); +#insert into t1 values ("A2345678901234567890"); +#insert into t1 values ("B2345678901234567890"); +#insert into t1 values ("1234567890123456789"); +#insert into t1 values ("1234567890123456"); +#select * from t1; +#explain partitions select * from t1 where a = "12345678901234567890"; +#explain partitions select * from t1 where a = "12345678901234567890" OR +# a = "A2345678901234567890" OR +# a = "B2345678901234567890" OR +# a = "C2345678901234567890"; +#explain partitions select * from t1 where a = "01234567890123456"; +#select * from t1 where a = "01234567890123456"; +#select * from t1 where a = "12345678901234567890" OR +# a = "A2345678901234567890" OR +# a = "B2345678901234567890" OR +# a = "C2345678901234567890"; +#select * from t1 where a = "12345678901234567890"; +#drop table t1; + + +# +# BUG#30573: get wrong result with "group by" on PARTITIONed table +# +create table t1 (a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +CREATE TABLE t2 ( + defid int(10) unsigned NOT NULL, + day int(10) unsigned NOT NULL, + count int(10) unsigned NOT NULL, + filler char(200), + KEY (defid,day) +) +PARTITION BY RANGE (day) ( + PARTITION p7 VALUES LESS THAN (20070401) , + PARTITION p8 VALUES LESS THAN (20070501)); + +insert into t2 select 20, 20070311, 1, 'filler' from t1 A, t1 B; +insert into t2 select 20, 20070411, 1, 'filler' from t1 A, t1 B; +insert into t2 values(52, 20070321, 123, 'filler') ; +insert into t2 values(52, 20070322, 456, 'filler') ; + +select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 20070320 and 20070401 group by defid; +drop table t1, t2; + + +--echo # +--echo # Bug#50939: Loose Index Scan unduly relies on engine to remember range +--echo # endpoints +--echo # +CREATE TABLE t1 ( + a INT, + b INT, + KEY ( a, b ) +) PARTITION BY HASH (a) PARTITIONS 1; + +CREATE TABLE t2 ( + a INT, + b INT, + KEY ( a, b ) +); + +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); + +INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; +INSERT INTO t1 SELECT a + 10, b + 10 FROM t1; +INSERT INTO t1 SELECT a + 20, b + 20 FROM t1; +INSERT INTO t1 SELECT a + 40, b + 40 FROM t1; + +INSERT INTO t2 SELECT * FROM t1; + +ANALYZE TABLE t1,t2; + +--echo # plans should be identical +EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; +EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; + +--disable_ps2_protocol +FLUSH status; +SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; +--echo # Should be no more than 4 reads. +SHOW status LIKE 'handler_read_key'; + +FLUSH status; +SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; +--echo # Should be no more than 4 reads. +SHOW status LIKE 'handler_read_key'; +--enable_ps2_protocol + +--echo # +--echo # MDEV-18501 Partition pruning doesn't work for historical queries +--echo # +set time_zone= '+00:00'; +let $ts= `select unix_timestamp('2000-01-01 00:00:00') + 1`; + +--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR +create or replace table t1 (d datetime(6)) +partition by range (unix_timestamp(d)) ( + partition p0 values less than (1), + partition p1 values less than (maxvalue)); + +--echo # DECIMAL functions are now allowed, partitioning is done by integer part +eval create or replace table t1 (d timestamp(6)) +partition by range (unix_timestamp(d)) ( + partition p0 values less than ($ts), + partition p1 values less than (maxvalue)); + +insert into t1 values + # go to p0 + ('2000-01-01 00:00:00'), + ('2000-01-01 00:00:00.000001'), + # goes to p1 + ('2000-01-01 00:00:01'); +select * from t1 partition (p0); +select * from t1 partition (p1); + +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-21195 INSERT chooses wrong partition for RANGE partitioning by DECIMAL column +--echo # +create or replace table t ( + d decimal(2,1)) partition by range (d) + (partition p1 values less than (10)); + +insert into t values (9.9); + +create or replace table t ( + d decimal(2,1)) partition by range (d) + (partition p1 values less than (10), + partition p2 values less than (20)); + +insert into t values (9.9); +select * from t partition (p1); +select * from t partition (p2); + +create or replace table t ( + d decimal(2,1)) partition by range (d) + (partition p1 values less than (-3)); + +insert into t values (-3.3); + +create or replace table t ( + d decimal(2,1)) partition by range (d+1) + (partition p1 values less than (10), + partition p2 values less than (20)); + +insert into t values (8.9); +select * from t partition (p1); +select * from t partition (p2); + +set time_zone='+00:00'; +create or replace table t ( + d timestamp(1)) partition by range (unix_timestamp(d)) + (partition p1 values less than (1577836800), + partition p2 values less than (2000000000)); + +insert into t values (from_unixtime(1577836799.9)); +select * from t partition (p1); +select * from t partition (p2); + +set time_zone=default; +drop table t; |