1drop table if exists t1, t2; 2# 3# Bug#11765667: bug#58655: ASSERTION FAILED, 4# SERVER CRASHES WITH MYSQLD GOT SIGNAL 6 5# 6CREATE TABLE t1 ( 7id MEDIUMINT NOT NULL AUTO_INCREMENT, 8dt DATE, st VARCHAR(255), uid INT, 9id2nd LONGBLOB, filler VARCHAR(255), PRIMARY KEY(id, dt) 10); 11INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES 12('1991-03-14', 'Initial Insert', 200, 1234567, 'No Data'), 13('1991-02-26', 'Initial Insert', 201, 1234567, 'No Data'), 14('1992-03-16', 'Initial Insert', 234, 1234567, 'No Data'), 15('1992-07-02', 'Initial Insert', 287, 1234567, 'No Data'), 16('1991-05-26', 'Initial Insert', 256, 1234567, 'No Data'), 17('1991-04-25', 'Initial Insert', 222, 1234567, 'No Data'), 18('1993-03-12', 'Initial Insert', 267, 1234567, 'No Data'), 19('1993-03-14', 'Initial Insert', 291, 1234567, 'No Data'), 20('1991-12-20', 'Initial Insert', 298, 1234567, 'No Data'), 21('1994-10-31', 'Initial Insert', 220, 1234567, 'No Data'); 22ALTER TABLE t1 PARTITION BY LIST (YEAR(dt)) ( 23PARTITION d1 VALUES IN (1991, 1994), 24PARTITION d2 VALUES IN (1993), 25PARTITION d3 VALUES IN (1992, 1995, 1996) 26); 27INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES 28('1991-07-14', 'After Partitioning Insert', 299, 1234567, 'Insert row'); 29UPDATE t1 SET filler='Updating the row' WHERE uid=298; 30DROP TABLE t1; 31# 32# Bug#59297: Can't find record in 'tablename' on update inner join 33# 34CREATE TABLE t1 ( 35a char(2) NOT NULL, 36b char(2) NOT NULL, 37c int(10) unsigned NOT NULL, 38d varchar(255) DEFAULT NULL, 39e varchar(1000) DEFAULT NULL, 40PRIMARY KEY (a, b, c), 41KEY (a), 42KEY (a, b) 43) 44/*!50100 PARTITION BY KEY (a) 45PARTITIONS 20 */; 46INSERT INTO t1 (a, b, c, d, e) VALUES 47('07', '03', 343, '1', '07_03_343'), 48('01', '04', 343, '2', '01_04_343'), 49('01', '06', 343, '3', '01_06_343'), 50('01', '07', 343, '4', '01_07_343'), 51('01', '08', 343, '5', '01_08_343'), 52('01', '09', 343, '6', '01_09_343'), 53('03', '03', 343, '7', '03_03_343'), 54('03', '06', 343, '8', '03_06_343'), 55('03', '07', 343, '9', '03_07_343'), 56('04', '03', 343, '10', '04_03_343'), 57('04', '06', 343, '11', '04_06_343'), 58('05', '03', 343, '12', '05_03_343'), 59('11', '03', 343, '13', '11_03_343'), 60('11', '04', 343, '14', '11_04_343') 61; 62UPDATE t1 AS A, 63(SELECT '03' AS a, '06' AS b, 343 AS c, 'last' AS d) AS B 64SET A.e = B.d 65WHERE A.a = '03' 66AND A.b = '06' 67AND A.c = 343; 68DROP TABLE t1; 69# 70# Bug#59503: explain extended crash in get_mm_leaf 71# 72CREATE TABLE t1 (a VARCHAR(51) CHARACTER SET latin1) 73ENGINE=MyISAM 74PARTITION BY KEY (a) PARTITIONS 1; 75INSERT INTO t1 VALUES ('a'),('b'),('c'); 76EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a > 1; 77id select_type table type possible_keys key key_len ref rows filtered Extra 781 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 79Warnings: 80Note 1003 select 1 AS `1` from `test`.`t1` where `test`.`t1`.`a` > 1 81DROP TABLE t1; 82# 83# Bug#57778: failed primary key add to partitioned innodb table 84# inconsistent and crashes 85# 86CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) 87PARTITION BY KEY (a) PARTITIONS 2; 88INSERT INTO t1 VALUES (0,1), (0,2); 89ALTER TABLE t1 ADD PRIMARY KEY (a); 90ERROR 23000: Duplicate entry '0' for key 'PRIMARY' 91SHOW CREATE TABLE t1; 92Table Create Table 93t1 CREATE TABLE `t1` ( 94 `a` int(11) NOT NULL, 95 `b` int(11) NOT NULL 96) ENGINE=MyISAM DEFAULT CHARSET=latin1 97 PARTITION BY KEY (`a`) 98PARTITIONS 2 99SELECT * FROM t1; 100a b 1010 1 1020 2 103UPDATE t1 SET a = 1, b = 1 WHERE a = 0 AND b = 2; 104ALTER TABLE t1 ADD PRIMARY KEY (a); 105SELECT * FROM t1; 106a b 1071 1 1080 1 109ALTER TABLE t1 DROP PRIMARY KEY; 110SELECT * FROM t1; 111a b 1121 1 1130 1 114DROP TABLE t1; 115# 116# Bug#57113: ha_partition::extra(ha_extra_function): 117# Assertion `m_extra_cache' failed 118CREATE TABLE t1 119(id INT NOT NULL PRIMARY KEY, 120name VARCHAR(16) NOT NULL, 121year YEAR, 122INDEX name (name(8)) 123) 124PARTITION BY HASH(id) PARTITIONS 2; 125INSERT INTO t1 VALUES ( 1, 'FooBar', '1924' ); 126CREATE TABLE t2 (id INT); 127INSERT INTO t2 VALUES (1),(2); 128UPDATE t1, t2 SET t1.year = '1955' WHERE t1.name = 'FooBar'; 129DROP TABLE t1, t2; 130# 131# Bug#55458: Partitioned MyISAM table gets crashed by multi-table update 132# 133CREATE TABLE t1 ( 134`id` int NOT NULL, 135`user_num` int DEFAULT NULL, 136PRIMARY KEY (`id`) 137) ENGINE=MyISAM CHARSET=latin1; 138INSERT INTO t1 VALUES (1,8601); 139INSERT INTO t1 VALUES (2,8601); 140INSERT INTO t1 VALUES (3,8601); 141INSERT INTO t1 VALUES (4,8601); 142CREATE TABLE t2 ( 143`id` int(11) NOT NULL, 144`user_num` int DEFAULT NULL, 145`name` varchar(64) NOT NULL, 146PRIMARY KEY (`id`) 147) ENGINE=MyISAM CHARSET=latin1 148PARTITION BY HASH (id) 149PARTITIONS 2; 150INSERT INTO t2 VALUES (1,8601,'John'); 151INSERT INTO t2 VALUES (2,8601,'JS'); 152INSERT INTO t2 VALUES (3,8601,'John S'); 153UPDATE t1, t2 SET t2.name = 'John Smith' WHERE t1.user_num = t2.user_num; 154DROP TABLE t1, t2; 155# Bug#39338: Fieldnames in 156# INFORMATIONSCHEMA.PARTITIONS.PARTITION_EXPRESSION become unescaped 157# NOTE: the partition expression is saved as a string, so changing from 158# normal quotes to ansi quotes does not change the expression, only 159# for partition by KEY. 160CREATE TABLE t1 ( 161ID int(11) NOT NULL, 162`aaaa,aaaaa` tinyint(3) UNSIGNED NOT NULL DEFAULT '0', 163ddddddddd int(11) NOT NULL DEFAULT '0', 164new_field0 varchar(50), 165PRIMARY KEY(ID, `aaaa,aaaaa`, ddddddddd)) 166PARTITION BY RANGE(ID) 167PARTITIONS 3 168SUBPARTITION BY LINEAR KEY(ID,`aaaa,aaaaa`) 169SUBPARTITIONS 2 ( 170PARTITION p01 VALUES LESS THAN(100), 171PARTITION p11 VALUES LESS THAN(200), 172PARTITION p21 VALUES LESS THAN MAXVALUE); 173SELECT PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1'; 174PARTITION_EXPRESSION SUBPARTITION_EXPRESSION 175`ID` `ID`,`aaaa,aaaaa` 176`ID` `ID`,`aaaa,aaaaa` 177`ID` `ID`,`aaaa,aaaaa` 178`ID` `ID`,`aaaa,aaaaa` 179`ID` `ID`,`aaaa,aaaaa` 180`ID` `ID`,`aaaa,aaaaa` 181show create table t1; 182Table Create Table 183t1 CREATE TABLE `t1` ( 184 `ID` int(11) NOT NULL, 185 `aaaa,aaaaa` tinyint(3) unsigned NOT NULL DEFAULT 0, 186 `ddddddddd` int(11) NOT NULL DEFAULT 0, 187 `new_field0` varchar(50) DEFAULT NULL, 188 PRIMARY KEY (`ID`,`aaaa,aaaaa`,`ddddddddd`) 189) ENGINE=MyISAM DEFAULT CHARSET=latin1 190 PARTITION BY RANGE (`ID`) 191SUBPARTITION BY LINEAR KEY (`ID`,`aaaa,aaaaa`) 192SUBPARTITIONS 2 193(PARTITION `p01` VALUES LESS THAN (100) ENGINE = MyISAM, 194 PARTITION `p11` VALUES LESS THAN (200) ENGINE = MyISAM, 195 PARTITION `p21` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) 196drop table t1; 197CREATE TABLE t1 (a INT, b INT) 198PARTITION BY LIST (a) 199SUBPARTITION BY HASH (b) 200(PARTITION p1 VALUES IN (1)); 201ALTER TABLE t1 ADD COLUMN c INT; 202DROP TABLE t1; 203CREATE TABLE t1 ( 204a int NOT NULL, 205b int NOT NULL); 206CREATE TABLE t2 ( 207a int NOT NULL, 208b int NOT NULL, 209INDEX(b) 210) 211PARTITION BY HASH(a) PARTITIONS 2; 212INSERT INTO t1 VALUES (399, 22); 213INSERT INTO t2 VALUES (1, 22), (1, 42); 214INSERT INTO t2 SELECT 1, 399 FROM t2, t1 215WHERE t1.b = t2.b; 216DROP TABLE t1, t2; 217CREATE TABLE t1 ( 218a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 219b varchar(10), 220PRIMARY KEY (a) 221) 222PARTITION BY RANGE (UNIX_TIMESTAMP(a)) ( 223PARTITION p1 VALUES LESS THAN (1199134800), 224PARTITION pmax VALUES LESS THAN MAXVALUE 225); 226INSERT INTO t1 VALUES ('2007-07-30 17:35:48', 'p1'); 227INSERT INTO t1 VALUES ('2009-07-14 17:35:55', 'pmax'); 228INSERT INTO t1 VALUES ('2009-09-21 17:31:42', 'pmax'); 229SELECT * FROM t1; 230a b 2312007-07-30 17:35:48 p1 2322009-07-14 17:35:55 pmax 2332009-09-21 17:31:42 pmax 234SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; 235a b 2362007-07-30 17:35:48 p1 237EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; 238id select_type table partitions type possible_keys key key_len ref rows Extra 2391 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 240EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48'; 241id select_type table partitions type possible_keys key key_len ref rows Extra 2421 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 243ALTER TABLE t1 REORGANIZE PARTITION pmax INTO ( 244PARTITION p3 VALUES LESS THAN (1247688000), 245PARTITION pmax VALUES LESS THAN MAXVALUE); 246SELECT * FROM t1; 247a b 2482007-07-30 17:35:48 p1 2492009-07-14 17:35:55 pmax 2502009-09-21 17:31:42 pmax 251SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; 252a b 2532007-07-30 17:35:48 p1 254EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; 255id select_type table partitions type possible_keys key key_len ref rows Extra 2561 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 257EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48'; 258id select_type table partitions type possible_keys key key_len ref rows Extra 2591 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 260SHOW CREATE TABLE t1; 261Table Create Table 262t1 CREATE TABLE `t1` ( 263 `a` timestamp NOT NULL DEFAULT current_timestamp(), 264 `b` varchar(10) DEFAULT NULL, 265 PRIMARY KEY (`a`) 266) ENGINE=MyISAM DEFAULT CHARSET=latin1 267 PARTITION BY RANGE (unix_timestamp(`a`)) 268(PARTITION `p1` VALUES LESS THAN (1199134800) ENGINE = MyISAM, 269 PARTITION `p3` VALUES LESS THAN (1247688000) ENGINE = MyISAM, 270 PARTITION `pmax` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) 271DROP TABLE t1; 272create table t1 (a int NOT NULL, b varchar(5) NOT NULL) 273default charset=utf8 274partition by list (a) 275subpartition by key (b) 276(partition p0 values in (1), 277partition p1 values in (2)); 278drop table t1; 279create table t1 (a int, b int, key(a)) 280partition by list (a) 281( partition p0 values in (1), 282partition p1 values in (2)); 283insert into t1 values (1,1),(2,1),(2,2),(2,3); 284show indexes from t1; 285Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 286t1 1 a 1 a A NULL NULL NULL YES BTREE 287analyze table t1; 288Table Op Msg_type Msg_text 289test.t1 analyze status Engine-independent statistics collected 290test.t1 analyze status OK 291show indexes from t1; 292Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 293t1 1 a 1 a A 2 NULL NULL YES BTREE 294drop table t1; 295create table t1 (a int) 296partition by hash (a); 297create index i on t1 (a); 298insert into t1 values (1); 299insert into t1 select * from t1; 300create index i on t1 (a); 301ERROR 42000: Duplicate key name 'i' 302create index i2 on t1 (a); 303Warnings: 304Note 1831 Duplicate index `i2`. This is deprecated and will be disallowed in a future release 305drop table t1; 306CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a)) 307ENGINE=MyISAM 308PARTITION BY HASH (a); 309ERROR HY000: Partitioned tables do not support FOREIGN KEY 310CREATE TABLE t1 ( 311pk INT NOT NULL AUTO_INCREMENT, 312PRIMARY KEY (pk) 313) 314/*!50100 PARTITION BY HASH (pk) 315PARTITIONS 2 */; 316INSERT INTO t1 VALUES (NULL); 317INSERT INTO t1 VALUES (NULL); 318INSERT INTO t1 VALUES (NULL); 319SELECT * FROM t1 WHERE pk < 0 ORDER BY pk; 320pk 321DROP TABLE t1; 322SET sql_mode=no_engine_substitution; 323CREATE TABLE t1 (a INT) 324ENGINE=NonExistentEngine; 325ERROR 42000: Unknown storage engine 'NonExistentEngine' 326CREATE TABLE t1 (a INT) 327ENGINE=NonExistentEngine 328PARTITION BY HASH (a); 329ERROR 42000: Unknown storage engine 'NonExistentEngine' 330CREATE TABLE t1 (a INT) 331ENGINE=Memory; 332ALTER TABLE t1 ENGINE=NonExistentEngine; 333ERROR 42000: Unknown storage engine 'NonExistentEngine' 334ALTER TABLE t1 335PARTITION BY HASH (a) 336(PARTITION p0 ENGINE=Memory, 337PARTITION p1 ENGINE=NonExistentEngine); 338ERROR 42000: Unknown storage engine 'NonExistentEngine' 339ALTER TABLE t1 ENGINE=NonExistentEngine; 340ERROR 42000: Unknown storage engine 'NonExistentEngine' 341SHOW CREATE TABLE t1; 342Table Create Table 343t1 CREATE TABLE `t1` ( 344 `a` int(11) DEFAULT NULL 345) ENGINE=MEMORY DEFAULT CHARSET=latin1 346DROP TABLE t1; 347SET sql_mode=''; 348CREATE TABLE t1 (a INT) 349ENGINE=NonExistentEngine; 350Warnings: 351Warning 1286 Unknown storage engine 'NonExistentEngine' 352Warning 1266 Using storage engine MyISAM for table 't1' 353DROP TABLE t1; 354CREATE TABLE t1 (a INT) 355ENGINE=NonExistentEngine 356PARTITION BY HASH (a); 357Warnings: 358Warning 1286 Unknown storage engine 'NonExistentEngine' 359Warning 1266 Using storage engine MyISAM for table 't1' 360DROP TABLE t1; 361CREATE TABLE t1 (a INT) 362ENGINE=Memory; 363ALTER TABLE t1 ENGINE=NonExistentEngine; 364Warnings: 365Warning 1286 Unknown storage engine 'NonExistentEngine' 366ALTER TABLE t1 367PARTITION BY HASH (a) 368(PARTITION p0 ENGINE=Memory, 369PARTITION p1 ENGINE=NonExistentEngine); 370Warnings: 371Warning 1286 Unknown storage engine 'NonExistentEngine' 372ALTER TABLE t1 ENGINE=NonExistentEngine; 373Warnings: 374Warning 1286 Unknown storage engine 'NonExistentEngine' 375SHOW CREATE TABLE t1; 376Table Create Table 377t1 CREATE TABLE `t1` ( 378 `a` int(11) DEFAULT NULL 379) ENGINE=MEMORY DEFAULT CHARSET=latin1 380 PARTITION BY HASH (`a`) 381(PARTITION `p0` ENGINE = MEMORY, 382 PARTITION `p1` ENGINE = MEMORY) 383DROP TABLE t1; 384SET sql_mode=DEFAULT; 385CREATE TABLE t1 (a INT NOT NULL, KEY(a)) 386PARTITION BY RANGE(a) 387(PARTITION p1 VALUES LESS THAN (200), PARTITION pmax VALUES LESS THAN MAXVALUE); 388INSERT INTO t1 VALUES (2), (40), (40), (70), (60), (90), (199); 389SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a ASC; 390a 39160 39270 39390 394SELECT a FROM t1 WHERE a BETWEEN 60 AND 95; 395a 39660 39770 39890 399INSERT INTO t1 VALUES (200), (250), (210); 400SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a ASC; 401a 40260 40370 40490 405199 406200 407210 408SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a ASC; 409a 410200 411210 412SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a DESC; 413a 41490 41570 41660 417SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a DESC; 418a 419210 420200 421199 42290 42370 42460 425SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a DESC; 426a 427210 428200 429SELECT a FROM t1 WHERE a BETWEEN 60 AND 220; 430a 431199 432200 433210 43460 43570 43690 437SELECT a FROM t1 WHERE a BETWEEN 200 AND 220; 438a 439200 440210 441SELECT a FROM t1 WHERE a BETWEEN 60 AND 95; 442a 44360 44470 44590 446SELECT a FROM t1 WHERE a BETWEEN 60 AND 220; 447a 448199 449200 450210 45160 45270 45390 454SELECT a FROM t1 WHERE a BETWEEN 200 AND 220; 455a 456200 457210 458DROP TABLE t1; 459CREATE TABLE t1 ( 460a INT NOT NULL, 461b MEDIUMINT NOT NULL, 462c INT NOT NULL, 463KEY b (b) 464) ENGINE=MyISAM 465PARTITION BY LIST (a) ( 466PARTITION p0 VALUES IN (1) 467); 468INSERT INTO t1 VALUES (1,1,0), (1,1,1), (1,1,2), (1,1,53), (1,1,4), (1,1,5), 469(1,1,6), (1,1,7), (1,1,8), (1,1,9), (1,1,10), (1,1,11), (1,1,12), (1,1,13), 470(1,1,14), (1,1,15), (1,1,16), (1,1,67), (1,1,18), (1,1,19), (1,1,20), (1,1,21), 471(1,1,22), (1,1,23), (1,1,24), (1,1,75), (1,1,26), (1,1,27), (1,1,128), 472(1,1,79), (1,1,30), (1,1,31), (1,1,32), (1,1,33), (1,1,34), (1,1,85), (1,1,36), 473(1,1,37), (1,1,38), (1,1,39), (1,1,40), (1,1,241), (1,1,42), (1,1,43), 474(1,1,44), (1,1,45), (1,1,46), (1,1,147), (1,1,48), (1,1,49), (1,2,0), (1,2,1), 475(1,2,2), (1,2,3), (1,2,4), (1,2,5), (1,2,6), (1,2,7), (1,2,8), (1,2,9), 476(1,2,10), (1,2,11), (1,2,12), (1,2,13), (1,2,14), (1,2,15), (1,2,16), (1,2,17), 477(1,2,18), (1,2,19), (1,2,20), (1,2,21), (1,2,22), (1,2,23), (1,2,24), (1,2,25), 478(1,2,26), (1,2,27), (1,2,28), (1,2,29), (1,2,30), (1,2,31), (1,2,32), (1,2,33), 479(1,2,34), (1,2,35), (1,2,36), (1,2,37), (1,2,38), (1,2,39), (1,2,40), (1,2,41), 480(1,2,42), (1,2,43), (1,2,44), (1,2,45), (1,2,46), (1,2,47), (1,2,48), (1,2,49), 481(1,6,0), (1,6,1), (1,6,2), (1,6,3), (1,6,4), (1,6,5), (1,6,6), (1,6,7), 482(1,6,8), (1,6,9), (1,6,10), (1,6,11), (1,6,12), (1,6,13), (1,6,14), (1,6,15), 483(1,6,16), (1,6,17), (1,6,18), (1,6,19), (1,6,20), (1,6,21), (1,6,22), (1,6,23), 484(1,6,24), (1,6,25), (1,6,26), (1,6,27), (1,6,28), (1,6,29), (1,6,30), (1,6,31), 485(1,6,32), (1,6,33), (1,6,34), (1,6,35), (1,6,36), (1,6,37), (1,6,38), (1,6,39), 486(1,6,40), (1,6,41), (1,6,42), (1,6,43), (1,6,44), (1,6,45), (1,6,46), (1,6,47), 487(1,6,48), (1,6,49), (1,7,0), (1,7,1), (1,7,2), (1,7,3), (1,7,4), (1,7,5), 488(1,7,6), (1,7,7), (1,7,8), (1,7,9), (1,7,10), (1,7,11), (1,7,12), (1,7,13), 489(1,7,14), (1,7,15), (1,7,16), (1,7,17), (1,7,18), (1,7,19), (1,7,20), (1,7,21), 490(1,7,22), (1,7,23), (1,7,24), (1,7,25), (1,7,26), (1,7,27), (1,7,28), (1,7,29), 491(1,7,30), (1,7,31), (1,7,32), (1,7,33), (1,7,34), (1,7,35), (1,7,38), (1,7,39), 492(1,7,90), (1,7,41), (1,7,43), (1,7,48), (1,7,49), (1,9,0), (1,9,1), (1,9,2), 493(1,9,3), (1,9,4), (1,9,5), (1,9,6), (1,9,7), (1,9,8), (1,9,9), (1,9,10), 494(1,9,11), (1,9,12), (1,9,13), (1,9,14), (1,9,15), (1,9,16), (1,9,17), (1,9,18), 495(1,9,19), (1,9,20), (1,9,21), (1,9,22), (1,9,23), (1,9,24), (1,9,25), (1,9,26), 496(1,9,29), (1,9,32), (1,9,35), (1,9,38), (1,10,0), (1,10,1), (1,10,2), (1,10,3), 497(1,10,4), (1,10,5), (1,10,6), (1,10,7), (1,10,8), (1,10,9), (1,10,10), 498(1,10,11), (1,10,13), (1,10,14), (1,10,15), (1,10,16), (1,10,17), (1,10,18), 499(1,10,22), (1,10,24), (1,10,25), (1,10,26), (1,10,28), (1,10,131), (1,10,33), 500(1,10,84), (1,10,35), (1,10,40), (1,10,42), (1,10,49), (1,11,0), (1,11,1), 501(1,11,2), (1,11,3), (1,11,4), (1,11,5), (1,11,6), (1,11,7), (1,11,8), (1,11,9), 502(1,11,10), (1,11,11), (1,11,12), (1,11,13), (1,11,14), (1,11,15), (1,11,16), 503(1,11,17), (1,11,18), (1,11,19), (1,11,20), (1,11,21), (1,11,22), (1,11,23), 504(1,11,24), (1,11,25), (1,11,26), (1,11,27), (1,11,28), (1,11,30), (1,11,31), 505(1,11,32), (1,11,33), (1,11,34), (1,11,35), (1,11,37), (1,11,39), (1,11,40), 506(1,11,42), (1,11,44), (1,11,45), (1,11,47), (1,11,48), (1,14,104), (1,14,58), 507(1,14,12), (1,14,13), (1,14,15), (1,14,16), (1,14,17), (1,14,34), (1,15,0), 508(1,15,1), (1,15,2), (1,15,3), (1,15,4), (1,15,5), (1,15,7), (1,15,9), 509(1,15,15), (1,15,27), (1,15,49), (1,16,0), (1,16,1), (1,16,3), (1,17,4), 510(1,19,1); 511SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 512COUNT(*) 51324 514SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 515SUM(c) 516400 517SELECT SUM(c+0.0) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 518SUM(c+0.0) 519400.0 520ALTER TABLE t1 DROP INDEX b; 521SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 522COUNT(*) 52324 524SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 525SUM(c) 526400 527ALTER TABLE t1 ENGINE = Memory; 528SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 529COUNT(*) 53024 531SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 532SUM(c) 533400 534ALTER TABLE t1 ADD INDEX b USING HASH (b); 535SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 536COUNT(*) 53724 538SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); 539SUM(c) 540400 541DROP TABLE t1; 542CREATE TABLE `t1` ( 543`c1` int(11) DEFAULT NULL, 544KEY `c1` (`c1`) 545) ENGINE=MyISAM DEFAULT CHARSET=latin1; 546CREATE TABLE `t2` ( 547`c1` int(11) DEFAULT NULL, 548KEY `c1` (`c1`) 549) 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) */; 550INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); 551INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); 552EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); 553id select_type table partitions type possible_keys key key_len ref rows Extra 5541 SIMPLE t1 NULL range c1 c1 5 NULL 4 Using where; Using index 555FLUSH STATUS; 556SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); 557c1 55811 55912 56018 56119 562SHOW STATUS LIKE 'Handler_read_%'; 563Variable_name Value 564Handler_read_first 0 565Handler_read_key 2 566Handler_read_last 0 567Handler_read_next 4 568Handler_read_prev 0 569Handler_read_retry 0 570Handler_read_rnd 0 571Handler_read_rnd_deleted 0 572Handler_read_rnd_next 0 573EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); 574id select_type table partitions type possible_keys key key_len ref rows Extra 5751 SIMPLE t2 a range c1 c1 5 NULL 4 Using where; Using index 576FLUSH STATUS; 577SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); 578c1 57911 58012 58118 58219 583SHOW STATUS LIKE 'Handler_read_%'; 584Variable_name Value 585Handler_read_first 0 586Handler_read_key 2 587Handler_read_last 0 588Handler_read_next 4 589Handler_read_prev 0 590Handler_read_retry 0 591Handler_read_rnd 0 592Handler_read_rnd_deleted 0 593Handler_read_rnd_next 0 594DROP TABLE t1,t2; 595CREATE TABLE `t1` ( 596`c1` int(11) DEFAULT NULL, 597KEY `c1` (`c1`) 598) ENGINE=MyISAM DEFAULT CHARSET=latin1; 599CREATE TABLE `t2` ( 600`c1` int(11) DEFAULT NULL, 601KEY `c1` (`c1`) 602) ENGINE=MyISAM DEFAULT CHARSET=latin1 603/*!50100 PARTITION BY RANGE (c1) 604(PARTITION a VALUES LESS THAN (100) ENGINE = MyISAM, 605PARTITION b VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */; 606INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); 607INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); 608EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5); 609id select_type table partitions type possible_keys key key_len ref rows Extra 6101 SIMPLE t1 NULL range c1 c1 5 NULL 2 Using where; Using index 611FLUSH STATUS; 612SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5); 613c1 6143 6154 616SHOW STATUS LIKE 'Handler_read_%'; 617Variable_name Value 618Handler_read_first 0 619Handler_read_key 1 620Handler_read_last 0 621Handler_read_next 2 622Handler_read_prev 0 623Handler_read_retry 0 624Handler_read_rnd 0 625Handler_read_rnd_deleted 0 626Handler_read_rnd_next 0 627EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5); 628id select_type table partitions type possible_keys key key_len ref rows Extra 6291 SIMPLE t2 a range c1 c1 5 NULL 2 Using where; Using index 630FLUSH STATUS; 631SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5); 632c1 6333 6344 635SHOW STATUS LIKE 'Handler_read_%'; 636Variable_name Value 637Handler_read_first 0 638Handler_read_key 1 639Handler_read_last 0 640Handler_read_next 2 641Handler_read_prev 0 642Handler_read_retry 0 643Handler_read_rnd 0 644Handler_read_rnd_deleted 0 645Handler_read_rnd_next 0 646EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15); 647id select_type table partitions type possible_keys key key_len ref rows Extra 6481 SIMPLE t1 NULL range c1 c1 5 NULL 2 Using where; Using index 649FLUSH STATUS; 650SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15); 651c1 65213 65314 654SHOW STATUS LIKE 'Handler_read_%'; 655Variable_name Value 656Handler_read_first 0 657Handler_read_key 1 658Handler_read_last 0 659Handler_read_next 2 660Handler_read_prev 0 661Handler_read_retry 0 662Handler_read_rnd 0 663Handler_read_rnd_deleted 0 664Handler_read_rnd_next 0 665EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15); 666id select_type table partitions type possible_keys key key_len ref rows Extra 6671 SIMPLE t2 a range c1 c1 5 NULL 2 Using where; Using index 668FLUSH STATUS; 669SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15); 670c1 67113 67214 673SHOW STATUS LIKE 'Handler_read_%'; 674Variable_name Value 675Handler_read_first 0 676Handler_read_key 1 677Handler_read_last 0 678Handler_read_next 2 679Handler_read_prev 0 680Handler_read_retry 0 681Handler_read_rnd 0 682Handler_read_rnd_deleted 0 683Handler_read_rnd_next 0 684DROP TABLE t1,t2; 685create table t1 (a int) partition by list ((a/3)*10 div 1) 686(partition p0 values in (0), partition p1 values in (1)); 687ERROR HY000: This partition function is not allowed 688CREATE TABLE t1 ( 689d DATE NOT NULL 690) 691PARTITION BY RANGE( YEAR(d) ) ( 692PARTITION p0 VALUES LESS THAN (1960), 693PARTITION p1 VALUES LESS THAN (1970), 694PARTITION p2 VALUES LESS THAN (1980), 695PARTITION p3 VALUES LESS THAN (1990) 696); 697ALTER TABLE t1 ADD PARTITION ( 698PARTITION `p5` VALUES LESS THAN (2010) 699COMMENT 'APSTART \' APEND' 700); 701SELECT * FROM t1 LIMIT 1; 702d 703DROP TABLE t1; 704create table t1 (id int auto_increment, s1 int, primary key (id)); 705insert into t1 values (null,1); 706insert into t1 values (null,6); 707select * from t1; 708id s1 7091 1 7102 6 711alter table t1 partition by range (id) ( 712partition p0 values less than (3), 713partition p1 values less than maxvalue 714); 715drop table t1; 716create table t1 (a int) 717partition by key(a) 718partitions 0.2+e1; 719ERROR 42000: Only integers allowed as number here near '0.2+e1' at line 3 720create table t1 (a int) 721partition by key(a) 722partitions -1; 723ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1' at line 3 724create table t1 (a int) 725partition by key(a) 726partitions 1.5; 727ERROR 42000: Only integers allowed as number here near '1.5' at line 3 728create table t1 (a int) 729partition by key(a) 730partitions 1e+300; 731ERROR 42000: Only integers allowed as number here near '1e+300' at line 3 732create table t1 (a int) 733partition by list (a) 734(partition p0 values in (1)); 735create procedure pz() 736alter table t1 engine = myisam; 737call pz(); 738call pz(); 739drop procedure pz; 740drop table t1; 741create table t1 (a bigint) 742partition by range (a) 743(partition p0 values less than (0xFFFFFFFFFFFFFFFF), 744partition p1 values less than (10)); 745ERROR HY000: VALUES value for partition 'p0' must have type INT 746create table t1 (a bigint) 747partition by list (a) 748(partition p0 values in (0xFFFFFFFFFFFFFFFF), 749partition p1 values in (10)); 750ERROR HY000: VALUES value for partition 'p0' must have type INT 751create table t1 (a bigint unsigned) 752partition by range (a) 753(partition p0 values less than (100), 754partition p1 values less than MAXVALUE); 755insert into t1 values (1); 756drop table t1; 757create table t1 (a bigint unsigned) 758partition by hash (a); 759insert into t1 values (0xFFFFFFFFFFFFFFFD); 760insert into t1 values (0xFFFFFFFFFFFFFFFE); 761select * from t1 where (a + 1) < 10; 762a 763select * from t1 where (a + 1) > 10; 764a 76518446744073709551613 76618446744073709551614 767drop table t1; 768create table t1 (a int) 769partition by key(a) 770(partition p0 engine = MEMORY); 771drop table t1; 772create table t1 (a int) 773partition by range (a) 774subpartition by key (a) 775(partition p0 values less than (1)); 776alter table t1 add partition (partition p1 values less than (2)); 777show create table t1; 778Table Create Table 779t1 CREATE TABLE `t1` ( 780 `a` int(11) DEFAULT NULL 781) ENGINE=MyISAM DEFAULT CHARSET=latin1 782 PARTITION BY RANGE (`a`) 783SUBPARTITION BY KEY (`a`) 784(PARTITION `p0` VALUES LESS THAN (1) ENGINE = MyISAM, 785 PARTITION `p1` VALUES LESS THAN (2) ENGINE = MyISAM) 786alter table t1 reorganize partition p1 into (partition p1 values less than (3)); 787show create table t1; 788Table Create Table 789t1 CREATE TABLE `t1` ( 790 `a` int(11) DEFAULT NULL 791) ENGINE=MyISAM DEFAULT CHARSET=latin1 792 PARTITION BY RANGE (`a`) 793SUBPARTITION BY KEY (`a`) 794(PARTITION `p0` VALUES LESS THAN (1) ENGINE = MyISAM, 795 PARTITION `p1` VALUES LESS THAN (3) ENGINE = MyISAM) 796drop table t1; 797CREATE TABLE t1 ( 798a int not null, 799b int not null, 800c int not null, 801primary key(a,b)) 802partition by key (a); 803select count(*) from t1; 804count(*) 8050 806show create table t1; 807Table Create Table 808t1 CREATE TABLE `t1` ( 809 `a` int(11) NOT NULL, 810 `b` int(11) NOT NULL, 811 `c` int(11) NOT NULL, 812 PRIMARY KEY (`a`,`b`) 813) ENGINE=MyISAM DEFAULT CHARSET=latin1 814 PARTITION BY KEY (`a`) 815drop table t1; 816CREATE TABLE t1 ( 817a int not null, 818b int not null, 819c int not null, 820primary key(a,b)) 821partition by key (a, b); 822drop table t1; 823CREATE TABLE t1 ( 824a int not null, 825b int not null, 826c int not null, 827primary key(a,b)) 828partition by key (a) 829partitions 3 830(partition x1, partition x2, partition x3); 831drop table t1; 832CREATE TABLE t1 ( 833a int not null, 834b int not null, 835c int not null, 836primary key(a,b)) 837partition by key (a) 838partitions 3 839(partition x1 nodegroup 0, 840partition x2 nodegroup 1, 841partition x3 nodegroup 2); 842drop table t1; 843CREATE TABLE t1 ( 844a int not null, 845b int not null, 846c int not null, 847primary key(a,b)) 848partition by key (a) 849partitions 3 850(partition x1 engine myisam, 851partition x2 engine myisam, 852partition x3 engine myisam); 853drop table t1; 854CREATE TABLE t1 ( 855a int not null, 856b int not null, 857c int not null, 858primary key(a,b)) 859partition by key (a) 860partitions 3 861(partition x1 tablespace ts1, 862partition x2 tablespace ts2, 863partition x3 tablespace ts3); 864CREATE TABLE t2 LIKE t1; 865drop table t2; 866drop table t1; 867CREATE TABLE t1 ( 868a int not null, 869b int not null, 870c int not null, 871primary key(a,b)) 872partition by list (a) 873partitions 3 874(partition x1 values in (1,2,9,4) tablespace ts1, 875partition x2 values in (3, 11, 5, 7) tablespace ts2, 876partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3); 877drop table t1; 878CREATE TABLE t1 ( 879a int not null, 880b int not null, 881c int not null, 882primary key(a,b)) 883partition by list (b*a) 884partitions 3 885(partition x1 values in (1,2,9,4) tablespace ts1, 886partition x2 values in (3, 11, 5, 7) tablespace ts2, 887partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3); 888drop table t1; 889CREATE TABLE t1 ( 890a int not null, 891b int not null, 892c int not null, 893primary key(a,b)) 894partition by list (b*a) 895(partition x1 values in (1) tablespace ts1, 896partition x2 values in (3, 11, 5, 7) tablespace ts2, 897partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3); 898drop table t1; 899CREATE TABLE t1 ( 900a int not null) 901partition by key(a); 902LOCK TABLES t1 WRITE; 903insert into t1 values (1); 904insert into t1 values (2); 905insert into t1 values (3); 906insert into t1 values (4); 907UNLOCK TABLES; 908drop table t1; 909CREATE TABLE t1 (a int, name VARCHAR(50), purchased DATE) 910PARTITION BY RANGE (a) 911(PARTITION p0 VALUES LESS THAN (3), 912PARTITION p1 VALUES LESS THAN (7), 913PARTITION p2 VALUES LESS THAN (9), 914PARTITION p3 VALUES LESS THAN (11)); 915INSERT INTO t1 VALUES 916(1, 'desk organiser', '2003-10-15'), 917(2, 'CD player', '1993-11-05'), 918(3, 'TV set', '1996-03-10'), 919(4, 'bookcase', '1982-01-10'), 920(5, 'exercise bike', '2004-05-09'), 921(6, 'sofa', '1987-06-05'), 922(7, 'popcorn maker', '2001-11-22'), 923(8, 'acquarium', '1992-08-04'), 924(9, 'study desk', '1984-09-16'), 925(10, 'lava lamp', '1998-12-25'); 926SELECT * from t1 ORDER BY a; 927a name purchased 9281 desk organiser 2003-10-15 9292 CD player 1993-11-05 9303 TV set 1996-03-10 9314 bookcase 1982-01-10 9325 exercise bike 2004-05-09 9336 sofa 1987-06-05 9347 popcorn maker 2001-11-22 9358 acquarium 1992-08-04 9369 study desk 1984-09-16 93710 lava lamp 1998-12-25 938ALTER TABLE t1 DROP PARTITION p0; 939SELECT * from t1 ORDER BY a; 940a name purchased 9413 TV set 1996-03-10 9424 bookcase 1982-01-10 9435 exercise bike 2004-05-09 9446 sofa 1987-06-05 9457 popcorn maker 2001-11-22 9468 acquarium 1992-08-04 9479 study desk 1984-09-16 94810 lava lamp 1998-12-25 949drop table t1; 950CREATE TABLE t1 (a int) 951PARTITION BY LIST (a) 952(PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (4,5,6)); 953insert into t1 values (1),(2),(3),(4),(5),(6); 954select * from t1; 955a 9561 9572 9583 9594 9605 9616 962truncate t1; 963select * from t1; 964a 965truncate t1; 966select * from t1; 967a 968drop table t1; 969CREATE TABLE t1 (a int, b int, primary key(a,b)) 970PARTITION BY KEY(b,a) PARTITIONS 4; 971insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6); 972select * from t1 where a = 4; 973a b 9744 4 975drop table t1; 976CREATE TABLE t1 (c1 INT, c2 INT, PRIMARY KEY USING BTREE (c1,c2)) ENGINE=MEMORY 977PARTITION BY KEY(c2,c1) PARTITIONS 4; 978INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6); 979SELECT * FROM t1 WHERE c1 = 4; 980c1 c2 9814 4 982DROP TABLE t1; 983CREATE TABLE t1 (a int) 984PARTITION BY LIST (a) 985PARTITIONS 1 986(PARTITION x1 VALUES IN (1) ENGINE=MEMORY); 987show create table t1; 988Table Create Table 989t1 CREATE TABLE `t1` ( 990 `a` int(11) DEFAULT NULL 991) ENGINE=MEMORY DEFAULT CHARSET=latin1 992 PARTITION BY LIST (`a`) 993(PARTITION `x1` VALUES IN (1) ENGINE = MEMORY) 994drop table t1; 995CREATE TABLE t1 (a int, unique(a)) 996PARTITION BY LIST (a) 997(PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20)); 998REPLACE t1 SET a = 4; 999ERROR HY000: Table has no partition for value 4 1000drop table t1; 1001CREATE TABLE t1 (a int) 1002PARTITION BY LIST (a) 1003(PARTITION x1 VALUES IN (2), PARTITION x2 VALUES IN (3)); 1004insert into t1 values (2), (3); 1005insert into t1 values (4); 1006ERROR HY000: Table has no partition for value 4 1007insert into t1 values (1); 1008ERROR HY000: Table has no partition for value 1 1009drop table t1; 1010CREATE TABLE t1 (a int) 1011PARTITION BY HASH(a) 1012PARTITIONS 5; 1013SHOW CREATE TABLE t1; 1014Table Create Table 1015t1 CREATE TABLE `t1` ( 1016 `a` int(11) DEFAULT NULL 1017) ENGINE=MyISAM DEFAULT CHARSET=latin1 1018 PARTITION BY HASH (`a`) 1019PARTITIONS 5 1020drop table t1; 1021CREATE TABLE t1 (a int) 1022PARTITION BY RANGE (a) 1023(PARTITION x1 VALUES LESS THAN (2)); 1024insert into t1 values (1); 1025update t1 set a = 5; 1026ERROR HY000: Table has no partition for value 5 1027drop table t1; 1028CREATE TABLE t1 (a int) 1029PARTITION BY LIST (a) 1030(PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20)); 1031analyze table t1; 1032Table Op Msg_type Msg_text 1033test.t1 analyze status Engine-independent statistics collected 1034test.t1 analyze status OK 1035drop table t1; 1036create table t1 1037(a int) 1038partition by range (a) 1039( partition p0 values less than(10), 1040partition p1 values less than (20), 1041partition p2 values less than (25)); 1042alter table t1 reorganize partition p2 into (partition p2 values less than (30)); 1043show create table t1; 1044Table Create Table 1045t1 CREATE TABLE `t1` ( 1046 `a` int(11) DEFAULT NULL 1047) ENGINE=MyISAM DEFAULT CHARSET=latin1 1048 PARTITION BY RANGE (`a`) 1049(PARTITION `p0` VALUES LESS THAN (10) ENGINE = MyISAM, 1050 PARTITION `p1` VALUES LESS THAN (20) ENGINE = MyISAM, 1051 PARTITION `p2` VALUES LESS THAN (30) ENGINE = MyISAM) 1052drop table t1; 1053CREATE TABLE t1 (a int, b int) 1054PARTITION BY RANGE (a) 1055(PARTITION x0 VALUES LESS THAN (2), 1056PARTITION x1 VALUES LESS THAN (4), 1057PARTITION x2 VALUES LESS THAN (6), 1058PARTITION x3 VALUES LESS THAN (8), 1059PARTITION x4 VALUES LESS THAN (10), 1060PARTITION x5 VALUES LESS THAN (12), 1061PARTITION x6 VALUES LESS THAN (14), 1062PARTITION x7 VALUES LESS THAN (16), 1063PARTITION x8 VALUES LESS THAN (18), 1064PARTITION x9 VALUES LESS THAN (20)); 1065ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO 1066(PARTITION x1 VALUES LESS THAN (6)); 1067show create table t1; 1068Table Create Table 1069t1 CREATE TABLE `t1` ( 1070 `a` int(11) DEFAULT NULL, 1071 `b` int(11) DEFAULT NULL 1072) ENGINE=MyISAM DEFAULT CHARSET=latin1 1073 PARTITION BY RANGE (`a`) 1074(PARTITION `x1` VALUES LESS THAN (6) ENGINE = MyISAM, 1075 PARTITION `x3` VALUES LESS THAN (8) ENGINE = MyISAM, 1076 PARTITION `x4` VALUES LESS THAN (10) ENGINE = MyISAM, 1077 PARTITION `x5` VALUES LESS THAN (12) ENGINE = MyISAM, 1078 PARTITION `x6` VALUES LESS THAN (14) ENGINE = MyISAM, 1079 PARTITION `x7` VALUES LESS THAN (16) ENGINE = MyISAM, 1080 PARTITION `x8` VALUES LESS THAN (18) ENGINE = MyISAM, 1081 PARTITION `x9` VALUES LESS THAN (20) ENGINE = MyISAM) 1082drop table t1; 1083create table t1 (a int not null, b int not null) partition by LIST (a+b) ( 1084partition p0 values in (12), 1085partition p1 values in (14) 1086); 1087insert into t1 values (10,1); 1088ERROR HY000: Table has no partition for value 11 1089drop table t1; 1090create table t1 (f1 integer,f2 integer, f3 varchar(10), primary key(f1,f2)) 1091partition by range(f1) subpartition by hash(f2) subpartitions 2 1092(partition p1 values less than (0), 1093partition p2 values less than (2), 1094partition p3 values less than (2147483647)); 1095insert into t1 values(10,10,'10'); 1096insert into t1 values(2,2,'2'); 1097select * from t1 where f1 = 2; 1098f1 f2 f3 10992 2 2 1100drop table t1; 1101create table t1 (f1 integer,f2 integer, unique index(f1)) 1102partition by range(f1 div 2) 1103subpartition by hash(f1) subpartitions 2 1104(partition partb values less than (2), 1105partition parte values less than (4), 1106partition partf values less than (10000)); 1107insert into t1 values(10,1); 1108select * from t1 where f1 = 10; 1109f1 f2 111010 1 1111drop table t1; 1112set session default_storage_engine= 'memory'; 1113create table t1 (f_int1 int(11) default null) engine = memory 1114partition by range (f_int1) subpartition by hash (f_int1) 1115(partition part1 values less than (1000) 1116(subpartition subpart11 engine = memory)); 1117drop table t1; 1118set session default_storage_engine='myisam'; 1119create table t1 (f_int1 integer, f_int2 integer, primary key (f_int1)) 1120partition by hash(f_int1) partitions 2; 1121insert into t1 values (1,1),(2,2); 1122replace into t1 values (1,1),(2,2); 1123drop table t1; 1124create table t1 (s1 int, unique (s1)) partition by list (s1) (partition x1 VALUES in (10), partition x2 values in (20)); 1125alter table t1 add partition (partition x3 values in (30)); 1126drop table t1; 1127create table t1 (a int) 1128partition by key(a) 1129partitions 2 1130(partition p0 engine=myisam, partition p1 engine=myisam); 1131show create table t1; 1132Table Create Table 1133t1 CREATE TABLE `t1` ( 1134 `a` int(11) DEFAULT NULL 1135) ENGINE=MyISAM DEFAULT CHARSET=latin1 1136 PARTITION BY KEY (`a`) 1137(PARTITION `p0` ENGINE = MyISAM, 1138 PARTITION `p1` ENGINE = MyISAM) 1139alter table t1; 1140show create table t1; 1141Table Create Table 1142t1 CREATE TABLE `t1` ( 1143 `a` int(11) DEFAULT NULL 1144) ENGINE=MyISAM DEFAULT CHARSET=latin1 1145 PARTITION BY KEY (`a`) 1146(PARTITION `p0` ENGINE = MyISAM, 1147 PARTITION `p1` ENGINE = MyISAM) 1148alter table t1 engine=myisam; 1149show create table t1; 1150Table Create Table 1151t1 CREATE TABLE `t1` ( 1152 `a` int(11) DEFAULT NULL 1153) ENGINE=MyISAM DEFAULT CHARSET=latin1 1154 PARTITION BY KEY (`a`) 1155(PARTITION `p0` ENGINE = MyISAM, 1156 PARTITION `p1` ENGINE = MyISAM) 1157alter table t1 engine=heap; 1158show create table t1; 1159Table Create Table 1160t1 CREATE TABLE `t1` ( 1161 `a` int(11) DEFAULT NULL 1162) ENGINE=MEMORY DEFAULT CHARSET=latin1 1163 PARTITION BY KEY (`a`) 1164(PARTITION `p0` ENGINE = MEMORY, 1165 PARTITION `p1` ENGINE = MEMORY) 1166alter table t1 remove partitioning; 1167show create table t1; 1168Table Create Table 1169t1 CREATE TABLE `t1` ( 1170 `a` int(11) DEFAULT NULL 1171) ENGINE=MEMORY DEFAULT CHARSET=latin1 1172drop table t1; 1173create table t1 (a int) 1174engine=myisam 1175partition by key(a) 1176partitions 2 1177(partition p0 engine=myisam, partition p1 engine=myisam); 1178show create table t1; 1179Table Create Table 1180t1 CREATE TABLE `t1` ( 1181 `a` int(11) DEFAULT NULL 1182) ENGINE=MyISAM DEFAULT CHARSET=latin1 1183 PARTITION BY KEY (`a`) 1184(PARTITION `p0` ENGINE = MyISAM, 1185 PARTITION `p1` ENGINE = MyISAM) 1186alter table t1 add column b int remove partitioning; 1187show create table t1; 1188Table Create Table 1189t1 CREATE TABLE `t1` ( 1190 `a` int(11) DEFAULT NULL, 1191 `b` int(11) DEFAULT NULL 1192) ENGINE=MyISAM DEFAULT CHARSET=latin1 1193alter table t1 1194engine=myisam 1195partition by key(a) 1196(partition p0 engine=myisam, partition p1); 1197show create table t1; 1198Table Create Table 1199t1 CREATE TABLE `t1` ( 1200 `a` int(11) DEFAULT NULL, 1201 `b` int(11) DEFAULT NULL 1202) ENGINE=MyISAM DEFAULT CHARSET=latin1 1203 PARTITION BY KEY (`a`) 1204(PARTITION `p0` ENGINE = MyISAM, 1205 PARTITION `p1` ENGINE = MyISAM) 1206alter table t1 1207engine=heap 1208partition by key(a) 1209(partition p0, partition p1 engine=heap); 1210show create table t1; 1211Table Create Table 1212t1 CREATE TABLE `t1` ( 1213 `a` int(11) DEFAULT NULL, 1214 `b` int(11) DEFAULT NULL 1215) ENGINE=MEMORY DEFAULT CHARSET=latin1 1216 PARTITION BY KEY (`a`) 1217(PARTITION `p0` ENGINE = MEMORY, 1218 PARTITION `p1` ENGINE = MEMORY) 1219alter table t1 engine=myisam, add column c int remove partitioning; 1220show create table t1; 1221Table Create Table 1222t1 CREATE TABLE `t1` ( 1223 `a` int(11) DEFAULT NULL, 1224 `b` int(11) DEFAULT NULL, 1225 `c` int(11) DEFAULT NULL 1226) ENGINE=MyISAM DEFAULT CHARSET=latin1 1227alter table t1 1228engine=heap 1229partition by key (a) 1230(partition p0, partition p1); 1231show create table t1; 1232Table Create Table 1233t1 CREATE TABLE `t1` ( 1234 `a` int(11) DEFAULT NULL, 1235 `b` int(11) DEFAULT NULL, 1236 `c` int(11) DEFAULT NULL 1237) ENGINE=MEMORY DEFAULT CHARSET=latin1 1238 PARTITION BY KEY (`a`) 1239(PARTITION `p0` ENGINE = MEMORY, 1240 PARTITION `p1` ENGINE = MEMORY) 1241alter table t1 1242partition by key (a) 1243(partition p0, partition p1); 1244show create table t1; 1245Table Create Table 1246t1 CREATE TABLE `t1` ( 1247 `a` int(11) DEFAULT NULL, 1248 `b` int(11) DEFAULT NULL, 1249 `c` int(11) DEFAULT NULL 1250) ENGINE=MEMORY DEFAULT CHARSET=latin1 1251 PARTITION BY KEY (`a`) 1252(PARTITION `p0` ENGINE = MEMORY, 1253 PARTITION `p1` ENGINE = MEMORY) 1254alter table t1 1255engine=heap 1256partition by key (a) 1257(partition p0, partition p1); 1258show create table t1; 1259Table Create Table 1260t1 CREATE TABLE `t1` ( 1261 `a` int(11) DEFAULT NULL, 1262 `b` int(11) DEFAULT NULL, 1263 `c` int(11) DEFAULT NULL 1264) ENGINE=MEMORY DEFAULT CHARSET=latin1 1265 PARTITION BY KEY (`a`) 1266(PARTITION `p0` ENGINE = MEMORY, 1267 PARTITION `p1` ENGINE = MEMORY) 1268alter table t1 1269partition by key(a) 1270(partition p0, partition p1 engine=heap); 1271alter table t1 1272partition by key(a) 1273(partition p0 engine=heap, partition p1); 1274alter table t1 1275engine=heap 1276partition by key (a) 1277(partition p0 engine=heap, partition p1 engine=myisam); 1278ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MariaDB 1279alter table t1 1280partition by key (a) 1281(partition p0 engine=heap, partition p1 engine=myisam); 1282ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MariaDB 1283drop table t1; 1284CREATE TABLE t1 ( 1285f_int1 INTEGER, f_int2 INTEGER, 1286f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000) 1287) 1288PARTITION BY RANGE(f_int1 DIV 2) 1289SUBPARTITION BY HASH(f_int1) 1290SUBPARTITIONS 2 1291(PARTITION parta VALUES LESS THAN (0), 1292PARTITION partb VALUES LESS THAN (5), 1293PARTITION parte VALUES LESS THAN (10), 1294PARTITION partf VALUES LESS THAN (2147483647)); 1295INSERT INTO t1 SET f_int1 = NULL , f_int2 = -20, f_char1 = CAST(-20 AS CHAR), 1296f_char2 = CAST(-20 AS CHAR), f_charbig = '#NULL#'; 1297SELECT * FROM t1 WHERE f_int1 IS NULL; 1298f_int1 f_int2 f_char1 f_char2 f_charbig 1299NULL -20 -20 -20 #NULL# 1300SELECT * FROM t1; 1301f_int1 f_int2 f_char1 f_char2 f_charbig 1302NULL -20 -20 -20 #NULL# 1303drop table t1; 1304CREATE TABLE t1 ( 1305f_int1 INTEGER, f_int2 INTEGER, 1306f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000) ) 1307PARTITION BY LIST(MOD(f_int1,2)) 1308SUBPARTITION BY KEY(f_int1) 1309(PARTITION part1 VALUES IN (-1) (SUBPARTITION sp1, SUBPARTITION sp2), 1310PARTITION part2 VALUES IN (0) (SUBPARTITION sp3, SUBPARTITION sp5), 1311PARTITION part3 VALUES IN (1) (SUBPARTITION sp4, SUBPARTITION sp6)); 1312INSERT INTO t1 SET f_int1 = 2, f_int2 = 2, f_char1 = '2', f_char2 = '2', f_charbig = '===2==='; 1313INSERT INTO t1 SET f_int1 = 2, f_int2 = 2, f_char1 = '2', f_char2 = '2', f_charbig = '===2==='; 1314SELECT * FROM t1 WHERE f_int1 IS NULL; 1315f_int1 f_int2 f_char1 f_char2 f_charbig 1316drop table t1; 1317create procedure p () 1318begin 1319create table t1 (s1 mediumint,s2 mediumint) 1320partition by list (s2) 1321(partition p1 values in (0), 1322partition p2 values in (1)); 1323end// 1324call p()// 1325drop procedure p// 1326drop table t1// 1327create procedure p () 1328begin 1329create table t1 (a int not null,b int not null,c int not null,primary key (a,b)) 1330partition by range (a) 1331subpartition by hash (a+b) 1332(partition x1 values less than (1) 1333(subpartition x11, 1334subpartition x12), 1335partition x2 values less than (5) 1336(subpartition x21, 1337subpartition x22)); 1338end// 1339call p()// 1340drop procedure p// 1341drop table t1// 1342create table t1 (a int,b int,c int,key(a,b)) 1343partition by range (a) 1344partitions 3 1345(partition x1 values less than (0) tablespace ts1, 1346partition x2 values less than (10) tablespace ts2, 1347partition x3 values less than maxvalue tablespace ts3); 1348insert into t1 values (NULL, 1, 1); 1349insert into t1 values (0, 1, 1); 1350insert into t1 values (12, 1, 1); 1351select partition_name, partition_description, table_rows 1352from information_schema.partitions where table_schema ='test'; 1353partition_name partition_description table_rows 1354x1 0 1 1355x2 10 1 1356x3 MAXVALUE 1 1357drop table t1; 1358create table t1 (a int,b int, c int) 1359partition by list(a) 1360partitions 2 1361(partition x123 values in (11,12), 1362partition x234 values in (1 ,NULL, NULL)); 1363ERROR HY000: Multiple definition of same constant in list partitioning 1364create table t1 (a int,b int, c int) 1365partition by list(a) 1366partitions 2 1367(partition x123 values in (11, NULL), 1368partition x234 values in (1 ,NULL)); 1369ERROR HY000: Multiple definition of same constant in list partitioning 1370create table t1 (a int,b int, c int) 1371partition by list(a) 1372partitions 2 1373(partition x123 values in (11, 12), 1374partition x234 values in (5, 1)); 1375insert into t1 values (NULL,1,1); 1376ERROR HY000: Table has no partition for value NULL 1377drop table t1; 1378create table t1 (a int,b int, c int) 1379partition by list(a) 1380partitions 2 1381(partition x123 values in (11, 12), 1382partition x234 values in (NULL, 1)); 1383insert into t1 values (11,1,6); 1384insert into t1 values (NULL,1,1); 1385select partition_name, partition_description, table_rows 1386from information_schema.partitions where table_schema ='test'; 1387partition_name partition_description table_rows 1388x123 11,12 1 1389x234 NULL,1 1 1390drop table t1; 1391create table t1 (a int) 1392partition by list (a) 1393(partition p0 values in (1)); 1394alter table t1 rebuild partition; 1395ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 1396drop table t1; 1397create table t1 (a int) 1398partition by list (a) 1399(partition p0 values in (5)); 1400insert into t1 values (0); 1401ERROR HY000: Table has no partition for value 0 1402drop table t1; 1403create table t1 (a int) 1404partition by range (a) subpartition by hash (a) 1405(partition p0 values less than (100)); 1406show create table t1; 1407Table Create Table 1408t1 CREATE TABLE `t1` ( 1409 `a` int(11) DEFAULT NULL 1410) ENGINE=MyISAM DEFAULT CHARSET=latin1 1411 PARTITION BY RANGE (`a`) 1412SUBPARTITION BY HASH (`a`) 1413(PARTITION `p0` VALUES LESS THAN (100) ENGINE = MyISAM) 1414alter table t1 add partition (partition p1 values less than (200) 1415(subpartition subpart21)); 1416show create table t1; 1417Table Create Table 1418t1 CREATE TABLE `t1` ( 1419 `a` int(11) DEFAULT NULL 1420) ENGINE=MyISAM DEFAULT CHARSET=latin1 1421 PARTITION BY RANGE (`a`) 1422SUBPARTITION BY HASH (`a`) 1423(PARTITION `p0` VALUES LESS THAN (100) 1424 (SUBPARTITION `p0sp0` ENGINE = MyISAM), 1425 PARTITION `p1` VALUES LESS THAN (200) 1426 (SUBPARTITION `subpart21` ENGINE = MyISAM)) 1427drop table t1; 1428create table t1 (a int) 1429partition by key (a); 1430show create table t1; 1431Table Create Table 1432t1 CREATE TABLE `t1` ( 1433 `a` int(11) DEFAULT NULL 1434) ENGINE=MyISAM DEFAULT CHARSET=latin1 1435 PARTITION BY KEY (`a`) 1436alter table t1 add partition (partition p1); 1437show create table t1; 1438Table Create Table 1439t1 CREATE TABLE `t1` ( 1440 `a` int(11) DEFAULT NULL 1441) ENGINE=MyISAM DEFAULT CHARSET=latin1 1442 PARTITION BY KEY (`a`) 1443(PARTITION `p0` ENGINE = MyISAM, 1444 PARTITION `p1` ENGINE = MyISAM) 1445drop table t1; 1446create table t1 (a int, b int) 1447partition by range (a) 1448subpartition by hash(a) 1449(partition p0 values less than (0) (subpartition sp0), 1450partition p1 values less than (1)); 1451ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')' at line 5 1452create table t1 (a int, b int) 1453partition by range (a) 1454subpartition by hash(a) 1455(partition p0 values less than (0), 1456partition p1 values less than (1) (subpartition sp0)); 1457ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'subpartition sp0))' at line 5 1458create table t1 (a int, b int) 1459partition by list (a) 1460subpartition by hash(a) 1461(partition p0 values in (0), 1462partition p1 values in (1) (subpartition sp0)); 1463ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'subpartition sp0))' at line 5 1464create table t1 (a int) 1465partition by hash (a) 1466(partition p0 (subpartition sp0)); 1467ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning 1468create table t1 (a int) 1469partition by range (a) 1470(partition p0 values less than (1)); 1471alter table t1 add partition (partition p1 values in (2)); 1472ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition 1473alter table t1 add partition (partition p1); 1474ERROR HY000: Syntax error: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition 1475drop table t1; 1476create table t1 (a int) 1477partition by list (a) 1478(partition p0 values in (1)); 1479alter table t1 add partition (partition p1 values less than (2)); 1480ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition 1481alter table t1 add partition (partition p1); 1482ERROR HY000: Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition 1483drop table t1; 1484create table t1 (a int) 1485partition by hash (a) 1486(partition p0); 1487alter table t1 add partition (partition p1 values less than (2)); 1488ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition 1489alter table t1 add partition (partition p1 values in (2)); 1490ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition 1491drop table t1; 1492create table t1 (a int) 1493partition by list (a) 1494(partition p0 values in (1)); 1495alter table t1 rebuild partition; 1496ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 1497drop table t1; 1498create 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)); 1499insert into t2 values (null),(null),(null); 1500select * from t2; 1501s1 15021 15032 15043 1505select * from t2 where s1 < 2; 1506s1 15071 1508update t2 set s1 = s1 + 1 order by s1 desc; 1509select * from t2 where s1 < 3; 1510s1 15112 1512select * from t2 where s1 = 2; 1513s1 15142 1515drop table t2; 1516create temporary table t1 (a int) partition by hash(a); 1517ERROR HY000: Partitioned tables do not support CREATE TEMPORARY TABLE 1518create table t1 (a int, b int) partition by list (a) 1519(partition p1 values in (1), partition p2 values in (2)); 1520alter table t1 add primary key (b); 1521ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function 1522show create table t1; 1523Table Create Table 1524t1 CREATE TABLE `t1` ( 1525 `a` int(11) DEFAULT NULL, 1526 `b` int(11) DEFAULT NULL 1527) ENGINE=MyISAM DEFAULT CHARSET=latin1 1528 PARTITION BY LIST (`a`) 1529(PARTITION `p1` VALUES IN (1) ENGINE = MyISAM, 1530 PARTITION `p2` VALUES IN (2) ENGINE = MyISAM) 1531drop table t1; 1532create table t1 (a int unsigned not null auto_increment primary key) 1533partition by key(a); 1534alter table t1 rename t2, add c char(10), comment "no comment"; 1535show create table t2; 1536Table Create Table 1537t2 CREATE TABLE `t2` ( 1538 `a` int(10) unsigned NOT NULL AUTO_INCREMENT, 1539 `c` char(10) DEFAULT NULL, 1540 PRIMARY KEY (`a`) 1541) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='no comment' 1542 PARTITION BY KEY (`a`) 1543drop table t2; 1544create table t1 (f1 int) partition by hash (f1) as select 1; 1545drop table t1; 1546prepare stmt1 from 'create table t1 (s1 int) partition by hash (s1)'; 1547execute stmt1; 1548execute stmt1; 1549ERROR 42S01: Table 't1' already exists 1550drop table t1; 1551CREATE PROCEDURE test.p1(IN i INT) 1552BEGIN 1553DECLARE CONTINUE HANDLER FOR sqlexception BEGIN END; 1554DROP TABLE IF EXISTS t1; 1555CREATE TABLE t1 (num INT,PRIMARY KEY(num)); 1556START TRANSACTION; 1557INSERT INTO t1 VALUES(i); 1558savepoint t1_save; 1559INSERT INTO t1 VALUES (14); 1560ROLLBACK to savepoint t1_save; 1561COMMIT; 1562END| 1563CALL test.p1(12); 1564Warnings: 1565Warning 1196 Some non-transactional changed tables couldn't be rolled back 1566CALL test.p1(13); 1567Warnings: 1568Warning 1196 Some non-transactional changed tables couldn't be rolled back 1569drop table t1; 1570drop procedure test.p1; 1571CREATE TABLE t1 (a int not null) 1572partition by key(a) 1573(partition p0 COMMENT='first partition'); 1574drop table t1; 1575CREATE TABLE t1 (`a b` int not null) 1576partition by key(`a b`); 1577drop table t1; 1578CREATE TABLE t1 (`a b` int not null) 1579partition by hash(`a b`); 1580drop table t1; 1581create table t1 (f1 integer) partition by range(f1) 1582(partition p1 values less than (0), partition p2 values less than (10)); 1583insert into t1 set f1 = null; 1584select * from t1 where f1 is null; 1585f1 1586NULL 1587explain partitions select * from t1 where f1 is null; 1588id select_type table partitions type possible_keys key key_len ref rows Extra 15891 SIMPLE t1 p1 system NULL NULL NULL NULL 1 1590drop table t1; 1591create table t1 (f1 integer) partition by list(f1) 1592(partition p1 values in (1), partition p2 values in (null)); 1593insert into t1 set f1 = null; 1594insert into t1 set f1 = 1; 1595select * from t1 where f1 is null or f1 = 1; 1596f1 15971 1598NULL 1599drop table t1; 1600create table t1 (f1 smallint) 1601partition by list (f1) (partition p0 values in (null)); 1602insert into t1 values (null); 1603select * from t1 where f1 is null; 1604f1 1605NULL 1606select * from t1 where f1 < 1; 1607f1 1608select * from t1 where f1 <= NULL; 1609f1 1610select * from t1 where f1 < NULL; 1611f1 1612select * from t1 where f1 >= NULL; 1613f1 1614select * from t1 where f1 > NULL; 1615f1 1616select * from t1 where f1 > 1; 1617f1 1618drop table t1; 1619create table t1 (f1 smallint) 1620partition by range (f1) (partition p0 values less than (0)); 1621insert into t1 values (null); 1622select * from t1 where f1 is null; 1623f1 1624NULL 1625drop table t1; 1626create table t1 (f1 integer) partition by list(f1) 1627( 1628partition p1 values in (1), 1629partition p2 values in (NULL), 1630partition p3 values in (2), 1631partition p4 values in (3), 1632partition p5 values in (4) 1633); 1634insert into t1 values (1),(2),(3),(4),(null); 1635select * from t1 where f1 < 3; 1636f1 16371 16382 1639explain partitions select * from t1 where f1 < 3; 1640id select_type table partitions type possible_keys key key_len ref rows Extra 16411 SIMPLE t1 p1,p3 ALL NULL NULL NULL NULL 2 Using where 1642select * from t1 where f1 is null; 1643f1 1644NULL 1645explain partitions select * from t1 where f1 is null; 1646id select_type table partitions type possible_keys key key_len ref rows Extra 16471 SIMPLE t1 p2 system NULL NULL NULL NULL 1 1648drop table t1; 1649create table t1 (f1 int) partition by list(f1 div 2) 1650( 1651partition p1 values in (1), 1652partition p2 values in (NULL), 1653partition p3 values in (2), 1654partition p4 values in (3), 1655partition p5 values in (4) 1656); 1657insert into t1 values (2),(4),(6),(8),(null); 1658select * from t1 where f1 < 3; 1659f1 16602 1661explain partitions select * from t1 where f1 < 3; 1662id select_type table partitions type possible_keys key key_len ref rows Extra 16631 SIMPLE t1 p1,p2,p3,p4,p5 ALL NULL NULL NULL NULL 5 Using where 1664select * from t1 where f1 is null; 1665f1 1666NULL 1667explain partitions select * from t1 where f1 is null; 1668id select_type table partitions type possible_keys key key_len ref rows Extra 16691 SIMPLE t1 p2 system NULL NULL NULL NULL 1 1670drop table t1; 1671create table t1 (a int) partition by LIST(a) ( 1672partition pn values in (NULL), 1673partition p0 values in (0), 1674partition p1 values in (1), 1675partition p2 values in (2) 1676); 1677insert into t1 values (NULL),(0),(1),(2); 1678select * from t1 where a is null or a < 2; 1679a 1680NULL 16810 16821 1683explain partitions select * from t1 where a is null or a < 2; 1684id select_type table partitions type possible_keys key key_len ref rows Extra 16851 SIMPLE t1 pn,p0,p1 ALL NULL NULL NULL NULL 3 Using where 1686select * from t1 where a is null or a < 0 or a > 1; 1687a 1688NULL 16892 1690explain partitions select * from t1 where a is null or a < 0 or a > 1; 1691id select_type table partitions type possible_keys key key_len ref rows Extra 16921 SIMPLE t1 pn,p2 ALL NULL NULL NULL NULL 2 Using where 1693drop table t1; 1694CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, name VARCHAR(20)) 1695ENGINE=MyISAM DEFAULT CHARSET=latin1 1696PARTITION BY RANGE(id) 1697(PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM, 1698PARTITION p1 VALUES LESS THAN (20) ENGINE = MyISAM, 1699PARTITION p2 VALUES LESS THAN (30) ENGINE = MyISAM); 1700SHOW TABLE STATUS; 1701Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary 1702t1 MyISAM 10 Dynamic 0 0 0 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N 1703DROP TABLE t1; 1704create table t1 (a bigint unsigned) 1705partition by list (a) 1706(partition p0 values in (0-1)); 1707ERROR HY000: Partition constant is out of partition function domain 1708create table t1 (a bigint unsigned) 1709partition by range (a) 1710(partition p0 values less than (10)); 1711insert into t1 values (0xFFFFFFFFFFFFFFFF); 1712ERROR HY000: Table has no partition for value 18446744073709551615 1713drop table t1; 1714create table t1 (a int) 1715partition by list (a) 1716(partition `s1 s2` values in (0)); 1717drop table t1; 1718create table t1 (a int) 1719partition by list (a) 1720(partition `7` values in (0)); 1721drop table t1; 1722create table t1 (a int) 1723partition by list (a) 1724(partition `s1 s2 ` values in (0)); 1725ERROR HY000: Incorrect partition name 1726create table t1 (a int) 1727partition by list (a) 1728subpartition by hash (a) 1729(partition p1 values in (0) (subpartition `p1 p2 `)); 1730ERROR HY000: Incorrect partition name 1731CREATE TABLE t1 (a int) 1732PARTITION BY LIST (a) 1733(PARTITION p0 VALUES IN (NULL)); 1734SHOW CREATE TABLE t1; 1735Table Create Table 1736t1 CREATE TABLE `t1` ( 1737 `a` int(11) DEFAULT NULL 1738) ENGINE=MyISAM DEFAULT CHARSET=latin1 1739 PARTITION BY LIST (`a`) 1740(PARTITION `p0` VALUES IN (NULL) ENGINE = MyISAM) 1741DROP TABLE t1; 1742CREATE TABLE t1 (a int) 1743PARTITION BY RANGE(a) 1744(PARTITION p0 VALUES LESS THAN (NULL)); 1745ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN 1746create table t1 (s1 int auto_increment primary key) 1747partition by list (s1) 1748(partition p1 values in (1), 1749partition p2 values in (2), 1750partition p3 values in (3)); 1751insert into t1 values (null); 1752insert into t1 values (null); 1753insert into t1 values (null); 1754select auto_increment from information_schema.tables where table_name='t1'; 1755auto_increment 17564 1757select * from t1; 1758s1 17591 17602 17613 1762drop table t1; 1763create table t1 (a int) engine=memory 1764partition by key(a); 1765insert into t1 values (1); 1766create index inx1 on t1(a); 1767drop table t1; 1768create table t1 (a int) 1769PARTITION BY KEY (a) 1770(PARTITION p0); 1771set session sql_mode='no_table_options'; 1772show create table t1; 1773Table Create Table 1774t1 CREATE TABLE `t1` ( 1775 `a` int(11) DEFAULT NULL 1776) 1777 PARTITION BY KEY (`a`) 1778(PARTITION `p0`) 1779set session sql_mode=''; 1780drop table t1; 1781create table t1 (a int) 1782partition by key (a) 1783(partition p0 engine = MERGE); 1784ERROR HY000: Engine cannot be used in partitioned tables 1785create table t1 (a varchar(1)) 1786partition by key (a) 1787as select 'a'; 1788show create table t1; 1789Table Create Table 1790t1 CREATE TABLE `t1` ( 1791 `a` varchar(1) DEFAULT NULL 1792) ENGINE=MyISAM DEFAULT CHARSET=latin1 1793 PARTITION BY KEY (`a`) 1794drop table t1; 1795CREATE TABLE t1 (a int) ENGINE = MYISAM PARTITION BY KEY(a); 1796INSERT into t1 values (1), (2); 1797SHOW TABLE STATUS; 1798Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary 1799t1 MyISAM 10 Fixed 2 7 14 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N 1800DELETE from t1 where a = 1; 1801SHOW TABLE STATUS; 1802Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary 1803t1 MyISAM 10 Fixed 1 14 14 0 0 7 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N 1804ALTER TABLE t1 OPTIMIZE PARTITION p0; 1805Table Op Msg_type Msg_text 1806test.t1 optimize status OK 1807SHOW TABLE STATUS; 1808Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary 1809t1 MyISAM 10 Fixed 1 7 7 0 1024 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N 1810DROP TABLE t1; 1811CREATE TABLE t1 (a int, index(a)) PARTITION BY KEY(a); 1812ALTER TABLE t1 DISABLE KEYS; 1813ALTER TABLE t1 ENABLE KEYS; 1814DROP TABLE t1; 1815create table t1 (a int) 1816engine=MEMORY 1817partition by key (a); 1818REPAIR TABLE t1; 1819Table Op Msg_type Msg_text 1820test.t1 repair status OK 1821OPTIMIZE TABLE t1; 1822Table Op Msg_type Msg_text 1823test.t1 optimize note The storage engine for the table doesn't support optimize 1824CHECK TABLE t1; 1825Table Op Msg_type Msg_text 1826test.t1 check status OK 1827ANALYZE TABLE t1; 1828Table Op Msg_type Msg_text 1829test.t1 analyze status Engine-independent statistics collected 1830test.t1 analyze note The storage engine for the table doesn't support analyze 1831drop table t1; 1832drop procedure if exists mysqltest_1; 1833create table t1 (a int) 1834partition by list (a) 1835(partition p0 values in (0)); 1836insert into t1 values (0); 1837create procedure mysqltest_1 () 1838begin 1839begin 1840declare continue handler for sqlexception begin end; 1841update ignore t1 set a = 1 where a = 0; 1842end; 1843prepare stmt1 from 'alter table t1'; 1844execute stmt1; 1845end// 1846call mysqltest_1()// 1847drop table t1; 1848drop procedure mysqltest_1; 1849create table t1 (a int, index(a)) 1850partition by hash(a); 1851insert into t1 values (1),(2); 1852select * from t1 ORDER BY a DESC; 1853a 18542 18551 1856drop table t1; 1857create table t1 (a bigint unsigned not null, primary key(a)) 1858engine = myisam 1859partition by key (a) 1860partitions 10; 1861show create table t1; 1862Table Create Table 1863t1 CREATE TABLE `t1` ( 1864 `a` bigint(20) unsigned NOT NULL, 1865 PRIMARY KEY (`a`) 1866) ENGINE=MyISAM DEFAULT CHARSET=latin1 1867 PARTITION BY KEY (`a`) 1868PARTITIONS 10 1869insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), 1870(18446744073709551613), (18446744073709551612); 1871select * from t1; 1872a 187318446744073709551612 187418446744073709551613 187518446744073709551614 187618446744073709551615 1877select * from t1 where a = 18446744073709551615; 1878a 187918446744073709551615 1880delete from t1 where a = 18446744073709551615; 1881select * from t1; 1882a 188318446744073709551612 188418446744073709551613 188518446744073709551614 1886drop table t1; 1887CREATE TABLE t1 ( 1888num int(11) NOT NULL, cs int(11) NOT NULL) 1889PARTITION BY RANGE (num) SUBPARTITION BY HASH ( 1890cs) SUBPARTITIONS 2 (PARTITION p_X VALUES LESS THAN MAXVALUE); 1891ALTER TABLE t1 1892REORGANIZE PARTITION p_X INTO ( 1893PARTITION p_100 VALUES LESS THAN (100), 1894PARTITION p_X VALUES LESS THAN MAXVALUE 1895); 1896drop table t1; 1897CREATE TABLE t2 ( 1898taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 1899id int(11) NOT NULL DEFAULT '0', 1900PRIMARY KEY (id,taken), 1901KEY taken (taken) 1902) ENGINE=MyISAM DEFAULT CHARSET=latin1; 1903INSERT INTO t2 VALUES 1904('2006-09-27 21:50:01',16421), 1905('2006-10-02 21:50:01',16421), 1906('2006-09-27 21:50:01',19092), 1907('2006-09-28 21:50:01',19092), 1908('2006-09-29 21:50:01',19092), 1909('2006-09-30 21:50:01',19092), 1910('2006-10-01 21:50:01',19092), 1911('2006-10-02 21:50:01',19092), 1912('2006-09-27 21:50:01',22589), 1913('2006-09-29 21:50:01',22589); 1914CREATE TABLE t1 ( 1915id int(8) NOT NULL, 1916PRIMARY KEY (id) 1917) ENGINE=MyISAM DEFAULT CHARSET=latin1; 1918INSERT INTO t1 VALUES 1919(16421), 1920(19092), 1921(22589); 1922CREATE TABLE t4 ( 1923taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 1924id int(11) NOT NULL DEFAULT '0', 1925PRIMARY KEY (id,taken), 1926KEY taken (taken) 1927) ENGINE=MyISAM DEFAULT CHARSET=latin1 1928PARTITION BY RANGE (to_days(taken)) 1929( 1930PARTITION p01 VALUES LESS THAN (732920) , 1931PARTITION p02 VALUES LESS THAN (732950) , 1932PARTITION p03 VALUES LESS THAN MAXVALUE ) ; 1933INSERT INTO t4 select * from t2; 1934set @f_date='2006-09-28'; 1935set @t_date='2006-10-02'; 1936SELECT t1.id AS MyISAM_part 1937FROM t1 1938WHERE t1.id IN ( 1939SELECT distinct id 1940FROM t4 1941WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY)) 1942ORDER BY t1.id; 1943MyISAM_part 194416421 194519092 194622589 1947drop table t1, t2, t4; 1948CREATE TABLE t1 ( 1949taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 1950id int(11) NOT NULL DEFAULT '0', 1951status varchar(20) NOT NULL DEFAULT '', 1952PRIMARY KEY (id,taken) 1953) ENGINE=MyISAM DEFAULT CHARSET=latin1 1954PARTITION BY RANGE (to_days(taken)) 1955( 1956PARTITION p15 VALUES LESS THAN (732950) , 1957PARTITION p16 VALUES LESS THAN MAXVALUE ) ; 1958INSERT INTO t1 VALUES 1959('2006-09-27 21:50:01',22589,'Open'), 1960('2006-09-29 21:50:01',22589,'Verified'); 1961DROP TABLE IF EXISTS t2; 1962Warnings: 1963Note 1051 Unknown table 'test.t2' 1964CREATE TABLE t2 ( 1965id int(8) NOT NULL, 1966severity tinyint(4) NOT NULL DEFAULT '0', 1967priority tinyint(4) NOT NULL DEFAULT '0', 1968status varchar(20) DEFAULT NULL, 1969alien tinyint(4) NOT NULL 1970) ENGINE=MyISAM DEFAULT CHARSET=latin1; 1971INSERT INTO t2 VALUES 1972(22589,1,1,'Need Feedback',0); 1973SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified'); 1974id 197522589 1976drop table t1, t2; 1977create table t1 (c1 varchar(255),c2 tinyint,primary key(c1)) 1978partition by key (c1) partitions 10 ; 1979insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1; 1980insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1; 1981select * from t1; 1982c1 c2 1983aaa 2 1984drop table t1; 1985create table t1 (s1 bigint) partition by list (s1) (partition p1 values in (-9223372036854775808)); 1986drop table t1; 1987create table t1(a int auto_increment, b int, primary key (b, a)) 1988partition by hash(b) partitions 2; 1989insert into t1 values (null, 1); 1990show table status; 1991Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary 1992t1 MyISAM 10 Fixed 1 9 9 0 0 0 1 NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N 1993drop table t1; 1994create table t1(a int auto_increment primary key) 1995partition by key(a) partitions 2; 1996insert into t1 values (null), (null), (null); 1997show table status; 1998Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary 1999t1 MyISAM 10 Fixed 3 7 21 0 0 0 4 NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N 2000drop table t1; 2001CREATE TABLE t1(a INT NOT NULL, b TINYBLOB, KEY(a)) 2002PARTITION BY RANGE(a) ( PARTITION p0 VALUES LESS THAN (32)); 2003INSERT INTO t1 VALUES (1, REPEAT('a', 10)); 2004INSERT INTO t1 SELECT a + 1, b FROM t1; 2005INSERT INTO t1 SELECT a + 2, b FROM t1; 2006INSERT INTO t1 SELECT a + 4, b FROM t1; 2007INSERT INTO t1 SELECT a + 8, b FROM t1; 2008ALTER TABLE t1 ADD PARTITION (PARTITION p1 VALUES LESS THAN (64)); 2009ALTER TABLE t1 DROP PARTITION p1; 2010DROP TABLE t1; 2011create table t (s1 int) engine=myisam partition by key (s1); 2012create trigger t_ad after delete on t for each row insert into t values (old.s1); 2013insert into t values (1); 2014drop table t; 2015create table t2 (b int); 2016create table t1 (b int) 2017PARTITION BY RANGE (t2.b) ( 2018PARTITION p1 VALUES LESS THAN (10), 2019PARTITION p2 VALUES LESS THAN (20) 2020) select * from t2; 2021ERROR 42S22: Unknown column 't2.b' in 'partition function' 2022create table t1 (a int) 2023PARTITION BY RANGE (b) ( 2024PARTITION p1 VALUES LESS THAN (10), 2025PARTITION p2 VALUES LESS THAN (20) 2026) select * from t2; 2027show create table t1; 2028Table Create Table 2029t1 CREATE TABLE `t1` ( 2030 `a` int(11) DEFAULT NULL, 2031 `b` int(11) DEFAULT NULL 2032) ENGINE=MyISAM DEFAULT CHARSET=latin1 2033 PARTITION BY RANGE (`b`) 2034(PARTITION `p1` VALUES LESS THAN (10) ENGINE = MyISAM, 2035 PARTITION `p2` VALUES LESS THAN (20) ENGINE = MyISAM) 2036drop table t1, t2; 2037create table t1 2038(s1 timestamp on update current_timestamp, s2 int) 2039partition by key(s1) partitions 3; 2040insert into t1 values (null,null); 2041drop table t1; 2042create table t1 ( 2043c0 int, 2044c1 bigint, 2045c2 set('sweet'), 2046key (c2,c1,c0), 2047key(c0) 2048) engine=myisam partition by hash (c0) partitions 5; 2049insert ignore into t1 set c0 = -6502262, c1 = 3992917, c2 = 35019; 2050insert ignore into t1 set c0 = 241221, c1 = -6862346, c2 = 56644; 2051select c1 from t1 group by (select c0 from t1 limit 1); 2052c1 2053-6862346 2054drop table t1; 2055CREATE TABLE t1(a int) 2056PARTITION BY RANGE (a) ( 2057PARTITION p1 VALUES LESS THAN (10), 2058PARTITION p2 VALUES LESS THAN (20) 2059); 2060ALTER TABLE t1 OPTIMIZE PARTITION p1 EXTENDED; 2061ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXTENDED' at line 1 2062ALTER TABLE t1 ANALYZE PARTITION p1 EXTENDED; 2063ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXTENDED' at line 1 2064ALTER TABLE t1 ANALYZE PARTITION p1; 2065Table Op Msg_type Msg_text 2066test.t1 analyze status Engine-independent statistics collected 2067test.t1 analyze status OK 2068ALTER TABLE t1 CHECK PARTITION p1; 2069Table Op Msg_type Msg_text 2070test.t1 check status OK 2071ALTER TABLE t1 REPAIR PARTITION p1; 2072Table Op Msg_type Msg_text 2073test.t1 repair status OK 2074ALTER TABLE t1 OPTIMIZE PARTITION p1; 2075Table Op Msg_type Msg_text 2076test.t1 optimize status OK 2077DROP TABLE t1; 2078CREATE TABLE t1 (s1 BIGINT UNSIGNED) 2079PARTITION BY RANGE (s1) ( 2080PARTITION p0 VALUES LESS THAN (0), 2081PARTITION p1 VALUES LESS THAN (1), 2082PARTITION p2 VALUES LESS THAN (18446744073709551615) 2083); 2084INSERT INTO t1 VALUES (0), (18446744073709551614); 2085INSERT INTO t1 VALUES (18446744073709551615); 2086ERROR HY000: Table has no partition for value 18446744073709551615 2087DROP TABLE t1; 2088CREATE TABLE t1 (s1 BIGINT UNSIGNED) 2089PARTITION BY RANGE (s1) ( 2090PARTITION p0 VALUES LESS THAN (0), 2091PARTITION p1 VALUES LESS THAN (1), 2092PARTITION p2 VALUES LESS THAN (18446744073709551614), 2093PARTITION p3 VALUES LESS THAN MAXVALUE 2094); 2095INSERT INTO t1 VALUES (-1), (0), (18446744073709551613), 2096(18446744073709551614), (18446744073709551615); 2097Warnings: 2098Warning 1264 Out of range value for column 's1' at row 1 2099SELECT * FROM t1; 2100s1 21010 21020 210318446744073709551613 210418446744073709551614 210518446744073709551615 2106SELECT * FROM t1 WHERE s1 = 0; 2107s1 21080 21090 2110SELECT * FROM t1 WHERE s1 = 18446744073709551614; 2111s1 211218446744073709551614 2113SELECT * FROM t1 WHERE s1 = 18446744073709551615; 2114s1 211518446744073709551615 2116DROP TABLE t1; 2117CREATE TABLE t1 (s1 BIGINT UNSIGNED) 2118PARTITION BY RANGE (s1) ( 2119PARTITION p0 VALUES LESS THAN (0), 2120PARTITION p1 VALUES LESS THAN (1), 2121PARTITION p2 VALUES LESS THAN (18446744073709551615), 2122PARTITION p3 VALUES LESS THAN MAXVALUE 2123); 2124DROP TABLE t1; 2125CREATE TABLE t1 2126(int_column INT, char_column CHAR(5), 2127PRIMARY KEY(char_column,int_column)) 2128PARTITION BY KEY(char_column,int_column) 2129PARTITIONS 101; 2130INSERT INTO t1 (int_column, char_column) VALUES 2131( 39868 ,'zZZRW'), 2132( 545592 ,'zZzSD'), 2133( 4936 ,'zzzsT'), 2134( 9274 ,'ZzZSX'), 2135( 970185 ,'ZZzTN'), 2136( 786036 ,'zZzTO'), 2137( 37240 ,'zZzTv'), 2138( 313801 ,'zzzUM'), 2139( 782427 ,'ZZZva'), 2140( 907955 ,'zZZvP'), 2141( 453491 ,'zzZWV'), 2142( 756594 ,'ZZZXU'), 2143( 718061 ,'ZZzZH'); 2144SELECT * FROM t1 ORDER BY char_column DESC; 2145int_column char_column 2146718061 ZZzZH 2147756594 ZZZXU 2148453491 zzZWV 2149907955 zZZvP 2150782427 ZZZva 2151313801 zzzUM 215237240 zZzTv 2153786036 zZzTO 2154970185 ZZzTN 21559274 ZzZSX 21564936 zzzsT 2157545592 zZzSD 215839868 zZZRW 2159DROP TABLE t1; 2160CREATE TABLE t1(id MEDIUMINT NOT NULL AUTO_INCREMENT, 2161user CHAR(25), PRIMARY KEY(id)) 2162PARTITION BY RANGE(id) 2163SUBPARTITION BY hash(id) subpartitions 2 2164(PARTITION pa1 values less than (10), 2165PARTITION pa2 values less than (20), 2166PARTITION pa11 values less than MAXVALUE); 2167show create table t1; 2168Table Create Table 2169t1 CREATE TABLE `t1` ( 2170 `id` mediumint(9) NOT NULL AUTO_INCREMENT, 2171 `user` char(25) DEFAULT NULL, 2172 PRIMARY KEY (`id`) 2173) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 2174 PARTITION BY RANGE (`id`) 2175SUBPARTITION BY HASH (`id`) 2176SUBPARTITIONS 2 2177(PARTITION `pa1` VALUES LESS THAN (10) ENGINE = MyISAM, 2178 PARTITION `pa2` VALUES LESS THAN (20) ENGINE = MyISAM, 2179 PARTITION `pa11` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) 2180drop table t1; 2181CREATE TABLE t1 ( 2182`ID` bigint(20) NOT NULL AUTO_INCREMENT, 2183`createdDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 2184`number` int, 2185PRIMARY KEY (`ID`, number) 2186) 2187PARTITION BY RANGE (number) ( 2188PARTITION p0 VALUES LESS THAN (6), 2189PARTITION p1 VALUES LESS THAN (11) 2190); 2191create table t2 ( 2192`ID` bigint(20), 2193`createdDate` TIMESTAMP, 2194`number` int 2195); 2196INSERT INTO t1 SET number=1; 2197insert into t2 select * from t1; 2198SELECT SLEEP(1); 2199SLEEP(1) 22000 2201UPDATE t1 SET number=6; 2202select count(*) from t1, t2 where t1.createdDate = t2.createdDate; 2203count(*) 22041 2205drop table t1, t2; 2206SET @orig_sql_mode = @@SQL_MODE; 2207SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO'; 2208CREATE TABLE t1 (c1 INT) 2209PARTITION BY LIST(1 DIV c1) ( 2210PARTITION p0 VALUES IN (NULL), 2211PARTITION p1 VALUES IN (1) 2212); 2213INSERT INTO t1 VALUES (0); 2214ERROR 22012: Division by 0 2215SELECT * FROM t1; 2216c1 2217TRUNCATE t1; 2218INSERT INTO t1 VALUES (NULL), (0), (1), (2); 2219ERROR 22012: Division by 0 2220SELECT * FROM t1; 2221c1 2222NULL 2223DROP TABLE t1; 2224SET SQL_MODE= @orig_sql_mode; 2225create table t1 (s1 int) partition by hash(s1) partitions 2; 2226create index i on t1 (s1); 2227insert into t1 values (1); 2228insert into t1 select s1 from t1; 2229insert into t1 select s1 from t1; 2230insert into t1 select s1 from t1 order by s1 desc; 2231select * from t1; 2232s1 22331 22341 22351 22361 22371 22381 22391 22401 2241drop table t1; 2242create table t1 (s1 int) partition by range(s1) 2243(partition pa1 values less than (10), 2244partition pa2 values less than MAXVALUE); 2245create index i on t1 (s1); 2246insert into t1 values (1); 2247insert into t1 select s1 from t1; 2248insert into t1 select s1 from t1; 2249insert into t1 select s1 from t1 order by s1 desc; 2250select * from t1; 2251s1 22521 22531 22541 22551 22561 22571 22581 22591 2260drop table t1; 2261create table t1 (s1 int) partition by range(s1) 2262(partition pa1 values less than (10), 2263partition pa2 values less than MAXVALUE); 2264create index i on t1 (s1); 2265insert into t1 values (20); 2266insert into t1 select s1 from t1; 2267insert into t1 select s1 from t1; 2268insert into t1 select s1 from t1 order by s1 desc; 2269select * from t1; 2270s1 227120 227220 227320 227420 227520 227620 227720 227820 2279drop table t1; 2280create table t1 (s1 int) partition by range(s1) 2281(partition pa1 values less than (10), 2282partition pa2 values less than MAXVALUE); 2283create index i on t1 (s1); 2284insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8); 2285insert into t1 select s1 from t1; 2286insert into t1 select s1 from t1; 2287insert into t1 select s1 from t1; 2288insert into t1 select s1 from t1; 2289insert into t1 select s1 from t1 order by s1 desc; 2290insert into t1 select s1 from t1 where s1=3; 2291select count(*) from t1; 2292count(*) 2293288 2294drop table t1; 2295# 2296# Bug#42944: partition not pruned correctly 2297# 2298CREATE TABLE t1 (a int) PARTITION BY RANGE (a) 2299(PARTITION p0 VALUES LESS THAN (100), 2300PARTITION p1 VALUES LESS THAN (200), 2301PARTITION p2 VALUES LESS THAN (300), 2302PARTITION p3 VALUES LESS THAN MAXVALUE); 2303INSERT INTO t1 VALUES (10), (100), (200), (300), (400); 2304EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a>=200; 2305id select_type table partitions type possible_keys key key_len ref rows Extra 23061 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 3 Using where 2307DROP TABLE t1; 2308CREATE TABLE t1 ( a INT, b INT, c INT, KEY bc(b, c) ) 2309PARTITION BY KEY (a, b) PARTITIONS 3 2310; 2311INSERT INTO t1 VALUES 2312(17, 1, -8), 2313(3, 1, -7), 2314(23, 1, -6), 2315(22, 1, -5), 2316(11, 1, -4), 2317(21, 1, -3), 2318(19, 1, -2), 2319(30, 1, -1), 2320(20, 1, 1), 2321(16, 1, 2), 2322(18, 1, 3), 2323(9, 1, 4), 2324(15, 1, 5), 2325(28, 1, 6), 2326(29, 1, 7), 2327(25, 1, 8), 2328(10, 1, 9), 2329(13, 1, 10), 2330(27, 1, 11), 2331(24, 1, 12), 2332(12, 1, 13), 2333(26, 1, 14), 2334(14, 1, 15) 2335; 2336SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c; 2337b c 23381 -8 23391 -7 23401 -6 23411 -5 23421 -4 23431 -3 23441 -2 23451 -1 23461 1 23471 2 23481 3 23491 4 23501 5 23511 6 23521 7 23531 8 23541 9 23551 10 23561 11 23571 12 23581 13 23591 14 23601 15 2361EXPLAIN 2362SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c; 2363id select_type table type possible_keys key key_len ref rows Extra 23641 SIMPLE t1 range bc bc 10 NULL 8 Using where; Using index for group-by 2365EXPLAIN 2366SELECT b, c FROM t1 WHERE b = 1 or b=2 GROUP BY b, c; 2367id select_type table type possible_keys key key_len ref rows Extra 23681 SIMPLE t1 range bc bc 10 NULL 8 Using where; Using index for group-by 2369DROP TABLE t1; 2370# 2371# Bug #45807: crash accessing partitioned table and sql_mode 2372# contains ONLY_FULL_GROUP_BY 2373# Bug#46923: select count(*) from partitioned table fails with 2374# ONLY_FULL_GROUP_BY 2375# 2376SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'; 2377CREATE TABLE t1(id INT,KEY(id)) ENGINE=MYISAM 2378PARTITION BY HASH(id) PARTITIONS 2; 2379SELECT COUNT(*) FROM t1; 2380COUNT(*) 23810 2382DROP TABLE t1; 2383SET SESSION SQL_MODE=DEFAULT; 2384# 2385# Bug#46198: Hang after failed ALTER TABLE on partitioned table. 2386# 2387DROP TABLE IF EXISTS t1; 2388CREATE TABLE t1 (s1 INT PRIMARY KEY) PARTITION BY HASH(s1); 2389LOCK TABLES t1 WRITE, t1 b READ; 2390UNLOCK TABLES; 2391ALTER TABLE t1 DROP PARTITION p1; 2392ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions 2393SELECT * FROM t1; 2394s1 2395DROP TABLE t1; 2396CREATE TABLE t1 (s1 VARCHAR(5) PRIMARY KEY) PARTITION BY KEY(s1); 2397LOCK TABLES t1 WRITE, t1 b READ; 2398UNLOCK TABLES; 2399ALTER TABLE t1 ADD COLUMN (s3 VARCHAR(5) UNIQUE); 2400ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function 2401SELECT * FROM t1; 2402s1 2403DROP TABLE t1; 2404# 2405# BUG#51868 - crash with myisam_use_mmap and partitioned myisam tables 2406# 2407SET GLOBAL myisam_use_mmap=1; 2408CREATE TABLE t1(a INT) PARTITION BY HASH(a) PARTITIONS 1; 2409INSERT INTO t1 VALUES(0); 2410FLUSH TABLE t1; 2411TRUNCATE TABLE t1; 2412INSERT INTO t1 VALUES(0); 2413DROP TABLE t1; 2414SET GLOBAL myisam_use_mmap=default; 2415# 2416# Bug#13580775 ASSERTION FAILED: RECORD_LENGTH == M_RECORD_LENGTH, 2417# FILE FILESORT_UTILS.CC 2418# 2419CREATE TABLE t1 ( 2420a INT PRIMARY KEY, 2421b INT, 2422c CHAR(1), 2423d INT, 2424KEY (c,d) 2425) PARTITION BY KEY () PARTITIONS 1; 2426INSERT INTO t1 VALUES (1,1,'a',1), (2,2,'a',1); 2427SELECT 1 FROM t1 WHERE 1 IN 2428(SELECT group_concat(b) 2429FROM t1 2430WHERE c > geomfromtext('point(1 1)') 2431GROUP BY b 2432); 24331 24341 24351 2436DROP TABLE t1; 2437# 2438# Bug#13011410 CRASH IN FILESORT CODE WITH GROUP BY/ROLLUP 2439# 2440CREATE TABLE t1 ( 2441a INT, 2442b MEDIUMINT, 2443c VARCHAR(300) CHARACTER SET hp8 COLLATE hp8_bin, 2444PRIMARY KEY (a,c(299))) 2445ENGINE=myisam 2446PARTITION BY LINEAR KEY () PARTITIONS 2; 2447INSERT INTO t1 VALUES (1,2,'test'), (2,3,'hi'), (4,5,'bye'); 2448SELECT 1 FROM t1 WHERE b < SOME 2449( SELECT 1 FROM t1 WHERE a >= 1 2450GROUP BY b WITH ROLLUP 2451HAVING b > geomfromtext("") 2452); 2453ERROR HY000: Illegal parameter data types mediumint and geometry for operation '>' 2454DROP TABLE t1; 2455 2456MDEV-612 Valgrind error in ha_maria::check_if_incompatible_data 2457 2458CREATE TABLE t1 (a INT, b INT, KEY(a)) ENGINE=Aria PARTITION BY KEY(a) PARTITIONS 2; 2459ALTER TABLE t1 ADD KEY (b); 2460drop table t1; 2461End of 5.1 tests 2462# 2463# BUG#55385: UPDATE statement throws an error, but still updates 2464# the table entries 2465CREATE TABLE t1_part ( 2466partkey int, 2467nokey int 2468) PARTITION BY LINEAR HASH(partkey) PARTITIONS 3; 2469INSERT INTO t1_part VALUES (1, 1) , (10, 10); 2470CREATE VIEW v1 AS SELECT * FROM t1_part; 2471 2472# Should be (1,1),(10,10) 2473SELECT * FROM t1_part; 2474partkey nokey 24751 1 247610 10 2477 2478# Case 1 2479# Update is refused because partitioning key is updated 2480UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.partkey = 2, B.nokey = 3; 2481ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' 2482UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2, B.partkey = 3; 2483ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' 2484 2485# Case 2 2486# Like 1, but partition accessed through a view 2487UPDATE t1_part AS A NATURAL JOIN v1 as B SET A.nokey = 2 , B.partkey = 3; 2488ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' 2489UPDATE v1 AS A NATURAL JOIN t1_part as B SET A.nokey = 2 , B.partkey = 3; 2490ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' 2491 2492# Should be (1,1),(10,10) 2493SELECT * FROM t1_part; 2494partkey nokey 24951 1 249610 10 2497 2498# Case 3 2499# Update is accepted because partitioning key is not updated 2500UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2 , B.nokey = 3; 2501 2502# Should be (1,3),(10,3) 2503SELECT * FROM t1_part; 2504partkey nokey 25051 3 250610 3 2507 2508DROP VIEW v1; 2509DROP TABLE t1_part; 2510# 2511# BUG#598247: partition.test produces valgrind errors in 5.3-based branches 2512# 2513CREATE TABLE t1 ( 2514a INT DEFAULT NULL, 2515b DOUBLE DEFAULT NULL, 2516c INT DEFAULT NULL, 2517KEY idx2(b,a) 2518) engine=myisam PARTITION BY HASH(c) PARTITIONS 3; 2519INSERT INTO t1 VALUES (6,8,9); 2520INSERT INTO t1 VALUES (6,8,10); 2521SELECT 1 FROM t1 JOIN t1 AS t2 USING (a); 25221 25231 25241 25251 25261 2527drop table t1; 2528# 2529# LP BUG#1001117 Crash on a simple select that uses a temptable view 2530# MySQL Bug #12330344 Crash and/or valgrind errors in free_io_cache with join, view, 2531# partitioned table 2532# 2533CREATE TABLE t1(a INT PRIMARY KEY) PARTITION BY LINEAR KEY (a); 2534CREATE ALGORITHM=TEMPTABLE VIEW vtmp AS 2535SELECT 1 FROM t1 AS t1_0 JOIN t1 ON t1_0.a LIKE (SELECT 1 FROM t1); 2536SELECT * FROM vtmp; 25371 2538DROP VIEW vtmp; 2539DROP TABLE t1; 2540# 2541# MDEV-365 "Got assertion when doing alter table on a partition" 2542# 2543CREATE TABLE t1 ( i INT ) ENGINE=Aria PARTITION BY HASH(i) PARTITIONS 2; 2544INSERT INTO t1 VALUES (1),(2),(2),(3),(4); 2545ALTER TABLE t1 ADD PARTITION PARTITIONS 2; 2546SELECT * from t1 order by i; 2547i 25481 25492 25502 25513 25524 2553DROP TABLE t1; 2554# 2555# MDEV-5555: Incorrect index_merge on BTREE indices 2556# 2557CREATE TABLE t1 ( 2558id bigint(20) unsigned NOT NULL, 2559id2 bigint(20) unsigned NOT NULL, 2560dob date DEFAULT NULL, 2561address char(100) DEFAULT NULL, 2562city char(35) DEFAULT NULL, 2563hours_worked_per_week smallint(5) unsigned DEFAULT NULL, 2564weeks_worked_last_year tinyint(3) unsigned DEFAULT NULL, 2565KEY dob (dob), 2566KEY address (address), 2567KEY city (city), 2568KEY hours_worked_per_week (hours_worked_per_week), 2569KEY weeks_worked_last_year (weeks_worked_last_year) 2570) ENGINE=MyISAM DEFAULT CHARSET=latin1 2571PARTITION BY KEY (id) PARTITIONS 5; 2572# Insert some rows 2573select * from t1 where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21'; 2574id id2 dob address city hours_worked_per_week weeks_worked_last_year 257516 16 1949-11-07 address16 city16 40 52 257650 50 1923-09-08 address50 city50 40 52 2577select * 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'; 2578id id2 dob address city hours_worked_per_week weeks_worked_last_year 257916 16 1949-11-07 address16 city16 40 52 258050 50 1923-09-08 address50 city50 40 52 2581drop table t1; 2582# 2583# MDEV-6322: The PARTITION engine can return wrong query results 2584# 2585CREATE TABLE t1 ( 2586CustomerID varchar(5) DEFAULT NULL, 2587CompanyName varchar(40) DEFAULT NULL, 2588ContactName varchar(30) DEFAULT NULL, 2589ContactTitle varchar(30) DEFAULT NULL, 2590Address varchar(60) DEFAULT NULL, 2591City varchar(15) DEFAULT NULL, 2592Region varchar(15) DEFAULT NULL, 2593PostalCode varchar(10) DEFAULT NULL, 2594Country varchar(15) NOT NULL, 2595Phone varchar(24) DEFAULT NULL, 2596Fax varchar(24) DEFAULT NULL 2597) ENGINE=MyISAM DEFAULT CHARSET=latin1 2598PARTITION BY LIST COLUMNS(Country) 2599(PARTITION p1 VALUES IN ('Germany','Austria','Switzerland','Poland'), 2600PARTITION p2 VALUES IN ('USA','Canada','Mexico'), 2601PARTITION p3 VALUES IN ('Spain','Portugal','Italy'), 2602PARTITION p4 VALUES IN ('UK','Ireland'), 2603PARTITION p5 VALUES IN ('France','Belgium'), 2604PARTITION p6 VALUES IN ('Sweden','Finland','Denmark','Norway'), 2605PARTITION p7 VALUES IN ('Venezuela','Argentina','Brazil') 2606); 2607INSERT INTO t1 (CustomerID, City, Country) VALUES 2608('ANATR','México D.F','Mexico'), 2609('ANTON','México D.F','Mexico'), 2610('BOTTM','Tsawassen','Canada'), 2611('CENTC','México D.F','Mexico'), 2612('GREAL','Eugene','USA'), 2613('HUNGC','Elgin','USA'), 2614('LAUGB','Vancouver','Canada'), 2615('LAZYK','Walla Walla','USA'), 2616('LETSS','San Francisco','USA'), 2617('LONEP','Portland','USA'); 2618SELECT * FROM t1 WHERE Country = 'USA'; 2619CustomerID CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone Fax 2620GREAL NULL NULL NULL NULL Eugene NULL NULL USA NULL NULL 2621HUNGC NULL NULL NULL NULL Elgin NULL NULL USA NULL NULL 2622LAZYK NULL NULL NULL NULL Walla Walla NULL NULL USA NULL NULL 2623LETSS NULL NULL NULL NULL San Francisco NULL NULL USA NULL NULL 2624LONEP NULL NULL NULL NULL Portland NULL NULL USA NULL NULL 2625DROP TABLE t1; 2626CREATE TABLE t1 ( d DATE NOT NULL) 2627PARTITION BY RANGE( YEAR(d) ) ( 2628PARTITION p0 VALUES LESS THAN (1960), 2629PARTITION p1 VALUES LESS THAN (1970), 2630PARTITION p2 VALUES LESS THAN (1980), 2631PARTITION p3 VALUES LESS THAN (1990) 2632); 2633ALTER TABLE t1 ADD PARTITION IF NOT EXISTS( 2634PARTITION `p5` VALUES LESS THAN (2010) 2635COMMENT 'APSTART \' APEND' 2636); 2637ALTER TABLE t1 ADD PARTITION IF NOT EXISTS( 2638PARTITION `p5` VALUES LESS THAN (2010) 2639COMMENT 'APSTART \' APEND' 2640); 2641Warnings: 2642Note 1517 Duplicate partition name p5 2643alter table t1 drop partition if exists p5; 2644alter table t1 drop partition if exists p5; 2645Warnings: 2646Note 1507 Error in list of partitions to DROP 2647DROP TABLE t1; 2648CREATE TABLE t1 (a INT) ENGINE=MyISAM PARTITION BY RANGE(a) (PARTITION p1 VALUES LESS THAN (0)); 2649ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES LESS THAN (1)); 2650PREPARE stmt FROM 'ALTER TABLE t1 ADD PARTITION IF NOT EXISTS (PARTITION p2 VALUES LESS THAN (2))'; 2651EXECUTE stmt; 2652Warnings: 2653Note 1517 Duplicate partition name p2 2654EXECUTE stmt; 2655Warnings: 2656Note 1517 Duplicate partition name p2 2657DEALLOCATE PREPARE stmt; 2658DROP TABLE t1; 2659# 2660# MDEV-17032: Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY 2661# 2662create table t0(a int); 2663insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2664create table t1(a int); 2665insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; 2666create table t2 ( 2667part_key int, 2668a int, 2669b int 2670) partition by list(part_key) ( 2671partition p0 values in (0), 2672partition p1 values in (1), 2673partition p2 values in (2), 2674partition p3 values in (3), 2675partition p4 values in (4) 2676); 2677insert into t2 2678select mod(a,5), a/100, mod(a,5) from t1; 2679set @save_use_stat_tables= @@use_stat_tables; 2680set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; 2681# 2682# Tests using stats provided by the storage engine 2683# 2684explain extended select * from t2 where part_key=1; 2685id select_type table type possible_keys key key_len ref rows filtered Extra 26861 SIMPLE t2 ALL NULL NULL NULL NULL 200 100.00 Using where 2687Warnings: 2688Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`part_key` = 1 2689explain partitions select * from t2 where part_key=1; 2690id select_type table partitions type possible_keys key key_len ref rows Extra 26911 SIMPLE t2 p1 ALL NULL NULL NULL NULL 200 Using where 2692explain extended select * from t2 where part_key in (1,2); 2693id select_type table type possible_keys key key_len ref rows filtered Extra 26941 SIMPLE t2 ALL NULL NULL NULL NULL 400 100.00 Using where 2695Warnings: 2696Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`part_key` in (1,2) 2697explain partitions select * from t2 where part_key in (1,2); 2698id select_type table partitions type possible_keys key key_len ref rows Extra 26991 SIMPLE t2 p1,p2 ALL NULL NULL NULL NULL 400 Using where 2700explain extended select * from t2 where b=5; 2701id select_type table type possible_keys key key_len ref rows filtered Extra 27021 SIMPLE t2 ALL NULL NULL NULL NULL 1000 100.00 Using where 2703Warnings: 2704Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`b` = 5 2705explain partitions select * from t2 where b=5; 2706id select_type table partitions type possible_keys key key_len ref rows Extra 27071 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1000 Using where 2708explain extended select * from t2 partition(p0) where b=1; 2709id select_type table type possible_keys key key_len ref rows filtered Extra 27101 SIMPLE t2 ALL NULL NULL NULL NULL 200 100.00 Using where 2711Warnings: 2712Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` PARTITION (`p0`) where `test`.`t2`.`b` = 1 2713set @save_histogram_size=@@histogram_size; 2714set @@histogram_size=100; 2715set @@use_stat_tables= PREFERABLY; 2716set @@optimizer_use_condition_selectivity=4; 2717analyze table t2; 2718Table Op Msg_type Msg_text 2719test.t2 analyze status Engine-independent statistics collected 2720test.t2 analyze status OK 2721# 2722# Tests using EITS 2723# 2724# filtered should be 100 2725explain extended select * from t2 where part_key=1; 2726id select_type table type possible_keys key key_len ref rows filtered Extra 27271 SIMPLE t2 ALL NULL NULL NULL NULL 200 100.00 Using where 2728Warnings: 2729Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`part_key` = 1 2730explain partitions select * from t2 where part_key=1; 2731id select_type table partitions type possible_keys key key_len ref rows Extra 27321 SIMPLE t2 p1 ALL NULL NULL NULL NULL 200 Using where 2733# filtered should be 100 2734explain extended select * from t2 where part_key in (1,2); 2735id select_type table type possible_keys key key_len ref rows filtered Extra 27361 SIMPLE t2 ALL NULL NULL NULL NULL 400 100.00 Using where 2737Warnings: 2738Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`part_key` in (1,2) 2739explain partitions select * from t2 where part_key in (1,2); 2740id select_type table partitions type possible_keys key key_len ref rows Extra 27411 SIMPLE t2 p1,p2 ALL NULL NULL NULL NULL 400 Using where 2742explain extended select * from t2 where b=5; 2743id select_type table type possible_keys key key_len ref rows filtered Extra 27441 SIMPLE t2 ALL NULL NULL NULL NULL 1000 19.61 Using where 2745Warnings: 2746Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`b` = 5 2747explain partitions select * from t2 where b=5; 2748id select_type table partitions type possible_keys key key_len ref rows Extra 27491 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1000 Using where 2750explain extended select * from t2 partition(p0) where b=1; 2751id select_type table type possible_keys key key_len ref rows filtered Extra 27521 SIMPLE t2 ALL NULL NULL NULL NULL 200 19.61 Using where 2753Warnings: 2754Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` PARTITION (`p0`) where `test`.`t2`.`b` = 1 2755set @@use_stat_tables= @save_use_stat_tables; 2756set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; 2757set @@histogram_size= @save_histogram_size; 2758drop table t0,t1,t2; 2759# 2760# End of 10.0 tests 2761# 2762# 2763# MDEV-8283 crash in get_mm_leaf with xor on binary col 2764# 2765CREATE TABLE t1(a BINARY(80)) PARTITION BY KEY(a) PARTITIONS 3; 2766SELECT 1 FROM t1 WHERE a XOR 'a'; 27671 2768DROP TABLE t1; 2769# 2770# Bug #25207522: INCORRECT ORDER-BY BEHAVIOR ON A PARTITIONED TABLE 2771# WITH A COMPOSITE PREFIX INDEX 2772# 2773create table t1(id int unsigned not null, 2774data varchar(2) default null, 2775key data_idx (data(1),id) 2776) default charset=utf8 2777partition by range (id) ( 2778partition p10 values less than (10), 2779partition p20 values less than (20) 2780); 2781insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ; 2782select id from t1 where data = 'ab' order by id; 2783id 27844 27855 27866 278714 278815 278916 2790drop table t1; 2791create table t1(id int unsigned not null, 2792data text default null, 2793key data_idx (data(1),id) 2794) default charset=utf8 2795partition by range (id) ( 2796partition p10 values less than (10), 2797partition p20 values less than (20) 2798); 2799insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ; 2800select id from t1 where data = 'ab' order by id; 2801id 28024 28035 28046 280514 280615 280716 2808drop table t1; 2809# 2810# MDEV-5628: Assertion `! is_set()' or `!is_set() || 2811# (m_status == DA_OK_BULK && is_bulk_op())' fails on UPDATE on a 2812# partitioned table with subquery (MySQL:71630) 2813# 2814CREATE TABLE t1 (a INT) PARTITION BY HASH(a) PARTITIONS 2; 2815CREATE TABLE t2 (b INT); 2816INSERT INTO t2 VALUES (1),(2); 2817UPDATE t1 SET a = 7 WHERE a = ( SELECT b FROM t2 ) ORDER BY a LIMIT 6; 2818ERROR 21000: Subquery returns more than 1 row 2819DROP TABLE t1,t2; 2820# 2821# End of 10.1 tests 2822# 2823