1 #--disable_abort_on_error
3 # Simple test for the partition storage engine
4 # taken from the select test.
7 # 2007-10-22 mleich - Move ARCHIVE, BLACKHOLE and CSV related sub tests to
8 # new tests. Reason: All these might be not available.
11 --source include/have_partition.inc
14 drop table if exists t1, t2;
18 --echo # Bug#11765667: bug#58655: ASSERTION FAILED,
19 --echo # SERVER CRASHES WITH MYSQLD GOT SIGNAL 6
22 id MEDIUMINT NOT NULL AUTO_INCREMENT,
23 dt DATE, st VARCHAR(255), uid INT,
24 id2nd LONGBLOB, filler VARCHAR(255), PRIMARY KEY(id, dt)
26 INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES
27 ('1991-03-14', 'Initial Insert', 200, 1234567, 'No Data'),
28 ('1991-02-26', 'Initial Insert', 201, 1234567, 'No Data'),
29 ('1992-03-16', 'Initial Insert', 234, 1234567, 'No Data'),
30 ('1992-07-02', 'Initial Insert', 287, 1234567, 'No Data'),
31 ('1991-05-26', 'Initial Insert', 256, 1234567, 'No Data'),
32 ('1991-04-25', 'Initial Insert', 222, 1234567, 'No Data'),
33 ('1993-03-12', 'Initial Insert', 267, 1234567, 'No Data'),
34 ('1993-03-14', 'Initial Insert', 291, 1234567, 'No Data'),
35 ('1991-12-20', 'Initial Insert', 298, 1234567, 'No Data'),
36 ('1994-10-31', 'Initial Insert', 220, 1234567, 'No Data');
37 ALTER TABLE t1 PARTITION BY LIST (YEAR(dt)) (
38 PARTITION d1 VALUES IN (1991, 1994),
39 PARTITION d2 VALUES IN (1993),
40 PARTITION d3 VALUES IN (1992, 1995, 1996)
42 INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES
43 ('1991-07-14', 'After Partitioning Insert', 299, 1234567, 'Insert row');
44 UPDATE t1 SET filler='Updating the row' WHERE uid=298;
49 --echo # Bug#59297: Can't find record in 'tablename' on update inner join
55 c int(10) unsigned NOT NULL,
56 d varchar(255) DEFAULT NULL,
57 e varchar(1000) DEFAULT NULL,
58 PRIMARY KEY (a, b, c),
62 /*!50100 PARTITION BY KEY (a)
65 INSERT INTO t1 (a, b, c, d, e) VALUES
66 ('07', '03', 343, '1', '07_03_343'),
67 ('01', '04', 343, '2', '01_04_343'),
68 ('01', '06', 343, '3', '01_06_343'),
69 ('01', '07', 343, '4', '01_07_343'),
70 ('01', '08', 343, '5', '01_08_343'),
71 ('01', '09', 343, '6', '01_09_343'),
72 ('03', '03', 343, '7', '03_03_343'),
73 ('03', '06', 343, '8', '03_06_343'),
74 ('03', '07', 343, '9', '03_07_343'),
75 ('04', '03', 343, '10', '04_03_343'),
76 ('04', '06', 343, '11', '04_06_343'),
77 ('05', '03', 343, '12', '05_03_343'),
78 ('11', '03', 343, '13', '11_03_343'),
79 ('11', '04', 343, '14', '11_04_343')
83 (SELECT '03' AS a, '06' AS b, 343 AS c, 'last' AS d) AS B
92 --echo # Bug#57113: ha_partition::extra(ha_extra_function):
93 --echo # Assertion `m_extra_cache' failed
95 (id INT NOT NULL PRIMARY KEY,
96 name VARCHAR(16) NOT NULL,
100 PARTITION BY HASH(id) PARTITIONS 2;
102 INSERT INTO t1 VALUES ( 1, 'FooBar', '1924' );
104 CREATE TABLE t2 (id INT);
106 INSERT INTO t2 VALUES (1),(2);
108 UPDATE t1, t2 SET t1.year = '1955' WHERE t1.name = 'FooBar';
114 --echo # Bug#55458: Partitioned MyISAM table gets crashed by multi-table update
118 `user_num` int DEFAULT NULL,
120 ) ENGINE=MyISAM CHARSET=latin1;
121 INSERT INTO t1 VALUES (1,8601);
122 INSERT INTO t1 VALUES (2,8601);
123 INSERT INTO t1 VALUES (3,8601);
124 INSERT INTO t1 VALUES (4,8601);
126 `id` int(11) NOT NULL,
127 `user_num` int DEFAULT NULL,
128 `name` varchar(64) NOT NULL,
130 ) ENGINE=MyISAM CHARSET=latin1
131 PARTITION BY HASH (id)
133 INSERT INTO t2 VALUES (1,8601,'John');
134 INSERT INTO t2 VALUES (2,8601,'JS');
135 INSERT INTO t2 VALUES (3,8601,'John S');
137 UPDATE t1, t2 SET t2.name = 'John Smith' WHERE t1.user_num = t2.user_num;
141 # Bug#48276: can't add column if subpartition exists
142 CREATE TABLE t1 (a INT, b INT)
143 PARTITION BY LIST (a)
144 SUBPARTITION BY HASH (b)
145 (PARTITION p1 VALUES IN (1));
146 ALTER TABLE t1 ADD COLUMN c INT;
150 # Bug#46639: 1030 (HY000): Got error 124 from storage engine on
151 # INSERT ... SELECT ...
161 PARTITION BY HASH(a) PARTITIONS 2;
163 INSERT INTO t1 VALUES (399, 22);
164 INSERT INTO t2 VALUES (1, 22), (1, 42);
166 INSERT INTO t2 SELECT 1, 399 FROM t2, t1
172 # Bug#46478: timestamp field incorrectly defaulted when partition is reorganized
175 a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
179 PARTITION BY RANGE (UNIX_TIMESTAMP(a)) (
180 PARTITION p1 VALUES LESS THAN (1199134800),
181 PARTITION pmax VALUES LESS THAN MAXVALUE
184 INSERT INTO t1 VALUES ('2007-07-30 17:35:48', 'p1');
185 INSERT INTO t1 VALUES ('2009-07-14 17:35:55', 'pmax');
186 INSERT INTO t1 VALUES ('2009-09-21 17:31:42', 'pmax');
189 ALTER TABLE t1 REORGANIZE PARTITION pmax INTO (
190 PARTITION p3 VALUES LESS THAN (1247688000),
191 PARTITION pmax VALUES LESS THAN MAXVALUE);
193 SHOW CREATE TABLE t1;
197 # Bug#45904: Error when CHARSET=utf8 and subpartitioning
199 create table t1 (a int NOT NULL, b varchar(5) NOT NULL)
201 partition by list (a)
202 subpartition by key (b)
203 (partition p0 values in (1),
204 partition p1 values in (2));
208 # Bug#44059: rec_per_key on empty partition gives weird optimiser results
210 create table t1 (a int, b int, key(a))
211 partition by list (a)
212 ( partition p0 values in (1),
213 partition p1 values in (2));
214 insert into t1 values (1,1),(2,1),(2,2),(2,3);
215 show indexes from t1;
217 show indexes from t1;
221 # Bug#36001: Partitions: spelling and using some error messages
223 --error ER_FOREIGN_KEY_ON_PARTITIONED
224 CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a))
226 PARTITION BY HASH (a);
229 # Bug#40954: Crash if range search and order by.
232 pk INT NOT NULL AUTO_INCREMENT,
235 /*!50100 PARTITION BY HASH (pk)
237 INSERT INTO t1 VALUES (NULL);
238 INSERT INTO t1 VALUES (NULL);
239 INSERT INTO t1 VALUES (NULL);
240 SELECT * FROM t1 WHERE pk < 0 ORDER BY pk;
244 # Bug#40494: Crash MYSQL server crashes on range access with partitioning
247 CREATE TABLE t1 (a INT NOT NULL, KEY(a))
248 PARTITION BY RANGE(a)
249 (PARTITION p1 VALUES LESS THAN (200), PARTITION pmax VALUES LESS THAN MAXVALUE);
250 INSERT INTO t1 VALUES (2), (40), (40), (70), (60), (90), (199);
251 SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a ASC;
253 SELECT a FROM t1 WHERE a BETWEEN 60 AND 95;
254 INSERT INTO t1 VALUES (200), (250), (210);
255 SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a ASC;
256 SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a ASC;
257 SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a DESC;
258 SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a DESC;
259 SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a DESC;
261 SELECT a FROM t1 WHERE a BETWEEN 60 AND 220;
263 SELECT a FROM t1 WHERE a BETWEEN 200 AND 220;
265 SELECT a FROM t1 WHERE a BETWEEN 60 AND 95;
267 SELECT a FROM t1 WHERE a BETWEEN 60 AND 220;
269 SELECT a FROM t1 WHERE a BETWEEN 200 AND 220;
273 # Bug35931: Index search may return duplicates
277 b MEDIUMINT NOT NULL,
281 PARTITION BY LIST (a) (
282 PARTITION p0 VALUES IN (1)
284 INSERT INTO t1 VALUES (1,1,0), (1,1,1), (1,1,2), (1,1,53), (1,1,4), (1,1,5),
285 (1,1,6), (1,1,7), (1,1,8), (1,1,9), (1,1,10), (1,1,11), (1,1,12), (1,1,13),
286 (1,1,14), (1,1,15), (1,1,16), (1,1,67), (1,1,18), (1,1,19), (1,1,20), (1,1,21),
287 (1,1,22), (1,1,23), (1,1,24), (1,1,75), (1,1,26), (1,1,27), (1,1,128),
288 (1,1,79), (1,1,30), (1,1,31), (1,1,32), (1,1,33), (1,1,34), (1,1,85), (1,1,36),
289 (1,1,37), (1,1,38), (1,1,39), (1,1,40), (1,1,241), (1,1,42), (1,1,43),
290 (1,1,44), (1,1,45), (1,1,46), (1,1,147), (1,1,48), (1,1,49), (1,2,0), (1,2,1),
291 (1,2,2), (1,2,3), (1,2,4), (1,2,5), (1,2,6), (1,2,7), (1,2,8), (1,2,9),
292 (1,2,10), (1,2,11), (1,2,12), (1,2,13), (1,2,14), (1,2,15), (1,2,16), (1,2,17),
293 (1,2,18), (1,2,19), (1,2,20), (1,2,21), (1,2,22), (1,2,23), (1,2,24), (1,2,25),
294 (1,2,26), (1,2,27), (1,2,28), (1,2,29), (1,2,30), (1,2,31), (1,2,32), (1,2,33),
295 (1,2,34), (1,2,35), (1,2,36), (1,2,37), (1,2,38), (1,2,39), (1,2,40), (1,2,41),
296 (1,2,42), (1,2,43), (1,2,44), (1,2,45), (1,2,46), (1,2,47), (1,2,48), (1,2,49),
297 (1,6,0), (1,6,1), (1,6,2), (1,6,3), (1,6,4), (1,6,5), (1,6,6), (1,6,7),
298 (1,6,8), (1,6,9), (1,6,10), (1,6,11), (1,6,12), (1,6,13), (1,6,14), (1,6,15),
299 (1,6,16), (1,6,17), (1,6,18), (1,6,19), (1,6,20), (1,6,21), (1,6,22), (1,6,23),
300 (1,6,24), (1,6,25), (1,6,26), (1,6,27), (1,6,28), (1,6,29), (1,6,30), (1,6,31),
301 (1,6,32), (1,6,33), (1,6,34), (1,6,35), (1,6,36), (1,6,37), (1,6,38), (1,6,39),
302 (1,6,40), (1,6,41), (1,6,42), (1,6,43), (1,6,44), (1,6,45), (1,6,46), (1,6,47),
303 (1,6,48), (1,6,49), (1,7,0), (1,7,1), (1,7,2), (1,7,3), (1,7,4), (1,7,5),
304 (1,7,6), (1,7,7), (1,7,8), (1,7,9), (1,7,10), (1,7,11), (1,7,12), (1,7,13),
305 (1,7,14), (1,7,15), (1,7,16), (1,7,17), (1,7,18), (1,7,19), (1,7,20), (1,7,21),
306 (1,7,22), (1,7,23), (1,7,24), (1,7,25), (1,7,26), (1,7,27), (1,7,28), (1,7,29),
307 (1,7,30), (1,7,31), (1,7,32), (1,7,33), (1,7,34), (1,7,35), (1,7,38), (1,7,39),
308 (1,7,90), (1,7,41), (1,7,43), (1,7,48), (1,7,49), (1,9,0), (1,9,1), (1,9,2),
309 (1,9,3), (1,9,4), (1,9,5), (1,9,6), (1,9,7), (1,9,8), (1,9,9), (1,9,10),
310 (1,9,11), (1,9,12), (1,9,13), (1,9,14), (1,9,15), (1,9,16), (1,9,17), (1,9,18),
311 (1,9,19), (1,9,20), (1,9,21), (1,9,22), (1,9,23), (1,9,24), (1,9,25), (1,9,26),
312 (1,9,29), (1,9,32), (1,9,35), (1,9,38), (1,10,0), (1,10,1), (1,10,2), (1,10,3),
313 (1,10,4), (1,10,5), (1,10,6), (1,10,7), (1,10,8), (1,10,9), (1,10,10),
314 (1,10,11), (1,10,13), (1,10,14), (1,10,15), (1,10,16), (1,10,17), (1,10,18),
315 (1,10,22), (1,10,24), (1,10,25), (1,10,26), (1,10,28), (1,10,131), (1,10,33),
316 (1,10,84), (1,10,35), (1,10,40), (1,10,42), (1,10,49), (1,11,0), (1,11,1),
317 (1,11,2), (1,11,3), (1,11,4), (1,11,5), (1,11,6), (1,11,7), (1,11,8), (1,11,9),
318 (1,11,10), (1,11,11), (1,11,12), (1,11,13), (1,11,14), (1,11,15), (1,11,16),
319 (1,11,17), (1,11,18), (1,11,19), (1,11,20), (1,11,21), (1,11,22), (1,11,23),
320 (1,11,24), (1,11,25), (1,11,26), (1,11,27), (1,11,28), (1,11,30), (1,11,31),
321 (1,11,32), (1,11,33), (1,11,34), (1,11,35), (1,11,37), (1,11,39), (1,11,40),
322 (1,11,42), (1,11,44), (1,11,45), (1,11,47), (1,11,48), (1,14,104), (1,14,58),
323 (1,14,12), (1,14,13), (1,14,15), (1,14,16), (1,14,17), (1,14,34), (1,15,0),
324 (1,15,1), (1,15,2), (1,15,3), (1,15,4), (1,15,5), (1,15,7), (1,15,9),
325 (1,15,15), (1,15,27), (1,15,49), (1,16,0), (1,16,1), (1,16,3), (1,17,4),
327 SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
328 SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
329 ALTER TABLE t1 DROP INDEX b;
330 SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
331 SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
332 ALTER TABLE t1 ENGINE = Memory;
333 SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
334 SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
335 ALTER TABLE t1 ADD INDEX b USING HASH (b);
336 SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
337 SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
340 # Bug#37327 Range scan on partitioned table returns duplicate rows
341 # (Duplicate of Bug#35931)
343 `c1` int(11) DEFAULT NULL,
345 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
348 `c1` int(11) DEFAULT NULL,
350 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (c1) (PARTITION a VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION b VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;
352 INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
353 INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
355 EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
358 SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
359 SHOW STATUS LIKE 'Handler_read_%';
361 EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
364 SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
365 SHOW STATUS LIKE 'Handler_read_%';
368 # Bug#37329 Range scan on partitioned tables shows higher Handler_read_next
369 # (marked as duplicate of Bug#35931)
371 `c1` int(11) DEFAULT NULL,
373 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
376 `c1` int(11) DEFAULT NULL,
378 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
379 /*!50100 PARTITION BY RANGE (c1)
380 (PARTITION a VALUES LESS THAN (100) ENGINE = MyISAM,
381 PARTITION b VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;
383 INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
384 INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
386 EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5);
389 SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5);
390 SHOW STATUS LIKE 'Handler_read_%';
392 EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5);
395 SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5);
396 SHOW STATUS LIKE 'Handler_read_%';
398 EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15);
401 SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15);
402 SHOW STATUS LIKE 'Handler_read_%';
404 EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15);
407 SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15);
408 SHOW STATUS LIKE 'Handler_read_%';
411 --error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
412 create table t1 (a int) partition by list ((a/3)*10 div 1)
413 (partition p0 values in (0), partition p1 values in (1));
416 # Bug #30695: An apostrophe ' in the comment of the ADD PARTITION causes the Server to crash.
418 # To verify the fix for crashing (on unix-type OS)
419 # uncomment the exec and error rows!
424 PARTITION BY RANGE( YEAR(d) ) (
425 PARTITION p0 VALUES LESS THAN (1960),
426 PARTITION p1 VALUES LESS THAN (1970),
427 PARTITION p2 VALUES LESS THAN (1980),
428 PARTITION p3 VALUES LESS THAN (1990)
431 ALTER TABLE t1 ADD PARTITION (
432 PARTITION `p5` VALUES LESS THAN (2010)
433 COMMENT 'APSTART \' APEND'
435 #--exec sed 's/APSTART \\/APSTART /' var/master-data/test/t1.frm > tmpt1.frm && mv tmpt1.frm var/master-data/test/t1.frm
436 #--error ER_PARSE_ERROR
437 SELECT * FROM t1 LIMIT 1;
442 # Bug 30878: crashing when alter an auto_increment non partitioned
443 # table to partitioned
445 create table t1 (id int auto_increment, s1 int, primary key (id));
447 insert into t1 values (null,1);
448 insert into t1 values (null,6);
453 alter table t1 partition by range (id) (
454 partition p0 values less than (3),
455 partition p1 values less than maxvalue
461 # Bug 15890: Strange number of partitions accepted
463 -- error ER_PARSE_ERROR
464 create table t1 (a int)
467 -- error ER_PARSE_ERROR
468 create table t1 (a int)
471 -- error ER_PARSE_ERROR
472 create table t1 (a int)
475 -- error ER_PARSE_ERROR
476 create table t1 (a int)
481 # Bug 19309 Partitions: Crash if double procedural alter
483 create table t1 (a int)
484 partition by list (a)
485 (partition p0 values in (1));
487 create procedure pz()
488 alter table t1 engine = myisam;
496 # BUG 16002: Handle unsigned integer functions properly
498 --error ER_PARSE_ERROR
499 create table t1 (a bigint)
500 partition by range (a)
501 (partition p0 values less than (0xFFFFFFFFFFFFFFFF),
502 partition p1 values less than (10));
503 --error ER_PARSE_ERROR
504 create table t1 (a bigint)
505 partition by list (a)
506 (partition p0 values in (0xFFFFFFFFFFFFFFFF),
507 partition p1 values in (10));
509 create table t1 (a bigint unsigned)
510 partition by range (a)
511 (partition p0 values less than (100),
512 partition p1 values less than MAXVALUE);
513 insert into t1 values (1);
516 create table t1 (a bigint unsigned)
517 partition by hash (a);
518 insert into t1 values (0xFFFFFFFFFFFFFFFD);
519 insert into t1 values (0xFFFFFFFFFFFFFFFE);
520 select * from t1 where (a + 1) < 10;
521 select * from t1 where (a + 1) > 10;
527 create table t1 (a int)
529 (partition p0 engine = MEMORY);
533 # BUG 19067 ALTER TABLE .. ADD PARTITION for subpartitioned table crashes
535 create table t1 (a int)
536 partition by range (a)
537 subpartition by key (a)
538 (partition p0 values less than (1));
539 alter table t1 add partition (partition p1 values less than (2));
540 show create table t1;
541 alter table t1 reorganize partition p1 into (partition p1 values less than (3));
542 show create table t1;
546 # Partition by key no partition defined => OK
553 partition by key (a);
556 # Bug 13323: Select count(*) on empty table returns 2
558 select count(*) from t1;
561 # Test SHOW CREATE TABLE
563 show create table t1;
567 # Partition by key no partition, list of fields
574 partition by key (a, b);
578 # Partition by key specified 3 partitions and defined 3 => ok
587 (partition x1, partition x2, partition x3);
591 # Partition by key specifying nodegroup
600 (partition x1 nodegroup 0,
601 partition x2 nodegroup 1,
602 partition x3 nodegroup 2);
606 # Partition by key specifying engine
615 (partition x1 engine myisam,
616 partition x2 engine myisam,
617 partition x3 engine myisam);
621 # Partition by key specifying tablespace
630 (partition x1 tablespace ts1,
631 partition x2 tablespace ts2,
632 partition x3 tablespace ts3);
634 CREATE TABLE t2 LIKE t1;
640 # Partition by key list, basic
647 partition by list (a)
649 (partition x1 values in (1,2,9,4) tablespace ts1,
650 partition x2 values in (3, 11, 5, 7) tablespace ts2,
651 partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
655 # Partition by key list, list function
662 partition by list (b*a)
664 (partition x1 values in (1,2,9,4) tablespace ts1,
665 partition x2 values in (3, 11, 5, 7) tablespace ts2,
666 partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
671 # Partition by key list, list function, no spec of #partitions
678 partition by list (b*a)
679 (partition x1 values in (1) tablespace ts1,
680 partition x2 values in (3, 11, 5, 7) tablespace ts2,
681 partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
686 # Bug 13154: Insert crashes due to bad calculation of partition id
687 # for PARTITION BY KEY and SUBPARTITION BY KEY
693 LOCK TABLES t1 WRITE;
694 insert into t1 values (1);
695 insert into t1 values (2);
696 insert into t1 values (3);
697 insert into t1 values (4);
703 # Bug #13644 DROP PARTITION NULL's DATE column
705 CREATE TABLE t1 (a int, name VARCHAR(50), purchased DATE)
706 PARTITION BY RANGE (a)
707 (PARTITION p0 VALUES LESS THAN (3),
708 PARTITION p1 VALUES LESS THAN (7),
709 PARTITION p2 VALUES LESS THAN (9),
710 PARTITION p3 VALUES LESS THAN (11));
711 INSERT INTO t1 VALUES
712 (1, 'desk organiser', '2003-10-15'),
713 (2, 'CD player', '1993-11-05'),
714 (3, 'TV set', '1996-03-10'),
715 (4, 'bookcase', '1982-01-10'),
716 (5, 'exercise bike', '2004-05-09'),
717 (6, 'sofa', '1987-06-05'),
718 (7, 'popcorn maker', '2001-11-22'),
719 (8, 'acquarium', '1992-08-04'),
720 (9, 'study desk', '1984-09-16'),
721 (10, 'lava lamp', '1998-12-25');
723 SELECT * from t1 ORDER BY a;
724 ALTER TABLE t1 DROP PARTITION p0;
725 SELECT * from t1 ORDER BY a;
730 # Bug #13442; Truncate Partitioned table doesn't work
733 CREATE TABLE t1 (a int)
734 PARTITION BY LIST (a)
735 (PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (4,5,6));
737 insert into t1 values (1),(2),(3),(4),(5),(6);
746 # Bug #13445 Partition by KEY method crashes server
748 CREATE TABLE t1 (a int, b int, primary key(a,b))
749 PARTITION BY KEY(b,a) PARTITIONS 4;
751 insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
752 select * from t1 where a = 4;
757 # Bug#22351 - handler::index_next_same() call to key_cmp_if_same()
758 # uses the wrong buffer
760 CREATE TABLE t1 (c1 INT, c2 INT, PRIMARY KEY USING BTREE (c1,c2)) ENGINE=MEMORY
761 PARTITION BY KEY(c2,c1) PARTITIONS 4;
762 INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
763 SELECT * FROM t1 WHERE c1 = 4;
767 # Bug #13438: Engine clause in PARTITION clause causes crash
769 CREATE TABLE t1 (a int)
770 PARTITION BY LIST (a)
772 (PARTITION x1 VALUES IN (1) ENGINE=MEMORY);
774 show create table t1;
778 # Bug #13440: REPLACE causes crash in partitioned table
780 CREATE TABLE t1 (a int, unique(a))
781 PARTITION BY LIST (a)
782 (PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20));
784 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
785 REPLACE t1 SET a = 4;
789 # Bug #14365: Crash if value too small in list partitioned table
791 CREATE TABLE t1 (a int)
792 PARTITION BY LIST (a)
793 (PARTITION x1 VALUES IN (2), PARTITION x2 VALUES IN (3));
795 insert into t1 values (2), (3);
796 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
797 insert into t1 values (4);
798 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
799 insert into t1 values (1);
803 # Bug 14327: PARTITIONS clause gets lost in SHOW CREATE TABLE
805 CREATE TABLE t1 (a int)
809 SHOW CREATE TABLE t1;
814 # Bug #13446: Update to value outside of list values doesn't give error
816 CREATE TABLE t1 (a int)
817 PARTITION BY RANGE (a)
818 (PARTITION x1 VALUES LESS THAN (2));
820 insert into t1 values (1);
821 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
827 # Bug #13441: Analyze on partitioned table didn't work
829 CREATE TABLE t1 (a int)
830 PARTITION BY LIST (a)
831 (PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20));
838 # BUG 15221 (Cannot reorganize with the same name)
842 partition by range (a)
843 ( partition p0 values less than(10),
844 partition p1 values less than (20),
845 partition p2 values less than (25));
847 alter table t1 reorganize partition p2 into (partition p2 values less than (30));
848 show create table t1;
851 CREATE TABLE t1 (a int, b int)
852 PARTITION BY RANGE (a)
853 (PARTITION x0 VALUES LESS THAN (2),
854 PARTITION x1 VALUES LESS THAN (4),
855 PARTITION x2 VALUES LESS THAN (6),
856 PARTITION x3 VALUES LESS THAN (8),
857 PARTITION x4 VALUES LESS THAN (10),
858 PARTITION x5 VALUES LESS THAN (12),
859 PARTITION x6 VALUES LESS THAN (14),
860 PARTITION x7 VALUES LESS THAN (16),
861 PARTITION x8 VALUES LESS THAN (18),
862 PARTITION x9 VALUES LESS THAN (20));
864 ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO
865 (PARTITION x1 VALUES LESS THAN (6));
866 show create table t1;
869 # Testcase for BUG#15819
870 create table t1 (a int not null, b int not null) partition by LIST (a+b) (
871 partition p0 values in (12),
872 partition p1 values in (14)
874 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
875 insert into t1 values (10,1);
880 # Bug#16901 Partitions: crash, SELECT, column of part.
881 # function=first column of primary key
883 create table t1 (f1 integer,f2 integer, f3 varchar(10), primary key(f1,f2))
884 partition by range(f1) subpartition by hash(f2) subpartitions 2
885 (partition p1 values less than (0),
886 partition p2 values less than (2),
887 partition p3 values less than (2147483647));
889 insert into t1 values(10,10,'10');
890 insert into t1 values(2,2,'2');
891 select * from t1 where f1 = 2;
895 # Bug #16907 Partitions: crash, SELECT goes into last partition, UNIQUE INDEX
897 create table t1 (f1 integer,f2 integer, unique index(f1))
898 partition by range(f1 div 2)
899 subpartition by hash(f1) subpartitions 2
900 (partition partb values less than (2),
901 partition parte values less than (4),
902 partition partf values less than (10000));
903 insert into t1 values(10,1);
904 select * from t1 where f1 = 10;
908 # Bug #16775: Wrong engine type stored for subpartition
910 set session storage_engine= 'memory';
911 create table t1 (f_int1 int(11) default null) engine = memory
912 partition by range (f_int1) subpartition by hash (f_int1)
913 (partition part1 values less than (1000)
914 (subpartition subpart11 engine = memory));
916 set session storage_engine='myisam';
919 # Bug #16782: Crash using REPLACE on table with primary key
921 create table t1 (f_int1 integer, f_int2 integer, primary key (f_int1))
922 partition by hash(f_int1) partitions 2;
923 insert into t1 values (1,1),(2,2);
924 replace into t1 values (1,1),(2,2);
928 # Bug #17169: Partitions: out of memory if add partition and unique
930 create table t1 (s1 int, unique (s1)) partition by list (s1) (partition x1 VALUES in (10), partition x2 values in (20));
931 alter table t1 add partition (partition x3 values in (30));
935 # Bug #17754 Change to explicit removal of partitioning scheme
936 # Also added a number of tests to ensure that proper engine is
937 # choosen in all kinds of scenarios.
940 create table t1 (a int)
943 (partition p0 engine=myisam, partition p1 engine=myisam);
944 show create table t1;
947 show create table t1;
949 alter table t1 engine=myisam;
950 show create table t1;
952 alter table t1 engine=heap;
953 show create table t1;
955 alter table t1 remove partitioning;
956 show create table t1;
960 create table t1 (a int)
964 (partition p0 engine=myisam, partition p1 engine=myisam);
965 show create table t1;
967 alter table t1 add column b int remove partitioning;
968 show create table t1;
973 (partition p0 engine=myisam, partition p1);
974 show create table t1;
979 (partition p0, partition p1 engine=heap);
980 show create table t1;
982 alter table t1 engine=myisam, add column c int remove partitioning;
983 show create table t1;
988 (partition p0, partition p1);
989 show create table t1;
993 (partition p0, partition p1);
994 show create table t1;
999 (partition p0, partition p1);
1000 show create table t1;
1002 # Since alter, it already have ENGINE=HEAP from before on table level
1006 (partition p0, partition p1 engine=heap);
1008 # Since alter, it already have ENGINE=HEAP from before on table level
1012 (partition p0 engine=heap, partition p1);
1014 --error ER_MIX_HANDLER_ERROR
1017 partition by key (a)
1018 (partition p0 engine=heap, partition p1 engine=myisam);
1020 --error ER_MIX_HANDLER_ERROR
1022 partition by key (a)
1023 (partition p0 engine=heap, partition p1 engine=myisam);
1027 # Bug #17432: Partition functions containing NULL values should return
1031 f_int1 INTEGER, f_int2 INTEGER,
1032 f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000)
1034 PARTITION BY RANGE(f_int1 DIV 2)
1035 SUBPARTITION BY HASH(f_int1)
1037 (PARTITION parta VALUES LESS THAN (0),
1038 PARTITION partb VALUES LESS THAN (5),
1039 PARTITION parte VALUES LESS THAN (10),
1040 PARTITION partf VALUES LESS THAN (2147483647));
1041 INSERT INTO t1 SET f_int1 = NULL , f_int2 = -20, f_char1 = CAST(-20 AS CHAR),
1042 f_char2 = CAST(-20 AS CHAR), f_charbig = '#NULL#';
1043 SELECT * FROM t1 WHERE f_int1 IS NULL;
1048 # Bug 17430: Crash when SELECT * from t1 where field IS NULL
1052 f_int1 INTEGER, f_int2 INTEGER,
1053 f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000) )
1054 PARTITION BY LIST(MOD(f_int1,2))
1055 SUBPARTITION BY KEY(f_int1)
1056 (PARTITION part1 VALUES IN (-1) (SUBPARTITION sp1, SUBPARTITION sp2),
1057 PARTITION part2 VALUES IN (0) (SUBPARTITION sp3, SUBPARTITION sp5),
1058 PARTITION part3 VALUES IN (1) (SUBPARTITION sp4, SUBPARTITION sp6));
1060 INSERT INTO t1 SET f_int1 = 2, f_int2 = 2, f_char1 = '2', f_char2 = '2', f_charbig = '===2===';
1061 INSERT INTO t1 SET f_int1 = 2, f_int2 = 2, f_char1 = '2', f_char2 = '2', f_charbig = '===2===';
1063 SELECT * FROM t1 WHERE f_int1 IS NULL;
1067 # Bug#14363 Partitions: failure if create in stored procedure
1071 create procedure p ()
1073 create table t1 (s1 mediumint,s2 mediumint)
1074 partition by list (s2)
1075 (partition p1 values in (0),
1076 partition p2 values in (1));
1083 create procedure p ()
1085 create table t1 (a int not null,b int not null,c int not null,primary key (a,b))
1086 partition by range (a)
1087 subpartition by hash (a+b)
1088 (partition x1 values less than (1)
1091 partition x2 values less than (5)
1102 # Bug #15447 Partitions: NULL is treated as zero
1105 # NULL for RANGE partition
1106 create table t1 (a int,b int,c int,key(a,b))
1107 partition by range (a)
1109 (partition x1 values less than (0) tablespace ts1,
1110 partition x2 values less than (10) tablespace ts2,
1111 partition x3 values less than maxvalue tablespace ts3);
1113 insert into t1 values (NULL, 1, 1);
1114 insert into t1 values (0, 1, 1);
1115 insert into t1 values (12, 1, 1);
1117 select partition_name, partition_description, table_rows
1118 from information_schema.partitions where table_schema ='test';
1121 # NULL for LIST partition
1122 --error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR
1123 create table t1 (a int,b int, c int)
1124 partition by list(a)
1126 (partition x123 values in (11,12),
1127 partition x234 values in (1 ,NULL, NULL));
1129 --error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR
1130 create table t1 (a int,b int, c int)
1131 partition by list(a)
1133 (partition x123 values in (11, NULL),
1134 partition x234 values in (1 ,NULL));
1136 create table t1 (a int,b int, c int)
1137 partition by list(a)
1139 (partition x123 values in (11, 12),
1140 partition x234 values in (5, 1));
1141 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
1142 insert into t1 values (NULL,1,1);
1145 create table t1 (a int,b int, c int)
1146 partition by list(a)
1148 (partition x123 values in (11, 12),
1149 partition x234 values in (NULL, 1));
1151 insert into t1 values (11,1,6);
1152 insert into t1 values (NULL,1,1);
1154 select partition_name, partition_description, table_rows
1155 from information_schema.partitions where table_schema ='test';
1159 # BUG 17947 Crash with REBUILD PARTITION
1161 create table t1 (a int)
1162 partition by list (a)
1163 (partition p0 values in (1));
1165 --error ER_PARSE_ERROR
1166 alter table t1 rebuild partition;
1171 # BUG 15253 Insert that should fail doesn't
1173 create table t1 (a int)
1174 partition by list (a)
1175 (partition p0 values in (5));
1177 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
1178 insert into t1 values (0);
1183 # BUG #16370 Subpartitions names not shown in SHOW CREATE TABLE output
1185 create table t1 (a int)
1186 partition by range (a) subpartition by hash (a)
1187 (partition p0 values less than (100));
1189 show create table t1;
1190 alter table t1 add partition (partition p1 values less than (200)
1191 (subpartition subpart21));
1193 show create table t1;
1197 create table t1 (a int)
1198 partition by key (a);
1200 show create table t1;
1201 alter table t1 add partition (partition p1);
1202 show create table t1;
1207 # BUG 15407 Crash with subpartition
1209 --error ER_PARSE_ERROR
1210 create table t1 (a int, b int)
1211 partition by range (a)
1212 subpartition by hash(a)
1213 (partition p0 values less than (0) (subpartition sp0),
1214 partition p1 values less than (1));
1216 --error ER_PARSE_ERROR
1217 create table t1 (a int, b int)
1218 partition by range (a)
1219 subpartition by hash(a)
1220 (partition p0 values less than (0),
1221 partition p1 values less than (1) (subpartition sp0));
1224 # Bug 46354 Crash with subpartition
1226 --error ER_PARSE_ERROR
1227 create table t1 (a int, b int)
1228 partition by list (a)
1229 subpartition by hash(a)
1230 (partition p0 values in (0),
1231 partition p1 values in (1) (subpartition sp0));
1235 # BUG 15961 No error when subpartition defined without subpartition by clause
1237 --error ER_SUBPARTITION_ERROR
1238 create table t1 (a int)
1239 partition by hash (a)
1240 (partition p0 (subpartition sp0));
1245 create table t1 (a int)
1246 partition by range (a)
1247 (partition p0 values less than (1));
1249 --error ER_PARTITION_WRONG_VALUES_ERROR
1250 alter table t1 add partition (partition p1 values in (2));
1251 --error ER_PARTITION_REQUIRES_VALUES_ERROR
1252 alter table t1 add partition (partition p1);
1256 create table t1 (a int)
1257 partition by list (a)
1258 (partition p0 values in (1));
1260 --error ER_PARTITION_WRONG_VALUES_ERROR
1261 alter table t1 add partition (partition p1 values less than (2));
1262 --error ER_PARTITION_REQUIRES_VALUES_ERROR
1263 alter table t1 add partition (partition p1);
1267 create table t1 (a int)
1268 partition by hash (a)
1271 --error ER_PARTITION_WRONG_VALUES_ERROR
1272 alter table t1 add partition (partition p1 values less than (2));
1273 --error ER_PARTITION_WRONG_VALUES_ERROR
1274 alter table t1 add partition (partition p1 values in (2));
1279 # BUG 17947 Crash with REBUILD PARTITION
1281 create table t1 (a int)
1282 partition by list (a)
1283 (partition p0 values in (1));
1285 --error ER_PARSE_ERROR
1286 alter table t1 rebuild partition;
1291 # Bug #14526: Partitions: indexed searches fail
1293 create table t2 (s1 int not null auto_increment, primary key (s1)) partition by list (s1) (partition p1 values in (1),partition p2 values in (2),partition p3 values in (3),partition p4 values in (4));
1294 insert into t2 values (null),(null),(null);
1296 select * from t2 where s1 < 2;
1297 update t2 set s1 = s1 + 1 order by s1 desc;
1298 select * from t2 where s1 < 3;
1299 select * from t2 where s1 = 2;
1303 # Bug #17497: Partitions: crash if add partition on temporary table
1305 --error ER_PARTITION_NO_TEMPORARY
1306 create temporary table t1 (a int) partition by hash(a);
1309 # Bug #17097: Partitions: failing ADD PRIMARY KEY leads to temporary rotten
1312 create table t1 (a int, b int) partition by list (a)
1313 (partition p1 values in (1), partition p2 values in (2));
1314 --error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
1315 alter table t1 add primary key (b);
1316 show create table t1;
1319 ############################################
1321 # Author: Mikael Ronstrom
1324 # Bug 17772: Crash at ALTER TABLE with rename
1325 # and add column + comment on
1328 ############################################
1329 create table t1 (a int unsigned not null auto_increment primary key)
1330 partition by key(a);
1331 alter table t1 rename t2, add c char(10), comment "no comment";
1332 show create table t2;
1337 # Bug#15336 Partitions: crash if create table as select
1339 create table t1 (f1 int) partition by hash (f1) as select 1;
1343 # bug #14350 Partitions: crash if prepared statement
1345 prepare stmt1 from 'create table t1 (s1 int) partition by hash (s1)';
1347 --error ER_TABLE_EXISTS_ERROR
1352 # bug 17290 SP with delete, create and rollback to save point causes MySQLD core
1355 eval CREATE PROCEDURE test.p1(IN i INT)
1357 DECLARE CONTINUE HANDLER FOR sqlexception BEGIN END;
1358 DROP TABLE IF EXISTS t1;
1359 CREATE TABLE t1 (num INT,PRIMARY KEY(num));
1361 INSERT INTO t1 VALUES(i);
1363 INSERT INTO t1 VALUES (14);
1364 ROLLBACK to savepoint t1_save;
1371 drop procedure test.p1;
1374 # Bug 13520: Problem with delimiters in COMMENT DATA DIRECTORY ..
1376 CREATE TABLE t1 (a int not null)
1378 (partition p0 COMMENT='first partition');
1382 # Bug 13433: Problem with delimited identifiers
1384 CREATE TABLE t1 (`a b` int not null)
1385 partition by key(`a b`);
1388 CREATE TABLE t1 (`a b` int not null)
1389 partition by hash(`a b`);
1393 # Bug#18053 Partitions: crash if null
1394 # Bug#18070 Partitions: wrong result on WHERE ... IS NULL
1396 create table t1 (f1 integer) partition by range(f1)
1397 (partition p1 values less than (0), partition p2 values less than (10));
1398 insert into t1 set f1 = null;
1399 select * from t1 where f1 is null;
1400 explain partitions select * from t1 where f1 is null;
1403 create table t1 (f1 integer) partition by list(f1)
1404 (partition p1 values in (1), partition p2 values in (null));
1405 insert into t1 set f1 = null;
1406 insert into t1 set f1 = 1;
1407 select * from t1 where f1 is null or f1 = 1;
1410 create table t1 (f1 smallint)
1411 partition by list (f1) (partition p0 values in (null));
1412 insert into t1 values (null);
1413 select * from t1 where f1 is null;
1414 select * from t1 where f1 < 1;
1415 select * from t1 where f1 <= NULL;
1416 select * from t1 where f1 < NULL;
1417 select * from t1 where f1 >= NULL;
1418 select * from t1 where f1 > NULL;
1419 select * from t1 where f1 > 1;
1422 create table t1 (f1 smallint)
1423 partition by range (f1) (partition p0 values less than (0));
1424 insert into t1 values (null);
1425 select * from t1 where f1 is null;
1428 create table t1 (f1 integer) partition by list(f1)
1430 partition p1 values in (1),
1431 partition p2 values in (NULL),
1432 partition p3 values in (2),
1433 partition p4 values in (3),
1434 partition p5 values in (4)
1437 insert into t1 values (1),(2),(3),(4),(null);
1438 select * from t1 where f1 < 3;
1439 explain partitions select * from t1 where f1 < 3;
1440 select * from t1 where f1 is null;
1441 explain partitions select * from t1 where f1 is null;
1444 create table t1 (f1 int) partition by list(f1 div 2)
1446 partition p1 values in (1),
1447 partition p2 values in (NULL),
1448 partition p3 values in (2),
1449 partition p4 values in (3),
1450 partition p5 values in (4)
1453 insert into t1 values (2),(4),(6),(8),(null);
1454 select * from t1 where f1 < 3;
1455 explain partitions select * from t1 where f1 < 3;
1456 select * from t1 where f1 is null;
1457 explain partitions select * from t1 where f1 is null;
1460 create table t1 (a int) partition by LIST(a) (
1461 partition pn values in (NULL),
1462 partition p0 values in (0),
1463 partition p1 values in (1),
1464 partition p2 values in (2)
1466 insert into t1 values (NULL),(0),(1),(2);
1467 select * from t1 where a is null or a < 2;
1468 explain partitions select * from t1 where a is null or a < 2;
1469 select * from t1 where a is null or a < 0 or a > 1;
1470 explain partitions select * from t1 where a is null or a < 0 or a > 1;
1474 #Bug# 17631 SHOW TABLE STATUS reports wrong engine
1476 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, name VARCHAR(20))
1477 ENGINE=MyISAM DEFAULT CHARSET=latin1
1478 PARTITION BY RANGE(id)
1479 (PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM,
1480 PARTITION p1 VALUES LESS THAN (20) ENGINE = MyISAM,
1481 PARTITION p2 VALUES LESS THAN (30) ENGINE = MyISAM);
1482 --replace_column 6 0 7 0 8 0 9 0 12 NULL 13 NULL 14 NULL
1487 #BUG 16002 Erroneus handling of unsigned partition functions
1489 --error ER_PARTITION_CONST_DOMAIN_ERROR
1490 create table t1 (a bigint unsigned)
1491 partition by list (a)
1492 (partition p0 values in (0-1));
1494 create table t1 (a bigint unsigned)
1495 partition by range (a)
1496 (partition p0 values less than (10));
1498 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
1499 insert into t1 values (0xFFFFFFFFFFFFFFFF);
1504 #BUG 18750 Problems with partition names
1506 create table t1 (a int)
1507 partition by list (a)
1508 (partition `s1 s2` values in (0));
1511 create table t1 (a int)
1512 partition by list (a)
1513 (partition `7` values in (0));
1516 --error ER_WRONG_PARTITION_NAME
1517 create table t1 (a int)
1518 partition by list (a)
1519 (partition `s1 s2 ` values in (0));
1521 --error ER_WRONG_PARTITION_NAME
1522 create table t1 (a int)
1523 partition by list (a)
1524 subpartition by hash (a)
1525 (partition p1 values in (0) (subpartition `p1 p2 `));
1528 # BUG 18752 SHOW CREATE TABLE doesn't show NULL value in SHOW CREATE TABLE
1530 CREATE TABLE t1 (a int)
1531 PARTITION BY LIST (a)
1532 (PARTITION p0 VALUES IN (NULL));
1533 SHOW CREATE TABLE t1;
1536 --error ER_PARSE_ERROR
1537 CREATE TABLE t1 (a int)
1538 PARTITION BY RANGE(a)
1539 (PARTITION p0 VALUES LESS THAN (NULL));
1542 # Bug#18753 Partitions: auto_increment fails
1544 create table t1 (s1 int auto_increment primary key)
1545 partition by list (s1)
1546 (partition p1 values in (1),
1547 partition p2 values in (2),
1548 partition p3 values in (3));
1549 insert into t1 values (null);
1550 insert into t1 values (null);
1551 insert into t1 values (null);
1552 select auto_increment from information_schema.tables where table_name='t1';
1557 # BUG 19140 Partitions: Create index for partitioned table crashes
1559 create table t1 (a int) engine=memory
1560 partition by key(a);
1561 insert into t1 values (1);
1562 create index inx1 on t1(a);
1566 # Bug 19695 Partitions: SHOW CREATE TABLE shows table options even when it
1569 create table t1 (a int)
1570 PARTITION BY KEY (a)
1572 set session sql_mode='no_table_options';
1573 show create table t1;
1574 set session sql_mode='';
1578 # BUG 19304 Partitions: MERGE handler not allowed in partitioned tables
1580 --error ER_PARTITION_MERGE_ERROR
1581 create table t1 (a int)
1582 partition by key (a)
1583 (partition p0 engine = MERGE);
1586 # BUG 19062 Partition clause ignored if CREATE TABLE ... AS SELECT ...;
1588 create table t1 (a varchar(1))
1589 partition by key (a)
1592 show create table t1;
1596 # BUG 19501 Partitions: SHOW TABLE STATUS shows wrong Data_free
1598 CREATE TABLE t1 (a int) ENGINE = MYISAM PARTITION BY KEY(a);
1599 INSERT into t1 values (1), (2);
1600 --replace_column 9 0 12 NULL 13 NULL 14 NULL
1602 DELETE from t1 where a = 1;
1603 --replace_column 9 0 12 NULL 13 NULL 14 NULL
1605 ALTER TABLE t1 OPTIMIZE PARTITION p0;
1606 --replace_column 12 NULL 13 NULL 14 NULL
1611 # BUG 19502: ENABLE/DISABLE Keys don't work for partitioned tables
1613 CREATE TABLE t1 (a int, index(a)) PARTITION BY KEY(a);
1614 ALTER TABLE t1 DISABLE KEYS;
1615 ALTER TABLE t1 ENABLE KEYS;
1619 # Bug 17455 Partitions: Wrong message and error when using Repair/Optimize
1620 # table on partitioned table
1621 # (added check/analyze for gcov of Bug#20129)
1622 create table t1 (a int)
1624 partition by key (a);
1634 #BUG 17138 Problem with stored procedure and analyze partition
1637 drop procedure if exists mysqltest_1;
1640 create table t1 (a int)
1641 partition by list (a)
1642 (partition p0 values in (0));
1644 insert into t1 values (0);
1647 create procedure mysqltest_1 ()
1650 declare continue handler for sqlexception begin end;
1651 update ignore t1 set a = 1 where a = 0;
1653 prepare stmt1 from 'alter table t1';
1657 call mysqltest_1()//
1660 drop procedure mysqltest_1;
1663 # Bug 20583 Partitions: Crash using index_last
1665 create table t1 (a int, index(a))
1666 partition by hash(a);
1667 insert into t1 values (1),(2);
1668 select * from t1 ORDER BY a DESC;
1672 # Bug 21388: Bigint fails to find record
1674 create table t1 (a bigint unsigned not null, primary key(a))
1676 partition by key (a)
1679 show create table t1;
1680 insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE),
1681 (18446744073709551613), (18446744073709551612);
1683 select * from t1 where a = 18446744073709551615;
1684 delete from t1 where a = 18446744073709551615;
1689 # Bug 24502 reorganize partition closes connection
1692 num int(11) NOT NULL, cs int(11) NOT NULL)
1693 PARTITION BY RANGE (num) SUBPARTITION BY HASH (
1694 cs) SUBPARTITIONS 2 (PARTITION p_X VALUES LESS THAN MAXVALUE);
1697 REORGANIZE PARTITION p_X INTO (
1698 PARTITION p_100 VALUES LESS THAN (100),
1699 PARTITION p_X VALUES LESS THAN MAXVALUE
1705 # Bug #24186 (nested query across partitions returns fewer records)
1709 taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
1710 id int(11) NOT NULL DEFAULT '0',
1711 PRIMARY KEY (id,taken),
1713 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1715 INSERT INTO t2 VALUES
1716 ('2006-09-27 21:50:01',16421),
1717 ('2006-10-02 21:50:01',16421),
1718 ('2006-09-27 21:50:01',19092),
1719 ('2006-09-28 21:50:01',19092),
1720 ('2006-09-29 21:50:01',19092),
1721 ('2006-09-30 21:50:01',19092),
1722 ('2006-10-01 21:50:01',19092),
1723 ('2006-10-02 21:50:01',19092),
1724 ('2006-09-27 21:50:01',22589),
1725 ('2006-09-29 21:50:01',22589);
1730 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1732 INSERT INTO t1 VALUES
1738 taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
1739 id int(11) NOT NULL DEFAULT '0',
1740 PRIMARY KEY (id,taken),
1742 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
1743 PARTITION BY RANGE (to_days(taken))
1745 PARTITION p01 VALUES LESS THAN (732920) ,
1746 PARTITION p02 VALUES LESS THAN (732950) ,
1747 PARTITION p03 VALUES LESS THAN MAXVALUE ) ;
1749 INSERT INTO t4 select * from t2;
1751 set @f_date='2006-09-28';
1752 set @t_date='2006-10-02';
1754 SELECT t1.id AS MyISAM_part
1759 WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY))
1763 drop table t1, t2, t4;
1766 taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
1767 id int(11) NOT NULL DEFAULT '0',
1768 status varchar(20) NOT NULL DEFAULT '',
1769 PRIMARY KEY (id,taken)
1770 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
1771 PARTITION BY RANGE (to_days(taken))
1773 PARTITION p15 VALUES LESS THAN (732950) ,
1774 PARTITION p16 VALUES LESS THAN MAXVALUE ) ;
1777 INSERT INTO t1 VALUES
1778 ('2006-09-27 21:50:01',22589,'Open'),
1779 ('2006-09-29 21:50:01',22589,'Verified');
1781 DROP TABLE IF EXISTS t2;
1784 severity tinyint(4) NOT NULL DEFAULT '0',
1785 priority tinyint(4) NOT NULL DEFAULT '0',
1786 status varchar(20) DEFAULT NULL,
1787 alien tinyint(4) NOT NULL
1788 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1790 INSERT INTO t2 VALUES
1791 (22589,1,1,'Need Feedback',0);
1793 SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified');
1798 # Bug #27123 partition + on duplicate key update + varchar = Can't find record in <table>
1800 create table t1 (c1 varchar(255),c2 tinyint,primary key(c1))
1801 partition by key (c1) partitions 10 ;
1802 insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1;
1803 insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1;
1808 # Bug #28005 Partitions: can't use -9223372036854775808
1811 create table t1 (s1 bigint) partition by list (s1) (partition p1 values in (-9223372036854775808));
1815 # Bug #28806: Running SHOW TABLE STATUS during high INSERT load crashes server
1817 create table t1(a int auto_increment, b int, primary key (b, a))
1818 partition by hash(b) partitions 2;
1819 insert into t1 values (null, 1);
1820 --replace_column 9 0 12 NULL 13 NULL 14 NULL
1824 create table t1(a int auto_increment primary key)
1825 partition by key(a) partitions 2;
1826 insert into t1 values (null), (null), (null);
1827 --replace_column 9 0 12 NULL 13 NULL 14 NULL
1830 # Bug #28488: Incorrect information in file: './test/t1_test#.frm'
1833 CREATE TABLE t1(a INT NOT NULL, b TINYBLOB, KEY(a))
1834 PARTITION BY RANGE(a) ( PARTITION p0 VALUES LESS THAN (32));
1835 INSERT INTO t1 VALUES (1, REPEAT('a', 10));
1836 INSERT INTO t1 SELECT a + 1, b FROM t1;
1837 INSERT INTO t1 SELECT a + 2, b FROM t1;
1838 INSERT INTO t1 SELECT a + 4, b FROM t1;
1839 INSERT INTO t1 SELECT a + 8, b FROM t1;
1841 ALTER TABLE t1 ADD PARTITION (PARTITION p1 VALUES LESS THAN (64));
1842 ALTER TABLE t1 DROP PARTITION p1;
1847 # Bug #30484: Partitions: crash with self-referencing trigger
1850 create table t (s1 int) engine=myisam partition by key (s1);
1851 create trigger t_ad after delete on t for each row insert into t values (old.s1);
1852 insert into t values (1);
1856 # Bug #27084 partitioning by list seems failing when using case
1857 # BUG #18198: Case no longer supported, test case removed
1860 create table t2 (b int);
1861 --error ER_BAD_FIELD_ERROR
1862 create table t1 (b int)
1863 PARTITION BY RANGE (t2.b) (
1864 PARTITION p1 VALUES LESS THAN (10),
1865 PARTITION p2 VALUES LESS THAN (20)
1867 create table t1 (a int)
1868 PARTITION BY RANGE (b) (
1869 PARTITION p1 VALUES LESS THAN (10),
1870 PARTITION p2 VALUES LESS THAN (20)
1872 show create table t1;
1876 # Bug #32067 Partitions: crash with timestamp column
1877 # this bug occurs randomly on some UPDATE statement
1878 # with the '1032: Can't find record in 't1'' error
1881 (s1 timestamp on update current_timestamp, s2 int)
1882 partition by key(s1) partitions 3;
1884 insert into t1 values (null,null);
1889 update t1 set s2 = 1;
1890 update t1 set s2 = 2;
1898 # BUG#32772: partition crash 1: enum column
1900 # Note that month(int_col) is disallowed after bug#54483.
1907 ) engine=myisam partition by hash (c0) partitions 5;
1910 insert ignore into t1 set c0 = -6502262, c1 = 3992917, c2 = 35019;
1911 insert ignore into t1 set c0 = 241221, c1 = -6862346, c2 = 56644;
1913 # This must not fail assert:
1914 select c1 from t1 group by (select c0 from t1 limit 1);
1917 # Bug #30495: optimize table t1,t2,t3 extended errors
1918 # (added more maintenace commands for Bug#20129
1919 CREATE TABLE t1(a int)
1920 PARTITION BY RANGE (a) (
1921 PARTITION p1 VALUES LESS THAN (10),
1922 PARTITION p2 VALUES LESS THAN (20)
1924 --error ER_PARSE_ERROR
1925 ALTER TABLE t1 OPTIMIZE PARTITION p1 EXTENDED;
1926 --error ER_PARSE_ERROR
1927 ALTER TABLE t1 ANALYZE PARTITION p1 EXTENDED;
1928 ALTER TABLE t1 ANALYZE PARTITION p1;
1929 ALTER TABLE t1 CHECK PARTITION p1;
1930 ALTER TABLE t1 REPAIR PARTITION p1;
1931 ALTER TABLE t1 OPTIMIZE PARTITION p1;
1935 # Bug #29258: Partitions: search fails for maximum unsigned bigint
1937 CREATE TABLE t1 (s1 BIGINT UNSIGNED)
1938 PARTITION BY RANGE (s1) (
1939 PARTITION p0 VALUES LESS THAN (0),
1940 PARTITION p1 VALUES LESS THAN (1),
1941 PARTITION p2 VALUES LESS THAN (18446744073709551615)
1943 INSERT INTO t1 VALUES (0), (18446744073709551614);
1944 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
1945 INSERT INTO t1 VALUES (18446744073709551615);
1948 CREATE TABLE t1 (s1 BIGINT UNSIGNED)
1949 PARTITION BY RANGE (s1) (
1950 PARTITION p0 VALUES LESS THAN (0),
1951 PARTITION p1 VALUES LESS THAN (1),
1952 PARTITION p2 VALUES LESS THAN (18446744073709551614),
1953 PARTITION p3 VALUES LESS THAN MAXVALUE
1955 INSERT INTO t1 VALUES (-1), (0), (18446744073709551613),
1956 (18446744073709551614), (18446744073709551615);
1958 SELECT * FROM t1 WHERE s1 = 0;
1959 SELECT * FROM t1 WHERE s1 = 18446744073709551614;
1960 SELECT * FROM t1 WHERE s1 = 18446744073709551615;
1963 CREATE TABLE t1 (s1 BIGINT UNSIGNED)
1964 PARTITION BY RANGE (s1) (
1965 PARTITION p0 VALUES LESS THAN (0),
1966 PARTITION p1 VALUES LESS THAN (1),
1967 PARTITION p2 VALUES LESS THAN (18446744073709551615),
1968 PARTITION p3 VALUES LESS THAN MAXVALUE
1973 # Bug #31890 Partitions: ORDER BY DESC in InnoDB not working
1977 (int_column INT, char_column CHAR(5),
1978 PRIMARY KEY(char_column,int_column))
1979 PARTITION BY KEY(char_column,int_column)
1981 INSERT INTO t1 (int_column, char_column) VALUES
1995 SELECT * FROM t1 ORDER BY char_column DESC;
1999 # Bug #32247 Test reports wrong value of "AUTO_INCREMENT" (on a partitioned InnoDB table)
2002 CREATE TABLE t1(id MEDIUMINT NOT NULL AUTO_INCREMENT,
2003 user CHAR(25), PRIMARY KEY(id))
2004 PARTITION BY RANGE(id)
2005 SUBPARTITION BY hash(id) subpartitions 2
2006 (PARTITION pa1 values less than (10),
2007 PARTITION pa2 values less than (20),
2008 PARTITION pa11 values less than MAXVALUE);
2013 insert into t1 (user) values ('mysql');
2017 show create table t1;
2021 # Bug #38272 timestamps fields incorrectly defaulted on update accross partitions.
2025 `ID` bigint(20) NOT NULL AUTO_INCREMENT,
2026 `createdDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2028 PRIMARY KEY (`ID`, number)
2030 PARTITION BY RANGE (number) (
2031 PARTITION p0 VALUES LESS THAN (6),
2032 PARTITION p1 VALUES LESS THAN (11)
2037 `createdDate` TIMESTAMP,
2041 INSERT INTO t1 SET number=1;
2042 insert into t2 select * from t1;
2044 UPDATE t1 SET number=6;
2045 select count(*) from t1, t2 where t1.createdDate = t2.createdDate;
2050 # Bug #38083 Error-causing row inserted into partitioned table despite error
2052 SET @orig_sql_mode = @@SQL_MODE;
2053 SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
2054 CREATE TABLE t1 (c1 INT)
2055 PARTITION BY LIST(1 DIV c1) (
2056 PARTITION p0 VALUES IN (NULL),
2057 PARTITION p1 VALUES IN (1)
2060 -- error ER_DIVISION_BY_ZERO
2061 INSERT INTO t1 VALUES (0);
2064 -- error ER_DIVISION_BY_ZERO
2065 INSERT INTO t1 VALUES (NULL), (0), (1), (2);
2068 SET SQL_MODE= @orig_sql_mode;
2073 # Bug #38005 Partitions: error with insert select
2076 create table t1 (s1 int) partition by hash(s1) partitions 2;
2077 create index i on t1 (s1);
2078 insert into t1 values (1);
2079 insert into t1 select s1 from t1;
2080 insert into t1 select s1 from t1;
2081 insert into t1 select s1 from t1 order by s1 desc;
2085 create table t1 (s1 int) partition by range(s1)
2086 (partition pa1 values less than (10),
2087 partition pa2 values less than MAXVALUE);
2088 create index i on t1 (s1);
2089 insert into t1 values (1);
2090 insert into t1 select s1 from t1;
2091 insert into t1 select s1 from t1;
2092 insert into t1 select s1 from t1 order by s1 desc;
2096 create table t1 (s1 int) partition by range(s1)
2097 (partition pa1 values less than (10),
2098 partition pa2 values less than MAXVALUE);
2099 create index i on t1 (s1);
2100 insert into t1 values (20);
2101 insert into t1 select s1 from t1;
2102 insert into t1 select s1 from t1;
2103 insert into t1 select s1 from t1 order by s1 desc;
2107 create table t1 (s1 int) partition by range(s1)
2108 (partition pa1 values less than (10),
2109 partition pa2 values less than MAXVALUE);
2110 create index i on t1 (s1);
2111 insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8);
2112 insert into t1 select s1 from t1;
2113 insert into t1 select s1 from t1;
2114 insert into t1 select s1 from t1;
2115 insert into t1 select s1 from t1;
2116 insert into t1 select s1 from t1 order by s1 desc;
2117 insert into t1 select s1 from t1 where s1=3;
2118 select count(*) from t1;
2123 --echo # Bug#42944: partition not pruned correctly
2125 CREATE TABLE t1 (a int) PARTITION BY RANGE (a)
2126 (PARTITION p0 VALUES LESS THAN (100),
2127 PARTITION p1 VALUES LESS THAN (200),
2128 PARTITION p2 VALUES LESS THAN (300),
2129 PARTITION p3 VALUES LESS THAN MAXVALUE);
2130 INSERT INTO t1 VALUES (10), (100), (200), (300), (400);
2131 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a>=200;
2135 # Bug#44821: select distinct on partitioned table returns wrong results
2137 CREATE TABLE t1 ( a INT, b INT, c INT, KEY bc(b, c) )
2138 PARTITION BY KEY (a, b) PARTITIONS 3
2141 INSERT INTO t1 VALUES
2168 SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c;
2171 SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c;
2176 --echo # Bug #45807: crash accessing partitioned table and sql_mode
2177 --echo # contains ONLY_FULL_GROUP_BY
2178 --echo # Bug#46923: select count(*) from partitioned table fails with
2179 --echo # ONLY_FULL_GROUP_BY
2182 SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY';
2183 CREATE TABLE t1(id INT,KEY(id)) ENGINE=MYISAM
2184 PARTITION BY HASH(id) PARTITIONS 2;
2185 SELECT COUNT(*) FROM t1;
2187 SET SESSION SQL_MODE=DEFAULT;
2191 --echo # BUG#45816 - assertion failure with index containing double
2192 --echo # column on partitioned table
2197 b DOUBLE DEFAULT NULL,
2200 ) PARTITION BY HASH(c) PARTITIONS 3;
2202 INSERT INTO t1 VALUES (6,8,9);
2203 INSERT INTO t1 VALUES (6,8,10);
2205 SELECT 1 FROM t1 JOIN t1 AS t2 USING (a) FOR UPDATE;
2210 --echo # BUG#51868 - crash with myisam_use_mmap and partitioned myisam tables
2212 SET GLOBAL myisam_use_mmap=1;
2213 CREATE TABLE t1(a INT) PARTITION BY HASH(a) PARTITIONS 1;
2214 INSERT INTO t1 VALUES(0);
2217 INSERT INTO t1 VALUES(0);
2219 SET GLOBAL myisam_use_mmap=default;
2222 --echo # Bug#13580775 ASSERTION FAILED: RECORD_LENGTH == M_RECORD_LENGTH,
2223 --echo # FILE FILESORT_UTILS.CC
2232 ) PARTITION BY KEY () PARTITIONS 1;
2234 INSERT INTO t1 VALUES (1,1,'a',1), (2,2,'a',1);
2236 SELECT 1 FROM t1 WHERE 1 IN
2237 (SELECT group_concat(b)
2239 WHERE c > geomfromtext('point(1 1)')
2246 --echo # Bug#13011410 CRASH IN FILESORT CODE WITH GROUP BY/ROLLUP
2252 c VARCHAR(300) CHARACTER SET hp8 COLLATE hp8_bin,
2253 PRIMARY KEY (a,c(299)))
2255 PARTITION BY LINEAR KEY () PARTITIONS 2;
2257 INSERT INTO t1 VALUES (1,2,'test'), (2,3,'hi'), (4,5,'bye');
2258 SELECT 1 FROM t1 WHERE b < SOME
2259 ( SELECT 1 FROM t1 WHERE a >= 1
2260 GROUP BY b WITH ROLLUP
2261 HAVING b > geomfromtext("")
2266 --echo End of 5.1 tests