mysql-test/main/partition_column.test

#
# Tests for the new column list partitioning introduced in second
# version for partitioning.
#
--source include/have_partition.inc

--disable_warnings
drop table if exists t1;
--enable_warnings

--echo #
--echo # Bug#52815: LIST COLUMNS doesn't insert rows in correct partition
--echo #            if muliple columns used
CREATE TABLE t1 (
id INT NOT NULL,
name VARCHAR(255),
department VARCHAR(10),
country VARCHAR(255)
) PARTITION BY LIST COLUMNS (department, country) (
PARTITION first_office VALUES IN (('dep1', 'Russia'), ('dep1', 'Croatia')),
PARTITION second_office VALUES IN (('dep2', 'Russia'))
);

INSERT INTO t1 VALUES(1, 'Ann', 'dep1', 'Russia');
INSERT INTO t1 VALUES(2, 'Bob', 'dep1', 'Croatia');
INSERT INTO t1 VALUES(3, 'Cecil', 'dep2', 'Russia');
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
INSERT INTO t1 VALUES(3, 'Dan', 'dep2', 'Croatia');
SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 't1';
SHOW CREATE TABLE t1;
SELECT * FROM t1 WHERE department = 'dep2' and country = 'Croatia';
SELECT * FROM t1 WHERE department = 'dep1' and country = 'Croatia';
DROP TABLE t1;

#
# Bug#51347: assertion with show create table + partition by columns
# on decimal column
#
--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
CREATE TABLE t1 (a DECIMAL)
PARTITION BY RANGE COLUMNS (a)
(PARTITION p0 VALUES LESS THAN (0));

--error ER_BLOB_FIELD_IN_PART_FUNC_ERROR
CREATE TABLE t1 (a BLOB)
PARTITION BY RANGE COLUMNS (a)
(PARTITION p0 VALUES LESS THAN ("X"));

--error ER_BLOB_FIELD_IN_PART_FUNC_ERROR
CREATE TABLE t1 (a TEXT)
PARTITION BY RANGE COLUMNS (a)
(PARTITION p0 VALUES LESS THAN ("X"));

--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
CREATE TABLE t1 (a FLOAT)
PARTITION BY RANGE COLUMNS (a)
(PARTITION p0 VALUES LESS THAN (0.0));

--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
CREATE TABLE t1 (a DOUBLE)
PARTITION BY RANGE COLUMNS (a)
(PARTITION p0 VALUES LESS THAN (0.0));

--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
CREATE TABLE t1 (d TIMESTAMP)
PARTITION BY RANGE COLUMNS(d)
(PARTITION p0 VALUES LESS THAN ('2000-01-01'),
 PARTITION p1 VALUES LESS THAN ('2040-01-01'));

--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
CREATE TABLE t1 (d BIT(1))
PARTITION BY RANGE COLUMNS(d)
(PARTITION p0 VALUES LESS THAN (0),
 PARTITION p1 VALUES LESS THAN (1));

--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
CREATE TABLE t1 (d ENUM("YES","NO"))
PARTITION BY RANGE COLUMNS(d)
(PARTITION p0 VALUES LESS THAN ("NO"),
 PARTITION p1 VALUES LESS THAN (MAXVALUE));

--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
CREATE TABLE t1 (d SET("Car","MC"))
PARTITION BY RANGE COLUMNS(d)
(PARTITION p0 VALUES LESS THAN ("MC"),
 PARTITION p1 VALUES LESS THAN (MAXVALUE));

#
# BUG#49180, Possible to define empty intervals for column list partitioning
#
--error ER_RANGE_NOT_INCREASING_ERROR
create table t1 (a int, b int)
partition by range columns (a,b)
( partition p0 values less than (maxvalue, 10),
  partition p1 values less than (maxvalue, maxvalue));

--error ER_RANGE_NOT_INCREASING_ERROR
create table t1 (a int, b int, c int)
partition by range columns (a,b,c)
( partition p0 values less than (1, maxvalue, 10),
  partition p1 values less than (1, maxvalue, maxvalue));

#
# BUG#48737, Search fails with ucs2
#
create table t1 (a varchar(5) character set ucs2 collate ucs2_bin)
partition by range columns (a)
(partition p0 values less than (0x0041));
insert into t1 values (0x00410000);
select hex(a) from t1 where a like 'A_';
explain partitions select hex(a) from t1 where a like 'A_';
alter table t1 remove partitioning;
select hex(a) from t1 where a like 'A_';
create index a on t1 (a);
select hex(a) from t1 where a like 'A_';
insert into t1 values ('A_');
select hex(a) from t1;
drop table t1;

