diff options
author | Mikael Ronstrom <mikael@mysql.com> | 2009-09-15 17:07:52 +0200 |
---|---|---|
committer | Mikael Ronstrom <mikael@mysql.com> | 2009-09-15 17:07:52 +0200 |
commit | 6942c25e733967e4d3da8aaad3b2e9a2832dc33e (patch) | |
tree | ee714c9620c9eaa85fba98f4423487d638cc9edf /mysql-test | |
parent | 905d715f10e711860311e0a10488c6bb5e19ee49 (diff) | |
download | mariadb-git-6942c25e733967e4d3da8aaad3b2e9a2832dc33e.tar.gz |
WL#3352, Introducing Column list partitioning, makes it possible to partition on most data types, makes it possible to prune on multi-field partitioning
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/r/partition_mgm_err.result | 2 | ||||
-rw-r--r-- | mysql-test/r/partition_range.result | 94 | ||||
-rw-r--r-- | mysql-test/suite/parts/inc/partition_key_32col.inc | 2 | ||||
-rw-r--r-- | mysql-test/t/partition_column.test | 209 | ||||
-rw-r--r-- | mysql-test/t/partition_column_prune.test | 71 | ||||
-rw-r--r-- | mysql-test/t/partition_error.test | 6 | ||||
-rw-r--r-- | mysql-test/t/partition_mgm_err.test | 2 | ||||
-rw-r--r-- | mysql-test/t/partition_range.test | 76 | ||||
-rw-r--r-- | mysql-test/t/type_decimal.test | 10 |
9 files changed, 461 insertions, 11 deletions
diff --git a/mysql-test/r/partition_mgm_err.result b/mysql-test/r/partition_mgm_err.result index f8403988f47..a13278d724e 100644 --- a/mysql-test/r/partition_mgm_err.result +++ b/mysql-test/r/partition_mgm_err.result @@ -41,7 +41,7 @@ ERROR HY000: Reorganize of range partitions cannot change total ranges except fo ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO (PARTITION x01 VALUES LESS THAN (4), PARTITION x11 VALUES LESS THAN (2)); -ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range +ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO (PARTITION x01 VALUES LESS THAN (6), PARTITION x11 VALUES LESS THAN (4)); diff --git a/mysql-test/r/partition_range.result b/mysql-test/r/partition_range.result index e8fc55b759b..fc15665d698 100644 --- a/mysql-test/r/partition_range.result +++ b/mysql-test/r/partition_range.result @@ -1,6 +1,100 @@ drop table if exists t1, t2; create table t1 (a int) partition by range (a) +( partition p0 values less than (NULL), +partition p1 values less than (MAXVALUE)); +ERROR 42000: Not allowed to use NULL value in VALUES LESS THAN near '), +partition p1 values less than (MAXVALUE))' at line 3 +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'))); +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'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where +explain partitions select * from t1 where a < '2007-03-08 00:00:01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 4 Using where +explain partitions select * from t1 where a <= '2007-03-08 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 4 Using where +explain partitions select * from t1 where a <= '2007-03-07 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where +explain partitions select * from t1 where a < '2007-03-07 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where +drop table t1; +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; +a +2003-12-30 +2004-12-31 +explain partitions select * from t1 where a <= '2003-12-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 system NULL NULL NULL NULL 1 +select * from t1 where a <= '2003-12-31'; +a +2003-12-30 +explain partitions select * from t1 where a <= '2005-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where +select * from t1 where a <= '2005-01-01'; +a +2003-12-30 +2004-12-31 +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; +a +2004-01-01 11:59:29 +2005-01-01 11:59:59 +explain partitions select * from t1 where a <= '2004-01-01 11:59.59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 system NULL NULL NULL NULL 1 +select * from t1 where a <= '2004-01-01 11:59:59'; +a +2004-01-01 11:59:29 +explain partitions select * from t1 where a <= '2005-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where +select * from t1 where a <= '2005-01-01'; +a +2004-01-01 11:59:29 +drop table t1; +create table t1 (a int, b char(20)) +partition by range column_list(a,b) +(partition p0 values less than (1)); +ERROR 42000: Inconsistency in usage of column lists for partitioning near '))' at line 3 +create table t1 (a int, b char(20)) +partition by range(a) +(partition p0 values less than (column_list(1,"b"))); +ERROR HY000: Inconsistency in usage of column lists for partitioning +create table t1 (a int, b char(20)) +partition by range(a) +(partition p0 values less than (column_list(1,"b"))); +ERROR HY000: Inconsistency in usage of column lists for partitioning +create table t1 (a int, b char(20)); +create global index inx on t1 (a,b) +partition by range (a) +(partition p0 values less than (1)); +drop table t1; +create table t1 (a int, b char(20)) +partition by range column_list(b) +(partition p0 values less than (column_list("b"))); +drop table t1; +create table t1 (a int) +partition by range (a) ( partition p0 values less than (maxvalue)); alter table t1 add partition (partition p1 values less than (100000)); ERROR HY000: MAXVALUE can only be used in last partition definition diff --git a/mysql-test/suite/parts/inc/partition_key_32col.inc b/mysql-test/suite/parts/inc/partition_key_32col.inc index 74016d9b556..b0635ca0e9c 100644 --- a/mysql-test/suite/parts/inc/partition_key_32col.inc +++ b/mysql-test/suite/parts/inc/partition_key_32col.inc @@ -1,4 +1,4 @@ ---error ER_TOO_MANY_KEY_PARTS +--error ER_TOO_MANY_PARTITION_FUNC_FIELDS_ERROR eval create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, a1 date not null, b1 varchar(50) not null, c1 varchar(50) not null, d1 enum('m', 'w') not null, e1 int not null, f1 decimal (18,2) not null, g1 bigint not null, h1 tinyint not null, a2 date not null, b2 varchar(50) not null, c2 varchar(50) not null, d2 enum('m', 'w') not null, e2 int not null, f2 decimal (18,2) not null, g2 bigint not null, h2 tinyint not null, a3 date not null, b3 varchar(50) not null, c3 varchar(50) not null, d3 enum('m', 'w') not null, e3 int not null, f3 decimal (18,2) not null, g3 bigint not null, h3 tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3)) engine=$engine partition by key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3) ( partition pa1 max_rows=20 min_rows=2, diff --git a/mysql-test/t/partition_column.test b/mysql-test/t/partition_column.test new file mode 100644 index 00000000000..b1f5f4abfcf --- /dev/null +++ b/mysql-test/t/partition_column.test @@ -0,0 +1,209 @@ +# +# 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 + +create table t1 (a int, b char(10), c varchar(25), d datetime) +partition by range column_list(a,b,c,d) +subpartition by hash (to_seconds(d)) +subpartitions 4 +( partition p0 values less than (column_list(1, NULL, MAXVALUE, NULL)), + partition p1 values less than (column_list(1, 'a', MAXVALUE, TO_DAYS('1999-01-01'))), + partition p2 values less than (column_list(1, 'a', MAXVALUE, MAXVALUE)), + partition p3 values less than (column_list(1, MAXVALUE, MAXVALUE, MAXVALUE))); +drop table t1; + +create table t1 (a int, b char(10), c varchar(5), d int) +partition by range column_list(a,b,c) +subpartition by key (c,d) +subpartitions 3 +( partition p0 values less than (column_list(1,'abc','abc')), + partition p1 values less than (column_list(2,'abc','abc')), + partition p2 values less than (column_list(3,'abc','abc')), + partition p3 values less than (column_list(4,'abc','abc'))); + +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 column_list (a, b, c) +(partition p0 values less than (column_list(1, 'A', 1)), + partition p1 values less than (column_list(1, 'B', 1))); +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 column_list(a) +( partition p0 values in (column_list('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 timestamp) +partition by range column_list(d) +( partition p0 values less than (column_list('2000-01-01')), + partition p1 values less than (column_list('2040-01-01'))); + +create table t1 (a int, b int) +partition by range column_list(a,b) +(partition p0 values less than (column_list(null, 10))); +drop table t1; + +create table t1 (d date) +partition by range column_list(d) +( partition p0 values less than (column_list('2000-01-01')), + partition p1 values less than (column_list('2009-01-01'))); +drop table t1; + +create table t1 (d date) +partition by range column_list(d) +( partition p0 values less than (column_list('1999-01-01')), + partition p1 values less than (column_list('2000-01-01'))); +drop table t1; + +create table t1 (d date) +partition by range column_list(d) +( partition p0 values less than (column_list('2000-01-01')), + partition p1 values less than (column_list('3000-01-01'))); +drop table t1; + +create table t1 (a int, b int) +partition by range column_list(a,b) +(partition p2 values less than (column_list(99,99)), + partition p1 values less than (column_list(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 column_name='to_seconds'; +drop table t1; + +--error ER_PARSE_ERROR +create table t1 (a int, b int) +partition by list column_list(a,b) +(partition p0 values in (column_list(maxvalue,maxvalue))); +create table t1 (a int, b int) +partition by range column_list(a,b) +(partition p0 values less than (column_list(maxvalue,maxvalue))); +drop table t1; + +create table t1 (a int) +partition by list column_list(a) +(partition p0 values in (column_list(0))); +select partition_method from information_schema.partitions where table_name='t1'; +drop table t1; + +create table t1 (a char(6)) +partition by range column_list(a) +(partition p0 values less than (column_list('H23456')), + partition p1 values less than (column_list('M23456'))); +insert into t1 values ('F23456'); +select * from t1; +drop table t1; + +-- error 1054 +create table t1 (a char(6)) +partition by range column_list(a) +(partition p0 values less than (column_list(H23456)), + partition p1 values less than (column_list(M23456))); + +-- error ER_RANGE_NOT_INCREASING_ERROR +create table t1 (a char(6)) +partition by range column_list(a) +(partition p0 values less than (column_list(23456)), + partition p1 values less than (column_list(23456))); + +-- error 1064 +create table t1 (a int, b int) +partition by range column_list(a,b) +(partition p0 values less than (10)); + +-- error ER_PARTITION_COLUMN_LIST_ERROR +create table t1 (a int, b int) +partition by range column_list(a,b) +(partition p0 values less than (column_list(1,1,1)); + +create table t1 (a int, b int) +partition by range column_list(a,b) +(partition p0 values less than (column_list(1, NULL)), + partition p1 values less than (column_list(2, maxvalue)), + partition p2 values less than (column_list(3, 3)), + partition p3 values less than (column_list(10, NULL))); + +-- error ER_NO_PARTITION_FOR_GIVEN_VALUE +insert into t1 values (10,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 column_list(b,a) +(partition p0 values less than (column_list(1,2)), + partition p1 values less than (column_list(3,3)), + partition p2 values less than (column_list(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 (column_list(2,2)), + partition p12 values less than (column_list(3,3))); + +-- error ER_REORG_OUTSIDE_RANGE +alter table t1 reorganize partition p0 into +(partition p01 values less than (column_list(0,3)), + partition p02 values less than (column_list(1,1))); + +-- error ER_PARTITION_COLUMN_LIST_ERROR +alter table t1 reorganize partition p2 into +(partition p2 values less than(column_list(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 (column_list(4,7)), + partition p22 values less than (column_list(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 range column_list(a,b) +#(partition p0 values less than (column_list(99,99)), +# partition p1 values less than (column_list(99,maxvalue))); +#drop table t1; + +create table t1 (a int, b int) +partition by list column_list(a,b) +subpartition by hash (b) +subpartitions 2 +(partition p0 values in (column_list(0,0), column_list(1,1)), + partition p1 values in (column_list(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 column_list(a,b,c) +( partition p0 values less than (column_list('a','b','c'))); +alter table t1 add partition +(partition p1 values less than (column_list('b','c','d'))); +drop table t1; diff --git a/mysql-test/t/partition_column_prune.test b/mysql-test/t/partition_column_prune.test new file mode 100644 index 00000000000..52267a66b65 --- /dev/null +++ b/mysql-test/t/partition_column_prune.test @@ -0,0 +1,71 @@ +# +# Partition pruning tests for new COLUMN LIST feature +# +-- source include/have_partition.inc + +--disable_warnings +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +--enable_warnings + +create table t1 (a char, b char, c char) +partition by range column_list(a,b,c) +( partition p0 values less than (column_list('a','b','c'))); +insert into t1 values ('a', NULL, 'd'); +explain partitions select * from t1 where a = 'a' AND c = 'd'; +select * from t1 where a = 'a' AND c = 'd'; +drop table t1; + +## COLUMN_LIST partition pruning tests +create table t1 (a int not null) partition by range column_list(a) ( + partition p0 values less than (column_list(10)), + partition p1 values less than (column_list(20)), + partition p2 values less than (column_list(30)), + partition p3 values less than (column_list(40)), + partition p4 values less than (column_list(50)), + partition p5 values less than (column_list(60)), + partition p6 values less than (column_list(70)) +); +insert into t1 values (5),(15),(25),(35),(45),(55),(65); +insert into t1 values (5),(15),(25),(35),(45),(55),(65); + +create table t2 (a int not null) partition by range(a) ( + partition p0 values less than (10), + partition p1 values less than (20), + partition p2 values less than (30), + partition p3 values less than (40), + partition p4 values less than (50), + partition p5 values less than (60), + partition p6 values less than (70) +); +insert into t2 values (5),(15),(25),(35),(45),(55),(65); +insert into t2 values (5),(15),(25),(35),(45),(55),(65); + +explain partitions select * from t1 where a > 35 and a < 45; +explain partitions select * from t2 where a > 35 and a < 45; + +drop table t1, t2; + +create table t1 (a int not null, b int not null ) +partition by range column_list(a,b) ( + partition p01 values less than (column_list(2,10)), + partition p02 values less than (column_list(2,20)), + partition p03 values less than (column_list(2,30)), + + partition p11 values less than (column_list(4,10)), + partition p12 values less than (column_list(4,20)), + partition p13 values less than (column_list(4,30)), + + partition p21 values less than (column_list(6,10)), + partition p22 values less than (column_list(6,20)), + partition p23 values less than (column_list(6,30)) +); + +insert into t1 values (2,5), (2,15), (2,25), + (4,5), (4,15), (4,25), (6,5), (6,15), (6,25); +insert into t1 select * from t1; + +explain partitions select * from t1 where a=2; +explain partitions select * from t1 where a=4; +explain partitions select * from t1 where a=2 and b < 22; + +drop table t1; diff --git a/mysql-test/t/partition_error.test b/mysql-test/t/partition_error.test index 49632f95dfb..1e76945ca46 100644 --- a/mysql-test/t/partition_error.test +++ b/mysql-test/t/partition_error.test @@ -180,7 +180,7 @@ partitions 3 (partition x1, partition x2); # -# Partition by key specified 3 partitions but only defined 2 => error +# Partition by hash, random function # --error 1064 CREATE TABLE t1 ( @@ -193,7 +193,7 @@ partitions 2 (partition x1, partition x2); # -# Partition by key specified 3 partitions but only defined 2 => error +# Partition by range, random function # --error 1064 CREATE TABLE t1 ( @@ -206,7 +206,7 @@ partitions 2 (partition x1 values less than (0), partition x2 values less than (2)); # -# Partition by key specified 3 partitions but only defined 2 => error +# Partition by list, random function # --error 1064 CREATE TABLE t1 ( diff --git a/mysql-test/t/partition_mgm_err.test b/mysql-test/t/partition_mgm_err.test index 0f8b8d3cd90..f921fa8ebca 100644 --- a/mysql-test/t/partition_mgm_err.test +++ b/mysql-test/t/partition_mgm_err.test @@ -61,7 +61,7 @@ ALTER TABLE t1 REORGANIZE PARTITION x0, x1, x1 INTO ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO (PARTITION x01 VALUES LESS THAN (5)); ---error ER_REORG_OUTSIDE_RANGE +--error ER_RANGE_NOT_INCREASING_ERROR ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO (PARTITION x01 VALUES LESS THAN (4), PARTITION x11 VALUES LESS THAN (2)); diff --git a/mysql-test/t/partition_range.test b/mysql-test/t/partition_range.test index c02d9049f2e..c14dfd1822d 100644 --- a/mysql-test/t/partition_range.test +++ b/mysql-test/t/partition_range.test @@ -9,6 +9,82 @@ drop table if exists t1, t2; --enable_warnings +--error ER_PARSE_ERROR +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'))); +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'; +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'; +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'; +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 column_list(a,b) +(partition p0 values less than (1)); + +--error ER_PARTITION_COLUMN_LIST_ERROR +create table t1 (a int, b char(20)) +partition by range(a) +(partition p0 values less than (column_list(1,"b"))); + +--error ER_PARTITION_COLUMN_LIST_ERROR +create table t1 (a int, b char(20)) +partition by range(a) +(partition p0 values less than (column_list(1,"b"))); + +create table t1 (a int, b char(20)); +create global index inx on t1 (a,b) +partition by range (a) +(partition p0 values less than (1)); +drop table t1; + +create table t1 (a int, b char(20)) +partition by range column_list(b) +(partition p0 values less than (column_list("b"))); +drop table t1; + # # BUG 33429: Succeeds in adding partition when maxvalue on last partition # diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 8a81908296f..dfe36ed0905 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -8,13 +8,13 @@ SET SQL_WARNINGS=1; CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, datatype_id int(11) DEFAULT '0' NOT NULL, - minvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL, - maxvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL, + min_value decimal(20,10) DEFAULT '0.0000000000' NOT NULL, + max_value decimal(20,10) DEFAULT '0.0000000000' NOT NULL, valuename varchar(20), forecolor int(11), backcolor int(11), PRIMARY KEY (id), - UNIQUE datatype_id (datatype_id, minvalue, maxvalue) + UNIQUE datatype_id (datatype_id, min_value, max_value) ); INSERT INTO t1 VALUES ( '1', '4', '0.0000000000', '0.0000000000', 'Ei saja', '0', '16776960'); INSERT INTO t1 VALUES ( '2', '4', '1.0000000000', '1.0000000000', 'Sajab', '16777215', '255'); @@ -148,8 +148,8 @@ INSERT INTO t1 VALUES ( '139', '21', '326.0000000000', '326.0000000000', 'Lumine INSERT INTO t1 VALUES ( '143', '16', '-4.9000000000', '-0.1000000000', '', NULL, '15774720'); INSERT INTO t1 VALUES ( '145', '15', '0.0000000000', '1.9000000000', '', '0', '16769024'); INSERT INTO t1 VALUES ( '146', '16', '0.0000000000', '1.9000000000', '', '0', '16769024'); -select * from t1 where minvalue<=1 and maxvalue>=-1 and datatype_id=16; -select * from t1 where minvalue<=-1 and maxvalue>=-1 and datatype_id=16; +select * from t1 where min_value<=1 and max_value>=-1 and datatype_id=16; +select * from t1 where min_value<=-1 and max_value>=-1 and datatype_id=16; drop table t1; # |