1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
-- source include/have_partition.inc
--disable_warnings
drop table if exists t1,t2,t3,t4;
--enable_warnings
create table t1 (a int not null,b int not null,c int not null, primary key(a,b))
partition by list (b*a)
(partition x1 values in (1) tablespace ts1,
partition x2 values in (3, 11, 5, 7) tablespace ts2,
partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
--replace_column 16 # 19 # 20 #
select * from information_schema.partitions where table_schema="test"
and table_name="t1";
create table t2 (a int not null,b int not null,c int not null, primary key(a,b))
partition by range (a)
partitions 3
(partition x1 values less than (5) tablespace ts1,
partition x2 values less than (10) tablespace ts2,
partition x3 values less than maxvalue tablespace ts3);
--replace_column 16 # 19 # 20 #
select * from information_schema.partitions where table_schema="test"
and table_name="t2";
create table t3 (f1 date)
partition by hash(month(f1))
partitions 3;
--replace_column 16 # 19 # 20 #
select * from information_schema.partitions where table_schema="test"
and table_name="t3";
create table t4 (f1 date, f2 int)
partition by key(f1,f2)
partitions 3;
--replace_column 16 # 19 # 20 #
select * from information_schema.partitions where table_schema="test"
and table_name="t4";
drop table t1,t2,t3,t4;
create table t1 (a int not null,b int not null,c int not null,primary key (a,b))
partition by range (a)
subpartition by hash (a+b)
( partition x1 values less than (1)
( subpartition x11 tablespace t1,
subpartition x12 tablespace t2),
partition x2 values less than (5)
( subpartition x21 tablespace t1,
subpartition x22 tablespace t2)
);
create table t2 (a int not null,b int not null,c int not null,primary key (a,b))
partition by range (a)
subpartition by key (a)
( partition x1 values less than (1)
( subpartition x11 tablespace t1,
subpartition x12 tablespace t2),
partition x2 values less than (5)
( subpartition x21 tablespace t1,
subpartition x22 tablespace t2)
);
--replace_column 16 # 19 # 20 #
select * from information_schema.partitions where table_schema="test" order by table_name, partition_name;
drop table t1,t2;
create table t1 (
a int not null,
b int not null,
c int not null,
primary key (a,b))
partition by range (a)
subpartition by hash (a+b)
( partition x1 values less than (1)
( subpartition x11 tablespace t1 nodegroup 0,
subpartition x12 tablespace t2 nodegroup 1),
partition x2 values less than (5)
( subpartition x21 tablespace t1 nodegroup 0,
subpartition x22 tablespace t2 nodegroup 1)
);
--replace_column 16 # 19 # 20 #
select * from information_schema.partitions where table_schema="test";
show tables;
drop table t1;
create table t1(f1 int, f2 int);
--replace_column 16 # 19 # 20 #
select * from information_schema.partitions where table_schema="test";
drop table t1;
create table t1 (f1 date)
partition by linear hash(month(f1))
partitions 3;
--replace_column 16 # 19 # 20 #
select * from information_schema.partitions where table_schema="test"
and table_name="t1";
drop table t1;
#
# Bug 20161 Partitions: SUBPARTITION METHOD doesn't show LINEAR keyword
#
create table t1 (a int)
PARTITION BY RANGE (a)
SUBPARTITION BY LINEAR HASH (a)
(PARTITION p0 VALUES LESS THAN (10));
SHOW CREATE TABLE t1;
select SUBPARTITION_METHOD FROM information_schema.partitions WHERE
table_schema="test" AND table_name="t1";
drop table t1;
create table t1 (a int)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN
(10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,
32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53));
SHOW CREATE TABLE t1;
SELECT PARTITION_DESCRIPTION FROM information_schema.partitions WHERE
table_schema = "test" AND table_name = "t1";
drop table t1;
#
# Bug#38909 CREATE_OPTIONS in information_schema produces wrong results
#
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (f1 int key) partition by key(f1) partitions 2;
--disable_view_protocol
select create_options from information_schema.tables where table_schema="test";
--enable_view_protocol
drop table t1;
--echo #
--echo # MDEV-11353 - Identical logical conditions
--echo #
CREATE TABLE t1(a INT) CHECKSUM=1 SELECT 1;
SELECT CHECKSUM FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
DROP TABLE t1;
|
