1# We use a separate test wrapper for engines not supporting native partitioning
2# since the test results will be different due to deprecation warnings. This test
3# expects to either be executed directly with InnoDB as default engine, or to be
4# executed indirectly by being included in a test wrapper  setting the ENGINE
5# variable.
6--disable_warnings
7if (!$ENGINE)
8{
9  let $ENGINE=`SELECT variable_value FROM performance_schema.global_variables
10               WHERE variable_name = 'DEFAULT_STORAGE_ENGINE'`;
11  if ($ENGINE != "InnoDB")
12  {
13    skip Engines not supporting native partitioning are tested using a test wrapper;
14  }
15}
16--enable_warnings
17
18--disable_warnings
19DROP TABLE IF EXISTS t1;
20--enable_warnings
21CREATE TABLE t1 (c1 TINYINT,name VARCHAR(30), purchased DATE)
22    PARTITION BY RANGE( YEAR(purchased) )
23    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
24        PARTITION p0 VALUES LESS THAN (1990) (
25            SUBPARTITION s0,
26            SUBPARTITION s1
27        ),
28        PARTITION p1 VALUES LESS THAN (2000) (
29            SUBPARTITION s2,
30            SUBPARTITION s3
31        ),
32        PARTITION p2 VALUES LESS THAN MAXVALUE (
33            SUBPARTITION s4,
34            SUBPARTITION s5
35        )
36    );
37INSERT INTO t1 VALUES(1,'abc','1994-01-01');
38INSERT INTO t1 VALUES(2,'abc','1995-01-01');
39INSERT INTO t1 VALUES(3,'abc','1996-01-01');
40INSERT INTO t1 VALUES(4,'abc','1997-01-01');
41INSERT INTO t1 VALUES(5,'abc','1998-01-01');
42INSERT INTO t1 VALUES(6,'abc','1999-01-01');
43INSERT INTO t1 VALUES(7,'abc','2000-01-01');
44INSERT INTO t1 VALUES(8,'abc','2001-01-01');
45INSERT INTO t1 VALUES(9,'abc','2002-01-01');
46INSERT INTO t1 VALUES(10,'abc','2003-01-01');
47INSERT INTO t1 VALUES(11,'abc','2004-01-01');
48INSERT INTO t1 VALUES(12,'abc','2005-01-01');
49INSERT INTO t1 VALUES(13,'abc','2006-01-01');
50SELECT * FROM t1 ORDER BY c1;
51SHOW TABLES;
52--replace_result $ENGINE ENGINE
53 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
54CREATE TABLE t1 (c1 SMALLINT,name VARCHAR(30), purchased DATE)
55    PARTITION BY RANGE( YEAR(purchased) )
56    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
57        PARTITION p0 VALUES LESS THAN (1990) (
58            SUBPARTITION s0,
59            SUBPARTITION s1
60        ),
61        PARTITION p1 VALUES LESS THAN (2000) (
62            SUBPARTITION s2,
63            SUBPARTITION s3
64        ),
65        PARTITION p2 VALUES LESS THAN MAXVALUE (
66            SUBPARTITION s4,
67            SUBPARTITION s5
68        )
69    );
70INSERT INTO t1 VALUES(1,'abc','1994-01-01');
71INSERT INTO t1 VALUES(2,'abc','1995-01-01');
72INSERT INTO t1 VALUES(3,'abc','1996-01-01');
73INSERT INTO t1 VALUES(4,'abc','1997-01-01');
74INSERT INTO t1 VALUES(5,'abc','1998-01-01');
75INSERT INTO t1 VALUES(6,'abc','1999-01-01');
76INSERT INTO t1 VALUES(7,'abc','2000-01-01');
77INSERT INTO t1 VALUES(8,'abc','2001-01-01');
78INSERT INTO t1 VALUES(9,'abc','2002-01-01');
79INSERT INTO t1 VALUES(10,'abc','2003-01-01');
80INSERT INTO t1 VALUES(11,'abc','2004-01-01');
81INSERT INTO t1 VALUES(12,'abc','2005-01-01');
82INSERT INTO t1 VALUES(13,'abc','2006-01-01');
83SELECT * FROM t1 ORDER BY c1;
84SHOW TABLES;
85--replace_result $ENGINE ENGINE
86 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
87CREATE TABLE t1 (c1 MEDIUMINT,name VARCHAR(30), purchased DATE)
88    PARTITION BY RANGE( YEAR(purchased) )
89    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
90        PARTITION p0 VALUES LESS THAN (1990) (
91            SUBPARTITION s0,
92            SUBPARTITION s1
93        ),
94        PARTITION p1 VALUES LESS THAN (2000) (
95            SUBPARTITION s2,
96            SUBPARTITION s3
97        ),
98        PARTITION p2 VALUES LESS THAN MAXVALUE (
99            SUBPARTITION s4,
100            SUBPARTITION s5
101        )
102    );
103INSERT INTO t1 VALUES(1,'abc','1994-01-01');
104INSERT INTO t1 VALUES(2,'abc','1995-01-01');
105INSERT INTO t1 VALUES(3,'abc','1996-01-01');
106INSERT INTO t1 VALUES(4,'abc','1997-01-01');
107INSERT INTO t1 VALUES(5,'abc','1998-01-01');
108INSERT INTO t1 VALUES(6,'abc','1999-01-01');
109INSERT INTO t1 VALUES(7,'abc','2000-01-01');
110INSERT INTO t1 VALUES(8,'abc','2001-01-01');
111INSERT INTO t1 VALUES(9,'abc','2002-01-01');
112INSERT INTO t1 VALUES(10,'abc','2003-01-01');
113INSERT INTO t1 VALUES(11,'abc','2004-01-01');
114INSERT INTO t1 VALUES(12,'abc','2005-01-01');
115INSERT INTO t1 VALUES(13,'abc','2006-01-01');
116SELECT * FROM t1 ORDER BY c1;
117SHOW TABLES;
118--replace_result $ENGINE ENGINE
119 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
120CREATE TABLE t1 (c1 INT,name VARCHAR(30), purchased DATE)
121    PARTITION BY RANGE( YEAR(purchased) )
122    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
123        PARTITION p0 VALUES LESS THAN (1990) (
124            SUBPARTITION s0,
125            SUBPARTITION s1
126        ),
127        PARTITION p1 VALUES LESS THAN (2000) (
128            SUBPARTITION s2,
129            SUBPARTITION s3
130        ),
131        PARTITION p2 VALUES LESS THAN MAXVALUE (
132            SUBPARTITION s4,
133            SUBPARTITION s5
134        )
135    );
136INSERT INTO t1 VALUES(1,'abc','1994-01-01');
137INSERT INTO t1 VALUES(2,'abc','1995-01-01');
138INSERT INTO t1 VALUES(3,'abc','1996-01-01');
139INSERT INTO t1 VALUES(4,'abc','1997-01-01');
140INSERT INTO t1 VALUES(5,'abc','1998-01-01');
141INSERT INTO t1 VALUES(6,'abc','1999-01-01');
142INSERT INTO t1 VALUES(7,'abc','2000-01-01');
143INSERT INTO t1 VALUES(8,'abc','2001-01-01');
144INSERT INTO t1 VALUES(9,'abc','2002-01-01');
145INSERT INTO t1 VALUES(10,'abc','2003-01-01');
146INSERT INTO t1 VALUES(11,'abc','2004-01-01');
147INSERT INTO t1 VALUES(12,'abc','2005-01-01');
148INSERT INTO t1 VALUES(13,'abc','2006-01-01');
149SELECT * FROM t1 ORDER BY c1;
150SHOW TABLES;
151--replace_result $ENGINE ENGINE
152 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
153CREATE TABLE t1 (c1 INTEGER,name VARCHAR(30), purchased DATE)
154    PARTITION BY RANGE( YEAR(purchased) )
155    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
156        PARTITION p0 VALUES LESS THAN (1990) (
157            SUBPARTITION s0,
158            SUBPARTITION s1
159        ),
160        PARTITION p1 VALUES LESS THAN (2000) (
161            SUBPARTITION s2,
162            SUBPARTITION s3
163        ),
164        PARTITION p2 VALUES LESS THAN MAXVALUE (
165            SUBPARTITION s4,
166            SUBPARTITION s5
167        )
168    );
169INSERT INTO t1 VALUES(1,'abc','1994-01-01');
170INSERT INTO t1 VALUES(2,'abc','1995-01-01');
171INSERT INTO t1 VALUES(3,'abc','1996-01-01');
172INSERT INTO t1 VALUES(4,'abc','1997-01-01');
173INSERT INTO t1 VALUES(5,'abc','1998-01-01');
174INSERT INTO t1 VALUES(6,'abc','1999-01-01');
175INSERT INTO t1 VALUES(7,'abc','2000-01-01');
176INSERT INTO t1 VALUES(8,'abc','2001-01-01');
177INSERT INTO t1 VALUES(9,'abc','2002-01-01');
178INSERT INTO t1 VALUES(10,'abc','2003-01-01');
179INSERT INTO t1 VALUES(11,'abc','2004-01-01');
180INSERT INTO t1 VALUES(12,'abc','2005-01-01');
181INSERT INTO t1 VALUES(13,'abc','2006-01-01');
182SELECT * FROM t1 ORDER BY c1;
183SHOW TABLES;
184--replace_result $ENGINE ENGINE
185 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
186CREATE TABLE t1 (c1 BIGINT,name VARCHAR(30), purchased DATE)
187    PARTITION BY RANGE( YEAR(purchased) )
188    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
189        PARTITION p0 VALUES LESS THAN (1990) (
190            SUBPARTITION s0,
191            SUBPARTITION s1
192        ),
193        PARTITION p1 VALUES LESS THAN (2000) (
194            SUBPARTITION s2,
195            SUBPARTITION s3
196        ),
197        PARTITION p2 VALUES LESS THAN MAXVALUE (
198            SUBPARTITION s4,
199            SUBPARTITION s5
200        )
201    );
202INSERT INTO t1 VALUES(1,'abc','1994-01-01');
203INSERT INTO t1 VALUES(2,'abc','1995-01-01');
204INSERT INTO t1 VALUES(3,'abc','1996-01-01');
205INSERT INTO t1 VALUES(4,'abc','1997-01-01');
206INSERT INTO t1 VALUES(5,'abc','1998-01-01');
207INSERT INTO t1 VALUES(6,'abc','1999-01-01');
208INSERT INTO t1 VALUES(7,'abc','2000-01-01');
209INSERT INTO t1 VALUES(8,'abc','2001-01-01');
210INSERT INTO t1 VALUES(9,'abc','2002-01-01');
211INSERT INTO t1 VALUES(10,'abc','2003-01-01');
212INSERT INTO t1 VALUES(11,'abc','2004-01-01');
213INSERT INTO t1 VALUES(12,'abc','2005-01-01');
214INSERT INTO t1 VALUES(13,'abc','2006-01-01');
215SELECT * FROM t1 ORDER BY c1;
216SHOW TABLES;
217--replace_result $ENGINE ENGINE
218 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
219
220