1#--disable_abort_on_error 2# 3# Simple test for the partition storage engine 4# taken from the select test. 5# 6# Last update: 7# 2007-10-22 mleich - Move ARCHIVE, BLACKHOLE and CSV related sub tests to 8# new tests. Reason: All these might be not available. 9# - Minor cleanup 10# 11--source include/have_partition.inc 12 13--disable_warnings 14drop table if exists t1, t2; 15--enable_warnings 16 17--echo # 18--echo # Bug#11765667: bug#58655: ASSERTION FAILED, 19--echo # SERVER CRASHES WITH MYSQLD GOT SIGNAL 6 20--echo # 21CREATE TABLE t1 ( 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) 25); 26INSERT 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'); 37ALTER 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) 41); 42INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES 43 ('1991-07-14', 'After Partitioning Insert', 299, 1234567, 'Insert row'); 44UPDATE t1 SET filler='Updating the row' WHERE uid=298; 45 46DROP TABLE t1; 47 48--echo # 49--echo # Bug#59297: Can't find record in 'tablename' on update inner join 50--echo # 51 52CREATE TABLE t1 ( 53a char(2) NOT NULL, 54b char(2) NOT NULL, 55c int(10) unsigned NOT NULL, 56d varchar(255) DEFAULT NULL, 57e varchar(1000) DEFAULT NULL, 58PRIMARY KEY (a, b, c), 59KEY (a), 60KEY (a, b) 61) 62/*!50100 PARTITION BY KEY (a) 63PARTITIONS 20 */; 64 65INSERT 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') 80; 81 82UPDATE t1 AS A, 83(SELECT '03' AS a, '06' AS b, 343 AS c, 'last' AS d) AS B 84SET A.e = B.d 85WHERE A.a = '03' 86AND A.b = '06' 87AND A.c = 343; 88 89DROP TABLE t1; 90 91--echo # 92--echo # Bug#59503: explain extended crash in get_mm_leaf 93--echo # 94CREATE TABLE t1 (a VARCHAR(51) CHARACTER SET latin1) 95ENGINE=MyISAM 96PARTITION BY KEY (a) PARTITIONS 1; 97INSERT INTO t1 VALUES ('a'),('b'),('c'); 98EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a > 1; 99DROP TABLE t1; 100 101--echo # 102--echo # Bug#57778: failed primary key add to partitioned innodb table 103--echo # inconsistent and crashes 104--echo # 105CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) 106PARTITION BY KEY (a) PARTITIONS 2; 107INSERT INTO t1 VALUES (0,1), (0,2); 108--error ER_DUP_ENTRY 109ALTER TABLE t1 ADD PRIMARY KEY (a); 110SHOW CREATE TABLE t1; 111SELECT * FROM t1; 112UPDATE t1 SET a = 1, b = 1 WHERE a = 0 AND b = 2; 113ALTER TABLE t1 ADD PRIMARY KEY (a); 114SELECT * FROM t1; 115ALTER TABLE t1 DROP PRIMARY KEY; 116SELECT * FROM t1; 117DROP TABLE t1; 118 119--echo # 120--echo # Bug#57113: ha_partition::extra(ha_extra_function): 121--echo # Assertion `m_extra_cache' failed 122CREATE TABLE t1 123(id INT NOT NULL PRIMARY KEY, 124 name VARCHAR(16) NOT NULL, 125 year YEAR, 126 INDEX name (name(8)) 127) 128PARTITION BY HASH(id) PARTITIONS 2; 129 130INSERT INTO t1 VALUES ( 1, 'FooBar', '1924' ); 131 132CREATE TABLE t2 (id INT); 133 134INSERT INTO t2 VALUES (1),(2); 135 136UPDATE t1, t2 SET t1.year = '1955' WHERE t1.name = 'FooBar'; 137 138DROP TABLE t1, t2; 139 140 141--echo # 142--echo # Bug#55458: Partitioned MyISAM table gets crashed by multi-table update 143--echo # 144CREATE TABLE t1 ( 145 `id` int NOT NULL, 146 `user_num` int DEFAULT NULL, 147 PRIMARY KEY (`id`) 148) ENGINE=MyISAM CHARSET=latin1; 149INSERT INTO t1 VALUES (1,8601); 150INSERT INTO t1 VALUES (2,8601); 151INSERT INTO t1 VALUES (3,8601); 152INSERT INTO t1 VALUES (4,8601); 153CREATE TABLE t2 ( 154 `id` int(11) NOT NULL, 155 `user_num` int DEFAULT NULL, 156 `name` varchar(64) NOT NULL, 157 PRIMARY KEY (`id`) 158) ENGINE=MyISAM CHARSET=latin1 159PARTITION BY HASH (id) 160PARTITIONS 2; 161INSERT INTO t2 VALUES (1,8601,'John'); 162INSERT INTO t2 VALUES (2,8601,'JS'); 163INSERT INTO t2 VALUES (3,8601,'John S'); 164 165UPDATE t1, t2 SET t2.name = 'John Smith' WHERE t1.user_num = t2.user_num; 166 167DROP TABLE t1, t2; 168 169--echo # Bug#39338: Fieldnames in 170--echo # INFORMATIONSCHEMA.PARTITIONS.PARTITION_EXPRESSION become unescaped 171--echo # NOTE: the partition expression is saved as a string, so changing from 172--echo # normal quotes to ansi quotes does not change the expression, only 173--echo # for partition by KEY. 174CREATE TABLE t1 ( 175 ID int(11) NOT NULL, 176 `aaaa,aaaaa` tinyint(3) UNSIGNED NOT NULL DEFAULT '0', 177 ddddddddd int(11) NOT NULL DEFAULT '0', 178 new_field0 varchar(50), 179 PRIMARY KEY(ID, `aaaa,aaaaa`, ddddddddd)) 180PARTITION BY RANGE(ID) 181PARTITIONS 3 182SUBPARTITION BY LINEAR KEY(ID,`aaaa,aaaaa`) 183SUBPARTITIONS 2 ( 184 PARTITION p01 VALUES LESS THAN(100), 185 PARTITION p11 VALUES LESS THAN(200), 186 PARTITION p21 VALUES LESS THAN MAXVALUE); 187SELECT PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1'; 188show create table t1; 189drop table t1; 190 191# 192# Bug#48276: can't add column if subpartition exists 193CREATE TABLE t1 (a INT, b INT) 194PARTITION BY LIST (a) 195SUBPARTITION BY HASH (b) 196(PARTITION p1 VALUES IN (1)); 197ALTER TABLE t1 ADD COLUMN c INT; 198DROP TABLE t1; 199 200# 201# Bug#46639: 1030 (HY000): Got error 124 from storage engine on 202# INSERT ... SELECT ... 203CREATE TABLE t1 ( 204 a int NOT NULL, 205 b int NOT NULL); 206 207CREATE TABLE t2 ( 208 a int NOT NULL, 209 b int NOT NULL, 210 INDEX(b) 211) 212PARTITION BY HASH(a) PARTITIONS 2; 213 214INSERT INTO t1 VALUES (399, 22); 215INSERT INTO t2 VALUES (1, 22), (1, 42); 216 217INSERT INTO t2 SELECT 1, 399 FROM t2, t1 218WHERE t1.b = t2.b; 219 220DROP TABLE t1, t2; 221 222# 223# Bug#46478: timestamp field incorrectly defaulted when partition is reorganized 224# 225CREATE TABLE t1 ( 226 a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 227 b varchar(10), 228 PRIMARY KEY (a) 229) 230PARTITION BY RANGE (UNIX_TIMESTAMP(a)) ( 231 PARTITION p1 VALUES LESS THAN (1199134800), 232 PARTITION pmax VALUES LESS THAN MAXVALUE 233); 234 235INSERT INTO t1 VALUES ('2007-07-30 17:35:48', 'p1'); 236INSERT INTO t1 VALUES ('2009-07-14 17:35:55', 'pmax'); 237INSERT INTO t1 VALUES ('2009-09-21 17:31:42', 'pmax'); 238 239SELECT * FROM t1; 240SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; 241EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; 242EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48'; 243ALTER TABLE t1 REORGANIZE PARTITION pmax INTO ( 244 PARTITION p3 VALUES LESS THAN (1247688000), 245 PARTITION pmax VALUES LESS THAN MAXVALUE); 246SELECT * FROM t1; 247SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; 248EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; 249EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48'; 250SHOW CREATE TABLE t1; 251DROP TABLE t1; 252 253# 254# Bug#45904: Error when CHARSET=utf8 and subpartitioning 255# 256create table t1 (a int NOT NULL, b varchar(5) NOT NULL) 257default charset=utf8 258partition by list (a) 259subpartition by key (b) 260(partition p0 values in (1), 261 partition p1 values in (2)); 262drop table t1; 263 264# 265# Bug#44059: rec_per_key on empty partition gives weird optimiser results 266# 267create table t1 (a int, b int, key(a)) 268partition by list (a) 269( partition p0 values in (1), 270 partition p1 values in (2)); 271insert into t1 values (1,1),(2,1),(2,2),(2,3); 272show indexes from t1; 273analyze table t1; 274show indexes from t1; 275drop table t1; 276 277# 278# Bug#40181: hang if create index 279# 280create table t1 (a int) 281partition by hash (a); 282create index i on t1 (a); 283insert into t1 values (1); 284insert into t1 select * from t1; 285--error ER_DUP_KEYNAME 286create index i on t1 (a); 287create index i2 on t1 (a); 288drop table t1; 289 290# 291# Bug#36001: Partitions: spelling and using some error messages 292# 293--error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING 294CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a)) 295ENGINE=MyISAM 296PARTITION BY HASH (a); 297 298# 299# Bug#40954: Crash if range search and order by. 300# 301CREATE TABLE t1 ( 302 pk INT NOT NULL AUTO_INCREMENT, 303 PRIMARY KEY (pk) 304) 305/*!50100 PARTITION BY HASH (pk) 306PARTITIONS 2 */; 307INSERT INTO t1 VALUES (NULL); 308INSERT INTO t1 VALUES (NULL); 309INSERT INTO t1 VALUES (NULL); 310SELECT * FROM t1 WHERE pk < 0 ORDER BY pk; 311DROP TABLE t1; 312 313# 314# Bug#35765: ALTER TABLE produces wrong error when non-existent storage engine 315# used 316SET sql_mode=no_engine_substitution; 317--error ER_UNKNOWN_STORAGE_ENGINE 318CREATE TABLE t1 (a INT) 319ENGINE=NonExistentEngine; 320--error ER_UNKNOWN_STORAGE_ENGINE 321CREATE TABLE t1 (a INT) 322ENGINE=NonExistentEngine 323PARTITION BY HASH (a); 324CREATE TABLE t1 (a INT) 325ENGINE=Memory; 326--error ER_UNKNOWN_STORAGE_ENGINE 327ALTER TABLE t1 ENGINE=NonExistentEngine; 328# OK to only specify one partitions engine, since it is already assigned at 329# table level (after create, it is specified on all levels and all parts). 330--error ER_UNKNOWN_STORAGE_ENGINE 331ALTER TABLE t1 332PARTITION BY HASH (a) 333(PARTITION p0 ENGINE=Memory, 334 PARTITION p1 ENGINE=NonExistentEngine); 335--error ER_UNKNOWN_STORAGE_ENGINE 336ALTER TABLE t1 ENGINE=NonExistentEngine; 337SHOW CREATE TABLE t1; 338DROP TABLE t1; 339 340SET sql_mode=''; 341CREATE TABLE t1 (a INT) 342ENGINE=NonExistentEngine; 343DROP TABLE t1; 344CREATE TABLE t1 (a INT) 345ENGINE=NonExistentEngine 346PARTITION BY HASH (a); 347DROP TABLE t1; 348CREATE TABLE t1 (a INT) 349ENGINE=Memory; 350ALTER TABLE t1 ENGINE=NonExistentEngine; 351# OK to only specify one partitions engine, since it is already assigned at 352# table level (after create, it is specified on all levels and all parts). 353ALTER TABLE t1 354PARTITION BY HASH (a) 355(PARTITION p0 ENGINE=Memory, 356 PARTITION p1 ENGINE=NonExistentEngine); 357ALTER TABLE t1 ENGINE=NonExistentEngine; 358SHOW CREATE TABLE t1; 359DROP TABLE t1; 360SET sql_mode=DEFAULT; 361 362# 363# Bug#40494: Crash MYSQL server crashes on range access with partitioning 364# and order by 365# 366CREATE TABLE t1 (a INT NOT NULL, KEY(a)) 367PARTITION BY RANGE(a) 368(PARTITION p1 VALUES LESS THAN (200), PARTITION pmax VALUES LESS THAN MAXVALUE); 369INSERT INTO t1 VALUES (2), (40), (40), (70), (60), (90), (199); 370SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a ASC; 371--sorted_result 372SELECT a FROM t1 WHERE a BETWEEN 60 AND 95; 373INSERT INTO t1 VALUES (200), (250), (210); 374SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a ASC; 375SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a ASC; 376SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a DESC; 377SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a DESC; 378SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a DESC; 379--sorted_result 380SELECT a FROM t1 WHERE a BETWEEN 60 AND 220; 381--sorted_result 382SELECT a FROM t1 WHERE a BETWEEN 200 AND 220; 383--sorted_result 384SELECT a FROM t1 WHERE a BETWEEN 60 AND 95; 385--sorted_result 386SELECT a FROM t1 WHERE a BETWEEN 60 AND 220; 387--sorted_result 388SELECT a FROM t1 WHERE a BETWEEN 200 AND 220; 389DROP TABLE t1; 390 391# 392# Bug35931: Index search may return duplicates 393# 394CREATE TABLE t1 ( 395 a INT NOT NULL, 396 b MEDIUMINT NOT NULL, 397 c INT NOT NULL, 398 KEY b (b) 399) ENGINE=MyISAM 400PARTITION BY LIST (a) ( 401 PARTITION p0 VALUES IN (1) 402); 403INSERT INTO t1 VALUES (1,1,0), (1,1,1), (1,1,2), (1,1,53), (1,1,4), (1,1,5), 404(1,1,6), (1,1,7), (1,1,8), (1,1,9), (1,1,10), (1,1,11), (1,1,12), (1,1,13), 405(1,1,14), (1,1,15), (1,1,16), (1,1,67), (1,1,18), (1,1,19), (1,1,20), (1,1,21), 406(1,1,22), (1,1,23), (1,1,24), (1,1,75), (1,1,26), (1,1,27), (1,1,128), 407(1,1,79), (1,1,30), (1,1,31), (1,1,32), (1,1,33), (1,1,34), (1,1,85), (1,1,36), 408(1,1,37), (1,1,38), (1,1,39), (1,1,40), (1,1,241), (1,1,42), (1,1,43), 409(1,1,44), (1,1,45), (1,1,46), (1,1,147), (1,1,48), (1,1,49), (1,2,0), (1,2,1), 410(1,2,2), (1,2,3), (1,2,4), (1,2,5), (1,2,6), (1,2,7), (1,2,8), (1,2,9), 411(1,2,10), (1,2,11), (1,2,12), (1,2,13), (1,2,14), (1,2,15), (1,2,16), (1,2,17), 412(1,2,18), (1,2,19), (1,2,20), (1,2,21), (1,2,22), (1,2,23), (1,2,24), (1,2,25), 413(1,2,26), (1,2,27), (1,2,28), (1,2,29), (1,2,30), (1,2,31), (1,2,32), (1,2,33), 414(1,2,34), (1,2,35), (1,2,36), (1,2,37), (1,2,38), (1,2,39), (1,2,40), (1,2,41), 415(1,2,42), (1,2,43), (1,2,44), (1,2,45), (1,2,46), (1,2,47), (1,2,48), (1,2,49), 416(1,6,0), (1,6,1), (1,6,2), (1,6,3), (1,6,4), (1,6,5), (1,6,6), (1,6,7), 417(1,6,8), (1,6,9), (1,6,10), (1,6,11), (1,6,12), (1,6,13), (1,6,14), (1,6,15), 418(1,6,16), (1,6,17), (1,6,18), (1,6,19), (1,6,20), (1,6,21), (1,6,22), (1,6,23), 419(1,6,24), (1,6,25), (1,6,26), (1,6,27), (1,6,28), (1,6,29), (1,6,30), (1,6,31), 420(1,6,32), (1,6,33), (1,6,34), (1,6,35), (1,6,36), (1,6,37), (1,6,38), (1,6,39), 421(1,6,40), (1,6,41), (1,6,42), (1,6,43), (1,6,44), (1,6,45), (1,6,46), (1,6,47), 422(1,6,48), (1,6,49), (1,7,0), (1,7,1), (1,7,2), (1,7,3), (1,7,4), (1,7,5), 423(1,7,6), (1,7,7), (1,7,8), (1,7,9), (1,7,10), (1,7,11), (1,7,12), (1,7,13), 424(1,7,14), (1,7,15), (1,7,16), (1,7,17), (1,7,18), (1,7,19), (1,7,20), (1,7,21), 425(1,7,22), (1,7,23), (1,7,24), (1,7,25), (1,7,26), (1,7,27), (1,7,28), (1,7,29), 426 (1,7,30), (1,7,31), (1,7,32), (1,7,33), (1,7,34), (1,7,35), (1,7,38), (1,7,39), 427(1,7,90), (1,7,41), (1,7,43), (1,7,48), (1,7,49), (1,9,0), (1,9,1), (1,9,2), 428(1,9,3), (1,9,4), (1,9,5), (1,9,6), (1,9,7), (1,9,8), (1,9,9), (1,9,10), 429(1,9,11), (1,9,12), (1,9,13), (1,9,14), (1,9,15), (1,9,16), (1,9,17), (1,9,18), 430(1,9,19), (1,9,20), (1,9,21), (1,9,22), (1,9,23), (1,9,24), (1,9,25), (1,9,26), 431(1,9,29), (1,9,32), (1,9,35), (1,9,38), (1,10,0), (1,10,1), (1,10,2), (1,10,3), 432(1,10,4), (1,10,5), (1,10,6), (1,10,7), (1,10,8), (1,10,9), (1,10,10), 433(1,10,11), (1,10,13), (1,10,14), (1,10,15), (1,10,16), (1,10,17), (1,10,18), 434(1,10,22), (1,10,24), (1,10,25), (1,10,26), (1,10,28), (1,10,131), (1,10,33), 435(1,10,84), (1,10,35), (1,10,40), (1,10,42), (1,10,49), (1,11,0), (1,11,1), 436(1,11,2), (1,11,3), (1,11,4), (1,11,5), (1,11,6), (1,11,7), (1,11,8), (1,11,9), 437(1,11,10), (1,11,11), (1,11,12), (1,11,13), (1,11,14), (1,11,15), (1,11,16), 438(1,11,17), (1,11,18), (1,11,19), (1,11,20), (1,11,21), (1,11,22), (1,11,23), 439(1,11,24), (1,11,25), (1,11,26), (1,11,27), (1,11,28), (1,11,30), (1,11,31), 440(1,11,32), (1,11,33), (1,11,34), (1,11,35), (1,11,37), (1,11,39), (1,11,40), 441(1,11,42), (1,11,44), (1,11,45), (1,11,47), (1,11,48), (1,14,104), (1,14,58), 442(1,14,12), (1,14,13), (1,14,15), (1,14,16), (1,14,17), (1,14,34), (1,15,0), 443(1,15,1), (1,15,2), (1,15,3), (1,15,4), (1,15,5), (1,15,7), (1,15,9), 444(1,15,15), (1,15,27), (1,15,49), (1,16,0), (1,16,1), (1,16,3), (1,17,4), 445(1,19,1); 446SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 447SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 448SELECT SUM(c+0.0) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 449ALTER TABLE t1 DROP INDEX b; 450SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 451SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 452ALTER TABLE t1 ENGINE = Memory; 453SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 454SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 455ALTER TABLE t1 ADD INDEX b USING HASH (b); 456SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 457SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 458DROP TABLE t1; 459 460# Bug#37327 Range scan on partitioned table returns duplicate rows 461# (Duplicate of Bug#35931) 462CREATE TABLE `t1` ( 463 `c1` int(11) DEFAULT NULL, 464 KEY `c1` (`c1`) 465) ENGINE=MyISAM DEFAULT CHARSET=latin1; 466 467CREATE TABLE `t2` ( 468 `c1` int(11) DEFAULT NULL, 469 KEY `c1` (`c1`) 470) 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) */; 471 472INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); 473INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); 474 475EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); 476 477FLUSH STATUS; 478SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); 479SHOW STATUS LIKE 'Handler_read_%'; 480 481EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); 482 483FLUSH STATUS; 484SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); 485SHOW STATUS LIKE 'Handler_read_%'; 486DROP TABLE t1,t2; 487 488# Bug#37329 Range scan on partitioned tables shows higher Handler_read_next 489# (marked as duplicate of Bug#35931) 490CREATE TABLE `t1` ( 491 `c1` int(11) DEFAULT NULL, 492 KEY `c1` (`c1`) 493) ENGINE=MyISAM DEFAULT CHARSET=latin1; 494 495CREATE TABLE `t2` ( 496 `c1` int(11) DEFAULT NULL, 497 KEY `c1` (`c1`) 498) ENGINE=MyISAM DEFAULT CHARSET=latin1 499/*!50100 PARTITION BY RANGE (c1) 500(PARTITION a VALUES LESS THAN (100) ENGINE = MyISAM, 501 PARTITION b VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */; 502 503INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); 504INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); 505 506EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5); 507 508FLUSH STATUS; 509SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5); 510SHOW STATUS LIKE 'Handler_read_%'; 511 512EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5); 513 514FLUSH STATUS; 515SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5); 516SHOW STATUS LIKE 'Handler_read_%'; 517 518EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15); 519 520FLUSH STATUS; 521SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15); 522SHOW STATUS LIKE 'Handler_read_%'; 523 524EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15); 525 526FLUSH STATUS; 527SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15); 528SHOW STATUS LIKE 'Handler_read_%'; 529DROP TABLE t1,t2; 530 531--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED 532create table t1 (a int) partition by list ((a/3)*10 div 1) 533(partition p0 values in (0), partition p1 values in (1)); 534 535# 536# Bug #30695: An apostrophe ' in the comment of the ADD PARTITION causes the Server to crash. 537# 538# To verify the fix for crashing (on unix-type OS) 539# uncomment the exec and error rows! 540 541CREATE TABLE t1 ( 542 d DATE NOT NULL 543) 544PARTITION BY RANGE( YEAR(d) ) ( 545 PARTITION p0 VALUES LESS THAN (1960), 546 PARTITION p1 VALUES LESS THAN (1970), 547 PARTITION p2 VALUES LESS THAN (1980), 548 PARTITION p3 VALUES LESS THAN (1990) 549); 550 551ALTER TABLE t1 ADD PARTITION ( 552PARTITION `p5` VALUES LESS THAN (2010) 553COMMENT 'APSTART \' APEND' 554); 555#--exec sed 's/APSTART \\/APSTART /' var/master-data/test/t1.frm > tmpt1.frm && mv tmpt1.frm var/master-data/test/t1.frm 556#--error ER_PARSE_ERROR 557SELECT * FROM t1 LIMIT 1; 558 559DROP TABLE t1; 560 561# 562# Bug 30878: crashing when alter an auto_increment non partitioned 563# table to partitioned 564 565create table t1 (id int auto_increment, s1 int, primary key (id)); 566 567insert into t1 values (null,1); 568insert into t1 values (null,6); 569 570-- sorted_result 571select * from t1; 572 573alter table t1 partition by range (id) ( 574 partition p0 values less than (3), 575 partition p1 values less than maxvalue 576); 577 578drop table t1; 579 580# 581# Bug 15890: Strange number of partitions accepted 582# 583-- error ER_PARSE_ERROR 584create table t1 (a int) 585partition by key(a) 586partitions 0.2+e1; 587-- error ER_PARSE_ERROR 588create table t1 (a int) 589partition by key(a) 590partitions -1; 591-- error ER_PARSE_ERROR 592create table t1 (a int) 593partition by key(a) 594partitions 1.5; 595-- error ER_PARSE_ERROR 596create table t1 (a int) 597partition by key(a) 598partitions 1e+300; 599 600# 601# Bug 19309 Partitions: Crash if double procedural alter 602# 603create table t1 (a int) 604partition by list (a) 605(partition p0 values in (1)); 606 607create procedure pz() 608alter table t1 engine = myisam; 609 610call pz(); 611call pz(); 612drop procedure pz; 613drop table t1; 614 615# 616# BUG 16002: Handle unsigned integer functions properly 617# 618--error ER_VALUES_IS_NOT_INT_TYPE_ERROR 619create table t1 (a bigint) 620partition by range (a) 621(partition p0 values less than (0xFFFFFFFFFFFFFFFF), 622 partition p1 values less than (10)); 623--error ER_VALUES_IS_NOT_INT_TYPE_ERROR 624create table t1 (a bigint) 625partition by list (a) 626(partition p0 values in (0xFFFFFFFFFFFFFFFF), 627 partition p1 values in (10)); 628 629create table t1 (a bigint unsigned) 630partition by range (a) 631(partition p0 values less than (100), 632 partition p1 values less than MAXVALUE); 633insert into t1 values (1); 634drop table t1; 635 636create table t1 (a bigint unsigned) 637partition by hash (a); 638insert into t1 values (0xFFFFFFFFFFFFFFFD); 639insert into t1 values (0xFFFFFFFFFFFFFFFE); 640select * from t1 where (a + 1) < 10; 641select * from t1 where (a + 1) > 10; 642drop table t1; 643 644# 645# Added test case 646# 647create table t1 (a int) 648partition by key(a) 649(partition p0 engine = MEMORY); 650drop table t1; 651 652# 653# BUG 19067 ALTER TABLE .. ADD PARTITION for subpartitioned table crashes 654# 655create table t1 (a int) 656partition by range (a) 657subpartition by key (a) 658(partition p0 values less than (1)); 659alter table t1 add partition (partition p1 values less than (2)); 660show create table t1; 661alter table t1 reorganize partition p1 into (partition p1 values less than (3)); 662show create table t1; 663drop table t1; 664 665# 666# Partition by key no partition defined => OK 667# 668CREATE TABLE t1 ( 669a int not null, 670b int not null, 671c int not null, 672primary key(a,b)) 673partition by key (a); 674 675# 676# Bug 13323: Select count(*) on empty table returns 2 677# 678select count(*) from t1; 679 680# 681# Test SHOW CREATE TABLE 682# 683show create table t1; 684 685drop table t1; 686# 687# Partition by key no partition, list of fields 688# 689CREATE TABLE t1 ( 690a int not null, 691b int not null, 692c int not null, 693primary key(a,b)) 694partition by key (a, b); 695 696drop table t1; 697# 698# Partition by key specified 3 partitions and defined 3 => ok 699# 700CREATE TABLE t1 ( 701a int not null, 702b int not null, 703c int not null, 704primary key(a,b)) 705partition by key (a) 706partitions 3 707(partition x1, partition x2, partition x3); 708 709drop table t1; 710# 711# Partition by key specifying nodegroup 712# 713CREATE TABLE t1 ( 714a int not null, 715b int not null, 716c int not null, 717primary key(a,b)) 718partition by key (a) 719partitions 3 720(partition x1 nodegroup 0, 721 partition x2 nodegroup 1, 722 partition x3 nodegroup 2); 723 724drop table t1; 725# 726# Partition by key specifying engine 727# 728CREATE TABLE t1 ( 729a int not null, 730b int not null, 731c int not null, 732primary key(a,b)) 733partition by key (a) 734partitions 3 735(partition x1 engine myisam, 736 partition x2 engine myisam, 737 partition x3 engine myisam); 738 739drop table t1; 740# 741# Partition by key specifying tablespace 742# 743CREATE TABLE t1 ( 744a int not null, 745b int not null, 746c int not null, 747primary key(a,b)) 748partition by key (a) 749partitions 3 750(partition x1 tablespace ts1, 751 partition x2 tablespace ts2, 752 partition x3 tablespace ts3); 753 754CREATE TABLE t2 LIKE t1; 755 756drop table t2; 757drop table t1; 758 759# 760# Partition by key list, basic 761# 762CREATE TABLE t1 ( 763a int not null, 764b int not null, 765c int not null, 766primary key(a,b)) 767partition by list (a) 768partitions 3 769(partition x1 values in (1,2,9,4) tablespace ts1, 770 partition x2 values in (3, 11, 5, 7) tablespace ts2, 771 partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3); 772 773drop table t1; 774# 775# Partition by key list, list function 776# 777CREATE TABLE t1 ( 778a int not null, 779b int not null, 780c int not null, 781primary key(a,b)) 782partition by list (b*a) 783partitions 3 784(partition x1 values in (1,2,9,4) tablespace ts1, 785 partition x2 values in (3, 11, 5, 7) tablespace ts2, 786 partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3); 787 788drop table t1; 789 790# 791# Partition by key list, list function, no spec of #partitions 792# 793CREATE TABLE t1 ( 794a int not null, 795b int not null, 796c int not null, 797primary key(a,b)) 798partition by list (b*a) 799(partition x1 values in (1) tablespace ts1, 800 partition x2 values in (3, 11, 5, 7) tablespace ts2, 801 partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3); 802 803drop table t1; 804 805# 806# Bug 13154: Insert crashes due to bad calculation of partition id 807# for PARTITION BY KEY and SUBPARTITION BY KEY 808# 809CREATE TABLE t1 ( 810a int not null) 811partition by key(a); 812 813LOCK TABLES t1 WRITE; 814insert into t1 values (1); 815insert into t1 values (2); 816insert into t1 values (3); 817insert into t1 values (4); 818UNLOCK TABLES; 819 820drop table t1; 821 822# 823# Bug #13644 DROP PARTITION NULL's DATE column 824# 825CREATE TABLE t1 (a int, name VARCHAR(50), purchased DATE) 826PARTITION BY RANGE (a) 827(PARTITION p0 VALUES LESS THAN (3), 828 PARTITION p1 VALUES LESS THAN (7), 829 PARTITION p2 VALUES LESS THAN (9), 830 PARTITION p3 VALUES LESS THAN (11)); 831INSERT INTO t1 VALUES 832(1, 'desk organiser', '2003-10-15'), 833(2, 'CD player', '1993-11-05'), 834(3, 'TV set', '1996-03-10'), 835(4, 'bookcase', '1982-01-10'), 836(5, 'exercise bike', '2004-05-09'), 837(6, 'sofa', '1987-06-05'), 838(7, 'popcorn maker', '2001-11-22'), 839(8, 'acquarium', '1992-08-04'), 840(9, 'study desk', '1984-09-16'), 841(10, 'lava lamp', '1998-12-25'); 842 843SELECT * from t1 ORDER BY a; 844ALTER TABLE t1 DROP PARTITION p0; 845SELECT * from t1 ORDER BY a; 846 847drop table t1; 848 849# 850# Bug #13442; Truncate Partitioned table doesn't work 851# 852 853CREATE TABLE t1 (a int) 854PARTITION BY LIST (a) 855(PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (4,5,6)); 856 857insert into t1 values (1),(2),(3),(4),(5),(6); 858select * from t1; 859truncate t1; 860select * from t1; 861truncate t1; 862select * from t1; 863drop table t1; 864 865# 866# Bug #13445 Partition by KEY method crashes server 867# 868CREATE TABLE t1 (a int, b int, primary key(a,b)) 869PARTITION BY KEY(b,a) PARTITIONS 4; 870 871insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6); 872select * from t1 where a = 4; 873 874drop table t1; 875 876# 877# Bug#22351 - handler::index_next_same() call to key_cmp_if_same() 878# uses the wrong buffer 879# 880CREATE TABLE t1 (c1 INT, c2 INT, PRIMARY KEY USING BTREE (c1,c2)) ENGINE=MEMORY 881 PARTITION BY KEY(c2,c1) PARTITIONS 4; 882INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6); 883SELECT * FROM t1 WHERE c1 = 4; 884DROP TABLE t1; 885 886# 887# Bug #13438: Engine clause in PARTITION clause causes crash 888# 889CREATE TABLE t1 (a int) 890PARTITION BY LIST (a) 891PARTITIONS 1 892(PARTITION x1 VALUES IN (1) ENGINE=MEMORY); 893 894show create table t1; 895drop table t1; 896 897# 898# Bug #13440: REPLACE causes crash in partitioned table 899# 900CREATE TABLE t1 (a int, unique(a)) 901PARTITION BY LIST (a) 902(PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20)); 903 904--error ER_NO_PARTITION_FOR_GIVEN_VALUE 905REPLACE t1 SET a = 4; 906drop table t1; 907 908# 909# Bug #14365: Crash if value too small in list partitioned table 910# 911CREATE TABLE t1 (a int) 912PARTITION BY LIST (a) 913(PARTITION x1 VALUES IN (2), PARTITION x2 VALUES IN (3)); 914 915insert into t1 values (2), (3); 916--error ER_NO_PARTITION_FOR_GIVEN_VALUE 917insert into t1 values (4); 918--error ER_NO_PARTITION_FOR_GIVEN_VALUE 919insert into t1 values (1); 920drop table t1; 921 922# 923# Bug 14327: PARTITIONS clause gets lost in SHOW CREATE TABLE 924# 925CREATE TABLE t1 (a int) 926PARTITION BY HASH(a) 927PARTITIONS 5; 928 929SHOW CREATE TABLE t1; 930 931drop table t1; 932 933# 934# Bug #13446: Update to value outside of list values doesn't give error 935# 936CREATE TABLE t1 (a int) 937PARTITION BY RANGE (a) 938(PARTITION x1 VALUES LESS THAN (2)); 939 940insert into t1 values (1); 941--error ER_NO_PARTITION_FOR_GIVEN_VALUE 942update t1 set a = 5; 943 944drop table t1; 945 946# 947# Bug #13441: Analyze on partitioned table didn't work 948# 949CREATE TABLE t1 (a int) 950PARTITION BY LIST (a) 951(PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20)); 952 953analyze table t1; 954 955drop table t1; 956 957# 958# BUG 15221 (Cannot reorganize with the same name) 959# 960create table t1 961(a int) 962partition by range (a) 963 ( partition p0 values less than(10), 964 partition p1 values less than (20), 965 partition p2 values less than (25)); 966 967alter table t1 reorganize partition p2 into (partition p2 values less than (30)); 968show create table t1; 969drop table t1; 970 971CREATE TABLE t1 (a int, b int) 972PARTITION BY RANGE (a) 973(PARTITION x0 VALUES LESS THAN (2), 974 PARTITION x1 VALUES LESS THAN (4), 975 PARTITION x2 VALUES LESS THAN (6), 976 PARTITION x3 VALUES LESS THAN (8), 977 PARTITION x4 VALUES LESS THAN (10), 978 PARTITION x5 VALUES LESS THAN (12), 979 PARTITION x6 VALUES LESS THAN (14), 980 PARTITION x7 VALUES LESS THAN (16), 981 PARTITION x8 VALUES LESS THAN (18), 982 PARTITION x9 VALUES LESS THAN (20)); 983 984ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO 985(PARTITION x1 VALUES LESS THAN (6)); 986show create table t1; 987drop table t1; 988 989# Testcase for BUG#15819 990create table t1 (a int not null, b int not null) partition by LIST (a+b) ( 991 partition p0 values in (12), 992 partition p1 values in (14) 993); 994--error ER_NO_PARTITION_FOR_GIVEN_VALUE 995insert into t1 values (10,1); 996 997drop table t1; 998 999# 1000# Bug#16901 Partitions: crash, SELECT, column of part. 1001# function=first column of primary key 1002# 1003create table t1 (f1 integer,f2 integer, f3 varchar(10), primary key(f1,f2)) 1004partition by range(f1) subpartition by hash(f2) subpartitions 2 1005(partition p1 values less than (0), 1006 partition p2 values less than (2), 1007 partition p3 values less than (2147483647)); 1008 1009insert into t1 values(10,10,'10'); 1010insert into t1 values(2,2,'2'); 1011select * from t1 where f1 = 2; 1012drop table t1; 1013 1014# 1015# Bug #16907 Partitions: crash, SELECT goes into last partition, UNIQUE INDEX 1016# 1017create table t1 (f1 integer,f2 integer, unique index(f1)) 1018partition by range(f1 div 2) 1019subpartition by hash(f1) subpartitions 2 1020(partition partb values less than (2), 1021partition parte values less than (4), 1022partition partf values less than (10000)); 1023insert into t1 values(10,1); 1024select * from t1 where f1 = 10; 1025drop table t1; 1026 1027# 1028# Bug #16775: Wrong engine type stored for subpartition 1029# 1030set session default_storage_engine= 'memory'; 1031create table t1 (f_int1 int(11) default null) engine = memory 1032 partition by range (f_int1) subpartition by hash (f_int1) 1033 (partition part1 values less than (1000) 1034 (subpartition subpart11 engine = memory)); 1035drop table t1; 1036set session default_storage_engine='myisam'; 1037 1038# 1039# Bug #16782: Crash using REPLACE on table with primary key 1040# 1041create table t1 (f_int1 integer, f_int2 integer, primary key (f_int1)) 1042 partition by hash(f_int1) partitions 2; 1043insert into t1 values (1,1),(2,2); 1044replace into t1 values (1,1),(2,2); 1045drop table t1; 1046 1047# 1048# Bug #17169: Partitions: out of memory if add partition and unique 1049# 1050create table t1 (s1 int, unique (s1)) partition by list (s1) (partition x1 VALUES in (10), partition x2 values in (20)); 1051alter table t1 add partition (partition x3 values in (30)); 1052drop table t1; 1053 1054# 1055# Bug #17754 Change to explicit removal of partitioning scheme 1056# Also added a number of tests to ensure that proper engine is 1057# chosen in all kinds of scenarios. 1058# 1059 1060create table t1 (a int) 1061partition by key(a) 1062partitions 2 1063(partition p0 engine=myisam, partition p1 engine=myisam); 1064show create table t1; 1065 1066alter table t1; 1067show create table t1; 1068 1069alter table t1 engine=myisam; 1070show create table t1; 1071 1072alter table t1 engine=heap; 1073show create table t1; 1074 1075alter table t1 remove partitioning; 1076show create table t1; 1077 1078drop table t1; 1079 1080create table t1 (a int) 1081engine=myisam 1082partition by key(a) 1083partitions 2 1084(partition p0 engine=myisam, partition p1 engine=myisam); 1085show create table t1; 1086 1087alter table t1 add column b int remove partitioning; 1088show create table t1; 1089 1090alter table t1 1091engine=myisam 1092partition by key(a) 1093(partition p0 engine=myisam, partition p1); 1094show create table t1; 1095 1096alter table t1 1097engine=heap 1098partition by key(a) 1099(partition p0, partition p1 engine=heap); 1100show create table t1; 1101 1102alter table t1 engine=myisam, add column c int remove partitioning; 1103show create table t1; 1104 1105alter table t1 1106engine=heap 1107partition by key (a) 1108(partition p0, partition p1); 1109show create table t1; 1110 1111alter table t1 1112partition by key (a) 1113(partition p0, partition p1); 1114show create table t1; 1115 1116alter table t1 1117engine=heap 1118partition by key (a) 1119(partition p0, partition p1); 1120show create table t1; 1121 1122# Since alter, it already have ENGINE=HEAP from before on table level 1123# -> OK 1124alter table t1 1125partition by key(a) 1126(partition p0, partition p1 engine=heap); 1127 1128# Since alter, it already have ENGINE=HEAP from before on table level 1129# -> OK 1130alter table t1 1131partition by key(a) 1132(partition p0 engine=heap, partition p1); 1133 1134--error ER_MIX_HANDLER_ERROR 1135alter table t1 1136engine=heap 1137partition by key (a) 1138(partition p0 engine=heap, partition p1 engine=myisam); 1139 1140--error ER_MIX_HANDLER_ERROR 1141alter table t1 1142partition by key (a) 1143(partition p0 engine=heap, partition p1 engine=myisam); 1144 1145drop table t1; 1146 1147# Bug #17432: Partition functions containing NULL values should return 1148# LONGLONG_MIN 1149# 1150CREATE TABLE t1 ( 1151 f_int1 INTEGER, f_int2 INTEGER, 1152 f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000) 1153 ) 1154 PARTITION BY RANGE(f_int1 DIV 2) 1155 SUBPARTITION BY HASH(f_int1) 1156 SUBPARTITIONS 2 1157 (PARTITION parta VALUES LESS THAN (0), 1158 PARTITION partb VALUES LESS THAN (5), 1159 PARTITION parte VALUES LESS THAN (10), 1160 PARTITION partf VALUES LESS THAN (2147483647)); 1161INSERT INTO t1 SET f_int1 = NULL , f_int2 = -20, f_char1 = CAST(-20 AS CHAR), 1162 f_char2 = CAST(-20 AS CHAR), f_charbig = '#NULL#'; 1163SELECT * FROM t1 WHERE f_int1 IS NULL; 1164SELECT * FROM t1; 1165drop table t1; 1166 1167# 1168# Bug 17430: Crash when SELECT * from t1 where field IS NULL 1169# 1170 1171CREATE TABLE t1 ( 1172 f_int1 INTEGER, f_int2 INTEGER, 1173 f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000) ) 1174 PARTITION BY LIST(MOD(f_int1,2)) 1175 SUBPARTITION BY KEY(f_int1) 1176 (PARTITION part1 VALUES IN (-1) (SUBPARTITION sp1, SUBPARTITION sp2), 1177 PARTITION part2 VALUES IN (0) (SUBPARTITION sp3, SUBPARTITION sp5), 1178 PARTITION part3 VALUES IN (1) (SUBPARTITION sp4, SUBPARTITION sp6)); 1179 1180INSERT INTO t1 SET f_int1 = 2, f_int2 = 2, f_char1 = '2', f_char2 = '2', f_charbig = '===2==='; 1181INSERT INTO t1 SET f_int1 = 2, f_int2 = 2, f_char1 = '2', f_char2 = '2', f_charbig = '===2==='; 1182 1183SELECT * FROM t1 WHERE f_int1 IS NULL; 1184drop table t1; 1185 1186# 1187# Bug#14363 Partitions: failure if create in stored procedure 1188# 1189delimiter //; 1190 1191create procedure p () 1192begin 1193create table t1 (s1 mediumint,s2 mediumint) 1194partition by list (s2) 1195(partition p1 values in (0), 1196 partition p2 values in (1)); 1197end// 1198 1199call p()// 1200drop procedure p// 1201drop table t1// 1202 1203create procedure p () 1204begin 1205create table t1 (a int not null,b int not null,c int not null,primary key (a,b)) 1206partition by range (a) 1207subpartition by hash (a+b) 1208(partition x1 values less than (1) 1209 (subpartition x11, 1210 subpartition x12), 1211 partition x2 values less than (5) 1212 (subpartition x21, 1213 subpartition x22)); 1214end// 1215 1216call p()// 1217drop procedure p// 1218drop table t1// 1219delimiter ;// 1220 1221# 1222# Bug #15447 Partitions: NULL is treated as zero 1223# 1224 1225# NULL for RANGE partition 1226create table t1 (a int,b int,c int,key(a,b)) 1227partition by range (a) 1228partitions 3 1229(partition x1 values less than (0) tablespace ts1, 1230 partition x2 values less than (10) tablespace ts2, 1231 partition x3 values less than maxvalue tablespace ts3); 1232 1233insert into t1 values (NULL, 1, 1); 1234insert into t1 values (0, 1, 1); 1235insert into t1 values (12, 1, 1); 1236 1237select partition_name, partition_description, table_rows 1238from information_schema.partitions where table_schema ='test'; 1239drop table t1; 1240 1241# NULL for LIST partition 1242--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR 1243create table t1 (a int,b int, c int) 1244partition by list(a) 1245partitions 2 1246(partition x123 values in (11,12), 1247 partition x234 values in (1 ,NULL, NULL)); 1248 1249--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR 1250create table t1 (a int,b int, c int) 1251partition by list(a) 1252partitions 2 1253(partition x123 values in (11, NULL), 1254 partition x234 values in (1 ,NULL)); 1255 1256create table t1 (a int,b int, c int) 1257partition by list(a) 1258partitions 2 1259(partition x123 values in (11, 12), 1260 partition x234 values in (5, 1)); 1261--error ER_NO_PARTITION_FOR_GIVEN_VALUE 1262insert into t1 values (NULL,1,1); 1263drop table t1; 1264 1265create table t1 (a int,b int, c int) 1266partition by list(a) 1267partitions 2 1268(partition x123 values in (11, 12), 1269 partition x234 values in (NULL, 1)); 1270 1271insert into t1 values (11,1,6); 1272insert into t1 values (NULL,1,1); 1273 1274select partition_name, partition_description, table_rows 1275from information_schema.partitions where table_schema ='test'; 1276drop table t1; 1277 1278# 1279# BUG 17947 Crash with REBUILD PARTITION 1280# 1281create table t1 (a int) 1282partition by list (a) 1283(partition p0 values in (1)); 1284 1285--error ER_PARSE_ERROR 1286alter table t1 rebuild partition; 1287 1288drop table t1; 1289 1290# 1291# BUG 15253 Insert that should fail doesn't 1292# 1293create table t1 (a int) 1294partition by list (a) 1295(partition p0 values in (5)); 1296 1297--error ER_NO_PARTITION_FOR_GIVEN_VALUE 1298insert into t1 values (0); 1299 1300drop table t1; 1301 1302# 1303# BUG #16370 Subpartitions names not shown in SHOW CREATE TABLE output 1304# 1305create table t1 (a int) 1306partition by range (a) subpartition by hash (a) 1307(partition p0 values less than (100)); 1308 1309show create table t1; 1310alter table t1 add partition (partition p1 values less than (200) 1311(subpartition subpart21)); 1312 1313show create table t1; 1314 1315drop table t1; 1316 1317create table t1 (a int) 1318partition by key (a); 1319 1320show create table t1; 1321alter table t1 add partition (partition p1); 1322show create table t1; 1323 1324drop table t1; 1325 1326# 1327# BUG 15407 Crash with subpartition 1328# 1329--error ER_PARSE_ERROR 1330create table t1 (a int, b int) 1331partition by range (a) 1332subpartition by hash(a) 1333(partition p0 values less than (0) (subpartition sp0), 1334 partition p1 values less than (1)); 1335 1336--error ER_PARSE_ERROR 1337create table t1 (a int, b int) 1338partition by range (a) 1339subpartition by hash(a) 1340(partition p0 values less than (0), 1341 partition p1 values less than (1) (subpartition sp0)); 1342 1343# 1344# Bug 46354 Crash with subpartition 1345# 1346--error ER_PARSE_ERROR 1347create table t1 (a int, b int) 1348partition by list (a) 1349subpartition by hash(a) 1350(partition p0 values in (0), 1351 partition p1 values in (1) (subpartition sp0)); 1352 1353 1354# 1355# BUG 15961 No error when subpartition defined without subpartition by clause 1356# 1357--error ER_SUBPARTITION_ERROR 1358create table t1 (a int) 1359partition by hash (a) 1360(partition p0 (subpartition sp0)); 1361 1362# 1363# Bug 17127 1364# 1365create table t1 (a int) 1366partition by range (a) 1367(partition p0 values less than (1)); 1368 1369--error ER_PARTITION_WRONG_VALUES_ERROR 1370alter table t1 add partition (partition p1 values in (2)); 1371--error ER_PARTITION_REQUIRES_VALUES_ERROR 1372alter table t1 add partition (partition p1); 1373 1374drop table t1; 1375 1376create table t1 (a int) 1377partition by list (a) 1378(partition p0 values in (1)); 1379 1380--error ER_PARTITION_WRONG_VALUES_ERROR 1381alter table t1 add partition (partition p1 values less than (2)); 1382--error ER_PARTITION_REQUIRES_VALUES_ERROR 1383alter table t1 add partition (partition p1); 1384 1385drop table t1; 1386 1387create table t1 (a int) 1388partition by hash (a) 1389(partition p0); 1390 1391--error ER_PARTITION_WRONG_VALUES_ERROR 1392alter table t1 add partition (partition p1 values less than (2)); 1393--error ER_PARTITION_WRONG_VALUES_ERROR 1394alter table t1 add partition (partition p1 values in (2)); 1395 1396drop table t1; 1397 1398# 1399# BUG 17947 Crash with REBUILD PARTITION 1400# 1401create table t1 (a int) 1402partition by list (a) 1403(partition p0 values in (1)); 1404 1405--error ER_PARSE_ERROR 1406alter table t1 rebuild partition; 1407 1408drop table t1; 1409 1410# 1411# Bug #14526: Partitions: indexed searches fail 1412# 1413create 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)); 1414insert into t2 values (null),(null),(null); 1415select * from t2; 1416select * from t2 where s1 < 2; 1417update t2 set s1 = s1 + 1 order by s1 desc; 1418select * from t2 where s1 < 3; 1419select * from t2 where s1 = 2; 1420drop table t2; 1421 1422# 1423# Bug #17497: Partitions: crash if add partition on temporary table 1424# 1425--error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING 1426create temporary table t1 (a int) partition by hash(a); 1427 1428# 1429# Bug #17097: Partitions: failing ADD PRIMARY KEY leads to temporary rotten 1430# metadata,crash 1431# 1432create table t1 (a int, b int) partition by list (a) 1433 (partition p1 values in (1), partition p2 values in (2)); 1434--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 1435alter table t1 add primary key (b); 1436show create table t1; 1437drop table t1; 1438 1439############################################ 1440# 1441# Author: Mikael Ronstrom 1442# Date: 2006-03-01 1443# Purpose 1444# Bug 17772: Crash at ALTER TABLE with rename 1445# and add column + comment on 1446# partitioned table 1447# 1448############################################ 1449create table t1 (a int unsigned not null auto_increment primary key) 1450partition by key(a); 1451alter table t1 rename t2, add c char(10), comment "no comment"; 1452show create table t2; 1453 1454drop table t2; 1455 1456# 1457# Bug#15336 Partitions: crash if create table as select 1458# 1459create table t1 (f1 int) partition by hash (f1) as select 1; 1460drop table t1; 1461 1462# 1463# bug #14350 Partitions: crash if prepared statement 1464# 1465prepare stmt1 from 'create table t1 (s1 int) partition by hash (s1)'; 1466execute stmt1; 1467--error ER_TABLE_EXISTS_ERROR 1468execute stmt1; 1469drop table t1; 1470 1471# 1472# bug 17290 SP with delete, create and rollback to save point causes MySQLD core 1473# 1474delimiter |; 1475eval CREATE PROCEDURE test.p1(IN i INT) 1476BEGIN 1477 DECLARE CONTINUE HANDLER FOR sqlexception BEGIN END; 1478 DROP TABLE IF EXISTS t1; 1479 CREATE TABLE t1 (num INT,PRIMARY KEY(num)); 1480 START TRANSACTION; 1481 INSERT INTO t1 VALUES(i); 1482 savepoint t1_save; 1483 INSERT INTO t1 VALUES (14); 1484 ROLLBACK to savepoint t1_save; 1485 COMMIT; 1486END| 1487delimiter ;| 1488CALL test.p1(12); 1489CALL test.p1(13); 1490drop table t1; 1491drop procedure test.p1; 1492 1493# 1494# Bug 13520: Problem with delimiters in COMMENT DATA DIRECTORY .. 1495# 1496CREATE TABLE t1 (a int not null) 1497partition by key(a) 1498(partition p0 COMMENT='first partition'); 1499drop table t1; 1500 1501# 1502# Bug 13433: Problem with delimited identifiers 1503# 1504CREATE TABLE t1 (`a b` int not null) 1505partition by key(`a b`); 1506drop table t1; 1507 1508CREATE TABLE t1 (`a b` int not null) 1509partition by hash(`a b`); 1510drop table t1; 1511 1512# 1513# Bug#18053 Partitions: crash if null 1514# Bug#18070 Partitions: wrong result on WHERE ... IS NULL 1515# 1516create table t1 (f1 integer) partition by range(f1) 1517(partition p1 values less than (0), partition p2 values less than (10)); 1518insert into t1 set f1 = null; 1519select * from t1 where f1 is null; 1520explain partitions select * from t1 where f1 is null; 1521drop table t1; 1522 1523create table t1 (f1 integer) partition by list(f1) 1524(partition p1 values in (1), partition p2 values in (null)); 1525insert into t1 set f1 = null; 1526insert into t1 set f1 = 1; 1527select * from t1 where f1 is null or f1 = 1; 1528drop table t1; 1529 1530create table t1 (f1 smallint) 1531partition by list (f1) (partition p0 values in (null)); 1532insert into t1 values (null); 1533select * from t1 where f1 is null; 1534select * from t1 where f1 < 1; 1535select * from t1 where f1 <= NULL; 1536select * from t1 where f1 < NULL; 1537select * from t1 where f1 >= NULL; 1538select * from t1 where f1 > NULL; 1539select * from t1 where f1 > 1; 1540drop table t1; 1541 1542create table t1 (f1 smallint) 1543partition by range (f1) (partition p0 values less than (0)); 1544insert into t1 values (null); 1545select * from t1 where f1 is null; 1546drop table t1; 1547 1548create table t1 (f1 integer) partition by list(f1) 1549( 1550 partition p1 values in (1), 1551 partition p2 values in (NULL), 1552 partition p3 values in (2), 1553 partition p4 values in (3), 1554 partition p5 values in (4) 1555); 1556 1557insert into t1 values (1),(2),(3),(4),(null); 1558select * from t1 where f1 < 3; 1559explain partitions select * from t1 where f1 < 3; 1560select * from t1 where f1 is null; 1561explain partitions select * from t1 where f1 is null; 1562drop table t1; 1563 1564create table t1 (f1 int) partition by list(f1 div 2) 1565( 1566 partition p1 values in (1), 1567 partition p2 values in (NULL), 1568 partition p3 values in (2), 1569 partition p4 values in (3), 1570 partition p5 values in (4) 1571); 1572 1573insert into t1 values (2),(4),(6),(8),(null); 1574select * from t1 where f1 < 3; 1575explain partitions select * from t1 where f1 < 3; 1576select * from t1 where f1 is null; 1577explain partitions select * from t1 where f1 is null; 1578drop table t1; 1579 1580create table t1 (a int) partition by LIST(a) ( 1581 partition pn values in (NULL), 1582 partition p0 values in (0), 1583 partition p1 values in (1), 1584 partition p2 values in (2) 1585); 1586insert into t1 values (NULL),(0),(1),(2); 1587select * from t1 where a is null or a < 2; 1588explain partitions select * from t1 where a is null or a < 2; 1589select * from t1 where a is null or a < 0 or a > 1; 1590explain partitions select * from t1 where a is null or a < 0 or a > 1; 1591drop table t1; 1592 1593# 1594#Bug# 17631 SHOW TABLE STATUS reports wrong engine 1595# 1596CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, name VARCHAR(20)) 1597ENGINE=MyISAM DEFAULT CHARSET=latin1 1598PARTITION BY RANGE(id) 1599(PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM, 1600PARTITION p1 VALUES LESS THAN (20) ENGINE = MyISAM, 1601PARTITION p2 VALUES LESS THAN (30) ENGINE = MyISAM); 1602--replace_column 6 0 7 0 8 0 9 0 12 NULL 13 NULL 14 NULL 1603SHOW TABLE STATUS; 1604DROP TABLE t1; 1605 1606# 1607#BUG 16002 Erroneus handling of unsigned partition functions 1608# 1609--error ER_PARTITION_CONST_DOMAIN_ERROR 1610create table t1 (a bigint unsigned) 1611partition by list (a) 1612(partition p0 values in (0-1)); 1613 1614create table t1 (a bigint unsigned) 1615partition by range (a) 1616(partition p0 values less than (10)); 1617 1618--error ER_NO_PARTITION_FOR_GIVEN_VALUE 1619insert into t1 values (0xFFFFFFFFFFFFFFFF); 1620 1621drop table t1; 1622 1623# 1624#BUG 18750 Problems with partition names 1625# 1626create table t1 (a int) 1627partition by list (a) 1628(partition `s1 s2` values in (0)); 1629drop table t1; 1630 1631create table t1 (a int) 1632partition by list (a) 1633(partition `7` values in (0)); 1634drop table t1; 1635 1636--error ER_WRONG_PARTITION_NAME 1637create table t1 (a int) 1638partition by list (a) 1639(partition `s1 s2 ` values in (0)); 1640 1641--error ER_WRONG_PARTITION_NAME 1642create table t1 (a int) 1643partition by list (a) 1644subpartition by hash (a) 1645(partition p1 values in (0) (subpartition `p1 p2 `)); 1646 1647# 1648# BUG 18752 SHOW CREATE TABLE doesn't show NULL value in SHOW CREATE TABLE 1649# 1650CREATE TABLE t1 (a int) 1651PARTITION BY LIST (a) 1652(PARTITION p0 VALUES IN (NULL)); 1653SHOW CREATE TABLE t1; 1654DROP TABLE t1; 1655 1656--error ER_NULL_IN_VALUES_LESS_THAN 1657CREATE TABLE t1 (a int) 1658PARTITION BY RANGE(a) 1659(PARTITION p0 VALUES LESS THAN (NULL)); 1660 1661# 1662# Bug#18753 Partitions: auto_increment fails 1663# 1664create table t1 (s1 int auto_increment primary key) 1665partition by list (s1) 1666(partition p1 values in (1), 1667 partition p2 values in (2), 1668 partition p3 values in (3)); 1669insert into t1 values (null); 1670insert into t1 values (null); 1671insert into t1 values (null); 1672select auto_increment from information_schema.tables where table_name='t1'; 1673select * from t1; 1674drop table t1; 1675 1676# 1677# BUG 19140 Partitions: Create index for partitioned table crashes 1678# 1679create table t1 (a int) engine=memory 1680partition by key(a); 1681insert into t1 values (1); 1682create index inx1 on t1(a); 1683drop table t1; 1684 1685# 1686# Bug 19695 Partitions: SHOW CREATE TABLE shows table options even when it 1687# shouldn't 1688# 1689create table t1 (a int) 1690PARTITION BY KEY (a) 1691(PARTITION p0); 1692set session sql_mode='no_table_options'; 1693show create table t1; 1694set session sql_mode=''; 1695drop table t1; 1696 1697# 1698# BUG 19304 Partitions: MERGE handler not allowed in partitioned tables 1699# 1700--error ER_PARTITION_MERGE_ERROR 1701create table t1 (a int) 1702partition by key (a) 1703(partition p0 engine = MERGE); 1704 1705# 1706# BUG 19062 Partition clause ignored if CREATE TABLE ... AS SELECT ...; 1707# 1708create table t1 (a varchar(1)) 1709partition by key (a) 1710as select 'a'; 1711 1712show create table t1; 1713drop table t1; 1714 1715# 1716# BUG 19501 Partitions: SHOW TABLE STATUS shows wrong Data_free 1717# 1718CREATE TABLE t1 (a int) ENGINE = MYISAM PARTITION BY KEY(a); 1719INSERT into t1 values (1), (2); 1720--replace_column 9 0 12 NULL 13 NULL 14 NULL 1721SHOW TABLE STATUS; 1722DELETE from t1 where a = 1; 1723--replace_column 9 0 12 NULL 13 NULL 14 NULL 1724SHOW TABLE STATUS; 1725ALTER TABLE t1 OPTIMIZE PARTITION p0; 1726--replace_column 12 NULL 13 NULL 14 NULL 1727SHOW TABLE STATUS; 1728DROP TABLE t1; 1729 1730# 1731# BUG 19502: ENABLE/DISABLE Keys don't work for partitioned tables 1732# 1733CREATE TABLE t1 (a int, index(a)) PARTITION BY KEY(a); 1734ALTER TABLE t1 DISABLE KEYS; 1735ALTER TABLE t1 ENABLE KEYS; 1736DROP TABLE t1; 1737 1738# 1739# Bug 17455 Partitions: Wrong message and error when using Repair/Optimize 1740# table on partitioned table 1741# (added check/analyze for gcov of Bug#20129) 1742create table t1 (a int) 1743engine=MEMORY 1744partition by key (a); 1745 1746REPAIR TABLE t1; 1747OPTIMIZE TABLE t1; 1748CHECK TABLE t1; 1749ANALYZE TABLE t1; 1750 1751drop table t1; 1752 1753# 1754#BUG 17138 Problem with stored procedure and analyze partition 1755# 1756--disable_warnings 1757drop procedure if exists mysqltest_1; 1758--enable_warnings 1759 1760create table t1 (a int) 1761partition by list (a) 1762(partition p0 values in (0)); 1763 1764insert into t1 values (0); 1765delimiter //; 1766 1767create procedure mysqltest_1 () 1768begin 1769 begin 1770 declare continue handler for sqlexception begin end; 1771 update ignore t1 set a = 1 where a = 0; 1772 end; 1773 prepare stmt1 from 'alter table t1'; 1774 execute stmt1; 1775end// 1776 1777call mysqltest_1()// 1778delimiter ;// 1779drop table t1; 1780drop procedure mysqltest_1; 1781 1782# 1783# Bug 20583 Partitions: Crash using index_last 1784# 1785create table t1 (a int, index(a)) 1786partition by hash(a); 1787insert into t1 values (1),(2); 1788select * from t1 ORDER BY a DESC; 1789drop table t1; 1790 1791# 1792# Bug 21388: Bigint fails to find record 1793# 1794create table t1 (a bigint unsigned not null, primary key(a)) 1795engine = myisam 1796partition by key (a) 1797partitions 10; 1798 1799show create table t1; 1800insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), 1801(18446744073709551613), (18446744073709551612); 1802select * from t1; 1803select * from t1 where a = 18446744073709551615; 1804delete from t1 where a = 18446744073709551615; 1805select * from t1; 1806drop table t1; 1807 1808# 1809# Bug 24502 reorganize partition closes connection 1810# 1811CREATE TABLE t1 ( 1812 num int(11) NOT NULL, cs int(11) NOT NULL) 1813PARTITION BY RANGE (num) SUBPARTITION BY HASH ( 1814cs) SUBPARTITIONS 2 (PARTITION p_X VALUES LESS THAN MAXVALUE); 1815 1816ALTER TABLE t1 1817REORGANIZE PARTITION p_X INTO ( 1818 PARTITION p_100 VALUES LESS THAN (100), 1819 PARTITION p_X VALUES LESS THAN MAXVALUE 1820 ); 1821 1822drop table t1; 1823 1824# 1825# Bug #24186 (nested query across partitions returns fewer records) 1826# 1827 1828CREATE TABLE t2 ( 1829 taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 1830 id int(11) NOT NULL DEFAULT '0', 1831 PRIMARY KEY (id,taken), 1832 KEY taken (taken) 1833) ENGINE=MyISAM DEFAULT CHARSET=latin1; 1834 1835INSERT INTO t2 VALUES 1836('2006-09-27 21:50:01',16421), 1837('2006-10-02 21:50:01',16421), 1838('2006-09-27 21:50:01',19092), 1839('2006-09-28 21:50:01',19092), 1840('2006-09-29 21:50:01',19092), 1841('2006-09-30 21:50:01',19092), 1842('2006-10-01 21:50:01',19092), 1843('2006-10-02 21:50:01',19092), 1844('2006-09-27 21:50:01',22589), 1845('2006-09-29 21:50:01',22589); 1846 1847CREATE TABLE t1 ( 1848 id int(8) NOT NULL, 1849 PRIMARY KEY (id) 1850) ENGINE=MyISAM DEFAULT CHARSET=latin1; 1851 1852INSERT INTO t1 VALUES 1853(16421), 1854(19092), 1855(22589); 1856 1857CREATE TABLE t4 ( 1858 taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 1859 id int(11) NOT NULL DEFAULT '0', 1860 PRIMARY KEY (id,taken), 1861 KEY taken (taken) 1862) ENGINE=MyISAM DEFAULT CHARSET=latin1 1863PARTITION BY RANGE (to_days(taken)) 1864( 1865PARTITION p01 VALUES LESS THAN (732920) , 1866PARTITION p02 VALUES LESS THAN (732950) , 1867PARTITION p03 VALUES LESS THAN MAXVALUE ) ; 1868 1869INSERT INTO t4 select * from t2; 1870 1871set @f_date='2006-09-28'; 1872set @t_date='2006-10-02'; 1873 1874SELECT t1.id AS MyISAM_part 1875FROM t1 1876WHERE t1.id IN ( 1877 SELECT distinct id 1878 FROM t4 1879 WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY)) 1880ORDER BY t1.id; 1881 1882drop table t1, t2, t4; 1883 1884CREATE TABLE t1 ( 1885 taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 1886 id int(11) NOT NULL DEFAULT '0', 1887 status varchar(20) NOT NULL DEFAULT '', 1888 PRIMARY KEY (id,taken) 1889) ENGINE=MyISAM DEFAULT CHARSET=latin1 1890PARTITION BY RANGE (to_days(taken)) 1891( 1892PARTITION p15 VALUES LESS THAN (732950) , 1893PARTITION p16 VALUES LESS THAN MAXVALUE ) ; 1894 1895 1896INSERT INTO t1 VALUES 1897('2006-09-27 21:50:01',22589,'Open'), 1898('2006-09-29 21:50:01',22589,'Verified'); 1899 1900DROP TABLE IF EXISTS t2; 1901CREATE TABLE t2 ( 1902 id int(8) NOT NULL, 1903 severity tinyint(4) NOT NULL DEFAULT '0', 1904 priority tinyint(4) NOT NULL DEFAULT '0', 1905 status varchar(20) DEFAULT NULL, 1906 alien tinyint(4) NOT NULL 1907) ENGINE=MyISAM DEFAULT CHARSET=latin1; 1908 1909INSERT INTO t2 VALUES 1910(22589,1,1,'Need Feedback',0); 1911 1912SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified'); 1913 1914drop table t1, t2; 1915 1916# 1917# Bug #27123 partition + on duplicate key update + varchar = Can't find record in <table> 1918# 1919create table t1 (c1 varchar(255),c2 tinyint,primary key(c1)) 1920 partition by key (c1) partitions 10 ; 1921insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1; 1922insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1; 1923select * from t1; 1924drop table t1; 1925 1926# 1927# Bug #28005 Partitions: can't use -9223372036854775808 1928# 1929 1930create table t1 (s1 bigint) partition by list (s1) (partition p1 values in (-9223372036854775808)); 1931drop table t1; 1932 1933# 1934# Bug #28806: Running SHOW TABLE STATUS during high INSERT load crashes server 1935# 1936create table t1(a int auto_increment, b int, primary key (b, a)) 1937 partition by hash(b) partitions 2; 1938insert into t1 values (null, 1); 1939--replace_column 9 0 12 NULL 13 NULL 14 NULL 1940show table status; 1941drop table t1; 1942 1943create table t1(a int auto_increment primary key) 1944 partition by key(a) partitions 2; 1945insert into t1 values (null), (null), (null); 1946--replace_column 9 0 12 NULL 13 NULL 14 NULL 1947show table status; 1948drop table t1; 1949# Bug #28488: Incorrect information in file: './test/t1_test#.frm' 1950# 1951 1952CREATE TABLE t1(a INT NOT NULL, b TINYBLOB, KEY(a)) 1953 PARTITION BY RANGE(a) ( PARTITION p0 VALUES LESS THAN (32)); 1954INSERT INTO t1 VALUES (1, REPEAT('a', 10)); 1955INSERT INTO t1 SELECT a + 1, b FROM t1; 1956INSERT INTO t1 SELECT a + 2, b FROM t1; 1957INSERT INTO t1 SELECT a + 4, b FROM t1; 1958INSERT INTO t1 SELECT a + 8, b FROM t1; 1959 1960ALTER TABLE t1 ADD PARTITION (PARTITION p1 VALUES LESS THAN (64)); 1961ALTER TABLE t1 DROP PARTITION p1; 1962 1963DROP TABLE t1; 1964 1965# 1966# Bug #30484: Partitions: crash with self-referencing trigger 1967# 1968 1969create table t (s1 int) engine=myisam partition by key (s1); 1970create trigger t_ad after delete on t for each row insert into t values (old.s1); 1971insert into t values (1); 1972drop table t; 1973 1974# 1975# Bug #27084 partitioning by list seems failing when using case 1976# BUG #18198: Case no longer supported, test case removed 1977# 1978 1979create table t2 (b int); 1980--error ER_BAD_FIELD_ERROR 1981create table t1 (b int) 1982PARTITION BY RANGE (t2.b) ( 1983 PARTITION p1 VALUES LESS THAN (10), 1984 PARTITION p2 VALUES LESS THAN (20) 1985) select * from t2; 1986create table t1 (a int) 1987PARTITION BY RANGE (b) ( 1988 PARTITION p1 VALUES LESS THAN (10), 1989 PARTITION p2 VALUES LESS THAN (20) 1990) select * from t2; 1991show create table t1; 1992drop table t1, t2; 1993 1994# 1995# Bug #32067 Partitions: crash with timestamp column 1996# this bug occurs randomly on some UPDATE statement 1997# with the '1032: Can't find record in 't1'' error 1998 1999create table t1 2000 (s1 timestamp on update current_timestamp, s2 int) 2001 partition by key(s1) partitions 3; 2002 2003insert into t1 values (null,null); 2004--disable_query_log 2005begin; 2006let $cnt= 1000; 2007while ($cnt) 2008{ 2009 update t1 set s2 = 1; 2010 update t1 set s2 = 2; 2011 dec $cnt; 2012} 2013commit; 2014--enable_query_log 2015 2016drop table t1; 2017 2018# 2019# BUG#32772: partition crash 1: enum column 2020# 2021# Note that month(int_col) is disallowed after bug#54483. 2022create table t1 ( 2023 c0 int, 2024 c1 bigint, 2025 c2 set('sweet'), 2026 key (c2,c1,c0), 2027 key(c0) 2028) engine=myisam partition by hash (c0) partitions 5; 2029 2030--disable_warnings 2031insert ignore into t1 set c0 = -6502262, c1 = 3992917, c2 = 35019; 2032insert ignore into t1 set c0 = 241221, c1 = -6862346, c2 = 56644; 2033--enable_warnings 2034# This must not fail assert: 2035select c1 from t1 group by (select c0 from t1 limit 1); 2036drop table t1; 2037 2038# Bug #30495: optimize table t1,t2,t3 extended errors 2039# (added more maintenace commands for Bug#20129 2040CREATE TABLE t1(a int) 2041PARTITION BY RANGE (a) ( 2042 PARTITION p1 VALUES LESS THAN (10), 2043 PARTITION p2 VALUES LESS THAN (20) 2044); 2045--error ER_PARSE_ERROR 2046ALTER TABLE t1 OPTIMIZE PARTITION p1 EXTENDED; 2047--error ER_PARSE_ERROR 2048ALTER TABLE t1 ANALYZE PARTITION p1 EXTENDED; 2049ALTER TABLE t1 ANALYZE PARTITION p1; 2050ALTER TABLE t1 CHECK PARTITION p1; 2051ALTER TABLE t1 REPAIR PARTITION p1; 2052ALTER TABLE t1 OPTIMIZE PARTITION p1; 2053DROP TABLE t1; 2054 2055# 2056# Bug #29258: Partitions: search fails for maximum unsigned bigint 2057# 2058CREATE TABLE t1 (s1 BIGINT UNSIGNED) 2059 PARTITION BY RANGE (s1) ( 2060 PARTITION p0 VALUES LESS THAN (0), 2061 PARTITION p1 VALUES LESS THAN (1), 2062 PARTITION p2 VALUES LESS THAN (18446744073709551615) 2063); 2064INSERT INTO t1 VALUES (0), (18446744073709551614); 2065--error ER_NO_PARTITION_FOR_GIVEN_VALUE 2066INSERT INTO t1 VALUES (18446744073709551615); 2067DROP TABLE t1; 2068 2069CREATE TABLE t1 (s1 BIGINT UNSIGNED) 2070 PARTITION BY RANGE (s1) ( 2071 PARTITION p0 VALUES LESS THAN (0), 2072 PARTITION p1 VALUES LESS THAN (1), 2073 PARTITION p2 VALUES LESS THAN (18446744073709551614), 2074 PARTITION p3 VALUES LESS THAN MAXVALUE 2075); 2076INSERT INTO t1 VALUES (-1), (0), (18446744073709551613), 2077 (18446744073709551614), (18446744073709551615); 2078SELECT * FROM t1; 2079SELECT * FROM t1 WHERE s1 = 0; 2080SELECT * FROM t1 WHERE s1 = 18446744073709551614; 2081SELECT * FROM t1 WHERE s1 = 18446744073709551615; 2082DROP TABLE t1; 2083 2084CREATE TABLE t1 (s1 BIGINT UNSIGNED) 2085 PARTITION BY RANGE (s1) ( 2086 PARTITION p0 VALUES LESS THAN (0), 2087 PARTITION p1 VALUES LESS THAN (1), 2088 PARTITION p2 VALUES LESS THAN (18446744073709551615), 2089 PARTITION p3 VALUES LESS THAN MAXVALUE 2090); 2091DROP TABLE t1; 2092 2093# 2094# Bug #31890 Partitions: ORDER BY DESC in InnoDB not working 2095# 2096 2097CREATE TABLE t1 2098(int_column INT, char_column CHAR(5), 2099PRIMARY KEY(char_column,int_column)) 2100PARTITION BY KEY(char_column,int_column) 2101PARTITIONS 101; 2102INSERT INTO t1 (int_column, char_column) VALUES 2103( 39868 ,'zZZRW'), 2104( 545592 ,'zZzSD'), 2105( 4936 ,'zzzsT'), 2106( 9274 ,'ZzZSX'), 2107( 970185 ,'ZZzTN'), 2108( 786036 ,'zZzTO'), 2109( 37240 ,'zZzTv'), 2110( 313801 ,'zzzUM'), 2111( 782427 ,'ZZZva'), 2112( 907955 ,'zZZvP'), 2113( 453491 ,'zzZWV'), 2114( 756594 ,'ZZZXU'), 2115( 718061 ,'ZZzZH'); 2116SELECT * FROM t1 ORDER BY char_column DESC; 2117DROP TABLE t1; 2118 2119# 2120# Bug #32247 Test reports wrong value of "AUTO_INCREMENT" (on a partitioned InnoDB table) 2121# 2122 2123CREATE TABLE t1(id MEDIUMINT NOT NULL AUTO_INCREMENT, 2124 user CHAR(25), PRIMARY KEY(id)) 2125 PARTITION BY RANGE(id) 2126 SUBPARTITION BY hash(id) subpartitions 2 2127 (PARTITION pa1 values less than (10), 2128 PARTITION pa2 values less than (20), 2129 PARTITION pa11 values less than MAXVALUE); 2130--disable_query_log 2131let $n= 15; 2132begin; 2133while ($n) 2134{ 2135 insert into t1 (user) values ('mysql'); 2136 dec $n; 2137} 2138commit; 2139--enable_query_log 2140show create table t1; 2141drop table t1; 2142 2143# 2144# Bug #38272 timestamps fields incorrectly defaulted on update accross partitions. 2145# 2146 2147CREATE TABLE t1 ( 2148 `ID` bigint(20) NOT NULL AUTO_INCREMENT, 2149 `createdDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 2150 `number` int, 2151 PRIMARY KEY (`ID`, number) 2152) 2153PARTITION BY RANGE (number) ( 2154 PARTITION p0 VALUES LESS THAN (6), 2155 PARTITION p1 VALUES LESS THAN (11) 2156); 2157 2158create table t2 ( 2159 `ID` bigint(20), 2160 `createdDate` TIMESTAMP, 2161 `number` int 2162); 2163 2164INSERT INTO t1 SET number=1; 2165insert into t2 select * from t1; 2166SELECT SLEEP(1); 2167UPDATE t1 SET number=6; 2168select count(*) from t1, t2 where t1.createdDate = t2.createdDate; 2169 2170drop table t1, t2; 2171 2172# 2173# Bug #38083 Error-causing row inserted into partitioned table despite error 2174# 2175SET @orig_sql_mode = @@SQL_MODE; 2176SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO'; 2177CREATE TABLE t1 (c1 INT) 2178 PARTITION BY LIST(1 DIV c1) ( 2179 PARTITION p0 VALUES IN (NULL), 2180 PARTITION p1 VALUES IN (1) 2181 ); 2182 2183-- error ER_DIVISION_BY_ZERO 2184INSERT INTO t1 VALUES (0); 2185SELECT * FROM t1; 2186TRUNCATE t1; 2187-- error ER_DIVISION_BY_ZERO 2188INSERT INTO t1 VALUES (NULL), (0), (1), (2); 2189SELECT * FROM t1; 2190DROP TABLE t1; 2191SET SQL_MODE= @orig_sql_mode; 2192 2193 2194 2195# 2196# Bug #38005 Partitions: error with insert select 2197# 2198 2199create table t1 (s1 int) partition by hash(s1) partitions 2; 2200create index i on t1 (s1); 2201insert into t1 values (1); 2202insert into t1 select s1 from t1; 2203insert into t1 select s1 from t1; 2204insert into t1 select s1 from t1 order by s1 desc; 2205select * from t1; 2206drop table t1; 2207 2208create table t1 (s1 int) partition by range(s1) 2209 (partition pa1 values less than (10), 2210 partition pa2 values less than MAXVALUE); 2211create index i on t1 (s1); 2212insert into t1 values (1); 2213insert into t1 select s1 from t1; 2214insert into t1 select s1 from t1; 2215insert into t1 select s1 from t1 order by s1 desc; 2216select * from t1; 2217drop table t1; 2218 2219create table t1 (s1 int) partition by range(s1) 2220 (partition pa1 values less than (10), 2221 partition pa2 values less than MAXVALUE); 2222create index i on t1 (s1); 2223insert into t1 values (20); 2224insert into t1 select s1 from t1; 2225insert into t1 select s1 from t1; 2226insert into t1 select s1 from t1 order by s1 desc; 2227select * from t1; 2228drop table t1; 2229 2230create table t1 (s1 int) partition by range(s1) 2231 (partition pa1 values less than (10), 2232 partition pa2 values less than MAXVALUE); 2233create index i on t1 (s1); 2234insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8); 2235insert into t1 select s1 from t1; 2236insert into t1 select s1 from t1; 2237insert into t1 select s1 from t1; 2238insert into t1 select s1 from t1; 2239insert into t1 select s1 from t1 order by s1 desc; 2240insert into t1 select s1 from t1 where s1=3; 2241select count(*) from t1; 2242drop table t1; 2243 2244 2245--echo # 2246--echo # Bug#42944: partition not pruned correctly 2247--echo # 2248CREATE TABLE t1 (a int) PARTITION BY RANGE (a) 2249 (PARTITION p0 VALUES LESS THAN (100), 2250 PARTITION p1 VALUES LESS THAN (200), 2251 PARTITION p2 VALUES LESS THAN (300), 2252 PARTITION p3 VALUES LESS THAN MAXVALUE); 2253INSERT INTO t1 VALUES (10), (100), (200), (300), (400); 2254EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a>=200; 2255DROP TABLE t1; 2256 2257# 2258# Bug#44821: select distinct on partitioned table returns wrong results 2259# 2260CREATE TABLE t1 ( a INT, b INT, c INT, KEY bc(b, c) ) 2261PARTITION BY KEY (a, b) PARTITIONS 3 2262; 2263 2264INSERT INTO t1 VALUES 2265(17, 1, -8), 2266(3, 1, -7), 2267(23, 1, -6), 2268(22, 1, -5), 2269(11, 1, -4), 2270(21, 1, -3), 2271(19, 1, -2), 2272(30, 1, -1), 2273 2274(20, 1, 1), 2275(16, 1, 2), 2276(18, 1, 3), 2277(9, 1, 4), 2278(15, 1, 5), 2279(28, 1, 6), 2280(29, 1, 7), 2281(25, 1, 8), 2282(10, 1, 9), 2283(13, 1, 10), 2284(27, 1, 11), 2285(24, 1, 12), 2286(12, 1, 13), 2287(26, 1, 14), 2288(14, 1, 15) 2289; 2290 2291SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c; 2292 2293EXPLAIN 2294SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c; 2295 2296EXPLAIN 2297SELECT b, c FROM t1 WHERE b = 1 or b=2 GROUP BY b, c; 2298 2299DROP TABLE t1; 2300 2301--echo # 2302--echo # Bug #45807: crash accessing partitioned table and sql_mode 2303--echo # contains ONLY_FULL_GROUP_BY 2304--echo # Bug#46923: select count(*) from partitioned table fails with 2305--echo # ONLY_FULL_GROUP_BY 2306--echo # 2307 2308SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'; 2309CREATE TABLE t1(id INT,KEY(id)) ENGINE=MYISAM 2310 PARTITION BY HASH(id) PARTITIONS 2; 2311SELECT COUNT(*) FROM t1; 2312DROP TABLE t1; 2313SET SESSION SQL_MODE=DEFAULT; 2314 2315# This testcase is commented due to the Bug #46853 2316# Should be uncommented after fixing Bug #46853 2317#--echo # 2318#--echo # BUG#45816 - assertion failure with index containing double 2319#--echo # column on partitioned table 2320#--echo # 2321# 2322#CREATE TABLE t1 ( 2323# a INT DEFAULT NULL, 2324# b DOUBLE DEFAULT NULL, 2325# c INT DEFAULT NULL, 2326# KEY idx2(b,a) 2327#) PARTITION BY HASH(c) PARTITIONS 3; 2328# 2329#INSERT INTO t1 VALUES (6,8,9); 2330#INSERT INTO t1 VALUES (6,8,10); 2331# 2332#SELECT 1 FROM t1 JOIN t1 AS t2 USING (a) FOR UPDATE; 2333# 2334#DROP TABLE t1; 2335 2336--echo # 2337--echo # Bug#46198: Hang after failed ALTER TABLE on partitioned table. 2338--echo # 2339 2340--disable_warnings 2341DROP TABLE IF EXISTS t1; 2342--enable_warnings 2343 2344# 2345# Case 1. 2346# 2347 2348CREATE TABLE t1 (s1 INT PRIMARY KEY) PARTITION BY HASH(s1); 2349 2350LOCK TABLES t1 WRITE, t1 b READ; 2351 2352UNLOCK TABLES; 2353 2354--error ER_ONLY_ON_RANGE_LIST_PARTITION 2355ALTER TABLE t1 DROP PARTITION p1; 2356 2357# The SELECT below used to hang in tdc_wait_for_old_versions(). 2358SELECT * FROM t1; 2359 2360DROP TABLE t1; 2361 2362# 2363# Case 2. 2364# 2365 2366CREATE TABLE t1 (s1 VARCHAR(5) PRIMARY KEY) PARTITION BY KEY(s1); 2367 2368LOCK TABLES t1 WRITE, t1 b READ; 2369 2370UNLOCK TABLES; 2371 2372--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 2373ALTER TABLE t1 ADD COLUMN (s3 VARCHAR(5) UNIQUE); 2374 2375# The SELECT below used to hang in tdc_wait_for_old_versions(). 2376SELECT * FROM t1; 2377 2378DROP TABLE t1; 2379 2380--echo # 2381--echo # BUG#51868 - crash with myisam_use_mmap and partitioned myisam tables 2382--echo # 2383SET GLOBAL myisam_use_mmap=1; 2384CREATE TABLE t1(a INT) PARTITION BY HASH(a) PARTITIONS 1; 2385INSERT INTO t1 VALUES(0); 2386FLUSH TABLE t1; 2387TRUNCATE TABLE t1; 2388INSERT INTO t1 VALUES(0); 2389DROP TABLE t1; 2390SET GLOBAL myisam_use_mmap=default; 2391 2392--echo # 2393--echo # Bug#13580775 ASSERTION FAILED: RECORD_LENGTH == M_RECORD_LENGTH, 2394--echo # FILE FILESORT_UTILS.CC 2395--echo # 2396 2397CREATE TABLE t1 ( 2398 a INT PRIMARY KEY, 2399 b INT, 2400 c CHAR(1), 2401 d INT, 2402 KEY (c,d) 2403) PARTITION BY KEY () PARTITIONS 1; 2404 2405INSERT INTO t1 VALUES (1,1,'a',1), (2,2,'a',1); 2406 2407SELECT 1 FROM t1 WHERE 1 IN 2408(SELECT group_concat(b) 2409 FROM t1 2410 WHERE c > geomfromtext('point(1 1)') 2411 GROUP BY b 2412); 2413 2414DROP TABLE t1; 2415 2416--echo # 2417--echo # Bug#13011410 CRASH IN FILESORT CODE WITH GROUP BY/ROLLUP 2418--echo # 2419 2420CREATE TABLE t1 ( 2421 a INT, 2422 b MEDIUMINT, 2423 c VARCHAR(300) CHARACTER SET hp8 COLLATE hp8_bin, 2424 PRIMARY KEY (a,c(299))) 2425ENGINE=myisam 2426PARTITION BY LINEAR KEY () PARTITIONS 2; 2427 2428INSERT INTO t1 VALUES (1,2,'test'), (2,3,'hi'), (4,5,'bye'); 2429--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION 2430SELECT 1 FROM t1 WHERE b < SOME 2431( SELECT 1 FROM t1 WHERE a >= 1 2432 GROUP BY b WITH ROLLUP 2433 HAVING b > geomfromtext("") 2434); 2435 2436DROP TABLE t1; 2437 2438--echo 2439--echo MDEV-612 Valgrind error in ha_maria::check_if_incompatible_data 2440--echo 2441 2442CREATE TABLE t1 (a INT, b INT, KEY(a)) ENGINE=Aria PARTITION BY KEY(a) PARTITIONS 2; 2443ALTER TABLE t1 ADD KEY (b); 2444drop table t1; 2445 2446--echo End of 5.1 tests 2447 2448--echo # 2449--echo # BUG#55385: UPDATE statement throws an error, but still updates 2450--echo # the table entries 2451 2452CREATE TABLE t1_part ( 2453 partkey int, 2454 nokey int 2455) PARTITION BY LINEAR HASH(partkey) PARTITIONS 3; 2456 2457INSERT INTO t1_part VALUES (1, 1) , (10, 10); 2458CREATE VIEW v1 AS SELECT * FROM t1_part; 2459 2460--echo 2461--echo # Should be (1,1),(10,10) 2462SELECT * FROM t1_part; 2463 2464--echo 2465--echo # Case 1 2466--echo # Update is refused because partitioning key is updated 2467--error ER_MULTI_UPDATE_KEY_CONFLICT 2468UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.partkey = 2, B.nokey = 3; 2469--error ER_MULTI_UPDATE_KEY_CONFLICT 2470UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2, B.partkey = 3; 2471 2472--echo 2473--echo # Case 2 2474--echo # Like 1, but partition accessed through a view 2475--error ER_MULTI_UPDATE_KEY_CONFLICT 2476UPDATE t1_part AS A NATURAL JOIN v1 as B SET A.nokey = 2 , B.partkey = 3; 2477--error ER_MULTI_UPDATE_KEY_CONFLICT 2478UPDATE v1 AS A NATURAL JOIN t1_part as B SET A.nokey = 2 , B.partkey = 3; 2479 2480--echo 2481--echo # Should be (1,1),(10,10) 2482SELECT * FROM t1_part; 2483 2484--echo 2485--echo # Case 3 2486--echo # Update is accepted because partitioning key is not updated 2487UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2 , B.nokey = 3; 2488 2489--echo 2490--echo # Should be (1,3),(10,3) 2491SELECT * FROM t1_part; 2492 2493--echo 2494# Cleanup 2495DROP VIEW v1; 2496DROP TABLE t1_part; 2497 2498--echo # 2499--echo # BUG#598247: partition.test produces valgrind errors in 5.3-based branches 2500--echo # 2501CREATE TABLE t1 ( 2502 a INT DEFAULT NULL, 2503 b DOUBLE DEFAULT NULL, 2504 c INT DEFAULT NULL, 2505 KEY idx2(b,a) 2506) engine=myisam PARTITION BY HASH(c) PARTITIONS 3; 2507 2508INSERT INTO t1 VALUES (6,8,9); 2509INSERT INTO t1 VALUES (6,8,10); 2510 2511SELECT 1 FROM t1 JOIN t1 AS t2 USING (a); 2512 2513drop table t1; 2514 2515--echo # 2516--echo # LP BUG#1001117 Crash on a simple select that uses a temptable view 2517--echo # MySQL Bug #12330344 Crash and/or valgrind errors in free_io_cache with join, view, 2518--echo # partitioned table 2519--echo # 2520 2521CREATE TABLE t1(a INT PRIMARY KEY) PARTITION BY LINEAR KEY (a); 2522CREATE ALGORITHM=TEMPTABLE VIEW vtmp AS 2523SELECT 1 FROM t1 AS t1_0 JOIN t1 ON t1_0.a LIKE (SELECT 1 FROM t1); 2524SELECT * FROM vtmp; 2525DROP VIEW vtmp; 2526DROP TABLE t1; 2527 2528--echo # 2529--echo # MDEV-365 "Got assertion when doing alter table on a partition" 2530--echo # 2531 2532CREATE TABLE t1 ( i INT ) ENGINE=Aria PARTITION BY HASH(i) PARTITIONS 2; 2533INSERT INTO t1 VALUES (1),(2),(2),(3),(4); 2534ALTER TABLE t1 ADD PARTITION PARTITIONS 2; 2535SELECT * from t1 order by i; 2536DROP TABLE t1; 2537 2538--echo # 2539--echo # MDEV-5555: Incorrect index_merge on BTREE indices 2540--echo # 2541 2542CREATE TABLE t1 ( 2543 id bigint(20) unsigned NOT NULL, 2544 id2 bigint(20) unsigned NOT NULL, 2545 dob date DEFAULT NULL, 2546 address char(100) DEFAULT NULL, 2547 city char(35) DEFAULT NULL, 2548 hours_worked_per_week smallint(5) unsigned DEFAULT NULL, 2549 weeks_worked_last_year tinyint(3) unsigned DEFAULT NULL, 2550 KEY dob (dob), 2551 KEY address (address), 2552 KEY city (city), 2553 KEY hours_worked_per_week (hours_worked_per_week), 2554 KEY weeks_worked_last_year (weeks_worked_last_year) 2555) ENGINE=MyISAM DEFAULT CHARSET=latin1 2556PARTITION BY KEY (id) PARTITIONS 5; 2557 2558--echo # Insert some rows 2559--disable_query_log 2560INSERT INTO t1 VALUES (123,123,'2001-04-14','address123','city123',40,51), 2561(127,127,'1977-03-30','address127','city127',0,0), 2562(131,131,'1985-07-29','address131','city131',50,52), 2563(135,135,'1997-01-20','address135','city135',0,0), 2564(139,139,'1963-04-27','address139','city139',27,52), 2565(143,143,'1979-01-28','address143','city143',40,52), 2566(147,147,'1985-08-28','address147','city147',0,0), 2567(151,151,'1997-01-24','address151','city151',40,52), 2568(156,156,'1975-02-19','address156','city156',46,52), 2569(158,158,'1996-07-06','address158','city158',46,8), 2570(164,164,'1925-12-30','address164','city164',0,0), 2571(166,166,'2010-12-30','address166','city166',0,0), 2572(172,172,'1996-08-15','address172','city172',0,0), 2573(174,174,'2008-05-20','address174','city174',40,52), 2574(180,180,'1969-09-05','address180','city180',25,52), 2575(182,182,'1977-08-11','address182','city182',40,8), 2576(188,188,'2012-03-29','address188','city188',0,0), 2577(190,190,'1978-02-19','address190','city190',0,0), 2578(215,215,'1982-02-07','address215','city215',40,36), 2579(223,223,'2005-02-11','address223','city223',55,52), 2580(247,247,'2005-07-02','address247','city247',30,51), 2581(255,255,'1997-08-15','address255','city255',0,0), 2582(2,2,'1973-05-05','address2','city2',25,52), 2583(4,4,'2012-07-21','address4','city4',40,12), 2584(6,6,'1982-07-15','address6','city6',0,0), 2585(8,8,'1979-02-16','address8','city8',0,0), 2586(10,10,'1955-10-06','address10','city10',60,52), 2587(12,12,'1977-08-09','address12','city12',40,52), 2588(14,14,'2005-03-28','address14','city14',0,0), 2589(16,16,'1949-11-07','address16','city16',40,52), 2590(18,18,'2012-01-04','address18','city18',0,0), 2591(20,20,'2011-01-23','address20','city20',0,0), 2592(22,22,'1954-10-14','address22','city22',20,52), 2593(24,24,'2010-01-22','address24','city24',0,0), 2594(26,26,'1999-08-15','address26','city26',0,0), 2595(28,28,'1964-07-05','address28','city28',10,20), 2596(30,30,'2004-10-13','address30','city30',0,0), 2597(32,32,'2007-06-08','address32','city32',0,0), 2598(34,34,'1977-02-23','address34','city34',40,52), 2599(36,36,'2007-06-11','address36','city36',75,52), 2600(38,38,'1932-04-12','address38','city38',0,0), 2601(40,40,'1968-11-16','address40','city40',0,0), 2602(42,42,'1996-10-01','address42','city42',38,52), 2603(44,44,'1977-08-23','address44','city44',18,6), 2604(46,46,'1978-11-23','address46','city46',0,0), 2605(48,48,'1998-02-27','address48','city48',0,0), 2606(50,50,'1923-09-08','address50','city50',40,52), 2607(52,52,'1964-09-09','address52','city52',0,0), 2608(55,55,'2001-01-27','address55','city55',40,40), 2609(56,56,'1938-08-28','address56','city56',48,52), 2610(59,59,'1967-12-19','address59','city59',40,52), 2611(60,60,'1969-06-30','address60','city60',40,46), 2612(63,63,'2002-04-05','address63','city63',0,0), 2613(64,64,'1972-11-21','address64','city64',48,52), 2614(67,67,'1988-04-04','address67','city67',0,0), 2615(68,68,'1964-07-14','address68','city68',4,16), 2616(71,71,'1998-03-09','address71','city71',0,0), 2617(72,72,'1960-10-28','address72','city72',35,52), 2618(75,75,'1968-04-14','address75','city75',40,52), 2619(76,76,'1977-05-13','address76','city76',0,0), 2620(79,79,'1982-12-19','address79','city79',0,0), 2621(80,80,'1966-01-07','address80','city80',15,12), 2622(83,83,'1947-02-09','address83','city83',10,18), 2623(84,84,'1976-06-23','address84','city84',40,52), 2624(87,87,'2000-10-24','address87','city87',25,4), 2625(88,88,'2002-05-23','address88','city88',20,52), 2626(91,91,'2000-11-28','address91','city91',60,52), 2627(92,92,'1965-07-17','address92','city92',40,52), 2628(95,95,'1977-09-16','address95','city95',30,52), 2629(96,96,'1994-09-26','address96','city96',0,0), 2630(99,99,'2008-02-19','address99','city99',0,0), 2631(100,100,'1953-01-07','address100','city100',0,0), 2632(103,103,'2010-12-29','address103','city103',0,0), 2633(104,104,'1990-12-03','address104','city104',40,52), 2634(107,107,'2003-10-27','address107','city107',0,0), 2635(108,108,'1998-03-05','address108','city108',40,17), 2636(111,111,'2002-10-18','address111','city111',0,0), 2637(112,112,'1960-04-02','address112','city112',0,0), 2638(115,115,'1989-05-28','address115','city115',40,52), 2639(116,116,'1985-10-25','address116','city116',15,52), 2640(119,119,'1974-04-15','address119','city119',0,0), 2641(120,120,'1926-03-21','address120','city120',0,0), 2642(157,157,'1972-03-23','address157','city157',0,0), 2643(159,159,'2002-11-08','address159','city159',0,0), 2644(165,165,'1998-07-10','address165','city165',0,0), 2645(167,167,'1973-11-16','address167','city167',0,0), 2646(173,173,'1966-06-26','address173','city173',0,0), 2647(175,175,'1957-02-02','address175','city175',0,0), 2648(181,181,'1964-11-16','address181','city181',45,26), 2649(183,183,'1943-12-02','address183','city183',0,0), 2650(189,189,'1986-06-30','address189','city189',0,0), 2651(191,191,'2005-05-14','address191','city191',0,0), 2652(196,196,'1961-03-23','address196','city196',0,0), 2653(197,197,'1955-07-13','address197','city197',0,0), 2654(198,198,'2006-11-26','address198','city198',0,0), 2655(199,199,'1978-02-06','address199','city199',0,0), 2656(208,208,'2012-04-13','address208','city208',48,52), 2657(210,210,'1989-08-18','address210','city210',0,0), 2658(211,211,'1982-08-17','address211','city211',40,52), 2659(212,212,'1919-08-29','address212','city212',0,0), 2660(213,213,'1987-03-25','address213','city213',0,0), 2661(228,228,'1988-05-05','address228','city228',40,52), 2662(229,229,'1936-10-15','address229','city229',0,0), 2663(230,230,'1973-08-19','address230','city230',40,52), 2664(231,231,'2002-06-18','address231','city231',50,52), 2665(240,240,'2011-10-17','address240','city240',60,52), 2666(242,242,'1981-07-24','address242','city242',0,0), 2667(243,243,'1978-10-12','address243','city243',0,0), 2668(244,244,'2003-01-15','address244','city244',0,0), 2669(245,245,'1950-09-26','address245','city245',0,0), 2670(125,125,'1939-08-02','address125','city125',28,32), 2671(126,126,'1984-02-10','address126','city126',0,0), 2672(129,129,'1992-01-20','address129','city129',0,0), 2673(130,130,'1992-09-18','address130','city130',0,0), 2674(133,133,'1996-05-07','address133','city133',24,20), 2675(134,134,'1987-07-13','address134','city134',0,0), 2676(137,137,'2004-03-10','address137','city137',0,0), 2677(138,138,'1989-02-10','address138','city138',0,0), 2678(141,141,'1970-03-21','address141','city141',0,0), 2679(142,142,'1984-05-25','address142','city142',40,50), 2680(145,145,'1959-05-24','address145','city145',0,0), 2681(146,146,'1946-07-28','address146','city146',35,16), 2682(149,149,'1993-09-16','address149','city149',0,0), 2683(150,150,'1975-12-18','address150','city150',0,0), 2684(153,153,'1993-12-20','address153','city153',0,0), 2685(155,155,'1934-10-29','address155','city155',0,0), 2686(161,161,'1969-11-04','address161','city161',50,50), 2687(163,163,'1976-05-03','address163','city163',40,52), 2688(169,169,'1982-12-19','address169','city169',0,0), 2689(171,171,'1976-07-01','address171','city171',0,0), 2690(177,177,'2002-11-16','address177','city177',0,0), 2691(179,179,'1964-02-05','address179','city179',40,32), 2692(185,185,'1981-02-06','address185','city185',0,0), 2693(187,187,'1962-06-04','address187','city187',40,52), 2694(216,216,'1996-05-21','address216','city216',48,52), 2695(248,248,'1963-09-06','address248','city248',0,0), 2696(256,256,'1966-07-14','address256','city256',0,0), 2697(53,53,'1992-05-25','address53','city53',0,0), 2698(57,57,'2003-11-12','address57','city57',25,20), 2699(61,61,'1953-01-29','address61','city61',0,0), 2700(65,65,'1975-05-02','address65','city65',10,10), 2701(69,69,'1938-03-20','address69','city69',0,0), 2702(73,73,'1969-05-05','address73','city73',0,0), 2703(77,77,'1996-05-19','address77','city77',0,0), 2704(81,81,'1985-06-22','address81','city81',0,0), 2705(85,85,'2002-10-10','address85','city85',0,0), 2706(89,89,'1958-06-16','address89','city89',0,0), 2707(93,93,'1962-06-16','address93','city93',0,0), 2708(97,97,'1964-10-08','address97','city97',0,0), 2709(101,101,'1986-06-11','address101','city101',40,52), 2710(105,105,'1999-05-14','address105','city105',40,45), 2711(109,109,'2000-05-23','address109','city109',0,0), 2712(113,113,'1960-08-03','address113','city113',8,15), 2713(117,117,'1982-02-15','address117','city117',50,36), 2714(121,121,'1998-10-18','address121','city121',24,52), 2715(192,192,'1964-07-24','address192','city192',40,52), 2716(193,193,'1973-05-03','address193','city193',0,0), 2717(194,194,'1980-01-14','address194','city194',40,52), 2718(195,195,'1975-07-15','address195','city195',45,52), 2719(200,200,'2006-03-09','address200','city200',0,0), 2720(201,201,'2008-05-20','address201','city201',3,28), 2721(202,202,'2000-06-30','address202','city202',12,52), 2722(203,203,'1992-07-08','address203','city203',50,52), 2723(204,204,'1988-07-05','address204','city204',14,40), 2724(205,205,'1950-10-29','address205','city205',0,0), 2725(206,206,'1962-11-25','address206','city206',0,0), 2726(207,207,'1946-06-03','address207','city207',0,0), 2727(214,214,'1973-12-14','address214','city214',0,0), 2728(217,217,'1945-11-06','address217','city217',40,36), 2729(218,218,'2007-07-20','address218','city218',0,0), 2730(219,219,'1979-10-05','address219','city219',0,0), 2731(220,220,'1992-06-20','address220','city220',10,12), 2732(221,221,'2007-03-26','address221','city221',50,52), 2733(222,222,'1989-12-24','address222','city222',0,0), 2734(224,224,'1975-07-14','address224','city224',0,0), 2735(225,225,'1976-02-23','address225','city225',20,52), 2736(226,226,'1974-06-22','address226','city226',0,0), 2737(227,227,'2004-01-16','address227','city227',0,0), 2738(232,232,'1958-01-01','address232','city232',0,0), 2739(233,233,'1966-08-03','address233','city233',40,32), 2740(234,234,'1975-10-22','address234','city234',40,52), 2741(235,235,'1983-10-25','address235','city235',0,0), 2742(236,236,'1974-03-07','address236','city236',0,0), 2743(237,237,'1965-12-31','address237','city237',45,20), 2744(238,238,'1971-10-16','address238','city238',0,0), 2745(239,239,'1989-07-19','address239','city239',0,0), 2746(246,246,'1960-07-08','address246','city246',0,0), 2747(249,249,'1943-07-01','address249','city249',40,30), 2748(250,250,'1983-10-15','address250','city250',30,52), 2749(251,251,'1979-07-03','address251','city251',0,0), 2750(252,252,'1985-10-04','address252','city252',15,4), 2751(253,253,'1966-10-24','address253','city253',0,0), 2752(254,254,'1956-02-02','address254','city254',0,0), 2753(1,1,'2003-11-23','address1','city1',40,52), 2754(3,3,'1938-01-23','address3','city3',0,0), 2755(5,5,'2006-12-27','address5','city5',40,48), 2756(7,7,'1969-04-09','address7','city7',0,0), 2757(9,9,'2006-06-14','address9','city9',0,0), 2758(11,11,'1999-01-12','address11','city11',40,52), 2759(13,13,'1968-01-13','address13','city13',50,12), 2760(15,15,'1960-04-11','address15','city15',0,0), 2761(17,17,'2006-10-13','address17','city17',40,52), 2762(19,19,'1950-08-19','address19','city19',0,0), 2763(21,21,'2000-05-01','address21','city21',40,30), 2764(23,23,'1952-06-09','address23','city23',40,52), 2765(25,25,'1934-12-08','address25','city25',32,40), 2766(27,27,'1995-04-19','address27','city27',40,45), 2767(29,29,'1986-01-14','address29','city29',44,52), 2768(31,31,'1978-04-19','address31','city31',10,20), 2769(33,33,'1989-11-23','address33','city33',25,10), 2770(35,35,'2012-01-02','address35','city35',8,48), 2771(37,37,'2005-08-24','address37','city37',40,42), 2772(39,39,'1973-11-02','address39','city39',40,52), 2773(41,41,'2011-10-12','address41','city41',20,30), 2774(43,43,'1960-12-24','address43','city43',0,0), 2775(45,45,'1990-04-17','address45','city45',35,40), 2776(47,47,'1964-04-02','address47','city47',0,0), 2777(49,49,'1957-01-25','address49','city49',40,52), 2778(51,51,'1970-10-20','address51','city51',0,0), 2779(54,54,'1987-09-30','address54','city54',0,0), 2780(58,58,'1975-05-07','address58','city58',0,0), 2781(62,62,'1972-08-03','address62','city62',40,52), 2782(66,66,'1995-11-04','address66','city66',0,0), 2783(70,70,'1985-10-19','address70','city70',40,52), 2784(74,74,'1969-06-09','address74','city74',0,0), 2785(78,78,'2003-01-16','address78','city78',66,52), 2786(82,82,'2012-04-29','address82','city82',50,30), 2787(86,86,'2008-02-03','address86','city86',0,0), 2788(90,90,'1973-05-15','address90','city90',35,12), 2789(94,94,'1987-10-28','address94','city94',40,50), 2790(98,98,'1973-06-10','address98','city98',65,50), 2791(102,102,'2009-09-13','address102','city102',0,0), 2792(106,106,'1986-07-03','address106','city106',0,0), 2793(110,110,'1982-06-10','address110','city110',35,52), 2794(114,114,'1963-10-08','address114','city114',48,52), 2795(118,118,'1948-03-07','address118','city118',0,0), 2796(122,122,'1997-12-19','address122','city122',0,0), 2797(124,124,'1966-03-25','address124','city124',0,0), 2798(128,128,'1968-08-13','address128','city128',0,0), 2799(132,132,'1989-09-25','address132','city132',20,20), 2800(136,136,'1993-09-02','address136','city136',0,0), 2801(140,140,'1981-05-31','address140','city140',48,52), 2802(144,144,'1960-09-15','address144','city144',0,0), 2803(148,148,'1945-02-13','address148','city148',40,38), 2804(152,152,'2010-11-13','address152','city152',20,52), 2805(154,154,'1950-11-07','address154','city154',55,52), 2806(160,160,'1981-01-17','address160','city160',0,0), 2807(162,162,'2001-03-19','address162','city162',0,0), 2808(168,168,'2003-03-28','address168','city168',0,0), 2809(170,170,'1977-06-18','address170','city170',50,52), 2810(176,176,'1967-04-15','address176','city176',30,50), 2811(178,178,'1989-10-25','address178','city178',60,12), 2812(184,184,'2004-04-21','address184','city184',0,0), 2813(186,186,'1952-11-08','address186','city186',50,48), 2814(209,209,'1943-03-15','address209','city209',40,30), 2815(241,241,'1979-12-02','address241','city241',0,0), 2816(257,257,'2010-03-06','address257','city257',40,47); 2817--enable_query_log 2818 2819--sorted_result 2820select * from t1 where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21'; 2821select * from t1 IGNORE INDEX(dob, weeks_worked_last_year, hours_worked_per_week) where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21'; 2822 2823drop table t1; 2824 2825--echo # 2826--echo # MDEV-6322: The PARTITION engine can return wrong query results 2827--echo # 2828CREATE TABLE t1 ( 2829 CustomerID varchar(5) DEFAULT NULL, 2830 CompanyName varchar(40) DEFAULT NULL, 2831 ContactName varchar(30) DEFAULT NULL, 2832 ContactTitle varchar(30) DEFAULT NULL, 2833 Address varchar(60) DEFAULT NULL, 2834 City varchar(15) DEFAULT NULL, 2835 Region varchar(15) DEFAULT NULL, 2836 PostalCode varchar(10) DEFAULT NULL, 2837 Country varchar(15) NOT NULL, 2838 Phone varchar(24) DEFAULT NULL, 2839 Fax varchar(24) DEFAULT NULL 2840) ENGINE=MyISAM DEFAULT CHARSET=latin1 2841PARTITION BY LIST COLUMNS(Country) 2842(PARTITION p1 VALUES IN ('Germany','Austria','Switzerland','Poland'), 2843 PARTITION p2 VALUES IN ('USA','Canada','Mexico'), 2844 PARTITION p3 VALUES IN ('Spain','Portugal','Italy'), 2845 PARTITION p4 VALUES IN ('UK','Ireland'), 2846 PARTITION p5 VALUES IN ('France','Belgium'), 2847 PARTITION p6 VALUES IN ('Sweden','Finland','Denmark','Norway'), 2848 PARTITION p7 VALUES IN ('Venezuela','Argentina','Brazil') 2849); 2850 2851INSERT INTO t1 (CustomerID, City, Country) VALUES 2852('ANATR','México D.F','Mexico'), 2853('ANTON','México D.F','Mexico'), 2854('BOTTM','Tsawassen','Canada'), 2855('CENTC','México D.F','Mexico'), 2856('GREAL','Eugene','USA'), 2857('HUNGC','Elgin','USA'), 2858('LAUGB','Vancouver','Canada'), 2859('LAZYK','Walla Walla','USA'), 2860('LETSS','San Francisco','USA'), 2861('LONEP','Portland','USA'); 2862 2863SELECT * FROM t1 WHERE Country = 'USA'; 2864DROP TABLE t1; 2865 2866# 2867# Test ALTER TABLE ADD/DROP PARTITION IF EXISTS 2868# 2869 2870CREATE TABLE t1 ( d DATE NOT NULL) 2871PARTITION BY RANGE( YEAR(d) ) ( 2872 PARTITION p0 VALUES LESS THAN (1960), 2873 PARTITION p1 VALUES LESS THAN (1970), 2874 PARTITION p2 VALUES LESS THAN (1980), 2875 PARTITION p3 VALUES LESS THAN (1990) 2876); 2877 2878ALTER TABLE t1 ADD PARTITION IF NOT EXISTS( 2879PARTITION `p5` VALUES LESS THAN (2010) 2880COMMENT 'APSTART \' APEND' 2881); 2882 2883ALTER TABLE t1 ADD PARTITION IF NOT EXISTS( 2884PARTITION `p5` VALUES LESS THAN (2010) 2885COMMENT 'APSTART \' APEND' 2886); 2887 2888alter table t1 drop partition if exists p5; 2889alter table t1 drop partition if exists p5; 2890 2891DROP TABLE t1; 2892 2893# 2894# MDEV-14696 Server crashes in in prep_alter_part_table on 2nd execution of PS. 2895# 2896 2897CREATE TABLE t1 (a INT) ENGINE=MyISAM PARTITION BY RANGE(a) (PARTITION p1 VALUES LESS THAN (0)); 2898ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES LESS THAN (1)); 2899PREPARE stmt FROM 'ALTER TABLE t1 ADD PARTITION IF NOT EXISTS (PARTITION p2 VALUES LESS THAN (2))'; 2900EXECUTE stmt; 2901EXECUTE stmt; 2902 2903DEALLOCATE PREPARE stmt; 2904DROP TABLE t1; 2905 2906--echo # 2907--echo # MDEV-17032: Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY 2908--echo # 2909 2910create table t0(a int); 2911insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2912 2913create table t1(a int); 2914insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; 2915 2916 2917create table t2 ( 2918 part_key int, 2919 a int, 2920 b int 2921) partition by list(part_key) ( 2922 partition p0 values in (0), 2923 partition p1 values in (1), 2924 partition p2 values in (2), 2925 partition p3 values in (3), 2926 partition p4 values in (4) 2927); 2928insert into t2 2929select mod(a,5), a/100, mod(a,5) from t1; 2930 2931set @save_use_stat_tables= @@use_stat_tables; 2932set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; 2933--echo # 2934--echo # Tests using stats provided by the storage engine 2935--echo # 2936explain extended select * from t2 where part_key=1; 2937explain partitions select * from t2 where part_key=1; 2938explain extended select * from t2 where part_key in (1,2); 2939explain partitions select * from t2 where part_key in (1,2); 2940explain extended select * from t2 where b=5; 2941explain partitions select * from t2 where b=5; 2942explain extended select * from t2 partition(p0) where b=1; 2943 2944 2945set @save_histogram_size=@@histogram_size; 2946set @@histogram_size=100; 2947set @@use_stat_tables= PREFERABLY; 2948set @@optimizer_use_condition_selectivity=4; 2949analyze table t2; 2950--echo # 2951--echo # Tests using EITS 2952--echo # 2953--echo # filtered should be 100 2954explain extended select * from t2 where part_key=1; 2955explain partitions select * from t2 where part_key=1; 2956--echo # filtered should be 100 2957explain extended select * from t2 where part_key in (1,2); 2958explain partitions select * from t2 where part_key in (1,2); 2959explain extended select * from t2 where b=5; 2960explain partitions select * from t2 where b=5; 2961explain extended select * from t2 partition(p0) where b=1; 2962 2963set @@use_stat_tables= @save_use_stat_tables; 2964set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; 2965set @@histogram_size= @save_histogram_size; 2966drop table t0,t1,t2; 2967 2968--echo # 2969--echo # End of 10.0 tests 2970--echo # 2971 2972--echo # 2973--echo # MDEV-8283 crash in get_mm_leaf with xor on binary col 2974--echo # 2975CREATE TABLE t1(a BINARY(80)) PARTITION BY KEY(a) PARTITIONS 3; 2976SELECT 1 FROM t1 WHERE a XOR 'a'; 2977DROP TABLE t1; 2978 2979--echo # 2980--echo # Bug #25207522: INCORRECT ORDER-BY BEHAVIOR ON A PARTITIONED TABLE 2981--echo # WITH A COMPOSITE PREFIX INDEX 2982--echo # 2983create table t1(id int unsigned not null, 2984 data varchar(2) default null, 2985 key data_idx (data(1),id) 2986) default charset=utf8 2987partition by range (id) ( 2988 partition p10 values less than (10), 2989 partition p20 values less than (20) 2990); 2991insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ; 2992select id from t1 where data = 'ab' order by id; 2993drop table t1; 2994 2995create table t1(id int unsigned not null, 2996 data text default null, 2997 key data_idx (data(1),id) 2998) default charset=utf8 2999partition by range (id) ( 3000 partition p10 values less than (10), 3001 partition p20 values less than (20) 3002); 3003insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ; 3004select id from t1 where data = 'ab' order by id; 3005drop table t1; 3006 3007--echo # 3008--echo # MDEV-5628: Assertion `! is_set()' or `!is_set() || 3009--echo # (m_status == DA_OK_BULK && is_bulk_op())' fails on UPDATE on a 3010--echo # partitioned table with subquery (MySQL:71630) 3011--echo # 3012 3013CREATE TABLE t1 (a INT) PARTITION BY HASH(a) PARTITIONS 2; 3014 3015CREATE TABLE t2 (b INT); 3016INSERT INTO t2 VALUES (1),(2); 3017 3018--error ER_SUBQUERY_NO_1_ROW 3019UPDATE t1 SET a = 7 WHERE a = ( SELECT b FROM t2 ) ORDER BY a LIMIT 6; 3020 3021DROP TABLE t1,t2; 3022 3023--echo # 3024--echo # End of 10.1 tests 3025--echo # 3026