diff options
Diffstat (limited to 'mysql-test/main/partition_list.result')
| -rw-r--r-- | mysql-test/main/partition_list.result | 336 |
1 files changed, 336 insertions, 0 deletions
diff --git a/mysql-test/main/partition_list.result b/mysql-test/main/partition_list.result new file mode 100644 index 00000000000..91c1c106cea --- /dev/null +++ b/mysql-test/main/partition_list.result @@ -0,0 +1,336 @@ +drop table if exists t1; +create table t1 (a int unsigned) +partition by list (a) +(partition p0 values in (0), +partition p1 values in (1), +partition pnull values in (null), +partition p2 values in (2)); +insert into t1 values (null),(0),(1),(2); +select * from t1 where a < 2; +a +0 +1 +select * from t1 where a <= 0; +a +0 +select * from t1 where a < 1; +a +0 +select * from t1 where a > 0; +a +1 +2 +select * from t1 where a > 1; +a +2 +select * from t1 where a >= 0; +a +0 +1 +2 +select * from t1 where a >= 1; +a +1 +2 +select * from t1 where a is null; +a +NULL +select * from t1 where a is not null; +a +0 +1 +2 +select * from t1 where a is null or a > 0; +a +1 +NULL +2 +drop table t1; +create table t1 (a int unsigned, b int) +partition by list (a) +subpartition by hash (b) +subpartitions 2 +(partition p0 values in (0), +partition p1 values in (1), +partition pnull values in (null, 2), +partition p3 values in (3)); +select partition_method, partition_expression, partition_description +from information_schema.partitions where table_name = "t1"; +partition_method partition_expression partition_description +LIST `a` 0 +LIST `a` 0 +LIST `a` 1 +LIST `a` 1 +LIST `a` NULL,2 +LIST `a` NULL,2 +LIST `a` 3 +LIST `a` 3 +insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1); +insert into t1 values (2,0),(2,1),(3,0),(3,1); +explain partitions select * from t1 where a is null; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 4 Using where +select * from t1 where a is null; +a b +NULL 0 +NULL 1 +explain partitions select * from t1 where a = 2; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 4 Using where +select * from t1 where a = 2; +a b +2 0 +2 1 +select * from t1 where a <= 0; +a b +0 0 +0 1 +select * from t1 where a < 3; +a b +0 0 +0 1 +1 0 +1 1 +2 0 +2 1 +select * from t1 where a >= 1 or a is null; +a b +1 0 +1 1 +NULL 0 +2 0 +NULL 1 +2 1 +3 0 +3 1 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null) +partition by list(a) +partitions 2 +(partition x123 values in (1,5,6), +partition x234 values in (4,7,8)); +INSERT into t1 VALUES (1,1,1); +INSERT into t1 VALUES (2,1,1); +ERROR HY000: Table has no partition for value 2 +INSERT into t1 VALUES (3,1,1); +ERROR HY000: Table has no partition for value 3 +INSERT into t1 VALUES (4,1,1); +INSERT into t1 VALUES (5,1,1); +INSERT into t1 VALUES (6,1,1); +INSERT into t1 VALUES (7,1,1); +INSERT into t1 VALUES (8,1,1); +INSERT into t1 VALUES (9,1,1); +ERROR HY000: Table has no partition for value 9 +INSERT into t1 VALUES (1,2,1); +INSERT into t1 VALUES (1,3,1); +INSERT into t1 VALUES (1,4,1); +INSERT into t1 VALUES (7,2,1); +INSERT into t1 VALUES (7,3,1); +INSERT into t1 VALUES (7,4,1); +SELECT * from t1; +a b c +1 1 1 +5 1 1 +6 1 1 +1 2 1 +1 3 1 +1 4 1 +4 1 1 +7 1 1 +8 1 1 +7 2 1 +7 3 1 +7 4 1 +SELECT * from t1 WHERE a=1; +a b c +1 1 1 +1 2 1 +1 3 1 +1 4 1 +SELECT * from t1 WHERE a=7; +a b c +7 1 1 +7 2 1 +7 3 1 +7 4 1 +SELECT * from t1 WHERE b=2; +a b c +1 2 1 +7 2 1 +UPDATE t1 SET a=8 WHERE a=7 AND b=3; +SELECT * from t1; +a b c +1 1 1 +5 1 1 +6 1 1 +1 2 1 +1 3 1 +1 4 1 +4 1 1 +7 1 1 +8 1 1 +7 2 1 +8 3 1 +7 4 1 +UPDATE t1 SET a=8 WHERE a=5 AND b=1; +SELECT * from t1; +a b c +1 1 1 +6 1 1 +1 2 1 +1 3 1 +1 4 1 +4 1 1 +7 1 1 +8 1 1 +7 2 1 +8 3 1 +7 4 1 +8 1 1 +DELETE from t1 WHERE a=8; +SELECT * from t1; +a b c +1 1 1 +6 1 1 +1 2 1 +1 3 1 +1 4 1 +4 1 1 +7 1 1 +7 2 1 +7 4 1 +DELETE from t1 WHERE a=2; +SELECT * from t1; +a b c +1 1 1 +6 1 1 +1 2 1 +1 3 1 +1 4 1 +4 1 1 +7 1 1 +7 2 1 +7 4 1 +DELETE from t1 WHERE a=5 OR a=6; +SELECT * from t1; +a b c +1 1 1 +1 2 1 +1 3 1 +1 4 1 +4 1 1 +7 1 1 +7 2 1 +7 4 1 +ALTER TABLE t1 +partition by list(a) +partitions 2 +(partition x123 values in (1,5,6), +partition x234 values in (4,7,8)); +SELECT * from t1; +a b c +1 1 1 +1 2 1 +1 3 1 +1 4 1 +4 1 1 +7 1 1 +7 2 1 +7 4 1 +INSERT into t1 VALUES (6,2,1); +INSERT into t1 VALUES (2,2,1); +ERROR HY000: Table has no partition for value 2 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key (a,b)) +partition by list (a) +subpartition by hash (a+b) +( partition x1 values in (1,2,3) +( subpartition x11 nodegroup 0, +subpartition x12 nodegroup 1), +partition x2 values in (4,5,6) +( subpartition x21 nodegroup 0, +subpartition x22 nodegroup 1) +); +INSERT into t1 VALUES (1,1,1); +INSERT into t1 VALUES (4,1,1); +INSERT into t1 VALUES (7,1,1); +ERROR HY000: Table has no partition for value 7 +UPDATE t1 SET a=5 WHERE a=1; +SELECT * from t1; +a b c +5 1 1 +4 1 1 +UPDATE t1 SET a=6 WHERE a=4; +SELECT * from t1; +a b c +5 1 1 +6 1 1 +DELETE from t1 WHERE a=6; +SELECT * from t1; +a b c +5 1 1 +drop table t1; +CREATE TABLE t1 ( +a int not null, +b int not null, +c int not null, +primary key(a,b)) +partition by list (a) +(partition x1 values in (1,2,9,4) tablespace ts1); +drop table t1; +CREATE TABLE t1 (s1 int) PARTITION BY LIST (s1) +(PARTITION p1 VALUES IN (1), +PARTITION p2 VALUES IN (2), +PARTITION p3 VALUES IN (3), +PARTITION p4 VALUES IN (4), +PARTITION p5 VALUES IN (5)); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +SELECT COUNT(*) FROM t1 WHERE s1 < 3; +COUNT(*) +2 +DROP TABLE t1; +create table t1 (a int auto_increment primary key) +auto_increment=100 +partition by list (a) +(partition p0 values in (1, 100)); +create index inx on t1 (a); +insert into t1 values (null); +select * from t1; +a +100 +drop table t1; +create table t1 (a char(1)) +partition by list (ascii(ucase(a))) +(partition p1 values in (2)); +ERROR HY000: This partition function is not allowed +# +# MDEV-11681: PARTITION BY LIST COLUMNS with default partition: +# Assertion `part_info->num_list_values' failed in +# get_part_iter_for_interval_cols_via_map +# +CREATE TABLE t1 (f int) PARTITION BY LIST COLUMNS (f) (PARTITION pdef DEFAULT); +insert into t1 values (1),(2); +select * from t1 where f = 1; +f +1 +drop table t1; +CREATE TABLE t1 (f int, d int) PARTITION BY LIST COLUMNS (f,d) (PARTITION pdef DEFAULT); +insert into t1 values (1,1),(2,2); +select * from t1 where f = 1 and d = 1 ; +f d +1 1 +drop table t1; +CREATE TABLE t1 (f int) PARTITION BY LIST (f) (PARTITION pdef DEFAULT); +insert into t1 values (1),(2); +select * from t1 where f = 1; +f +1 +drop table t1; +#end of 10.2 tests |