diff options
Diffstat (limited to 'mysql-test/t/partition_default.test')
| -rw-r--r-- | mysql-test/t/partition_default.test | 522 |
1 files changed, 522 insertions, 0 deletions
diff --git a/mysql-test/t/partition_default.test b/mysql-test/t/partition_default.test new file mode 100644 index 00000000000..1110b311c29 --- /dev/null +++ b/mysql-test/t/partition_default.test @@ -0,0 +1,522 @@ + +--source include/have_partition.inc + +# +# expression lists +# +create table t1 (a int, b int) + PARTITION BY LIST (a) + ( + PARTITION p2 VALUES IN (4,5,6), + PARTITION p1 VALUES IN (1) + ) +; +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +insert into t1 values (10,10); +drop table t1; +create table t1 (a int, b int) + PARTITION BY LIST (a) + ( + PARTITION p2 VALUES IN (4,5,6), + PARTITION p1 VALUES IN (1), + PARTITION p0 DEFAULT + ) +; +show create table t1; +insert into t1 values (10,10); +insert into t1 values (4,4); +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +drop table t1; + +--error ER_PARTITION_DEFAULT_ERROR +create table t1 (a int, b int) + PARTITION BY LIST (a) + ( + PARTITION p2 VALUES IN (4,5,6), + PARTITION p1 VALUES IN (1), + PARTITION p0 DEFAULT, + PARTITION p3 DEFAULT + ) +; +--error ER_PARTITION_DEFAULT_ERROR +create table t1 (a int, b int) + PARTITION BY LIST (a) + ( + PARTITION p0 DEFAULT, + PARTITION p2 VALUES IN (4,5,6), + PARTITION p1 VALUES IN (1), + PARTITION p3 DEFAULT + ) +; + +create table t1 (a int, b int) + PARTITION BY LIST (a) + ( + PARTITION p0 DEFAULT, + PARTITION p2 VALUES IN (4,5,6), + PARTITION p1 VALUES IN (1) + ) +; +show create table t1; +insert into t1 values (10,10); +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +drop table t1; + +# +# Default has its value as 0 check that they are not clash. +# +create table t1 (a int, b int) + PARTITION BY LIST (a) + ( + PARTITION p0 DEFAULT, + PARTITION p2 VALUES IN (4,5,6), + PARTITION p1 VALUES IN (1, 0) + ) +; +show create table t1; +insert into t1 values (10,10); +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +drop table t1; + +# +# columns lists +# +create table t1 (a int, b int) + PARTITION BY LIST COLUMNS(a,b) + ( + PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)), + PARTITION p1 VALUES IN ((1,1),(0,0)), + PARTITION p0 DEFAULT + ) +; +show create table t1; +insert into t1 values (10,10); +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +drop table t1; + +--error ER_PARTITION_DEFAULT_ERROR +create table t1 (a int, b int) + PARTITION BY LIST COLUMNS(a,b) + ( + PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)), + PARTITION p1 VALUES IN ((1,1),(0,0)), + PARTITION p0 DEFAULT, + PARTITION p3 DEFAULT + ) +; + +--error ER_PARTITION_DEFAULT_ERROR +create table t1 (a int, b int) + PARTITION BY LIST COLUMNS(a,b) + ( + PARTITION p0 DEFAULT, + PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)), + PARTITION p1 VALUES IN ((1,1),(0,0)), + PARTITION p3 DEFAULT + ) +; + +# +# partititon prunning test +# + +create table t1 (a int, b int) + PARTITION BY LIST (a) + ( + PARTITION p2 VALUES IN (4,5,6), + PARTITION p1 VALUES IN (1,20), + PARTITION p0 default + ) +; +show create table t1; +insert into t1 values (10,10); +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +select * from t1 where a=10; +select * from t1 where a<=10; +select * from t1 where a<=20; +select * from t1 where a>=10; +select * from t1 where a>=5; +insert into t1 values (20,20),(5,5); +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +select * from t1 where a=10; +select * from t1 where a<=10; +select * from t1 where a<=20; +select * from t1 where a>=10; +select * from t1 where a>=5; +explain partitions select * from t1 where a=10; +explain partitions select * from t1 where a=5; +select * from t1 where a=10 or a=5; +explain partitions select * from t1 where a=10 or a=5; + +drop table t1; + +create table t1 (a int, b int) + PARTITION BY LIST COLUMNS(a,b) + ( + PARTITION p2 VALUES IN ((1,4),(2,5),(3,6),(5,5)), + PARTITION p1 VALUES IN ((1,1),(20,20)), + PARTITION p0 DEFAULT + ) +; +show create table t1; +insert into t1 values (10,10); +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +select * from t1 where a=10 and b=10; +explain partitions select * from t1 where a=10 and b=10; +select * from t1 where a=10; +explain partitions select * from t1 where a=10; +select * from t1 where a<=10; +select * from t1 where a>=10; +insert into t1 values (20,20),(5,5); +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +select * from t1 where a=10 and b=10; +explain partitions select * from t1 where a=10 and b=10; +select * from t1 where a=10 and b=10 or a=20 and b=20; +explain partitions select * from t1 where a=10 and b=10 or a=20 and b=20; +drop table t1; + +# +# partition pruning with expressions +# +create table t1 (a int, b int); + +insert into t1 values (10,10),(2,5),(0,0); + +select * from t1; + +alter table t1 + PARTITION BY LIST (a+b) + ( + PARTITION p2 VALUES IN (1,2,3,7), + PARTITION p1 VALUES IN (21,0), + PARTITION p0 DEFAULT + ) +; +show create table t1; +select * from t1; +explain partitions select * from t1 where a=2 and b=5; +explain partitions select * from t1 where a=10 and b=10; +drop table t1; + +create table t1 (a int, b int); + +insert into t1 values (10,10),(2,5),(0,0); + +select * from t1; + +alter table t1 + PARTITION BY LIST (a+5) + ( + PARTITION p2 VALUES IN (1,2,3,7), + PARTITION p1 VALUES IN (0), + PARTITION p0 DEFAULT + ) +; +show create table t1; +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +explain partitions select * from t1 where a>=2; +explain partitions select * from t1 where a>=2 and a<=3; +explain partitions select * from t1 where a=10; +drop table t1; + + + +CREATE TABLE t1 (a DATE, KEY(a)) +PARTITION BY LIST (TO_DAYS(a)) +(PARTITION `pDEF` DEFAULT, + PARTITION `p2001-01-01` VALUES IN (TO_DAYS('2001-01-01')), + PARTITION `pNULL` VALUES IN (NULL), + PARTITION `p0000-01-02` VALUES IN (TO_DAYS('0000-01-02')), + PARTITION `p1001-01-01` VALUES IN (TO_DAYS('1001-01-01'))); +if ($verify_without_partitions) +{ +ALTER TABLE t1 REMOVE PARTITIONING; +} +INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), + ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); +--source include/partition_date_range.inc +--echo # test without index +ALTER TABLE t1 DROP KEY a; +--source include/partition_date_range.inc +DROP TABLE t1; +--echo # TO_SECONDS, test of LIST and index +CREATE TABLE t1 (a DATE, KEY(a)) +PARTITION BY LIST (TO_SECONDS(a)) +(PARTITION `pDEF` DEFAULT, + PARTITION `p2001-01-01` VALUES IN (TO_SECONDS('2001-01-01')), + PARTITION `pNULL` VALUES IN (NULL), + PARTITION `p0000-01-02` VALUES IN (TO_SECONDS('0000-01-02')), + PARTITION `p1001-01-01` VALUES IN (TO_SECONDS('1001-01-01'))); +if ($verify_without_partitions) +{ +ALTER TABLE t1 REMOVE PARTITIONING; +} +INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), + ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); +--source include/partition_date_range.inc +--echo # test without index +ALTER TABLE t1 DROP KEY a; +--source include/partition_date_range.inc +DROP TABLE t1; + +# +# ALTER TABLE test +# + +create table t1 (a int, b int); + +insert into t1 values (10,10),(2,5),(0,0); + +select * from t1; + +alter table t1 + PARTITION BY LIST (a) + ( + PARTITION p2 VALUES IN (1,2,3), + PARTITION p1 VALUES IN (20,0), + PARTITION p0 DEFAULT + ) +; +show create table t1; +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +explain partitions select * from t1 where a=2 and b=5; +explain partitions select * from t1 where a=10 and b=10; +alter table t1 + PARTITION BY LIST (a) + ( + PARTITION p2 VALUES IN (1,2,3), + PARTITION p1 VALUES IN (20,0), + PARTITION p0 VALUES IN (10) + ) +; +show create table t1; +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +explain partitions select * from t1 where a=2 and b=5; +explain partitions select * from t1 where a=10 and b=10; +alter table t1 + PARTITION BY LIST (a) + ( + PARTITION p2 DEFAULT, + PARTITION p1 VALUES IN (20,0), + PARTITION p0 VALUES IN (10) + ) +; +show create table t1; +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +explain partitions select * from t1 where a=2 and b=5; +explain partitions select * from t1 where a=10 and b=10; + +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +alter table t1 drop partition p2; +delete from t1 where a=2; +alter table t1 drop partition p2; +show create table t1; +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; + +alter table t1 add partition (PARTITION pd DEFAULT); +show create table t1; +--error ER_PARTITION_DEFAULT_ERROR +alter table t1 add partition (PARTITION pdd DEFAULT); +alter table t1 drop partition pd; +--error ER_PARTITION_DEFAULT_ERROR +alter table t1 add partition (PARTITION pdd DEFAULT, + PARTITION pd DEFAULT); + +drop table t1; + +create table t1 (a int, b int); + +insert into t1 values (10,10),(2,5),(0,0); + +select * from t1; + +alter table t1 + PARTITION BY LIST COLUMNS(a,b) + ( + PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)), + PARTITION p1 VALUES IN ((1,1),(0,0)), + PARTITION p0 DEFAULT + ) +; +show create table t1; +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +explain partitions select * from t1 where a=2 and b=5; +explain partitions select * from t1 where a=10 and b=10; +alter table t1 + PARTITION BY LIST COLUMNS(a,b) + ( + PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)), + PARTITION p1 VALUES IN ((1,1),(0,0)), + PARTITION p0 VALUES IN ((10,10)) + ) +; +show create table t1; +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +explain partitions select * from t1 where a=2 and b=5; +explain partitions select * from t1 where a=10 and b=10; +alter table t1 + PARTITION BY LIST COLUMNS(a,b) + ( + PARTITION p2 DEFAULT, + PARTITION p1 VALUES IN ((1,1),(0,0)), + PARTITION p0 VALUES IN ((10,10)) + ) +; +show create table t1; +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +explain partitions select * from t1 where a=2 and b=5; +explain partitions select * from t1 where a=10 and b=10; + +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +alter table t1 drop partition p2; +delete from t1 where a=2; +alter table t1 drop partition p2; +show create table t1; +select * from t1; +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; + +alter table t1 add partition (PARTITION pd DEFAULT); +show create table t1; +--error ER_PARTITION_DEFAULT_ERROR +alter table t1 add partition (PARTITION pdd DEFAULT); +alter table t1 drop partition pd; +--error ER_PARTITION_DEFAULT_ERROR +alter table t1 add partition (PARTITION pdd DEFAULT, + PARTITION pd DEFAULT); +drop table t1; + + +# +# Problem of reorganizing DEFAULT partition +# +create table t1 (a int) + PARTITION BY LIST (a) + ( + PARTITION p2 VALUES IN (4,5,6), + PARTITION p1 VALUES IN (1), + PARTITION pd DEFAULT + ) +; +insert into t1 values (1),(2),(3),(4); +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; + +alter table t1 add partition + (partition p0 VALUES IN (2,3)); + +--sorted_result +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; + +drop table t1; + +# +# Check that first DEFAULT works in LIST COLUMN +# +create table t1 (a int, b int) + PARTITION BY LIST COLUMNS(a,b) + ( + PARTITION p0 DEFAULT, + PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)), + PARTITION p1 VALUES IN ((1,1),(0,0)) + ) +; + +show create table t1; + +drop table t1; + +--echo # +--echo # MDEV-10765: Wrong result - query does not retrieve values from +--echo # default partition on a table partitioned by list columns +--echo # + +create table t1 (i int, j int) partition by list columns(i,j) (partition p1 values in ((10,10)), partition p2 default); +insert into t1 values (10,1); +select * from t1 where i = 10; +explain partitions +select * from t1 where i = 10; +select * from t1 where i = 10 and j=1; +explain partitions +select * from t1 where i = 10 and j=1; +insert into t1 values (10,10); +select * from t1 where i = 10 and j=10; +explain partitions +select * from t1 where i = 10 and j=10; +drop table t1; + +create table t1 +( + a int not null, + b int not null, + c int +) +partition by list columns(a,b) +( + partition p1 values in ((10,10)), + partition p2 values in ((10,20)), + partition p3 values in ((10,30)), + partition p4 values in ((10,40)), + partition p5 values in ((10,50)) +); + +insert into t1 values + (10,10,1234), + (10,20,1234), + (10,30,1234), + (10,40,1234), + (10,50,1234); + +explain partitions +select * from t1 +where a>=10 and (a <=10 and b <=30); +drop table t1; + +--echo # +--echo # MDEV-10763: Wrong result - server does not return NULL values +--echo # from default list partition after ALTER table +--echo # +create table t1 (i int) partition by list (i) ( partition p1 default); +insert into t1 values (null); +select * from t1 where i is null; +alter table t1 partition by list (i) ( partition p1 values in (1), partition p2 default); +select * from t1 where i is null; +explain partitions +select * from t1 where i is null; +alter table t1 partition by list (i) ( + partition p0 values in (NULL), + partition p1 values in (1), + partition p2 default); +select * from t1 where i is null; +explain partitions +select * from t1 where i is null; + +drop table t1; |