Debian Codegraph @

mysql-test/suite/engines/funcs/t/tc_partition_sub2.test

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1;
3 --enable_warnings
4 CREATE TABLE t1 (c1 TINYINT,name VARCHAR(30), purchased DATE)
5 PARTITION BY RANGE( YEAR(purchased) )
6 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
7 PARTITION p0 VALUES LESS THAN (1990) (
8 SUBPARTITION s0,
9 SUBPARTITION s1
10 ),
11 PARTITION p1 VALUES LESS THAN (2000) (
12 SUBPARTITION s2,
13 SUBPARTITION s3
14 ),
15 PARTITION p2 VALUES LESS THAN MAXVALUE (
16 SUBPARTITION s4,
17 SUBPARTITION s5
18 )
19 );
20 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
21 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
22 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
23 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
24 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
25 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
26 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
27 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
28 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
29 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
30 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
31 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
32 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
33 SELECT * FROM t1 ORDER BY c1;
34 SHOW TABLES;
35 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
36 --replace_result $ENGINE ENGINE
37 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
38 CREATE TABLE t1 (c1 SMALLINT,name VARCHAR(30), purchased DATE)
39 PARTITION BY RANGE( YEAR(purchased) )
40 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
41 PARTITION p0 VALUES LESS THAN (1990) (
42 SUBPARTITION s0,
43 SUBPARTITION s1
44 ),
45 PARTITION p1 VALUES LESS THAN (2000) (
46 SUBPARTITION s2,
47 SUBPARTITION s3
48 ),
49 PARTITION p2 VALUES LESS THAN MAXVALUE (
50 SUBPARTITION s4,
51 SUBPARTITION s5
52 )
53 );
54 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
55 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
56 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
57 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
58 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
59 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
60 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
61 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
62 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
63 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
64 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
65 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
66 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
67 SELECT * FROM t1 ORDER BY c1;
68 SHOW TABLES;
69 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
70 --replace_result $ENGINE ENGINE
71 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
72 CREATE TABLE t1 (c1 MEDIUMINT,name VARCHAR(30), purchased DATE)
73 PARTITION BY RANGE( YEAR(purchased) )
74 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
75 PARTITION p0 VALUES LESS THAN (1990) (
76 SUBPARTITION s0,
77 SUBPARTITION s1
78 ),
79 PARTITION p1 VALUES LESS THAN (2000) (
80 SUBPARTITION s2,
81 SUBPARTITION s3
82 ),
83 PARTITION p2 VALUES LESS THAN MAXVALUE (
84 SUBPARTITION s4,
85 SUBPARTITION s5
86 )
87 );
88 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
89 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
90 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
91 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
92 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
93 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
94 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
95 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
96 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
97 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
98 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
99 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
100 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
101 SELECT * FROM t1 ORDER BY c1;
102 SHOW TABLES;
103 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
104 --replace_result $ENGINE ENGINE
105 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
106 CREATE TABLE t1 (c1 INT,name VARCHAR(30), purchased DATE)
107 PARTITION BY RANGE( YEAR(purchased) )
108 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
109 PARTITION p0 VALUES LESS THAN (1990) (
110 SUBPARTITION s0,
111 SUBPARTITION s1
112 ),
113 PARTITION p1 VALUES LESS THAN (2000) (
114 SUBPARTITION s2,
115 SUBPARTITION s3
116 ),
117 PARTITION p2 VALUES LESS THAN MAXVALUE (
118 SUBPARTITION s4,
119 SUBPARTITION s5
120 )
121 );
122 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
123 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
124 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
125 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
126 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
127 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
128 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
129 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
130 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
131 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
132 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
133 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
134 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
135 SELECT * FROM t1 ORDER BY c1;
136 SHOW TABLES;
137 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
138 --replace_result $ENGINE ENGINE
139 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
140 CREATE TABLE t1 (c1 INTEGER,name VARCHAR(30), purchased DATE)
141 PARTITION BY RANGE( YEAR(purchased) )
142 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
143 PARTITION p0 VALUES LESS THAN (1990) (
144 SUBPARTITION s0,
145 SUBPARTITION s1
146 ),
147 PARTITION p1 VALUES LESS THAN (2000) (
148 SUBPARTITION s2,
149 SUBPARTITION s3
150 ),
151 PARTITION p2 VALUES LESS THAN MAXVALUE (
152 SUBPARTITION s4,
153 SUBPARTITION s5
154 )
155 );
156 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
157 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
158 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
159 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
160 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
161 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
162 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
163 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
164 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
165 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
166 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
167 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
168 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
169 SELECT * FROM t1 ORDER BY c1;
170 SHOW TABLES;
171 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
172 --replace_result $ENGINE ENGINE
173 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
174 CREATE TABLE t1 (c1 BIGINT,name VARCHAR(30), purchased DATE)
175 PARTITION BY RANGE( YEAR(purchased) )
176 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
177 PARTITION p0 VALUES LESS THAN (1990) (
178 SUBPARTITION s0,
179 SUBPARTITION s1
180 ),
181 PARTITION p1 VALUES LESS THAN (2000) (
182 SUBPARTITION s2,
183 SUBPARTITION s3
184 ),
185 PARTITION p2 VALUES LESS THAN MAXVALUE (
186 SUBPARTITION s4,
187 SUBPARTITION s5
188 )
189 );
190 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
191 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
192 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
193 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
194 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
195 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
196 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
197 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
198 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
199 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
200 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
201 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
202 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
203 SELECT * FROM t1 ORDER BY c1;
204 SHOW TABLES;
205 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
206 --replace_result $ENGINE ENGINE
207 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
208