diff options
Diffstat (limited to 'mysql-test/t/partition.test')
| -rw-r--r-- | mysql-test/t/partition.test | 274 |
1 files changed, 260 insertions, 14 deletions
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 0630dd31714..2a8ba15d3f1 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -15,6 +15,98 @@ drop table if exists t1, t2; --enable_warnings --echo # +--echo # Bug#11765667: bug#58655: ASSERTION FAILED, +--echo # SERVER CRASHES WITH MYSQLD GOT SIGNAL 6 +--echo # +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; + +--echo # +--echo # Bug#59297: Can't find record in 'tablename' on update inner join +--echo # + +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; + +--echo # +--echo # Bug#57778: failed primary key add to partitioned innodb table +--echo # inconsistent and crashes +--echo # +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); +--error ER_DUP_ENTRY +ALTER TABLE t1 ADD PRIMARY KEY (a); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +UPDATE t1 SET a = 1, b = 1 WHERE a = 0 AND b = 2; +ALTER TABLE t1 ADD PRIMARY KEY (a); +SELECT * FROM t1; +ALTER TABLE t1 DROP PRIMARY KEY; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # --echo # Bug#57113: ha_partition::extra(ha_extra_function): --echo # Assertion `m_extra_cache' failed CREATE TABLE t1 @@ -63,6 +155,29 @@ 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; + +--echo # Bug#39338: Fieldnames in +--echo # INFORMATIONSCHEMA.PARTITIONS.PARTITION_EXPRESSION become unescaped +--echo # NOTE: the partition expression is saved as a string, so changing from +--echo # normal quotes to ansi quotes does not change the expression, only +--echo # 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'; +show create table t1; +drop table t1; + # # Bug#48276: can't add column if subpartition exists CREATE TABLE t1 (a INT, b INT) @@ -150,6 +265,19 @@ show indexes from t1; drop table t1; # +# Bug#40181: hang if create index +# +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; +--error ER_DUP_KEYNAME +create index i on t1 (a); +create index i2 on t1 (a); +drop table t1; + +# # Bug#36001: Partitions: spelling and using some error messages # --error ER_FOREIGN_KEY_ON_PARTITIONED @@ -173,6 +301,29 @@ SELECT * FROM t1 WHERE pk < 0 ORDER BY pk; DROP TABLE t1; # +# Bug#35765: ALTER TABLE produces wrong error when non-existent storage engine +# used +CREATE TABLE t1 (a INT) +ENGINE=NonExistentEngine; +DROP TABLE t1; +CREATE TABLE t1 (a INT) +ENGINE=NonExistentEngine +PARTITION BY HASH (a); +DROP TABLE t1; +CREATE TABLE t1 (a INT) +ENGINE=Memory; +ALTER TABLE t1 ENGINE=NonExistentEngine; +# OK to only specify one partitions engine, since it is already assigned at +# table level (after create, it is specified on all levels and all parts). +ALTER TABLE t1 +PARTITION BY HASH (a) +(PARTITION p0 ENGINE=Memory, + PARTITION p1 ENGINE=NonExistentEngine); +ALTER TABLE t1 ENGINE=NonExistentEngine; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# # Bug#40494: Crash MYSQL server crashes on range access with partitioning # and order by # @@ -427,12 +578,12 @@ drop table t1; # # BUG 16002: Handle unsigned integer functions properly # ---error ER_PARSE_ERROR +--error ER_VALUES_IS_NOT_INT_TYPE_ERROR create table t1 (a bigint) partition by range (a) (partition p0 values less than (0xFFFFFFFFFFFFFFFF), partition p1 values less than (10)); ---error ER_PARSE_ERROR +--error ER_VALUES_IS_NOT_INT_TYPE_ERROR create table t1 (a bigint) partition by list (a) (partition p0 values in (0xFFFFFFFFFFFFFFFF), @@ -1465,7 +1616,7 @@ PARTITION BY LIST (a) SHOW CREATE TABLE t1; DROP TABLE t1; ---error ER_PARSE_ERROR +--error ER_NULL_IN_VALUES_LESS_THAN CREATE TABLE t1 (a int) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (NULL)); @@ -2118,23 +2269,68 @@ SELECT COUNT(*) FROM t1; DROP TABLE t1; SET SESSION SQL_MODE=DEFAULT; +# This testcase is commented due to the Bug #46853 +# Should be uncommented after fixing Bug #46853 +#--echo # +#--echo # BUG#45816 - assertion failure with index containing double +#--echo # column on partitioned table +#--echo # +# +#CREATE TABLE t1 ( +# a INT DEFAULT NULL, +# b DOUBLE DEFAULT NULL, +# c INT DEFAULT NULL, +# KEY idx2(b,a) +#) 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) FOR UPDATE; +# +#DROP TABLE t1; --echo # ---echo # BUG#45816 - assertion failure with index containing double ---echo # column on partitioned table +--echo # Bug#46198: Hang after failed ALTER TABLE on partitioned table. --echo # -CREATE TABLE t1 ( - a INT DEFAULT NULL, - b DOUBLE DEFAULT NULL, - c INT DEFAULT NULL, - KEY idx2(b,a) -) PARTITION BY HASH(c) PARTITIONS 3; +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings -INSERT INTO t1 VALUES (6,8,9); -INSERT INTO t1 VALUES (6,8,10); +# +# Case 1. +# + +CREATE TABLE t1 (s1 INT PRIMARY KEY) PARTITION BY HASH(s1); -SELECT 1 FROM t1 JOIN t1 AS t2 USING (a) FOR UPDATE; +LOCK TABLES t1 WRITE, t1 b READ; + +UNLOCK TABLES; + +--error ER_ONLY_ON_RANGE_LIST_PARTITION +ALTER TABLE t1 DROP PARTITION p1; + +# The SELECT below used to hang in tdc_wait_for_old_versions(). +SELECT * FROM t1; + +DROP TABLE t1; + +# +# Case 2. +# + +CREATE TABLE t1 (s1 VARCHAR(5) PRIMARY KEY) PARTITION BY KEY(s1); + +LOCK TABLES t1 WRITE, t1 b READ; + +UNLOCK TABLES; + +--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF +ALTER TABLE t1 ADD COLUMN (s3 VARCHAR(5) UNIQUE); + +# The SELECT below used to hang in tdc_wait_for_old_versions(). +SELECT * FROM t1; DROP TABLE t1; @@ -2151,3 +2347,53 @@ DROP TABLE t1; SET GLOBAL myisam_use_mmap=default; --echo End of 5.1 tests + +--echo # +--echo # BUG#55385: UPDATE statement throws an error, but still updates +--echo # 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; + +--echo +--echo # Should be (1,1),(10,10) +SELECT * FROM t1_part; + +--echo +--echo # Case 1 +--echo # Update is refused because partitioning key is updated +--error ER_MULTI_UPDATE_KEY_CONFLICT +UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.partkey = 2, B.nokey = 3; +--error ER_MULTI_UPDATE_KEY_CONFLICT +UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2, B.partkey = 3; + +--echo +--echo # Case 2 +--echo # Like 1, but partition accessed through a view +--error ER_MULTI_UPDATE_KEY_CONFLICT +UPDATE t1_part AS A NATURAL JOIN v1 as B SET A.nokey = 2 , B.partkey = 3; +--error ER_MULTI_UPDATE_KEY_CONFLICT +UPDATE v1 AS A NATURAL JOIN t1_part as B SET A.nokey = 2 , B.partkey = 3; + +--echo +--echo # Should be (1,1),(10,10) +SELECT * FROM t1_part; + +--echo +--echo # Case 3 +--echo # 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; + +--echo +--echo # Should be (1,3),(10,3) +SELECT * FROM t1_part; + +--echo +# Cleanup +DROP VIEW v1; +DROP TABLE t1_part; |