drop table if exists t1, t2; # # Bug#11765667: bug#58655: ASSERTION FAILED, # SERVER CRASHES WITH MYSQLD GOT SIGNAL 6 # CREATE TABLE t1 ( id MEDIUMINT NOT NULL AUTO_INCREMENT, dt DATE, st VARCHAR(255), uid INT, id2nd LONGBLOB, filler VARCHAR(255), PRIMARY KEY(id, dt) ); INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES ('1991-03-14', 'Initial Insert', 200, 1234567, 'No Data'), ('1991-02-26', 'Initial Insert', 201, 1234567, 'No Data'), ('1992-03-16', 'Initial Insert', 234, 1234567, 'No Data'), ('1992-07-02', 'Initial Insert', 287, 1234567, 'No Data'), ('1991-05-26', 'Initial Insert', 256, 1234567, 'No Data'), ('1991-04-25', 'Initial Insert', 222, 1234567, 'No Data'), ('1993-03-12', 'Initial Insert', 267, 1234567, 'No Data'), ('1993-03-14', 'Initial Insert', 291, 1234567, 'No Data'), ('1991-12-20', 'Initial Insert', 298, 1234567, 'No Data'), ('1994-10-31', 'Initial Insert', 220, 1234567, 'No Data'); ALTER TABLE t1 PARTITION BY LIST (YEAR(dt)) ( PARTITION d1 VALUES IN (1991, 1994), PARTITION d2 VALUES IN (1993), PARTITION d3 VALUES IN (1992, 1995, 1996) ); INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES ('1991-07-14', 'After Partitioning Insert', 299, 1234567, 'Insert row'); UPDATE t1 SET filler='Updating the row' WHERE uid=298; DROP TABLE t1; # # Bug#59297: Can't find record in 'tablename' on update inner join # CREATE TABLE t1 ( a char(2) NOT NULL, b char(2) NOT NULL, c int(10) unsigned NOT NULL, d varchar(255) DEFAULT NULL, e varchar(1000) DEFAULT NULL, PRIMARY KEY (a, b, c), KEY (a), KEY (a, b) ) /*!50100 PARTITION BY KEY (a) PARTITIONS 20 */; INSERT INTO t1 (a, b, c, d, e) VALUES ('07', '03', 343, '1', '07_03_343'), ('01', '04', 343, '2', '01_04_343'), ('01', '06', 343, '3', '01_06_343'), ('01', '07', 343, '4', '01_07_343'), ('01', '08', 343, '5', '01_08_343'), ('01', '09', 343, '6', '01_09_343'), ('03', '03', 343, '7', '03_03_343'), ('03', '06', 343, '8', '03_06_343'), ('03', '07', 343, '9', '03_07_343'), ('04', '03', 343, '10', '04_03_343'), ('04', '06', 343, '11', '04_06_343'), ('05', '03', 343, '12', '05_03_343'), ('11', '03', 343, '13', '11_03_343'), ('11', '04', 343, '14', '11_04_343') ; UPDATE t1 AS A, (SELECT '03' AS a, '06' AS b, 343 AS c, 'last' AS d) AS B SET A.e = B.d WHERE A.a = '03' AND A.b = '06' AND A.c = 343; DROP TABLE t1; # # Bug#59503: explain extended crash in get_mm_leaf # CREATE TABLE t1 (a VARCHAR(51) CHARACTER SET latin1) ENGINE=MyISAM PARTITION BY KEY (a) PARTITIONS 1; INSERT INTO t1 VALUES ('a'),('b'),('c'); EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a > 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select 1 AS `1` from `test`.`t1` where `test`.`t1`.`a` > 1 DROP TABLE t1; # # Bug#57778: failed primary key add to partitioned innodb table # inconsistent and crashes # CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) PARTITION BY KEY (a) PARTITIONS 2; INSERT INTO t1 VALUES (0,1), (0,2); ALTER TABLE t1 ADD PRIMARY KEY (a); ERROR 23000: Duplicate entry '0' for key 'PRIMARY' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) PARTITIONS 2 SELECT * FROM t1; a b 0 1 0 2 UPDATE t1 SET a = 1, b = 1 WHERE a = 0 AND b = 2; ALTER TABLE t1 ADD PRIMARY KEY (a); SELECT * FROM t1; a b 1 1 0 1 ALTER TABLE t1 DROP PRIMARY KEY; SELECT * FROM t1; a b 1 1 0 1 DROP TABLE t1; # # Bug#57113: ha_partition::extra(ha_extra_function): # Assertion `m_extra_cache' failed CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, name VARCHAR(16) NOT NULL, year YEAR, INDEX name (name(8)) ) PARTITION BY HASH(id) PARTITIONS 2; INSERT INTO t1 VALUES ( 1, 'FooBar', '1924' ); CREATE TABLE t2 (id INT); INSERT INTO t2 VALUES (1),(2); UPDATE t1, t2 SET t1.year = '1955' WHERE t1.name = 'FooBar'; DROP TABLE t1, t2; # # Bug#55458: Partitioned MyISAM table gets crashed by multi-table update # CREATE TABLE t1 ( `id` int NOT NULL, `user_num` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES (1,8601); INSERT INTO t1 VALUES (2,8601); INSERT INTO t1 VALUES (3,8601); INSERT INTO t1 VALUES (4,8601); CREATE TABLE t2 ( `id` int(11) NOT NULL, `user_num` int DEFAULT NULL, `name` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM CHARSET=latin1 PARTITION BY HASH (id) PARTITIONS 2; INSERT INTO t2 VALUES (1,8601,'John'); INSERT INTO t2 VALUES (2,8601,'JS'); INSERT INTO t2 VALUES (3,8601,'John S'); UPDATE t1, t2 SET t2.name = 'John Smith' WHERE t1.user_num = t2.user_num; DROP TABLE t1, t2; # Bug#39338: Fieldnames in # INFORMATIONSCHEMA.PARTITIONS.PARTITION_EXPRESSION become unescaped # NOTE: the partition expression is saved as a string, so changing from # normal quotes to ansi quotes does not change the expression, only # for partition by KEY. CREATE TABLE t1 ( ID int(11) NOT NULL, `aaaa,aaaaa` tinyint(3) UNSIGNED NOT NULL DEFAULT '0', ddddddddd int(11) NOT NULL DEFAULT '0', new_field0 varchar(50), PRIMARY KEY(ID, `aaaa,aaaaa`, ddddddddd)) PARTITION BY RANGE(ID) PARTITIONS 3 SUBPARTITION BY LINEAR KEY(ID,`aaaa,aaaaa`) SUBPARTITIONS 2 ( PARTITION p01 VALUES LESS THAN(100), PARTITION p11 VALUES LESS THAN(200), PARTITION p21 VALUES LESS THAN MAXVALUE); SELECT PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1'; PARTITION_EXPRESSION SUBPARTITION_EXPRESSION `ID` `ID`,`aaaa,aaaaa` `ID` `ID`,`aaaa,aaaaa` `ID` `ID`,`aaaa,aaaaa` `ID` `ID`,`aaaa,aaaaa` `ID` `ID`,`aaaa,aaaaa` `ID` `ID`,`aaaa,aaaaa` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `ID` int(11) NOT NULL, `aaaa,aaaaa` tinyint(3) unsigned NOT NULL DEFAULT 0, `ddddddddd` int(11) NOT NULL DEFAULT 0, `new_field0` varchar(50) DEFAULT NULL, PRIMARY KEY (`ID`,`aaaa,aaaaa`,`ddddddddd`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (`ID`) SUBPARTITION BY LINEAR KEY (`ID`,`aaaa,aaaaa`) SUBPARTITIONS 2 (PARTITION `p01` VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION `p11` VALUES LESS THAN (200) ENGINE = MyISAM, PARTITION `p21` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) drop table t1; CREATE TABLE t1 (a INT, b INT) PARTITION BY LIST (a) SUBPARTITION BY HASH (b) (PARTITION p1 VALUES IN (1)); ALTER TABLE t1 ADD COLUMN c INT; DROP TABLE t1; CREATE TABLE t1 ( a int NOT NULL, b int NOT NULL); CREATE TABLE t2 ( a int NOT NULL, b int NOT NULL, INDEX(b) ) PARTITION BY HASH(a) PARTITIONS 2; INSERT INTO t1 VALUES (399, 22); INSERT INTO t2 VALUES (1, 22), (1, 42); INSERT INTO t2 SELECT 1, 399 FROM t2, t1 WHERE t1.b = t2.b; DROP TABLE t1, t2; CREATE TABLE t1 ( a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, b varchar(10), PRIMARY KEY (a) ) PARTITION BY RANGE (UNIX_TIMESTAMP(a)) ( PARTITION p1 VALUES LESS THAN (1199134800), PARTITION pmax VALUES LESS THAN MAXVALUE ); INSERT INTO t1 VALUES ('2007-07-30 17:35:48', 'p1'); INSERT INTO t1 VALUES ('2009-07-14 17:35:55', 'pmax'); INSERT INTO t1 VALUES ('2009-09-21 17:31:42', 'pmax'); SELECT * FROM t1; a b 2007-07-30 17:35:48 p1 2009-07-14 17:35:55 pmax 2009-09-21 17:31:42 pmax SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; a b 2007-07-30 17:35:48 p1 EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 ALTER TABLE t1 REORGANIZE PARTITION pmax INTO ( PARTITION p3 VALUES LESS THAN (1247688000), PARTITION pmax VALUES LESS THAN MAXVALUE); SELECT * FROM t1; a b 2007-07-30 17:35:48 p1 2009-07-14 17:35:55 pmax 2009-09-21 17:31:42 pmax SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; a b 2007-07-30 17:35:48 p1 EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` timestamp NOT NULL DEFAULT current_timestamp(), `b` varchar(10) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (unix_timestamp(`a`)) (PARTITION `p1` VALUES LESS THAN (1199134800) ENGINE = MyISAM, PARTITION `p3` VALUES LESS THAN (1247688000) ENGINE = MyISAM, PARTITION `pmax` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) DROP TABLE t1; create table t1 (a int NOT NULL, b varchar(5) NOT NULL) default charset=utf8 partition by list (a) subpartition by key (b) (partition p0 values in (1), partition p1 values in (2)); drop table t1; create table t1 (a int, b int, key(a)) partition by list (a) ( partition p0 values in (1), partition p1 values in (2)); insert into t1 values (1,1),(2,1),(2,2),(2,3); show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A NULL NULL NULL YES BTREE NO analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A 2 NULL NULL YES BTREE NO drop table t1; create table t1 (a int) partition by hash (a); create index i on t1 (a); insert into t1 values (1); insert into t1 select * from t1; create index i on t1 (a); ERROR 42000: Duplicate key name 'i' create index i2 on t1 (a); Warnings: Note 1831 Duplicate index `i2`. This is deprecated and will be disallowed in a future release drop table t1; CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a)) ENGINE=MyISAM PARTITION BY HASH (a); ERROR HY000: Partitioned tables do not support FOREIGN KEY CREATE TABLE t1 ( pk INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (pk) ) /*!50100 PARTITION BY HASH (pk) PARTITIONS 2 */; INSERT INTO t1 VALUES (NULL); INSERT INTO t1 VALUES (NULL); INSERT INTO t1 VALUES (NULL); SELECT * FROM t1 WHERE pk < 0 ORDER BY pk; pk DROP TABLE t1; SET sql_mode=no_engine_substitution; CREATE TABLE t1 (a INT) ENGINE=NonExistentEngine; ERROR 42000: Unknown storage engine 'NonExistentEngine' CREATE TABLE t1 (a INT) ENGINE=NonExistentEngine PARTITION BY HASH (a); ERROR 42000: Unknown storage engine 'NonExistentEngine' CREATE TABLE t1 (a INT) ENGINE=Memory; ALTER TABLE t1 ENGINE=NonExistentEngine; ERROR 42000: Unknown storage engine 'NonExistentEngine' ALTER TABLE t1 PARTITION BY HASH (a) (PARTITION p0 ENGINE=Memory, PARTITION p1 ENGINE=NonExistentEngine); ERROR 42000: Unknown storage engine 'NonExistentEngine' ALTER TABLE t1 ENGINE=NonExistentEngine; ERROR 42000: Unknown storage engine 'NonExistentEngine' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; SET sql_mode=''; CREATE TABLE t1 (a INT) ENGINE=NonExistentEngine; Warnings: Warning 1286 Unknown storage engine 'NonExistentEngine' Warning 1266 Using storage engine MyISAM for table 't1' DROP TABLE t1; CREATE TABLE t1 (a INT) ENGINE=NonExistentEngine PARTITION BY HASH (a); Warnings: Warning 1286 Unknown storage engine 'NonExistentEngine' Warning 1266 Using storage engine MyISAM for table 't1' DROP TABLE t1; CREATE TABLE t1 (a INT) ENGINE=Memory; ALTER TABLE t1 ENGINE=NonExistentEngine; Warnings: Warning 1286 Unknown storage engine 'NonExistentEngine' ALTER TABLE t1 PARTITION BY HASH (a) (PARTITION p0 ENGINE=Memory, PARTITION p1 ENGINE=NonExistentEngine); Warnings: Warning 1286 Unknown storage engine 'NonExistentEngine' ALTER TABLE t1 ENGINE=NonExistentEngine; Warnings: Warning 1286 Unknown storage engine 'NonExistentEngine' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY HASH (`a`) (PARTITION `p0` ENGINE = MEMORY, PARTITION `p1` ENGINE = MEMORY) DROP TABLE t1; SET sql_mode=DEFAULT; CREATE TABLE t1 (a INT NOT NULL, KEY(a)) PARTITION BY RANGE(a) (PARTITION p1 VALUES LESS THAN (200), PARTITION pmax VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES (2), (40), (40), (70), (60), (90), (199); SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a ASC; a 60 70 90 SELECT a FROM t1 WHERE a BETWEEN 60 AND 95; a 60 70 90 INSERT INTO t1 VALUES (200), (250), (210); SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a ASC; a 60 70 90 199 200 210 SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a ASC; a 200 210 SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a DESC; a 90 70 60 SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a DESC; a 210 200 199 90 70 60 SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a DESC; a 210 200 SELECT a FROM t1 WHERE a BETWEEN 60 AND 220; a 199 200 210 60 70 90 SELECT a FROM t1 WHERE a BETWEEN 200 AND 220; a 200 210 SELECT a FROM t1 WHERE a BETWEEN 60 AND 95; a 60 70 90 SELECT a FROM t1 WHERE a BETWEEN 60 AND 220; a 199 200 210 60 70 90 SELECT a FROM t1 WHERE a BETWEEN 200 AND 220; a 200 210 DROP TABLE t1; CREATE TABLE t1 ( a INT NOT NULL, b MEDIUMINT NOT NULL, c INT NOT NULL, KEY b (b) ) ENGINE=MyISAM PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (1) ); INSERT INTO t1 VALUES (1,1,0), (1,1,1), (1,1,2), (1,1,53), (1,1,4), (1,1,5), (1,1,6), (1,1,7), (1,1,8), (1,1,9), (1,1,10), (1,1,11), (1,1,12), (1,1,13), (1,1,14), (1,1,15), (1,1,16), (1,1,67), (1,1,18), (1,1,19), (1,1,20), (1,1,21), (1,1,22), (1,1,23), (1,1,24), (1,1,75), (1,1,26), (1,1,27), (1,1,128), (1,1,79), (1,1,30), (1,1,31), (1,1,32), (1,1,33), (1,1,34), (1,1,85), (1,1,36), (1,1,37), (1,1,38), (1,1,39), (1,1,40), (1,1,241), (1,1,42), (1,1,43), (1,1,44), (1,1,45), (1,1,46), (1,1,147), (1,1,48), (1,1,49), (1,2,0), (1,2,1), (1,2,2), (1,2,3), (1,2,4), (1,2,5), (1,2,6), (1,2,7), (1,2,8), (1,2,9), (1,2,10), (1,2,11), (1,2,12), (1,2,13), (1,2,14), (1,2,15), (1,2,16), (1,2,17), (1,2,18), (1,2,19), (1,2,20), (1,2,21), (1,2,22), (1,2,23), (1,2,24), (1,2,25), (1,2,26), (1,2,27), (1,2,28), (1,2,29), (1,2,30), (1,2,31), (1,2,32), (1,2,33), (1,2,34), (1,2,35), (1,2,36), (1,2,37), (1,2,38), (1,2,39), (1,2,40), (1,2,41), (1,2,42), (1,2,43), (1,2,44), (1,2,45), (1,2,46), (1,2,47), (1,2,48), (1,2,49), (1,6,0), (1,6,1), (1,6,2), (1,6,3), (1,6,4), (1,6,5), (1,6,6), (1,6,7), (1,6,8), (1,6,9), (1,6,10), (1,6,11), (1,6,12), (1,6,13), (1,6,14), (1,6,15), (1,6,16), (1,6,17), (1,6,18), (1,6,19), (1,6,20), (1,6,21), (1,6,22), (1,6,23), (1,6,24), (1,6,25), (1,6,26), (1,6,27), (1,6,28), (1,6,29), (1,6,30), (1,6,31), (1,6,32), (1,6,33), (1,6,34), (1,6,35), (1,6,36), (1,6,37), (1,6,38), (1,6,39), (1,6,40), (1,6,41), (1,6,42), (1,6,43), (1,6,44), (1,6,45), (1,6,46), (1,6,47), (1,6,48), (1,6,49), (1,7,0), (1,7,1), (1,7,2), (1,7,3), (1,7,4), (1,7,5), (1,7,6), (1,7,7), (1,7,8), (1,7,9), (1,7,10), (1,7,11), (1,7,12), (1,7,13), (1,7,14), (1,7,15), (1,7,16), (1,7,17), (1,7,18), (1,7,19), (1,7,20), (1,7,21), (1,7,22), (1,7,23), (1,7,24), (1,7,25), (1,7,26), (1,7,27), (1,7,28), (1,7,29), (1,7,30), (1,7,31), (1,7,32), (1,7,33), (1,7,34), (1,7,35), (1,7,38), (1,7,39), (1,7,90), (1,7,41), (1,7,43), (1,7,48), (1,7,49), (1,9,0), (1,9,1), (1,9,2), (1,9,3), (1,9,4), (1,9,5), (1,9,6), (1,9,7), (1,9,8), (1,9,9), (1,9,10), (1,9,11), (1,9,12), (1,9,13), (1,9,14), (1,9,15), (1,9,16), (1,9,17), (1,9,18), (1,9,19), (1,9,20), (1,9,21), (1,9,22), (1,9,23), (1,9,24), (1,9,25), (1,9,26), (1,9,29), (1,9,32), (1,9,35), (1,9,38), (1,10,0), (1,10,1), (1,10,2), (1,10,3), (1,10,4), (1,10,5), (1,10,6), (1,10,7), (1,10,8), (1,10,9), (1,10,10), (1,10,11), (1,10,13), (1,10,14), (1,10,15), (1,10,16), (1,10,17), (1,10,18), (1,10,22), (1,10,24), (1,10,25), (1,10,26), (1,10,28), (1,10,131), (1,10,33), (1,10,84), (1,10,35), (1,10,40), (1,10,42), (1,10,49), (1,11,0), (1,11,1), (1,11,2), (1,11,3), (1,11,4), (1,11,5), (1,11,6), (1,11,7), (1,11,8), (1,11,9), (1,11,10), (1,11,11), (1,11,12), (1,11,13), (1,11,14), (1,11,15), (1,11,16), (1,11,17), (1,11,18), (1,11,19), (1,11,20), (1,11,21), (1,11,22), (1,11,23), (1,11,24), (1,11,25), (1,11,26), (1,11,27), (1,11,28), (1,11,30), (1,11,31), (1,11,32), (1,11,33), (1,11,34), (1,11,35), (1,11,37), (1,11,39), (1,11,40), (1,11,42), (1,11,44), (1,11,45), (1,11,47), (1,11,48), (1,14,104), (1,14,58), (1,14,12), (1,14,13), (1,14,15), (1,14,16), (1,14,17), (1,14,34), (1,15,0), (1,15,1), (1,15,2), (1,15,3), (1,15,4), (1,15,5), (1,15,7), (1,15,9), (1,15,15), (1,15,27), (1,15,49), (1,16,0), (1,16,1), (1,16,3), (1,17,4), (1,19,1); SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); COUNT(*) 24 SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); SUM(c) 400 SELECT SUM(c+0.0) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); SUM(c+0.0) 400.0 ALTER TABLE t1 DROP INDEX b; SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); COUNT(*) 24 SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); SUM(c) 400 ALTER TABLE t1 ENGINE = Memory; SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); COUNT(*) 24 SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); SUM(c) 400 ALTER TABLE t1 ADD INDEX b USING HASH (b); SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); COUNT(*) 24 SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 ); SUM(c) 400 DROP TABLE t1; CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, KEY `c1` (`c1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `t2` ( `c1` int(11) DEFAULT NULL, KEY `c1` (`c1`) ) 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) */; INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 NULL range c1 c1 5 NULL 4 Using where; Using index FLUSH STATUS; SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); c1 11 12 18 19 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 2 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 a range c1 c1 5 NULL 4 Using where; Using index FLUSH STATUS; SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); c1 11 12 18 19 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 2 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 DROP TABLE t1,t2; CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, KEY `c1` (`c1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `t2` ( `c1` int(11) DEFAULT NULL, KEY `c1` (`c1`) ) 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) */; INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 NULL range c1 c1 5 NULL 2 Using where; Using index FLUSH STATUS; SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5); c1 3 4 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 a range c1 c1 5 NULL 2 Using where; Using index FLUSH STATUS; SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5); c1 3 4 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 NULL range c1 c1 5 NULL 2 Using where; Using index FLUSH STATUS; SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15); c1 13 14 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 a range c1 c1 5 NULL 2 Using where; Using index FLUSH STATUS; SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15); c1 13 14 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 DROP TABLE t1,t2; create table t1 (a int) partition by list ((a/3)*10 div 1) (partition p0 values in (0), partition p1 values in (1)); ERROR HY000: This partition function is not allowed CREATE TABLE t1 ( d DATE NOT NULL ) PARTITION BY RANGE( YEAR(d) ) ( PARTITION p0 VALUES LESS THAN (1960), PARTITION p1 VALUES LESS THAN (1970), PARTITION p2 VALUES LESS THAN (1980), PARTITION p3 VALUES LESS THAN (1990) ); ALTER TABLE t1 ADD PARTITION ( PARTITION `p5` VALUES LESS THAN (2010) COMMENT 'APSTART \' APEND' ); SELECT * FROM t1 LIMIT 1; d DROP TABLE t1; create table t1 (id int auto_increment, s1 int, primary key (id)); insert into t1 values (null,1); insert into t1 values (null,6); select * from t1; id s1 1 1 2 6 alter table t1 partition by range (id) ( partition p0 values less than (3), partition p1 values less than maxvalue ); drop table t1; create table t1 (a int) partition by key(a) partitions 0.2+e1; ERROR 42000: Only integers allowed as number here near '0.2+e1' at line 3 create table t1 (a int) partition by key(a) partitions -1; ERROR 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 create table t1 (a int) partition by key(a) partitions 1.5; ERROR 42000: Only integers allowed as number here near '1.5' at line 3 create table t1 (a int) partition by key(a) partitions 1e+300; ERROR 42000: Only integers allowed as number here near '1e+300' at line 3 create table t1 (a int) partition by list (a) (partition p0 values in (1)); create procedure pz() alter table t1 engine = myisam; call pz(); call pz(); drop procedure pz; drop table t1; create table t1 (a bigint) partition by range (a) (partition p0 values less than (0xFFFFFFFFFFFFFFFF), partition p1 values less than (10)); ERROR HY000: VALUES value for partition 'p0' must have type INT create table t1 (a bigint) partition by list (a) (partition p0 values in (0xFFFFFFFFFFFFFFFF), partition p1 values in (10)); ERROR HY000: VALUES value for partition 'p0' must have type INT create table t1 (a bigint unsigned) partition by range (a) (partition p0 values less than (100), partition p1 values less than MAXVALUE); insert into t1 values (1); drop table t1; create table t1 (a bigint unsigned) partition by hash (a); insert into t1 values (0xFFFFFFFFFFFFFFFD); insert into t1 values (0xFFFFFFFFFFFFFFFE); select * from t1 where (a + 1) < 10; a select * from t1 where (a + 1) > 10; a 18446744073709551613 18446744073709551614 drop table t1; create table t1 (a int) partition by key(a) (partition p0 engine = MEMORY); drop table t1; create table t1 (a int) partition by range (a) subpartition by key (a) (partition p0 values less than (1)); alter table t1 add partition (partition p1 values less than (2)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (`a`) SUBPARTITION BY KEY (`a`) (PARTITION `p0` VALUES LESS THAN (1) ENGINE = MyISAM, PARTITION `p1` VALUES LESS THAN (2) ENGINE = MyISAM) alter table t1 reorganize partition p1 into (partition p1 values less than (3)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (`a`) SUBPARTITION BY KEY (`a`) (PARTITION `p0` VALUES LESS THAN (1) ENGINE = MyISAM, PARTITION `p1` VALUES LESS THAN (3) ENGINE = MyISAM) drop table t1; CREATE TABLE t1 ( a int not null, b int not null, c int not null, primary key(a,b)) partition by key (a); select count(*) from t1; count(*) 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY (`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) drop table t1; CREATE TABLE t1 ( a int not null, b int not null, c int not null, primary key(a,b)) partition by key (a, b); drop table t1; CREATE TABLE t1 ( a int not null, b int not null, c int not null, primary key(a,b)) partition by key (a) partitions 3 (partition x1, partition x2, partition x3); drop table t1; CREATE TABLE t1 ( a int not null, b int not null, c int not null, primary key(a,b)) partition by key (a) partitions 3 (partition x1 nodegroup 0, partition x2 nodegroup 1, partition x3 nodegroup 2); drop table t1; CREATE TABLE t1 ( a int not null, b int not null, c int not null, primary key(a,b)) partition by key (a) partitions 3 (partition x1 engine myisam, partition x2 engine myisam, partition x3 engine myisam); drop table t1; CREATE TABLE t1 ( a int not null, b int not null, c int not null, primary key(a,b)) partition by key (a) partitions 3 (partition x1 tablespace ts1, partition x2 tablespace ts2, partition x3 tablespace ts3); CREATE TABLE t2 LIKE t1; drop table t2; drop table t1; CREATE TABLE t1 ( a int not null, b int not null, c int not null, primary key(a,b)) partition by list (a) partitions 3 (partition x1 values in (1,2,9,4) tablespace ts1, partition x2 values in (3, 11, 5, 7) tablespace ts2, partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3); drop table t1; CREATE TABLE t1 ( a int not null, b int not null, c int not null, primary key(a,b)) partition by list (b*a) partitions 3 (partition x1 values in (1,2,9,4) tablespace ts1, partition x2 values in (3, 11, 5, 7) tablespace ts2, partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3); drop table t1; CREATE TABLE t1 ( a int not null, b int not null, c int not null, primary key(a,b)) partition by list (b*a) (partition x1 values in (1) tablespace ts1, partition x2 values in (3, 11, 5, 7) tablespace ts2, partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3); drop table t1; CREATE TABLE t1 ( a int not null) partition by key(a); LOCK TABLES t1 WRITE; insert into t1 values (1); insert into t1 values (2); insert into t1 values (3); insert into t1 values (4); UNLOCK TABLES; drop table t1; CREATE TABLE t1 (a int, name VARCHAR(50), purchased DATE) PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (3), PARTITION p1 VALUES LESS THAN (7), PARTITION p2 VALUES LESS THAN (9), PARTITION p3 VALUES LESS THAN (11)); INSERT INTO t1 VALUES (1, 'desk organiser', '2003-10-15'), (2, 'CD player', '1993-11-05'), (3, 'TV set', '1996-03-10'), (4, 'bookcase', '1982-01-10'), (5, 'exercise bike', '2004-05-09'), (6, 'sofa', '1987-06-05'), (7, 'popcorn maker', '2001-11-22'), (8, 'acquarium', '1992-08-04'), (9, 'study desk', '1984-09-16'), (10, 'lava lamp', '1998-12-25'); SELECT * from t1 ORDER BY a; a name purchased 1 desk organiser 2003-10-15 2 CD player 1993-11-05 3 TV set 1996-03-10 4 bookcase 1982-01-10 5 exercise bike 2004-05-09 6 sofa 1987-06-05 7 popcorn maker 2001-11-22 8 acquarium 1992-08-04 9 study desk 1984-09-16 10 lava lamp 1998-12-25 ALTER TABLE t1 DROP PARTITION p0; SELECT * from t1 ORDER BY a; a name purchased 3 TV set 1996-03-10 4 bookcase 1982-01-10 5 exercise bike 2004-05-09 6 sofa 1987-06-05 7 popcorn maker 2001-11-22 8 acquarium 1992-08-04 9 study desk 1984-09-16 10 lava lamp 1998-12-25 drop table t1; CREATE TABLE t1 (a int) PARTITION BY LIST (a) (PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (4,5,6)); insert into t1 values (1),(2),(3),(4),(5),(6); select * from t1; a 1 2 3 4 5 6 truncate t1; select * from t1; a truncate t1; select * from t1; a drop table t1; CREATE TABLE t1 (a int, b int, primary key(a,b)) PARTITION BY KEY(b,a) PARTITIONS 4; insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6); select * from t1 where a = 4; a b 4 4 drop table t1; CREATE TABLE t1 (c1 INT, c2 INT, PRIMARY KEY USING BTREE (c1,c2)) ENGINE=MEMORY PARTITION BY KEY(c2,c1) PARTITIONS 4; INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6); SELECT * FROM t1 WHERE c1 = 4; c1 c2 4 4 DROP TABLE t1; CREATE TABLE t1 (a int) PARTITION BY LIST (a) PARTITIONS 1 (PARTITION x1 VALUES IN (1) ENGINE=MEMORY); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY LIST (`a`) (PARTITION `x1` VALUES IN (1) ENGINE = MEMORY) drop table t1; CREATE TABLE t1 (a int, unique(a)) PARTITION BY LIST (a) (PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20)); REPLACE t1 SET a = 4; ERROR HY000: Table has no partition for value 4 drop table t1; CREATE TABLE t1 (a int) PARTITION BY LIST (a) (PARTITION x1 VALUES IN (2), PARTITION x2 VALUES IN (3)); insert into t1 values (2), (3); insert into t1 values (4); ERROR HY000: Table has no partition for value 4 insert into t1 values (1); ERROR HY000: Table has no partition for value 1 drop table t1; CREATE TABLE t1 (a int) PARTITION BY HASH(a) PARTITIONS 5; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY HASH (`a`) PARTITIONS 5 drop table t1; CREATE TABLE t1 (a int) PARTITION BY RANGE (a) (PARTITION x1 VALUES LESS THAN (2)); insert into t1 values (1); update t1 set a = 5; ERROR HY000: Table has no partition for value 5 drop table t1; CREATE TABLE t1 (a int) PARTITION BY LIST (a) (PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20)); analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK drop table t1; create table t1 (a int) partition by range (a) ( partition p0 values less than(10), partition p1 values less than (20), partition p2 values less than (25)); alter table t1 reorganize partition p2 into (partition p2 values less than (30)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (`a`) (PARTITION `p0` VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION `p1` VALUES LESS THAN (20) ENGINE = MyISAM, PARTITION `p2` VALUES LESS THAN (30) ENGINE = MyISAM) drop table t1; CREATE TABLE t1 (a int, b int) PARTITION BY RANGE (a) (PARTITION x0 VALUES LESS THAN (2), PARTITION x1 VALUES LESS THAN (4), PARTITION x2 VALUES LESS THAN (6), PARTITION x3 VALUES LESS THAN (8), PARTITION x4 VALUES LESS THAN (10), PARTITION x5 VALUES LESS THAN (12), PARTITION x6 VALUES LESS THAN (14), PARTITION x7 VALUES LESS THAN (16), PARTITION x8 VALUES LESS THAN (18), PARTITION x9 VALUES LESS THAN (20)); ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO (PARTITION x1 VALUES LESS THAN (6)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (`a`) (PARTITION `x1` VALUES LESS THAN (6) ENGINE = MyISAM, PARTITION `x3` VALUES LESS THAN (8) ENGINE = MyISAM, PARTITION `x4` VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION `x5` VALUES LESS THAN (12) ENGINE = MyISAM, PARTITION `x6` VALUES LESS THAN (14) ENGINE = MyISAM, PARTITION `x7` VALUES LESS THAN (16) ENGINE = MyISAM, PARTITION `x8` VALUES LESS THAN (18) ENGINE = MyISAM, PARTITION `x9` VALUES LESS THAN (20) ENGINE = MyISAM) drop table t1; create table t1 (a int not null, b int not null) partition by LIST (a+b) ( partition p0 values in (12), partition p1 values in (14) ); insert into t1 values (10,1); ERROR HY000: Table has no partition for value 11 drop table t1; create table t1 (f1 integer,f2 integer, f3 varchar(10), primary key(f1,f2)) partition by range(f1) subpartition by hash(f2) subpartitions 2 (partition p1 values less than (0), partition p2 values less than (2), partition p3 values less than (2147483647)); insert into t1 values(10,10,'10'); insert into t1 values(2,2,'2'); select * from t1 where f1 = 2; f1 f2 f3 2 2 2 drop table t1; create table t1 (f1 integer,f2 integer, unique index(f1)) partition by range(f1 div 2) subpartition by hash(f1) subpartitions 2 (partition partb values less than (2), partition parte values less than (4), partition partf values less than (10000)); insert into t1 values(10,1); select * from t1 where f1 = 10; f1 f2 10 1 drop table t1; set session default_storage_engine= 'memory'; create table t1 (f_int1 int(11) default null) engine = memory partition by range (f_int1) subpartition by hash (f_int1) (partition part1 values less than (1000) (subpartition subpart11 engine = memory)); drop table t1; set session default_storage_engine='myisam'; create table t1 (f_int1 integer, f_int2 integer, primary key (f_int1)) partition by hash(f_int1) partitions 2; insert into t1 values (1,1),(2,2); replace into t1 values (1,1),(2,2); drop table t1; create table t1 (s1 int, unique (s1)) partition by list (s1) (partition x1 VALUES in (10), partition x2 values in (20)); alter table t1 add partition (partition x3 values in (30)); drop table t1; create table t1 (a int) partition by key(a) partitions 2 (partition p0 engine=myisam, partition p1 engine=myisam); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) (PARTITION `p0` ENGINE = MyISAM, PARTITION `p1` ENGINE = MyISAM) alter table t1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) (PARTITION `p0` ENGINE = MyISAM, PARTITION `p1` ENGINE = MyISAM) alter table t1 engine=myisam; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) (PARTITION `p0` ENGINE = MyISAM, PARTITION `p1` ENGINE = MyISAM) alter table t1 engine=heap; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) (PARTITION `p0` ENGINE = MEMORY, PARTITION `p1` ENGINE = MEMORY) alter table t1 remove partitioning; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; create table t1 (a int) engine=myisam partition by key(a) partitions 2 (partition p0 engine=myisam, partition p1 engine=myisam); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) (PARTITION `p0` ENGINE = MyISAM, PARTITION `p1` ENGINE = MyISAM) alter table t1 add column b int remove partitioning; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci alter table t1 engine=myisam partition by key(a) (partition p0 engine=myisam, partition p1); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) (PARTITION `p0` ENGINE = MyISAM, PARTITION `p1` ENGINE = MyISAM) alter table t1 engine=heap partition by key(a) (partition p0, partition p1 engine=heap); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) (PARTITION `p0` ENGINE = MEMORY, PARTITION `p1` ENGINE = MEMORY) alter table t1 engine=myisam, add column c int remove partitioning; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci alter table t1 engine=heap partition by key (a) (partition p0, partition p1); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) (PARTITION `p0` ENGINE = MEMORY, PARTITION `p1` ENGINE = MEMORY) alter table t1 partition by key (a) (partition p0, partition p1); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) (PARTITION `p0` ENGINE = MEMORY, PARTITION `p1` ENGINE = MEMORY) alter table t1 engine=heap partition by key (a) (partition p0, partition p1); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) (PARTITION `p0` ENGINE = MEMORY, PARTITION `p1` ENGINE = MEMORY) alter table t1 partition by key(a) (partition p0, partition p1 engine=heap); alter table t1 partition by key(a) (partition p0 engine=heap, partition p1); alter table t1 engine=heap partition by key (a) (partition p0 engine=heap, partition p1 engine=myisam); ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MariaDB alter table t1 partition by key (a) (partition p0 engine=heap, partition p1 engine=myisam); ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MariaDB drop table t1; CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000) ) PARTITION BY RANGE(f_int1 DIV 2) SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 (PARTITION parta VALUES LESS THAN (0), PARTITION partb VALUES LESS THAN (5), PARTITION parte VALUES LESS THAN (10), PARTITION partf VALUES LESS THAN (2147483647)); INSERT INTO t1 SET f_int1 = NULL , f_int2 = -20, f_char1 = CAST(-20 AS CHAR), f_char2 = CAST(-20 AS CHAR), f_charbig = '#NULL#'; SELECT * FROM t1 WHERE f_int1 IS NULL; f_int1 f_int2 f_char1 f_char2 f_charbig NULL -20 -20 -20 #NULL# SELECT * FROM t1; f_int1 f_int2 f_char1 f_char2 f_charbig NULL -20 -20 -20 #NULL# drop table t1; CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000) ) PARTITION BY LIST(MOD(f_int1,2)) SUBPARTITION BY KEY(f_int1) (PARTITION part1 VALUES IN (-1) (SUBPARTITION sp1, SUBPARTITION sp2), PARTITION part2 VALUES IN (0) (SUBPARTITION sp3, SUBPARTITION sp5), PARTITION part3 VALUES IN (1) (SUBPARTITION sp4, SUBPARTITION sp6)); INSERT INTO t1 SET f_int1 = 2, f_int2 = 2, f_char1 = '2', f_char2 = '2', f_charbig = '===2==='; INSERT INTO t1 SET f_int1 = 2, f_int2 = 2, f_char1 = '2', f_char2 = '2', f_charbig = '===2==='; SELECT * FROM t1 WHERE f_int1 IS NULL; f_int1 f_int2 f_char1 f_char2 f_charbig drop table t1; create procedure p () begin create table t1 (s1 mediumint,s2 mediumint) partition by list (s2) (partition p1 values in (0), partition p2 values in (1)); end// call p()// drop procedure p// drop table t1// create procedure p () begin create table t1 (a int not null,b int not null,c int not null,primary key (a,b)) partition by range (a) subpartition by hash (a+b) (partition x1 values less than (1) (subpartition x11, subpartition x12), partition x2 values less than (5) (subpartition x21, subpartition x22)); end// call p()// drop procedure p// drop table t1// create table t1 (a int,b int,c int,key(a,b)) partition by range (a) partitions 3 (partition x1 values less than (0) tablespace ts1, partition x2 values less than (10) tablespace ts2, partition x3 values less than maxvalue tablespace ts3); insert into t1 values (NULL, 1, 1); insert into t1 values (0, 1, 1); insert into t1 values (12, 1, 1); select partition_name, partition_description, table_rows from information_schema.partitions where table_schema ='test'; partition_name partition_description table_rows x1 0 1 x2 10 1 x3 MAXVALUE 1 drop table t1; create table t1 (a int,b int, c int) partition by list(a) partitions 2 (partition x123 values in (11,12), partition x234 values in (1 ,NULL, NULL)); ERROR HY000: Multiple definition of same constant in list partitioning create table t1 (a int,b int, c int) partition by list(a) partitions 2 (partition x123 values in (11, NULL), partition x234 values in (1 ,NULL)); ERROR HY000: Multiple definition of same constant in list partitioning create table t1 (a int,b int, c int) partition by list(a) partitions 2 (partition x123 values in (11, 12), partition x234 values in (5, 1)); insert into t1 values (NULL,1,1); ERROR HY000: Table has no partition for value NULL drop table t1; create table t1 (a int,b int, c int) partition by list(a) partitions 2 (partition x123 values in (11, 12), partition x234 values in (NULL, 1)); insert into t1 values (11,1,6); insert into t1 values (NULL,1,1); select partition_name, partition_description, table_rows from information_schema.partitions where table_schema ='test'; partition_name partition_description table_rows x123 11,12 1 x234 NULL,1 1 drop table t1; create table t1 (a int) partition by list (a) (partition p0 values in (1)); alter table t1 rebuild partition; ERROR 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 drop table t1; create table t1 (a int) partition by list (a) (partition p0 values in (5)); insert into t1 values (0); ERROR HY000: Table has no partition for value 0 drop table t1; create table t1 (a int) partition by range (a) subpartition by hash (a) (partition p0 values less than (100)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (`a`) SUBPARTITION BY HASH (`a`) (PARTITION `p0` VALUES LESS THAN (100) ENGINE = MyISAM) alter table t1 add partition (partition p1 values less than (200) (subpartition subpart21)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (`a`) SUBPARTITION BY HASH (`a`) (PARTITION `p0` VALUES LESS THAN (100) (SUBPARTITION `p0sp0` ENGINE = MyISAM), PARTITION `p1` VALUES LESS THAN (200) (SUBPARTITION `subpart21` ENGINE = MyISAM)) drop table t1; create table t1 (a int) partition by key (a); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) alter table t1 add partition (partition p1); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) (PARTITION `p0` ENGINE = MyISAM, PARTITION `p1` ENGINE = MyISAM) drop table t1; create table t1 (a int, b int) partition by range (a) subpartition by hash(a) (partition p0 values less than (0) (subpartition sp0), partition p1 values less than (1)); ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')' at line 5 create table t1 (a int, b int) partition by range (a) subpartition by hash(a) (partition p0 values less than (0), partition p1 values less than (1) (subpartition sp0)); ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'subpartition sp0))' at line 5 create table t1 (a int, b int) partition by list (a) subpartition by hash(a) (partition p0 values in (0), partition p1 values in (1) (subpartition sp0)); ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'subpartition sp0))' at line 5 create table t1 (a int) partition by hash (a) (partition p0 (subpartition sp0)); ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning create table t1 (a int) partition by range (a) (partition p0 values less than (1)); alter table t1 add partition (partition p1 values in (2)); ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition alter table t1 add partition (partition p1); ERROR HY000: Syntax error: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition drop table t1; create table t1 (a int) partition by list (a) (partition p0 values in (1)); alter table t1 add partition (partition p1 values less than (2)); ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition alter table t1 add partition (partition p1); ERROR HY000: Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition drop table t1; create table t1 (a int) partition by hash (a) (partition p0); alter table t1 add partition (partition p1 values less than (2)); ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition alter table t1 add partition (partition p1 values in (2)); ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition drop table t1; create table t1 (a int) partition by list (a) (partition p0 values in (1)); alter table t1 rebuild partition; ERROR 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 drop table t1; create table t2 (s1 int not null auto_increment, primary key (s1)) partition by list (s1) (partition p1 values in (1),partition p2 values in (2),partition p3 values in (3),partition p4 values in (4)); insert into t2 values (null),(null),(null); select * from t2; s1 1 2 3 select * from t2 where s1 < 2; s1 1 update t2 set s1 = s1 + 1 order by s1 desc; select * from t2 where s1 < 3; s1 2 select * from t2 where s1 = 2; s1 2 drop table t2; create temporary table t1 (a int) partition by hash(a); ERROR HY000: Partitioned tables do not support CREATE TEMPORARY TABLE create table t1 (a int, b int) partition by list (a) (partition p1 values in (1), partition p2 values in (2)); alter table t1 add primary key (b); ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY LIST (`a`) (PARTITION `p1` VALUES IN (1) ENGINE = MyISAM, PARTITION `p2` VALUES IN (2) ENGINE = MyISAM) drop table t1; create table t1 (a int unsigned not null auto_increment primary key) partition by key(a); alter table t1 rename t2, add c char(10), comment "no comment"; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `c` char(10) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci COMMENT='no comment' PARTITION BY KEY (`a`) drop table t2; create table t1 (f1 int) partition by hash (f1) as select 1; drop table t1; prepare stmt1 from 'create table t1 (s1 int) partition by hash (s1)'; execute stmt1; execute stmt1; ERROR 42S01: Table 't1' already exists drop table t1; CREATE PROCEDURE test.p1(IN i INT) BEGIN DECLARE CONTINUE HANDLER FOR sqlexception BEGIN END; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (num INT,PRIMARY KEY(num)); START TRANSACTION; INSERT INTO t1 VALUES(i); savepoint t1_save; INSERT INTO t1 VALUES (14); ROLLBACK to savepoint t1_save; COMMIT; END| CALL test.p1(12); Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back CALL test.p1(13); Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back drop table t1; drop procedure test.p1; CREATE TABLE t1 (a int not null) partition by key(a) (partition p0 COMMENT='first partition'); drop table t1; CREATE TABLE t1 (`a b` int not null) partition by key(`a b`); drop table t1; CREATE TABLE t1 (`a b` int not null) partition by hash(`a b`); drop table t1; create table t1 (f1 integer) partition by range(f1) (partition p1 values less than (0), partition p2 values less than (10)); insert into t1 set f1 = null; select * from t1 where f1 is null; f1 NULL explain partitions select * from t1 where f1 is null; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 system NULL NULL NULL NULL 1 drop table t1; create table t1 (f1 integer) partition by list(f1) (partition p1 values in (1), partition p2 values in (null)); insert into t1 set f1 = null; insert into t1 set f1 = 1; select * from t1 where f1 is null or f1 = 1; f1 1 NULL drop table t1; create table t1 (f1 smallint) partition by list (f1) (partition p0 values in (null)); insert into t1 values (null); select * from t1 where f1 is null; f1 NULL select * from t1 where f1 < 1; f1 select * from t1 where f1 <= NULL; f1 select * from t1 where f1 < NULL; f1 select * from t1 where f1 >= NULL; f1 select * from t1 where f1 > NULL; f1 select * from t1 where f1 > 1; f1 drop table t1; create table t1 (f1 smallint) partition by range (f1) (partition p0 values less than (0)); insert into t1 values (null); select * from t1 where f1 is null; f1 NULL drop table t1; create table t1 (f1 integer) partition by list(f1) ( partition p1 values in (1), partition p2 values in (NULL), partition p3 values in (2), partition p4 values in (3), partition p5 values in (4) ); insert into t1 values (1),(2),(3),(4),(null); select * from t1 where f1 < 3; f1 1 2 explain partitions select * from t1 where f1 < 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p3 ALL NULL NULL NULL NULL 2 Using where select * from t1 where f1 is null; f1 NULL explain partitions select * from t1 where f1 is null; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 system NULL NULL NULL NULL 1 drop table t1; create table t1 (f1 int) partition by list(f1 div 2) ( partition p1 values in (1), partition p2 values in (NULL), partition p3 values in (2), partition p4 values in (3), partition p5 values in (4) ); insert into t1 values (2),(4),(6),(8),(null); select * from t1 where f1 < 3; f1 2 explain partitions select * from t1 where f1 < 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2,p3,p4,p5 ALL NULL NULL NULL NULL 5 Using where select * from t1 where f1 is null; f1 NULL explain partitions select * from t1 where f1 is null; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 system NULL NULL NULL NULL 1 drop table t1; create table t1 (a int) partition by LIST(a) ( partition pn values in (NULL), partition p0 values in (0), partition p1 values in (1), partition p2 values in (2) ); insert into t1 values (NULL),(0),(1),(2); select * from t1 where a is null or a < 2; a NULL 0 1 explain partitions select * from t1 where a is null or a < 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pn,p0,p1 ALL NULL NULL NULL NULL 3 Using where select * from t1 where a is null or a < 0 or a > 1; a NULL 2 explain partitions select * from t1 where a is null or a < 0 or a > 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pn,p2 ALL NULL NULL NULL NULL 2 Using where drop table t1; CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, name VARCHAR(20)) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE(id) (PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (20) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (30) ENGINE = MyISAM); SHOW TABLE STATUS; Name 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 t1 MyISAM 10 Dynamic 0 0 0 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N DROP TABLE t1; create table t1 (a bigint unsigned) partition by list (a) (partition p0 values in (0-1)); ERROR HY000: Partition constant is out of partition function domain create table t1 (a bigint unsigned) partition by range (a) (partition p0 values less than (10)); insert into t1 values (0xFFFFFFFFFFFFFFFF); ERROR HY000: Table has no partition for value 18446744073709551615 drop table t1; create table t1 (a int) partition by list (a) (partition `s1 s2` values in (0)); drop table t1; create table t1 (a int) partition by list (a) (partition `7` values in (0)); drop table t1; create table t1 (a int) partition by list (a) (partition `s1 s2 ` values in (0)); ERROR HY000: Incorrect partition name create table t1 (a int) partition by list (a) subpartition by hash (a) (partition p1 values in (0) (subpartition `p1 p2 `)); ERROR HY000: Incorrect partition name CREATE TABLE t1 (a int) PARTITION BY LIST (a) (PARTITION p0 VALUES IN (NULL)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY LIST (`a`) (PARTITION `p0` VALUES IN (NULL) ENGINE = MyISAM) DROP TABLE t1; CREATE TABLE t1 (a int) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (NULL)); ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN create table t1 (s1 int auto_increment primary key) partition by list (s1) (partition p1 values in (1), partition p2 values in (2), partition p3 values in (3)); insert into t1 values (null); insert into t1 values (null); insert into t1 values (null); select auto_increment from information_schema.tables where table_name='t1'; auto_increment 4 select * from t1; s1 1 2 3 drop table t1; create table t1 (a int) engine=memory partition by key(a); insert into t1 values (1); create index inx1 on t1(a); drop table t1; create table t1 (a int) PARTITION BY KEY (a) (PARTITION p0); set session sql_mode='no_table_options'; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) PARTITION BY KEY (`a`) (PARTITION `p0`) set session sql_mode=''; drop table t1; create table t1 (a int) partition by key (a) (partition p0 engine = MERGE); ERROR HY000: Engine cannot be used in partitioned tables create table t1 (a varchar(1)) partition by key (a) as select 'a'; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(1) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) drop table t1; CREATE TABLE t1 (a int) ENGINE = MYISAM PARTITION BY KEY(a); INSERT into t1 values (1), (2); SHOW TABLE STATUS; Name 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 t1 MyISAM 10 Fixed 2 7 14 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N DELETE from t1 where a = 1; SHOW TABLE STATUS; Name 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 t1 MyISAM 10 Fixed 1 14 14 0 0 7 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N ALTER TABLE t1 OPTIMIZE PARTITION p0; Table Op Msg_type Msg_text test.t1 optimize status OK SHOW TABLE STATUS; Name 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 t1 MyISAM 10 Fixed 1 7 7 0 1024 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N DROP TABLE t1; CREATE TABLE t1 (a int, index(a)) PARTITION BY KEY(a); ALTER TABLE t1 DISABLE KEYS; ALTER TABLE t1 ENABLE KEYS; DROP TABLE t1; create table t1 (a int) engine=MEMORY partition by key (a); REPAIR TABLE t1; Table Op Msg_type Msg_text test.t1 repair status OK OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note The storage engine for the table doesn't support optimize CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze drop table t1; drop procedure if exists mysqltest_1; create table t1 (a int) partition by list (a) (partition p0 values in (0)); insert into t1 values (0); create procedure mysqltest_1 () begin begin declare continue handler for sqlexception begin end; update ignore t1 set a = 1 where a = 0; end; prepare stmt1 from 'alter table t1'; execute stmt1; end// call mysqltest_1()// drop table t1; drop procedure mysqltest_1; create table t1 (a int, index(a)) partition by hash(a); insert into t1 values (1),(2); select * from t1 ORDER BY a DESC; a 2 1 drop table t1; create table t1 (a bigint unsigned not null, primary key(a)) engine = myisam partition by key (a) partitions 10; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bigint(20) unsigned NOT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY KEY (`a`) PARTITIONS 10 insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612); select * from t1; a 18446744073709551612 18446744073709551613 18446744073709551614 18446744073709551615 select * from t1 where a = 18446744073709551615; a 18446744073709551615 delete from t1 where a = 18446744073709551615; select * from t1; a 18446744073709551612 18446744073709551613 18446744073709551614 drop table t1; CREATE TABLE t1 ( num int(11) NOT NULL, cs int(11) NOT NULL) PARTITION BY RANGE (num) SUBPARTITION BY HASH ( cs) SUBPARTITIONS 2 (PARTITION p_X VALUES LESS THAN MAXVALUE); ALTER TABLE t1 REORGANIZE PARTITION p_X INTO ( PARTITION p_100 VALUES LESS THAN (100), PARTITION p_X VALUES LESS THAN MAXVALUE ); drop table t1; CREATE TABLE t2 ( taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', id int(11) NOT NULL DEFAULT '0', PRIMARY KEY (id,taken), KEY taken (taken) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO t2 VALUES ('2006-09-27 21:50:01',16421), ('2006-10-02 21:50:01',16421), ('2006-09-27 21:50:01',19092), ('2006-09-28 21:50:01',19092), ('2006-09-29 21:50:01',19092), ('2006-09-30 21:50:01',19092), ('2006-10-01 21:50:01',19092), ('2006-10-02 21:50:01',19092), ('2006-09-27 21:50:01',22589), ('2006-09-29 21:50:01',22589); CREATE TABLE t1 ( id int(8) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO t1 VALUES (16421), (19092), (22589); CREATE TABLE t4 ( taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', id int(11) NOT NULL DEFAULT '0', PRIMARY KEY (id,taken), KEY taken (taken) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (to_days(taken)) ( PARTITION p01 VALUES LESS THAN (732920) , PARTITION p02 VALUES LESS THAN (732950) , PARTITION p03 VALUES LESS THAN MAXVALUE ) ; INSERT INTO t4 select * from t2; set @f_date='2006-09-28'; set @t_date='2006-10-02'; SELECT t1.id AS MyISAM_part FROM t1 WHERE t1.id IN ( SELECT distinct id FROM t4 WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY)) ORDER BY t1.id; MyISAM_part 16421 19092 22589 drop table t1, t2, t4; CREATE TABLE t1 ( taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', id int(11) NOT NULL DEFAULT '0', status varchar(20) NOT NULL DEFAULT '', PRIMARY KEY (id,taken) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (to_days(taken)) ( PARTITION p15 VALUES LESS THAN (732950) , PARTITION p16 VALUES LESS THAN MAXVALUE ) ; INSERT INTO t1 VALUES ('2006-09-27 21:50:01',22589,'Open'), ('2006-09-29 21:50:01',22589,'Verified'); DROP TABLE IF EXISTS t2; Warnings: Note 1051 Unknown table 'test.t2' CREATE TABLE t2 ( id int(8) NOT NULL, severity tinyint(4) NOT NULL DEFAULT '0', priority tinyint(4) NOT NULL DEFAULT '0', status varchar(20) DEFAULT NULL, alien tinyint(4) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO t2 VALUES (22589,1,1,'Need Feedback',0); SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified'); id 22589 drop table t1, t2; create table t1 (c1 varchar(255),c2 tinyint,primary key(c1)) partition by key (c1) partitions 10 ; insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1; insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1; select * from t1; c1 c2 aaa 2 drop table t1; create table t1 (s1 bigint) partition by list (s1) (partition p1 values in (-9223372036854775808)); drop table t1; create table t1(a int auto_increment, b int, primary key (b, a)) partition by hash(b) partitions 2; insert into t1 values (null, 1); show table status; Name 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 t1 MyISAM 10 Fixed 1 9 9 0 0 0 1 NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N drop table t1; create table t1(a int auto_increment primary key) partition by key(a) partitions 2; insert into t1 values (null), (null), (null); show table status; Name 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 t1 MyISAM 10 Fixed 3 7 21 0 0 0 4 NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N drop table t1; CREATE TABLE t1(a INT NOT NULL, b TINYBLOB, KEY(a)) PARTITION BY RANGE(a) ( PARTITION p0 VALUES LESS THAN (32)); INSERT INTO t1 VALUES (1, REPEAT('a', 10)); INSERT INTO t1 SELECT a + 1, b FROM t1; INSERT INTO t1 SELECT a + 2, b FROM t1; INSERT INTO t1 SELECT a + 4, b FROM t1; INSERT INTO t1 SELECT a + 8, b FROM t1; ALTER TABLE t1 ADD PARTITION (PARTITION p1 VALUES LESS THAN (64)); ALTER TABLE t1 DROP PARTITION p1; DROP TABLE t1; create table t (s1 int) engine=myisam partition by key (s1); create trigger t_ad after delete on t for each row insert into t values (old.s1); insert into t values (1); drop table t; create table t2 (b int); create table t1 (b int) PARTITION BY RANGE (t2.b) ( PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20) ) select * from t2; ERROR 42S22: Unknown column 't2.b' in 'partition function' create table t1 (a int) PARTITION BY RANGE (b) ( PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20) ) select * from t2; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (`b`) (PARTITION `p1` VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION `p2` VALUES LESS THAN (20) ENGINE = MyISAM) drop table t1, t2; create table t1 (s1 timestamp on update current_timestamp, s2 int) partition by key(s1) partitions 3; insert into t1 values (null,null); drop table t1; create table t1 ( c0 int, c1 bigint, c2 set('sweet'), key (c2,c1,c0), key(c0) ) engine=myisam partition by hash (c0) partitions 5; insert ignore into t1 set c0 = -6502262, c1 = 3992917, c2 = 35019; insert ignore into t1 set c0 = 241221, c1 = -6862346, c2 = 56644; select c1 from t1 group by (select c0 from t1 limit 1); c1 -6862346 drop table t1; CREATE TABLE t1(a int) PARTITION BY RANGE (a) ( PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20) ); ALTER TABLE t1 OPTIMIZE PARTITION p1 EXTENDED; ERROR 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 ALTER TABLE t1 ANALYZE PARTITION p1 EXTENDED; ERROR 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 ALTER TABLE t1 ANALYZE PARTITION p1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK ALTER TABLE t1 CHECK PARTITION p1; Table Op Msg_type Msg_text test.t1 check status OK ALTER TABLE t1 REPAIR PARTITION p1; Table Op Msg_type Msg_text test.t1 repair status OK ALTER TABLE t1 OPTIMIZE PARTITION p1; Table Op Msg_type Msg_text test.t1 optimize status OK DROP TABLE t1; CREATE TABLE t1 (s1 BIGINT UNSIGNED) PARTITION BY RANGE (s1) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (1), PARTITION p2 VALUES LESS THAN (18446744073709551615) ); INSERT INTO t1 VALUES (0), (18446744073709551614); INSERT INTO t1 VALUES (18446744073709551615); ERROR HY000: Table has no partition for value 18446744073709551615 DROP TABLE t1; CREATE TABLE t1 (s1 BIGINT UNSIGNED) PARTITION BY RANGE (s1) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (1), PARTITION p2 VALUES LESS THAN (18446744073709551614), PARTITION p3 VALUES LESS THAN MAXVALUE ); INSERT INTO t1 VALUES (-1), (0), (18446744073709551613), (18446744073709551614), (18446744073709551615); Warnings: Warning 1264 Out of range value for column 's1' at row 1 SELECT * FROM t1; s1 0 0 18446744073709551613 18446744073709551614 18446744073709551615 SELECT * FROM t1 WHERE s1 = 0; s1 0 0 SELECT * FROM t1 WHERE s1 = 18446744073709551614; s1 18446744073709551614 SELECT * FROM t1 WHERE s1 = 18446744073709551615; s1 18446744073709551615 DROP TABLE t1; CREATE TABLE t1 (s1 BIGINT UNSIGNED) PARTITION BY RANGE (s1) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (1), PARTITION p2 VALUES LESS THAN (18446744073709551615), PARTITION p3 VALUES LESS THAN MAXVALUE ); DROP TABLE t1; CREATE TABLE t1 (int_column INT, char_column CHAR(5), PRIMARY KEY(char_column,int_column)) PARTITION BY KEY(char_column,int_column) PARTITIONS 101; INSERT INTO t1 (int_column, char_column) VALUES ( 39868 ,'zZZRW'), ( 545592 ,'zZzSD'), ( 4936 ,'zzzsT'), ( 9274 ,'ZzZSX'), ( 970185 ,'ZZzTN'), ( 786036 ,'zZzTO'), ( 37240 ,'zZzTv'), ( 313801 ,'zzzUM'), ( 782427 ,'ZZZva'), ( 907955 ,'zZZvP'), ( 453491 ,'zzZWV'), ( 756594 ,'ZZZXU'), ( 718061 ,'ZZzZH'); SELECT * FROM t1 ORDER BY char_column DESC; int_column char_column 718061 ZZzZH 756594 ZZZXU 453491 zzZWV 907955 zZZvP 782427 ZZZva 313801 zzzUM 37240 zZzTv 786036 zZzTO 970185 ZZzTN 9274 ZzZSX 4936 zzzsT 545592 zZzSD 39868 zZZRW DROP TABLE t1; CREATE TABLE t1(id MEDIUMINT NOT NULL AUTO_INCREMENT, user CHAR(25), PRIMARY KEY(id)) PARTITION BY RANGE(id) SUBPARTITION BY hash(id) subpartitions 2 (PARTITION pa1 values less than (10), PARTITION pa2 values less than (20), PARTITION pa11 values less than MAXVALUE); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `user` char(25) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (`id`) SUBPARTITION BY HASH (`id`) SUBPARTITIONS 2 (PARTITION `pa1` VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION `pa2` VALUES LESS THAN (20) ENGINE = MyISAM, PARTITION `pa11` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) drop table t1; CREATE TABLE t1 ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `createdDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `number` int, PRIMARY KEY (`ID`, number) ) PARTITION BY RANGE (number) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11) ); create table t2 ( `ID` bigint(20), `createdDate` TIMESTAMP, `number` int ); INSERT INTO t1 SET number=1; insert into t2 select * from t1; SELECT SLEEP(1); SLEEP(1) 0 UPDATE t1 SET number=6; select count(*) from t1, t2 where t1.createdDate = t2.createdDate; count(*) 1 drop table t1, t2; SET @orig_sql_mode = @@SQL_MODE; SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO'; CREATE TABLE t1 (c1 INT) PARTITION BY LIST(1 DIV c1) ( PARTITION p0 VALUES IN (NULL), PARTITION p1 VALUES IN (1) ); INSERT INTO t1 VALUES (0); ERROR 22012: Division by 0 SELECT * FROM t1; c1 TRUNCATE t1; INSERT INTO t1 VALUES (NULL), (0), (1), (2); ERROR 22012: Division by 0 SELECT * FROM t1; c1 NULL DROP TABLE t1; SET SQL_MODE= @orig_sql_mode; create table t1 (s1 int) partition by hash(s1) partitions 2; create index i on t1 (s1); insert into t1 values (1); insert into t1 select s1 from t1; insert into t1 select s1 from t1; insert into t1 select s1 from t1 order by s1 desc; select * from t1; s1 1 1 1 1 1 1 1 1 drop table t1; create table t1 (s1 int) partition by range(s1) (partition pa1 values less than (10), partition pa2 values less than MAXVALUE); create index i on t1 (s1); insert into t1 values (1); insert into t1 select s1 from t1; insert into t1 select s1 from t1; insert into t1 select s1 from t1 order by s1 desc; select * from t1; s1 1 1 1 1 1 1 1 1 drop table t1; create table t1 (s1 int) partition by range(s1) (partition pa1 values less than (10), partition pa2 values less than MAXVALUE); create index i on t1 (s1); insert into t1 values (20); insert into t1 select s1 from t1; insert into t1 select s1 from t1; insert into t1 select s1 from t1 order by s1 desc; select * from t1; s1 20 20 20 20 20 20 20 20 drop table t1; create table t1 (s1 int) partition by range(s1) (partition pa1 values less than (10), partition pa2 values less than MAXVALUE); create index i on t1 (s1); insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8); insert into t1 select s1 from t1; insert into t1 select s1 from t1; insert into t1 select s1 from t1; insert into t1 select s1 from t1; insert into t1 select s1 from t1 order by s1 desc; insert into t1 select s1 from t1 where s1=3; select count(*) from t1; count(*) 288 drop table t1; # # Bug#42944: partition not pruned correctly # CREATE TABLE t1 (a int) PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300), PARTITION p3 VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES (10), (100), (200), (300), (400); EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a>=200; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 3 Using where DROP TABLE t1; CREATE TABLE t1 ( a INT, b INT, c INT, KEY bc(b, c) ) PARTITION BY KEY (a, b) PARTITIONS 3 ; INSERT INTO t1 VALUES (17, 1, -8), (3, 1, -7), (23, 1, -6), (22, 1, -5), (11, 1, -4), (21, 1, -3), (19, 1, -2), (30, 1, -1), (20, 1, 1), (16, 1, 2), (18, 1, 3), (9, 1, 4), (15, 1, 5), (28, 1, 6), (29, 1, 7), (25, 1, 8), (10, 1, 9), (13, 1, 10), (27, 1, 11), (24, 1, 12), (12, 1, 13), (26, 1, 14), (14, 1, 15) ; SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c; b c 1 -8 1 -7 1 -6 1 -5 1 -4 1 -3 1 -2 1 -1 1 1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 1 10 1 11 1 12 1 13 1 14 1 15 EXPLAIN SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref bc bc 5 const 23 Using where; Using index EXPLAIN SELECT b, c FROM t1 WHERE b = 1 or b=2 GROUP BY b, c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range bc bc 5 NULL 23 Using where; Using index DROP TABLE t1; # # Bug #45807: crash accessing partitioned table and sql_mode # contains ONLY_FULL_GROUP_BY # Bug#46923: select count(*) from partitioned table fails with # ONLY_FULL_GROUP_BY # SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'; CREATE TABLE t1(id INT,KEY(id)) ENGINE=MYISAM PARTITION BY HASH(id) PARTITIONS 2; SELECT COUNT(*) FROM t1; COUNT(*) 0 DROP TABLE t1; SET SESSION SQL_MODE=DEFAULT; # # Bug#46198: Hang after failed ALTER TABLE on partitioned table. # DROP TABLE IF EXISTS t1; CREATE TABLE t1 (s1 INT PRIMARY KEY) PARTITION BY HASH(s1); LOCK TABLES t1 WRITE, t1 b READ; UNLOCK TABLES; ALTER TABLE t1 DROP PARTITION p1; ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions SELECT * FROM t1; s1 DROP TABLE t1; CREATE TABLE t1 (s1 VARCHAR(5) PRIMARY KEY) PARTITION BY KEY(s1); LOCK TABLES t1 WRITE, t1 b READ; UNLOCK TABLES; ALTER TABLE t1 ADD COLUMN (s3 VARCHAR(5) UNIQUE); ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function SELECT * FROM t1; s1 DROP TABLE t1; # # BUG#51868 - crash with myisam_use_mmap and partitioned myisam tables # SET GLOBAL myisam_use_mmap=1; CREATE TABLE t1(a INT) PARTITION BY HASH(a) PARTITIONS 1; INSERT INTO t1 VALUES(0); FLUSH TABLE t1; TRUNCATE TABLE t1; INSERT INTO t1 VALUES(0); DROP TABLE t1; SET GLOBAL myisam_use_mmap=default; # # Bug#13580775 ASSERTION FAILED: RECORD_LENGTH == M_RECORD_LENGTH, # FILE FILESORT_UTILS.CC # CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, c CHAR(1), d INT, KEY (c,d) ) PARTITION BY KEY () PARTITIONS 1; INSERT INTO t1 VALUES (1,1,'a',1), (2,2,'a',1); SELECT 1 FROM t1 WHERE 1 IN (SELECT group_concat(b) FROM t1 WHERE c > geomfromtext('point(1 1)') GROUP BY b ); 1 1 1 DROP TABLE t1; # # Bug#13011410 CRASH IN FILESORT CODE WITH GROUP BY/ROLLUP # CREATE TABLE t1 ( a INT, b MEDIUMINT, c VARCHAR(300) CHARACTER SET hp8 COLLATE hp8_bin, PRIMARY KEY (a,c(299))) ENGINE=myisam PARTITION BY LINEAR KEY () PARTITIONS 2; INSERT INTO t1 VALUES (1,2,'test'), (2,3,'hi'), (4,5,'bye'); SELECT 1 FROM t1 WHERE b < SOME ( SELECT 1 FROM t1 WHERE a >= 1 GROUP BY b WITH ROLLUP HAVING b > geomfromtext("") ); ERROR HY000: Illegal parameter data types mediumint and geometry for operation '>' DROP TABLE t1; MDEV-612 Valgrind error in ha_maria::check_if_incompatible_data CREATE TABLE t1 (a INT, b INT, KEY(a)) ENGINE=Aria PARTITION BY KEY(a) PARTITIONS 2; ALTER TABLE t1 ADD KEY (b); drop table t1; End of 5.1 tests # # BUG#55385: UPDATE statement throws an error, but still updates # the table entries CREATE TABLE t1_part ( partkey int, nokey int ) PARTITION BY LINEAR HASH(partkey) PARTITIONS 3; INSERT INTO t1_part VALUES (1, 1) , (10, 10); CREATE VIEW v1 AS SELECT * FROM t1_part; # Should be (1,1),(10,10) SELECT * FROM t1_part; partkey nokey 1 1 10 10 # Case 1 # Update is refused because partitioning key is updated UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.partkey = 2, B.nokey = 3; ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2, B.partkey = 3; ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' # Case 2 # Like 1, but partition accessed through a view UPDATE t1_part AS A NATURAL JOIN v1 as B SET A.nokey = 2 , B.partkey = 3; ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' UPDATE v1 AS A NATURAL JOIN t1_part as B SET A.nokey = 2 , B.partkey = 3; ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' # Should be (1,1),(10,10) SELECT * FROM t1_part; partkey nokey 1 1 10 10 # Case 3 # Update is accepted because partitioning key is not updated UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2 , B.nokey = 3; # Should be (1,3),(10,3) SELECT * FROM t1_part; partkey nokey 1 3 10 3 DROP VIEW v1; DROP TABLE t1_part; # # BUG#598247: partition.test produces valgrind errors in 5.3-based branches # CREATE TABLE t1 ( a INT DEFAULT NULL, b DOUBLE DEFAULT NULL, c INT DEFAULT NULL, KEY idx2(b,a) ) engine=myisam PARTITION BY HASH(c) PARTITIONS 3; INSERT INTO t1 VALUES (6,8,9); INSERT INTO t1 VALUES (6,8,10); SELECT 1 FROM t1 JOIN t1 AS t2 USING (a); 1 1 1 1 1 drop table t1; # # LP BUG#1001117 Crash on a simple select that uses a temptable view # MySQL Bug #12330344 Crash and/or valgrind errors in free_io_cache with join, view, # partitioned table # CREATE TABLE t1(a INT PRIMARY KEY) PARTITION BY LINEAR KEY (a); CREATE ALGORITHM=TEMPTABLE VIEW vtmp AS SELECT 1 FROM t1 AS t1_0 JOIN t1 ON t1_0.a LIKE (SELECT 1 FROM t1); SELECT * FROM vtmp; 1 DROP VIEW vtmp; DROP TABLE t1; # # MDEV-365 "Got assertion when doing alter table on a partition" # CREATE TABLE t1 ( i INT ) ENGINE=Aria PARTITION BY HASH(i) PARTITIONS 2; INSERT INTO t1 VALUES (1),(2),(2),(3),(4); ALTER TABLE t1 ADD PARTITION PARTITIONS 2; SELECT * from t1 order by i; i 1 2 2 3 4 DROP TABLE t1; # # MDEV-5555: Incorrect index_merge on BTREE indices # CREATE TABLE t1 ( id bigint(20) unsigned NOT NULL, id2 bigint(20) unsigned NOT NULL, dob date DEFAULT NULL, address char(100) DEFAULT NULL, city char(35) DEFAULT NULL, hours_worked_per_week smallint(5) unsigned DEFAULT NULL, weeks_worked_last_year tinyint(3) unsigned DEFAULT NULL, KEY dob (dob), KEY address (address), KEY city (city), KEY hours_worked_per_week (hours_worked_per_week), KEY weeks_worked_last_year (weeks_worked_last_year) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY KEY (id) PARTITIONS 5; # Insert some rows select * from t1 where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21'; id id2 dob address city hours_worked_per_week weeks_worked_last_year 16 16 1949-11-07 address16 city16 40 52 50 50 1923-09-08 address50 city50 40 52 select * 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'; id id2 dob address city hours_worked_per_week weeks_worked_last_year 16 16 1949-11-07 address16 city16 40 52 50 50 1923-09-08 address50 city50 40 52 drop table t1; # # MDEV-6322: The PARTITION engine can return wrong query results # CREATE TABLE t1 ( CustomerID varchar(5) DEFAULT NULL, CompanyName varchar(40) DEFAULT NULL, ContactName varchar(30) DEFAULT NULL, ContactTitle varchar(30) DEFAULT NULL, Address varchar(60) DEFAULT NULL, City varchar(15) DEFAULT NULL, Region varchar(15) DEFAULT NULL, PostalCode varchar(10) DEFAULT NULL, Country varchar(15) NOT NULL, Phone varchar(24) DEFAULT NULL, Fax varchar(24) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY LIST COLUMNS(Country) (PARTITION p1 VALUES IN ('Germany','Austria','Switzerland','Poland'), PARTITION p2 VALUES IN ('USA','Canada','Mexico'), PARTITION p3 VALUES IN ('Spain','Portugal','Italy'), PARTITION p4 VALUES IN ('UK','Ireland'), PARTITION p5 VALUES IN ('France','Belgium'), PARTITION p6 VALUES IN ('Sweden','Finland','Denmark','Norway'), PARTITION p7 VALUES IN ('Venezuela','Argentina','Brazil') ); INSERT INTO t1 (CustomerID, City, Country) VALUES ('ANATR','México D.F','Mexico'), ('ANTON','México D.F','Mexico'), ('BOTTM','Tsawassen','Canada'), ('CENTC','México D.F','Mexico'), ('GREAL','Eugene','USA'), ('HUNGC','Elgin','USA'), ('LAUGB','Vancouver','Canada'), ('LAZYK','Walla Walla','USA'), ('LETSS','San Francisco','USA'), ('LONEP','Portland','USA'); SELECT * FROM t1 WHERE Country = 'USA'; CustomerID CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone Fax GREAL NULL NULL NULL NULL Eugene NULL NULL USA NULL NULL HUNGC NULL NULL NULL NULL Elgin NULL NULL USA NULL NULL LAZYK NULL NULL NULL NULL Walla Walla NULL NULL USA NULL NULL LETSS NULL NULL NULL NULL San Francisco NULL NULL USA NULL NULL LONEP NULL NULL NULL NULL Portland NULL NULL USA NULL NULL DROP TABLE t1; CREATE TABLE t1 ( d DATE NOT NULL) PARTITION BY RANGE( YEAR(d) ) ( PARTITION p0 VALUES LESS THAN (1960), PARTITION p1 VALUES LESS THAN (1970), PARTITION p2 VALUES LESS THAN (1980), PARTITION p3 VALUES LESS THAN (1990) ); ALTER TABLE t1 ADD PARTITION IF NOT EXISTS( PARTITION `p5` VALUES LESS THAN (2010) COMMENT 'APSTART \' APEND' ); ALTER TABLE t1 ADD PARTITION IF NOT EXISTS( PARTITION `p5` VALUES LESS THAN (2010) COMMENT 'APSTART \' APEND' ); Warnings: Note 1517 Duplicate partition name p5 alter table t1 drop partition if exists p5; alter table t1 drop partition if exists p5; Warnings: Note 1507 Wrong partition name or partition list DROP TABLE t1; CREATE TABLE t1 (a INT) ENGINE=MyISAM PARTITION BY RANGE(a) (PARTITION p1 VALUES LESS THAN (0)); ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES LESS THAN (1)); PREPARE stmt FROM 'ALTER TABLE t1 ADD PARTITION IF NOT EXISTS (PARTITION p2 VALUES LESS THAN (2))'; EXECUTE stmt; Warnings: Note 1517 Duplicate partition name p2 EXECUTE stmt; Warnings: Note 1517 Duplicate partition name p2 DEALLOCATE PREPARE stmt; DROP TABLE t1; # # MDEV-17032: Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY # create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int); insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; create table t2 ( part_key int, a int, b int ) partition by list(part_key) ( partition p0 values in (0), partition p1 values in (1), partition p2 values in (2), partition p3 values in (3), partition p4 values in (4) ); insert into t2 select mod(a,5), a/100, mod(a,5) from t1; set @save_use_stat_tables= @@use_stat_tables; set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; # # Tests using stats provided by the storage engine # explain extended select * from t2 where part_key=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 200 100.00 Using where Warnings: Note 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 explain partitions select * from t2 where part_key=1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p1 ALL NULL NULL NULL NULL 200 Using where explain extended select * from t2 where part_key in (1,2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 400 100.00 Using where Warnings: Note 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) explain partitions select * from t2 where part_key in (1,2); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p1,p2 ALL NULL NULL NULL NULL 400 Using where explain extended select * from t2 where b=5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 100.00 Using where Warnings: Note 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 explain partitions select * from t2 where b=5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1000 Using where explain extended select * from t2 partition(p0) where b=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 200 100.00 Using where Warnings: Note 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 set @save_histogram_size=@@histogram_size; set @@histogram_size=100; set @@use_stat_tables= PREFERABLY; set @@optimizer_use_condition_selectivity=4; analyze table t2; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK # # Tests using EITS # # filtered should be 100 explain extended select * from t2 where part_key=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 200 100.00 Using where Warnings: Note 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 explain partitions select * from t2 where part_key=1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p1 ALL NULL NULL NULL NULL 200 Using where # filtered should be 100 explain extended select * from t2 where part_key in (1,2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 400 100.00 Using where Warnings: Note 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) explain partitions select * from t2 where part_key in (1,2); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p1,p2 ALL NULL NULL NULL NULL 400 Using where explain extended select * from t2 where b=5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 0.10 Using where Warnings: Note 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 explain partitions select * from t2 where b=5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1000 Using where explain extended select * from t2 partition(p0) where b=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 200 20.00 Using where Warnings: Note 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 set @@use_stat_tables= @save_use_stat_tables; set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set @@histogram_size= @save_histogram_size; drop table t0,t1,t2; # # End of 10.0 tests # # # MDEV-8283 crash in get_mm_leaf with xor on binary col # CREATE TABLE t1(a BINARY(80)) PARTITION BY KEY(a) PARTITIONS 3; SELECT 1 FROM t1 WHERE a XOR 'a'; 1 DROP TABLE t1; # # Bug #25207522: INCORRECT ORDER-BY BEHAVIOR ON A PARTITIONED TABLE # WITH A COMPOSITE PREFIX INDEX # create table t1(id int unsigned not null, data varchar(2) default null, key data_idx (data(1),id) ) default charset=utf8 partition by range (id) ( partition p10 values less than (10), partition p20 values less than (20) ); insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ; select id from t1 where data = 'ab' order by id; id 4 5 6 14 15 16 drop table t1; create table t1(id int unsigned not null, data text default null, key data_idx (data(1),id) ) default charset=utf8 partition by range (id) ( partition p10 values less than (10), partition p20 values less than (20) ); insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ; select id from t1 where data = 'ab' order by id; id 4 5 6 14 15 16 drop table t1; # # MDEV-5628: Assertion `! is_set()' or `!is_set() || # (m_status == DA_OK_BULK && is_bulk_op())' fails on UPDATE on a # partitioned table with subquery (MySQL:71630) # CREATE TABLE t1 (a INT) PARTITION BY HASH(a) PARTITIONS 2; CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1),(2); UPDATE t1 SET a = 7 WHERE a = ( SELECT b FROM t2 ) ORDER BY a LIMIT 6; ERROR 21000: Subquery returns more than 1 row DROP TABLE t1,t2; # # End of 10.1 tests # # # MDEV-30596: Assertion 'pushed_rowid_filter != __null ...' failed # create table t1 (a int); insert into t1 values (NULL),(1),(2); create table t2 (a int); insert into t2 select seq from seq_1_to_1000; create table t3 ( a1 int, a2 int, b int, c int, filler1 char(200), filler2 char(200), key(a1,a2), key(b) ) partition by hash(a1) partitions 2; insert into t3 select seq/100, seq/100, seq, seq, seq, seq from seq_1_to_10000; analyze table t3 persistent for all; Table Op Msg_type Msg_text test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK set @tmp_os= @@optimizer_switch; set optimizer_switch='materialization=off'; # Must not show "Using rowid filter": explain select * from t1 where t1.a not in (select straight_join t3.a1 from t2, t3 where t3.b < 3000 and t3.a2=t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1000 Using where 2 DEPENDENT SUBQUERY t3 ref_or_null a1,b a1 10 func,test.t2.a 198 Using where; Full scan on NULL key set optimizer_switch=@tmp_os; drop table t1,t2,t3;