1
2-- source include/have_partition.inc
3
4--disable_warnings
5drop table if exists t1,t2,t3,t4;
6--enable_warnings
7
8create table t1 (a int not null,b int not null,c int not null, primary key(a,b))
9partition 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);
13
14--replace_column 16 # 19 # 20 #
15select * from information_schema.partitions where table_schema="test"
16and table_name="t1";
17
18create table t2 (a int not null,b int not null,c int not null, primary key(a,b))
19partition by range (a)
20partitions 3
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 #
25select * from information_schema.partitions where table_schema="test"
26and table_name="t2";
27
28create table t3 (f1 date)
29partition by hash(month(f1))
30partitions 3;
31--replace_column 16 # 19 # 20 #
32select * from information_schema.partitions where table_schema="test"
33and table_name="t3";
34
35create table t4 (f1 date, f2 int)
36partition by key(f1,f2)
37partitions 3;
38--replace_column 16 # 19 # 20 #
39select * from information_schema.partitions where table_schema="test"
40and table_name="t4";
41
42drop table t1,t2,t3,t4;
43
44create table t1 (a int not null,b int not null,c int not null,primary key (a,b))
45partition by range (a)
46subpartition 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)
53);
54
55create table t2 (a int not null,b int not null,c int not null,primary key (a,b))
56partition by range (a)
57subpartition 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)
64);
65--replace_column 16 # 19 # 20 #
66select * from information_schema.partitions where table_schema="test" order by table_name, partition_name;
67drop table t1,t2;
68
69create table t1 (
70a int not null,
71b int not null,
72c int not null,
73primary key (a,b))
74partition by range (a)
75subpartition 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)
82);
83
84--replace_column 16 # 19 # 20 #
85select * from information_schema.partitions where table_schema="test";
86show tables;
87drop table t1;
88
89create table t1(f1 int, f2 int);
90--replace_column 16 # 19 # 20 #
91select * from information_schema.partitions where table_schema="test";
92drop table t1;
93
94create table t1 (f1 date)
95partition by linear hash(month(f1))
96partitions 3;
97--replace_column 16 # 19 # 20 #
98select * from information_schema.partitions where table_schema="test"
99and table_name="t1";
100drop table t1;
101
102#
103# Bug 20161 Partitions: SUBPARTITION METHOD doesn't show LINEAR keyword
104#
105create table t1 (a int)
106PARTITION BY RANGE (a)
107SUBPARTITION BY LINEAR HASH (a)
108(PARTITION p0 VALUES LESS THAN (10));
109
110SHOW CREATE TABLE t1;
111select SUBPARTITION_METHOD FROM information_schema.partitions WHERE
112table_schema="test" AND table_name="t1";
113drop table t1;
114
115create table t1 (a int)
116PARTITION 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));
120SHOW CREATE TABLE t1;
121SELECT PARTITION_DESCRIPTION FROM information_schema.partitions WHERE
122table_schema = "test" AND table_name = "t1";
123drop table t1;
124
125#
126# Bug#38909 CREATE_OPTIONS in information_schema produces wrong results
127#
128--disable_warnings
129drop table if exists t1;
130--enable_warnings
131create table t1 (f1 int key) partition by key(f1) partitions 2;
132select create_options from information_schema.tables where table_schema="test";
133drop table t1;
134
135--echo #
136--echo # MDEV-11353 - Identical logical conditions
137--echo #
138CREATE TABLE t1(a INT) CHECKSUM=1 SELECT 1;
139SELECT CHECKSUM FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
140DROP TABLE t1;
141