--source include/have_partition.inc --source include/have_innodb.inc # # Bug37721: ORDER BY when WHERE contains non-partitioned index column # wrong order since it did not use pk as second compare --echo # Bug#37721, test of ORDER BY on PK and WHERE on INDEX CREATE TABLE t1 ( a INT, b INT, PRIMARY KEY (a), INDEX (b)) ENGINE InnoDB PARTITION BY HASH(a) PARTITIONS 3; # This will give the middle partition the highest value INSERT INTO t1 VALUES (0,0),(4,0),(2,0); SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC; SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC; ALTER TABLE t1 DROP INDEX b; SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC; SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC; DROP TABLE t1; CREATE TABLE t1 ( a VARCHAR(600), b VARCHAR(600), PRIMARY KEY (a), INDEX (b)) ENGINE InnoDB PARTITION BY KEY(a) PARTITIONS 3; # This will give the middle partition the highest value INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'1'),repeat('0',257)); INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'3'),repeat('0',257)); INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'2'),repeat('0',257)); SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC; SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC; ALTER TABLE t1 DROP INDEX b; SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC; SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC; DROP TABLE t1; # # Bug#32948 - FKs allowed to reference partitioned table # -- echo # Bug#32948 CREATE TABLE t1 (c1 INT, PRIMARY KEY (c1)) ENGINE=INNODB; CREATE TABLE t2 (c1 INT, PRIMARY KEY (c1), FOREIGN KEY (c1) REFERENCES t1 (c1) ON DELETE CASCADE) ENGINE=INNODB; --error ER_ROW_IS_REFERENCED ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5; --error ER_ROW_IS_REFERENCED ALTER TABLE t1 ENGINE=MyISAM; DROP TABLE t2; DROP TABLE t1; # # Bug #14673: Wrong InnoDB default row format # create table t1 (a int) engine=innodb partition by hash(a) ; # Data_free for InnoDB tablespace varies depending on which # tests have been run before this one --replace_column 10 # show table status like 't1'; drop table t1; # # Bug 21173: SHOW TABLE STATUS crashes server in InnoDB # create table t1 (a int) engine = innodb partition by key (a); # Data_free for InnoDB tablespace varies depending on which # tests have been run before this one --replace_column 10 # show table status; insert into t1 values (0), (1), (2), (3); # Data_free for InnoDB tablespace varies depending on which # tests have been run before this one --replace_column 10 # show table status; drop table t1; create table t1 (a int auto_increment primary key) engine = innodb partition by key (a); # Data_free for InnoDB tablespace varies depending on which # tests have been run before this one --replace_column 10 # show table status; insert into t1 values (NULL), (NULL), (NULL), (NULL); # Data_free for InnoDB tablespace varies depending on which # tests have been run before this one --replace_column 10 # show table status; insert into t1 values (NULL), (NULL), (NULL), (NULL); # Data_free for InnoDB tablespace varies depending on which # tests have been run before this one --replace_column 10 # show table status; drop table t1; # # BUG 19122 Crash after ALTER TABLE t1 REBUILD PARTITION p1 # create table t1 (a int) partition by key (a) (partition p1 engine = innodb); alter table t1 rebuild partition p1; alter table t1 rebuild partition p1; alter table t1 rebuild partition p1; alter table t1 rebuild partition p1; alter table t1 rebuild partition p1; alter table t1 rebuild partition p1; alter table t1 rebuild partition p1; drop table t1; # # Bug 21339: Crash in Explain Partitions # create table t1 (a date) engine = innodb partition by range (year(a)) (partition p0 values less than (2006), partition p1 values less than (2007)); explain partitions select * from t1 where a between '2006-01-01' and '2007-06-01'; drop table t1; # # Bug 20397: Partitions: Crash when using non-existing engine # create table t1 (a int) engine = x partition by key (a); show create table t1; drop table t1; create table t1 (a int) engine = innodb partition by list (a) (partition p0 values in (0)); -- error ER_MIX_HANDLER_ERROR alter table t1 engine = x; show create table t1; drop table t1; # BUG#26117: index_merge sort-union over partitioned table crashes create table t1 ( id int unsigned auto_increment, time datetime not null, first_name varchar(40), last_name varchar(50), primary key (id, time), index first_index (first_name), index last_index (last_name) ) engine=Innodb partition by range (to_days(time)) ( partition p1 values less than (to_days('2007-02-07')), partition p2 values less than (to_days('2007-02-08')), partition p3 values less than MAXVALUE ); insert into t1 (time, first_name, last_name) values ('2007-02-07', 'Q', 'Robert'), ('2007-02-07', 'Mark', 'Nate'), ('2007-02-07', 'Nate', 'Oscar'), ('2007-02-07', 'Zack', 'Alice'), ('2007-02-07', 'Jack', 'Kathy'), ('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Brian', 'Charles'), ('2007-02-06', 'Charles', 'David'), ('2007-02-06', 'David', 'Eric'), ('2007-02-07', 'Hector', 'Isaac'), ('2007-02-07', 'Oscar', 'Patricia'), ('2007-02-07', 'Patricia', 'Q'), ('2007-02-07', 'X', 'Yuri'), ('2007-02-07', 'Robert', 'Shawn'), ('2007-02-07', 'Kathy', 'Lois'), ('2007-02-07', 'Eric', 'Francis'), ('2007-02-06', 'Shawn', 'Theron'), ('2007-02-06', 'U', 'Vincent'), ('2007-02-06', 'Francis', 'George'), ('2007-02-06', 'George', 'Hector'), ('2007-02-06', 'Vincent', 'Walter'), ('2007-02-06', 'Walter', 'X'), ('2007-02-07', 'Lois', 'Mark'), ('2007-02-07', 'Yuri', 'Zack'), ('2007-02-07', 'Isaac', 'Jack'), ('2007-02-07', 'Sharon', 'Mark'), ('2007-02-07', 'Michael', 'Michelle'), ('2007-02-07', 'Derick', 'Nathan'), ('2007-02-07', 'Peter', 'Xavier'), ('2007-02-07', 'Fred', 'Harold'), ('2007-02-07', 'Katherine', 'Lisa'), ('2007-02-07', 'Tom', 'Rina'), ('2007-02-07', 'Jerry', 'Victor'), ('2007-02-07', 'Alexander', 'Terry'), ('2007-02-07', 'Justin', 'John'), ('2007-02-07', 'Greg', 'Ernest'), ('2007-02-07', 'Robert', 'Q'), ('2007-02-07', 'Nate', 'Mark'), ('2007-02-07', 'Oscar', 'Nate'), ('2007-02-07', 'Alice', 'Zack'), ('2007-02-07', 'Kathy', 'Jack'), ('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Charles', 'Brian'), ('2007-02-06', 'David', 'Charles'), ('2007-02-06', 'Eric', 'David'), ('2007-02-07', 'Isaac', 'Hector'), ('2007-02-07', 'Patricia', 'Oscar'), ('2007-02-07', 'Q', 'Patricia'), ('2007-02-07', 'Yuri', 'X'), ('2007-02-07', 'Shawn', 'Robert'), ('2007-02-07', 'Lois', 'Kathy'), ('2007-02-07', 'Francis', 'Eric'), ('2007-02-06', 'Theron', 'Shawn'), ('2007-02-06', 'Vincent', 'U'), ('2007-02-06', 'George', 'Francis'), ('2007-02-06', 'Hector', 'George'), ('2007-02-06', 'Walter', 'Vincent'), ('2007-02-06', 'X', 'Walter'), ('2007-02-07', 'Mark', 'Lois'), ('2007-02-07', 'Zack', 'Yuri'), ('2007-02-07', 'Jack', 'Isaac'), ('2007-02-07', 'Mark', 'Sharon'), ('2007-02-07', 'Michelle', 'Michael'), ('2007-02-07', 'Nathan', 'Derick'), ('2007-02-07', 'Xavier', 'Peter'), ('2007-02-07', 'Harold', 'Fred'), ('2007-02-07', 'Lisa', 'Katherine'), ('2007-02-07', 'Rina', 'Tom'), ('2007-02-07', 'Victor', 'Jerry'), ('2007-02-07', 'Terry', 'Alexander'), ('2007-02-07', 'John', 'Justin'), ('2007-02-07', 'Ernest', 'Greg'); SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake'; drop table t1; # # BUG#30583 - Partition on DOUBLE key + INNODB + count(*) == crash # CREATE TABLE t1 (a DOUBLE NOT NULL, KEY(a)) ENGINE=InnoDB PARTITION BY KEY(a) PARTITIONS 10; INSERT INTO t1 VALUES(1),(2); SELECT COUNT(*) FROM t1; DROP TABLE t1; # # Bug #31893 Partitions: crash if subpartitions and engine change # create table t1 (int_column int, char_column char(5)) PARTITION BY RANGE (int_column) subpartition by key (char_column) subpartitions 2 (PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB); alter table t1 ENGINE = MyISAM PARTITION BY RANGE (int_column) subpartition by key (char_column) subpartitions 2 (PARTITION p1 VALUES LESS THAN (5)); show create table t1; drop table t1;