#
# BUG#48161, Delivering too few records using collate syntax with partitions
#
# Test case from BUG#48447 with some extension
create table t1 (a varchar(1) character set latin1 collate latin1_general_ci)
partition by range columns(a)
( partition p0 values less than ('a'),
  partition p1 values less than ('b'),
  partition p2 values less than ('c'),
  partition p3 values less than ('d'));
insert into t1 values ('A'),('a'),('B'),('b'),('C'),('c');
select * from t1 where a > 'B' collate latin1_bin;
select * from t1 where a <> 'B' collate latin1_bin;
alter table t1 remove partitioning;
select * from t1 where a > 'B' collate latin1_bin;
select * from t1 where a <> 'B' collate latin1_bin;
drop table t1;

# Test case from BUG#48161
create table t1 (a varchar(2) character set latin1,
                 b varchar(2) character set latin1)
partition by list columns(a,b)
(partition p0 values in (('a','a')));
insert into t1 values ('A','A');
select * from t1 where b <> 'a' collate latin1_bin AND
                       a = 'A' collate latin1_bin;
alter table t1 remove partitioning;
select * from t1 where b <> 'a' collate latin1_bin AND
                       a = 'A' collate latin1_bin;
drop table t1;

create table t1 (a varchar(5))
partition by list columns(a)
( partition p0 values in ('\''),
  partition p1 values in ('\\'),
  partition p2 values in ('\0'));
show create table t1;
drop table t1;

#
# BUG#48165, sql_mode gives error
#
set @@sql_mode=allow_invalid_dates;
--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR
create table t1 (a char, b char, c date)
partition by range columns (a,b,c)
( partition p0 values less than (0,0,to_days('3000-11-31')));

--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR
create table t1 (a char, b char, c date)
partition by range columns (a,b,c)
( partition p0 values less than (0,0,'3000-11-31'));
set @@sql_mode='';

--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR
create table t1 (a int, b char(10), c varchar(25), d datetime)
partition by range columns(a,b,c,d)
subpartition by hash (to_seconds(d))
subpartitions 4
( partition p0 values less than (1, 0, MAXVALUE, '1900-01-01'),
  partition p1 values less than (1, 'a', MAXVALUE, '1999-01-01'),
  partition p2 values less than (1, 'a', MAXVALUE, MAXVALUE),
  partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE));

create table t1 (a int, b char(10), c varchar(25), d datetime)
partition by range columns(a,b,c,d)
subpartition by hash (to_seconds(d))
subpartitions 4
( partition p0 values less than (1, '0', MAXVALUE, '1900-01-01'),
  partition p1 values less than (1, 'a', MAXVALUE, '1999-01-01'),
  partition p2 values less than (1, 'b', MAXVALUE, MAXVALUE),
  partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE));
select partition_method, partition_expression, partition_description
  from information_schema.partitions where table_name = "t1";
show create table t1;
drop table t1;

--error ER_NULL_IN_VALUES_LESS_THAN
create table t1 (a int, b int)
partition by range columns (a,b)
(partition p0 values less than (NULL, maxvalue));

--error ER_MAXVALUE_IN_VALUES_IN, ER_PARSE_ERROR
create table t1 (a int, b int)
partition by list columns(a,b)
( partition p0 values in ((maxvalue, 0)));

create table t1 (a int, b int)
partition by list columns (a,b)
( partition p0 values in ((0,0)));
--error ER_MAXVALUE_IN_VALUES_IN, ER_PARSE_ERROR
alter table t1 add partition
(partition p1 values in (maxvalue, maxvalue));
drop table t1;
#
# BUG#47837, Crash when two same fields in column list processing
#
--error ER_SAME_NAME_PARTITION_FIELD
create table t1 (a int, b int)
partition by key (a,a);
--error ER_SAME_NAME_PARTITION_FIELD
create table t1 (a int, b int)
partition by list columns(a,a)
( partition p values in ((1,1)));

#
# BUG#47838, List partitioning have problems with <= and >=
#
create table t1 (a int signed)
partition by list (a)
( partition p0 values in (1, 3, 5, 7, 9, NULL),
  partition p1 values in (2, 4, 6, 8, 0));
insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8);
select * from t1 where NULL <= a;
select * from t1 where a is null;
explain partitions select * from t1 where a is null;
select * from t1 where a <= 1;
drop table t1;

create table t1 (a int signed)
partition by list columns(a)
( partition p0 values in (1, 3, 5, 7, 9, NULL),
  partition p1 values in (2, 4, 6, 8, 0));
insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8);
select * from t1 where a <= NULL;
select * from t1 where a is null;
explain partitions select * from t1 where a is null;
select * from t1 where a <= 1;
drop table t1;

create table t1 (a int, b int)
partition by list columns(a,b)
( partition p0 values in ((1, NULL), (2, NULL), (NULL, NULL)),
  partition p1 values in ((1,1), (2,2)),
  partition p2 values in ((3, NULL), (NULL, 1)));
select partition_method, partition_expression, partition_description
  from information_schema.partitions where table_name = "t1";
show create table t1;
#
# BUG#47754 Crash when selecting using NOT BETWEEN for column list partitioning
#
insert into t1 values (3, NULL);
insert into t1 values (NULL, 1);
insert into t1 values (NULL, NULL);
insert into t1 values (1, NULL);
insert into t1 values (2, NULL);
insert into t1 values (1,1);
insert into t1 values (2,2);
select * from t1 where a = 1;
select * from t1 where a = 2;
select * from t1 where a > 8;
select * from t1 where a not between 8 and 8;
show create table t1;
drop table t1;

--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR
create table t1 (a int)
partition by list (a)
( partition p0 values in (1),
  partition p1 values in (1));

create table t1 (a int)
partition by list (a)
( partition p0 values in (2, 1),
  partition p1 values in (4, NULL, 3));
select partition_method, partition_expression, partition_description
  from information_schema.partitions where table_name = "t1";
show create table t1;
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);
insert into t1 values (NULL);
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
insert into t1 values (5);
drop table t1;

--error ER_PARSE_ERROR
create table t1 (a int)
partition by list columns(a)
( partition p0 values in (2, 1),
  partition p1 values in ((4), (NULL), (3)));

create table t1 (a int)
partition by list columns(a)
( partition p0 values in (2, 1),
  partition p1 values in (4, NULL, 3));
select partition_method, partition_expression, partition_description
 from information_schema.partitions where table_name = "t1";
show create table t1;
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);
insert into t1 values (NULL);
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
insert into t1 values (5);
show create table t1;
drop table t1;

create table t1 (a int, b char(10), c varchar(5), d int)
partition by range columns(a,b,c)
subpartition by key (c,d)
subpartitions 3
( partition p0 values less than (1,'abc','abc'),
  partition p1 values less than (2,'abc','abc'),
  partition p2 values less than (3,'abc','abc'),
  partition p3 values less than (4,'abc','abc'));
select partition_method, partition_expression, partition_description
  from information_schema.partitions where table_name = "t1";
show create table t1;

insert into t1 values (1,'a','b',1),(2,'a','b',2),(3,'a','b',3);
insert into t1 values (1,'b','c',1),(2,'b','c',2),(3,'b','c',3);
insert into t1 values (1,'c','d',1),(2,'c','d',2),(3,'c','d',3);
insert into t1 values (1,'d','e',1),(2,'d','e',2),(3,'d','e',3);
select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) OR
                       (a = 1 AND b >= 'a' AND (c = 'c' OR (c = 'd' AND d = 2))));
drop table t1;

create table t1 (a int, b varchar(2), c int)
partition by range columns (a, b, c)
(partition p0 values less than (1, 'A', 1),
 partition p1 values less than (1, 'B', 1));
select partition_method, partition_expression, partition_description
  from information_schema.partitions where table_name = "t1";
show create table t1;
insert into t1 values (1, 'A', 1);
explain partitions select * from t1 where a = 1 AND b <= 'A' and c = 1;
select * from t1 where a = 1 AND b <= 'A' and c = 1;
drop table t1;

create table t1 (a char, b char, c char)
partition by list columns(a)
( partition p0 values in ('a'));
insert into t1 (a) values ('a');
select * from t1 where a = 'a';
drop table t1;

--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR
create table t1 (d time)
partition by range columns(d)
( partition p0 values less than ('2000-01-01'),
  partition p1 values less than ('2040-01-01'));

