Debian Codegraph @

mysql-test/suite/engines/funcs/t/tc_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 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
28 --replace_result $ENGINE ENGINE
29 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
30 CREATE TABLE t1 (c1 SMALLINT,name VARCHAR(30), purchased DATE)
31 PARTITION BY RANGE( YEAR(purchased) )
32 SUBPARTITION BY HASH( TO_DAYS(purchased) )
33 SUBPARTITIONS 2 (
34 PARTITION p0 VALUES LESS THAN (1990),
35 PARTITION p1 VALUES LESS THAN (2000),
36 PARTITION p2 VALUES LESS THAN MAXVALUE
37 );
38 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
39 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
40 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
41 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
42 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
43 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
44 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
45 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
46 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
47 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
48 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
49 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
50 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
51 SELECT * FROM t1 ORDER BY c1;
52 SHOW TABLES;
53 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
54 --replace_result $ENGINE ENGINE
55 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
56 CREATE TABLE t1 (c1 MEDIUMINT,name VARCHAR(30), purchased DATE)
57 PARTITION BY RANGE( YEAR(purchased) )
58 SUBPARTITION BY HASH( TO_DAYS(purchased) )
59 SUBPARTITIONS 2 (
60 PARTITION p0 VALUES LESS THAN (1990),
61 PARTITION p1 VALUES LESS THAN (2000),
62 PARTITION p2 VALUES LESS THAN MAXVALUE
63 );
64 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
65 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
66 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
67 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
68 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
69 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
70 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
71 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
72 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
73 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
74 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
75 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
76 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
77 SELECT * FROM t1 ORDER BY c1;
78 SHOW TABLES;
79 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
80 --replace_result $ENGINE ENGINE
81 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
82 CREATE TABLE t1 (c1 INT,name VARCHAR(30), purchased DATE)
83 PARTITION BY RANGE( YEAR(purchased) )
84 SUBPARTITION BY HASH( TO_DAYS(purchased) )
85 SUBPARTITIONS 2 (
86 PARTITION p0 VALUES LESS THAN (1990),
87 PARTITION p1 VALUES LESS THAN (2000),
88 PARTITION p2 VALUES LESS THAN MAXVALUE
89 );
90 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
91 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
92 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
93 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
94 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
95 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
96 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
97 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
98 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
99 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
100 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
101 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
102 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
103 SELECT * FROM t1 ORDER BY c1;
104 SHOW TABLES;
105 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
106 --replace_result $ENGINE ENGINE
107 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
108 CREATE TABLE t1 (c1 INTEGER,name VARCHAR(30), purchased DATE)
109 PARTITION BY RANGE( YEAR(purchased) )
110 SUBPARTITION BY HASH( TO_DAYS(purchased) )
111 SUBPARTITIONS 2 (
112 PARTITION p0 VALUES LESS THAN (1990),
113 PARTITION p1 VALUES LESS THAN (2000),
114 PARTITION p2 VALUES LESS THAN MAXVALUE
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 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
132 --replace_result $ENGINE ENGINE
133 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
134 CREATE TABLE t1 (c1 BIGINT,name VARCHAR(30), purchased DATE)
135 PARTITION BY RANGE( YEAR(purchased) )
136 SUBPARTITION BY HASH( TO_DAYS(purchased) )
137 SUBPARTITIONS 2 (
138 PARTITION p0 VALUES LESS THAN (1990),
139 PARTITION p1 VALUES LESS THAN (2000),
140 PARTITION p2 VALUES LESS THAN MAXVALUE
141 );
142 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
143 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
144 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
145 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
146 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
147 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
148 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
149 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
150 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
151 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
152 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
153 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
154 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
155 SELECT * FROM t1 ORDER BY c1;
156 SHOW TABLES;
157 let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
158 --replace_result $ENGINE ENGINE
159 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
160