2
-- source include/have_partition.inc
5
drop table if exists t1,t2,t3,t4;
8
create table t1 (a int not null,b int not null,c int not null, primary key(a,b))
9
partition by list (b*a)
10
(partition x1 values in (1) tablespace ts1,
11
partition x2 values in (3, 11, 5, 7) tablespace ts2,
12
partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
14
--replace_column 16 # 19 # 20 #
15
select * from information_schema.partitions where table_schema="test"
18
create table t2 (a int not null,b int not null,c int not null, primary key(a,b))
19
partition by range (a)
21
(partition x1 values less than (5) tablespace ts1,
22
partition x2 values less than (10) tablespace ts2,
23
partition x3 values less than maxvalue tablespace ts3);
24
--replace_column 16 # 19 # 20 #
25
select * from information_schema.partitions where table_schema="test"
28
create table t3 (f1 date)
29
partition by hash(month(f1))
31
--replace_column 16 # 19 # 20 #
32
select * from information_schema.partitions where table_schema="test"
35
create table t4 (f1 date, f2 int)
36
partition by key(f1,f2)
38
--replace_column 16 # 19 # 20 #
39
select * from information_schema.partitions where table_schema="test"
42
drop table t1,t2,t3,t4;
44
create table t1 (a int not null,b int not null,c int not null,primary key (a,b))
45
partition by range (a)
46
subpartition by hash (a+b)
47
( partition x1 values less than (1)
48
( subpartition x11 tablespace t1,
49
subpartition x12 tablespace t2),
50
partition x2 values less than (5)
51
( subpartition x21 tablespace t1,
52
subpartition x22 tablespace t2)
55
create table t2 (a int not null,b int not null,c int not null,primary key (a,b))
56
partition by range (a)
57
subpartition by key (a)
58
( partition x1 values less than (1)
59
( subpartition x11 tablespace t1,
60
subpartition x12 tablespace t2),
61
partition x2 values less than (5)
62
( subpartition x21 tablespace t1,
63
subpartition x22 tablespace t2)
65
--replace_column 16 # 19 # 20 #
66
select * from information_schema.partitions where table_schema="test";
74
partition by range (a)
75
subpartition by hash (a+b)
76
( partition x1 values less than (1)
77
( subpartition x11 tablespace t1 nodegroup 0,
78
subpartition x12 tablespace t2 nodegroup 1),
79
partition x2 values less than (5)
80
( subpartition x21 tablespace t1 nodegroup 0,
81
subpartition x22 tablespace t2 nodegroup 1)
84
--replace_column 16 # 19 # 20 #
85
select * from information_schema.partitions where table_schema="test";
89
create table t1(f1 int, f2 int);
90
--replace_column 16 # 19 # 20 #
91
select * from information_schema.partitions where table_schema="test";
94
create table t1 (f1 date)
95
partition by linear hash(month(f1))
97
--replace_column 16 # 19 # 20 #
98
select * from information_schema.partitions where table_schema="test"
103
# Bug 20161 Partitions: SUBPARTITION METHOD doesn't show LINEAR keyword
105
create table t1 (a int)
106
PARTITION BY RANGE (a)
107
SUBPARTITION BY LINEAR HASH (a)
108
(PARTITION p0 VALUES LESS THAN (10));
110
SHOW CREATE TABLE t1;
111
select SUBPARTITION_METHOD FROM information_schema.partitions WHERE
112
table_schema="test" AND table_name="t1";
115
create table t1 (a int)
116
PARTITION BY LIST (a)
117
(PARTITION p0 VALUES IN
118
(10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,
119
32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53));
120
SHOW CREATE TABLE t1;
121
SELECT PARTITION_DESCRIPTION FROM information_schema.partitions WHERE
122
table_schema = "test" AND table_name = "t1";
126
# Bug#38909 CREATE_OPTIONS in information_schema produces wrong results
129
drop table if exists t1;
131
create table t1 (f1 int key) partition by key(f1) partitions 2;
132
select create_options from information_schema.tables where table_schema="test";