1call mtr.add_suppression("Deadlock found when trying to get lock; try restarting transaction"); 2set global default_storage_engine='innodb'; 3set session default_storage_engine='innodb'; 4set @innodb_stats_persistent_save= @@innodb_stats_persistent; 5set @innodb_stats_persistent_sample_pages_save= 6@@innodb_stats_persistent_sample_pages; 7set global innodb_stats_persistent= 1; 8set global innodb_stats_persistent_sample_pages=100; 9drop table if exists t1, t2; 10# 11# Bug#13694811: THE OPTIMIZER WRONGLY USES THE FIRST 12# INNODB PARTITION STATISTICS 13# 14CREATE TABLE t1 15(a INT, 16b varchar(64), 17PRIMARY KEY (a), 18KEY (b)) 19ENGINE = InnoDB 20PARTITION BY RANGE (a) 21SUBPARTITION BY HASH (a) SUBPARTITIONS 10 22(PARTITION pNeg VALUES LESS THAN (0), 23PARTITION p0 VALUES LESS THAN (1000), 24PARTITION pMAX VALUES LESS THAN MAXVALUE); 25# Only one row in the first 10 subpartitions 26INSERT INTO t1 VALUES (-1, 'Only negative pk value'); 27INSERT INTO t1 VALUES (0, 'Mod Zero'), (1, 'One'), (2, 'Two'), (3, 'Three'), 28(10, 'Zero'), (11, 'Mod One'), (12, 'Mod Two'), (13, 'Mod Three'), 29(20, '0'), (21, '1'), (22, '2'), (23, '3'), 30(4, '4'), (5, '5'), (6, '6'), (7, '7'), (8, '8'), (9, '9'); 31INSERT INTO t1 SELECT a + 30, b FROM t1 WHERE a >= 0; 32INSERT INTO t1 SELECT a + 60, b FROM t1 WHERE a >= 0; 33INSERT INTO t1 SELECT a + 120, b FROM t1 WHERE a >= 0; 34INSERT INTO t1 SELECT a + 240, b FROM t1 WHERE a >= 0; 35ANALYZE TABLE t1; 36Table Op Msg_type Msg_text 37test.t1 analyze status Engine-independent statistics collected 38test.t1 analyze status OK 39EXPLAIN SELECT b FROM t1 WHERE b between 'L' and 'N' AND a > -100; 40id select_type table type possible_keys key key_len ref rows Extra 411 SIMPLE t1 range PRIMARY,b b 67 NULL 90 Using where; Using index 42DROP TABLE t1; 43# 44# Bug#13007154: Crash in keys_to_use_for_scanning with ORDER BY 45# and PARTITIONING 46# 47CREATE TABLE t1 (a INT, KEY(a)) 48ENGINE = InnoDB 49PARTITION BY KEY (a) PARTITIONS 1; 50SELECT 1 FROM t1 WHERE a > (SELECT LAST_INSERT_ID() FROM t1 LIMIT 0) 51ORDER BY a; 521 53DROP TABLE t1; 54# 55# Bug#56287: crash when using Partition datetime in sub in query 56# 57CREATE TABLE t1 58(c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT, 59c2 varchar(40) not null default '', 60c3 datetime not NULL, 61PRIMARY KEY (c1,c3), 62KEY partidx(c3)) 63ENGINE=InnoDB 64PARTITION BY RANGE (TO_DAYS(c3)) 65(PARTITION p200912 VALUES LESS THAN (to_days('2010-01-01')), 66PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')), 67PARTITION p201912 VALUES LESS THAN MAXVALUE); 68insert into t1(c2,c3) values ("Test row",'2010-01-01 00:00:00'); 69SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test'; 70PARTITION_NAME TABLE_ROWS 71p200912 0 72p201103 1 73p201912 0 74SELECT count(*) FROM t1 p where c3 in 75(select c3 from t1 t where t.c3 < timestamp '2011-04-26 19:19:44' 76 and t.c3 > timestamp '2011-04-26 19:18:44') ; 77count(*) 780 79DROP TABLE t1; 80# 81# Bug#54747: Deadlock between REORGANIZE PARTITION and 82# SELECT is not detected 83# 84SET @old_innodb_thread_concurrency := @@innodb_thread_concurrency; 85SET @old_innodb_thread_sleep_delay := @@innodb_thread_sleep_delay; 86SET GLOBAL innodb_thread_concurrency = 1; 87CREATE TABLE t1 88(user_num BIGINT, 89hours SMALLINT, 90KEY user_num (user_num)) 91ENGINE = InnoDB 92PARTITION BY RANGE COLUMNS (hours) 93(PARTITION hour_003 VALUES LESS THAN (3), 94PARTITION hour_004 VALUES LESS THAN (4), 95PARTITION hour_005 VALUES LESS THAN (5), 96PARTITION hour_last VALUES LESS THAN (MAXVALUE)); 97INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 98BEGIN; 99SELECT COUNT(*) FROM t1; 100COUNT(*) 1015 102connect con1,localhost,root,,; 103# SEND a ALTER PARTITION which waits on the ongoing transaction. 104ALTER TABLE t1 105REORGANIZE PARTITION hour_003, hour_004 INTO 106(PARTITION oldest VALUES LESS THAN (4)); 107# Connection default wait until the ALTER is in 'waiting for table...' 108# state and then continue the transaction by trying a SELECT 109connection default; 110SELECT COUNT(*) FROM t1; 111COUNT(*) 1125 113COMMIT; 114# reaping ALTER. 115connection con1; 116# Cleaning up. 117disconnect con1; 118connection default; 119SET GLOBAL innodb_thread_concurrency = @old_innodb_thread_concurrency; 120SET GLOBAL innodb_thread_sleep_delay = @old_innodb_thread_sleep_delay; 121DROP TABLE t1; 122# 123# Bug#50418: DROP PARTITION does not interact with transactions 124# 125CREATE TABLE t1 ( 126id INT AUTO_INCREMENT NOT NULL, 127name CHAR(50) NOT NULL, 128myDate DATE NOT NULL, 129PRIMARY KEY (id, myDate), 130INDEX idx_date (myDate) 131) ENGINE=InnoDB 132PARTITION BY RANGE ( TO_DAYS(myDate) ) ( 133PARTITION p0 VALUES LESS THAN (734028), 134PARTITION p1 VALUES LESS THAN (734029), 135PARTITION p2 VALUES LESS THAN (734030), 136PARTITION p3 VALUES LESS THAN MAXVALUE 137) ; 138INSERT INTO t1 VALUES 139(NULL, 'Lachlan', '2009-09-13'), 140(NULL, 'Clint', '2009-09-13'), 141(NULL, 'John', '2009-09-14'), 142(NULL, 'Dave', '2009-09-14'), 143(NULL, 'Jeremy', '2009-09-15'), 144(NULL, 'Scott', '2009-09-15'), 145(NULL, 'Jeff', '2009-09-16'), 146(NULL, 'Joe', '2009-09-16'); 147SET AUTOCOMMIT=0; 148SELECT * FROM t1 FOR UPDATE; 149id name myDate 1501 Lachlan 2009-09-13 1512 Clint 2009-09-13 1523 John 2009-09-14 1534 Dave 2009-09-14 1545 Jeremy 2009-09-15 1556 Scott 2009-09-15 1567 Jeff 2009-09-16 1578 Joe 2009-09-16 158UPDATE t1 SET name = 'Mattias' WHERE id = 7; 159SELECT * FROM t1 WHERE id = 7; 160id name myDate 1617 Mattias 2009-09-16 162connect con1, localhost, root,,; 163SET lock_wait_timeout = 1; 164# After the patch it will wait and fail on timeout. 165ALTER TABLE t1 DROP PARTITION p3; 166ERROR HY000: Lock wait timeout exceeded; try restarting transaction 167SHOW WARNINGS; 168Level Code Message 169Error 1205 Lock wait timeout exceeded; try restarting transaction 170disconnect con1; 171connection default; 172SELECT * FROM t1; 173id name myDate 1741 Lachlan 2009-09-13 1752 Clint 2009-09-13 1763 John 2009-09-14 1774 Dave 2009-09-14 1785 Jeremy 2009-09-15 1796 Scott 2009-09-15 1807 Mattias 2009-09-16 1818 Joe 2009-09-16 182# No changes. 183COMMIT; 184DROP TABLE t1; 185# 186# Bug#51830: Incorrect partition pruning on range partition (regression) 187# 188CREATE TABLE t1 (a INT NOT NULL) 189ENGINE = InnoDB 190PARTITION BY RANGE(a) 191(PARTITION p10 VALUES LESS THAN (10), 192PARTITION p30 VALUES LESS THAN (30), 193PARTITION p50 VALUES LESS THAN (50), 194PARTITION p70 VALUES LESS THAN (70), 195PARTITION p90 VALUES LESS THAN (90)); 196INSERT INTO t1 VALUES (10),(30),(50); 197INSERT INTO t1 VALUES (70); 198INSERT INTO t1 VALUES (80); 199INSERT INTO t1 VALUES (89); 200INSERT INTO t1 VALUES (90); 201ERROR HY000: Table has no partition for value 90 202INSERT INTO t1 VALUES (100); 203ERROR HY000: Table has no partition for value 100 204insert INTO t1 VALUES (110); 205ERROR HY000: Table has no partition for value 110 206EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90; 207id select_type table partitions type possible_keys key key_len ref rows Extra 2081 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 209EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90; 210id select_type table partitions type possible_keys key key_len ref rows Extra 2111 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 212EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90; 213id select_type table partitions type possible_keys key key_len ref rows Extra 2141 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 215EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89; 216id select_type table partitions type possible_keys key key_len ref rows Extra 2171 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where 218EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 89; 219id select_type table partitions type possible_keys key key_len ref rows Extra 2201 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where 221EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89; 222id select_type table partitions type possible_keys key key_len ref rows Extra 2231 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 224EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100; 225id select_type table partitions type possible_keys key key_len ref rows Extra 2261 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 227EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100; 228id select_type table partitions type possible_keys key key_len ref rows Extra 2291 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 230EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100; 231id select_type table partitions type possible_keys key key_len ref rows Extra 2321 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 233DROP TABLE t1; 234# 235# Bug#50104: Partitioned table with just 1 partion works with fk 236# 237CREATE TABLE t2 ( 238id INT, 239PRIMARY KEY (id) 240) ENGINE=InnoDB ; 241CREATE TABLE t1 ( 242id INT NOT NULL AUTO_INCREMENT, 243parent_id INT DEFAULT NULL, 244PRIMARY KEY (id), 245KEY parent_id (parent_id) 246) ENGINE=InnoDB; 247ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 1; 248ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id); 249ERROR HY000: Partitioned tables do not support FOREIGN KEY 250ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 2; 251ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id); 252ERROR HY000: Partitioned tables do not support FOREIGN KEY 253DROP TABLE t1, t2; 254create table t1 (a varchar(5), b int signed, c varchar(10), d datetime) 255partition by range columns(b,c) 256subpartition by hash(to_seconds(d)) 257( partition p0 values less than (2, 'b'), 258partition p1 values less than (4, 'd'), 259partition p2 values less than (10, 'za')); 260insert into t1 values ('a', 3, 'w', '2001-10-27 04:34:00'); 261insert into t1 values ('r', 7, 'w', '2001-10-27 05:34:00'); 262insert into t1 values ('g', 10, 'w', '2001-10-27 06:34:00'); 263update t1 set a = 'c' where a > 'f'; 264drop table t1; 265create table t1 (a varchar(5)) 266engine=memory 267partition by range columns(a) 268( partition p0 values less than ('m'), 269partition p1 values less than ('za')); 270insert into t1 values ('j'); 271update t1 set a = 'z' where (a >= 'j'); 272drop table t1; 273create table t1 (a varchar(5)) 274engine=myisam 275partition by range columns(a) 276( partition p0 values less than ('m'), 277partition p1 values less than ('za')); 278insert into t1 values ('j'); 279update t1 set a = 'z' where (a >= 'j'); 280drop table t1; 281create table t1 (a varchar(5)) 282engine=innodb 283partition by range columns(a) 284( partition p0 values less than ('m'), 285partition p1 values less than ('za')); 286insert into t1 values ('j'); 287update t1 set a = 'z' where (a >= 'j'); 288drop table t1; 289create table t1 (a int not null, 290b datetime not null, 291primary key (a,b)) 292engine=innodb 293partition by range (to_days(b)) 294subpartition by hash (a) 295subpartitions 2 296( partition p0 values less than (to_days('2009-01-01')), 297partition p1 values less than (to_days('2009-02-01')), 298partition p2 values less than (to_days('2009-03-01')), 299partition p3 values less than maxvalue); 300alter table t1 reorganize partition p1,p2 into 301( partition p2 values less than (to_days('2009-03-01'))); 302drop table t1; 303CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB 304PARTITION BY RANGE(id) ( 305PARTITION p0 VALUES LESS THAN (5), 306PARTITION p1 VALUES LESS THAN (10), 307PARTITION p2 VALUES LESS THAN MAXVALUE 308); 309INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), 310(9,9), (10,10), (11,11); 311SET @old_tx_isolation := @@session.tx_isolation; 312SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 313SET autocommit = 0; 314UPDATE t1 SET DATA = data*2 WHERE id = 3; 315UPDATE t1 SET data = data*2 WHERE data = 2; 316SET @@session.tx_isolation = @old_tx_isolation; 317DROP TABLE t1; 318# Bug#37721, test of ORDER BY on PK and WHERE on INDEX 319CREATE TABLE t1 ( 320a INT, 321b INT, 322PRIMARY KEY (a), 323INDEX (b)) 324ENGINE InnoDB 325PARTITION BY HASH(a) 326PARTITIONS 3; 327INSERT INTO t1 VALUES (0,0),(4,0),(2,0); 328SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC; 329a 3300 3312 3324 333SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC; 334a 3354 3362 3370 338ALTER TABLE t1 DROP INDEX b; 339SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC; 340a 3410 3422 3434 344SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC; 345a 3464 3472 3480 349DROP TABLE t1; 350CREATE TABLE t1 ( 351a VARCHAR(600), 352b VARCHAR(600), 353PRIMARY KEY (a), 354INDEX (b)) 355ENGINE InnoDB 356PARTITION BY KEY(a) 357PARTITIONS 3; 358INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'1'),repeat('0',257)); 359INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'3'),repeat('0',257)); 360INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'2'),repeat('0',257)); 361SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC; 362right(a,1) 3631 3642 3653 366SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC; 367right(a,1) 3683 3692 3701 371ALTER TABLE t1 DROP INDEX b; 372SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC; 373right(a,1) 3741 3752 3763 377SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC; 378right(a,1) 3793 3802 3811 382DROP TABLE t1; 383# Bug#32948 384CREATE TABLE t1 (c1 INT, PRIMARY KEY (c1)) ENGINE=INNODB; 385CREATE TABLE t2 (c1 INT, PRIMARY KEY (c1), 386FOREIGN KEY (c1) REFERENCES t1 (c1) 387ON DELETE CASCADE) 388ENGINE=INNODB; 389ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5; 390ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails 391ALTER TABLE t1 ENGINE=MyISAM; 392ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails 393DROP TABLE t2; 394DROP TABLE t1; 395create table t1 (a int) engine=innodb partition by hash(a) ; 396show table status like 't1'; 397Name 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 398t1 InnoDB 10 Dynamic 2 8192 16384 0 0 0 NULL # NULL NULL latin1_swedish_ci NULL partitioned 0 N 399drop table t1; 400create table t1 (a int) 401engine = innodb 402partition by key (a); 403show table status; 404Name 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 405t1 InnoDB 10 Dynamic 2 8192 16384 0 0 0 NULL Create_time NULL NULL latin1_swedish_ci NULL partitioned 0 N 406insert into t1 values (0), (1), (2), (3); 407show table status; 408Name 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 409t1 InnoDB 10 Dynamic 4 4096 16384 0 0 0 NULL Create_time Update_time NULL latin1_swedish_ci NULL partitioned 0 N 410drop table t1; 411create table t1 (a int auto_increment primary key) 412engine = innodb 413partition by key (a); 414show table status; 415Name 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 416t1 InnoDB 10 Dynamic 2 8192 16384 0 0 0 1 Create_time NULL NULL latin1_swedish_ci NULL partitioned 0 N 417insert into t1 values (NULL), (NULL), (NULL), (NULL); 418show table status; 419Name 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 420t1 InnoDB 10 Dynamic 4 4096 16384 0 0 0 5 Create_time Update_time NULL latin1_swedish_ci NULL partitioned 0 N 421insert into t1 values (NULL), (NULL), (NULL), (NULL); 422show table status; 423Name 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 424t1 InnoDB 10 Dynamic 8 2048 16384 0 0 0 9 Create_time Update_time NULL latin1_swedish_ci NULL partitioned 0 N 425drop table t1; 426create table t1 (a int) 427partition by key (a) 428(partition p1 engine = innodb); 429alter table t1 rebuild partition p1; 430alter table t1 rebuild partition p1; 431alter table t1 rebuild partition p1; 432alter table t1 rebuild partition p1; 433alter table t1 rebuild partition p1; 434alter table t1 rebuild partition p1; 435alter table t1 rebuild partition p1; 436drop table t1; 437create table t1 (a date) 438engine = innodb 439partition by range (year(a)) 440(partition p0 values less than (2006), 441partition p1 values less than (2007)); 442explain partitions select * from t1 443where a between '2006-01-01' and '2007-06-01'; 444id select_type table partitions type possible_keys key key_len ref rows Extra 4451 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where 446drop table t1; 447SET SQL_MODE=""; 448create table t1 (a int) 449engine = x 450partition by key (a); 451Warnings: 452Warning 1286 Unknown storage engine 'x' 453Warning 1266 Using storage engine InnoDB for table 't1' 454show create table t1; 455Table Create Table 456t1 CREATE TABLE `t1` ( 457 `a` int(11) DEFAULT NULL 458) ENGINE=InnoDB DEFAULT CHARSET=latin1 459 PARTITION BY KEY (`a`) 460drop table t1; 461create table t1 (a int) 462engine = innodb 463partition by list (a) 464(partition p0 values in (0)); 465alter table t1 engine = x; 466Warnings: 467Warning 1286 Unknown storage engine 'x' 468show create table t1; 469Table Create Table 470t1 CREATE TABLE `t1` ( 471 `a` int(11) DEFAULT NULL 472) ENGINE=InnoDB DEFAULT CHARSET=latin1 473 PARTITION BY LIST (`a`) 474(PARTITION `p0` VALUES IN (0) ENGINE = InnoDB) 475drop table t1; 476SET SQL_MODE=default; 477create table t1 478( 479id int unsigned auto_increment, 480time datetime not null, 481first_name varchar(40), 482last_name varchar(50), 483primary key (id, time), 484index first_index (first_name), 485index last_index (last_name) 486) engine=Innodb partition by range (to_days(time)) ( 487partition p1 values less than (to_days('2007-02-07')), 488partition p2 values less than (to_days('2007-02-08')), 489partition p3 values less than MAXVALUE 490); 491insert into t1 (time, first_name, last_name) values ('2007-02-07', 'Q', 'Robert'), 492('2007-02-07', 'Mark', 'Nate'), ('2007-02-07', 'Nate', 'Oscar'), 493('2007-02-07', 'Zack', 'Alice'), ('2007-02-07', 'Jack', 'Kathy'), 494('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Brian', 'Charles'), 495('2007-02-06', 'Charles', 'David'), ('2007-02-06', 'David', 'Eric'), 496('2007-02-07', 'Hector', 'Isaac'), ('2007-02-07', 'Oscar', 'Patricia'), 497('2007-02-07', 'Patricia', 'Q'), ('2007-02-07', 'X', 'Yuri'), 498('2007-02-07', 'Robert', 'Shawn'), ('2007-02-07', 'Kathy', 'Lois'), 499('2007-02-07', 'Eric', 'Francis'), ('2007-02-06', 'Shawn', 'Theron'), 500('2007-02-06', 'U', 'Vincent'), ('2007-02-06', 'Francis', 'George'), 501('2007-02-06', 'George', 'Hector'), ('2007-02-06', 'Vincent', 'Walter'), 502('2007-02-06', 'Walter', 'X'), ('2007-02-07', 'Lois', 'Mark'), 503('2007-02-07', 'Yuri', 'Zack'), ('2007-02-07', 'Isaac', 'Jack'), 504('2007-02-07', 'Sharon', 'Mark'), ('2007-02-07', 'Michael', 'Michelle'), 505('2007-02-07', 'Derick', 'Nathan'), ('2007-02-07', 'Peter', 'Xavier'), 506('2007-02-07', 'Fred', 'Harold'), ('2007-02-07', 'Katherine', 'Lisa'), 507('2007-02-07', 'Tom', 'Rina'), ('2007-02-07', 'Jerry', 'Victor'), 508('2007-02-07', 'Alexander', 'Terry'), ('2007-02-07', 'Justin', 'John'), 509('2007-02-07', 'Greg', 'Ernest'), ('2007-02-07', 'Robert', 'Q'), 510('2007-02-07', 'Nate', 'Mark'), ('2007-02-07', 'Oscar', 'Nate'), 511('2007-02-07', 'Alice', 'Zack'), ('2007-02-07', 'Kathy', 'Jack'), 512('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Charles', 'Brian'), 513('2007-02-06', 'David', 'Charles'), ('2007-02-06', 'Eric', 'David'), 514('2007-02-07', 'Isaac', 'Hector'), ('2007-02-07', 'Patricia', 'Oscar'), 515('2007-02-07', 'Q', 'Patricia'), ('2007-02-07', 'Yuri', 'X'), 516('2007-02-07', 'Shawn', 'Robert'), ('2007-02-07', 'Lois', 'Kathy'), 517('2007-02-07', 'Francis', 'Eric'), ('2007-02-06', 'Theron', 'Shawn'), 518('2007-02-06', 'Vincent', 'U'), ('2007-02-06', 'George', 'Francis'), 519('2007-02-06', 'Hector', 'George'), ('2007-02-06', 'Walter', 'Vincent'), 520('2007-02-06', 'X', 'Walter'), ('2007-02-07', 'Mark', 'Lois'), 521('2007-02-07', 'Zack', 'Yuri'), ('2007-02-07', 'Jack', 'Isaac'), 522('2007-02-07', 'Mark', 'Sharon'), ('2007-02-07', 'Michelle', 'Michael'), 523('2007-02-07', 'Nathan', 'Derick'), ('2007-02-07', 'Xavier', 'Peter'), 524('2007-02-07', 'Harold', 'Fred'), ('2007-02-07', 'Lisa', 'Katherine'), 525('2007-02-07', 'Rina', 'Tom'), ('2007-02-07', 'Victor', 'Jerry'), 526('2007-02-07', 'Terry', 'Alexander'), ('2007-02-07', 'John', 'Justin'), 527('2007-02-07', 'Ernest', 'Greg'); 528SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake'; 529id time first_name last_name 530drop table t1; 531CREATE TABLE t1 (a DOUBLE NOT NULL, KEY(a)) ENGINE=InnoDB 532PARTITION BY KEY(a) PARTITIONS 10; 533INSERT INTO t1 VALUES(1),(2); 534SELECT COUNT(*) FROM t1; 535COUNT(*) 5362 537DROP TABLE t1; 538create table t1 (int_column int, char_column char(5)) 539PARTITION BY RANGE (int_column) subpartition by key (char_column) subpartitions 2 540(PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB); 541alter table t1 542ENGINE = MyISAM 543PARTITION BY RANGE (int_column) 544subpartition by key (char_column) subpartitions 2 545(PARTITION p1 VALUES LESS THAN (5)); 546show create table t1; 547Table Create Table 548t1 CREATE TABLE `t1` ( 549 `int_column` int(11) DEFAULT NULL, 550 `char_column` char(5) DEFAULT NULL 551) ENGINE=MyISAM DEFAULT CHARSET=latin1 552 PARTITION BY RANGE (`int_column`) 553SUBPARTITION BY KEY (`char_column`) 554SUBPARTITIONS 2 555(PARTITION `p1` VALUES LESS THAN (5) ENGINE = MyISAM) 556drop table t1; 557CREATE TABLE t1 (a INT) ENGINE=InnoDB 558PARTITION BY list(a) (PARTITION p1 VALUES IN (1)); 559CREATE INDEX i1 ON t1 (a); 560DROP TABLE t1; 561# 562# Bug#54783: optimize table crashes with invalid timestamp default value and NO_ZERO_DATE 563# 564DROP TABLE IF EXISTS t1; 565CREATE TABLE t1 (a INT, b TIMESTAMP DEFAULT '0000-00-00 00:00:00') 566ENGINE=INNODB PARTITION BY LINEAR HASH (a) PARTITIONS 1; 567SET @old_mode = @@sql_mode; 568SET SESSION sql_mode = 'NO_ZERO_DATE'; 569OPTIMIZE TABLE t1; 570Table Op Msg_type Msg_text 571test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 572test.t1 optimize error Invalid default value for 'b' 573test.t1 optimize status Operation failed 574Warnings: 575Warning 1265 Data truncated for column 'b' at row 1 576Error 1067 Invalid default value for 'b' 577SET SESSION sql_mode = @old_mode; 578DROP TABLE t1; 579# 580# Bug#57985 "ONLINE/FAST ALTER PARTITION can fail and leave the 581# table unusable". 582# 583CREATE TABLE t1 (a bigint not null, b int not null, PRIMARY KEY (a)) 584ENGINE = InnoDB PARTITION BY KEY(a) PARTITIONS 2; 585INSERT INTO t1 values (0,1), (1,2); 586# The below ALTER should fail. It should leave the 587# table in its original, non-corrupted, usable state. 588ALTER TABLE t1 ADD UNIQUE KEY (b); 589ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function 590# The below statements should succeed, as ALTER should 591# have left table intact. 592SHOW CREATE TABLE t1; 593Table Create Table 594t1 CREATE TABLE `t1` ( 595 `a` bigint(20) NOT NULL, 596 `b` int(11) NOT NULL, 597 PRIMARY KEY (`a`) 598) ENGINE=InnoDB DEFAULT CHARSET=latin1 599 PARTITION BY KEY (`a`) 600PARTITIONS 2 601SELECT * FROM t1; 602a b 6031 2 6040 1 605DROP TABLE t1; 606# 607# Bug#57985 "ONLINE/FAST ALTER PARTITION can fail and leave the 608# table unusable". 609# 610DROP TABLE IF EXISTS t1; 611CREATE TABLE t1 (a bigint not null, b int not null, PRIMARY KEY (a)) 612ENGINE = InnoDB PARTITION BY KEY(a) PARTITIONS 2; 613INSERT INTO t1 values (0,1), (1,2); 614# The below ALTER should fail. It should leave the 615# table in its original, non-corrupted, usable state. 616ALTER TABLE t1 ADD UNIQUE KEY (b); 617ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function 618# The below statements should succeed, as ALTER should 619# have left table intact. 620SHOW CREATE TABLE t1; 621Table Create Table 622t1 CREATE TABLE `t1` ( 623 `a` bigint(20) NOT NULL, 624 `b` int(11) NOT NULL, 625 PRIMARY KEY (`a`) 626) ENGINE=InnoDB DEFAULT CHARSET=latin1 627 PARTITION BY KEY (`a`) 628PARTITIONS 2 629SELECT * FROM t1; 630a b 6311 2 6320 1 633DROP TABLE t1; 634# 635# Bug #17299181 CREATE_TIME AND UPDATE_TIME ARE 636# WRONG FOR PARTITIONED TABLES 637# 638CREATE TABLE t1 (a int, PRIMARY KEY (a)) ENGINE=InnoDB 639PARTITION BY HASH (a) PARTITIONS 2; 640SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE 641CREATE_TIME IS NOT NULL AND TABLE_NAME='t1'; 642COUNT(*) 6431 644DROP TABLE t1; 645# 646# BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == 647# SAVE_READ_SET 648# 649CREATE TABLE t1 ( 650a INT, 651b INT, 652c INT, 653PRIMARY KEY (c,a), KEY (a),KEY (a) 654) ENGINE=INNODB PARTITION BY KEY () PARTITIONS 2; 655Warnings: 656Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release 657INSERT INTO t1 VALUES (1,5,1),(2,4,1),(3,3,1),(4,2,1),(5,1,1); 658UPDATE t1 SET b = 0, c=1 WHERE a <=>0; 659SELECT * FROM t1; 660a b c 6611 5 1 6622 4 1 6633 3 1 6644 2 1 6655 1 1 666DROP TABLE t1; 667# 668# MDEV-5102 : MySQL Bug 69851 669# 670CREATE TABLE t1 ( 671`col1` bigint(20) unsigned NOT NULL , 672`col2` bigint(20) unsigned NOT NULL , 673`col3` datetime NOT NULL , 674PRIMARY KEY (`col3`), 675KEY (`col1`), 676KEY (`col2`) 677) ENGINE=InnoDB DEFAULT CHARSET=latin1 678PARTITION BY RANGE (TO_DAYS(col3)) 679( 680PARTITION p_20130310 VALUES LESS THAN (735303) ENGINE = InnoDB, 681PARTITION p_20130311 VALUES LESS THAN (735304) ENGINE = InnoDB, 682PARTITION p_20130312 VALUES LESS THAN (735305) ENGINE = InnoDB 683); 684INSERT INTO `t1` VALUES (2,96,'2013-03-08 16:28:05'); 685INSERT INTO `t1` VALUES (1,2,'2013-03-08 16:47:39'); 686INSERT INTO `t1` VALUES (1,2,'2013-03-08 16:50:27'); 687INSERT INTO `t1` VALUES (1,2,'2013-03-11 16:33:04'); 688INSERT INTO `t1` VALUES (1,2,'2013-03-11 16:33:24'); 689INSERT INTO `t1` VALUES (2,2,'2013-03-12 10:11:48'); 690SELECT * FROM t1 WHERE col1 = 1 AND col2 = 2 691AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00' 692GROUP BY 1, 2, 3; 693col1 col2 col3 6941 2 2013-03-08 16:47:39 6951 2 2013-03-08 16:50:27 6961 2 2013-03-11 16:33:04 6971 2 2013-03-11 16:33:24 698EXPLAIN SELECT * FROM t1 WHERE col1 = 1 AND col2 = 2 699AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00' 700GROUP BY 1, 2, 3; 701id select_type table type possible_keys key key_len ref rows Extra 7021 SIMPLE t1 range PRIMARY,col1,col2 PRIMARY 5 NULL # Using where; Using filesort 703SELECT * FROM t1 USE INDEX () WHERE col1 = 1 AND col2 = 2 704AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00' 705GROUP BY 1, 2, 3; 706col1 col2 col3 7071 2 2013-03-08 16:47:39 7081 2 2013-03-08 16:50:27 7091 2 2013-03-11 16:33:04 7101 2 2013-03-11 16:33:24 711DROP TABLE t1; 712# 713# MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703) 714# 715create table t1 ( 716a int not null, 717b int not null, 718pk int not null, 719primary key (pk), 720key(a), 721key(b) 722) engine=innodb partition by hash(pk) partitions 10; 723insert into t1 values (1,2,4); 724insert into t1 values (1,0,17); 725insert into t1 values (1,2,25); 726insert into t1 values (10,20,122); 727insert into t1 values (10,20,123); 728create table t2 (a int); 729insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 730insert into t1 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t2 A, t2 B, t2 C; 731insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a, 73210+A.a + 10*B.a + 100*C.a + 1000*D.a, 7332000 + A.a + 10*B.a + 100*C.a + 1000*D.a 734from t2 A, t2 B, t2 C ,t2 D; 735set statement optimizer_switch='rowid_filter=off' for 736explain select * from t1 where a=1 and b=2 and pk between 1 and 999999 ; 737id select_type table type possible_keys key key_len ref rows Extra 7381 SIMPLE t1 index_merge PRIMARY,a,b b,a 4,4 NULL # Using intersect(b,a); Using where; Using index 739set statement optimizer_switch='rowid_filter=off' for 740create temporary table t3 as 741select * from t1 where a=1 and b=2 and pk between 1 and 999 ; 742select count(*) from t3; 743count(*) 744802 745drop table t3; 746create temporary table t3 as 747select * from t1 ignore index(a,b) where a=1 and b=2 and pk between 1 and 999 ; 748select count(*) from t3; 749count(*) 750802 751drop table t3; 752drop table t1,t2; 753# 754# MySQL Bug#71095: Wrong results with PARTITION BY LIST COLUMNS() 755# 756create table t1(c1 int, c2 int, c3 int, c4 int, 757primary key(c1,c2)) engine=InnoDB 758partition by list columns(c2) 759(partition p1 values in (1,2) engine=InnoDB, 760partition p2 values in (3,4) engine=InnoDB); 761insert into t1 values (1,1,1,1),(2,3,1,1); 762select * from t1 where c1=2 and c2=3; 763c1 c2 c3 c4 7642 3 1 1 765drop table t1; 766# 767# MySQL Bug#72803: Wrong "Impossible where" with LIST partitioning 768# also MDEV-6240: Wrong "Impossible where" with LIST partitioning 769# 770CREATE TABLE t1 ( d DATE) ENGINE = InnoDB 771PARTITION BY LIST COLUMNS (d) 772( 773PARTITION p0 VALUES IN ('1990-01-01','1991-01-01'), 774PARTITION p1 VALUES IN ('1981-01-01') 775); 776INSERT INTO t1 (d) VALUES ('1991-01-01'); 777SELECT * FROM t1 WHERE d = '1991-01-01'; 778d 7791991-01-01 780DROP TABLE t1; 781set global default_storage_engine=default; 782# 783# MDEV-9455: [ERROR] mysqld got signal 11 784# 785CREATE TABLE `t1` ( 786`DIARY_TOTAL_DAY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 787`IMORY_ID` bigint(20) NOT NULL, 788`NAME` varchar(75) DEFAULT NULL, 789`DATETIME` varchar(10) NOT NULL DEFAULT '', 790`DAILY_CALL_CNT` int(11) DEFAULT NULL, 791`DAILY_SMS_CNT` int(11) DEFAULT NULL, 792`NUMBER` varchar(64) DEFAULT NULL, 793`DURATION` varchar(16) DEFAULT NULL, 794PRIMARY KEY (`DIARY_TOTAL_DAY_SEQ`,`DATETIME`), 795KEY `IDX_t1_01` (`IMORY_ID`,`DATETIME`) 796) AUTO_INCREMENT=328702514 DEFAULT CHARSET=utf8mb4 797PARTITION BY RANGE COLUMNS(`DATETIME`) 798(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB, 799PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB, 800PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB, 801PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB, 802PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB, 803PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB, 804PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB, 805PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB, 806PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB, 807PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB, 808PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB) 809; 810CREATE TABLE `t2` ( 811`DIARY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 812`IMORY_ID` bigint(20) NOT NULL, 813`CALL_TYPE` varchar(1) DEFAULT NULL, 814`DATA_TYPE` varchar(1) DEFAULT NULL, 815`FEATURES` varchar(1) DEFAULT NULL, 816`NAME` varchar(75) DEFAULT NULL, 817`NUMBER` varchar(64) DEFAULT NULL, 818`DATETIME` datetime NOT NULL, 819`REG_DATE` datetime NOT NULL, 820`TITLE` varchar(50) DEFAULT NULL, 821`BODY` varchar(4200) DEFAULT NULL, 822`MIME_TYPE` varchar(32) DEFAULT NULL, 823`DURATION` varchar(16) DEFAULT NULL, 824`DEVICE_ID` varchar(64) DEFAULT NULL, 825`DEVICE_NAME` varchar(32) DEFAULT NULL, 826PRIMARY KEY (`DIARY_SEQ`,`DATETIME`,`REG_DATE`), 827KEY `IDX_TB_DIARY_01` (`IMORY_ID`,`DATETIME`,`CALL_TYPE`,`NUMBER`), 828KEY `IDX_TB_DIARY_02` (`REG_DATE`) 829) AUTO_INCREMENT=688799006 DEFAULT CHARSET=utf8mb4 830PARTITION BY RANGE COLUMNS(REG_DATE) 831(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB, 832PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB, 833PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB, 834PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB, 835PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB, 836PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB, 837PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB, 838PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB, 839PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB, 840PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB, 841PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB) 842; 843SELECT 844A.IMORY_ID, 845A.NUMBER, 846A.NAME, 847DATE_FORMAT(A.DATETIME, '%Y-%m-%d') AS TARGET_DATE, 848SUM( CASE WHEN A.DATA_TYPE='1' THEN 1 ELSE 0 END) AS CALL_CNT, 849SUM( CASE WHEN A.DATA_TYPE IN ('2', '3') THEN 1 ELSE 0 END) AS SMS_CNT, 850SUM(CAST(A.DURATION AS INT)) AS DURATION, 851( SELECT COUNT(*) 852FROM t1 853WHERE IMORY_ID=A.IMORY_ID 854AND NUMBER=A.NUMBER 855AND NAME=A.NAME 856AND DATETIME = DATE_FORMAT(A.DATETIME, '%Y-%m-%d') 857) STATS_COUNT 858FROM t2 A 859WHERE A.IMORY_ID = 55094102 860AND A.DATETIME LIKE ( 861SELECT CONCAT (DATE_FORMAT(DATETIME, '%Y-%m-%d') ,'%') 862FROM t2 863WHERE IMORY_ID=55094102 864AND DIARY_SEQ IN ( 608351221, 608351225, 608351229 ) 865group by DATE_FORMAT(DATETIME, '%Y-%m-%d') 866) 867GROUP BY A.IMORY_ID, A.NUMBER, A.NAME, DATE_FORMAT(A.DATETIME, '%Y-%m-%d') 868; 869IMORY_ID NUMBER NAME TARGET_DATE CALL_CNT SMS_CNT DURATION STATS_COUNT 870drop table t2, t1; 871set global default_storage_engine='innodb'; 872# 873# MDEV-5963: InnoDB: Assertion failure in file row0sel.cc line 2503, 874# Failing assertion: 0 with "key ptr now exceeds key end by 762 bytes" 875# (independent testcase for Oracle Bug#13947868) 876# 877CREATE TABLE t1 (f1 VARCHAR(512) CHARACTER SET utf8) ENGINE=InnoDB; 878INSERT INTO t1 VALUES ('j'); 879CREATE TABLE t2 ( 880f2 VARCHAR(5) CHARACTER SET latin1, 881f3 VARCHAR(5) CHARACTER SET utf8, 882f4 INT, 883f5 VARCHAR(512) CHARACTER SET utf8, 884f6 VARCHAR(256) CHARACTER SET utf8, 885key (f2), 886key (f3), 887key (f5) 888) ENGINE=InnoDB PARTITION BY LIST COLUMNS (f4) 889SUBPARTITION BY KEY(f6) SUBPARTITIONS 4 ( 890PARTITION p0 VALUES IN (1,3,9,null), 891PARTITION p1 VALUES IN (2,4,0) 892); 893INSERT INTO t2 VALUES 894('k','s',3,'b','j'),('a','b',NULL,'v','j'),('c','m',9,'t',NULL), 895('b','l',9,'b',NULL),('i','y',3,'o','w'),('c','m',NULL,'a','m'), 896('f','o',9,'m','w'),('f','q',NULL,'o','a'); 897CREATE TABLE t3 LIKE t2; 898SELECT * FROM t1 INNER JOIN t2 ON ( f5 = f1 ); 899f1 f2 f3 f4 f5 f6 900INSERT INTO t3 SELECT * FROM t2 WHERE f3 = 'm' AND f2 ='c'; 901DROP TABLE t1,t2,t3; 902set global default_storage_engine=default; 903# 904# Bug#13737949: CRASH IN HA_PARTITION::INDEX_INIT 905# Bug#18694052: SERVER CRASH IN HA_PARTITION::INIT_RECORD_PRIORITY_QUEUE 906# 907CREATE TABLE t1 908(a INT, 909b INT, 910PRIMARY KEY (a)) 911ENGINE = InnoDB 912PARTITION BY HASH (a) PARTITIONS 3; 913START TRANSACTION WITH CONSISTENT SNAPSHOT; 914connect con1, localhost, root,,; 915ALTER TABLE t1 ADD INDEX idx1 (b); 916connection default; 917SELECT b FROM t1 WHERE b = 0; 918ERROR HY000: Table definition has changed, please retry transaction 919SELECT b FROM t1 WHERE b = 0; 920ERROR HY000: Table definition has changed, please retry transaction 921disconnect con1; 922DROP TABLE t1; 923# Same test without partitioning 924CREATE TABLE t1 925(a INT, 926b INT, 927PRIMARY KEY (a)) 928ENGINE = InnoDB; 929START TRANSACTION WITH CONSISTENT SNAPSHOT; 930connect con1, localhost, root,,; 931ALTER TABLE t1 ADD INDEX idx1 (b); 932connection default; 933SELECT b FROM t1 WHERE b = 0; 934ERROR HY000: Table definition has changed, please retry transaction 935SELECT b FROM t1 WHERE b = 0; 936ERROR HY000: Table definition has changed, please retry transaction 937disconnect con1; 938DROP TABLE t1; 939# 940# MDEV-11167: InnoDB: Warning: using a partial-field key prefix 941# in search, results in assertion failure or "Can't find record" error 942# 943set @save_sql_mode = @@sql_mode; 944set sql_mode=""; 945CREATE TABLE t1 (a INT) ENGINE=InnoDB; 946CREATE TABLE t2 (b INT, c INT, KEY(b)) ENGINE=InnoDB PARTITION BY HASH(c) PARTITIONS 2; 947CREATE ALGORITHM = MERGE VIEW v AS SELECT a, b FROM t1 STRAIGHT_JOIN t2 WHERE b = 'foo' WITH CHECK OPTION; 948INSERT INTO t1 VALUES (1),(2); 949INSERT IGNORE INTO t2 VALUES (2,2),('three',3),(4,4); 950Warnings: 951Warning 1366 Incorrect integer value: 'three' for column `test`.`t2`.`b` at row 2 952UPDATE v SET a = NULL; 953Warnings: 954Warning 1292 Truncated incorrect DOUBLE value: 'foo' 955DROP view v; 956DROP TABLE t1, t2; 957SET @save_isp=@@innodb_stats_persistent; 958SET GLOBAL innodb_stats_persistent= ON; 959CREATE TABLE t (f1 INT, f2 INT, KEY(f2)) ENGINE=InnoDB PARTITION BY HASH (f1) PARTITIONS 2; 960INSERT IGNORE INTO t VALUES (NULL,0),(NULL,0),(0,21),(4,0),(1,8),(5,66); 961CREATE ALGORITHM=MERGE VIEW v AS SELECT t1.* FROM t t1 JOIN t t2 WHERE t1.f1 < t2.f2 WITH LOCAL CHECK OPTION; 962UPDATE v SET f2 = NULL; 963ERROR 44000: CHECK OPTION failed `test`.`v` 964SET GLOBAL innodb_stats_persistent= @save_isp; 965DROP view v; 966DROP TABLE t; 967set sql_mode= @save_sql_mode; 968# 969# Bug#28573894 ALTER PARTITIONED TABLE ADD AUTO_INCREMENT DIFF RESULT 970# 971CREATE TABLE t (a VARCHAR(10) NOT NULL,b INT,PRIMARY KEY (b)) ENGINE=INNODB 972PARTITION BY RANGE (b) 973(PARTITION pa VALUES LESS THAN (2), 974PARTITION pb VALUES LESS THAN (20), 975PARTITION pc VALUES LESS THAN (30), 976PARTITION pd VALUES LESS THAN (40)); 977INSERT INTO t 978VALUES('A',0),('B',1),('C',2),('D',3),('E',4),('F',5),('G',25),('H',35); 979CREATE TABLE t_copy LIKE t; 980INSERT INTO t_copy SELECT * FROM t; 981ALTER TABLE t ADD COLUMN r INT UNSIGNED NOT NULL AUTO_INCREMENT, 982ADD UNIQUE KEY (r,b); 983affected rows: 0 984info: Records: 0 Duplicates: 0 Warnings: 0 985ALTER TABLE t_copy ADD COLUMN r INT UNSIGNED NOT NULL AUTO_INCREMENT, 986ADD UNIQUE KEY (r,b), ALGORITHM=COPY; 987affected rows: 8 988info: Records: 8 Duplicates: 0 Warnings: 0 989SELECT * FROM t; 990a b r 991A 0 1 992B 1 2 993C 2 3 994D 3 4 995E 4 5 996F 5 6 997G 25 7 998H 35 8 999SELECT * FROM t_copy; 1000a b r 1001A 0 1 1002B 1 2 1003C 2 3 1004D 3 4 1005E 4 5 1006F 5 6 1007G 25 7 1008H 35 8 1009DROP TABLE t,t_copy; 1010# 1011# Bug#26390658 RENAMING A PARTITIONED TABLE DOES NOT UPDATE 1012# MYSQL.INNODB_TABLE_STATS 1013# 1014CREATE DATABASE test_jfg; 1015CREATE TABLE test_jfg.test_jfg1 (id int(10) unsigned NOT NULL,PRIMARY 1016KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=1; 1017CREATE TABLE test_jfg.test_jfg2 (id int(10) unsigned NOT NULL,PRIMARY 1018KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=1 1019PARTITION BY RANGE ( id ) (PARTITION p1000 VALUES LESS THAN (1000) 1020ENGINE = InnoDB,PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = 1021InnoDB); 1022SELECT database_name, table_name FROM mysql.innodb_table_stats WHERE 1023database_name = 'test_jfg'; 1024database_name table_name 1025test_jfg test_jfg1 1026test_jfg test_jfg2#P#p1000 1027test_jfg test_jfg2#P#pmax 1028RENAME TABLE test_jfg.test_jfg1 TO test_jfg.test_jfg11; 1029RENAME TABLE test_jfg.test_jfg2 TO test_jfg.test_jfg12; 1030SELECT database_name, table_name FROM mysql.innodb_table_stats WHERE 1031database_name = 'test_jfg'; 1032database_name table_name 1033test_jfg test_jfg11 1034test_jfg test_jfg12#P#p1000 1035test_jfg test_jfg12#P#pmax 1036DROP DATABASE test_jfg; 1037set global innodb_stats_persistent= @innodb_stats_persistent_save; 1038set global innodb_stats_persistent_sample_pages= 1039@innodb_stats_persistent_sample_pages_save; 1040create table t1 (a int) engine=innodb; 1041create table t2 ( 1042b int, 1043c int, 1044d bit not null default 0, 1045v bit as (d) virtual, 1046key (b,v) 1047) engine=innodb partition by hash (b); 1048insert into t1 values (1),(2); 1049insert into t2 (b,c,d) values (1,1,0),(2,2,0); 1050explain select t1.* from t1 join t2 on (v = a); 1051id select_type table type possible_keys key key_len ref rows Extra 10521 SIMPLE t1 ALL NULL NULL NULL NULL 2 10531 SIMPLE t2 index NULL b 7 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) 1054select t1.* from t1 join t2 on (v = a); 1055a 1056drop table t1, t2; 1057# 1058# End of 10.2 tests 1059# 1060# 1061# MDEV-16241 Assertion `inited==RND' failed in handler::ha_rnd_end() 1062# 1063CREATE TABLE t1 (pk INT PRIMARY KEY, x INT, y INT, z INT, KEY (x), KEY (y, z)) 1064WITH SYSTEM VERSIONING 1065PARTITION BY SYSTEM_TIME (PARTITION p1 HISTORY, PARTITION pn CURRENT); 1066INSERT INTO t1 VALUES (1, 7, 8, 9), (2, NULL, NULL, NULL), (3, NULL, NULL, NULL); 1067SELECT COUNT(*) FROM t1 WHERE x IS NULL AND y IS NULL AND z IS NULL; 1068COUNT(*) 10692 1070DROP TABLE t1; 1071# 1072# MDEV-18244 Server crashes in ha_innobase::update_thd / ... / ha_partition::update_next_auto_inc_val 1073# 1074CREATE TABLE t1 (a INT) 1075ENGINE=InnoDB 1076PARTITION BY RANGE (a) ( 1077PARTITION p0 VALUES LESS THAN (6), 1078PARTITION pn VALUES LESS THAN MAXVALUE 1079); 1080INSERT INTO t1 VALUES (4),(5),(6); 1081ALTER TABLE t1 MODIFY a INT AUTO_INCREMENT PRIMARY KEY; 1082UPDATE t1 PARTITION (p0) SET a = 3 WHERE a = 5; 1083INSERT INTO t1 PARTITION(p0) VALUES (); 1084ERROR HY000: Found a row not matching the given partition set 1085INSERT INTO t1 PARTITION(p0) VALUES (-1); 1086INSERT INTO t1 VALUES (); 1087SELECT * FROM t1; 1088a 1089-1 10903 10914 10926 10937 1094DROP TABLE t1; 1095# 1096# End of 10.3 tests 1097# 1098