mirror of
https://github.com/MariaDB/server.git
synced 2025-10-26 16:38:11 +01:00
510 lines
16 KiB
Text
510 lines
16 KiB
Text
#
|
|
# 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 multiple 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;
|