create table t1 (a int, b int)
partition by range columns(a,b)
(partition p0 values less than (maxvalue, 10));
drop table t1;

create table t1 (d date)
partition by range columns(d)
( partition p0 values less than ('2000-01-01'),
  partition p1 values less than ('2009-01-01'));
drop table t1;

create table t1 (d date)
partition by range columns(d)
( partition p0 values less than ('1999-01-01'),
  partition p1 values less than ('2000-01-01'));
drop table t1;

create table t1 (d date)
partition by range columns(d)
( partition p0 values less than ('2000-01-01'),
  partition p1 values less than ('3000-01-01'));
drop table t1;

create table t1 (a int, b int)
partition by range columns(a,b)
(partition p2 values less than (99,99),
 partition p1 values less than (99,999));

insert into t1 values (99,998);
select * from t1 where b = 998;
drop table t1;

create table t1 as select to_seconds(null) as to_seconds;
select data_type from information_schema.columns
where table_schema='test' and column_name='to_seconds';
drop table t1;

--error ER_PARSE_ERROR
create table t1 (a int, b int)
partition by list columns(a,b)
(partition p0 values in ((maxvalue,maxvalue)));
create table t1 (a int, b int)
partition by range columns(a,b)
(partition p0 values less than (maxvalue,maxvalue));
drop table t1;

create table t1 (a int)
partition by list columns(a)
(partition p0 values in (0));
select partition_method from information_schema.partitions where table_name='t1';
drop table t1;

create table t1 (a char(6))
partition by range columns(a)
(partition p0 values less than ('H23456'),
 partition p1 values less than ('M23456'));
insert into t1 values ('F23456');
select * from t1;
drop table t1;

-- error 1054
create table t1 (a char(6))
partition by range columns(a)
(partition p0 values less than (H23456),
 partition p1 values less than (M23456));

-- error ER_WRONG_TYPE_COLUMN_VALUE_ERROR
create table t1 (a char(6))
partition by range columns(a)
(partition p0 values less than (23456),
 partition p1 values less than (23456));

-- error 1064
create table t1 (a int, b int)
partition by range columns(a,b)
(partition p0 values less than (10));

-- error ER_PARTITION_COLUMN_LIST_ERROR
create table t1 (a int, b int)
partition by range columns(a,b)
(partition p0 values less than (1,1,1);

create table t1 (a int, b int)
partition by range columns(a,b)
(partition p0 values less than (1, 0),
 partition p1 values less than (2, maxvalue),
 partition p2 values less than (3, 3),
 partition p3 values less than (10, maxvalue));

-- error ER_NO_PARTITION_FOR_GIVEN_VALUE
insert into t1 values (11,0);
insert into t1 values (0,1),(1,1),(2,1),(3,1),(3,4),(4,9),(9,1);
select * from t1;

alter table t1
partition by range columns(b,a)
(partition p0 values less than (1,2),
 partition p1 values less than (3,3),
 partition p2 values less than (9,5));
explain partitions select * from t1 where b < 2;
select * from t1 where b < 2;
explain partitions select * from t1 where b < 4;
select * from t1 where b < 4;

alter table t1 reorganize partition p1 into
(partition p11 values less than (2,2),
 partition p12 values less than (3,3));

-- error ER_REORG_OUTSIDE_RANGE
alter table t1 reorganize partition p0 into
(partition p01 values less than (0,3),
 partition p02 values less than (1,1));

-- error ER_PARTITION_COLUMN_LIST_ERROR
alter table t1 reorganize partition p2 into
(partition p2 values less than(9,6,1));

-- error ER_PARTITION_COLUMN_LIST_ERROR
alter table t1 reorganize partition p2 into
(partition p2 values less than (10));

alter table t1 reorganize partition p2 into
(partition p21 values less than (4,7),
 partition p22 values less than (9,5));
explain partitions select * from t1 where b < 4;
select * from t1 where b < 4;
drop table t1;

create table t1 (a int, b int)
partition by list columns(a,b)
subpartition by hash (b)
subpartitions 2
(partition p0 values in ((0,0), (1,1)),
 partition p1 values in ((1000,1000)));
insert into t1 values (1000,1000);
#select * from t1 where a = 0 and b = 0;
drop table t1;

create table t1 (a char, b char, c char)
partition by range columns(a,b,c)
( partition p0 values less than ('a','b','c'));
alter table t1 add partition
(partition p1 values less than ('b','c','d'));
drop table t1;