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
# Bug#48276: can't add column if subpartition exists
19
CREATE TABLE t1 (a INT, b INT)
21
SUBPARTITION BY HASH (b)
22
(PARTITION p1 VALUES IN (1));
23
ALTER TABLE t1 ADD COLUMN c INT;
27
# Bug#46639: 1030 (HY000): Got error 124 from storage engine on
28
# INSERT ... SELECT ...
38
PARTITION BY HASH(a) PARTITIONS 2;
40
INSERT INTO t1 VALUES (399, 22);
41
INSERT INTO t2 VALUES (1, 22), (1, 42);
43
INSERT INTO t2 SELECT 1, 399 FROM t2, t1
49
# Bug#46478: timestamp field incorrectly defaulted when partition is reorganized
52
a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
56
PARTITION BY RANGE (UNIX_TIMESTAMP(a)) (
57
PARTITION p1 VALUES LESS THAN (1199134800),
58
PARTITION pmax VALUES LESS THAN MAXVALUE
61
INSERT INTO t1 VALUES ('2007-07-30 17:35:48', 'p1');
62
INSERT INTO t1 VALUES ('2009-07-14 17:35:55', 'pmax');
63
INSERT INTO t1 VALUES ('2009-09-21 17:31:42', 'pmax');
66
ALTER TABLE t1 REORGANIZE PARTITION pmax INTO (
67
PARTITION p3 VALUES LESS THAN (1247688000),
68
PARTITION pmax VALUES LESS THAN MAXVALUE);
74
# Bug#45904: Error when CHARSET=utf8 and subpartitioning
76
create table t1 (a int NOT NULL, b varchar(5) NOT NULL)
79
subpartition by key (b)
80
(partition p0 values in (1),
81
partition p1 values in (2));
85
# Bug#44059: rec_per_key on empty partition gives weird optimiser results
87
create table t1 (a int, b int, key(a))
89
( partition p0 values in (1),
90
partition p1 values in (2));
91
insert into t1 values (1,1),(2,1),(2,2),(2,3);
98
# Bug#36001: Partitions: spelling and using some error messages
100
--error ER_FOREIGN_KEY_ON_PARTITIONED
101
CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a))
103
PARTITION BY HASH (a);
106
# Bug#40954: Crash if range search and order by.
109
pk INT NOT NULL AUTO_INCREMENT,
112
/*!50100 PARTITION BY HASH (pk)
114
INSERT INTO t1 VALUES (NULL);
115
INSERT INTO t1 VALUES (NULL);
116
INSERT INTO t1 VALUES (NULL);
117
SELECT * FROM t1 WHERE pk < 0 ORDER BY pk;
121
# Bug#40494: Crash MYSQL server crashes on range access with partitioning
124
CREATE TABLE t1 (a INT NOT NULL, KEY(a))
125
PARTITION BY RANGE(a)
126
(PARTITION p1 VALUES LESS THAN (200), PARTITION pmax VALUES LESS THAN MAXVALUE);
127
INSERT INTO t1 VALUES (2), (40), (40), (70), (60), (90), (199);
128
SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a ASC;
130
SELECT a FROM t1 WHERE a BETWEEN 60 AND 95;
131
INSERT INTO t1 VALUES (200), (250), (210);
132
SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a ASC;
133
SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a ASC;
134
SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a DESC;
135
SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a DESC;
136
SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a DESC;
138
SELECT a FROM t1 WHERE a BETWEEN 60 AND 220;
140
SELECT a FROM t1 WHERE a BETWEEN 200 AND 220;
142
SELECT a FROM t1 WHERE a BETWEEN 60 AND 95;
144
SELECT a FROM t1 WHERE a BETWEEN 60 AND 220;
146
SELECT a FROM t1 WHERE a BETWEEN 200 AND 220;
150
# Bug35931: Index search may return duplicates
154
b MEDIUMINT NOT NULL,
158
PARTITION BY LIST (a) (
159
PARTITION p0 VALUES IN (1)
161
INSERT INTO t1 VALUES (1,1,0), (1,1,1), (1,1,2), (1,1,53), (1,1,4), (1,1,5),
162
(1,1,6), (1,1,7), (1,1,8), (1,1,9), (1,1,10), (1,1,11), (1,1,12), (1,1,13),
163
(1,1,14), (1,1,15), (1,1,16), (1,1,67), (1,1,18), (1,1,19), (1,1,20), (1,1,21),
164
(1,1,22), (1,1,23), (1,1,24), (1,1,75), (1,1,26), (1,1,27), (1,1,128),
165
(1,1,79), (1,1,30), (1,1,31), (1,1,32), (1,1,33), (1,1,34), (1,1,85), (1,1,36),
166
(1,1,37), (1,1,38), (1,1,39), (1,1,40), (1,1,241), (1,1,42), (1,1,43),
167
(1,1,44), (1,1,45), (1,1,46), (1,1,147), (1,1,48), (1,1,49), (1,2,0), (1,2,1),
168
(1,2,2), (1,2,3), (1,2,4), (1,2,5), (1,2,6), (1,2,7), (1,2,8), (1,2,9),
169
(1,2,10), (1,2,11), (1,2,12), (1,2,13), (1,2,14), (1,2,15), (1,2,16), (1,2,17),
170
(1,2,18), (1,2,19), (1,2,20), (1,2,21), (1,2,22), (1,2,23), (1,2,24), (1,2,25),
171
(1,2,26), (1,2,27), (1,2,28), (1,2,29), (1,2,30), (1,2,31), (1,2,32), (1,2,33),
172
(1,2,34), (1,2,35), (1,2,36), (1,2,37), (1,2,38), (1,2,39), (1,2,40), (1,2,41),
173
(1,2,42), (1,2,43), (1,2,44), (1,2,45), (1,2,46), (1,2,47), (1,2,48), (1,2,49),
174
(1,6,0), (1,6,1), (1,6,2), (1,6,3), (1,6,4), (1,6,5), (1,6,6), (1,6,7),
175
(1,6,8), (1,6,9), (1,6,10), (1,6,11), (1,6,12), (1,6,13), (1,6,14), (1,6,15),
176
(1,6,16), (1,6,17), (1,6,18), (1,6,19), (1,6,20), (1,6,21), (1,6,22), (1,6,23),
177
(1,6,24), (1,6,25), (1,6,26), (1,6,27), (1,6,28), (1,6,29), (1,6,30), (1,6,31),
178
(1,6,32), (1,6,33), (1,6,34), (1,6,35), (1,6,36), (1,6,37), (1,6,38), (1,6,39),
179
(1,6,40), (1,6,41), (1,6,42), (1,6,43), (1,6,44), (1,6,45), (1,6,46), (1,6,47),
180
(1,6,48), (1,6,49), (1,7,0), (1,7,1), (1,7,2), (1,7,3), (1,7,4), (1,7,5),
181
(1,7,6), (1,7,7), (1,7,8), (1,7,9), (1,7,10), (1,7,11), (1,7,12), (1,7,13),
182
(1,7,14), (1,7,15), (1,7,16), (1,7,17), (1,7,18), (1,7,19), (1,7,20), (1,7,21),
183
(1,7,22), (1,7,23), (1,7,24), (1,7,25), (1,7,26), (1,7,27), (1,7,28), (1,7,29),
184
(1,7,30), (1,7,31), (1,7,32), (1,7,33), (1,7,34), (1,7,35), (1,7,38), (1,7,39),
185
(1,7,90), (1,7,41), (1,7,43), (1,7,48), (1,7,49), (1,9,0), (1,9,1), (1,9,2),
186
(1,9,3), (1,9,4), (1,9,5), (1,9,6), (1,9,7), (1,9,8), (1,9,9), (1,9,10),
187
(1,9,11), (1,9,12), (1,9,13), (1,9,14), (1,9,15), (1,9,16), (1,9,17), (1,9,18),
188
(1,9,19), (1,9,20), (1,9,21), (1,9,22), (1,9,23), (1,9,24), (1,9,25), (1,9,26),
189
(1,9,29), (1,9,32), (1,9,35), (1,9,38), (1,10,0), (1,10,1), (1,10,2), (1,10,3),
190
(1,10,4), (1,10,5), (1,10,6), (1,10,7), (1,10,8), (1,10,9), (1,10,10),
191
(1,10,11), (1,10,13), (1,10,14), (1,10,15), (1,10,16), (1,10,17), (1,10,18),
192
(1,10,22), (1,10,24), (1,10,25), (1,10,26), (1,10,28), (1,10,131), (1,10,33),
193
(1,10,84), (1,10,35), (1,10,40), (1,10,42), (1,10,49), (1,11,0), (1,11,1),
194
(1,11,2), (1,11,3), (1,11,4), (1,11,5), (1,11,6), (1,11,7), (1,11,8), (1,11,9),
195
(1,11,10), (1,11,11), (1,11,12), (1,11,13), (1,11,14), (1,11,15), (1,11,16),
196
(1,11,17), (1,11,18), (1,11,19), (1,11,20), (1,11,21), (1,11,22), (1,11,23),
197
(1,11,24), (1,11,25), (1,11,26), (1,11,27), (1,11,28), (1,11,30), (1,11,31),
198
(1,11,32), (1,11,33), (1,11,34), (1,11,35), (1,11,37), (1,11,39), (1,11,40),
199
(1,11,42), (1,11,44), (1,11,45), (1,11,47), (1,11,48), (1,14,104), (1,14,58),
200
(1,14,12), (1,14,13), (1,14,15), (1,14,16), (1,14,17), (1,14,34), (1,15,0),
201
(1,15,1), (1,15,2), (1,15,3), (1,15,4), (1,15,5), (1,15,7), (1,15,9),
202
(1,15,15), (1,15,27), (1,15,49), (1,16,0), (1,16,1), (1,16,3), (1,17,4),
204
SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
205
SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
206
ALTER TABLE t1 DROP INDEX b;
207
SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
208
SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
209
ALTER TABLE t1 ENGINE = Memory;
210
SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
211
SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
212
ALTER TABLE t1 ADD INDEX b USING HASH (b);
213
SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
214
SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
217
# Bug#37327 Range scan on partitioned table returns duplicate rows
218
# (Duplicate of Bug#35931)
220
`c1` int(11) DEFAULT NULL,
222
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
225
`c1` int(11) DEFAULT NULL,
227
) 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) */;
229
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
230
INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
232
EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
235
SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
236
SHOW STATUS LIKE 'Handler_read_%';
238
EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
241
SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
242
SHOW STATUS LIKE 'Handler_read_%';
245
# Bug#37329 Range scan on partitioned tables shows higher Handler_read_next
246
# (marked as duplicate of Bug#35931)
248
`c1` int(11) DEFAULT NULL,
250
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
253
`c1` int(11) DEFAULT NULL,
255
) ENGINE=MyISAM DEFAULT CHARSET=latin1
256
/*!50100 PARTITION BY RANGE (c1)
257
(PARTITION a VALUES LESS THAN (100) ENGINE = MyISAM,
258
PARTITION b VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;
260
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
261
INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
263
EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5);
266
SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5);
267
SHOW STATUS LIKE 'Handler_read_%';
269
EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5);
272
SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5);
273
SHOW STATUS LIKE 'Handler_read_%';
275
EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15);
278
SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15);
279
SHOW STATUS LIKE 'Handler_read_%';
281
EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15);
284
SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15);
285
SHOW STATUS LIKE 'Handler_read_%';
288
--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
289
create table t1 (a int) partition by list ((a/3)*10 div 1)
290
(partition p0 values in (0), partition p1 values in (1));
293
# Bug #30695: An apostrophe ' in the comment of the ADD PARTITION causes the Server to crash.
295
# To verify the fix for crashing (on unix-type OS)
296
# uncomment the exec and error rows!
301
PARTITION BY RANGE( YEAR(d) ) (
302
PARTITION p0 VALUES LESS THAN (1960),
303
PARTITION p1 VALUES LESS THAN (1970),
304
PARTITION p2 VALUES LESS THAN (1980),
305
PARTITION p3 VALUES LESS THAN (1990)
308
ALTER TABLE t1 ADD PARTITION (
309
PARTITION `p5` VALUES LESS THAN (2010)
310
COMMENT 'APSTART \' APEND'
312
#--exec sed 's/APSTART \\/APSTART /' var/master-data/test/t1.frm > tmpt1.frm && mv tmpt1.frm var/master-data/test/t1.frm
313
#--error ER_PARSE_ERROR
314
SELECT * FROM t1 LIMIT 1;
319
# Bug 30878: crashing when alter an auto_increment non partitioned
320
# table to partitioned
322
create table t1 (id int auto_increment, s1 int, primary key (id));
324
insert into t1 values (null,1);
325
insert into t1 values (null,6);
330
alter table t1 partition by range (id) (
331
partition p0 values less than (3),
332
partition p1 values less than maxvalue
338
# Bug 15890: Strange number of partitions accepted
340
-- error ER_PARSE_ERROR
341
create table t1 (a int)
344
-- error ER_PARSE_ERROR
345
create table t1 (a int)
348
-- error ER_PARSE_ERROR
349
create table t1 (a int)
352
-- error ER_PARSE_ERROR
353
create table t1 (a int)
358
# Bug 19309 Partitions: Crash if double procedural alter
360
create table t1 (a int)
361
partition by list (a)
362
(partition p0 values in (1));
364
create procedure pz()
365
alter table t1 engine = myisam;
373
# BUG 16002: Handle unsigned integer functions properly
375
--error ER_PARSE_ERROR
376
create table t1 (a bigint)
377
partition by range (a)
378
(partition p0 values less than (0xFFFFFFFFFFFFFFFF),
379
partition p1 values less than (10));
380
--error ER_PARSE_ERROR
381
create table t1 (a bigint)
382
partition by list (a)
383
(partition p0 values in (0xFFFFFFFFFFFFFFFF),
384
partition p1 values in (10));
386
create table t1 (a bigint unsigned)
387
partition by range (a)
388
(partition p0 values less than (100),
389
partition p1 values less than MAXVALUE);
390
insert into t1 values (1);
393
create table t1 (a bigint unsigned)
394
partition by hash (a);
395
insert into t1 values (0xFFFFFFFFFFFFFFFD);
396
insert into t1 values (0xFFFFFFFFFFFFFFFE);
397
select * from t1 where (a + 1) < 10;
398
select * from t1 where (a + 1) > 10;
404
create table t1 (a int)
406
(partition p0 engine = MEMORY);
410
# BUG 19067 ALTER TABLE .. ADD PARTITION for subpartitioned table crashes
412
create table t1 (a int)
413
partition by range (a)
414
subpartition by key (a)
415
(partition p0 values less than (1));
416
alter table t1 add partition (partition p1 values less than (2));
417
show create table t1;
418
alter table t1 reorganize partition p1 into (partition p1 values less than (3));
419
show create table t1;
423
# Partition by key no partition defined => OK
430
partition by key (a);
433
# Bug 13323: Select count(*) on empty table returns 2
435
select count(*) from t1;
438
# Test SHOW CREATE TABLE
440
show create table t1;
444
# Partition by key no partition, list of fields
451
partition by key (a, b);
455
# Partition by key specified 3 partitions and defined 3 => ok
464
(partition x1, partition x2, partition x3);
468
# Partition by key specifying nodegroup
477
(partition x1 nodegroup 0,
478
partition x2 nodegroup 1,
479
partition x3 nodegroup 2);
483
# Partition by key specifying engine
492
(partition x1 engine myisam,
493
partition x2 engine myisam,
494
partition x3 engine myisam);
498
# Partition by key specifying tablespace
507
(partition x1 tablespace ts1,
508
partition x2 tablespace ts2,
509
partition x3 tablespace ts3);
511
CREATE TABLE t2 LIKE t1;
517
# Partition by key list, basic
524
partition by list (a)
526
(partition x1 values in (1,2,9,4) tablespace ts1,
527
partition x2 values in (3, 11, 5, 7) tablespace ts2,
528
partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
532
# Partition by key list, list function
539
partition by list (b*a)
541
(partition x1 values in (1,2,9,4) tablespace ts1,
542
partition x2 values in (3, 11, 5, 7) tablespace ts2,
543
partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
548
# Partition by key list, list function, no spec of #partitions
555
partition by list (b*a)
556
(partition x1 values in (1) tablespace ts1,
557
partition x2 values in (3, 11, 5, 7) tablespace ts2,
558
partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
563
# Bug 13154: Insert crashes due to bad calculation of partition id
564
# for PARTITION BY KEY and SUBPARTITION BY KEY
570
LOCK TABLES t1 WRITE;
571
insert into t1 values (1);
572
insert into t1 values (2);
573
insert into t1 values (3);
574
insert into t1 values (4);
580
# Bug #13644 DROP PARTITION NULL's DATE column
582
CREATE TABLE t1 (a int, name VARCHAR(50), purchased DATE)
583
PARTITION BY RANGE (a)
584
(PARTITION p0 VALUES LESS THAN (3),
585
PARTITION p1 VALUES LESS THAN (7),
586
PARTITION p2 VALUES LESS THAN (9),
587
PARTITION p3 VALUES LESS THAN (11));
588
INSERT INTO t1 VALUES
589
(1, 'desk organiser', '2003-10-15'),
590
(2, 'CD player', '1993-11-05'),
591
(3, 'TV set', '1996-03-10'),
592
(4, 'bookcase', '1982-01-10'),
593
(5, 'exercise bike', '2004-05-09'),
594
(6, 'sofa', '1987-06-05'),
595
(7, 'popcorn maker', '2001-11-22'),
596
(8, 'acquarium', '1992-08-04'),
597
(9, 'study desk', '1984-09-16'),
598
(10, 'lava lamp', '1998-12-25');
600
SELECT * from t1 ORDER BY a;
601
ALTER TABLE t1 DROP PARTITION p0;
602
SELECT * from t1 ORDER BY a;
607
# Bug #13442; Truncate Partitioned table doesn't work
610
CREATE TABLE t1 (a int)
611
PARTITION BY LIST (a)
612
(PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (4,5,6));
614
insert into t1 values (1),(2),(3),(4),(5),(6);
623
# Bug #13445 Partition by KEY method crashes server
625
CREATE TABLE t1 (a int, b int, primary key(a,b))
626
PARTITION BY KEY(b,a) PARTITIONS 4;
628
insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
629
select * from t1 where a = 4;
634
# Bug#22351 - handler::index_next_same() call to key_cmp_if_same()
635
# uses the wrong buffer
637
CREATE TABLE t1 (c1 INT, c2 INT, PRIMARY KEY USING BTREE (c1,c2)) ENGINE=MEMORY
638
PARTITION BY KEY(c2,c1) PARTITIONS 4;
639
INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
640
SELECT * FROM t1 WHERE c1 = 4;
644
# Bug #13438: Engine clause in PARTITION clause causes crash
646
CREATE TABLE t1 (a int)
647
PARTITION BY LIST (a)
649
(PARTITION x1 VALUES IN (1) ENGINE=MEMORY);
651
show create table t1;
655
# Bug #13440: REPLACE causes crash in partitioned table
657
CREATE TABLE t1 (a int, unique(a))
658
PARTITION BY LIST (a)
659
(PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20));
661
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
662
REPLACE t1 SET a = 4;
666
# Bug #14365: Crash if value too small in list partitioned table
668
CREATE TABLE t1 (a int)
669
PARTITION BY LIST (a)
670
(PARTITION x1 VALUES IN (2), PARTITION x2 VALUES IN (3));
672
insert into t1 values (2), (3);
673
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
674
insert into t1 values (4);
675
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
676
insert into t1 values (1);
680
# Bug 14327: PARTITIONS clause gets lost in SHOW CREATE TABLE
682
CREATE TABLE t1 (a int)
686
SHOW CREATE TABLE t1;
691
# Bug #13446: Update to value outside of list values doesn't give error
693
CREATE TABLE t1 (a int)
694
PARTITION BY RANGE (a)
695
(PARTITION x1 VALUES LESS THAN (2));
697
insert into t1 values (1);
698
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
704
# Bug #13441: Analyze on partitioned table didn't work
706
CREATE TABLE t1 (a int)
707
PARTITION BY LIST (a)
708
(PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20));
715
# BUG 15221 (Cannot reorganize with the same name)
719
partition by range (a)
720
( partition p0 values less than(10),
721
partition p1 values less than (20),
722
partition p2 values less than (25));
724
alter table t1 reorganize partition p2 into (partition p2 values less than (30));
725
show create table t1;
728
CREATE TABLE t1 (a int, b int)
729
PARTITION BY RANGE (a)
730
(PARTITION x0 VALUES LESS THAN (2),
731
PARTITION x1 VALUES LESS THAN (4),
732
PARTITION x2 VALUES LESS THAN (6),
733
PARTITION x3 VALUES LESS THAN (8),
734
PARTITION x4 VALUES LESS THAN (10),
735
PARTITION x5 VALUES LESS THAN (12),
736
PARTITION x6 VALUES LESS THAN (14),
737
PARTITION x7 VALUES LESS THAN (16),
738
PARTITION x8 VALUES LESS THAN (18),
739
PARTITION x9 VALUES LESS THAN (20));
741
ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO
742
(PARTITION x1 VALUES LESS THAN (6));
743
show create table t1;
746
# Testcase for BUG#15819
747
create table t1 (a int not null, b int not null) partition by LIST (a+b) (
748
partition p0 values in (12),
749
partition p1 values in (14)
751
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
752
insert into t1 values (10,1);
757
# Bug#16901 Partitions: crash, SELECT, column of part.
758
# function=first column of primary key
760
create table t1 (f1 integer,f2 integer, f3 varchar(10), primary key(f1,f2))
761
partition by range(f1) subpartition by hash(f2) subpartitions 2
762
(partition p1 values less than (0),
763
partition p2 values less than (2),
764
partition p3 values less than (2147483647));
766
insert into t1 values(10,10,'10');
767
insert into t1 values(2,2,'2');
768
select * from t1 where f1 = 2;
772
# Bug #16907 Partitions: crash, SELECT goes into last partition, UNIQUE INDEX
774
create table t1 (f1 integer,f2 integer, unique index(f1))
775
partition by range(f1 div 2)
776
subpartition by hash(f1) subpartitions 2
777
(partition partb values less than (2),
778
partition parte values less than (4),
779
partition partf values less than (10000));
780
insert into t1 values(10,1);
781
select * from t1 where f1 = 10;
785
# Bug #16775: Wrong engine type stored for subpartition
787
set session storage_engine= 'memory';
788
create table t1 (f_int1 int(11) default null) engine = memory
789
partition by range (f_int1) subpartition by hash (f_int1)
790
(partition part1 values less than (1000)
791
(subpartition subpart11 engine = memory));
793
set session storage_engine='myisam';
796
# Bug #16782: Crash using REPLACE on table with primary key
798
create table t1 (f_int1 integer, f_int2 integer, primary key (f_int1))
799
partition by hash(f_int1) partitions 2;
800
insert into t1 values (1,1),(2,2);
801
replace into t1 values (1,1),(2,2);
805
# Bug #17169: Partitions: out of memory if add partition and unique
807
create table t1 (s1 int, unique (s1)) partition by list (s1) (partition x1 VALUES in (10), partition x2 values in (20));
808
alter table t1 add partition (partition x3 values in (30));
812
# Bug #17754 Change to explicit removal of partitioning scheme
813
# Also added a number of tests to ensure that proper engine is
814
# choosen in all kinds of scenarios.
817
create table t1 (a int)
820
(partition p0 engine=myisam, partition p1 engine=myisam);
821
show create table t1;
824
show create table t1;
826
alter table t1 engine=myisam;
827
show create table t1;
829
alter table t1 engine=heap;
830
show create table t1;
832
alter table t1 remove partitioning;
833
show create table t1;
837
create table t1 (a int)
841
(partition p0 engine=myisam, partition p1 engine=myisam);
842
show create table t1;
844
alter table t1 add column b int remove partitioning;
845
show create table t1;
850
(partition p0 engine=myisam, partition p1);
851
show create table t1;
856
(partition p0, partition p1 engine=heap);
857
show create table t1;
859
alter table t1 engine=myisam, add column c int remove partitioning;
860
show create table t1;
865
(partition p0, partition p1);
866
show create table t1;
870
(partition p0, partition p1);
871
show create table t1;
876
(partition p0, partition p1);
877
show create table t1;
879
# Since alter, it already have ENGINE=HEAP from before on table level
883
(partition p0, partition p1 engine=heap);
885
# Since alter, it already have ENGINE=HEAP from before on table level
889
(partition p0 engine=heap, partition p1);
891
--error ER_MIX_HANDLER_ERROR
895
(partition p0 engine=heap, partition p1 engine=myisam);
897
--error ER_MIX_HANDLER_ERROR
900
(partition p0 engine=heap, partition p1 engine=myisam);
904
# Bug #17432: Partition functions containing NULL values should return
908
f_int1 INTEGER, f_int2 INTEGER,
909
f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000)
911
PARTITION BY RANGE(f_int1 DIV 2)
912
SUBPARTITION BY HASH(f_int1)
914
(PARTITION parta VALUES LESS THAN (0),
915
PARTITION partb VALUES LESS THAN (5),
916
PARTITION parte VALUES LESS THAN (10),
917
PARTITION partf VALUES LESS THAN (2147483647));
918
INSERT INTO t1 SET f_int1 = NULL , f_int2 = -20, f_char1 = CAST(-20 AS CHAR),
919
f_char2 = CAST(-20 AS CHAR), f_charbig = '#NULL#';
920
SELECT * FROM t1 WHERE f_int1 IS NULL;
925
# Bug 17430: Crash when SELECT * from t1 where field IS NULL
929
f_int1 INTEGER, f_int2 INTEGER,
930
f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000) )
931
PARTITION BY LIST(MOD(f_int1,2))
932
SUBPARTITION BY KEY(f_int1)
933
(PARTITION part1 VALUES IN (-1) (SUBPARTITION sp1, SUBPARTITION sp2),
934
PARTITION part2 VALUES IN (0) (SUBPARTITION sp3, SUBPARTITION sp5),
935
PARTITION part3 VALUES IN (1) (SUBPARTITION sp4, SUBPARTITION sp6));
937
INSERT INTO t1 SET f_int1 = 2, f_int2 = 2, f_char1 = '2', f_char2 = '2', f_charbig = '===2===';
938
INSERT INTO t1 SET f_int1 = 2, f_int2 = 2, f_char1 = '2', f_char2 = '2', f_charbig = '===2===';
940
SELECT * FROM t1 WHERE f_int1 IS NULL;
944
# Bug#14363 Partitions: failure if create in stored procedure
948
create procedure p ()
950
create table t1 (s1 mediumint,s2 mediumint)
951
partition by list (s2)
952
(partition p1 values in (0),
953
partition p2 values in (1));
960
create procedure p ()
962
create table t1 (a int not null,b int not null,c int not null,primary key (a,b))
963
partition by range (a)
964
subpartition by hash (a+b)
965
(partition x1 values less than (1)
968
partition x2 values less than (5)
979
# Bug #15447 Partitions: NULL is treated as zero
982
# NULL for RANGE partition
983
create table t1 (a int,b int,c int,key(a,b))
984
partition by range (a)
986
(partition x1 values less than (0) tablespace ts1,
987
partition x2 values less than (10) tablespace ts2,
988
partition x3 values less than maxvalue tablespace ts3);
990
insert into t1 values (NULL, 1, 1);
991
insert into t1 values (0, 1, 1);
992
insert into t1 values (12, 1, 1);
994
select partition_name, partition_description, table_rows
995
from information_schema.partitions where table_schema ='test';
998
# NULL for LIST partition
999
--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR
1000
create table t1 (a int,b int, c int)
1001
partition by list(a)
1003
(partition x123 values in (11,12),
1004
partition x234 values in (1 ,NULL, NULL));
1006
--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR
1007
create table t1 (a int,b int, c int)
1008
partition by list(a)
1010
(partition x123 values in (11, NULL),
1011
partition x234 values in (1 ,NULL));
1013
create table t1 (a int,b int, c int)
1014
partition by list(a)
1016
(partition x123 values in (11, 12),
1017
partition x234 values in (5, 1));
1018
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
1019
insert into t1 values (NULL,1,1);
1022
create table t1 (a int,b int, c int)
1023
partition by list(a)
1025
(partition x123 values in (11, 12),
1026
partition x234 values in (NULL, 1));
1028
insert into t1 values (11,1,6);
1029
insert into t1 values (NULL,1,1);
1031
select partition_name, partition_description, table_rows
1032
from information_schema.partitions where table_schema ='test';
1036
# BUG 17947 Crash with REBUILD PARTITION
1038
create table t1 (a int)
1039
partition by list (a)
1040
(partition p0 values in (1));
1042
--error ER_PARSE_ERROR
1043
alter table t1 rebuild partition;
1048
# BUG 15253 Insert that should fail doesn't
1050
create table t1 (a int)
1051
partition by list (a)
1052
(partition p0 values in (5));
1054
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
1055
insert into t1 values (0);
1060
# BUG #16370 Subpartitions names not shown in SHOW CREATE TABLE output
1062
create table t1 (a int)
1063
partition by range (a) subpartition by hash (a)
1064
(partition p0 values less than (100));
1066
show create table t1;
1067
alter table t1 add partition (partition p1 values less than (200)
1068
(subpartition subpart21));
1070
show create table t1;
1074
create table t1 (a int)
1075
partition by key (a);
1077
show create table t1;
1078
alter table t1 add partition (partition p1);
1079
show create table t1;
1084
# BUG 15407 Crash with subpartition
1086
--error ER_PARSE_ERROR
1087
create table t1 (a int, b int)
1088
partition by range (a)
1089
subpartition by hash(a)
1090
(partition p0 values less than (0) (subpartition sp0),
1091
partition p1 values less than (1));
1093
--error ER_PARSE_ERROR
1094
create table t1 (a int, b int)
1095
partition by range (a)
1096
subpartition by hash(a)
1097
(partition p0 values less than (0),
1098
partition p1 values less than (1) (subpartition sp0));
1101
# Bug 46354 Crash with subpartition
1103
--error ER_PARSE_ERROR
1104
create table t1 (a int, b int)
1105
partition by list (a)
1106
subpartition by hash(a)
1107
(partition p0 values in (0),
1108
partition p1 values in (1) (subpartition sp0));
1112
# BUG 15961 No error when subpartition defined without subpartition by clause
1114
--error ER_SUBPARTITION_ERROR
1115
create table t1 (a int)
1116
partition by hash (a)
1117
(partition p0 (subpartition sp0));
1122
create table t1 (a int)
1123
partition by range (a)
1124
(partition p0 values less than (1));
1126
--error ER_PARTITION_WRONG_VALUES_ERROR
1127
alter table t1 add partition (partition p1 values in (2));
1128
--error ER_PARTITION_REQUIRES_VALUES_ERROR
1129
alter table t1 add partition (partition p1);
1133
create table t1 (a int)
1134
partition by list (a)
1135
(partition p0 values in (1));
1137
--error ER_PARTITION_WRONG_VALUES_ERROR
1138
alter table t1 add partition (partition p1 values less than (2));
1139
--error ER_PARTITION_REQUIRES_VALUES_ERROR
1140
alter table t1 add partition (partition p1);
1144
create table t1 (a int)
1145
partition by hash (a)
1148
--error ER_PARTITION_WRONG_VALUES_ERROR
1149
alter table t1 add partition (partition p1 values less than (2));
1150
--error ER_PARTITION_WRONG_VALUES_ERROR
1151
alter table t1 add partition (partition p1 values in (2));
1156
# BUG 17947 Crash with REBUILD PARTITION
1158
create table t1 (a int)
1159
partition by list (a)
1160
(partition p0 values in (1));
1162
--error ER_PARSE_ERROR
1163
alter table t1 rebuild partition;
1168
# Bug #14526: Partitions: indexed searches fail
1170
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));
1171
insert into t2 values (null),(null),(null);
1173
select * from t2 where s1 < 2;
1174
update t2 set s1 = s1 + 1 order by s1 desc;
1175
select * from t2 where s1 < 3;
1176
select * from t2 where s1 = 2;
1180
# Bug #17497: Partitions: crash if add partition on temporary table
1182
--error ER_PARTITION_NO_TEMPORARY
1183
create temporary table t1 (a int) partition by hash(a);
1186
# Bug #17097: Partitions: failing ADD PRIMARY KEY leads to temporary rotten
1189
create table t1 (a int, b int) partition by list (a)
1190
(partition p1 values in (1), partition p2 values in (2));
1191
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
1192
alter table t1 add primary key (b);
1193
show create table t1;
1196
############################################
1198
# Author: Mikael Ronstrom
1201
# Bug 17772: Crash at ALTER TABLE with rename
1202
# and add column + comment on
1205
############################################
1206
create table t1 (a int unsigned not null auto_increment primary key)
1207
partition by key(a);
1208
alter table t1 rename t2, add c char(10), comment "no comment";
1209
show create table t2;
1214
# Bug#15336 Partitions: crash if create table as select
1216
create table t1 (f1 int) partition by hash (f1) as select 1;
1220
# bug #14350 Partitions: crash if prepared statement
1222
prepare stmt1 from 'create table t1 (s1 int) partition by hash (s1)';
1224
--error ER_TABLE_EXISTS_ERROR
1229
# bug 17290 SP with delete, create and rollback to save point causes MySQLD core
1232
eval CREATE PROCEDURE test.p1(IN i INT)
1234
DECLARE CONTINUE HANDLER FOR sqlexception BEGIN END;
1235
DROP TABLE IF EXISTS t1;
1236
CREATE TABLE t1 (num INT,PRIMARY KEY(num));
1238
INSERT INTO t1 VALUES(i);
1240
INSERT INTO t1 VALUES (14);
1241
ROLLBACK to savepoint t1_save;
1248
drop procedure test.p1;
1251
# Bug 13520: Problem with delimiters in COMMENT DATA DIRECTORY ..
1253
CREATE TABLE t1 (a int not null)
1255
(partition p0 COMMENT='first partition');
1259
# Bug 13433: Problem with delimited identifiers
1261
CREATE TABLE t1 (`a b` int not null)
1262
partition by key(`a b`);
1265
CREATE TABLE t1 (`a b` int not null)
1266
partition by hash(`a b`);
1270
# Bug#18053 Partitions: crash if null
1271
# Bug#18070 Partitions: wrong result on WHERE ... IS NULL
1273
create table t1 (f1 integer) partition by range(f1)
1274
(partition p1 values less than (0), partition p2 values less than (10));
1275
insert into t1 set f1 = null;
1276
select * from t1 where f1 is null;
1277
explain partitions select * from t1 where f1 is null;
1280
create table t1 (f1 integer) partition by list(f1)
1281
(partition p1 values in (1), partition p2 values in (null));
1282
insert into t1 set f1 = null;
1283
insert into t1 set f1 = 1;
1284
select * from t1 where f1 is null or f1 = 1;
1287
create table t1 (f1 smallint)
1288
partition by list (f1) (partition p0 values in (null));
1289
insert into t1 values (null);
1290
select * from t1 where f1 is null;
1291
select * from t1 where f1 < 1;
1292
select * from t1 where f1 <= NULL;
1293
select * from t1 where f1 < NULL;
1294
select * from t1 where f1 >= NULL;
1295
select * from t1 where f1 > NULL;
1296
select * from t1 where f1 > 1;
1299
create table t1 (f1 smallint)
1300
partition by range (f1) (partition p0 values less than (0));
1301
insert into t1 values (null);
1302
select * from t1 where f1 is null;
1305
create table t1 (f1 integer) partition by list(f1)
1307
partition p1 values in (1),
1308
partition p2 values in (NULL),
1309
partition p3 values in (2),
1310
partition p4 values in (3),
1311
partition p5 values in (4)
1314
insert into t1 values (1),(2),(3),(4),(null);
1315
select * from t1 where f1 < 3;
1316
explain partitions select * from t1 where f1 < 3;
1317
select * from t1 where f1 is null;
1318
explain partitions select * from t1 where f1 is null;
1321
create table t1 (f1 int) partition by list(f1 div 2)
1323
partition p1 values in (1),
1324
partition p2 values in (NULL),
1325
partition p3 values in (2),
1326
partition p4 values in (3),
1327
partition p5 values in (4)
1330
insert into t1 values (2),(4),(6),(8),(null);
1331
select * from t1 where f1 < 3;
1332
explain partitions select * from t1 where f1 < 3;
1333
select * from t1 where f1 is null;
1334
explain partitions select * from t1 where f1 is null;
1337
create table t1 (a int) partition by LIST(a) (
1338
partition pn values in (NULL),
1339
partition p0 values in (0),
1340
partition p1 values in (1),
1341
partition p2 values in (2)
1343
insert into t1 values (NULL),(0),(1),(2);
1344
select * from t1 where a is null or a < 2;
1345
explain partitions select * from t1 where a is null or a < 2;
1346
select * from t1 where a is null or a < 0 or a > 1;
1347
explain partitions select * from t1 where a is null or a < 0 or a > 1;
1351
#Bug# 17631 SHOW TABLE STATUS reports wrong engine
1353
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, name VARCHAR(20))
1354
ENGINE=MyISAM DEFAULT CHARSET=latin1
1355
PARTITION BY RANGE(id)
1356
(PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM,
1357
PARTITION p1 VALUES LESS THAN (20) ENGINE = MyISAM,
1358
PARTITION p2 VALUES LESS THAN (30) ENGINE = MyISAM);
1359
--replace_column 6 0 7 0 8 0 9 0 12 NULL 13 NULL 14 NULL
1364
#BUG 16002 Erroneus handling of unsigned partition functions
1366
--error ER_PARTITION_CONST_DOMAIN_ERROR
1367
create table t1 (a bigint unsigned)
1368
partition by list (a)
1369
(partition p0 values in (0-1));
1371
create table t1 (a bigint unsigned)
1372
partition by range (a)
1373
(partition p0 values less than (10));
1375
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
1376
insert into t1 values (0xFFFFFFFFFFFFFFFF);
1381
#BUG 18750 Problems with partition names
1383
create table t1 (a int)
1384
partition by list (a)
1385
(partition `s1 s2` values in (0));
1388
create table t1 (a int)
1389
partition by list (a)
1390
(partition `7` values in (0));
1393
--error ER_WRONG_PARTITION_NAME
1394
create table t1 (a int)
1395
partition by list (a)
1396
(partition `s1 s2 ` values in (0));
1398
--error ER_WRONG_PARTITION_NAME
1399
create table t1 (a int)
1400
partition by list (a)
1401
subpartition by hash (a)
1402
(partition p1 values in (0) (subpartition `p1 p2 `));
1405
# BUG 18752 SHOW CREATE TABLE doesn't show NULL value in SHOW CREATE TABLE
1407
CREATE TABLE t1 (a int)
1408
PARTITION BY LIST (a)
1409
(PARTITION p0 VALUES IN (NULL));
1410
SHOW CREATE TABLE t1;
1413
--error ER_PARSE_ERROR
1414
CREATE TABLE t1 (a int)
1415
PARTITION BY RANGE(a)
1416
(PARTITION p0 VALUES LESS THAN (NULL));
1419
# Bug#18753 Partitions: auto_increment fails
1421
create table t1 (s1 int auto_increment primary key)
1422
partition by list (s1)
1423
(partition p1 values in (1),
1424
partition p2 values in (2),
1425
partition p3 values in (3));
1426
insert into t1 values (null);
1427
insert into t1 values (null);
1428
insert into t1 values (null);
1429
select auto_increment from information_schema.tables where table_name='t1';
1434
# BUG 19140 Partitions: Create index for partitioned table crashes
1436
create table t1 (a int) engine=memory
1437
partition by key(a);
1438
insert into t1 values (1);
1439
create index inx1 on t1(a);
1443
# Bug 19695 Partitions: SHOW CREATE TABLE shows table options even when it
1446
create table t1 (a int)
1447
PARTITION BY KEY (a)
1449
set session sql_mode='no_table_options';
1450
show create table t1;
1451
set session sql_mode='';
1455
# BUG 19304 Partitions: MERGE handler not allowed in partitioned tables
1457
--error ER_PARTITION_MERGE_ERROR
1458
create table t1 (a int)
1459
partition by key (a)
1460
(partition p0 engine = MERGE);
1463
# BUG 19062 Partition clause ignored if CREATE TABLE ... AS SELECT ...;
1465
create table t1 (a varchar(1))
1466
partition by key (a)
1469
show create table t1;
1473
# BUG 19501 Partitions: SHOW TABLE STATUS shows wrong Data_free
1475
CREATE TABLE t1 (a int) ENGINE = MYISAM PARTITION BY KEY(a);
1476
INSERT into t1 values (1), (2);
1477
--replace_column 9 0 12 NULL 13 NULL 14 NULL
1479
DELETE from t1 where a = 1;
1480
--replace_column 9 0 12 NULL 13 NULL 14 NULL
1482
ALTER TABLE t1 OPTIMIZE PARTITION p0;
1483
--replace_column 12 NULL 13 NULL 14 NULL
1488
# BUG 19502: ENABLE/DISABLE Keys don't work for partitioned tables
1490
CREATE TABLE t1 (a int, index(a)) PARTITION BY KEY(a);
1491
ALTER TABLE t1 DISABLE KEYS;
1492
ALTER TABLE t1 ENABLE KEYS;
1496
# Bug 17455 Partitions: Wrong message and error when using Repair/Optimize
1497
# table on partitioned table
1498
# (added check/analyze for gcov of Bug#20129)
1499
create table t1 (a int)
1501
partition by key (a);
1511
#BUG 17138 Problem with stored procedure and analyze partition
1514
drop procedure if exists mysqltest_1;
1517
create table t1 (a int)
1518
partition by list (a)
1519
(partition p0 values in (0));
1521
insert into t1 values (0);
1524
create procedure mysqltest_1 ()
1527
declare continue handler for sqlexception begin end;
1528
update ignore t1 set a = 1 where a = 0;
1530
prepare stmt1 from 'alter table t1';
1534
call mysqltest_1()//
1537
drop procedure mysqltest_1;
1540
# Bug 20583 Partitions: Crash using index_last
1542
create table t1 (a int, index(a))
1543
partition by hash(a);
1544
insert into t1 values (1),(2);
1545
select * from t1 ORDER BY a DESC;
1549
# Bug 21388: Bigint fails to find record
1551
create table t1 (a bigint unsigned not null, primary key(a))
1553
partition by key (a)
1556
show create table t1;
1557
insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE),
1558
(18446744073709551613), (18446744073709551612);
1560
select * from t1 where a = 18446744073709551615;
1561
delete from t1 where a = 18446744073709551615;
1566
# Bug 24502 reorganize partition closes connection
1569
num int(11) NOT NULL, cs int(11) NOT NULL)
1570
PARTITION BY RANGE (num) SUBPARTITION BY HASH (
1571
cs) SUBPARTITIONS 2 (PARTITION p_X VALUES LESS THAN MAXVALUE);
1574
REORGANIZE PARTITION p_X INTO (
1575
PARTITION p_100 VALUES LESS THAN (100),
1576
PARTITION p_X VALUES LESS THAN MAXVALUE
1582
# Bug #24186 (nested query across partitions returns fewer records)
1586
taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
1587
id int(11) NOT NULL DEFAULT '0',
1588
PRIMARY KEY (id,taken),
1590
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1592
INSERT INTO t2 VALUES
1593
('2006-09-27 21:50:01',16421),
1594
('2006-10-02 21:50:01',16421),
1595
('2006-09-27 21:50:01',19092),
1596
('2006-09-28 21:50:01',19092),
1597
('2006-09-29 21:50:01',19092),
1598
('2006-09-30 21:50:01',19092),
1599
('2006-10-01 21:50:01',19092),
1600
('2006-10-02 21:50:01',19092),
1601
('2006-09-27 21:50:01',22589),
1602
('2006-09-29 21:50:01',22589);
1607
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1609
INSERT INTO t1 VALUES
1615
taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
1616
id int(11) NOT NULL DEFAULT '0',
1617
PRIMARY KEY (id,taken),
1619
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1620
PARTITION BY RANGE (to_days(taken))
1622
PARTITION p01 VALUES LESS THAN (732920) ,
1623
PARTITION p02 VALUES LESS THAN (732950) ,
1624
PARTITION p03 VALUES LESS THAN MAXVALUE ) ;
1626
INSERT INTO t4 select * from t2;
1628
set @f_date='2006-09-28';
1629
set @t_date='2006-10-02';
1631
SELECT t1.id AS MyISAM_part
1636
WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY))
1640
drop table t1, t2, t4;
1643
taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
1644
id int(11) NOT NULL DEFAULT '0',
1645
status varchar(20) NOT NULL DEFAULT '',
1646
PRIMARY KEY (id,taken)
1647
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1648
PARTITION BY RANGE (to_days(taken))
1650
PARTITION p15 VALUES LESS THAN (732950) ,
1651
PARTITION p16 VALUES LESS THAN MAXVALUE ) ;
1654
INSERT INTO t1 VALUES
1655
('2006-09-27 21:50:01',22589,'Open'),
1656
('2006-09-29 21:50:01',22589,'Verified');
1658
DROP TABLE IF EXISTS t2;
1661
severity tinyint(4) NOT NULL DEFAULT '0',
1662
priority tinyint(4) NOT NULL DEFAULT '0',
1663
status varchar(20) DEFAULT NULL,
1664
alien tinyint(4) NOT NULL
1665
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1667
INSERT INTO t2 VALUES
1668
(22589,1,1,'Need Feedback',0);
1670
SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified');
1675
# Bug #27123 partition + on duplicate key update + varchar = Can't find record in <table>
1677
create table t1 (c1 varchar(255),c2 tinyint,primary key(c1))
1678
partition by key (c1) partitions 10 ;
1679
insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1;
1680
insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1;
1685
# Bug #28005 Partitions: can't use -9223372036854775808
1688
create table t1 (s1 bigint) partition by list (s1) (partition p1 values in (-9223372036854775808));
1692
# Bug #28806: Running SHOW TABLE STATUS during high INSERT load crashes server
1694
create table t1(a int auto_increment, b int, primary key (b, a))
1695
partition by hash(b) partitions 2;
1696
insert into t1 values (null, 1);
1697
--replace_column 9 0 12 NULL 13 NULL 14 NULL
1701
create table t1(a int auto_increment primary key)
1702
partition by key(a) partitions 2;
1703
insert into t1 values (null), (null), (null);
1704
--replace_column 9 0 12 NULL 13 NULL 14 NULL
1707
# Bug #28488: Incorrect information in file: './test/t1_test#.frm'
1710
CREATE TABLE t1(a INT NOT NULL, b TINYBLOB, KEY(a))
1711
PARTITION BY RANGE(a) ( PARTITION p0 VALUES LESS THAN (32));
1712
INSERT INTO t1 VALUES (1, REPEAT('a', 10));
1713
INSERT INTO t1 SELECT a + 1, b FROM t1;
1714
INSERT INTO t1 SELECT a + 2, b FROM t1;
1715
INSERT INTO t1 SELECT a + 4, b FROM t1;
1716
INSERT INTO t1 SELECT a + 8, b FROM t1;
1718
ALTER TABLE t1 ADD PARTITION (PARTITION p1 VALUES LESS THAN (64));
1719
ALTER TABLE t1 DROP PARTITION p1;
1724
# Bug #30484: Partitions: crash with self-referencing trigger
1727
create table t (s1 int) engine=myisam partition by key (s1);
1728
create trigger t_ad after delete on t for each row insert into t values (old.s1);
1729
insert into t values (1);
1733
# Bug #27084 partitioning by list seems failing when using case
1734
# BUG #18198: Case no longer supported, test case removed
1737
create table t2 (b int);
1738
--error ER_BAD_FIELD_ERROR
1739
create table t1 (b int)
1740
PARTITION BY RANGE (t2.b) (
1741
PARTITION p1 VALUES LESS THAN (10),
1742
PARTITION p2 VALUES LESS THAN (20)
1744
create table t1 (a int)
1745
PARTITION BY RANGE (b) (
1746
PARTITION p1 VALUES LESS THAN (10),
1747
PARTITION p2 VALUES LESS THAN (20)
1749
show create table t1;
1753
# Bug #32067 Partitions: crash with timestamp column
1754
# this bug occurs randomly on some UPDATE statement
1755
# with the '1032: Can't find record in 't1'' error
1758
(s1 timestamp on update current_timestamp, s2 int)
1759
partition by key(s1) partitions 3;
1761
insert into t1 values (null,null);
1766
update t1 set s2 = 1;
1767
update t1 set s2 = 2;
1775
# BUG#32272: partition crash 1: enum column
1783
) engine=myisam partition by hash (month(c0)) partitions 5;
1786
insert ignore into t1 set c0 = -6502262, c1 = 3992917, c2 = 35019;
1787
insert ignore into t1 set c0 = 241221, c1 = -6862346, c2 = 56644;
1789
# This must not fail assert:
1790
select c1 from t1 group by (select c0 from t1 limit 1);
1793
# Bug #30495: optimize table t1,t2,t3 extended errors
1794
# (added more maintenace commands for Bug#20129
1795
CREATE TABLE t1(a int)
1796
PARTITION BY RANGE (a) (
1797
PARTITION p1 VALUES LESS THAN (10),
1798
PARTITION p2 VALUES LESS THAN (20)
1800
--error ER_PARSE_ERROR
1801
ALTER TABLE t1 OPTIMIZE PARTITION p1 EXTENDED;
1802
--error ER_PARSE_ERROR
1803
ALTER TABLE t1 ANALYZE PARTITION p1 EXTENDED;
1804
ALTER TABLE t1 ANALYZE PARTITION p1;
1805
ALTER TABLE t1 CHECK PARTITION p1;
1806
ALTER TABLE t1 REPAIR PARTITION p1;
1807
ALTER TABLE t1 OPTIMIZE PARTITION p1;
1811
# Bug #29258: Partitions: search fails for maximum unsigned bigint
1813
CREATE TABLE t1 (s1 BIGINT UNSIGNED)
1814
PARTITION BY RANGE (s1) (
1815
PARTITION p0 VALUES LESS THAN (0),
1816
PARTITION p1 VALUES LESS THAN (1),
1817
PARTITION p2 VALUES LESS THAN (18446744073709551615)
1819
INSERT INTO t1 VALUES (0), (18446744073709551614);
1820
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
1821
INSERT INTO t1 VALUES (18446744073709551615);
1824
CREATE TABLE t1 (s1 BIGINT UNSIGNED)
1825
PARTITION BY RANGE (s1) (
1826
PARTITION p0 VALUES LESS THAN (0),
1827
PARTITION p1 VALUES LESS THAN (1),
1828
PARTITION p2 VALUES LESS THAN (18446744073709551614),
1829
PARTITION p3 VALUES LESS THAN MAXVALUE
1831
INSERT INTO t1 VALUES (-1), (0), (18446744073709551613),
1832
(18446744073709551614), (18446744073709551615);
1834
SELECT * FROM t1 WHERE s1 = 0;
1835
SELECT * FROM t1 WHERE s1 = 18446744073709551614;
1836
SELECT * FROM t1 WHERE s1 = 18446744073709551615;
1839
CREATE TABLE t1 (s1 BIGINT UNSIGNED)
1840
PARTITION BY RANGE (s1) (
1841
PARTITION p0 VALUES LESS THAN (0),
1842
PARTITION p1 VALUES LESS THAN (1),
1843
PARTITION p2 VALUES LESS THAN (18446744073709551615),
1844
PARTITION p3 VALUES LESS THAN MAXVALUE
1849
# Bug #31890 Partitions: ORDER BY DESC in InnoDB not working
1853
(int_column INT, char_column CHAR(5),
1854
PRIMARY KEY(char_column,int_column))
1855
PARTITION BY KEY(char_column,int_column)
1857
INSERT INTO t1 (int_column, char_column) VALUES
1871
SELECT * FROM t1 ORDER BY char_column DESC;
1875
# Bug #32247 Test reports wrong value of "AUTO_INCREMENT" (on a partitioned InnoDB table)
1878
CREATE TABLE t1(id MEDIUMINT NOT NULL AUTO_INCREMENT,
1879
user CHAR(25), PRIMARY KEY(id))
1880
PARTITION BY RANGE(id)
1881
SUBPARTITION BY hash(id) subpartitions 2
1882
(PARTITION pa1 values less than (10),
1883
PARTITION pa2 values less than (20),
1884
PARTITION pa11 values less than MAXVALUE);
1889
insert into t1 (user) values ('mysql');
1893
show create table t1;
1897
# Bug #38272 timestamps fields incorrectly defaulted on update accross partitions.
1901
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
1902
`createdDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
1904
PRIMARY KEY (`ID`, number)
1906
PARTITION BY RANGE (number) (
1907
PARTITION p0 VALUES LESS THAN (6),
1908
PARTITION p1 VALUES LESS THAN (11)
1913
`createdDate` TIMESTAMP,
1917
INSERT INTO t1 SET number=1;
1918
insert into t2 select * from t1;
1920
UPDATE t1 SET number=6;
1921
select count(*) from t1, t2 where t1.createdDate = t2.createdDate;
1926
# Bug #38083 Error-causing row inserted into partitioned table despite error
1928
SET @orig_sql_mode = @@SQL_MODE;
1929
SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
1930
CREATE TABLE t1 (c1 INT)
1931
PARTITION BY LIST(1 DIV c1) (
1932
PARTITION p0 VALUES IN (NULL),
1933
PARTITION p1 VALUES IN (1)
1936
-- error ER_DIVISION_BY_ZERO
1937
INSERT INTO t1 VALUES (0);
1940
-- error ER_DIVISION_BY_ZERO
1941
INSERT INTO t1 VALUES (NULL), (0), (1), (2);
1944
SET SQL_MODE= @orig_sql_mode;
1949
# Bug #38005 Partitions: error with insert select
1952
create table t1 (s1 int) partition by hash(s1) partitions 2;
1953
create index i on t1 (s1);
1954
insert into t1 values (1);
1955
insert into t1 select s1 from t1;
1956
insert into t1 select s1 from t1;
1957
insert into t1 select s1 from t1 order by s1 desc;
1961
create table t1 (s1 int) partition by range(s1)
1962
(partition pa1 values less than (10),
1963
partition pa2 values less than MAXVALUE);
1964
create index i on t1 (s1);
1965
insert into t1 values (1);
1966
insert into t1 select s1 from t1;
1967
insert into t1 select s1 from t1;
1968
insert into t1 select s1 from t1 order by s1 desc;
1972
create table t1 (s1 int) partition by range(s1)
1973
(partition pa1 values less than (10),
1974
partition pa2 values less than MAXVALUE);
1975
create index i on t1 (s1);
1976
insert into t1 values (20);
1977
insert into t1 select s1 from t1;
1978
insert into t1 select s1 from t1;
1979
insert into t1 select s1 from t1 order by s1 desc;
1983
create table t1 (s1 int) partition by range(s1)
1984
(partition pa1 values less than (10),
1985
partition pa2 values less than MAXVALUE);
1986
create index i on t1 (s1);
1987
insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8);
1988
insert into t1 select s1 from t1;
1989
insert into t1 select s1 from t1;
1990
insert into t1 select s1 from t1;
1991
insert into t1 select s1 from t1;
1992
insert into t1 select s1 from t1 order by s1 desc;
1993
insert into t1 select s1 from t1 where s1=3;
1994
select count(*) from t1;
1999
--echo # Bug#42944: partition not pruned correctly
2001
CREATE TABLE t1 (a int) PARTITION BY RANGE (a)
2002
(PARTITION p0 VALUES LESS THAN (100),
2003
PARTITION p1 VALUES LESS THAN (200),
2004
PARTITION p2 VALUES LESS THAN (300),
2005
PARTITION p3 VALUES LESS THAN MAXVALUE);
2006
INSERT INTO t1 VALUES (10), (100), (200), (300), (400);
2007
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a>=200;
2011
# Bug#44821: select distinct on partitioned table returns wrong results
2013
CREATE TABLE t1 ( a INT, b INT, c INT, KEY bc(b, c) )
2014
PARTITION BY KEY (a, b) PARTITIONS 3
2017
INSERT INTO t1 VALUES
2044
SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c;
2047
SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c;
2052
--echo # Bug #45807: crash accessing partitioned table and sql_mode
2053
--echo # contains ONLY_FULL_GROUP_BY
2054
--echo # Bug#46923: select count(*) from partitioned table fails with
2055
--echo # ONLY_FULL_GROUP_BY
2058
SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY';
2059
CREATE TABLE t1(id INT,KEY(id)) ENGINE=MYISAM
2060
PARTITION BY HASH(id) PARTITIONS 2;
2061
SELECT COUNT(*) FROM t1;
2063
SET SESSION SQL_MODE=DEFAULT;
2067
--echo # BUG#45816 - assertion failure with index containing double
2068
--echo # column on partitioned table
2073
b DOUBLE DEFAULT NULL,
2076
) PARTITION BY HASH(c) PARTITIONS 3;
2078
INSERT INTO t1 VALUES (6,8,9);
2079
INSERT INTO t1 VALUES (6,8,10);
2081
SELECT 1 FROM t1 JOIN t1 AS t2 USING (a) FOR UPDATE;
2084
--echo End of 5.1 tests
added
removed
mysql-test/t/partition.test
