diff options
| author | Michael Widenius <monty@mariadb.org> | 2018-03-09 14:05:35 +0200 |
|---|---|---|
| committer | Monty <monty@mariadb.org> | 2018-03-29 13:59:44 +0300 |
| commit | a7abddeffa6a760ce948c2dfb007cdf3f1a369d5 (patch) | |
| tree | 70eb743fa965a17380bbc0ac88ae79ca1075b896 /mysql-test/r/partition.result | |
| parent | ab1941266c59a19703a74b5593cf3f508a5752d7 (diff) | |
| download | mariadb-git-a7abddeffa6a760ce948c2dfb007cdf3f1a369d5.tar.gz | |
Create 'main' test directory and move 't' and 'r' there
Diffstat (limited to 'mysql-test/r/partition.result')
| -rw-r--r-- | mysql-test/r/partition.result | 2663 |
1 files changed, 0 insertions, 2663 deletions
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result deleted file mode 100644 index fd76da09d78..00000000000 --- a/mysql-test/r/partition.result +++ /dev/null @@ -1,2663 +0,0 @@ -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 - 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 - 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 - 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 -t1 1 a 1 a A NULL NULL NULL YES BTREE -analyze table t1; -Table Op Msg_type Msg_text -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 -t1 1 a 1 a A 1 NULL NULL YES BTREE -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: Foreign key clause is not yet supported in conjunction with partitioning -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 -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 - 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 - 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 - 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 - 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 - 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 - 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 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 - 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 - 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 - 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 - 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 - 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 - 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 -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 - 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 -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 - 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 - 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 -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 - 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 - 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 - 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 - 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 - 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 - 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 - 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: Cannot create temporary table with partitions -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 - 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 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 - 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 - 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 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 - 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 - 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 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 - 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 range bc bc 10 NULL 7 Using where; Using index for group-by -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 Error in list of partitions to DROP -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; -# -# 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; -# -# End of 10.1 tests -# |