Debian Codegraph @

mysql-test/suite/parts/t/partition_sub1.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 SUBPARTITIONS 2 (
8 PARTITION p0 VALUES LESS THAN (1990),
9 PARTITION p1 VALUES LESS THAN (2000),
10 PARTITION p2 VALUES LESS THAN MAXVALUE
11 );
12 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
13 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
14 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
15 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
16 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
17 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
18 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
19 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
20 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
21 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
22 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
23 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
24 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
25 SELECT * FROM t1 ORDER BY c1;
26 SHOW TABLES;
27 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
28 CREATE TABLE t1 (c1 SMALLINT,name VARCHAR(30), purchased DATE)
29 PARTITION BY RANGE( YEAR(purchased) )
30 SUBPARTITION BY HASH( TO_DAYS(purchased) )
31 SUBPARTITIONS 2 (
32 PARTITION p0 VALUES LESS THAN (1990),
33 PARTITION p1 VALUES LESS THAN (2000),
34 PARTITION p2 VALUES LESS THAN MAXVALUE
35 );
36 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
37 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
38 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
39 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
40 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
41 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
42 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
43 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
44 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
45 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
46 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
47 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
48 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
49 SELECT * FROM t1 ORDER BY c1;
50 SHOW TABLES;
51 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
52 CREATE TABLE t1 (c1 MEDIUMINT,name VARCHAR(30), purchased DATE)
53 PARTITION BY RANGE( YEAR(purchased) )
54 SUBPARTITION BY HASH( TO_DAYS(purchased) )
55 SUBPARTITIONS 2 (
56 PARTITION p0 VALUES LESS THAN (1990),
57 PARTITION p1 VALUES LESS THAN (2000),
58 PARTITION p2 VALUES LESS THAN MAXVALUE
59 );
60 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
61 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
62 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
63 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
64 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
65 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
66 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
67 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
68 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
69 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
70 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
71 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
72 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
73 SELECT * FROM t1 ORDER BY c1;
74 SHOW TABLES;
75 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
76 CREATE TABLE t1 (c1 INT,name VARCHAR(30), purchased DATE)
77 PARTITION BY RANGE( YEAR(purchased) )
78 SUBPARTITION BY HASH( TO_DAYS(purchased) )
79 SUBPARTITIONS 2 (
80 PARTITION p0 VALUES LESS THAN (1990),
81 PARTITION p1 VALUES LESS THAN (2000),
82 PARTITION p2 VALUES LESS THAN MAXVALUE
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 INTEGER,name VARCHAR(30), purchased DATE)
101 PARTITION BY RANGE( YEAR(purchased) )
102 SUBPARTITION BY HASH( TO_DAYS(purchased) )
103 SUBPARTITIONS 2 (
104 PARTITION p0 VALUES LESS THAN (1990),
105 PARTITION p1 VALUES LESS THAN (2000),
106 PARTITION p2 VALUES LESS THAN MAXVALUE
107 );
108 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
109 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
110 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
111 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
112 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
113 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
114 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
115 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
116 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
117 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
118 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
119 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
120 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
121 SELECT * FROM t1 ORDER BY c1;
122 SHOW TABLES;
123 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
124 CREATE TABLE t1 (c1 BIGINT,name VARCHAR(30), purchased DATE)
125 PARTITION BY RANGE( YEAR(purchased) )
126 SUBPARTITION BY HASH( TO_DAYS(purchased) )
127 SUBPARTITIONS 2 (
128 PARTITION p0 VALUES LESS THAN (1990),
129 PARTITION p1 VALUES LESS THAN (2000),
130 PARTITION p2 VALUES LESS THAN MAXVALUE
131 );
132 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
133 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
134 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
135 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
136 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
137 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
138 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
139 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
140 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
141 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
142 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
143 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
144 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
145 SELECT * FROM t1 ORDER BY c1;
146 SHOW TABLES;
147 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;