diff options
Diffstat (limited to 'mysql-test/suite/parts')
28 files changed, 971 insertions, 614 deletions
diff --git a/mysql-test/suite/parts/inc/partition_decimal.inc b/mysql-test/suite/parts/inc/partition_decimal.inc index 93e9e48c..4ad2a000 100644 --- a/mysql-test/suite/parts/inc/partition_decimal.inc +++ b/mysql-test/suite/parts/inc/partition_decimal.inc @@ -6,9 +6,11 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values (999999.9999), (-999999.9999), (123456.7899), (-123456.7899), (-1.5), (1), (0), (-1), (1.5), (1234.567), (-1234.567); +--sorted_result select * from t1; select * from t1 where a=1234.567; delete from t1 where a=1234.567; +--sorted_result select * from t1; drop table t1; @@ -16,9 +18,11 @@ eval create table t2 (a decimal(18,9) not null, primary key(a)) engine=$engine partition by key (a) partitions 10; show create table t2; insert into t2 values (999999999.999999999), (-999999999.999999999), (-1.5), (-1), (0), (1.5), (1234.567), (-1234.567); +--sorted_result select * from t2; select * from t2 where a=1234.567; delete from t2 where a=1234.567; +--sorted_result select * from t2; delete from t2; let $count=$maxrows; diff --git a/mysql-test/suite/parts/inc/partition_double.inc b/mysql-test/suite/parts/inc/partition_double.inc index 9e43887b..dd2fd100 100644 --- a/mysql-test/suite/parts/inc/partition_double.inc +++ b/mysql-test/suite/parts/inc/partition_double.inc @@ -6,9 +6,11 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values (-2.2250738585072014E+208), (-2.2250738585072014E-208), (-1.5), (-1), (0), (1.5), (1234.567), (2.2250738585072014E+208); +--sorted_result select * from t1; select * from t1 where a=1.5; delete from t1 where a=1.5; +--sorted_result select * from t1; drop table t1; @@ -16,9 +18,11 @@ eval create table t2 (a double not null, primary key(a)) engine=$engine partition by key (a) partitions 10; show create table t2; insert into t2 values (-2.2250738585072014E+208), (-2.2250738585072014E-208), (-1.5), (-1), (0), (1.5), (1234.567), (2.2250738585072014E+208); +--sorted_result select * from t2; select * from t2 where a=1234.567; delete from t2 where a=1234.567; +--sorted_result select * from t2; delete from t2; let $count=$maxrows; diff --git a/mysql-test/suite/parts/inc/partition_key_16col.inc b/mysql-test/suite/parts/inc/partition_key_16col.inc index 988dc455..e7917451 100644 --- a/mysql-test/suite/parts/inc/partition_key_16col.inc +++ b/mysql-test/suite/parts/inc/partition_key_16col.inc @@ -10,6 +10,7 @@ insert into t1 values ('1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127,'1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127, 'liuugbzvdmrlti b itiortudirtfgtibm dfi'), ('1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124,'1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, 'd,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr'), ('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m'); +--sorted_result select * from t1; select * from t1 where a<19851231; drop table t1; diff --git a/mysql-test/suite/parts/inc/partition_key_32col.inc b/mysql-test/suite/parts/inc/partition_key_32col.inc index 0acedefa..880751db 100644 --- a/mysql-test/suite/parts/inc/partition_key_32col.inc +++ b/mysql-test/suite/parts/inc/partition_key_32col.inc @@ -28,6 +28,7 @@ insert into t1 values ('1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127,'1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127, '1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127, '1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127, 'liuugbzvdmrlti b itiortudirtfgtibm dfi'), ('1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, '1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, '1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, '1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, 'd,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr'), ('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m'); +--sorted_result select * from t1; select * from t1 where a<19851231; drop table t1; diff --git a/mysql-test/suite/parts/inc/partition_key_4col.inc b/mysql-test/suite/parts/inc/partition_key_4col.inc index a94ab581..706c8add 100644 --- a/mysql-test/suite/parts/inc/partition_key_4col.inc +++ b/mysql-test/suite/parts/inc/partition_key_4col.inc @@ -10,6 +10,7 @@ insert into t1 values ('1983-12-31', 'cdef', 'srtbvsr', 'w'), ('1980-10-14', 'fgbbd', 'dtzndtz', 'w'), ('2000-06-15', 'jukg','zikhuk','m'); +--sorted_result select * from t1; select * from t1 where a<19851231; drop table t1; diff --git a/mysql-test/suite/parts/inc/partition_key_8col.inc b/mysql-test/suite/parts/inc/partition_key_8col.inc index fcbab7c3..1e49ee0b 100644 --- a/mysql-test/suite/parts/inc/partition_key_8col.inc +++ b/mysql-test/suite/parts/inc/partition_key_8col.inc @@ -10,6 +10,7 @@ insert into t1 values ('1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127, 'liuugbzvdmrlti b itiortudirtfgtibm dfi'), ('1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, 'd,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr'), ('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m' ); +--sorted_result select * from t1; select * from t1 where a<19851231; drop table t1; diff --git a/mysql-test/suite/parts/inc/partition_time.inc b/mysql-test/suite/parts/inc/partition_time.inc index 674fe546..5f9466ba 100644 --- a/mysql-test/suite/parts/inc/partition_time.inc +++ b/mysql-test/suite/parts/inc/partition_time.inc @@ -57,7 +57,7 @@ dec $count; commit; --enable_query_log select count(*) from t3; -select * from t3; +select a, second(a), if(second(a)<16,1,if(second(a)<31,2,if(second(a)<45,3,4))) from t3; drop table t3; eval create table t4 (a time not null, primary key(a)) engine=$engine diff --git a/mysql-test/suite/parts/inc/partition_timestamp.inc b/mysql-test/suite/parts/inc/partition_timestamp.inc index fb1bf391..53334c13 100644 --- a/mysql-test/suite/parts/inc/partition_timestamp.inc +++ b/mysql-test/suite/parts/inc/partition_timestamp.inc @@ -6,9 +6,11 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values ('1975-01-01 21:21:21'), ('2020-12-31 12:10:30'), ('1980-10-14 03:03'), ('2000-06-15 23:59'); +--sorted_result select * from t1; select * from t1 where a=19801014030300; delete from t1 where a=19801014030300; +--sorted_result select * from t1; drop table t1; @@ -16,9 +18,11 @@ eval create table t2 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE C partition by key (a) partitions 12; show create table t2; insert into t2 values ('1975-01-01 0:1:1'), ('2020-12-31 10:11:12'), ('1980-10-14 13:14:15'), ('2000-06-15 14:15:16'); +--sorted_result select * from t2; select * from t2 where a='1980-10-14 13:14:15'; delete from t2 where a='1980-10-14 13:14:15'; +--sorted_result select * from t2; delete from t2; let $count=59; @@ -33,6 +37,7 @@ dec $count; commit; --enable_query_log select count(*) from t2; +--sorted_result select * from t2; drop table t2; diff --git a/mysql-test/suite/parts/r/alter_data_directory_innodb.result b/mysql-test/suite/parts/r/alter_data_directory_innodb.result index 8c07093f..e8f97637 100644 --- a/mysql-test/suite/parts/r/alter_data_directory_innodb.result +++ b/mysql-test/suite/parts/r/alter_data_directory_innodb.result @@ -40,6 +40,8 @@ t CREATE TABLE `t` ( PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) SET @TMP = @@GLOBAL.INNODB_FILE_PER_TABLE; SET GLOBAL INNODB_FILE_PER_TABLE=OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; Warnings: Warning 1280 Name 'pk' ignored for PRIMARY key. @@ -53,6 +55,8 @@ t CREATE TABLE `t` ( (PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB, PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) SET GLOBAL INNODB_FILE_PER_TABLE=@TMP; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB, PARTITION p2 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB diff --git a/mysql-test/suite/parts/r/alter_table.result b/mysql-test/suite/parts/r/alter_table.result index 8f633370..0a78be2b 100644 --- a/mysql-test/suite/parts/r/alter_table.result +++ b/mysql-test/suite/parts/r/alter_table.result @@ -370,3 +370,185 @@ connection default; drop user u@localhost; drop database db; # End of 10.11 tests +# +# MDEV-22164 without validation for exchange partition/convert in +# +call mtr.add_suppression('was altered WITHOUT'); +create table validation(x int); +drop table validation; +create table t (a int primary key); +create table tp (a int primary key) partition by range (a) ( +partition p0 values less than (100), +p1 values less than (300)); +insert into t values (1), (99); +insert into tp values (2), (200); +select * from t order by a; +a +1 +99 +select * from tp partition (p0) order by a; +a +2 +select * from tp partition (p1) order by a; +a +200 +alter table tp exchange partition p0 with table t; +select * from t order by a; +a +2 +select * from tp partition (p0) order by a; +a +1 +99 +select * from tp partition (p1) order by a; +a +200 +alter table tp exchange partition p0 with table t; +select * from t order by a; +a +1 +99 +select * from tp partition (p0) order by a; +a +2 +select * from tp partition (p1) order by a; +a +200 +alter table tp exchange partition p1 with table t; +ERROR HY000: Found a row that does not match the partition +alter table tp exchange partition p1 with table t with validation; +ERROR HY000: Found a row that does not match the partition +alter table tp exchange partition p1 with table t without validation; +FOUND 1 /Table `test`.`tp` was altered WITHOUT VALIDATION: the table might be corrupted/ in mysqld.1.err +select * from t order by a; +a +200 +select * from tp partition (p1) order by a; +a +1 +99 +alter table tp check partition p0; +Table Op Msg_type Msg_text +test.tp check status OK +alter table tp check partition p1; +Table Op Msg_type Msg_text +test.tp check error Found a misplaced row +test.tp check error Partition p1 returned error +test.tp check error Upgrade required. Please do "REPAIR TABLE `tp`" or dump/reload to fix it! +alter table tp exchange partition p1 with table t with validation; +alter table tp exchange partition p1 with table t with validation; +ERROR HY000: Found a row that does not match the partition +select * from t order by a; +a +1 +99 +select * from tp partition (p1) order by a; +a +200 +create or replace procedure validation() +alter table tp exchange partition p1 with table t with validation; +create or replace procedure without_validation() +alter table tp exchange partition p1 with table t without validation; +call validation; +ERROR HY000: Found a row that does not match the partition +call without_validation; +FOUND 2 /Table `test`.`tp` was altered WITHOUT VALIDATION: the table might be corrupted/ in mysqld.1.err +call validation; +call validation; +ERROR HY000: Found a row that does not match the partition +call without_validation; +FOUND 3 /Table `test`.`tp` was altered WITHOUT VALIDATION: the table might be corrupted/ in mysqld.1.err +select * from t order by a; +a +200 +select * from tp partition (p1) order by a; +a +1 +99 +call validation; +select * from t order by a; +a +1 +99 +select * from tp partition (p1) order by a; +a +200 +drop procedure validation; +drop procedure without_validation; +prepare validation from "alter table tp exchange partition p1 with table t with validation"; +prepare without_validation from "alter table tp exchange partition p1 with table t without validation"; +execute validation; +ERROR HY000: Found a row that does not match the partition +execute without_validation; +FOUND 4 /Table `test`.`tp` was altered WITHOUT VALIDATION: the table might be corrupted/ in mysqld.1.err +execute validation; +execute validation; +ERROR HY000: Found a row that does not match the partition +execute without_validation; +FOUND 5 /Table `test`.`tp` was altered WITHOUT VALIDATION: the table might be corrupted/ in mysqld.1.err +select * from t order by a; +a +200 +select * from tp partition (p1) order by a; +a +1 +99 +execute validation; +select * from t order by a; +a +1 +99 +select * from tp partition (p1) order by a; +a +200 +drop prepare validation; +drop prepare without_validation; +alter table tp convert table t to partition p2 values less than (maxvalue); +ERROR HY000: Found a row that does not match the partition +alter table tp convert table t to partition p2 values less than (maxvalue) with validation; +ERROR HY000: Found a row that does not match the partition +alter table tp convert table t to partition p2 values less than (maxvalue) without validation; +FOUND 6 /Table `test`.`tp` was altered WITHOUT VALIDATION: the table might be corrupted/ in mysqld.1.err +select * from tp partition (p0) order by a; +a +2 +select * from tp partition (p1) order by a; +a +200 +select * from tp partition (p2) order by a; +a +1 +99 +create table t (a int primary key); +insert t values (1), (2); +create or replace table tp (a int primary key) +partition by hash(a) partitions 2; +insert tp values (1), (2), (3), (4); +alter table tp exchange partition p0 with table t; +ERROR HY000: Found a row that does not match the partition +alter table tp exchange partition p0 with table t without validation; +FOUND 7 /Table `test`.`tp` was altered WITHOUT VALIDATION: the table might be corrupted/ in mysqld.1.err +select * from t; +a +2 +4 +alter table tp exchange partition p0 with table t; +drop table tp; +create or replace table tp (a int primary key) +partition by list(a) ( +partition p0 values in (2, 3, 4), +partition p1 values in (12, 13, 14), +partition p2 values in (52, 53, 54)); +insert tp values (12), (2), (3), (4); +alter table tp exchange partition p0 with table t; +ERROR HY000: Table has no partition for value 0 +alter table tp exchange partition p0 with table t without validation; +FOUND 8 /Table `test`.`tp` was altered WITHOUT VALIDATION: the table might be corrupted/ in mysqld.1.err +select * from t; +a +2 +3 +4 +alter table tp exchange partition p0 with table t; +drop tables tp, t; +# End of 11.4 tests diff --git a/mysql-test/suite/parts/r/longname.result b/mysql-test/suite/parts/r/longname.result index eb850d6f..8524eb84 100644 --- a/mysql-test/suite/parts/r/longname.result +++ b/mysql-test/suite/parts/r/longname.result @@ -31,6 +31,8 @@ PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); ERROR HY000: The path specified for @0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@... is too long SET @file_per_table=@@GLOBAL.innodb_file_per_table; SET GLOBAL innodb_file_per_table=0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE mysqltest1.t1 (a INT) ENGINE=INNODB PARTITION BY RANGE (a) SUBPARTITION BY HASH(a) (PARTITION `$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$` @@ -41,6 +43,8 @@ SUBPARTITION `0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef`) ); SET GLOBAL innodb_file_per_table=@file_per_table; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SHOW CREATE TABLE mysqltest1.t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/suite/parts/r/optimizer.result b/mysql-test/suite/parts/r/optimizer.result index 42d85dbb..95f0e561 100644 --- a/mysql-test/suite/parts/r/optimizer.result +++ b/mysql-test/suite/parts/r/optimizer.result @@ -22,10 +22,10 @@ INSERT INTO t2 SELECT * FROM t1; # plans should be identical EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 2 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 2 Using where; Using index for group-by EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index +1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index for group-by FLUSH status; SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; a MAX(b) @@ -33,7 +33,7 @@ a MAX(b) # Should be no more than 4 reads. SHOW status LIKE 'handler_read_key'; Variable_name Value -Handler_read_key 2 +Handler_read_key 4 FLUSH status; SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; a MAX(b) @@ -41,5 +41,5 @@ a MAX(b) # Should be no more than 4 reads. SHOW status LIKE 'handler_read_key'; Variable_name Value -Handler_read_key 2 +Handler_read_key 4 DROP TABLE t1, t2; diff --git a/mysql-test/suite/parts/r/partition_basic_symlink_innodb.result b/mysql-test/suite/parts/r/partition_basic_symlink_innodb.result index 9d05d04c..6d361bb1 100644 --- a/mysql-test/suite/parts/r/partition_basic_symlink_innodb.result +++ b/mysql-test/suite/parts/r/partition_basic_symlink_innodb.result @@ -8,6 +8,8 @@ SET SESSION innodb_strict_mode = ON; # InnoDB only supports DATA DIRECTORY with innodb_file_per_table=ON # SET GLOBAL innodb_file_per_table = OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE t1 (c1 INT) ENGINE = InnoDB PARTITION BY HASH (c1) ( PARTITION p0 @@ -36,6 +38,8 @@ Error 6 Error on delete of 'MYSQLD_DATADIR/test/t1.par' (Errcode: 2 "No such fil # SET SESSION innodb_strict_mode = OFF; SET GLOBAL innodb_file_per_table = ON; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE t1 (c1 INT) ENGINE = InnoDB PARTITION BY HASH (c1) (PARTITION p0 @@ -134,6 +138,8 @@ DROP TABLE t1; # properly when used with DATA DIRECTORY # SET GLOBAL innodb_file_per_table = ON; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE t1 ( myid INT(11) NOT NULL, @@ -326,4 +332,6 @@ DROP TABLE t1, t2; # Cleanup # SET GLOBAL innodb_file_per_table=@file_per_table; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SET SESSION innodb_strict_mode=@strict_mode; diff --git a/mysql-test/suite/parts/r/partition_char_innodb.result b/mysql-test/suite/parts/r/partition_char_innodb.result Binary files differindex 3131ba58..da6f9bb6 100644 --- a/mysql-test/suite/parts/r/partition_char_innodb.result +++ b/mysql-test/suite/parts/r/partition_char_innodb.result diff --git a/mysql-test/suite/parts/r/partition_datetime_innodb.result b/mysql-test/suite/parts/r/partition_datetime_innodb.result index 8779bfea..f00b7a3e 100644 --- a/mysql-test/suite/parts/r/partition_datetime_innodb.result +++ b/mysql-test/suite/parts/r/partition_datetime_innodb.result @@ -145,19 +145,19 @@ t1 CREATE TABLE `t1` ( insert into t1 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15'); select * from t1; a -1975-01-01 1980-10-14 -2000-06-15 2020-12-31 +1975-01-01 +2000-06-15 select * from t1 where a=19801014; a 1980-10-14 delete from t1 where a=19801014; select * from t1; a +2020-12-31 1975-01-01 2000-06-15 -2020-12-31 drop table t1; create table t2 (a date not null, primary key(a)) engine='InnoDB' partition by key (a) partitions 12; @@ -174,8 +174,8 @@ select * from t2; a 1975-01-01 1980-10-14 -2000-06-15 2020-12-31 +2000-06-15 select * from t2 where a='1980-10-14'; a 1980-10-14 @@ -183,8 +183,8 @@ delete from t2 where a='1980-10-14'; select * from t2; a 1975-01-01 -2000-06-15 2020-12-31 +2000-06-15 delete from t2; 28 inserts; select count(*) from t2; @@ -192,90 +192,90 @@ count(*) 84 select * from t2; a -1970-01-01 -1970-01-02 -1970-01-03 1970-01-04 -1970-01-05 -1970-01-06 -1970-01-07 -1970-01-08 -1970-01-09 -1970-01-10 -1970-01-11 1970-01-12 -1970-01-13 1970-01-14 -1970-01-15 -1970-01-16 -1970-01-17 -1970-01-18 -1970-01-19 -1970-01-20 -1970-01-21 -1970-01-22 -1970-01-23 1970-01-24 -1970-01-25 -1970-01-26 -1970-01-27 1970-01-28 -1970-02-01 1970-02-02 -1970-02-03 1970-02-04 -1970-02-05 -1970-02-06 -1970-02-07 -1970-02-08 -1970-02-09 1970-02-10 -1970-02-11 -1970-02-12 -1970-02-13 1970-02-14 -1970-02-15 1970-02-16 -1970-02-17 -1970-02-18 -1970-02-19 -1970-02-20 -1970-02-21 -1970-02-22 -1970-02-23 -1970-02-24 -1970-02-25 -1970-02-26 -1970-02-27 -1970-02-28 -1970-03-01 -1970-03-02 -1970-03-03 1970-03-04 -1970-03-05 1970-03-06 +1970-03-12 +1970-01-13 +1970-01-27 +1970-02-03 +1970-02-09 +1970-02-13 +1970-02-21 +1970-03-05 1970-03-07 -1970-03-08 1970-03-09 -1970-03-10 -1970-03-11 -1970-03-12 -1970-03-13 -1970-03-14 1970-03-15 +1970-03-23 +1970-01-06 +1970-01-08 +1970-01-16 +1970-01-18 +1970-01-22 +1970-02-06 +1970-02-12 +1970-02-20 +1970-02-22 +1970-02-28 +1970-03-02 +1970-03-08 1970-03-16 -1970-03-17 -1970-03-18 -1970-03-19 1970-03-20 -1970-03-21 1970-03-22 -1970-03-23 1970-03-24 +1970-01-05 +1970-01-07 +1970-01-11 +1970-01-15 +1970-01-21 +1970-02-01 +1970-02-11 +1970-02-15 +1970-02-19 +1970-03-03 +1970-03-11 +1970-03-13 +1970-03-17 +1970-03-19 1970-03-25 -1970-03-26 1970-03-27 +1970-01-02 +1970-01-10 +1970-01-20 +1970-01-26 +1970-02-08 +1970-02-18 +1970-02-24 +1970-02-26 +1970-03-10 +1970-03-14 +1970-03-18 +1970-03-26 1970-03-28 +1970-01-01 +1970-01-03 +1970-01-09 +1970-01-17 +1970-01-19 +1970-01-23 +1970-01-25 +1970-02-05 +1970-02-07 +1970-02-17 +1970-02-23 +1970-02-25 +1970-02-27 +1970-03-01 +1970-03-21 drop table t2; create table t3 (a date not null, primary key(a)) engine='InnoDB' partition by range (month(a)) subpartition by key (a) @@ -304,18 +304,18 @@ count(*) 12 select * from t3; a -1970-01-01 1970-02-01 +1970-01-01 1970-03-01 1970-04-01 1970-05-01 1970-06-01 +1970-09-01 1970-07-01 1970-08-01 -1970-09-01 +1970-12-01 1970-10-01 1970-11-01 -1970-12-01 drop table t3; create table t4 (a date not null, primary key(a)) engine='InnoDB' partition by list (month(a)) subpartition by key (a) @@ -344,18 +344,18 @@ count(*) 12 select * from t4; a -1970-01-01 1970-02-01 +1970-01-01 1970-03-01 1970-04-01 1970-05-01 1970-06-01 +1970-09-01 1970-07-01 1970-08-01 -1970-09-01 +1970-12-01 1970-10-01 1970-11-01 -1970-12-01 drop table t4; create table t1 (a time not null, primary key(a)) engine='InnoDB' partition by key (a) ( @@ -378,18 +378,18 @@ insert into t1 values ('21:21:21'), ('12:10:30'), ('03:03:03'), ('23:59'); select * from t1; a 03:03:03 -12:10:30 21:21:21 23:59:00 +12:10:30 select * from t1 where a=030303; a 03:03:03 delete from t1 where a=030303; select * from t1; a -12:10:30 21:21:21 23:59:00 +12:10:30 drop table t1; create table t2 (a time not null, primary key(a)) engine='InnoDB' partition by key (a) partitions 12; @@ -404,19 +404,19 @@ PARTITIONS 12 insert into t2 values ('0:1:1'), ('10:11:12'), ('13:14:15'), ('14:15:16'); select * from t2; a -00:01:01 10:11:12 13:14:15 14:15:16 +00:01:01 select * from t2 where a='13:14:15'; a 13:14:15 delete from t2 where a='13:14:15'; select * from t2; a -00:01:01 10:11:12 14:15:16 +00:01:01 delete from t2; 59 inserts; select count(*) from t2; @@ -424,65 +424,65 @@ count(*) 59 select * from t2; a -00:01:01 -00:01:02 -00:01:03 -00:01:04 +00:01:15 +00:01:23 +00:01:39 +00:01:47 +00:01:59 +00:01:16 +00:01:24 +00:01:40 +00:01:48 00:01:05 -00:01:06 -00:01:07 -00:01:08 -00:01:09 -00:01:10 -00:01:11 -00:01:12 00:01:13 +00:01:21 +00:01:37 +00:01:45 +00:01:57 00:01:14 -00:01:15 -00:01:16 -00:01:17 -00:01:18 +00:01:22 +00:01:38 +00:01:46 +00:01:58 +00:01:03 +00:01:11 00:01:19 +00:01:35 +00:01:43 +00:01:55 +00:01:04 +00:01:12 00:01:20 -00:01:21 -00:01:22 -00:01:23 -00:01:24 -00:01:25 -00:01:26 -00:01:27 -00:01:28 +00:01:36 +00:01:44 +00:01:56 +00:01:01 +00:01:09 00:01:29 -00:01:30 -00:01:31 -00:01:32 00:01:33 -00:01:34 -00:01:35 -00:01:36 -00:01:37 -00:01:38 -00:01:39 -00:01:40 00:01:41 +00:01:53 +00:01:02 +00:01:10 +00:01:18 +00:01:34 00:01:42 -00:01:43 -00:01:44 -00:01:45 -00:01:46 -00:01:47 -00:01:48 -00:01:49 -00:01:50 +00:01:54 +00:01:07 +00:01:27 +00:01:31 00:01:51 +00:01:08 +00:01:28 +00:01:32 00:01:52 -00:01:53 -00:01:54 -00:01:55 -00:01:56 -00:01:57 -00:01:58 -00:01:59 +00:01:17 +00:01:25 +00:01:49 +00:01:06 +00:01:26 +00:01:30 +00:01:50 drop table t2; create table t3 (a time not null, primary key(a)) engine='InnoDB' partition by range (second(a)) subpartition by key (a) @@ -509,67 +509,67 @@ SUBPARTITIONS 3 select count(*) from t3; count(*) 59 -select * from t3; -a -10:00:01 -10:00:02 -10:00:03 -10:00:04 -10:00:05 -10:00:06 -10:00:07 -10:00:08 -10:00:09 -10:00:10 -10:00:11 -10:00:12 -10:00:13 -10:00:14 -10:00:15 -10:00:16 -10:00:17 -10:00:18 -10:00:19 -10:00:20 -10:00:21 -10:00:22 -10:00:23 -10:00:24 -10:00:25 -10:00:26 -10:00:27 -10:00:28 -10:00:29 -10:00:30 -10:00:31 -10:00:32 -10:00:33 -10:00:34 -10:00:35 -10:00:36 -10:00:37 -10:00:38 -10:00:39 -10:00:40 -10:00:41 -10:00:42 -10:00:43 -10:00:44 -10:00:45 -10:00:46 -10:00:47 -10:00:48 -10:00:49 -10:00:50 -10:00:51 -10:00:52 -10:00:53 -10:00:54 -10:00:55 -10:00:56 -10:00:57 -10:00:58 -10:00:59 +select a, second(a), if(second(a)<16,1,if(second(a)<31,2,if(second(a)<45,3,4))) from t3; +a second(a) if(second(a)<16,1,if(second(a)<31,2,if(second(a)<45,3,4))) +10:00:01 1 1 +10:00:06 6 1 +10:00:07 7 1 +10:00:14 14 1 +10:00:15 15 1 +10:00:02 2 1 +10:00:03 3 1 +10:00:08 8 1 +10:00:09 9 1 +10:00:04 4 1 +10:00:05 5 1 +10:00:10 10 1 +10:00:11 11 1 +10:00:12 12 1 +10:00:13 13 1 +10:00:20 20 2 +10:00:21 21 2 +10:00:26 26 2 +10:00:27 27 2 +10:00:16 16 2 +10:00:17 17 2 +10:00:22 22 2 +10:00:23 23 2 +10:00:28 28 2 +10:00:29 29 2 +10:00:18 18 2 +10:00:19 19 2 +10:00:24 24 2 +10:00:25 25 2 +10:00:30 30 2 +10:00:32 32 3 +10:00:33 33 3 +10:00:35 35 3 +10:00:40 40 3 +10:00:41 41 3 +10:00:34 34 3 +10:00:36 36 3 +10:00:37 37 3 +10:00:42 42 3 +10:00:43 43 3 +10:00:31 31 3 +10:00:38 38 3 +10:00:39 39 3 +10:00:44 44 3 +10:00:45 45 4 +10:00:46 46 4 +10:00:48 48 4 +10:00:49 49 4 +10:00:54 54 4 +10:00:55 55 4 +10:00:50 50 4 +10:00:51 51 4 +10:00:56 56 4 +10:00:57 57 4 +10:00:59 59 4 +10:00:47 47 4 +10:00:52 52 4 +10:00:53 53 4 +10:00:58 58 4 drop table t3; create table t4 (a time not null, primary key(a)) engine='InnoDB' partition by list (second(a)) subpartition by key (a) @@ -599,64 +599,64 @@ count(*) select * from t4; a 10:00:01 -10:00:02 -10:00:03 -10:00:04 -10:00:05 10:00:06 10:00:07 +10:00:14 +10:00:15 +10:00:02 +10:00:03 10:00:08 10:00:09 +10:00:04 +10:00:05 10:00:10 10:00:11 10:00:12 10:00:13 -10:00:14 -10:00:15 -10:00:16 -10:00:17 -10:00:18 -10:00:19 10:00:20 10:00:21 -10:00:22 -10:00:23 -10:00:24 -10:00:25 10:00:26 10:00:27 +10:00:16 +10:00:17 +10:00:22 +10:00:23 10:00:28 10:00:29 +10:00:18 +10:00:19 +10:00:24 +10:00:25 10:00:30 -10:00:31 10:00:32 10:00:33 -10:00:34 10:00:35 -10:00:36 -10:00:37 -10:00:38 -10:00:39 10:00:40 10:00:41 +10:00:34 +10:00:36 +10:00:37 10:00:42 10:00:43 +10:00:31 +10:00:38 +10:00:39 10:00:44 10:00:45 10:00:46 -10:00:47 10:00:48 10:00:49 -10:00:50 -10:00:51 -10:00:52 -10:00:53 10:00:54 10:00:55 +10:00:50 +10:00:51 10:00:56 10:00:57 -10:00:58 10:00:59 +10:00:47 +10:00:52 +10:00:53 +10:00:58 drop table t4; create table t1 (a datetime not null, primary key(a)) engine='InnoDB' partition by key (a) ( @@ -679,9 +679,9 @@ insert into t1 values ('1975-01-01 21:21:21'), ('2020-12-31 12:10:30'), ('1980-1 select * from t1; a 1975-01-01 21:21:21 -1980-10-14 03:03:00 2000-06-15 23:59:00 2020-12-31 12:10:30 +1980-10-14 03:03:00 select * from t1 where a=19801014030300; a 1980-10-14 03:03:00 @@ -705,19 +705,19 @@ PARTITIONS 12 insert into t2 values ('1975-01-01 0:1:1'), ('2020-12-31 10:11:12'), ('1980-10-14 13:14:15'), ('2000-06-15 14:15:16'); select * from t2; a -1975-01-01 00:01:01 -1980-10-14 13:14:15 -2000-06-15 14:15:16 2020-12-31 10:11:12 +2000-06-15 14:15:16 +1980-10-14 13:14:15 +1975-01-01 00:01:01 select * from t2 where a='1980-10-14 13:14:15'; a 1980-10-14 13:14:15 delete from t2 where a='1980-10-14 13:14:15'; select * from t2; a -1975-01-01 00:01:01 -2000-06-15 14:15:16 2020-12-31 10:11:12 +2000-06-15 14:15:16 +1975-01-01 00:01:01 delete from t2; 59 inserts; select count(*) from t2; @@ -725,65 +725,65 @@ count(*) 59 select * from t2; a -1970-01-01 00:00:01 -1970-01-01 00:00:02 -1970-01-01 00:00:03 -1970-01-01 00:00:04 -1970-01-01 00:00:05 -1970-01-01 00:00:06 -1970-01-01 00:00:07 -1970-01-01 00:00:08 1970-01-01 00:00:09 -1970-01-01 00:00:10 -1970-01-01 00:00:11 -1970-01-01 00:00:12 1970-01-01 00:00:13 -1970-01-01 00:00:14 -1970-01-01 00:00:15 -1970-01-01 00:00:16 -1970-01-01 00:00:17 -1970-01-01 00:00:18 1970-01-01 00:00:19 -1970-01-01 00:00:20 -1970-01-01 00:00:21 -1970-01-01 00:00:22 1970-01-01 00:00:23 +1970-01-01 00:00:33 +1970-01-01 00:00:37 +1970-01-01 00:00:51 +1970-01-01 00:00:59 +1970-01-01 00:00:04 +1970-01-01 00:00:10 +1970-01-01 00:00:14 1970-01-01 00:00:24 -1970-01-01 00:00:25 -1970-01-01 00:00:26 -1970-01-01 00:00:27 1970-01-01 00:00:28 -1970-01-01 00:00:29 -1970-01-01 00:00:30 -1970-01-01 00:00:31 -1970-01-01 00:00:32 -1970-01-01 00:00:33 1970-01-01 00:00:34 -1970-01-01 00:00:35 -1970-01-01 00:00:36 -1970-01-01 00:00:37 1970-01-01 00:00:38 +1970-01-01 00:00:48 +1970-01-01 00:00:52 +1970-01-01 00:00:56 +1970-01-01 00:00:03 +1970-01-01 00:00:07 +1970-01-01 00:00:17 +1970-01-01 00:00:21 +1970-01-01 00:00:27 +1970-01-01 00:00:31 +1970-01-01 00:00:35 1970-01-01 00:00:39 -1970-01-01 00:00:40 1970-01-01 00:00:41 -1970-01-01 00:00:42 -1970-01-01 00:00:43 -1970-01-01 00:00:44 1970-01-01 00:00:45 +1970-01-01 00:00:08 +1970-01-01 00:00:12 +1970-01-01 00:00:18 +1970-01-01 00:00:22 +1970-01-01 00:00:32 +1970-01-01 00:00:36 +1970-01-01 00:00:42 1970-01-01 00:00:46 +1970-01-01 00:00:01 +1970-01-01 00:00:05 +1970-01-01 00:00:11 +1970-01-01 00:00:15 +1970-01-01 00:00:25 +1970-01-01 00:00:29 +1970-01-01 00:00:43 1970-01-01 00:00:47 -1970-01-01 00:00:48 1970-01-01 00:00:49 -1970-01-01 00:00:50 -1970-01-01 00:00:51 -1970-01-01 00:00:52 1970-01-01 00:00:53 -1970-01-01 00:00:54 1970-01-01 00:00:55 -1970-01-01 00:00:56 1970-01-01 00:00:57 +1970-01-01 00:00:02 +1970-01-01 00:00:06 +1970-01-01 00:00:16 +1970-01-01 00:00:20 +1970-01-01 00:00:26 +1970-01-01 00:00:30 +1970-01-01 00:00:40 +1970-01-01 00:00:44 +1970-01-01 00:00:50 +1970-01-01 00:00:54 1970-01-01 00:00:58 -1970-01-01 00:00:59 drop table t2; create table t3 (a datetime not null, primary key(a)) engine='InnoDB' partition by range (month(a)) subpartition by key (a) @@ -812,15 +812,15 @@ count(*) 12 select * from t3; a -1970-01-01 00:00:00 1970-02-01 00:00:00 +1970-01-01 00:00:00 1970-03-01 00:00:00 1970-04-01 00:00:00 1970-05-01 00:00:00 1970-06-01 00:00:00 -1970-07-01 00:00:00 1970-08-01 00:00:00 1970-09-01 00:00:00 +1970-07-01 00:00:00 1970-10-01 00:00:00 1970-11-01 00:00:00 1970-12-01 00:00:00 @@ -852,15 +852,15 @@ count(*) 12 select * from t4; a -1970-01-01 00:00:00 1970-02-01 00:00:00 +1970-01-01 00:00:00 1970-03-01 00:00:00 1970-04-01 00:00:00 1970-05-01 00:00:00 1970-06-01 00:00:00 -1970-07-01 00:00:00 1970-08-01 00:00:00 1970-09-01 00:00:00 +1970-07-01 00:00:00 1970-10-01 00:00:00 1970-11-01 00:00:00 1970-12-01 00:00:00 @@ -885,19 +885,19 @@ t1 CREATE TABLE `t1` ( insert into t1 values ('1975'), (2020), ('1980'), ('2000'); select * from t1; a -1975 1980 2000 2020 +1975 select * from t1 where a=1980; a 1980 delete from t1 where a=1980; select * from t1; a -1975 2000 2020 +1975 drop table t1; create table t2 (a year not null, primary key(a)) engine='InnoDB' partition by key (a) partitions 12; @@ -912,19 +912,19 @@ PARTITIONS 12 insert into t2 values ('1975'), ('2020'), ('1980'), ('2000'); select * from t2; a +2020 1975 1980 2000 -2020 select * from t2 where a='1980'; a 1980 delete from t2 where a='1980'; select * from t2; a +2020 1975 2000 -2020 delete from t2; 255 inserts; Warnings: @@ -934,259 +934,259 @@ count(*) 255 select * from t2; a -0000 -1902 -1903 -1904 -1905 -1906 -1907 -1908 1909 -1910 -1911 -1912 -1913 -1914 -1915 -1916 -1917 -1918 -1919 -1920 1921 -1922 -1923 -1924 -1925 -1926 -1927 +1933 +1945 +1957 +1969 +1981 +1993 +2001 +2013 +2029 +2041 +2053 +2065 +2077 +2089 +2100 +2101 +2102 +2103 +2114 +2132 +2133 +2134 +2135 +2146 +1904 +1916 1928 -1929 -1930 -1931 1932 -1933 -1934 -1935 -1936 -1937 -1938 -1939 1940 -1941 -1942 -1943 -1944 -1945 -1946 -1947 -1948 -1949 -1950 -1951 1952 -1953 -1954 -1955 -1956 -1957 -1958 -1959 -1960 -1961 -1962 -1963 1964 -1965 -1966 -1967 -1968 -1969 -1970 -1971 -1972 -1973 -1974 -1975 1976 -1977 -1978 -1979 -1980 -1981 -1982 -1983 -1984 -1985 -1986 -1987 1988 -1989 -1990 +2008 +2020 +2024 +2036 +2048 +2060 +2072 +2084 +1907 +1919 +1931 +1943 +1955 +1967 +1979 1991 -1992 -1993 -1994 -1995 -1996 -1997 -1998 1999 -2000 -2001 -2002 -2003 -2004 -2005 -2006 -2007 -2008 -2009 -2010 2011 -2012 -2013 -2014 -2015 -2016 -2017 +2027 +2039 +2051 +2063 +2075 +2087 +2097 +2099 +2110 +2111 +2112 +2113 +2123 +2129 +2131 +2142 +2143 +2144 +2145 +2147 +1902 +1914 +1926 +1938 +1950 +1962 +1974 +1986 +2006 2018 -2019 -2020 +2034 +2046 +2058 +2070 +2082 +1905 +1917 +1929 +1941 +1953 +1965 +1977 +1989 +1997 +2009 2021 -2022 -2023 -2024 2025 -2026 -2027 -2028 -2029 -2030 -2031 -2032 -2033 -2034 -2035 -2036 2037 -2038 -2039 -2040 -2041 -2042 -2043 -2044 -2045 -2046 -2047 -2048 2049 -2050 -2051 -2052 -2053 -2054 -2055 -2056 -2057 -2058 -2059 -2060 2061 -2062 -2063 -2064 -2065 -2066 -2067 -2068 -2069 -2070 -2071 -2072 2073 -2074 -2075 -2076 -2077 -2078 -2079 -2080 -2081 -2082 -2083 -2084 2085 -2086 -2087 -2088 -2089 -2090 -2091 2092 2093 2094 2095 -2096 -2097 -2098 -2099 -2100 -2101 -2102 -2103 +2120 +2121 +2124 +2125 +2126 +2127 +2152 +2153 +0000 +1912 +1924 +1936 +1948 +1960 +1972 +1984 +1996 +2004 +2016 +2032 +2044 +2056 +2068 +2080 +2116 +2148 +1903 +1915 +1927 +1939 +1951 +1963 +1975 +1987 +2007 +2019 +2023 +2035 +2047 +2059 +2071 +2083 +2155 +1910 +1922 +1934 +1946 +1958 +1970 +1982 +1994 +2002 +2014 +2030 +2042 +2054 +2066 +2078 +2090 +1913 +1925 +1937 +1949 +1961 +1973 +1985 +2005 +2017 +2033 +2045 +2057 +2069 +2081 2104 2105 2106 2107 -2108 -2109 -2110 -2111 -2112 -2113 -2114 -2115 -2116 2117 2118 2119 -2120 -2121 -2122 -2123 -2124 -2125 -2126 -2127 -2128 -2129 -2130 -2131 -2132 -2133 -2134 -2135 2136 2137 2138 2139 -2140 -2141 -2142 -2143 -2144 -2145 -2146 -2147 -2148 2149 2150 2151 -2152 -2153 +1908 +1920 +1944 +1956 +1968 +1980 +1992 +2000 +2012 +2028 +2040 +2052 +2064 +2076 +2088 +2096 +2098 +2108 +2128 +2130 +2140 +1911 +1923 +1935 +1947 +1959 +1971 +1983 +1995 +2003 +2015 +2031 +2043 +2055 +2067 +2079 +2091 +2115 +1906 +1918 +1930 +1942 +1954 +1966 +1978 +1990 +1998 +2010 +2022 +2026 +2038 +2050 +2062 +2074 +2086 +2109 +2122 +2141 2154 -2155 drop table t2; diff --git a/mysql-test/suite/parts/r/partition_datetime_myisam.result b/mysql-test/suite/parts/r/partition_datetime_myisam.result index 0d1dcf3e..9d8acb09 100644 --- a/mysql-test/suite/parts/r/partition_datetime_myisam.result +++ b/mysql-test/suite/parts/r/partition_datetime_myisam.result @@ -509,67 +509,67 @@ SUBPARTITIONS 3 select count(*) from t3; count(*) 59 -select * from t3; -a -10:00:01 -10:00:02 -10:00:03 -10:00:04 -10:00:05 -10:00:06 -10:00:07 -10:00:08 -10:00:09 -10:00:10 -10:00:11 -10:00:12 -10:00:13 -10:00:14 -10:00:15 -10:00:16 -10:00:17 -10:00:18 -10:00:19 -10:00:20 -10:00:21 -10:00:22 -10:00:23 -10:00:24 -10:00:25 -10:00:26 -10:00:27 -10:00:28 -10:00:29 -10:00:30 -10:00:31 -10:00:32 -10:00:33 -10:00:34 -10:00:35 -10:00:36 -10:00:37 -10:00:38 -10:00:39 -10:00:40 -10:00:41 -10:00:42 -10:00:43 -10:00:44 -10:00:45 -10:00:46 -10:00:47 -10:00:48 -10:00:49 -10:00:50 -10:00:51 -10:00:52 -10:00:53 -10:00:54 -10:00:55 -10:00:56 -10:00:57 -10:00:58 -10:00:59 +select a, second(a), if(second(a)<16,1,if(second(a)<31,2,if(second(a)<45,3,4))) from t3; +a second(a) if(second(a)<16,1,if(second(a)<31,2,if(second(a)<45,3,4))) +10:00:01 1 1 +10:00:02 2 1 +10:00:03 3 1 +10:00:04 4 1 +10:00:05 5 1 +10:00:06 6 1 +10:00:07 7 1 +10:00:08 8 1 +10:00:09 9 1 +10:00:10 10 1 +10:00:11 11 1 +10:00:12 12 1 +10:00:13 13 1 +10:00:14 14 1 +10:00:15 15 1 +10:00:16 16 2 +10:00:17 17 2 +10:00:18 18 2 +10:00:19 19 2 +10:00:20 20 2 +10:00:21 21 2 +10:00:22 22 2 +10:00:23 23 2 +10:00:24 24 2 +10:00:25 25 2 +10:00:26 26 2 +10:00:27 27 2 +10:00:28 28 2 +10:00:29 29 2 +10:00:30 30 2 +10:00:31 31 3 +10:00:32 32 3 +10:00:33 33 3 +10:00:34 34 3 +10:00:35 35 3 +10:00:36 36 3 +10:00:37 37 3 +10:00:38 38 3 +10:00:39 39 3 +10:00:40 40 3 +10:00:41 41 3 +10:00:42 42 3 +10:00:43 43 3 +10:00:44 44 3 +10:00:45 45 4 +10:00:46 46 4 +10:00:47 47 4 +10:00:48 48 4 +10:00:49 49 4 +10:00:50 50 4 +10:00:51 51 4 +10:00:52 52 4 +10:00:53 53 4 +10:00:54 54 4 +10:00:55 55 4 +10:00:56 56 4 +10:00:57 57 4 +10:00:58 58 4 +10:00:59 59 4 drop table t3; create table t4 (a time not null, primary key(a)) engine='MyISAM' partition by list (second(a)) subpartition by key (a) diff --git a/mysql-test/suite/parts/r/partition_decimal_innodb.result b/mysql-test/suite/parts/r/partition_decimal_innodb.result index c2f00a89..bce03461 100644 --- a/mysql-test/suite/parts/r/partition_decimal_innodb.result +++ b/mysql-test/suite/parts/r/partition_decimal_innodb.result @@ -18,11 +18,11 @@ t1 CREATE TABLE `t1` ( insert into t1 values (999999.9999), (-999999.9999), (123456.7899), (-123456.7899), (-1.5), (1), (0), (-1), (1.5), (1234.567), (-1234.567); select * from t1; a --999999.9999 --123456.7899 --1234.5670 --1.5000 -1.0000 +-1.5000 +-1234.5670 +-123456.7899 +-999999.9999 0.0000 1.0000 1.5000 @@ -35,11 +35,11 @@ a delete from t1 where a=1234.567; select * from t1; a --999999.9999 --123456.7899 --1234.5670 --1.5000 -1.0000 +-1.5000 +-1234.5670 +-123456.7899 +-999999.9999 0.0000 1.0000 1.5000 @@ -59,10 +59,10 @@ PARTITIONS 10 insert into t2 values (999999999.999999999), (-999999999.999999999), (-1.5), (-1), (0), (1.5), (1234.567), (-1234.567); select * from t2; a --999999999.999999999 --1234.567000000 --1.500000000 -1.000000000 +-1.500000000 +-1234.567000000 +-999999999.999999999 0.000000000 1.500000000 1234.567000000 @@ -73,10 +73,10 @@ a delete from t2 where a=1234.567; select * from t2; a --999999999.999999999 --1234.567000000 --1.500000000 -1.000000000 +-1.500000000 +-1234.567000000 +-999999999.999999999 0.000000000 1.500000000 999999999.999999999 diff --git a/mysql-test/suite/parts/r/partition_decimal_myisam.result b/mysql-test/suite/parts/r/partition_decimal_myisam.result index a5175079..90ea5ea8 100644 --- a/mysql-test/suite/parts/r/partition_decimal_myisam.result +++ b/mysql-test/suite/parts/r/partition_decimal_myisam.result @@ -18,11 +18,11 @@ t1 CREATE TABLE `t1` ( insert into t1 values (999999.9999), (-999999.9999), (123456.7899), (-123456.7899), (-1.5), (1), (0), (-1), (1.5), (1234.567), (-1234.567); select * from t1; a --999999.9999 --123456.7899 --1234.5670 --1.5000 -1.0000 +-1.5000 +-1234.5670 +-123456.7899 +-999999.9999 0.0000 1.0000 1.5000 @@ -35,11 +35,11 @@ a delete from t1 where a=1234.567; select * from t1; a --999999.9999 --123456.7899 --1234.5670 --1.5000 -1.0000 +-1.5000 +-1234.5670 +-123456.7899 +-999999.9999 0.0000 1.0000 1.5000 @@ -59,10 +59,10 @@ PARTITIONS 10 insert into t2 values (999999999.999999999), (-999999999.999999999), (-1.5), (-1), (0), (1.5), (1234.567), (-1234.567); select * from t2; a --999999999.999999999 --1234.567000000 --1.500000000 -1.000000000 +-1.500000000 +-1234.567000000 +-999999999.999999999 0.000000000 1.500000000 1234.567000000 @@ -73,10 +73,10 @@ a delete from t2 where a=1234.567; select * from t2; a --999999999.999999999 --1234.567000000 --1.500000000 -1.000000000 +-1.500000000 +-1234.567000000 +-999999999.999999999 0.000000000 1.500000000 999999999.999999999 diff --git a/mysql-test/suite/parts/r/partition_double_innodb.result b/mysql-test/suite/parts/r/partition_double_innodb.result index 7563109f..41834075 100644 --- a/mysql-test/suite/parts/r/partition_double_innodb.result +++ b/mysql-test/suite/parts/r/partition_double_innodb.result @@ -18,10 +18,10 @@ t1 CREATE TABLE `t1` ( insert into t1 values (-2.2250738585072014E+208), (-2.2250738585072014E-208), (-1.5), (-1), (0), (1.5), (1234.567), (2.2250738585072014E+208); select * from t1; a --2.2250738585072016e208 --1.5 -1 +-1.5 -2.2250738585072014e-208 +-2.2250738585072016e208 0 1.5 1234.567 @@ -32,10 +32,10 @@ a delete from t1 where a=1.5; select * from t1; a --2.2250738585072016e208 --1.5 -1 +-1.5 -2.2250738585072014e-208 +-2.2250738585072016e208 0 1234.567 2.2250738585072016e208 @@ -53,10 +53,10 @@ PARTITIONS 10 insert into t2 values (-2.2250738585072014E+208), (-2.2250738585072014E-208), (-1.5), (-1), (0), (1.5), (1234.567), (2.2250738585072014E+208); select * from t2; a --2.2250738585072016e208 --1.5 -1 +-1.5 -2.2250738585072014e-208 +-2.2250738585072016e208 0 1.5 1234.567 @@ -67,10 +67,10 @@ a delete from t2 where a=1234.567; select * from t2; a --2.2250738585072016e208 --1.5 -1 +-1.5 -2.2250738585072014e-208 +-2.2250738585072016e208 0 1.5 2.2250738585072016e208 diff --git a/mysql-test/suite/parts/r/partition_double_myisam.result b/mysql-test/suite/parts/r/partition_double_myisam.result index e9cf25e6..f2161d42 100644 --- a/mysql-test/suite/parts/r/partition_double_myisam.result +++ b/mysql-test/suite/parts/r/partition_double_myisam.result @@ -18,10 +18,10 @@ t1 CREATE TABLE `t1` ( insert into t1 values (-2.2250738585072014E+208), (-2.2250738585072014E-208), (-1.5), (-1), (0), (1.5), (1234.567), (2.2250738585072014E+208); select * from t1; a --2.2250738585072016e208 --1.5 -1 +-1.5 -2.2250738585072014e-208 +-2.2250738585072016e208 0 1.5 1234.567 @@ -32,10 +32,10 @@ a delete from t1 where a=1.5; select * from t1; a --2.2250738585072016e208 --1.5 -1 +-1.5 -2.2250738585072014e-208 +-2.2250738585072016e208 0 1234.567 2.2250738585072016e208 @@ -53,10 +53,10 @@ PARTITIONS 10 insert into t2 values (-2.2250738585072014E+208), (-2.2250738585072014E-208), (-1.5), (-1), (0), (1.5), (1234.567), (2.2250738585072014E+208); select * from t2; a --2.2250738585072016e208 --1.5 -1 +-1.5 -2.2250738585072014e-208 +-2.2250738585072016e208 0 1.5 1234.567 @@ -67,10 +67,10 @@ a delete from t2 where a=1234.567; select * from t2; a --2.2250738585072016e208 --1.5 -1 +-1.5 -2.2250738585072014e-208 +-2.2250738585072016e208 0 1.5 2.2250738585072016e208 diff --git a/mysql-test/suite/parts/r/partition_float_innodb.result b/mysql-test/suite/parts/r/partition_float_innodb.result index 7cdccdb8..c82609b4 100644 --- a/mysql-test/suite/parts/r/partition_float_innodb.result +++ b/mysql-test/suite/parts/r/partition_float_innodb.result @@ -18,10 +18,10 @@ t1 CREATE TABLE `t1` ( insert into t1 values (-3.402823466E+38), (3.402823466E+38), (-1.5), (-1), (0), (1), (1.5); select * from t1; a +0 -3.40282e38 -1.5 -1 -0 1 1.5 3.40282e38 @@ -31,10 +31,10 @@ a delete from t1 where a=1.5; select * from t1; a +0 -3.40282e38 -1.5 -1 -0 1 3.40282e38 drop table t1; @@ -51,10 +51,10 @@ PARTITIONS 10 insert into t2 values (-3.402823466E+38), (-3.402823466E+37), (-123.456), (0), (1234546.789), (123.456), (1.5); select * from t2; a +0 -3.40282e38 -3.40282e37 -123.456 -0 1.5 123.456 1234550 @@ -63,10 +63,10 @@ a delete from t2 where a=123.456; select * from t2; a +0 -3.40282e38 -3.40282e37 -123.456 -0 1.5 123.456 1234550 @@ -76,10 +76,10 @@ a delete from t2 where a=1.5; select * from t2; a +0 -3.40282e38 -3.40282e37 -123.456 -0 123.456 1234550 delete from t2; diff --git a/mysql-test/suite/parts/r/partition_special_innodb.result b/mysql-test/suite/parts/r/partition_special_innodb.result index 1d0c075d..504989e2 100644 --- a/mysql-test/suite/parts/r/partition_special_innodb.result +++ b/mysql-test/suite/parts/r/partition_special_innodb.result @@ -67,9 +67,9 @@ insert into t1 values ('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m' ); select * from t1; a b c d e f g h i +1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi -1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m select * from t1 where a<19851231; a b c d e f g h i @@ -117,9 +117,9 @@ insert into t1 values ('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m'); select * from t1; a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i -1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi -1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh +1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr +1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m select * from t1 where a<19851231; a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i @@ -197,9 +197,9 @@ insert into t1 values ('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m'); select * from t1; a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 a2 b2 c2 d2 e2 f2 g2 h2 a3 b3 c3 d3 e3 f3 g3 h3 i +1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi -1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m select * from t1 where a<19851231; a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 a2 b2 c2 d2 e2 f2 g2 h2 a3 b3 c3 d3 e3 f3 g3 h3 i diff --git a/mysql-test/suite/parts/r/partition_special_myisam.result b/mysql-test/suite/parts/r/partition_special_myisam.result index f6ceef4f..ce30977c 100644 --- a/mysql-test/suite/parts/r/partition_special_myisam.result +++ b/mysql-test/suite/parts/r/partition_special_myisam.result @@ -67,9 +67,9 @@ insert into t1 values ('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m' ); select * from t1; a b c d e f g h i +1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi -1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m select * from t1 where a<19851231; a b c d e f g h i @@ -117,9 +117,9 @@ insert into t1 values ('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m'); select * from t1; a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i -1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi -1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh +1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr +1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m select * from t1 where a<19851231; a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i @@ -197,9 +197,9 @@ insert into t1 values ('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m'); select * from t1; a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 a2 b2 c2 d2 e2 f2 g2 h2 a3 b3 c3 d3 e3 f3 g3 h3 i +1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi -1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m select * from t1 where a<19851231; a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 a2 b2 c2 d2 e2 f2 g2 h2 a3 b3 c3 d3 e3 f3 g3 h3 i diff --git a/mysql-test/suite/parts/r/percona_nonflushing_analyze_debug.result b/mysql-test/suite/parts/r/percona_nonflushing_analyze_debug.result index 070d5e8d..18afb41f 100644 --- a/mysql-test/suite/parts/r/percona_nonflushing_analyze_debug.result +++ b/mysql-test/suite/parts/r/percona_nonflushing_analyze_debug.result @@ -4,7 +4,7 @@ PARTITION p0 VALUES LESS THAN (3), PARTITION p1 VALUES LESS THAN (10)); INSERT INTO t1 VALUES (1), (2), (3), (4); connect con1,localhost,root; -SET DEBUG_SYNC="handler_ha_index_next_end SIGNAL idx_scan_in_progress WAIT_FOR finish_scan"; +SET DEBUG_SYNC="handler_rnd_next_end SIGNAL idx_scan_in_progress WAIT_FOR finish_scan"; SELECT * FROM t1; connection default; SET DEBUG_SYNC="now WAIT_FOR idx_scan_in_progress"; @@ -39,7 +39,7 @@ PARTITION p0 VALUES LESS THAN (3), PARTITION p1 VALUES LESS THAN (10)); INSERT INTO t2 VALUES (1), (2), (3), (4); connect con1,localhost,root; -SET DEBUG_SYNC="handler_ha_index_next_end SIGNAL idx_scan_in_progress WAIT_FOR finish_scan"; +SET DEBUG_SYNC="handler_rnd_next_end SIGNAL idx_scan_in_progress WAIT_FOR finish_scan"; SELECT * FROM t2; connection default; SET DEBUG_SYNC="now WAIT_FOR idx_scan_in_progress"; @@ -51,17 +51,17 @@ test.t2 analyze status OK set use_stat_tables=@tmp; SELECT * FROM t2; a -1 2 -3 +1 4 +3 SET DEBUG_SYNC="now SIGNAL finish_scan"; connection con1; a -1 2 -3 +1 4 +3 disconnect con1; connection default; SET DEBUG_SYNC='reset'; diff --git a/mysql-test/suite/parts/r/reorganize_partition_innodb.result b/mysql-test/suite/parts/r/reorganize_partition_innodb.result index b56cb6bb..b2233781 100644 --- a/mysql-test/suite/parts/r/reorganize_partition_innodb.result +++ b/mysql-test/suite/parts/r/reorganize_partition_innodb.result @@ -40,6 +40,8 @@ t CREATE TABLE `t` ( PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) SET @TMP = @@GLOBAL.INNODB_FILE_PER_TABLE; SET GLOBAL INNODB_FILE_PER_TABLE=OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; Warnings: Warning 1280 Name 'pk' ignored for PRIMARY key. @@ -53,6 +55,8 @@ t CREATE TABLE `t` ( (PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB, PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) SET GLOBAL INNODB_FILE_PER_TABLE=@TMP; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB, PARTITION p2 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB diff --git a/mysql-test/suite/parts/t/alter_table.test b/mysql-test/suite/parts/t/alter_table.test index 9c131c73..f1da7cc3 100644 --- a/mysql-test/suite/parts/t/alter_table.test +++ b/mysql-test/suite/parts/t/alter_table.test @@ -329,3 +329,142 @@ drop user u@localhost; drop database db; --echo # End of 10.11 tests + +--echo # +--echo # MDEV-22164 without validation for exchange partition/convert in +--echo # +call mtr.add_suppression('was altered WITHOUT'); +let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err; +let SEARCH_PATTERN=Table `test`.`tp` was altered WITHOUT VALIDATION: the table might be corrupted; + +create table validation(x int); +drop table validation; + +create table t (a int primary key); + +create table tp (a int primary key) partition by range (a) ( + partition p0 values less than (100), + p1 values less than (300)); + +insert into t values (1), (99); +insert into tp values (2), (200); + +select * from t order by a; +select * from tp partition (p0) order by a; +select * from tp partition (p1) order by a; + +alter table tp exchange partition p0 with table t; +select * from t order by a; +select * from tp partition (p0) order by a; +select * from tp partition (p1) order by a; + +alter table tp exchange partition p0 with table t; +select * from t order by a; +select * from tp partition (p0) order by a; +select * from tp partition (p1) order by a; + +--error ER_ROW_DOES_NOT_MATCH_PARTITION +alter table tp exchange partition p1 with table t; +--error ER_ROW_DOES_NOT_MATCH_PARTITION +alter table tp exchange partition p1 with table t with validation; +alter table tp exchange partition p1 with table t without validation; +source include/search_pattern_in_file.inc; +select * from t order by a; +select * from tp partition (p1) order by a; +alter table tp check partition p0; +alter table tp check partition p1; + +alter table tp exchange partition p1 with table t with validation; +--error ER_ROW_DOES_NOT_MATCH_PARTITION +alter table tp exchange partition p1 with table t with validation; +select * from t order by a; +select * from tp partition (p1) order by a; + +# SP +create or replace procedure validation() +alter table tp exchange partition p1 with table t with validation; +create or replace procedure without_validation() +alter table tp exchange partition p1 with table t without validation; +--error ER_ROW_DOES_NOT_MATCH_PARTITION +call validation; +call without_validation; +source include/search_pattern_in_file.inc; +call validation; +--error ER_ROW_DOES_NOT_MATCH_PARTITION +call validation; +call without_validation; +source include/search_pattern_in_file.inc; +select * from t order by a; +select * from tp partition (p1) order by a; +call validation; +select * from t order by a; +select * from tp partition (p1) order by a; +drop procedure validation; +drop procedure without_validation; + +# PS +prepare validation from "alter table tp exchange partition p1 with table t with validation"; +prepare without_validation from "alter table tp exchange partition p1 with table t without validation"; +--error ER_ROW_DOES_NOT_MATCH_PARTITION +execute validation; +execute without_validation; +source include/search_pattern_in_file.inc; +execute validation; +--error ER_ROW_DOES_NOT_MATCH_PARTITION +execute validation; +execute without_validation; +source include/search_pattern_in_file.inc; +select * from t order by a; +select * from tp partition (p1) order by a; +execute validation; +select * from t order by a; +select * from tp partition (p1) order by a; +drop prepare validation; +drop prepare without_validation; + +# CONVERT IN +--error ER_ROW_DOES_NOT_MATCH_PARTITION +alter table tp convert table t to partition p2 values less than (maxvalue); +--error ER_ROW_DOES_NOT_MATCH_PARTITION +alter table tp convert table t to partition p2 values less than (maxvalue) with validation; +alter table tp convert table t to partition p2 values less than (maxvalue) without validation; +source include/search_pattern_in_file.inc; +select * from tp partition (p0) order by a; +select * from tp partition (p1) order by a; +select * from tp partition (p2) order by a; + +# Hash +create table t (a int primary key); +insert t values (1), (2); + +create or replace table tp (a int primary key) +partition by hash(a) partitions 2; + +insert tp values (1), (2), (3), (4); +--error ER_ROW_DOES_NOT_MATCH_PARTITION +alter table tp exchange partition p0 with table t; +alter table tp exchange partition p0 with table t without validation; +source include/search_pattern_in_file.inc; +select * from t; +alter table tp exchange partition p0 with table t; +drop table tp; + +# List +create or replace table tp (a int primary key) +partition by list(a) ( + partition p0 values in (2, 3, 4), + partition p1 values in (12, 13, 14), + partition p2 values in (52, 53, 54)); + + +insert tp values (12), (2), (3), (4); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +alter table tp exchange partition p0 with table t; +alter table tp exchange partition p0 with table t without validation; +source include/search_pattern_in_file.inc; +select * from t; +alter table tp exchange partition p0 with table t; + +drop tables tp, t; + +--echo # End of 11.4 tests diff --git a/mysql-test/suite/parts/t/debug_innodb_crash-master.opt b/mysql-test/suite/parts/t/debug_innodb_crash-master.opt index b7f94e14..d8813652 100644 --- a/mysql-test/suite/parts/t/debug_innodb_crash-master.opt +++ b/mysql-test/suite/parts/t/debug_innodb_crash-master.opt @@ -1 +1 @@ ---loose-innodb-file-per-table=1 --loose-skip-stack-trace --skip-core-file --loose-innodb-buffer-pool-size=32M +--loose-skip-stack-trace --skip-core-file --loose-innodb-buffer-pool-size=32M diff --git a/mysql-test/suite/parts/t/partition_debug_sync_innodb-master.opt b/mysql-test/suite/parts/t/partition_debug_sync_innodb-master.opt deleted file mode 100644 index 115a0ba2..00000000 --- a/mysql-test/suite/parts/t/partition_debug_sync_innodb-master.opt +++ /dev/null @@ -1 +0,0 @@ ---loose-innodb_file_per_table |