1 --source include/have_partition.inc
2 --source include/have_innodb.inc
5 drop table if exists t1, t2;
8 let $MYSQLD_DATADIR= `SELECT @@datadir`;
11 --echo # Bug#56287: crash when using Partition datetime in sub in query
14 (c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT,
15 c2 varchar(40) not null default '',
20 PARTITION BY RANGE (TO_DAYS(c3))
21 (PARTITION p200912 VALUES LESS THAN (to_days('2010-01-01')),
22 PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),
23 PARTITION p201912 VALUES LESS THAN MAXVALUE);
25 insert into t1(c2,c3) values ("Test row",'2010-01-01 00:00:00');
27 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test';
28 SELECT count(*) FROM t1 p where c3 in
29 (select c3 from t1 t where t.c3 < date '2011-04-26 19:19:44'
30 and t.c3 > date '2011-04-26 19:18:44') ;
35 --echo # Bug#51830: Incorrect partition pruning on range partition (regression)
37 CREATE TABLE t1 (a INT NOT NULL)
40 (PARTITION p10 VALUES LESS THAN (10),
41 PARTITION p30 VALUES LESS THAN (30),
42 PARTITION p50 VALUES LESS THAN (50),
43 PARTITION p70 VALUES LESS THAN (70),
44 PARTITION p90 VALUES LESS THAN (90));
45 INSERT INTO t1 VALUES (10),(30),(50);
46 INSERT INTO t1 VALUES (70);
47 INSERT INTO t1 VALUES (80);
48 INSERT INTO t1 VALUES (89);
49 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
50 INSERT INTO t1 VALUES (90);
51 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
52 INSERT INTO t1 VALUES (100);
53 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
54 insert INTO t1 VALUES (110);
55 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90;
56 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90;
57 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90;
58 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89;
59 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 89;
60 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89;
61 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100;
62 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100;
63 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100;
67 --echo # Bug#50104: Partitioned table with just 1 partion works with fk
75 id INT NOT NULL AUTO_INCREMENT,
76 parent_id INT DEFAULT NULL,
78 KEY parent_id (parent_id)
81 ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 1;
83 --error ER_FOREIGN_KEY_ON_PARTITIONED
84 ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id);
86 ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 2;
88 --error ER_FOREIGN_KEY_ON_PARTITIONED
89 ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id);
94 # Bug#47029: Crash when reorganize partition with subpartition
96 create table t1 (a int not null,
100 partition by range (to_days(b))
101 subpartition by hash (a)
103 ( partition p0 values less than (to_days('2009-01-01')),
104 partition p1 values less than (to_days('2009-02-01')),
105 partition p2 values less than (to_days('2009-03-01')),
106 partition p3 values less than maxvalue);
107 alter table t1 reorganize partition p1,p2 into
108 ( partition p2 values less than (to_days('2009-03-01')));
111 # Bug#40595: Non-matching rows not released with READ-COMMITTED on tables
113 CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB
114 PARTITION BY RANGE(id) (
115 PARTITION p0 VALUES LESS THAN (5),
116 PARTITION p1 VALUES LESS THAN (10),
117 PARTITION p2 VALUES LESS THAN MAXVALUE
120 INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8),
121 (9,9), (10,10), (11,11);
123 SET @old_tx_isolation := @@session.tx_isolation;
124 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
128 UPDATE t1 SET DATA = data*2 WHERE id = 3;
130 # grouping/referencing in replace_regex is very slow on long strings,
131 # removing all before/after the interesting row before grouping/referencing
132 --replace_regex /.*---TRANSACTION [0-9]+ [0-9]+, .*, OS thread id [0-9]+// /MySQL thread id [0-9]+, query id [0-9]+ .*// /.*([0-9]+ lock struct\(s\)), heap size [0-9]+, ([0-9]+ row lock\(s\)).*/\1 \2/
133 SHOW ENGINE InnoDB STATUS;
135 UPDATE t1 SET data = data*2 WHERE data = 2;
137 # grouping/referencing in replace_regex is very slow on long strings,
138 # removing all before/after the interesting row before grouping/referencing
139 --replace_regex /.*---TRANSACTION [0-9]+ [0-9]+, .*, OS thread id [0-9]+// /MySQL thread id [0-9]+, query id [0-9]+ .*// /.*([0-9]+ lock struct\(s\)), heap size [0-9]+, ([0-9]+ row lock\(s\)).*/\1 \2/
140 SHOW ENGINE InnoDB STATUS;
142 SET @@session.tx_isolation = @old_tx_isolation;
147 # Bug37721: ORDER BY when WHERE contains non-partitioned index column
148 # wrong order since it did not use pk as second compare
149 --echo # Bug#37721, test of ORDER BY on PK and WHERE on INDEX
158 # This will give the middle partition the highest value
159 INSERT INTO t1 VALUES (0,0),(4,0),(2,0);
160 SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC;
161 SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC;
162 ALTER TABLE t1 DROP INDEX b;
163 SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC;
164 SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC;
174 # This will give the middle partition the highest value
175 INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'1'),repeat('0',257));
176 INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'3'),repeat('0',257));
177 INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'2'),repeat('0',257));
178 SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC;
179 SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC;
180 ALTER TABLE t1 DROP INDEX b;
181 SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC;
182 SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC;
186 # Bug#32948 - FKs allowed to reference partitioned table
189 CREATE TABLE t1 (c1 INT, PRIMARY KEY (c1)) ENGINE=INNODB;
190 CREATE TABLE t2 (c1 INT, PRIMARY KEY (c1),
191 FOREIGN KEY (c1) REFERENCES t1 (c1)
194 --error ER_ROW_IS_REFERENCED
195 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
196 --error ER_ROW_IS_REFERENCED
197 ALTER TABLE t1 ENGINE=MyISAM;
202 # Bug #14673: Wrong InnoDB default row format
204 create table t1 (a int) engine=innodb partition by hash(a) ;
205 # Data_free for InnoDB tablespace varies depending on which
206 # tests have been run before this one
207 --replace_column 10 #
208 show table status like 't1';
212 # Bug 21173: SHOW TABLE STATUS crashes server in InnoDB
214 create table t1 (a int)
216 partition by key (a);
217 # Data_free for InnoDB tablespace varies depending on which
218 # tests have been run before this one
219 --replace_column 10 #
221 insert into t1 values (0), (1), (2), (3);
222 # Data_free for InnoDB tablespace varies depending on which
223 # tests have been run before this one
224 --replace_column 10 #
228 create table t1 (a int auto_increment primary key)
230 partition by key (a);
231 # Data_free for InnoDB tablespace varies depending on which
232 # tests have been run before this one
233 --replace_column 10 #
235 insert into t1 values (NULL), (NULL), (NULL), (NULL);
236 # Data_free for InnoDB tablespace varies depending on which
237 # tests have been run before this one
238 --replace_column 10 #
240 insert into t1 values (NULL), (NULL), (NULL), (NULL);
241 # Data_free for InnoDB tablespace varies depending on which
242 # tests have been run before this one
243 --replace_column 10 #
248 # BUG 19122 Crash after ALTER TABLE t1 REBUILD PARTITION p1
250 create table t1 (a int)
252 (partition p1 engine = innodb);
254 alter table t1 rebuild partition p1;
255 alter table t1 rebuild partition p1;
256 alter table t1 rebuild partition p1;
257 alter table t1 rebuild partition p1;
258 alter table t1 rebuild partition p1;
259 alter table t1 rebuild partition p1;
260 alter table t1 rebuild partition p1;
264 # Bug 21339: Crash in Explain Partitions
266 create table t1 (a date)
268 partition by range (year(a))
269 (partition p0 values less than (2006),
270 partition p1 values less than (2007));
271 explain partitions select * from t1
272 where a between '2006-01-01' and '2007-06-01';
276 # Bug 20397: Partitions: Crash when using non-existing engine
278 create table t1 (a int)
280 partition by key (a);
281 show create table t1;
284 create table t1 (a int)
286 partition by list (a)
287 (partition p0 values in (0));
289 -- error ER_MIX_HANDLER_ERROR
290 alter table t1 engine = x;
291 show create table t1;
294 # BUG#26117: index_merge sort-union over partitioned table crashes
298 id int unsigned auto_increment,
299 time datetime not null,
300 first_name varchar(40),
301 last_name varchar(50),
302 primary key (id, time),
303 index first_index (first_name),
304 index last_index (last_name)
305 ) engine=Innodb partition by range (to_days(time)) (
306 partition p1 values less than (to_days('2007-02-07')),
307 partition p2 values less than (to_days('2007-02-08')),
308 partition p3 values less than MAXVALUE
311 insert into t1 (time, first_name, last_name) values ('2007-02-07', 'Q', 'Robert'),
312 ('2007-02-07', 'Mark', 'Nate'), ('2007-02-07', 'Nate', 'Oscar'),
313 ('2007-02-07', 'Zack', 'Alice'), ('2007-02-07', 'Jack', 'Kathy'),
314 ('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Brian', 'Charles'),
315 ('2007-02-06', 'Charles', 'David'), ('2007-02-06', 'David', 'Eric'),
316 ('2007-02-07', 'Hector', 'Isaac'), ('2007-02-07', 'Oscar', 'Patricia'),
317 ('2007-02-07', 'Patricia', 'Q'), ('2007-02-07', 'X', 'Yuri'),
318 ('2007-02-07', 'Robert', 'Shawn'), ('2007-02-07', 'Kathy', 'Lois'),
319 ('2007-02-07', 'Eric', 'Francis'), ('2007-02-06', 'Shawn', 'Theron'),
320 ('2007-02-06', 'U', 'Vincent'), ('2007-02-06', 'Francis', 'George'),
321 ('2007-02-06', 'George', 'Hector'), ('2007-02-06', 'Vincent', 'Walter'),
322 ('2007-02-06', 'Walter', 'X'), ('2007-02-07', 'Lois', 'Mark'),
323 ('2007-02-07', 'Yuri', 'Zack'), ('2007-02-07', 'Isaac', 'Jack'),
324 ('2007-02-07', 'Sharon', 'Mark'), ('2007-02-07', 'Michael', 'Michelle'),
325 ('2007-02-07', 'Derick', 'Nathan'), ('2007-02-07', 'Peter', 'Xavier'),
326 ('2007-02-07', 'Fred', 'Harold'), ('2007-02-07', 'Katherine', 'Lisa'),
327 ('2007-02-07', 'Tom', 'Rina'), ('2007-02-07', 'Jerry', 'Victor'),
328 ('2007-02-07', 'Alexander', 'Terry'), ('2007-02-07', 'Justin', 'John'),
329 ('2007-02-07', 'Greg', 'Ernest'), ('2007-02-07', 'Robert', 'Q'),
330 ('2007-02-07', 'Nate', 'Mark'), ('2007-02-07', 'Oscar', 'Nate'),
331 ('2007-02-07', 'Alice', 'Zack'), ('2007-02-07', 'Kathy', 'Jack'),
332 ('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Charles', 'Brian'),
333 ('2007-02-06', 'David', 'Charles'), ('2007-02-06', 'Eric', 'David'),
334 ('2007-02-07', 'Isaac', 'Hector'), ('2007-02-07', 'Patricia', 'Oscar'),
335 ('2007-02-07', 'Q', 'Patricia'), ('2007-02-07', 'Yuri', 'X'),
336 ('2007-02-07', 'Shawn', 'Robert'), ('2007-02-07', 'Lois', 'Kathy'),
337 ('2007-02-07', 'Francis', 'Eric'), ('2007-02-06', 'Theron', 'Shawn'),
338 ('2007-02-06', 'Vincent', 'U'), ('2007-02-06', 'George', 'Francis'),
339 ('2007-02-06', 'Hector', 'George'), ('2007-02-06', 'Walter', 'Vincent'),
340 ('2007-02-06', 'X', 'Walter'), ('2007-02-07', 'Mark', 'Lois'),
341 ('2007-02-07', 'Zack', 'Yuri'), ('2007-02-07', 'Jack', 'Isaac'),
342 ('2007-02-07', 'Mark', 'Sharon'), ('2007-02-07', 'Michelle', 'Michael'),
343 ('2007-02-07', 'Nathan', 'Derick'), ('2007-02-07', 'Xavier', 'Peter'),
344 ('2007-02-07', 'Harold', 'Fred'), ('2007-02-07', 'Lisa', 'Katherine'),
345 ('2007-02-07', 'Rina', 'Tom'), ('2007-02-07', 'Victor', 'Jerry'),
346 ('2007-02-07', 'Terry', 'Alexander'), ('2007-02-07', 'John', 'Justin'),
347 ('2007-02-07', 'Ernest', 'Greg');
349 SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake';
354 # BUG#30583 - Partition on DOUBLE key + INNODB + count(*) == crash
356 CREATE TABLE t1 (a DOUBLE NOT NULL, KEY(a)) ENGINE=InnoDB
357 PARTITION BY KEY(a) PARTITIONS 10;
358 INSERT INTO t1 VALUES(1),(2);
359 SELECT COUNT(*) FROM t1;
363 # Bug #31893 Partitions: crash if subpartitions and engine change
365 create table t1 (int_column int, char_column char(5))
366 PARTITION BY RANGE (int_column) subpartition by key (char_column) subpartitions 2
367 (PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB);
370 PARTITION BY RANGE (int_column)
371 subpartition by key (char_column) subpartitions 2
372 (PARTITION p1 VALUES LESS THAN (5));
373 show create table t1;
377 # BUG#46483 - drop table of partitioned table may leave extraneous file
378 # Note: was only repeatable with InnoDB plugin
380 CREATE TABLE t1 (a INT) ENGINE=InnoDB
381 PARTITION BY list(a) (PARTITION p1 VALUES IN (1));
382 CREATE INDEX i1 ON t1 (a);
385 # Before the fix it should show extra file like #sql-2405_2.par
386 --list_files $MYSQLD_DATADIR/test/ *
389 --echo # Bug#47343: InnoDB fails to clean-up after lock wait timeout on
390 --echo # REORGANIZE PARTITION
397 PARTITION BY RANGE (a) (
398 PARTITION pMAX VALUES LESS THAN MAXVALUE
401 INSERT INTO t1 VALUES (1, '2001-01-01'), (2, '2002-02-02'), (3, '2003-03-03');
404 SELECT * FROM t1 FOR UPDATE;
406 connect (con1, localhost, root,,);
407 --echo # Connection con1
408 --error ER_LOCK_WAIT_TIMEOUT
409 ALTER TABLE t1 REORGANIZE PARTITION pMAX INTO
410 (PARTITION p3 VALUES LESS THAN (3),
411 PARTITION pMAX VALUES LESS THAN MAXVALUE);
413 --error ER_LOCK_WAIT_TIMEOUT
414 ALTER TABLE t1 REORGANIZE PARTITION pMAX INTO
415 (PARTITION p3 VALUES LESS THAN (3),
416 PARTITION pMAX VALUES LESS THAN MAXVALUE);
419 #Contents of the 'test' database directory:
420 --list_files $MYSQLD_DATADIR/test
424 --echo # Connection default
430 # Bug #55146 Assertion `m_part_spec.start_part == m_part_spec.end_part' in index_read_idx_map
433 CREATE TABLE t1 (i1 int NOT NULL primary key, f1 int) ENGINE = InnoDB
434 PARTITION BY HASH(i1) PARTITIONS 2;
436 INSERT INTO t1 VALUES (1,1), (2,2);
438 SELECT * FROM t1 WHERE i1 = ( SELECT i1 FROM t1 WHERE f1=0 LIMIT 1 );