diff options
Diffstat (limited to 'mysql-test/main/partition_innodb.result')
| -rw-r--r-- | mysql-test/main/partition_innodb.result | 1092 |
1 files changed, 1092 insertions, 0 deletions
diff --git a/mysql-test/main/partition_innodb.result b/mysql-test/main/partition_innodb.result new file mode 100644 index 00000000..95ca989e --- /dev/null +++ b/mysql-test/main/partition_innodb.result @@ -0,0 +1,1092 @@ +call mtr.add_suppression("Deadlock found when trying to get lock; try restarting transaction"); +set global default_storage_engine='innodb'; +set session default_storage_engine='innodb'; +set @innodb_stats_persistent_save= @@innodb_stats_persistent; +set @innodb_stats_persistent_sample_pages_save= +@@innodb_stats_persistent_sample_pages; +set global innodb_stats_persistent= 1; +set global innodb_stats_persistent_sample_pages=100; +drop table if exists t1, t2; +# +# Bug#13694811: THE OPTIMIZER WRONGLY USES THE FIRST +# INNODB PARTITION STATISTICS +# +CREATE TABLE t1 +(a INT, +b varchar(64), +PRIMARY KEY (a), +KEY (b)) +ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) SUBPARTITIONS 10 +(PARTITION pNeg VALUES LESS THAN (0), +PARTITION p0 VALUES LESS THAN (1000), +PARTITION pMAX VALUES LESS THAN MAXVALUE); +# Only one row in the first 10 subpartitions +INSERT INTO t1 VALUES (-1, 'Only negative pk value'); +INSERT INTO t1 VALUES (0, 'Mod Zero'), (1, 'One'), (2, 'Two'), (3, 'Three'), +(10, 'Zero'), (11, 'Mod One'), (12, 'Mod Two'), (13, 'Mod Three'), +(20, '0'), (21, '1'), (22, '2'), (23, '3'), +(4, '4'), (5, '5'), (6, '6'), (7, '7'), (8, '8'), (9, '9'); +INSERT INTO t1 SELECT a + 30, b FROM t1 WHERE a >= 0; +INSERT INTO t1 SELECT a + 60, b FROM t1 WHERE a >= 0; +INSERT INTO t1 SELECT a + 120, b FROM t1 WHERE a >= 0; +INSERT INTO t1 SELECT a + 240, b FROM t1 WHERE a >= 0; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +EXPLAIN SELECT b FROM t1 WHERE b between 'L' and 'N' AND a > -100; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b b 67 NULL 90 Using where; Using index +DROP TABLE t1; +# +# Bug#13007154: Crash in keys_to_use_for_scanning with ORDER BY +# and PARTITIONING +# +CREATE TABLE t1 (a INT, KEY(a)) +ENGINE = InnoDB +PARTITION BY KEY (a) PARTITIONS 1; +SELECT 1 FROM t1 WHERE a > (SELECT LAST_INSERT_ID() FROM t1 LIMIT 0) +ORDER BY a; +1 +DROP TABLE t1; +# +# Bug#56287: crash when using Partition datetime in sub in query +# +CREATE TABLE t1 +(c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT, +c2 varchar(40) not null default '', +c3 datetime not NULL, +PRIMARY KEY (c1,c3), +KEY partidx(c3)) +ENGINE=InnoDB +PARTITION BY RANGE (TO_DAYS(c3)) +(PARTITION p200912 VALUES LESS THAN (to_days('2010-01-01')), +PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')), +PARTITION p201912 VALUES LESS THAN MAXVALUE); +insert into t1(c2,c3) values ("Test row",'2010-01-01 00:00:00'); +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test'; +PARTITION_NAME TABLE_ROWS +p200912 0 +p201103 1 +p201912 0 +SELECT count(*) FROM t1 p where c3 in +(select c3 from t1 t where t.c3 < timestamp '2011-04-26 19:19:44' + and t.c3 > timestamp '2011-04-26 19:18:44') ; +count(*) +0 +DROP TABLE t1; +# +# Bug#54747: Deadlock between REORGANIZE PARTITION and +# SELECT is not detected +# +CREATE TABLE t1 +(user_num BIGINT, +hours SMALLINT, +KEY user_num (user_num)) +ENGINE = InnoDB +PARTITION BY RANGE COLUMNS (hours) +(PARTITION hour_003 VALUES LESS THAN (3), +PARTITION hour_004 VALUES LESS THAN (4), +PARTITION hour_005 VALUES LESS THAN (5), +PARTITION hour_last VALUES LESS THAN (MAXVALUE)); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +BEGIN; +SELECT COUNT(*) FROM t1; +COUNT(*) +5 +connect con1,localhost,root,,; +# SEND a ALTER PARTITION which waits on the ongoing transaction. +ALTER TABLE t1 +REORGANIZE PARTITION hour_003, hour_004 INTO +(PARTITION oldest VALUES LESS THAN (4)); +# Connection default wait until the ALTER is in 'waiting for table...' +# state and then continue the transaction by trying a SELECT +connection default; +SELECT COUNT(*) FROM t1; +COUNT(*) +5 +COMMIT; +# reaping ALTER. +connection con1; +# Cleaning up. +disconnect con1; +connection default; +DROP TABLE t1; +# +# Bug#50418: DROP PARTITION does not interact with transactions +# +CREATE TABLE t1 ( +id INT AUTO_INCREMENT NOT NULL, +name CHAR(50) NOT NULL, +myDate DATE NOT NULL, +PRIMARY KEY (id, myDate), +INDEX idx_date (myDate) +) ENGINE=InnoDB +PARTITION BY RANGE ( TO_DAYS(myDate) ) ( +PARTITION p0 VALUES LESS THAN (734028), +PARTITION p1 VALUES LESS THAN (734029), +PARTITION p2 VALUES LESS THAN (734030), +PARTITION p3 VALUES LESS THAN MAXVALUE +) ; +INSERT INTO t1 VALUES +(NULL, 'Lachlan', '2009-09-13'), +(NULL, 'Clint', '2009-09-13'), +(NULL, 'John', '2009-09-14'), +(NULL, 'Dave', '2009-09-14'), +(NULL, 'Jeremy', '2009-09-15'), +(NULL, 'Scott', '2009-09-15'), +(NULL, 'Jeff', '2009-09-16'), +(NULL, 'Joe', '2009-09-16'); +SET AUTOCOMMIT=0; +SELECT * FROM t1 FOR UPDATE; +id name myDate +1 Lachlan 2009-09-13 +2 Clint 2009-09-13 +3 John 2009-09-14 +4 Dave 2009-09-14 +5 Jeremy 2009-09-15 +6 Scott 2009-09-15 +7 Jeff 2009-09-16 +8 Joe 2009-09-16 +UPDATE t1 SET name = 'Mattias' WHERE id = 7; +SELECT * FROM t1 WHERE id = 7; +id name myDate +7 Mattias 2009-09-16 +connect con1, localhost, root,,; +SET lock_wait_timeout = 1; +# After the patch it will wait and fail on timeout. +ALTER TABLE t1 DROP PARTITION p3; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SHOW WARNINGS; +Level Code Message +Error 1205 Lock wait timeout exceeded; try restarting transaction +disconnect con1; +connection default; +SELECT * FROM t1; +id name myDate +1 Lachlan 2009-09-13 +2 Clint 2009-09-13 +3 John 2009-09-14 +4 Dave 2009-09-14 +5 Jeremy 2009-09-15 +6 Scott 2009-09-15 +7 Mattias 2009-09-16 +8 Joe 2009-09-16 +# No changes. +COMMIT; +DROP TABLE t1; +# +# Bug#51830: Incorrect partition pruning on range partition (regression) +# +CREATE TABLE t1 (a INT NOT NULL) +ENGINE = InnoDB +PARTITION BY RANGE(a) +(PARTITION p10 VALUES LESS THAN (10), +PARTITION p30 VALUES LESS THAN (30), +PARTITION p50 VALUES LESS THAN (50), +PARTITION p70 VALUES LESS THAN (70), +PARTITION p90 VALUES LESS THAN (90)); +INSERT INTO t1 VALUES (10),(30),(50); +INSERT INTO t1 VALUES (70); +INSERT INTO t1 VALUES (80); +INSERT INTO t1 VALUES (89); +INSERT INTO t1 VALUES (90); +ERROR HY000: Table has no partition for value 90 +INSERT INTO t1 VALUES (100); +ERROR HY000: Table has no partition for value 100 +insert INTO t1 VALUES (110); +ERROR HY000: Table has no partition for value 110 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +DROP TABLE t1; +# +# Bug#50104: Partitioned table with just 1 partion works with fk +# +CREATE TABLE t2 ( +id INT, +PRIMARY KEY (id) +) ENGINE=InnoDB ; +CREATE TABLE t1 ( +id INT NOT NULL AUTO_INCREMENT, +parent_id INT DEFAULT NULL, +PRIMARY KEY (id), +KEY parent_id (parent_id) +) ENGINE=InnoDB; +ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 1; +ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id); +ERROR HY000: Partitioned tables do not support FOREIGN KEY +ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 2; +ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id); +ERROR HY000: Partitioned tables do not support FOREIGN KEY +DROP TABLE t1, t2; +create table t1 (a varchar(5), b int signed, c varchar(10), d datetime) +partition by range columns(b,c) +subpartition by hash(to_seconds(d)) +( partition p0 values less than (2, 'b'), +partition p1 values less than (4, 'd'), +partition p2 values less than (10, 'za')); +insert into t1 values ('a', 3, 'w', '2001-10-27 04:34:00'); +insert into t1 values ('r', 7, 'w', '2001-10-27 05:34:00'); +insert into t1 values ('g', 10, 'w', '2001-10-27 06:34:00'); +update t1 set a = 'c' where a > 'f'; +drop table t1; +create table t1 (a varchar(5)) +engine=memory +partition by range columns(a) +( partition p0 values less than ('m'), +partition p1 values less than ('za')); +insert into t1 values ('j'); +update t1 set a = 'z' where (a >= 'j'); +drop table t1; +create table t1 (a varchar(5)) +engine=myisam +partition by range columns(a) +( partition p0 values less than ('m'), +partition p1 values less than ('za')); +insert into t1 values ('j'); +update t1 set a = 'z' where (a >= 'j'); +drop table t1; +create table t1 (a varchar(5)) +engine=innodb +partition by range columns(a) +( partition p0 values less than ('m'), +partition p1 values less than ('za')); +insert into t1 values ('j'); +update t1 set a = 'z' where (a >= 'j'); +drop table t1; +create table t1 (a int not null, +b datetime not null, +primary key (a,b)) +engine=innodb +partition by range (to_days(b)) +subpartition by hash (a) +subpartitions 2 +( partition p0 values less than (to_days('2009-01-01')), +partition p1 values less than (to_days('2009-02-01')), +partition p2 values less than (to_days('2009-03-01')), +partition p3 values less than maxvalue); +alter table t1 reorganize partition p1,p2 into +( partition p2 values less than (to_days('2009-03-01'))); +drop table t1; +CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB +PARTITION BY RANGE(id) ( +PARTITION p0 VALUES LESS THAN (5), +PARTITION p1 VALUES LESS THAN (10), +PARTITION p2 VALUES LESS THAN MAXVALUE +); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), +(9,9), (10,10), (11,11); +SET @old_tx_isolation := @@session.tx_isolation; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +SET autocommit = 0; +UPDATE t1 SET DATA = data*2 WHERE id = 3; +UPDATE t1 SET data = data*2 WHERE data = 2; +SET @@session.tx_isolation = @old_tx_isolation; +DROP TABLE t1; +# 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; +INSERT INTO t1 VALUES (0,0),(4,0),(2,0); +SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC; +a +0 +2 +4 +SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC; +a +4 +2 +0 +ALTER TABLE t1 DROP INDEX b; +SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC; +a +0 +2 +4 +SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC; +a +4 +2 +0 +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; +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; +right(a,1) +1 +2 +3 +SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC; +right(a,1) +3 +2 +1 +ALTER TABLE t1 DROP INDEX b; +SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC; +right(a,1) +1 +2 +3 +SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC; +right(a,1) +3 +2 +1 +DROP TABLE t1; +# 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; +ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +ALTER TABLE t1 ENGINE=MyISAM; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +DROP TABLE t2; +DROP TABLE t1; +create table t1 (a int) engine=innodb partition by hash(a) ; +show table status like 't1'; +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 InnoDB 10 Dynamic 2 8192 16384 0 0 0 NULL # NULL NULL latin1_swedish_ci NULL partitioned 0 N +drop table t1; +create table t1 (a int) +engine = innodb +partition by key (a); +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 InnoDB 10 Dynamic 2 8192 16384 0 0 0 NULL Create_time NULL NULL latin1_swedish_ci NULL partitioned 0 N +insert into t1 values (0), (1), (2), (3); +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 InnoDB 10 Dynamic 4 4096 16384 0 0 0 NULL Create_time Update_time NULL latin1_swedish_ci NULL partitioned 0 N +drop table t1; +create table t1 (a int auto_increment primary key) +engine = innodb +partition by key (a); +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 InnoDB 10 Dynamic 2 8192 16384 0 0 0 1 Create_time NULL NULL latin1_swedish_ci NULL partitioned 0 N +insert into t1 values (NULL), (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 InnoDB 10 Dynamic 4 4096 16384 0 0 0 5 Create_time Update_time NULL latin1_swedish_ci NULL partitioned 0 N +insert into t1 values (NULL), (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 InnoDB 10 Dynamic 8 2048 16384 0 0 0 9 Create_time Update_time NULL latin1_swedish_ci NULL partitioned 0 N +drop table t1; +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; +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'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where +drop table t1; +SET SQL_MODE=""; +create table t1 (a int) +engine = x +partition by key (a); +Warnings: +Warning 1286 Unknown storage engine 'x' +Warning 1266 Using storage engine InnoDB for table 't1' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`a`) +drop table t1; +create table t1 (a int) +engine = innodb +partition by list (a) +(partition p0 values in (0)); +alter table t1 engine = x; +Warnings: +Warning 1286 Unknown storage engine 'x' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY LIST (`a`) +(PARTITION `p0` VALUES IN (0) ENGINE = InnoDB) +drop table t1; +SET SQL_MODE=default; +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'; +id time first_name last_name +drop table t1; +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; +COUNT(*) +2 +DROP TABLE t1; +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `int_column` int(11) DEFAULT NULL, + `char_column` char(5) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY RANGE (`int_column`) +SUBPARTITION BY KEY (`char_column`) +SUBPARTITIONS 2 +(PARTITION `p1` VALUES LESS THAN (5) ENGINE = MyISAM) +drop table t1; +CREATE TABLE t1 (a INT) ENGINE=InnoDB +PARTITION BY list(a) (PARTITION p1 VALUES IN (1)); +CREATE INDEX i1 ON t1 (a); +DROP TABLE t1; +# +# Bug#54783: optimize table crashes with invalid timestamp default value and NO_ZERO_DATE +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a INT, b TIMESTAMP DEFAULT '0000-00-00 00:00:00') +ENGINE=INNODB PARTITION BY LINEAR HASH (a) PARTITIONS 1; +SET @old_mode = @@sql_mode; +SET SESSION sql_mode = 'NO_ZERO_DATE'; +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize, doing recreate + analyze instead +test.t1 optimize error Invalid default value for 'b' +test.t1 optimize status Operation failed +Warnings: +Warning 1265 Data truncated for column 'b' at row 1 +Error 1067 Invalid default value for 'b' +SET SESSION sql_mode = @old_mode; +DROP TABLE t1; +# +# Bug#57985 "ONLINE/FAST ALTER PARTITION can fail and leave the +# table unusable". +# +CREATE TABLE t1 (a bigint not null, b int not null, PRIMARY KEY (a)) +ENGINE = InnoDB PARTITION BY KEY(a) PARTITIONS 2; +INSERT INTO t1 values (0,1), (1,2); +# The below ALTER should fail. It should leave the +# table in its original, non-corrupted, usable state. +ALTER TABLE t1 ADD UNIQUE KEY (b); +ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function +# The below statements should succeed, as ALTER should +# have left table intact. +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) NOT NULL, + `b` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`a`) +PARTITIONS 2 +SELECT * FROM t1; +a b +1 2 +0 1 +DROP TABLE t1; +# +# Bug#57985 "ONLINE/FAST ALTER PARTITION can fail and leave the +# table unusable". +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a bigint not null, b int not null, PRIMARY KEY (a)) +ENGINE = InnoDB PARTITION BY KEY(a) PARTITIONS 2; +INSERT INTO t1 values (0,1), (1,2); +# The below ALTER should fail. It should leave the +# table in its original, non-corrupted, usable state. +ALTER TABLE t1 ADD UNIQUE KEY (b); +ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function +# The below statements should succeed, as ALTER should +# have left table intact. +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) NOT NULL, + `b` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`a`) +PARTITIONS 2 +SELECT * FROM t1; +a b +1 2 +0 1 +DROP TABLE t1; +# +# Bug #17299181 CREATE_TIME AND UPDATE_TIME ARE +# WRONG FOR PARTITIONED TABLES +# +CREATE TABLE t1 (a int, PRIMARY KEY (a)) ENGINE=InnoDB +PARTITION BY HASH (a) PARTITIONS 2; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE +CREATE_TIME IS NOT NULL AND TABLE_NAME='t1'; +COUNT(*) +1 +DROP TABLE t1; +# +# BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == +# SAVE_READ_SET +# +CREATE TABLE t1 ( +a INT, +b INT, +c INT, +PRIMARY KEY (c,a), KEY (a),KEY (a) +) ENGINE=INNODB PARTITION BY KEY () PARTITIONS 2; +Warnings: +Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release +INSERT INTO t1 VALUES (1,5,1),(2,4,1),(3,3,1),(4,2,1),(5,1,1); +UPDATE t1 SET b = 0, c=1 WHERE a <=>0; +SELECT * FROM t1; +a b c +1 5 1 +2 4 1 +3 3 1 +4 2 1 +5 1 1 +DROP TABLE t1; +# +# MDEV-5102 : MySQL Bug 69851 +# +CREATE TABLE t1 ( +`col1` bigint(20) unsigned NOT NULL , +`col2` bigint(20) unsigned NOT NULL , +`col3` datetime NOT NULL , +PRIMARY KEY (`col3`), +KEY (`col1`), +KEY (`col2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +PARTITION BY RANGE (TO_DAYS(col3)) +( +PARTITION p_20130310 VALUES LESS THAN (735303) ENGINE = InnoDB, +PARTITION p_20130311 VALUES LESS THAN (735304) ENGINE = InnoDB, +PARTITION p_20130312 VALUES LESS THAN (735305) ENGINE = InnoDB +); +INSERT INTO `t1` VALUES (2,96,'2013-03-08 16:28:05'); +INSERT INTO `t1` VALUES (1,2,'2013-03-08 16:47:39'); +INSERT INTO `t1` VALUES (1,2,'2013-03-08 16:50:27'); +INSERT INTO `t1` VALUES (1,2,'2013-03-11 16:33:04'); +INSERT INTO `t1` VALUES (1,2,'2013-03-11 16:33:24'); +INSERT INTO `t1` VALUES (2,2,'2013-03-12 10:11:48'); +SELECT * FROM t1 WHERE col1 = 1 AND col2 = 2 +AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00' +GROUP BY 1, 2, 3; +col1 col2 col3 +1 2 2013-03-08 16:47:39 +1 2 2013-03-08 16:50:27 +1 2 2013-03-11 16:33:04 +1 2 2013-03-11 16:33:24 +EXPLAIN SELECT * FROM t1 WHERE col1 = 1 AND col2 = 2 +AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00' +GROUP BY 1, 2, 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,col1,col2 PRIMARY 5 NULL # Using where; Using filesort +SELECT * FROM t1 USE INDEX () WHERE col1 = 1 AND col2 = 2 +AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00' +GROUP BY 1, 2, 3; +col1 col2 col3 +1 2 2013-03-08 16:47:39 +1 2 2013-03-08 16:50:27 +1 2 2013-03-11 16:33:04 +1 2 2013-03-11 16:33:24 +DROP TABLE t1; +# +# MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703) +# +create table t1 ( +a int not null, +b int not null, +pk int not null, +primary key (pk), +key(a), +key(b) +) engine=innodb partition by hash(pk) partitions 10; +insert into t1 values (1,2,4); +insert into t1 values (1,0,17); +insert into t1 values (1,2,25); +insert into t1 values (10,20,122); +insert into t1 values (10,20,123); +create table t2 (a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t1 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t2 A, t2 B, t2 C; +insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a, +10+A.a + 10*B.a + 100*C.a + 1000*D.a, +2000 + A.a + 10*B.a + 100*C.a + 1000*D.a +from t2 A, t2 B, t2 C ,t2 D; +set statement optimizer_switch='rowid_filter=off' for +explain select * from t1 where a=1 and b=2 and pk between 1 and 999999 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge PRIMARY,a,b b,a 4,4 NULL # Using intersect(b,a); Using where; Using index +set statement optimizer_switch='rowid_filter=off' for +create temporary table t3 as +select * from t1 where a=1 and b=2 and pk between 1 and 999 ; +select count(*) from t3; +count(*) +802 +drop table t3; +create temporary table t3 as +select * from t1 ignore index(a,b) where a=1 and b=2 and pk between 1 and 999 ; +select count(*) from t3; +count(*) +802 +drop table t3; +drop table t1,t2; +# +# MySQL Bug#71095: Wrong results with PARTITION BY LIST COLUMNS() +# +create table t1(c1 int, c2 int, c3 int, c4 int, +primary key(c1,c2)) engine=InnoDB +partition by list columns(c2) +(partition p1 values in (1,2) engine=InnoDB, +partition p2 values in (3,4) engine=InnoDB); +insert into t1 values (1,1,1,1),(2,3,1,1); +select * from t1 where c1=2 and c2=3; +c1 c2 c3 c4 +2 3 1 1 +drop table t1; +# +# MySQL Bug#72803: Wrong "Impossible where" with LIST partitioning +# also MDEV-6240: Wrong "Impossible where" with LIST partitioning +# +CREATE TABLE t1 ( d DATE) ENGINE = InnoDB +PARTITION BY LIST COLUMNS (d) +( +PARTITION p0 VALUES IN ('1990-01-01','1991-01-01'), +PARTITION p1 VALUES IN ('1981-01-01') +); +INSERT INTO t1 (d) VALUES ('1991-01-01'); +SELECT * FROM t1 WHERE d = '1991-01-01'; +d +1991-01-01 +DROP TABLE t1; +set global default_storage_engine=default; +# +# MDEV-9455: [ERROR] mysqld got signal 11 +# +CREATE TABLE `t1` ( +`DIARY_TOTAL_DAY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT, +`IMORY_ID` bigint(20) NOT NULL, +`NAME` varchar(75) DEFAULT NULL, +`DATETIME` varchar(10) NOT NULL DEFAULT '', +`DAILY_CALL_CNT` int(11) DEFAULT NULL, +`DAILY_SMS_CNT` int(11) DEFAULT NULL, +`NUMBER` varchar(64) DEFAULT NULL, +`DURATION` varchar(16) DEFAULT NULL, +PRIMARY KEY (`DIARY_TOTAL_DAY_SEQ`,`DATETIME`), +KEY `IDX_t1_01` (`IMORY_ID`,`DATETIME`) +) AUTO_INCREMENT=328702514 DEFAULT CHARSET=utf8mb4 +PARTITION BY RANGE COLUMNS(`DATETIME`) +(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB, +PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB, +PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB, +PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB, +PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB, +PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB, +PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB, +PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB, +PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB, +PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB, +PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB) +; +CREATE TABLE `t2` ( +`DIARY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT, +`IMORY_ID` bigint(20) NOT NULL, +`CALL_TYPE` varchar(1) DEFAULT NULL, +`DATA_TYPE` varchar(1) DEFAULT NULL, +`FEATURES` varchar(1) DEFAULT NULL, +`NAME` varchar(75) DEFAULT NULL, +`NUMBER` varchar(64) DEFAULT NULL, +`DATETIME` datetime NOT NULL, +`REG_DATE` datetime NOT NULL, +`TITLE` varchar(50) DEFAULT NULL, +`BODY` varchar(4200) DEFAULT NULL, +`MIME_TYPE` varchar(32) DEFAULT NULL, +`DURATION` varchar(16) DEFAULT NULL, +`DEVICE_ID` varchar(64) DEFAULT NULL, +`DEVICE_NAME` varchar(32) DEFAULT NULL, +PRIMARY KEY (`DIARY_SEQ`,`DATETIME`,`REG_DATE`), +KEY `IDX_TB_DIARY_01` (`IMORY_ID`,`DATETIME`,`CALL_TYPE`,`NUMBER`), +KEY `IDX_TB_DIARY_02` (`REG_DATE`) +) AUTO_INCREMENT=688799006 DEFAULT CHARSET=utf8mb4 +PARTITION BY RANGE COLUMNS(REG_DATE) +(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB, +PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB, +PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB, +PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB, +PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB, +PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB, +PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB, +PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB, +PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB, +PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB, +PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB) +; +SELECT +A.IMORY_ID, +A.NUMBER, +A.NAME, +DATE_FORMAT(A.DATETIME, '%Y-%m-%d') AS TARGET_DATE, +SUM( CASE WHEN A.DATA_TYPE='1' THEN 1 ELSE 0 END) AS CALL_CNT, +SUM( CASE WHEN A.DATA_TYPE IN ('2', '3') THEN 1 ELSE 0 END) AS SMS_CNT, +SUM(CAST(A.DURATION AS INT)) AS DURATION, +( SELECT COUNT(*) +FROM t1 +WHERE IMORY_ID=A.IMORY_ID +AND NUMBER=A.NUMBER +AND NAME=A.NAME +AND DATETIME = DATE_FORMAT(A.DATETIME, '%Y-%m-%d') +) STATS_COUNT +FROM t2 A +WHERE A.IMORY_ID = 55094102 +AND A.DATETIME LIKE ( +SELECT CONCAT (DATE_FORMAT(DATETIME, '%Y-%m-%d') ,'%') +FROM t2 +WHERE IMORY_ID=55094102 +AND DIARY_SEQ IN ( 608351221, 608351225, 608351229 ) +group by DATE_FORMAT(DATETIME, '%Y-%m-%d') +) +GROUP BY A.IMORY_ID, A.NUMBER, A.NAME, DATE_FORMAT(A.DATETIME, '%Y-%m-%d') +; +IMORY_ID NUMBER NAME TARGET_DATE CALL_CNT SMS_CNT DURATION STATS_COUNT +drop table t2, t1; +set global default_storage_engine='innodb'; +# +# MDEV-5963: InnoDB: Assertion failure in file row0sel.cc line 2503, +# Failing assertion: 0 with "key ptr now exceeds key end by 762 bytes" +# (independent testcase for Oracle Bug#13947868) +# +CREATE TABLE t1 (f1 VARCHAR(512) CHARACTER SET utf8) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('j'); +CREATE TABLE t2 ( +f2 VARCHAR(5) CHARACTER SET latin1, +f3 VARCHAR(5) CHARACTER SET utf8, +f4 INT, +f5 VARCHAR(512) CHARACTER SET utf8, +f6 VARCHAR(256) CHARACTER SET utf8, +key (f2), +key (f3), +key (f5) +) ENGINE=InnoDB PARTITION BY LIST COLUMNS (f4) +SUBPARTITION BY KEY(f6) SUBPARTITIONS 4 ( +PARTITION p0 VALUES IN (1,3,9,null), +PARTITION p1 VALUES IN (2,4,0) +); +INSERT INTO t2 VALUES +('k','s',3,'b','j'),('a','b',NULL,'v','j'),('c','m',9,'t',NULL), +('b','l',9,'b',NULL),('i','y',3,'o','w'),('c','m',NULL,'a','m'), +('f','o',9,'m','w'),('f','q',NULL,'o','a'); +CREATE TABLE t3 LIKE t2; +SELECT * FROM t1 INNER JOIN t2 ON ( f5 = f1 ); +f1 f2 f3 f4 f5 f6 +INSERT INTO t3 SELECT * FROM t2 WHERE f3 = 'm' AND f2 ='c'; +DROP TABLE t1,t2,t3; +set global default_storage_engine=default; +# +# Bug#13737949: CRASH IN HA_PARTITION::INDEX_INIT +# Bug#18694052: SERVER CRASH IN HA_PARTITION::INIT_RECORD_PRIORITY_QUEUE +# +CREATE TABLE t1 +(a INT, +b INT, +PRIMARY KEY (a)) +ENGINE = InnoDB +PARTITION BY HASH (a) PARTITIONS 3; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connect con1, localhost, root,,; +ALTER TABLE t1 ADD INDEX idx1 (b); +connection default; +SELECT b FROM t1 WHERE b = 0; +ERROR HY000: Table definition has changed, please retry transaction +SELECT b FROM t1 WHERE b = 0; +ERROR HY000: Table definition has changed, please retry transaction +disconnect con1; +DROP TABLE t1; +# Same test without partitioning +CREATE TABLE t1 +(a INT, +b INT, +PRIMARY KEY (a)) +ENGINE = InnoDB; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connect con1, localhost, root,,; +ALTER TABLE t1 ADD INDEX idx1 (b); +connection default; +SELECT b FROM t1 WHERE b = 0; +ERROR HY000: Table definition has changed, please retry transaction +SELECT b FROM t1 WHERE b = 0; +ERROR HY000: Table definition has changed, please retry transaction +disconnect con1; +DROP TABLE t1; +# +# MDEV-11167: InnoDB: Warning: using a partial-field key prefix +# in search, results in assertion failure or "Can't find record" error +# +set @save_sql_mode = @@sql_mode; +set sql_mode=""; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +CREATE TABLE t2 (b INT, c INT, KEY(b)) ENGINE=InnoDB PARTITION BY HASH(c) PARTITIONS 2; +CREATE ALGORITHM = MERGE VIEW v AS SELECT a, b FROM t1 STRAIGHT_JOIN t2 WHERE b = 'foo' WITH CHECK OPTION; +INSERT INTO t1 VALUES (1),(2); +INSERT IGNORE INTO t2 VALUES (2,2),('three',3),(4,4); +Warnings: +Warning 1366 Incorrect integer value: 'three' for column `test`.`t2`.`b` at row 2 +UPDATE v SET a = NULL; +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: 'foo' +DROP view v; +DROP TABLE t1, t2; +SET @save_isp=@@innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent= ON; +CREATE TABLE t (f1 INT, f2 INT, KEY(f2)) ENGINE=InnoDB PARTITION BY HASH (f1) PARTITIONS 2; +INSERT IGNORE INTO t VALUES (NULL,0),(NULL,0),(0,21),(4,0),(1,8),(5,66); +CREATE ALGORITHM=MERGE VIEW v AS SELECT t1.* FROM t t1 JOIN t t2 WHERE t1.f1 < t2.f2 WITH LOCAL CHECK OPTION; +UPDATE v SET f2 = NULL; +ERROR 44000: CHECK OPTION failed `test`.`v` +SET GLOBAL innodb_stats_persistent= @save_isp; +DROP view v; +DROP TABLE t; +set sql_mode= @save_sql_mode; +# +# Bug#28573894 ALTER PARTITIONED TABLE ADD AUTO_INCREMENT DIFF RESULT +# +CREATE TABLE t (a VARCHAR(10) NOT NULL,b INT,PRIMARY KEY (b)) ENGINE=INNODB +PARTITION BY RANGE (b) +(PARTITION pa VALUES LESS THAN (2), +PARTITION pb VALUES LESS THAN (20), +PARTITION pc VALUES LESS THAN (30), +PARTITION pd VALUES LESS THAN (40)); +INSERT INTO t +VALUES('A',0),('B',1),('C',2),('D',3),('E',4),('F',5),('G',25),('H',35); +CREATE TABLE t_copy LIKE t; +INSERT INTO t_copy SELECT * FROM t; +ALTER TABLE t ADD COLUMN r INT UNSIGNED NOT NULL AUTO_INCREMENT, +ADD UNIQUE KEY (r,b); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t_copy ADD COLUMN r INT UNSIGNED NOT NULL AUTO_INCREMENT, +ADD UNIQUE KEY (r,b), ALGORITHM=COPY; +affected rows: 8 +info: Records: 8 Duplicates: 0 Warnings: 0 +SELECT * FROM t; +a b r +A 0 1 +B 1 2 +C 2 3 +D 3 4 +E 4 5 +F 5 6 +G 25 7 +H 35 8 +SELECT * FROM t_copy; +a b r +A 0 1 +B 1 2 +C 2 3 +D 3 4 +E 4 5 +F 5 6 +G 25 7 +H 35 8 +DROP TABLE t,t_copy; +# +# Bug#26390658 RENAMING A PARTITIONED TABLE DOES NOT UPDATE +# MYSQL.INNODB_TABLE_STATS +# +CREATE DATABASE test_jfg; +CREATE TABLE test_jfg.test_jfg1 (id int(10) unsigned NOT NULL,PRIMARY +KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=1; +CREATE TABLE test_jfg.test_jfg2 (id int(10) unsigned NOT NULL,PRIMARY +KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=1 +PARTITION BY RANGE ( id ) (PARTITION p1000 VALUES LESS THAN (1000) +ENGINE = InnoDB,PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = +InnoDB); +SELECT database_name, table_name FROM mysql.innodb_table_stats WHERE +database_name = 'test_jfg'; +database_name table_name +test_jfg test_jfg1 +test_jfg test_jfg2#P#p1000 +test_jfg test_jfg2#P#pmax +RENAME TABLE test_jfg.test_jfg1 TO test_jfg.test_jfg11; +RENAME TABLE test_jfg.test_jfg2 TO test_jfg.test_jfg12; +SELECT database_name, table_name FROM mysql.innodb_table_stats WHERE +database_name = 'test_jfg'; +database_name table_name +test_jfg test_jfg11 +test_jfg test_jfg12#P#p1000 +test_jfg test_jfg12#P#pmax +DROP DATABASE test_jfg; +set global innodb_stats_persistent= @innodb_stats_persistent_save; +set global innodb_stats_persistent_sample_pages= +@innodb_stats_persistent_sample_pages_save; +create table t1 (a int) engine=innodb; +create table t2 ( +b int, +c int, +d bit not null default 0, +v bit as (d) virtual, +key (b,v) +) engine=innodb partition by hash (b); +insert into t1 values (1),(2); +insert into t2 (b,c,d) values (1,1,0),(2,2,0); +explain select t1.* from t1 join t2 on (v = a); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 index NULL b 7 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +select t1.* from t1 join t2 on (v = a); +a +drop table t1, t2; +# +# End of 10.2 tests +# +# +# MDEV-16241 Assertion `inited==RND' failed in handler::ha_rnd_end() +# +CREATE TABLE t1 (pk INT PRIMARY KEY, x INT, y INT, z INT, KEY (x), KEY (y, z)) +WITH SYSTEM VERSIONING +PARTITION BY SYSTEM_TIME (PARTITION p1 HISTORY, PARTITION pn CURRENT); +INSERT INTO t1 VALUES (1, 7, 8, 9), (2, NULL, NULL, NULL), (3, NULL, NULL, NULL); +SELECT COUNT(*) FROM t1 WHERE x IS NULL AND y IS NULL AND z IS NULL; +COUNT(*) +2 +DROP TABLE t1; +# +# MDEV-18244 Server crashes in ha_innobase::update_thd / ... / ha_partition::update_next_auto_inc_val +# +CREATE TABLE t1 (a INT) +ENGINE=InnoDB +PARTITION BY RANGE (a) ( +PARTITION p0 VALUES LESS THAN (6), +PARTITION pn VALUES LESS THAN MAXVALUE +); +INSERT INTO t1 VALUES (4),(5),(6); +ALTER TABLE t1 MODIFY a INT AUTO_INCREMENT PRIMARY KEY; +UPDATE t1 PARTITION (p0) SET a = 3 WHERE a = 5; +INSERT INTO t1 PARTITION(p0) VALUES (); +ERROR HY000: Found a row not matching the given partition set +INSERT INTO t1 PARTITION(p0) VALUES (-1); +INSERT INTO t1 VALUES (); +SELECT * FROM t1; +a +-1 +3 +4 +6 +7 +DROP TABLE t1; +# +# End of 10.3 tests +# |