| blob | 5fcb0e796b19079a1178b1e095b9c44435aeee95 |
1 drop table if exists t1, t2;
2 #
3 # Bug#56287: crash when using Partition datetime in sub in query
4 #
5 CREATE TABLE t1
6 (c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT,
7 c2 varchar(40) not null default '',
8 c3 datetime not NULL,
9 PRIMARY KEY (c1,c3),
10 KEY partidx(c3))
11 ENGINE=InnoDB
12 PARTITION BY RANGE (TO_DAYS(c3))
13 (PARTITION p200912 VALUES LESS THAN (to_days('2010-01-01')),
14 PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),
15 PARTITION p201912 VALUES LESS THAN MAXVALUE);
16 insert into t1(c2,c3) values ("Test row",'2010-01-01 00:00:00');
17 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test';
18 PARTITION_NAME TABLE_ROWS
19 p200912 0
20 p201103 1
21 p201912 0
22 SELECT count(*) FROM t1 p where c3 in
23 (select c3 from t1 t where t.c3 < date '2011-04-26 19:19:44'
24 and t.c3 > date '2011-04-26 19:18:44') ;
25 count(*)
26 0
27 DROP TABLE t1;
28 #
29 # Bug#51830: Incorrect partition pruning on range partition (regression)
30 #
31 CREATE TABLE t1 (a INT NOT NULL)
32 ENGINE = InnoDB
33 PARTITION BY RANGE(a)
34 (PARTITION p10 VALUES LESS THAN (10),
35 PARTITION p30 VALUES LESS THAN (30),
36 PARTITION p50 VALUES LESS THAN (50),
37 PARTITION p70 VALUES LESS THAN (70),
38 PARTITION p90 VALUES LESS THAN (90));
39 INSERT INTO t1 VALUES (10),(30),(50);
40 INSERT INTO t1 VALUES (70);
41 INSERT INTO t1 VALUES (80);
42 INSERT INTO t1 VALUES (89);
43 INSERT INTO t1 VALUES (90);
44 ERROR HY000: Table has no partition for value 90
45 INSERT INTO t1 VALUES (100);
46 ERROR HY000: Table has no partition for value 100
47 insert INTO t1 VALUES (110);
48 ERROR HY000: Table has no partition for value 110
49 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90;
50 id select_type table partitions type possible_keys key key_len ref rows Extra
51 1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
52 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90;
53 id select_type table partitions type possible_keys key key_len ref rows Extra
54 1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
55 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90;
56 id select_type table partitions type possible_keys key key_len ref rows Extra
57 1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
58 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89;
59 id select_type table partitions type possible_keys key key_len ref rows Extra
60 1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where
61 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 89;
62 id select_type table partitions type possible_keys key key_len ref rows Extra
63 1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where
64 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89;
65 id select_type table partitions type possible_keys key key_len ref rows Extra
66 1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
67 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100;
68 id select_type table partitions type possible_keys key key_len ref rows Extra
69 1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
70 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100;
71 id select_type table partitions type possible_keys key key_len ref rows Extra
72 1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
73 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100;
74 id select_type table partitions type possible_keys key key_len ref rows Extra
75 1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
76 DROP TABLE t1;
77 #
78 # Bug#50104: Partitioned table with just 1 partion works with fk
79 #
80 CREATE TABLE t2 (
81 id INT,
82 PRIMARY KEY (id)
83 ) ENGINE=InnoDB ;
84 CREATE TABLE t1 (
85 id INT NOT NULL AUTO_INCREMENT,
86 parent_id INT DEFAULT NULL,
87 PRIMARY KEY (id),
88 KEY parent_id (parent_id)
89 ) ENGINE=InnoDB;
90 ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 1;
91 ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id);
92 ERROR HY000: Foreign key clause is not yet supported in conjunction with partitioning
93 ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 2;
94 ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id);
95 ERROR HY000: Foreign key clause is not yet supported in conjunction with partitioning
96 DROP TABLE t1, t2;
97 create table t1 (a int not null,
98 b datetime not null,
99 primary key (a,b))
100 engine=innodb
101 partition by range (to_days(b))
102 subpartition by hash (a)
103 subpartitions 2
104 ( partition p0 values less than (to_days('2009-01-01')),
105 partition p1 values less than (to_days('2009-02-01')),
106 partition p2 values less than (to_days('2009-03-01')),
107 partition p3 values less than maxvalue);
108 alter table t1 reorganize partition p1,p2 into
109 ( partition p2 values less than (to_days('2009-03-01')));
110 drop table t1;
111 CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB
112 PARTITION BY RANGE(id) (
113 PARTITION p0 VALUES LESS THAN (5),
114 PARTITION p1 VALUES LESS THAN (10),
115 PARTITION p2 VALUES LESS THAN MAXVALUE
116 );
117 INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8),
118 (9,9), (10,10), (11,11);
119 SET @old_tx_isolation := @@session.tx_isolation;
120 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
121 SET autocommit = 0;
122 UPDATE t1 SET DATA = data*2 WHERE id = 3;
123 SHOW ENGINE InnoDB STATUS;
124 Type Name Status
125 InnoDB 2 lock struct(s) 1 row lock(s)
126 UPDATE t1 SET data = data*2 WHERE data = 2;
127 SHOW ENGINE InnoDB STATUS;
128 Type Name Status
129 InnoDB 6 lock struct(s) 2 row lock(s)
130 SET @@session.tx_isolation = @old_tx_isolation;
131 DROP TABLE t1;
132 # Bug#37721, test of ORDER BY on PK and WHERE on INDEX
133 CREATE TABLE t1 (
134 a INT,
135 b INT,
136 PRIMARY KEY (a),
137 INDEX (b))
138 ENGINE InnoDB
139 PARTITION BY HASH(a)
140 PARTITIONS 3;
141 INSERT INTO t1 VALUES (0,0),(4,0),(2,0);
142 SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC;
143 a
144 0
145 2
146 4
147 SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC;
148 a
149 4
150 2
151 0
152 ALTER TABLE t1 DROP INDEX b;
153 SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC;
154 a
155 0
156 2
157 4
158 SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC;
159 a
160 4
161 2
162 0
163 DROP TABLE t1;
164 CREATE TABLE t1 (
165 a VARCHAR(600),
166 b VARCHAR(600),
167 PRIMARY KEY (a),
168 INDEX (b))
169 ENGINE InnoDB
170 PARTITION BY KEY(a)
171 PARTITIONS 3;
172 INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'1'),repeat('0',257));
173 INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'3'),repeat('0',257));
174 INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'2'),repeat('0',257));
175 SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC;
176 right(a,1)
177 1
178 2
179 3
180 SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC;
181 right(a,1)
182 3
183 2
184 1
185 ALTER TABLE t1 DROP INDEX b;
186 SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC;
187 right(a,1)
188 1
189 2
190 3
191 SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC;
192 right(a,1)
193 3
194 2
195 1
196 DROP TABLE t1;
197 # Bug#32948
198 CREATE TABLE t1 (c1 INT, PRIMARY KEY (c1)) ENGINE=INNODB;
199 CREATE TABLE t2 (c1 INT, PRIMARY KEY (c1),
200 FOREIGN KEY (c1) REFERENCES t1 (c1)
201 ON DELETE CASCADE)
202 ENGINE=INNODB;
203 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
204 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
205 ALTER TABLE t1 ENGINE=MyISAM;
206 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
207 DROP TABLE t2;
208 DROP TABLE t1;
209 create table t1 (a int) engine=innodb partition by hash(a) ;
210 show table status like 't1';
211 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
212 t1 InnoDB 10 Compact 2 8192 16384 0 0 # NULL NULL NULL NULL latin1_swedish_ci NULL partitioned
213 drop table t1;
214 create table t1 (a int)
215 engine = innodb
216 partition by key (a);
217 show table status;
218 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
219 t1 InnoDB 10 Compact 2 8192 16384 0 0 # NULL NULL NULL NULL latin1_swedish_ci NULL partitioned
220 insert into t1 values (0), (1), (2), (3);
221 show table status;
222 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
223 t1 InnoDB 10 Compact 4 4096 16384 0 0 # NULL NULL NULL NULL latin1_swedish_ci NULL partitioned
224 drop table t1;
225 create table t1 (a int auto_increment primary key)
226 engine = innodb
227 partition by key (a);
228 show table status;
229 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
230 t1 InnoDB 10 Compact 2 8192 16384 0 0 # 1 NULL NULL NULL latin1_swedish_ci NULL partitioned
231 insert into t1 values (NULL), (NULL), (NULL), (NULL);
232 show table status;
233 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
234 t1 InnoDB 10 Compact 4 4096 16384 0 0 # 5 NULL NULL NULL latin1_swedish_ci NULL partitioned
235 insert into t1 values (NULL), (NULL), (NULL), (NULL);
236 show table status;
237 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
238 t1 InnoDB 10 Compact 8 2048 16384 0 0 # 9 NULL NULL NULL latin1_swedish_ci NULL partitioned
239 drop table t1;
240 create table t1 (a int)
241 partition by key (a)
242 (partition p1 engine = innodb);
243 alter table t1 rebuild partition p1;
244 alter table t1 rebuild partition p1;
245 alter table t1 rebuild partition p1;
246 alter table t1 rebuild partition p1;
247 alter table t1 rebuild partition p1;
248 alter table t1 rebuild partition p1;
249 alter table t1 rebuild partition p1;
250 drop table t1;
251 create table t1 (a date)
252 engine = innodb
253 partition by range (year(a))
254 (partition p0 values less than (2006),
255 partition p1 values less than (2007));
256 explain partitions select * from t1
257 where a between '2006-01-01' and '2007-06-01';
258 id select_type table partitions type possible_keys key key_len ref rows Extra
259 1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where
260 drop table t1;
261 create table t1 (a int)
262 engine = x
263 partition by key (a);
264 Warnings:
265 Warning 1286 Unknown table engine 'x'
266 Warning 1266 Using storage engine MyISAM for table 't1'
267 show create table t1;
268 Table Create Table
269 t1 CREATE TABLE `t1` (
270 `a` int(11) DEFAULT NULL
271 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
272 /*!50100 PARTITION BY KEY (a) */
273 drop table t1;
274 create table t1 (a int)
275 engine = innodb
276 partition by list (a)
277 (partition p0 values in (0));
278 alter table t1 engine = x;
279 ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
280 show create table t1;
281 Table Create Table
282 t1 CREATE TABLE `t1` (
283 `a` int(11) DEFAULT NULL
284 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
285 /*!50100 PARTITION BY LIST (a)
286 (PARTITION p0 VALUES IN (0) ENGINE = InnoDB) */
287 drop table t1;
288 create table t1
289 (
290 id int unsigned auto_increment,
291 time datetime not null,
292 first_name varchar(40),
293 last_name varchar(50),
294 primary key (id, time),
295 index first_index (first_name),
296 index last_index (last_name)
297 ) engine=Innodb partition by range (to_days(time)) (
298 partition p1 values less than (to_days('2007-02-07')),
299 partition p2 values less than (to_days('2007-02-08')),
300 partition p3 values less than MAXVALUE
301 );
302 insert into t1 (time, first_name, last_name) values ('2007-02-07', 'Q', 'Robert'),
303 ('2007-02-07', 'Mark', 'Nate'), ('2007-02-07', 'Nate', 'Oscar'),
304 ('2007-02-07', 'Zack', 'Alice'), ('2007-02-07', 'Jack', 'Kathy'),
305 ('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Brian', 'Charles'),
306 ('2007-02-06', 'Charles', 'David'), ('2007-02-06', 'David', 'Eric'),
307 ('2007-02-07', 'Hector', 'Isaac'), ('2007-02-07', 'Oscar', 'Patricia'),
308 ('2007-02-07', 'Patricia', 'Q'), ('2007-02-07', 'X', 'Yuri'),
309 ('2007-02-07', 'Robert', 'Shawn'), ('2007-02-07', 'Kathy', 'Lois'),
310 ('2007-02-07', 'Eric', 'Francis'), ('2007-02-06', 'Shawn', 'Theron'),
311 ('2007-02-06', 'U', 'Vincent'), ('2007-02-06', 'Francis', 'George'),
312 ('2007-02-06', 'George', 'Hector'), ('2007-02-06', 'Vincent', 'Walter'),
313 ('2007-02-06', 'Walter', 'X'), ('2007-02-07', 'Lois', 'Mark'),
314 ('2007-02-07', 'Yuri', 'Zack'), ('2007-02-07', 'Isaac', 'Jack'),
315 ('2007-02-07', 'Sharon', 'Mark'), ('2007-02-07', 'Michael', 'Michelle'),
316 ('2007-02-07', 'Derick', 'Nathan'), ('2007-02-07', 'Peter', 'Xavier'),
317 ('2007-02-07', 'Fred', 'Harold'), ('2007-02-07', 'Katherine', 'Lisa'),
318 ('2007-02-07', 'Tom', 'Rina'), ('2007-02-07', 'Jerry', 'Victor'),
319 ('2007-02-07', 'Alexander', 'Terry'), ('2007-02-07', 'Justin', 'John'),
320 ('2007-02-07', 'Greg', 'Ernest'), ('2007-02-07', 'Robert', 'Q'),
321 ('2007-02-07', 'Nate', 'Mark'), ('2007-02-07', 'Oscar', 'Nate'),
322 ('2007-02-07', 'Alice', 'Zack'), ('2007-02-07', 'Kathy', 'Jack'),
323 ('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Charles', 'Brian'),
324 ('2007-02-06', 'David', 'Charles'), ('2007-02-06', 'Eric', 'David'),
325 ('2007-02-07', 'Isaac', 'Hector'), ('2007-02-07', 'Patricia', 'Oscar'),
326 ('2007-02-07', 'Q', 'Patricia'), ('2007-02-07', 'Yuri', 'X'),
327 ('2007-02-07', 'Shawn', 'Robert'), ('2007-02-07', 'Lois', 'Kathy'),
328 ('2007-02-07', 'Francis', 'Eric'), ('2007-02-06', 'Theron', 'Shawn'),
329 ('2007-02-06', 'Vincent', 'U'), ('2007-02-06', 'George', 'Francis'),
330 ('2007-02-06', 'Hector', 'George'), ('2007-02-06', 'Walter', 'Vincent'),
331 ('2007-02-06', 'X', 'Walter'), ('2007-02-07', 'Mark', 'Lois'),
332 ('2007-02-07', 'Zack', 'Yuri'), ('2007-02-07', 'Jack', 'Isaac'),
333 ('2007-02-07', 'Mark', 'Sharon'), ('2007-02-07', 'Michelle', 'Michael'),
334 ('2007-02-07', 'Nathan', 'Derick'), ('2007-02-07', 'Xavier', 'Peter'),
335 ('2007-02-07', 'Harold', 'Fred'), ('2007-02-07', 'Lisa', 'Katherine'),
336 ('2007-02-07', 'Rina', 'Tom'), ('2007-02-07', 'Victor', 'Jerry'),
337 ('2007-02-07', 'Terry', 'Alexander'), ('2007-02-07', 'John', 'Justin'),
338 ('2007-02-07', 'Ernest', 'Greg');
339 SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake';
340 id time first_name last_name
341 drop table t1;
342 CREATE TABLE t1 (a DOUBLE NOT NULL, KEY(a)) ENGINE=InnoDB
343 PARTITION BY KEY(a) PARTITIONS 10;
344 INSERT INTO t1 VALUES(1),(2);
345 SELECT COUNT(*) FROM t1;
346 COUNT(*)
347 2
348 DROP TABLE t1;
349 create table t1 (int_column int, char_column char(5))
350 PARTITION BY RANGE (int_column) subpartition by key (char_column) subpartitions 2
351 (PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB);
352 alter table t1
353 ENGINE = MyISAM
354 PARTITION BY RANGE (int_column)
355 subpartition by key (char_column) subpartitions 2
356 (PARTITION p1 VALUES LESS THAN (5));
357 show create table t1;
358 Table Create Table
359 t1 CREATE TABLE `t1` (
360 `int_column` int(11) DEFAULT NULL,
361 `char_column` char(5) DEFAULT NULL
362 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
363 /*!50100 PARTITION BY RANGE (int_column)
364 SUBPARTITION BY KEY (char_column)
365 SUBPARTITIONS 2
366 (PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM) */
367 drop table t1;
368 CREATE TABLE t1 (a INT) ENGINE=InnoDB
369 PARTITION BY list(a) (PARTITION p1 VALUES IN (1));
370 CREATE INDEX i1 ON t1 (a);
371 DROP TABLE t1;
372 #
373 # Bug#47343: InnoDB fails to clean-up after lock wait timeout on
374 # REORGANIZE PARTITION
375 #
376 CREATE TABLE t1 (
377 a INT,
378 b DATE NOT NULL,
379 PRIMARY KEY (a, b)
380 ) ENGINE=InnoDB
381 PARTITION BY RANGE (a) (
382 PARTITION pMAX VALUES LESS THAN MAXVALUE
383 ) ;
384 INSERT INTO t1 VALUES (1, '2001-01-01'), (2, '2002-02-02'), (3, '2003-03-03');
385 START TRANSACTION;
386 SELECT * FROM t1 FOR UPDATE;
387 a b
388 1 2001-01-01
389 2 2002-02-02
390 3 2003-03-03
391 # Connection con1
392 ALTER TABLE t1 REORGANIZE PARTITION pMAX INTO
393 (PARTITION p3 VALUES LESS THAN (3),
394 PARTITION pMAX VALUES LESS THAN MAXVALUE);
395 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
396 SHOW WARNINGS;
397 Level Code Message
398 Error 1205 Lock wait timeout exceeded; try restarting transaction
399 ALTER TABLE t1 REORGANIZE PARTITION pMAX INTO
400 (PARTITION p3 VALUES LESS THAN (3),
401 PARTITION pMAX VALUES LESS THAN MAXVALUE);
402 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
403 SHOW WARNINGS;
404 Level Code Message
405 Error 1205 Lock wait timeout exceeded; try restarting transaction
406 t1.frm
407 t1.par
408 # Connection default
409 SELECT * FROM t1;
410 a b
411 1 2001-01-01
412 2 2002-02-02
413 3 2003-03-03
414 COMMIT;
415 DROP TABLE t1;
416 CREATE TABLE t1 (i1 int NOT NULL primary key, f1 int) ENGINE = InnoDB
417 PARTITION BY HASH(i1) PARTITIONS 2;
418 INSERT INTO t1 VALUES (1,1), (2,2);
419 SELECT * FROM t1 WHERE i1 = ( SELECT i1 FROM t1 WHERE f1=0 LIMIT 1 );
420 i1 f1
421 DROP TABLE t1;