Debian Codegraph @

mysql-test/suite/parts/t/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 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
36 CREATE TABLE t1 (c1 SMALLINT,name VARCHAR(30), purchased DATE)
37 PARTITION BY RANGE( YEAR(purchased) )
38 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
39 PARTITION p0 VALUES LESS THAN (1990) (
40 SUBPARTITION s0,
41 SUBPARTITION s1
42 ),
43 PARTITION p1 VALUES LESS THAN (2000) (
44 SUBPARTITION s2,
45 SUBPARTITION s3
46 ),
47 PARTITION p2 VALUES LESS THAN MAXVALUE (
48 SUBPARTITION s4,
49 SUBPARTITION s5
50 )
51 );
52 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
53 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
54 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
55 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
56 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
57 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
58 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
59 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
60 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
61 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
62 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
63 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
64 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
65 SELECT * FROM t1 ORDER BY c1;
66 SHOW TABLES;
67 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
68 CREATE TABLE t1 (c1 MEDIUMINT,name VARCHAR(30), purchased DATE)
69 PARTITION BY RANGE( YEAR(purchased) )
70 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
71 PARTITION p0 VALUES LESS THAN (1990) (
72 SUBPARTITION s0,
73 SUBPARTITION s1
74 ),
75 PARTITION p1 VALUES LESS THAN (2000) (
76 SUBPARTITION s2,
77 SUBPARTITION s3
78 ),
79 PARTITION p2 VALUES LESS THAN MAXVALUE (
80 SUBPARTITION s4,
81 SUBPARTITION s5
82 )
83 );
84 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
85 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
86 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
87 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
88 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
89 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
90 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
91 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
92 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
93 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
94 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
95 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
96 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
97 SELECT * FROM t1 ORDER BY c1;
98 SHOW TABLES;
99 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
100 CREATE TABLE t1 (c1 INT,name VARCHAR(30), purchased DATE)
101 PARTITION BY RANGE( YEAR(purchased) )
102 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
103 PARTITION p0 VALUES LESS THAN (1990) (
104 SUBPARTITION s0,
105 SUBPARTITION s1
106 ),
107 PARTITION p1 VALUES LESS THAN (2000) (
108 SUBPARTITION s2,
109 SUBPARTITION s3
110 ),
111 PARTITION p2 VALUES LESS THAN MAXVALUE (
112 SUBPARTITION s4,
113 SUBPARTITION s5
114 )
115 );
116 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
117 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
118 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
119 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
120 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
121 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
122 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
123 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
124 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
125 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
126 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
127 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
128 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
129 SELECT * FROM t1 ORDER BY c1;
130 SHOW TABLES;
131 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
132 CREATE TABLE t1 (c1 INTEGER,name VARCHAR(30), purchased DATE)
133 PARTITION BY RANGE( YEAR(purchased) )
134 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
135 PARTITION p0 VALUES LESS THAN (1990) (
136 SUBPARTITION s0,
137 SUBPARTITION s1
138 ),
139 PARTITION p1 VALUES LESS THAN (2000) (
140 SUBPARTITION s2,
141 SUBPARTITION s3
142 ),
143 PARTITION p2 VALUES LESS THAN MAXVALUE (
144 SUBPARTITION s4,
145 SUBPARTITION s5
146 )
147 );
148 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
149 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
150 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
151 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
152 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
153 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
154 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
155 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
156 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
157 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
158 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
159 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
160 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
161 SELECT * FROM t1 ORDER BY c1;
162 SHOW TABLES;
163 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
164 CREATE TABLE t1 (c1 BIGINT,name VARCHAR(30), purchased DATE)
165 PARTITION BY RANGE( YEAR(purchased) )
166 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
167 PARTITION p0 VALUES LESS THAN (1990) (
168 SUBPARTITION s0,
169 SUBPARTITION s1
170 ),
171 PARTITION p1 VALUES LESS THAN (2000) (
172 SUBPARTITION s2,
173 SUBPARTITION s3
174 ),
175 PARTITION p2 VALUES LESS THAN MAXVALUE (
176 SUBPARTITION s4,
177 SUBPARTITION s5
178 )
179 );
180 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
181 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
182 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
183 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
184 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
185 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
186 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
187 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
188 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
189 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
190 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
191 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
192 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
193 SELECT * FROM t1 ORDER BY c1;
194 SHOW TABLES;
195 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;