Merging upstream version 1:11.4.2.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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
index 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