Manual merge from 5.1

12 files changed, 1492 insertions, 4125 deletions

diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index a639f9e6b3b..9c8b93d9fe2 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -1901,7 +1901,7 @@ c1 bigint,
 c2 set('sweet'),
 key (c2,c1,c0), 
 key(c0)
-) engine=myisam partition by hash (month(c0)) partitions 5;
+) engine=myisam partition by hash (c0) partitions 5;
 insert ignore into t1 set c0 = -6502262, c1 = 3992917, c2 = 35019;
 insert ignore into t1 set c0 = 241221, c1 = -6862346, c2 = 56644;
 select c1 from t1 group by (select c0 from t1 limit 1);
diff --git a/mysql-test/r/partition_error.result b/mysql-test/r/partition_error.result
index d41e4cb510c..1dcb2fdebb8 100644
--- a/mysql-test/r/partition_error.result
+++ b/mysql-test/r/partition_error.result
@@ -1,5 +1,655 @@
 drop table if exists t1, t2;
 #
+# Bug#54483: valgrind errors when making warnings for multiline inserts
+#            into partition
+#
+CREATE TABLE t1 (a VARBINARY(10))
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a CHAR(10))
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIMESTAMP)
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+INSERT INTO t1 VALUES ('test'),('a'),('5');
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+Warning	1265	Data truncated for column 'a' at row 2
+Warning	1265	Data truncated for column 'a' at row 3
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1265	Data truncated for column 'a' at row 1
+Warning	1265	Data truncated for column 'a' at row 2
+Warning	1265	Data truncated for column 'a' at row 3
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+INSERT INTO t1 VALUES ('test'),('a'),('5');
+Warnings:
+Warning	1264	Out of range value for column 'a' at row 1
+Warning	1264	Out of range value for column 'a' at row 2
+Warning	1264	Out of range value for column 'a' at row 3
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1264	Out of range value for column 'a' at row 1
+Warning	1264	Out of range value for column 'a' at row 2
+Warning	1264	Out of range value for column 'a' at row 3
+DROP TABLE t1;
+CREATE TABLE t1 (a TIME)
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+SHOW WARNINGS;
+Level	Code	Message
+Error	1486	Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (TO_DAYS(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (TO_DAYS(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (TO_DAYS(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (TO_DAYS(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (TO_DAYS(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (DAYOFMONTH(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (DAYOFMONTH(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (DAYOFMONTH(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (DAYOFMONTH(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (DAYOFMONTH(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (MONTH(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (MONTH(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (MONTH(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (MONTH(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (MONTH(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (DAYOFYEAR(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (DAYOFYEAR(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (DAYOFYEAR(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (DAYOFYEAR(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (DAYOFYEAR(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (HOUR(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (HOUR(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (HOUR(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (HOUR(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (HOUR(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (MINUTE(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (MINUTE(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (MINUTE(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (MINUTE(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (MINUTE(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (QUARTER(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (QUARTER(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (QUARTER(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (QUARTER(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (QUARTER(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (SECOND(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (SECOND(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (SECOND(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (SECOND(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (SECOND(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (YEARWEEK(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (YEARWEEK(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (YEARWEEK(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (YEARWEEK(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (YEARWEEK(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (WEEKDAY(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (WEEKDAY(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (WEEKDAY(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (WEEKDAY(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (WEEKDAY(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+# TO_SECONDS() is added in 5.5.
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (TO_SECONDS(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (TO_SECONDS(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (TO_SECONDS(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (TO_SECONDS(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (TO_SECONDS(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (TIME_TO_SEC(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (TIME_TO_SEC(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (TIME_TO_SEC(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (TIME_TO_SEC(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (TIME_TO_SEC(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (FROM_DAYS(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (FROM_DAYS(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (FROM_DAYS(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (TO_DAYS(FROM_DAYS(a)));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (FROM_DAYS(a));
+ERROR HY000: The PARTITION function returns the wrong type
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (TO_DAYS(FROM_DAYS(a)));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (FROM_DAYS(a));
+ERROR HY000: The PARTITION function returns the wrong type
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (MICROSECOND(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (MICROSECOND(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (MICROSECOND(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (MICROSECOND(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (MICROSECOND(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+# Bug#57071
+CREATE TABLE t1
+(`date` date,
+`extracted_week` int,
+`yearweek` int,
+`week` int,
+`default_week_format` int)
+PARTITION BY LIST (EXTRACT(WEEK FROM date) % 3)
+(PARTITION p0 VALUES IN (0),
+PARTITION p1 VALUES IN (1),
+PARTITION p2 VALUES IN (2));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1
+(`date` date,
+`extracted_week` int,
+`yearweek` int,
+`week` int,
+`default_week_format` int);
+SET @old_default_week_format := @@default_week_format;
+SET default_week_format = 0;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 1;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 2;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 3;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 4;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 5;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 6;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 7;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SELECT * FROM t1;
+date	extracted_week	yearweek	week	default_week_format
+2000-01-01	0	199952	0	0
+2000-01-01	0	199952	0	1
+2000-01-01	52	199952	52	2
+2000-01-01	52	199952	52	3
+2000-01-01	0	199952	0	4
+2000-01-01	0	199952	0	5
+2000-01-01	52	199952	52	6
+2000-01-01	52	199952	52	7
+SET default_week_format = @old_default_week_format;
+DROP TABLE t1;
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(YEAR FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(YEAR FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(YEAR FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(YEAR FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(YEAR FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(QUARTER FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(QUARTER FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(QUARTER FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(QUARTER FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(QUARTER FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(MONTH FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(MONTH FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(MONTH FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(MONTH FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(MONTH FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+# EXTRACT(WEEK...) is disallowed, see bug#57071.
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(WEEK FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(WEEK FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(WEEK FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(WEEK FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(WEEK FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(DAY FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(DAY FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(DAY FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(DAY FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(DAY FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(HOUR FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(HOUR FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(HOUR FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(HOUR FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(HOUR FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(MINUTE FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(MINUTE FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(MINUTE FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(MINUTE FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(MINUTE FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(SECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(SECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(SECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME, b DATE)
+PARTITION BY HASH (DATEDIFF(a, b));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATE, b DATETIME)
+PARTITION BY HASH (DATEDIFF(a, b));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME, b DATE)
+PARTITION BY HASH (DATEDIFF(a, b));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE, b VARCHAR(10))
+PARTITION BY HASH (DATEDIFF(a, b));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT, b DATETIME)
+PARTITION BY HASH (DATEDIFF(a, b));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (TIME_TO_SEC(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (TIME_TO_SEC(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (TIME_TO_SEC(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (TIME_TO_SEC(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (TIME_TO_SEC(a));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+#
 # Bug#50036: Inconsistent errors when using TIMESTAMP
 #            columns/expressions
 # 1. correct and appropriate errors in light of
diff --git a/mysql-test/suite/parts/inc/part_supported_sql_funcs_main.inc b/mysql-test/suite/parts/inc/part_supported_sql_funcs_main.inc
index 45d77225a23..b7a170dd9d8 100644
--- a/mysql-test/suite/parts/inc/part_supported_sql_funcs_main.inc
+++ b/mysql-test/suite/parts/inc/part_supported_sql_funcs_main.inc
@@ -88,8 +88,9 @@ let $val2 =  '2006-01-17';
 let $val3 =  '2006-02-25';
 let $val4 =  '2006-02-05';
 --source suite/parts/inc/partition_supported_sql_funcs.inc
-let $coltype = char(30);
---source suite/parts/inc/partition_supported_sql_funcs.inc
+# Disabled after fixing bug#54483.
+#let $coltype = char(30);
+#--source suite/parts/inc/partition_supported_sql_funcs.inc
 
 let $sqlfunc = extract(month from col1);
 let $valsqlfunc = extract(year from '1998-11-23');
@@ -139,8 +140,9 @@ let $val2 =  '14:30:20';
 let $val3 =  '21:59:22';
 let $val4 =  '10:22:33';
 --source suite/parts/inc/partition_supported_sql_funcs.inc
-let $coltype = char(30);
---source suite/parts/inc/partition_supported_sql_funcs.inc
+# second(non_time_col) is disabled after bug#54483.
+#let $coltype = char(30);
+#--source suite/parts/inc/partition_supported_sql_funcs.inc
 
 let $sqlfunc = month(col1);
 let $valsqlfunc = month('2006-10-14');
@@ -172,26 +174,28 @@ let $val3 =  '21:59:22';
 let $val4 =  '10:33:11';
 --source suite/parts/inc/partition_supported_sql_funcs.inc
 
-let $sqlfunc = to_days(col1)-to_days('2006-01-01');
-let $valsqlfunc = to_days('2006-02-02')-to_days('2006-01-01');
-let $coltype = date;
-let $infile = part_supported_sql_funcs_int_date.inc;
-let $val1 =  '2006-02-03';
-let $val2 =  '2006-01-17';
-let $val3 =  '2006-01-25';
-let $val4 =  '2006-02-06';
---source suite/parts/inc/partition_supported_sql_funcs.inc
+# to_days(non_date_col) is disabled after bug#54483.
+#let $sqlfunc = to_days(col1)-to_days('2006-01-01');
+#let $valsqlfunc = to_days('2006-02-02')-to_days('2006-01-01');
+#let $coltype = date;
+#let $infile = part_supported_sql_funcs_int_date.inc;
+#let $val1 =  '2006-02-03';
+#let $val2 =  '2006-01-17';
+#let $val3 =  '2006-01-25';
+#let $val4 =  '2006-02-06';
+#--source suite/parts/inc/partition_supported_sql_funcs.inc
 
+# to_days(non_date_col) is disabled after bug#54483.
 # DATEDIFF() is implemented as (TO_DAYS(d1) - TO_DAYS(d2))
-let $sqlfunc = datediff(col1, '2006-01-01');
-let $valsqlfunc = datediff('2006-02-02', '2006-01-01');
-let $coltype = date;
-let $infile = part_supported_sql_funcs_int_date.inc;
-let $val1 =  '2006-02-03';
-let $val2 =  '2006-01-17';
-let $val3 =  '2006-01-25';
-let $val4 =  '2006-02-06';
---source suite/parts/inc/partition_supported_sql_funcs.inc
+#let $sqlfunc = datediff(col1, '2006-01-01');
+#let $valsqlfunc = datediff('2006-02-02', '2006-01-01');
+#let $coltype = date;
+#let $infile = part_supported_sql_funcs_int_date.inc;
+#let $val1 =  '2006-02-03';
+#let $val2 =  '2006-01-17';
+#let $val3 =  '2006-01-25';
+#let $val4 =  '2006-02-06';
+#--source suite/parts/inc/partition_supported_sql_funcs.inc
 
 let $sqlfunc = weekday(col1);
 let $valsqlfunc = weekday('2006-10-14');
diff --git a/mysql-test/suite/parts/r/part_supported_sql_func_innodb.result b/mysql-test/suite/parts/r/part_supported_sql_func_innodb.result
index 79462c25050..c5b4e0a8665 100644
--- a/mysql-test/suite/parts/r/part_supported_sql_func_innodb.result
+++ b/mysql-test/suite/parts/r/part_supported_sql_func_innodb.result
@@ -5425,513 +5425,6 @@ drop table if exists t44 ;
 drop table if exists t55 ;
 drop table if exists t66 ;
 -------------------------------------------------------------------------
----  dayofyear(col1)  in partition with coltype  char(30)
--------------------------------------------------------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
--------------------------------------------------------------------------
----  Create tables with dayofyear(col1)
--------------------------------------------------------------------------
-create table t1 (col1 char(30)) engine='INNODB' 
-partition by range(dayofyear(col1)) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t2 (col1 char(30)) engine='INNODB' 
-partition by list(dayofyear(col1)) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t3 (col1 char(30)) engine='INNODB' 
-partition by hash(dayofyear(col1));
-create table t4 (colint int, col1 char(30)) engine='INNODB' 
-partition by range(colint) 
-subpartition by hash(dayofyear(col1)) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t5 (colint int, col1 char(30)) engine='INNODB' 
-partition by list(colint)
-subpartition by hash(dayofyear(col1)) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t6 (colint int, col1 char(30)) engine='INNODB' 
-partition by range(colint) 
-(partition p0 values less than (dayofyear('2006-12-25')),
-partition p1 values less than maxvalue);
--------------------------------------------------------------------------
----  Access tables with dayofyear(col1) 
--------------------------------------------------------------------------
-insert into t1 values ('2006-01-03');
-insert into t1 values ('2006-01-17');
-insert into t2 values ('2006-01-03');
-insert into t2 values ('2006-01-17');
-insert into t2 values ('2006-02-25');
-insert into t3 values ('2006-01-03');
-insert into t3 values ('2006-01-17');
-insert into t3 values ('2006-02-25');
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
-select dayofyear(col1) from t1 order by col1;
-dayofyear(col1)
-3
-17
-select * from t1 order by col1;
-col1
-2006-01-03
-2006-01-17
-select * from t2 order by col1;
-col1
-2006-01-03
-2006-01-17
-2006-02-25
-select * from t3 order by col1;
-col1
-2006-01-03
-2006-01-17
-2006-02-25
-select * from t4 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t6 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-update t1 set col1='2006-02-05' where col1='2006-01-03';
-update t2 set col1='2006-02-05' where col1='2006-01-03';
-update t3 set col1='2006-02-05' where col1='2006-01-03';
-update t4 set col1='2006-02-05' where col1='2006-01-03';
-update t5 set col1='2006-02-05' where col1='2006-01-03';
-update t6 set col1='2006-02-05' where col1='2006-01-03';
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-05
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t4 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t6 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
--------------------------------------------------------------------------
----  Alter tables with dayofyear(col1)
--------------------------------------------------------------------------
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
-create table t11 engine='INNODB' as select * from t1;
-create table t22 engine='INNODB' as select * from t2;
-create table t33 engine='INNODB' as select * from t3;
-create table t44 engine='INNODB' as select * from t4;
-create table t55 engine='INNODB' as select * from t5;
-create table t66 engine='INNODB' as select * from t6;
-alter table t11
-partition by range(dayofyear(col1)) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t22
-partition by list(dayofyear(col1)) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t33
-partition by hash(dayofyear(col1));
-alter table t44
-partition by range(colint) 
-subpartition by hash(dayofyear(col1)) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t55
-partition by list(colint)
-subpartition by hash(dayofyear(col1)) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t66
-partition by range(colint) 
-(partition p0 values less than (dayofyear('2006-12-25')),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-05
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t44 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t55 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t66 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
----------------------------
----- some alter table begin
----------------------------
-alter table t11
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-05
-alter table t11
-reorganize partition s1 into
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-05
-alter table t55
-partition by list(colint)
-subpartition by hash(dayofyear(col1)) subpartitions 5 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-show create table t55;
-Table	Create Table
-t55	CREATE TABLE `t55` (
-  `colint` int(11) DEFAULT NULL,
-  `col1` char(30) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY LIST (colint)
-SUBPARTITION BY HASH (dayofyear(col1))
-SUBPARTITIONS 5
-(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
- PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
- PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
- PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
- PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
- PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
-select * from t55 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (dayofyear('2006-12-25')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (dayofyear('2006-12-25')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with dayofyear(col1)
--------------------------------------------------------------------------
-delete from t1 where col1='2006-01-17';
-delete from t2 where col1='2006-01-17';
-delete from t3 where col1='2006-01-17';
-delete from t4 where col1='2006-01-17';
-delete from t5 where col1='2006-01-17';
-delete from t6 where col1='2006-01-17';
-select * from t1 order by col1;
-col1
-2006-02-05
-select * from t2 order by col1;
-col1
-2006-02-05
-2006-02-25
-select * from t3 order by col1;
-col1
-2006-02-05
-2006-02-25
-select * from t4 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-insert into t1 values ('2006-01-17');
-insert into t2 values ('2006-01-17');
-insert into t3 values ('2006-01-17');
-insert into t4 values (60,'2006-01-17');
-insert into t5 values (60,'2006-01-17');
-insert into t6 values (60,'2006-01-17');
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-05
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t4 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t5 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t6 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-alter table t1 drop partition p0;
-alter table t2 drop partition p0;
-alter table t4 drop partition p0;
-alter table t5 drop partition p0;
-alter table t6 drop partition p0;
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-05
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t4 order by colint;
-colint	col1
-60	2006-01-17
-select * from t5 order by colint;
-colint	col1
-60	2006-01-17
-select * from t6 order by colint;
-colint	col1
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with dayofyear(col1)
--------------------------------------------------------------------------
-delete from t11 where col1='2006-01-17';
-delete from t22 where col1='2006-01-17';
-delete from t33 where col1='2006-01-17';
-delete from t44 where col1='2006-01-17';
-delete from t55 where col1='2006-01-17';
-delete from t66 where col1='2006-01-17';
-select * from t11 order by col1;
-col1
-2006-02-05
-select * from t22 order by col1;
-col1
-2006-02-05
-2006-02-25
-select * from t33 order by col1;
-col1
-2006-02-05
-2006-02-25
-select * from t44 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-select * from t55 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-insert into t11 values ('2006-01-17');
-insert into t22 values ('2006-01-17');
-insert into t33 values ('2006-01-17');
-insert into t44 values (60,'2006-01-17');
-insert into t55 values (60,'2006-01-17');
-insert into t66 values (60,'2006-01-17');
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-05
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t44 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t55 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t66 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-alter table t11 drop partition p0;
-alter table t22 drop partition p0;
-alter table t44 drop partition p0;
-alter table t55 drop partition p0;
-alter table t66 drop partition p0;
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-05
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t44 order by colint;
-colint	col1
-60	2006-01-17
-select * from t55 order by colint;
-colint	col1
-60	2006-01-17
-select * from t66 order by colint;
-colint	col1
--------------------------
----- some alter table end
--------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
--------------------------------------------------------------------------
 ---  extract(month from col1)  in partition with coltype  date
 -------------------------------------------------------------------------
 drop table if exists t1 ;
@@ -8489,525 +7982,6 @@ drop table if exists t44 ;
 drop table if exists t55 ;
 drop table if exists t66 ;
 -------------------------------------------------------------------------
----  second(col1)  in partition with coltype  char(30)
--------------------------------------------------------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
--------------------------------------------------------------------------
----  Create tables with second(col1)
--------------------------------------------------------------------------
-create table t1 (col1 char(30)) engine='INNODB' 
-partition by range(second(col1)) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t2 (col1 char(30)) engine='INNODB' 
-partition by list(second(col1)) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t3 (col1 char(30)) engine='INNODB' 
-partition by hash(second(col1));
-create table t4 (colint int, col1 char(30)) engine='INNODB' 
-partition by range(colint) 
-subpartition by hash(second(col1)) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t5 (colint int, col1 char(30)) engine='INNODB' 
-partition by list(colint)
-subpartition by hash(second(col1)) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t6 (colint int, col1 char(30)) engine='INNODB' 
-partition by range(colint) 
-(partition p0 values less than (second('18:30:14')),
-partition p1 values less than maxvalue);
--------------------------------------------------------------------------
----  Access tables with second(col1) 
--------------------------------------------------------------------------
-insert into t1 values ('09:09:09');
-insert into t1 values ('14:30:20');
-insert into t2 values ('09:09:09');
-insert into t2 values ('14:30:20');
-insert into t2 values ('21:59:22');
-insert into t3 values ('09:09:09');
-insert into t3 values ('14:30:20');
-insert into t3 values ('21:59:22');
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
-select second(col1) from t1 order by col1;
-second(col1)
-9
-20
-select * from t1 order by col1;
-col1
-09:09:09
-14:30:20
-select * from t2 order by col1;
-col1
-09:09:09
-14:30:20
-21:59:22
-select * from t3 order by col1;
-col1
-09:09:09
-14:30:20
-21:59:22
-select * from t4 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t5 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t6 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-update t1 set col1='10:22:33' where col1='09:09:09';
-update t2 set col1='10:22:33' where col1='09:09:09';
-update t3 set col1='10:22:33' where col1='09:09:09';
-update t4 set col1='10:22:33' where col1='09:09:09';
-update t5 set col1='10:22:33' where col1='09:09:09';
-update t6 set col1='10:22:33' where col1='09:09:09';
-select * from t1 order by col1;
-col1
-10:22:33
-14:30:20
-select * from t2 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t3 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t4 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t5 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t6 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
--------------------------------------------------------------------------
----  Alter tables with second(col1)
--------------------------------------------------------------------------
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
-create table t11 engine='INNODB' as select * from t1;
-create table t22 engine='INNODB' as select * from t2;
-create table t33 engine='INNODB' as select * from t3;
-create table t44 engine='INNODB' as select * from t4;
-create table t55 engine='INNODB' as select * from t5;
-create table t66 engine='INNODB' as select * from t6;
-alter table t11
-partition by range(second(col1)) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t22
-partition by list(second(col1)) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t33
-partition by hash(second(col1));
-alter table t44
-partition by range(colint) 
-subpartition by hash(second(col1)) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t55
-partition by list(colint)
-subpartition by hash(second(col1)) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t66
-partition by range(colint) 
-(partition p0 values less than (second('18:30:14')),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-10:22:33
-14:30:20
-select * from t22 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t33 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t44 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t55 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t66 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
----------------------------
----- some alter table begin
----------------------------
-alter table t11
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-10:22:33
-14:30:20
-alter table t11
-reorganize partition s1 into
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-10:22:33
-14:30:20
-alter table t55
-partition by list(colint)
-subpartition by hash(second(col1)) subpartitions 5 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-show create table t55;
-Table	Create Table
-t55	CREATE TABLE `t55` (
-  `colint` int(11) DEFAULT NULL,
-  `col1` char(30) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY LIST (colint)
-SUBPARTITION BY HASH (second(col1))
-SUBPARTITIONS 5
-(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
- PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
- PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
- PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
- PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
- PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
-select * from t55 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (second('18:30:14')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (second('18:30:14')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with second(col1)
--------------------------------------------------------------------------
-delete from t1 where col1='14:30:20';
-delete from t2 where col1='14:30:20';
-delete from t3 where col1='14:30:20';
-delete from t4 where col1='14:30:20';
-delete from t5 where col1='14:30:20';
-delete from t6 where col1='14:30:20';
-select * from t1 order by col1;
-col1
-10:22:33
-select * from t2 order by col1;
-col1
-10:22:33
-21:59:22
-select * from t3 order by col1;
-col1
-10:22:33
-21:59:22
-select * from t4 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t5 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-insert into t1 values ('14:30:20');
-insert into t2 values ('14:30:20');
-insert into t3 values ('14:30:20');
-insert into t4 values (60,'14:30:20');
-insert into t5 values (60,'14:30:20');
-insert into t6 values (60,'14:30:20');
-select * from t1 order by col1;
-col1
-10:22:33
-14:30:20
-select * from t2 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t3 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t4 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-60	14:30:20
-select * from t5 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-60	14:30:20
-select * from t6 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-60	14:30:20
-alter table t1 drop partition p0;
-alter table t2 drop partition p0;
-alter table t4 drop partition p0;
-alter table t5 drop partition p0;
-alter table t6 drop partition p0;
-select * from t1 order by col1;
-col1
-10:22:33
-14:30:20
-select * from t2 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t3 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t4 order by colint;
-colint	col1
-60	14:30:20
-select * from t5 order by colint;
-colint	col1
-60	14:30:20
-select * from t6 order by colint;
-colint	col1
-60	14:30:20
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with second(col1)
--------------------------------------------------------------------------
-delete from t11 where col1='14:30:20';
-delete from t22 where col1='14:30:20';
-delete from t33 where col1='14:30:20';
-delete from t44 where col1='14:30:20';
-delete from t55 where col1='14:30:20';
-delete from t66 where col1='14:30:20';
-select * from t11 order by col1;
-col1
-10:22:33
-select * from t22 order by col1;
-col1
-10:22:33
-21:59:22
-select * from t33 order by col1;
-col1
-10:22:33
-21:59:22
-select * from t44 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t55 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-insert into t11 values ('14:30:20');
-insert into t22 values ('14:30:20');
-insert into t33 values ('14:30:20');
-insert into t44 values (60,'14:30:20');
-insert into t55 values (60,'14:30:20');
-insert into t66 values (60,'14:30:20');
-select * from t11 order by col1;
-col1
-10:22:33
-14:30:20
-select * from t22 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t33 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t44 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-60	14:30:20
-select * from t55 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-60	14:30:20
-select * from t66 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-60	14:30:20
-alter table t11 drop partition p0;
-alter table t22 drop partition p0;
-alter table t44 drop partition p0;
-alter table t55 drop partition p0;
-alter table t66 drop partition p0;
-select * from t11 order by col1;
-col1
-10:22:33
-14:30:20
-select * from t22 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t33 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t44 order by colint;
-colint	col1
-60	14:30:20
-select * from t55 order by colint;
-colint	col1
-60	14:30:20
-select * from t66 order by colint;
-colint	col1
-60	14:30:20
--------------------------
----- some alter table end
--------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
--------------------------------------------------------------------------
 ---  month(col1)  in partition with coltype  date
 -------------------------------------------------------------------------
 drop table if exists t1 ;
@@ -10549,1024 +9523,6 @@ drop table if exists t44 ;
 drop table if exists t55 ;
 drop table if exists t66 ;
 -------------------------------------------------------------------------
----  to_days(col1)-to_days('2006-01-01')  in partition with coltype  date
--------------------------------------------------------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
--------------------------------------------------------------------------
----  Create tables with to_days(col1)-to_days('2006-01-01')
--------------------------------------------------------------------------
-create table t1 (col1 date) engine='INNODB' 
-partition by range(to_days(col1)-to_days('2006-01-01')) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t2 (col1 date) engine='INNODB' 
-partition by list(to_days(col1)-to_days('2006-01-01')) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t3 (col1 date) engine='INNODB' 
-partition by hash(to_days(col1)-to_days('2006-01-01'));
-create table t4 (colint int, col1 date) engine='INNODB' 
-partition by range(colint) 
-subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t5 (colint int, col1 date) engine='INNODB' 
-partition by list(colint)
-subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t6 (colint int, col1 date) engine='INNODB' 
-partition by range(colint) 
-(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
-partition p1 values less than maxvalue);
--------------------------------------------------------------------------
----  Access tables with to_days(col1)-to_days('2006-01-01') 
--------------------------------------------------------------------------
-insert into t1 values ('2006-02-03');
-insert into t1 values ('2006-01-17');
-insert into t2 values ('2006-02-03');
-insert into t2 values ('2006-01-17');
-insert into t2 values ('2006-01-25');
-insert into t3 values ('2006-02-03');
-insert into t3 values ('2006-01-17');
-insert into t3 values ('2006-01-25');
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
-select to_days(col1)-to_days('2006-01-01') from t1 order by col1;
-to_days(col1)-to_days('2006-01-01')
-16
-33
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-03
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-03
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-03
-select * from t4 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t6 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-update t1 set col1='2006-02-06' where col1='2006-02-03';
-update t2 set col1='2006-02-06' where col1='2006-02-03';
-update t3 set col1='2006-02-06' where col1='2006-02-03';
-update t4 set col1='2006-02-06' where col1='2006-02-03';
-update t5 set col1='2006-02-06' where col1='2006-02-03';
-update t6 set col1='2006-02-06' where col1='2006-02-03';
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t6 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
--------------------------------------------------------------------------
----  Alter tables with to_days(col1)-to_days('2006-01-01')
--------------------------------------------------------------------------
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
-create table t11 engine='INNODB' as select * from t1;
-create table t22 engine='INNODB' as select * from t2;
-create table t33 engine='INNODB' as select * from t3;
-create table t44 engine='INNODB' as select * from t4;
-create table t55 engine='INNODB' as select * from t5;
-create table t66 engine='INNODB' as select * from t6;
-alter table t11
-partition by range(to_days(col1)-to_days('2006-01-01')) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t22
-partition by list(to_days(col1)-to_days('2006-01-01')) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t33
-partition by hash(to_days(col1)-to_days('2006-01-01'));
-alter table t44
-partition by range(colint) 
-subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t55
-partition by list(colint)
-subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t66
-partition by range(colint) 
-(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
----------------------------
----- some alter table begin
----------------------------
-alter table t11
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-alter table t11
-reorganize partition s1 into
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-alter table t55
-partition by list(colint)
-subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 5 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-show create table t55;
-Table	Create Table
-t55	CREATE TABLE `t55` (
-  `colint` int(11) DEFAULT NULL,
-  `col1` date DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY LIST (colint)
-SUBPARTITION BY HASH (to_days(col1)-to_days('2006-01-01'))
-SUBPARTITIONS 5
-(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
- PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
- PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
- PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
- PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
- PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
--------------------------------------------------------------------------
-delete from t1 where col1='2006-01-17';
-delete from t2 where col1='2006-01-17';
-delete from t3 where col1='2006-01-17';
-delete from t4 where col1='2006-01-17';
-delete from t5 where col1='2006-01-17';
-delete from t6 where col1='2006-01-17';
-select * from t1 order by col1;
-col1
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-insert into t1 values ('2006-01-17');
-insert into t2 values ('2006-01-17');
-insert into t3 values ('2006-01-17');
-insert into t4 values (60,'2006-01-17');
-insert into t5 values (60,'2006-01-17');
-insert into t6 values (60,'2006-01-17');
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t5 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t6 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-alter table t1 drop partition p0;
-alter table t2 drop partition p0;
-alter table t4 drop partition p0;
-alter table t5 drop partition p0;
-alter table t6 drop partition p0;
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-60	2006-01-17
-select * from t5 order by colint;
-colint	col1
-60	2006-01-17
-select * from t6 order by colint;
-colint	col1
-60	2006-01-17
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
--------------------------------------------------------------------------
-delete from t11 where col1='2006-01-17';
-delete from t22 where col1='2006-01-17';
-delete from t33 where col1='2006-01-17';
-delete from t44 where col1='2006-01-17';
-delete from t55 where col1='2006-01-17';
-delete from t66 where col1='2006-01-17';
-select * from t11 order by col1;
-col1
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-insert into t11 values ('2006-01-17');
-insert into t22 values ('2006-01-17');
-insert into t33 values ('2006-01-17');
-insert into t44 values (60,'2006-01-17');
-insert into t55 values (60,'2006-01-17');
-insert into t66 values (60,'2006-01-17');
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-alter table t11 drop partition p0;
-alter table t22 drop partition p0;
-alter table t44 drop partition p0;
-alter table t55 drop partition p0;
-alter table t66 drop partition p0;
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-60	2006-01-17
-select * from t55 order by colint;
-colint	col1
-60	2006-01-17
-select * from t66 order by colint;
-colint	col1
-60	2006-01-17
--------------------------
----- some alter table end
--------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
--------------------------------------------------------------------------
----  datediff(col1, '2006-01-01')  in partition with coltype  date
--------------------------------------------------------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
--------------------------------------------------------------------------
----  Create tables with datediff(col1, '2006-01-01')
--------------------------------------------------------------------------
-create table t1 (col1 date) engine='INNODB' 
-partition by range(datediff(col1, '2006-01-01')) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t2 (col1 date) engine='INNODB' 
-partition by list(datediff(col1, '2006-01-01')) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t3 (col1 date) engine='INNODB' 
-partition by hash(datediff(col1, '2006-01-01'));
-create table t4 (colint int, col1 date) engine='INNODB' 
-partition by range(colint) 
-subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t5 (colint int, col1 date) engine='INNODB' 
-partition by list(colint)
-subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t6 (colint int, col1 date) engine='INNODB' 
-partition by range(colint) 
-(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
-partition p1 values less than maxvalue);
--------------------------------------------------------------------------
----  Access tables with datediff(col1, '2006-01-01') 
--------------------------------------------------------------------------
-insert into t1 values ('2006-02-03');
-insert into t1 values ('2006-01-17');
-insert into t2 values ('2006-02-03');
-insert into t2 values ('2006-01-17');
-insert into t2 values ('2006-01-25');
-insert into t3 values ('2006-02-03');
-insert into t3 values ('2006-01-17');
-insert into t3 values ('2006-01-25');
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
-select datediff(col1, '2006-01-01') from t1 order by col1;
-datediff(col1, '2006-01-01')
-16
-33
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-03
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-03
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-03
-select * from t4 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t6 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-update t1 set col1='2006-02-06' where col1='2006-02-03';
-update t2 set col1='2006-02-06' where col1='2006-02-03';
-update t3 set col1='2006-02-06' where col1='2006-02-03';
-update t4 set col1='2006-02-06' where col1='2006-02-03';
-update t5 set col1='2006-02-06' where col1='2006-02-03';
-update t6 set col1='2006-02-06' where col1='2006-02-03';
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t6 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
--------------------------------------------------------------------------
----  Alter tables with datediff(col1, '2006-01-01')
--------------------------------------------------------------------------
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
-create table t11 engine='INNODB' as select * from t1;
-create table t22 engine='INNODB' as select * from t2;
-create table t33 engine='INNODB' as select * from t3;
-create table t44 engine='INNODB' as select * from t4;
-create table t55 engine='INNODB' as select * from t5;
-create table t66 engine='INNODB' as select * from t6;
-alter table t11
-partition by range(datediff(col1, '2006-01-01')) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t22
-partition by list(datediff(col1, '2006-01-01')) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t33
-partition by hash(datediff(col1, '2006-01-01'));
-alter table t44
-partition by range(colint) 
-subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t55
-partition by list(colint)
-subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t66
-partition by range(colint) 
-(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
----------------------------
----- some alter table begin
----------------------------
-alter table t11
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-alter table t11
-reorganize partition s1 into
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-alter table t55
-partition by list(colint)
-subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 5 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-show create table t55;
-Table	Create Table
-t55	CREATE TABLE `t55` (
-  `colint` int(11) DEFAULT NULL,
-  `col1` date DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY LIST (colint)
-SUBPARTITION BY HASH (datediff(col1, '2006-01-01'))
-SUBPARTITIONS 5
-(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
- PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
- PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
- PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
- PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
- PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with datediff(col1, '2006-01-01')
--------------------------------------------------------------------------
-delete from t1 where col1='2006-01-17';
-delete from t2 where col1='2006-01-17';
-delete from t3 where col1='2006-01-17';
-delete from t4 where col1='2006-01-17';
-delete from t5 where col1='2006-01-17';
-delete from t6 where col1='2006-01-17';
-select * from t1 order by col1;
-col1
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-insert into t1 values ('2006-01-17');
-insert into t2 values ('2006-01-17');
-insert into t3 values ('2006-01-17');
-insert into t4 values (60,'2006-01-17');
-insert into t5 values (60,'2006-01-17');
-insert into t6 values (60,'2006-01-17');
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t5 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t6 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-alter table t1 drop partition p0;
-alter table t2 drop partition p0;
-alter table t4 drop partition p0;
-alter table t5 drop partition p0;
-alter table t6 drop partition p0;
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-60	2006-01-17
-select * from t5 order by colint;
-colint	col1
-60	2006-01-17
-select * from t6 order by colint;
-colint	col1
-60	2006-01-17
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with datediff(col1, '2006-01-01')
--------------------------------------------------------------------------
-delete from t11 where col1='2006-01-17';
-delete from t22 where col1='2006-01-17';
-delete from t33 where col1='2006-01-17';
-delete from t44 where col1='2006-01-17';
-delete from t55 where col1='2006-01-17';
-delete from t66 where col1='2006-01-17';
-select * from t11 order by col1;
-col1
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-insert into t11 values ('2006-01-17');
-insert into t22 values ('2006-01-17');
-insert into t33 values ('2006-01-17');
-insert into t44 values (60,'2006-01-17');
-insert into t55 values (60,'2006-01-17');
-insert into t66 values (60,'2006-01-17');
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-alter table t11 drop partition p0;
-alter table t22 drop partition p0;
-alter table t44 drop partition p0;
-alter table t55 drop partition p0;
-alter table t66 drop partition p0;
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-60	2006-01-17
-select * from t55 order by colint;
-colint	col1
-60	2006-01-17
-select * from t66 order by colint;
-colint	col1
-60	2006-01-17
--------------------------
----- some alter table end
--------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
--------------------------------------------------------------------------
 ---  weekday(col1)  in partition with coltype  date
 -------------------------------------------------------------------------
 drop table if exists t1 ;
diff --git a/mysql-test/suite/parts/r/part_supported_sql_func_myisam.result b/mysql-test/suite/parts/r/part_supported_sql_func_myisam.result
index 375a6e130be..3cd8e10a4f3 100644
--- a/mysql-test/suite/parts/r/part_supported_sql_func_myisam.result
+++ b/mysql-test/suite/parts/r/part_supported_sql_func_myisam.result
@@ -5425,513 +5425,6 @@ drop table if exists t44 ;
 drop table if exists t55 ;
 drop table if exists t66 ;
 -------------------------------------------------------------------------
----  dayofyear(col1)  in partition with coltype  char(30)
--------------------------------------------------------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
--------------------------------------------------------------------------
----  Create tables with dayofyear(col1)
--------------------------------------------------------------------------
-create table t1 (col1 char(30)) engine='MYISAM' 
-partition by range(dayofyear(col1)) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t2 (col1 char(30)) engine='MYISAM' 
-partition by list(dayofyear(col1)) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t3 (col1 char(30)) engine='MYISAM' 
-partition by hash(dayofyear(col1));
-create table t4 (colint int, col1 char(30)) engine='MYISAM' 
-partition by range(colint) 
-subpartition by hash(dayofyear(col1)) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t5 (colint int, col1 char(30)) engine='MYISAM' 
-partition by list(colint)
-subpartition by hash(dayofyear(col1)) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t6 (colint int, col1 char(30)) engine='MYISAM' 
-partition by range(colint) 
-(partition p0 values less than (dayofyear('2006-12-25')),
-partition p1 values less than maxvalue);
--------------------------------------------------------------------------
----  Access tables with dayofyear(col1) 
--------------------------------------------------------------------------
-insert into t1 values ('2006-01-03');
-insert into t1 values ('2006-01-17');
-insert into t2 values ('2006-01-03');
-insert into t2 values ('2006-01-17');
-insert into t2 values ('2006-02-25');
-insert into t3 values ('2006-01-03');
-insert into t3 values ('2006-01-17');
-insert into t3 values ('2006-02-25');
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
-select dayofyear(col1) from t1 order by col1;
-dayofyear(col1)
-3
-17
-select * from t1 order by col1;
-col1
-2006-01-03
-2006-01-17
-select * from t2 order by col1;
-col1
-2006-01-03
-2006-01-17
-2006-02-25
-select * from t3 order by col1;
-col1
-2006-01-03
-2006-01-17
-2006-02-25
-select * from t4 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t6 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-update t1 set col1='2006-02-05' where col1='2006-01-03';
-update t2 set col1='2006-02-05' where col1='2006-01-03';
-update t3 set col1='2006-02-05' where col1='2006-01-03';
-update t4 set col1='2006-02-05' where col1='2006-01-03';
-update t5 set col1='2006-02-05' where col1='2006-01-03';
-update t6 set col1='2006-02-05' where col1='2006-01-03';
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-05
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t4 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t6 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
--------------------------------------------------------------------------
----  Alter tables with dayofyear(col1)
--------------------------------------------------------------------------
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
-create table t11 engine='MYISAM' as select * from t1;
-create table t22 engine='MYISAM' as select * from t2;
-create table t33 engine='MYISAM' as select * from t3;
-create table t44 engine='MYISAM' as select * from t4;
-create table t55 engine='MYISAM' as select * from t5;
-create table t66 engine='MYISAM' as select * from t6;
-alter table t11
-partition by range(dayofyear(col1)) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t22
-partition by list(dayofyear(col1)) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t33
-partition by hash(dayofyear(col1));
-alter table t44
-partition by range(colint) 
-subpartition by hash(dayofyear(col1)) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t55
-partition by list(colint)
-subpartition by hash(dayofyear(col1)) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t66
-partition by range(colint) 
-(partition p0 values less than (dayofyear('2006-12-25')),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-05
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t44 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t55 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t66 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
----------------------------
----- some alter table begin
----------------------------
-alter table t11
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-05
-alter table t11
-reorganize partition s1 into
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-05
-alter table t55
-partition by list(colint)
-subpartition by hash(dayofyear(col1)) subpartitions 5 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-show create table t55;
-Table	Create Table
-t55	CREATE TABLE `t55` (
-  `colint` int(11) DEFAULT NULL,
-  `col1` char(30) DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY LIST (colint)
-SUBPARTITION BY HASH (dayofyear(col1))
-SUBPARTITIONS 5
-(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM,
- PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM,
- PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM,
- PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM,
- PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM,
- PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
-select * from t55 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (dayofyear('2006-12-25')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (dayofyear('2006-12-25')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with dayofyear(col1)
--------------------------------------------------------------------------
-delete from t1 where col1='2006-01-17';
-delete from t2 where col1='2006-01-17';
-delete from t3 where col1='2006-01-17';
-delete from t4 where col1='2006-01-17';
-delete from t5 where col1='2006-01-17';
-delete from t6 where col1='2006-01-17';
-select * from t1 order by col1;
-col1
-2006-02-05
-select * from t2 order by col1;
-col1
-2006-02-05
-2006-02-25
-select * from t3 order by col1;
-col1
-2006-02-05
-2006-02-25
-select * from t4 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-insert into t1 values ('2006-01-17');
-insert into t2 values ('2006-01-17');
-insert into t3 values ('2006-01-17');
-insert into t4 values (60,'2006-01-17');
-insert into t5 values (60,'2006-01-17');
-insert into t6 values (60,'2006-01-17');
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-05
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t4 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t5 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t6 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-alter table t1 drop partition p0;
-alter table t2 drop partition p0;
-alter table t4 drop partition p0;
-alter table t5 drop partition p0;
-alter table t6 drop partition p0;
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-05
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t4 order by colint;
-colint	col1
-60	2006-01-17
-select * from t5 order by colint;
-colint	col1
-60	2006-01-17
-select * from t6 order by colint;
-colint	col1
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with dayofyear(col1)
--------------------------------------------------------------------------
-delete from t11 where col1='2006-01-17';
-delete from t22 where col1='2006-01-17';
-delete from t33 where col1='2006-01-17';
-delete from t44 where col1='2006-01-17';
-delete from t55 where col1='2006-01-17';
-delete from t66 where col1='2006-01-17';
-select * from t11 order by col1;
-col1
-2006-02-05
-select * from t22 order by col1;
-col1
-2006-02-05
-2006-02-25
-select * from t33 order by col1;
-col1
-2006-02-05
-2006-02-25
-select * from t44 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-select * from t55 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-insert into t11 values ('2006-01-17');
-insert into t22 values ('2006-01-17');
-insert into t33 values ('2006-01-17');
-insert into t44 values (60,'2006-01-17');
-insert into t55 values (60,'2006-01-17');
-insert into t66 values (60,'2006-01-17');
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-05
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t44 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t55 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t66 order by colint;
-colint	col1
-1	2006-02-03
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-alter table t11 drop partition p0;
-alter table t22 drop partition p0;
-alter table t44 drop partition p0;
-alter table t55 drop partition p0;
-alter table t66 drop partition p0;
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-05
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-02-05
-2006-02-25
-select * from t44 order by colint;
-colint	col1
-60	2006-01-17
-select * from t55 order by colint;
-colint	col1
-60	2006-01-17
-select * from t66 order by colint;
-colint	col1
--------------------------
----- some alter table end
--------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
--------------------------------------------------------------------------
 ---  extract(month from col1)  in partition with coltype  date
 -------------------------------------------------------------------------
 drop table if exists t1 ;
@@ -8489,525 +7982,6 @@ drop table if exists t44 ;
 drop table if exists t55 ;
 drop table if exists t66 ;
 -------------------------------------------------------------------------
----  second(col1)  in partition with coltype  char(30)
--------------------------------------------------------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
--------------------------------------------------------------------------
----  Create tables with second(col1)
--------------------------------------------------------------------------
-create table t1 (col1 char(30)) engine='MYISAM' 
-partition by range(second(col1)) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t2 (col1 char(30)) engine='MYISAM' 
-partition by list(second(col1)) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t3 (col1 char(30)) engine='MYISAM' 
-partition by hash(second(col1));
-create table t4 (colint int, col1 char(30)) engine='MYISAM' 
-partition by range(colint) 
-subpartition by hash(second(col1)) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t5 (colint int, col1 char(30)) engine='MYISAM' 
-partition by list(colint)
-subpartition by hash(second(col1)) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t6 (colint int, col1 char(30)) engine='MYISAM' 
-partition by range(colint) 
-(partition p0 values less than (second('18:30:14')),
-partition p1 values less than maxvalue);
--------------------------------------------------------------------------
----  Access tables with second(col1) 
--------------------------------------------------------------------------
-insert into t1 values ('09:09:09');
-insert into t1 values ('14:30:20');
-insert into t2 values ('09:09:09');
-insert into t2 values ('14:30:20');
-insert into t2 values ('21:59:22');
-insert into t3 values ('09:09:09');
-insert into t3 values ('14:30:20');
-insert into t3 values ('21:59:22');
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
-select second(col1) from t1 order by col1;
-second(col1)
-9
-20
-select * from t1 order by col1;
-col1
-09:09:09
-14:30:20
-select * from t2 order by col1;
-col1
-09:09:09
-14:30:20
-21:59:22
-select * from t3 order by col1;
-col1
-09:09:09
-14:30:20
-21:59:22
-select * from t4 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t5 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t6 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-update t1 set col1='10:22:33' where col1='09:09:09';
-update t2 set col1='10:22:33' where col1='09:09:09';
-update t3 set col1='10:22:33' where col1='09:09:09';
-update t4 set col1='10:22:33' where col1='09:09:09';
-update t5 set col1='10:22:33' where col1='09:09:09';
-update t6 set col1='10:22:33' where col1='09:09:09';
-select * from t1 order by col1;
-col1
-10:22:33
-14:30:20
-select * from t2 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t3 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t4 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t5 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t6 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
--------------------------------------------------------------------------
----  Alter tables with second(col1)
--------------------------------------------------------------------------
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
-create table t11 engine='MYISAM' as select * from t1;
-create table t22 engine='MYISAM' as select * from t2;
-create table t33 engine='MYISAM' as select * from t3;
-create table t44 engine='MYISAM' as select * from t4;
-create table t55 engine='MYISAM' as select * from t5;
-create table t66 engine='MYISAM' as select * from t6;
-alter table t11
-partition by range(second(col1)) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t22
-partition by list(second(col1)) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t33
-partition by hash(second(col1));
-alter table t44
-partition by range(colint) 
-subpartition by hash(second(col1)) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t55
-partition by list(colint)
-subpartition by hash(second(col1)) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t66
-partition by range(colint) 
-(partition p0 values less than (second('18:30:14')),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-10:22:33
-14:30:20
-select * from t22 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t33 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t44 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t55 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t66 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
----------------------------
----- some alter table begin
----------------------------
-alter table t11
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-10:22:33
-14:30:20
-alter table t11
-reorganize partition s1 into
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-10:22:33
-14:30:20
-alter table t55
-partition by list(colint)
-subpartition by hash(second(col1)) subpartitions 5 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-show create table t55;
-Table	Create Table
-t55	CREATE TABLE `t55` (
-  `colint` int(11) DEFAULT NULL,
-  `col1` char(30) DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY LIST (colint)
-SUBPARTITION BY HASH (second(col1))
-SUBPARTITIONS 5
-(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM,
- PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM,
- PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM,
- PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM,
- PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM,
- PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
-select * from t55 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (second('18:30:14')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (second('18:30:14')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with second(col1)
--------------------------------------------------------------------------
-delete from t1 where col1='14:30:20';
-delete from t2 where col1='14:30:20';
-delete from t3 where col1='14:30:20';
-delete from t4 where col1='14:30:20';
-delete from t5 where col1='14:30:20';
-delete from t6 where col1='14:30:20';
-select * from t1 order by col1;
-col1
-10:22:33
-select * from t2 order by col1;
-col1
-10:22:33
-21:59:22
-select * from t3 order by col1;
-col1
-10:22:33
-21:59:22
-select * from t4 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t5 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-insert into t1 values ('14:30:20');
-insert into t2 values ('14:30:20');
-insert into t3 values ('14:30:20');
-insert into t4 values (60,'14:30:20');
-insert into t5 values (60,'14:30:20');
-insert into t6 values (60,'14:30:20');
-select * from t1 order by col1;
-col1
-10:22:33
-14:30:20
-select * from t2 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t3 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t4 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-60	14:30:20
-select * from t5 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-60	14:30:20
-select * from t6 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-60	14:30:20
-alter table t1 drop partition p0;
-alter table t2 drop partition p0;
-alter table t4 drop partition p0;
-alter table t5 drop partition p0;
-alter table t6 drop partition p0;
-select * from t1 order by col1;
-col1
-10:22:33
-14:30:20
-select * from t2 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t3 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t4 order by colint;
-colint	col1
-60	14:30:20
-select * from t5 order by colint;
-colint	col1
-60	14:30:20
-select * from t6 order by colint;
-colint	col1
-60	14:30:20
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with second(col1)
--------------------------------------------------------------------------
-delete from t11 where col1='14:30:20';
-delete from t22 where col1='14:30:20';
-delete from t33 where col1='14:30:20';
-delete from t44 where col1='14:30:20';
-delete from t55 where col1='14:30:20';
-delete from t66 where col1='14:30:20';
-select * from t11 order by col1;
-col1
-10:22:33
-select * from t22 order by col1;
-col1
-10:22:33
-21:59:22
-select * from t33 order by col1;
-col1
-10:22:33
-21:59:22
-select * from t44 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-select * from t55 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-insert into t11 values ('14:30:20');
-insert into t22 values ('14:30:20');
-insert into t33 values ('14:30:20');
-insert into t44 values (60,'14:30:20');
-insert into t55 values (60,'14:30:20');
-insert into t66 values (60,'14:30:20');
-select * from t11 order by col1;
-col1
-10:22:33
-14:30:20
-select * from t22 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t33 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t44 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-60	14:30:20
-select * from t55 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-60	14:30:20
-select * from t66 order by colint;
-colint	col1
-1	09:09:15.000002
-2	04:30:01.000018
-3	00:59:22.000024
-4	05:30:34.000037
-60	14:30:20
-alter table t11 drop partition p0;
-alter table t22 drop partition p0;
-alter table t44 drop partition p0;
-alter table t55 drop partition p0;
-alter table t66 drop partition p0;
-select * from t11 order by col1;
-col1
-10:22:33
-14:30:20
-select * from t22 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t33 order by col1;
-col1
-10:22:33
-14:30:20
-21:59:22
-select * from t44 order by colint;
-colint	col1
-60	14:30:20
-select * from t55 order by colint;
-colint	col1
-60	14:30:20
-select * from t66 order by colint;
-colint	col1
-60	14:30:20
--------------------------
----- some alter table end
--------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
--------------------------------------------------------------------------
 ---  month(col1)  in partition with coltype  date
 -------------------------------------------------------------------------
 drop table if exists t1 ;
@@ -10549,1024 +9523,6 @@ drop table if exists t44 ;
 drop table if exists t55 ;
 drop table if exists t66 ;
 -------------------------------------------------------------------------
----  to_days(col1)-to_days('2006-01-01')  in partition with coltype  date
--------------------------------------------------------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
--------------------------------------------------------------------------
----  Create tables with to_days(col1)-to_days('2006-01-01')
--------------------------------------------------------------------------
-create table t1 (col1 date) engine='MYISAM' 
-partition by range(to_days(col1)-to_days('2006-01-01')) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t2 (col1 date) engine='MYISAM' 
-partition by list(to_days(col1)-to_days('2006-01-01')) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t3 (col1 date) engine='MYISAM' 
-partition by hash(to_days(col1)-to_days('2006-01-01'));
-create table t4 (colint int, col1 date) engine='MYISAM' 
-partition by range(colint) 
-subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t5 (colint int, col1 date) engine='MYISAM' 
-partition by list(colint)
-subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t6 (colint int, col1 date) engine='MYISAM' 
-partition by range(colint) 
-(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
-partition p1 values less than maxvalue);
--------------------------------------------------------------------------
----  Access tables with to_days(col1)-to_days('2006-01-01') 
--------------------------------------------------------------------------
-insert into t1 values ('2006-02-03');
-insert into t1 values ('2006-01-17');
-insert into t2 values ('2006-02-03');
-insert into t2 values ('2006-01-17');
-insert into t2 values ('2006-01-25');
-insert into t3 values ('2006-02-03');
-insert into t3 values ('2006-01-17');
-insert into t3 values ('2006-01-25');
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
-select to_days(col1)-to_days('2006-01-01') from t1 order by col1;
-to_days(col1)-to_days('2006-01-01')
-16
-33
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-03
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-03
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-03
-select * from t4 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t6 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-update t1 set col1='2006-02-06' where col1='2006-02-03';
-update t2 set col1='2006-02-06' where col1='2006-02-03';
-update t3 set col1='2006-02-06' where col1='2006-02-03';
-update t4 set col1='2006-02-06' where col1='2006-02-03';
-update t5 set col1='2006-02-06' where col1='2006-02-03';
-update t6 set col1='2006-02-06' where col1='2006-02-03';
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t6 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
--------------------------------------------------------------------------
----  Alter tables with to_days(col1)-to_days('2006-01-01')
--------------------------------------------------------------------------
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
-create table t11 engine='MYISAM' as select * from t1;
-create table t22 engine='MYISAM' as select * from t2;
-create table t33 engine='MYISAM' as select * from t3;
-create table t44 engine='MYISAM' as select * from t4;
-create table t55 engine='MYISAM' as select * from t5;
-create table t66 engine='MYISAM' as select * from t6;
-alter table t11
-partition by range(to_days(col1)-to_days('2006-01-01')) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t22
-partition by list(to_days(col1)-to_days('2006-01-01')) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t33
-partition by hash(to_days(col1)-to_days('2006-01-01'));
-alter table t44
-partition by range(colint) 
-subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t55
-partition by list(colint)
-subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t66
-partition by range(colint) 
-(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
----------------------------
----- some alter table begin
----------------------------
-alter table t11
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-alter table t11
-reorganize partition s1 into
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-alter table t55
-partition by list(colint)
-subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 5 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-show create table t55;
-Table	Create Table
-t55	CREATE TABLE `t55` (
-  `colint` int(11) DEFAULT NULL,
-  `col1` date DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY LIST (colint)
-SUBPARTITION BY HASH (to_days(col1)-to_days('2006-01-01'))
-SUBPARTITIONS 5
-(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM,
- PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM,
- PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM,
- PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM,
- PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM,
- PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
--------------------------------------------------------------------------
-delete from t1 where col1='2006-01-17';
-delete from t2 where col1='2006-01-17';
-delete from t3 where col1='2006-01-17';
-delete from t4 where col1='2006-01-17';
-delete from t5 where col1='2006-01-17';
-delete from t6 where col1='2006-01-17';
-select * from t1 order by col1;
-col1
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-insert into t1 values ('2006-01-17');
-insert into t2 values ('2006-01-17');
-insert into t3 values ('2006-01-17');
-insert into t4 values (60,'2006-01-17');
-insert into t5 values (60,'2006-01-17');
-insert into t6 values (60,'2006-01-17');
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t5 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t6 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-alter table t1 drop partition p0;
-alter table t2 drop partition p0;
-alter table t4 drop partition p0;
-alter table t5 drop partition p0;
-alter table t6 drop partition p0;
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-60	2006-01-17
-select * from t5 order by colint;
-colint	col1
-60	2006-01-17
-select * from t6 order by colint;
-colint	col1
-60	2006-01-17
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
--------------------------------------------------------------------------
-delete from t11 where col1='2006-01-17';
-delete from t22 where col1='2006-01-17';
-delete from t33 where col1='2006-01-17';
-delete from t44 where col1='2006-01-17';
-delete from t55 where col1='2006-01-17';
-delete from t66 where col1='2006-01-17';
-select * from t11 order by col1;
-col1
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-insert into t11 values ('2006-01-17');
-insert into t22 values ('2006-01-17');
-insert into t33 values ('2006-01-17');
-insert into t44 values (60,'2006-01-17');
-insert into t55 values (60,'2006-01-17');
-insert into t66 values (60,'2006-01-17');
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-alter table t11 drop partition p0;
-alter table t22 drop partition p0;
-alter table t44 drop partition p0;
-alter table t55 drop partition p0;
-alter table t66 drop partition p0;
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-60	2006-01-17
-select * from t55 order by colint;
-colint	col1
-60	2006-01-17
-select * from t66 order by colint;
-colint	col1
-60	2006-01-17
--------------------------
----- some alter table end
--------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
--------------------------------------------------------------------------
----  datediff(col1, '2006-01-01')  in partition with coltype  date
--------------------------------------------------------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
--------------------------------------------------------------------------
----  Create tables with datediff(col1, '2006-01-01')
--------------------------------------------------------------------------
-create table t1 (col1 date) engine='MYISAM' 
-partition by range(datediff(col1, '2006-01-01')) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t2 (col1 date) engine='MYISAM' 
-partition by list(datediff(col1, '2006-01-01')) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t3 (col1 date) engine='MYISAM' 
-partition by hash(datediff(col1, '2006-01-01'));
-create table t4 (colint int, col1 date) engine='MYISAM' 
-partition by range(colint) 
-subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-create table t5 (colint int, col1 date) engine='MYISAM' 
-partition by list(colint)
-subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-create table t6 (colint int, col1 date) engine='MYISAM' 
-partition by range(colint) 
-(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
-partition p1 values less than maxvalue);
--------------------------------------------------------------------------
----  Access tables with datediff(col1, '2006-01-01') 
--------------------------------------------------------------------------
-insert into t1 values ('2006-02-03');
-insert into t1 values ('2006-01-17');
-insert into t2 values ('2006-02-03');
-insert into t2 values ('2006-01-17');
-insert into t2 values ('2006-01-25');
-insert into t3 values ('2006-02-03');
-insert into t3 values ('2006-01-17');
-insert into t3 values ('2006-01-25');
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
-load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
-select datediff(col1, '2006-01-01') from t1 order by col1;
-datediff(col1, '2006-01-01')
-16
-33
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-03
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-03
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-03
-select * from t4 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t6 order by colint;
-colint	col1
-1	2006-02-03
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-update t1 set col1='2006-02-06' where col1='2006-02-03';
-update t2 set col1='2006-02-06' where col1='2006-02-03';
-update t3 set col1='2006-02-06' where col1='2006-02-03';
-update t4 set col1='2006-02-06' where col1='2006-02-03';
-update t5 set col1='2006-02-06' where col1='2006-02-03';
-update t6 set col1='2006-02-06' where col1='2006-02-03';
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t6 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
--------------------------------------------------------------------------
----  Alter tables with datediff(col1, '2006-01-01')
--------------------------------------------------------------------------
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
-create table t11 engine='MYISAM' as select * from t1;
-create table t22 engine='MYISAM' as select * from t2;
-create table t33 engine='MYISAM' as select * from t3;
-create table t44 engine='MYISAM' as select * from t4;
-create table t55 engine='MYISAM' as select * from t5;
-create table t66 engine='MYISAM' as select * from t6;
-alter table t11
-partition by range(datediff(col1, '2006-01-01')) 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t22
-partition by list(datediff(col1, '2006-01-01')) 
-(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t33
-partition by hash(datediff(col1, '2006-01-01'));
-alter table t44
-partition by range(colint) 
-subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2 
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-alter table t55
-partition by list(colint)
-subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-alter table t66
-partition by range(colint) 
-(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
----------------------------
----- some alter table begin
----------------------------
-alter table t11
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-alter table t11
-reorganize partition s1 into
-(partition p0 values less than (15),
-partition p1 values less than maxvalue);
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-alter table t55
-partition by list(colint)
-subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 5 
-(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
-partition p1 values in (11,12,13,14,15,16,17,18,19,20),
-partition p2 values in (21,22,23,24,25,26,27,28,29,30),
-partition p3 values in (31,32,33,34,35,36,37,38,39,40),
-partition p4 values in (41,42,43,44,45,46,47,48,49,50),
-partition p5 values in (51,52,53,54,55,56,57,58,59,60)
-);
-show create table t55;
-Table	Create Table
-t55	CREATE TABLE `t55` (
-  `colint` int(11) DEFAULT NULL,
-  `col1` date DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY LIST (colint)
-SUBPARTITION BY HASH (datediff(col1, '2006-01-01'))
-SUBPARTITIONS 5
-(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM,
- PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM,
- PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM,
- PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM,
- PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM,
- PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition p0,p1 into
-(partition s1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
-alter table t66
-reorganize partition s1 into
-(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
-partition p1 values less than maxvalue);
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-2	2006-01-17
-3	2006-01-25
-4	2006-02-05
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with datediff(col1, '2006-01-01')
--------------------------------------------------------------------------
-delete from t1 where col1='2006-01-17';
-delete from t2 where col1='2006-01-17';
-delete from t3 where col1='2006-01-17';
-delete from t4 where col1='2006-01-17';
-delete from t5 where col1='2006-01-17';
-delete from t6 where col1='2006-01-17';
-select * from t1 order by col1;
-col1
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-select * from t5 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-insert into t1 values ('2006-01-17');
-insert into t2 values ('2006-01-17');
-insert into t3 values ('2006-01-17');
-insert into t4 values (60,'2006-01-17');
-insert into t5 values (60,'2006-01-17');
-insert into t6 values (60,'2006-01-17');
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t5 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t6 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-alter table t1 drop partition p0;
-alter table t2 drop partition p0;
-alter table t4 drop partition p0;
-alter table t5 drop partition p0;
-alter table t6 drop partition p0;
-select * from t1 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t2 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t3 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t4 order by colint;
-colint	col1
-60	2006-01-17
-select * from t5 order by colint;
-colint	col1
-60	2006-01-17
-select * from t6 order by colint;
-colint	col1
-60	2006-01-17
--------------------------------------------------------------------------
----  Delete rows and partitions of tables with datediff(col1, '2006-01-01')
--------------------------------------------------------------------------
-delete from t11 where col1='2006-01-17';
-delete from t22 where col1='2006-01-17';
-delete from t33 where col1='2006-01-17';
-delete from t44 where col1='2006-01-17';
-delete from t55 where col1='2006-01-17';
-delete from t66 where col1='2006-01-17';
-select * from t11 order by col1;
-col1
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-insert into t11 values ('2006-01-17');
-insert into t22 values ('2006-01-17');
-insert into t33 values ('2006-01-17');
-insert into t44 values (60,'2006-01-17');
-insert into t55 values (60,'2006-01-17');
-insert into t66 values (60,'2006-01-17');
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t55 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-select * from t66 order by colint;
-colint	col1
-1	2006-02-06
-3	2006-01-25
-4	2006-02-05
-60	2006-01-17
-alter table t11 drop partition p0;
-alter table t22 drop partition p0;
-alter table t44 drop partition p0;
-alter table t55 drop partition p0;
-alter table t66 drop partition p0;
-select * from t11 order by col1;
-col1
-2006-01-17
-2006-02-06
-select * from t22 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t33 order by col1;
-col1
-2006-01-17
-2006-01-25
-2006-02-06
-select * from t44 order by colint;
-colint	col1
-60	2006-01-17
-select * from t55 order by colint;
-colint	col1
-60	2006-01-17
-select * from t66 order by colint;
-colint	col1
-60	2006-01-17
--------------------------
----- some alter table end
--------------------------
-drop table if exists t1 ;
-drop table if exists t2 ;
-drop table if exists t3 ;
-drop table if exists t4 ;
-drop table if exists t5 ;
-drop table if exists t6 ;
-drop table if exists t11 ;
-drop table if exists t22 ;
-drop table if exists t33 ;
-drop table if exists t44 ;
-drop table if exists t55 ;
-drop table if exists t66 ;
--------------------------------------------------------------------------
 ---  weekday(col1)  in partition with coltype  date
 -------------------------------------------------------------------------
 drop table if exists t1 ;
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test
index cf3dcfadb27..21c385485cc 100644
--- a/mysql-test/t/partition.test
+++ b/mysql-test/t/partition.test
@@ -1898,15 +1898,16 @@ while ($cnt)
 drop table t1;
 
 #
-# BUG#32272: partition crash 1: enum column
+# BUG#32772: partition crash 1: enum column
 #
+# Note that month(int_col) is disallowed after bug#54483.
 create table t1 (
   c0 int,
   c1 bigint,
   c2 set('sweet'),
   key (c2,c1,c0), 
   key(c0)
-) engine=myisam partition by hash (month(c0)) partitions 5;
+) engine=myisam partition by hash (c0) partitions 5;
 
 --disable_warnings
 insert ignore into t1 set c0 = -6502262, c1 = 3992917, c2 = 35019;
diff --git a/mysql-test/t/partition_error.test b/mysql-test/t/partition_error.test
index 213778764b5..1e0032a2bfa 100644
--- a/mysql-test/t/partition_error.test
+++ b/mysql-test/t/partition_error.test
@@ -11,6 +11,667 @@ drop table if exists t1, t2;
 let $MYSQLD_DATADIR= `SELECT @@datadir`;
 
 --echo #
+--echo # Bug#54483: valgrind errors when making warnings for multiline inserts
+--echo #            into partition
+--echo #
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARBINARY(10))
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a CHAR(10))
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIMESTAMP)
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+CREATE TABLE t1 (a DATE)
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+INSERT INTO t1 VALUES ('test'),('a'),('5');
+SHOW WARNINGS;
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+INSERT INTO t1 VALUES ('test'),('a'),('5');
+SHOW WARNINGS;
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY RANGE (DAYOFWEEK(a))
+(PARTITION a1 VALUES LESS THAN (60));
+SHOW WARNINGS;
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (TO_DAYS(a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (TO_DAYS(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (TO_DAYS(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (TO_DAYS(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (TO_DAYS(a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (DAYOFMONTH(a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (DAYOFMONTH(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (DAYOFMONTH(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (DAYOFMONTH(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (DAYOFMONTH(a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (MONTH(a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (MONTH(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (MONTH(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (MONTH(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (MONTH(a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (DAYOFYEAR(a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (DAYOFYEAR(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (DAYOFYEAR(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (DAYOFYEAR(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (DAYOFYEAR(a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (HOUR(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (HOUR(a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (HOUR(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (HOUR(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (HOUR(a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (MINUTE(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (MINUTE(a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (MINUTE(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (MINUTE(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (MINUTE(a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (QUARTER(a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (QUARTER(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (QUARTER(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (QUARTER(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (QUARTER(a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (SECOND(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (SECOND(a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (SECOND(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (SECOND(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (SECOND(a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (YEARWEEK(a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (YEARWEEK(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (YEARWEEK(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (YEARWEEK(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (YEARWEEK(a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (WEEKDAY(a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (WEEKDAY(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (WEEKDAY(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (WEEKDAY(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (WEEKDAY(a));
+
+--echo # TO_SECONDS() is added in 5.5.
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (TO_SECONDS(a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (TO_SECONDS(a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (TO_SECONDS(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (TO_SECONDS(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (TO_SECONDS(a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (TIME_TO_SEC(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (TIME_TO_SEC(a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (TIME_TO_SEC(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (TIME_TO_SEC(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (TIME_TO_SEC(a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (FROM_DAYS(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (FROM_DAYS(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (FROM_DAYS(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (TO_DAYS(FROM_DAYS(a)));
+--error ER_PARTITION_FUNC_NOT_ALLOWED_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (FROM_DAYS(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (TO_DAYS(FROM_DAYS(a)));
+--error ER_PARTITION_FUNC_NOT_ALLOWED_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (FROM_DAYS(a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (MICROSECOND(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (MICROSECOND(a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (MICROSECOND(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (MICROSECOND(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (MICROSECOND(a));
+--echo # Bug#57071
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1
+(`date` date,
+ `extracted_week` int,
+ `yearweek` int,
+ `week` int,
+ `default_week_format` int)
+PARTITION BY LIST (EXTRACT(WEEK FROM date) % 3)
+(PARTITION p0 VALUES IN (0),
+ PARTITION p1 VALUES IN (1),
+ PARTITION p2 VALUES IN (2));
+CREATE TABLE t1
+(`date` date,
+ `extracted_week` int,
+ `yearweek` int,
+ `week` int,
+ `default_week_format` int);
+SET @old_default_week_format := @@default_week_format;
+SET default_week_format = 0;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 1;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 2;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 3;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 4;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 5;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 6;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SET default_week_format = 7;
+INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
+SELECT * FROM t1;
+SET default_week_format = @old_default_week_format;
+DROP TABLE t1;
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(YEAR FROM a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(YEAR FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(YEAR FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(YEAR FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(YEAR FROM a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(QUARTER FROM a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(QUARTER FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(QUARTER FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(QUARTER FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(QUARTER FROM a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(MONTH FROM a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(MONTH FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(MONTH FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(MONTH FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(MONTH FROM a));
+
+--echo # EXTRACT(WEEK...) is disallowed, see bug#57071.
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(WEEK FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(WEEK FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(WEEK FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(WEEK FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(WEEK FROM a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(DAY FROM a));
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(DAY FROM a));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(DAY FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(DAY FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(DAY FROM a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(HOUR FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(HOUR FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(HOUR FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(HOUR FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(HOUR FROM a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(MINUTE FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(MINUTE FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(MINUTE FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(MINUTE FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(MINUTE FROM a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(SECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(SECOND FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(SECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(SECOND FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(SECOND FROM a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
+
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a TIME, b DATE)
+PARTITION BY HASH (DATEDIFF(a, b));
+CREATE TABLE t1 (a DATE, b DATETIME)
+PARTITION BY HASH (DATEDIFF(a, b));
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME, b DATE)
+PARTITION BY HASH (DATEDIFF(a, b));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE, b VARCHAR(10))
+PARTITION BY HASH (DATEDIFF(a, b));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT, b DATETIME)
+PARTITION BY HASH (DATEDIFF(a, b));
+
+CREATE TABLE t1 (a TIME)
+PARTITION BY HASH (TIME_TO_SEC(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a DATE)
+PARTITION BY HASH (TIME_TO_SEC(a));
+CREATE TABLE t1 (a DATETIME)
+PARTITION BY HASH (TIME_TO_SEC(a));
+DROP TABLE t1;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a VARCHAR(10))
+PARTITION BY HASH (TIME_TO_SEC(a));
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (TIME_TO_SEC(a));
+
+
+--echo #
 --echo # Bug#50036: Inconsistent errors when using TIMESTAMP
 --echo #            columns/expressions
 
diff --git a/sql/item.h b/sql/item.h
index 3fa11cfd8dd..d86e64eac09 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1092,11 +1092,11 @@ public:
   virtual bool set_no_const_sub(uchar *arg) { return FALSE; }
   virtual Item *replace_equal_field(uchar * arg) { return this; }
   /*
-    Check if an expression value depends on the current timezone. Used by
-    partitioning code to reject timezone-dependent expressions in a
-    (sub)partitioning function.
+    Check if an expression value has allowed arguments, like DATE/DATETIME
+    for date functions. Also used by partitioning code to reject
+    timezone-dependent expressions in a (sub)partitioning function.
   */
-  virtual bool is_timezone_dependent_processor(uchar *bool_arg)
+  virtual bool check_valid_arguments_processor(uchar *bool_arg)
   {
     return FALSE;
   }
diff --git a/sql/item_func.h b/sql/item_func.h
index 1139442fe76..937907c9404 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -237,6 +237,7 @@ public:
   {
     return (error == E_DEC_OVERFLOW) ? raise_decimal_overflow() : error;
   }
+
   bool has_timestamp_args()
   {
     DBUG_ASSERT(fixed == TRUE);
@@ -248,6 +249,45 @@ public:
     }
     return FALSE;
   }
+
+  bool has_date_args()
+  {
+    DBUG_ASSERT(fixed == TRUE);
+    for (uint i= 0; i < arg_count; i++)
+    {
+      if (args[i]->type() == Item::FIELD_ITEM &&
+          (args[i]->field_type() == MYSQL_TYPE_DATE ||
+           args[i]->field_type() == MYSQL_TYPE_DATETIME))
+        return TRUE;
+    }
+    return FALSE;
+  }
+
+  bool has_time_args()
+  {
+    DBUG_ASSERT(fixed == TRUE);
+    for (uint i= 0; i < arg_count; i++)
+    {
+      if (args[i]->type() == Item::FIELD_ITEM &&
+          (args[i]->field_type() == MYSQL_TYPE_TIME ||
+           args[i]->field_type() == MYSQL_TYPE_DATETIME))
+        return TRUE;
+    }
+    return FALSE;
+  }
+
+  bool has_datetime_args()
+  {
+    DBUG_ASSERT(fixed == TRUE);
+    for (uint i= 0; i < arg_count; i++)
+    {
+      if (args[i]->type() == Item::FIELD_ITEM &&
+          args[i]->field_type() == MYSQL_TYPE_DATETIME)
+        return TRUE;
+    }
+    return FALSE;
+  }
+
   /*
     We assume the result of any function that has a TIMESTAMP argument to be
     timezone-dependent, since a TIMESTAMP value in both numeric and string
@@ -256,7 +296,7 @@ public:
     representation of a TIMESTAMP argument verbatim, and thus does not depend on
     the timezone.
    */
-  virtual bool is_timezone_dependent_processor(uchar *bool_arg)
+  virtual bool check_valid_arguments_processor(uchar *bool_arg)
   {
     return has_timestamp_args();
   }
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 72a5aa0c296..622d6a76e50 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -75,6 +75,10 @@ public:
   enum_monotonicity_info get_monotonicity_info() const;
   longlong val_int_endpoint(bool left_endp, bool *incl_endp);
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_date_args();
+  }
 };
 
 
@@ -102,6 +106,12 @@ public:
     *input_version= output_version;
     return 0;
   }
+
+  /* Only meaningful with date part and optional time part */
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_date_args();
+  }
 };
 
 
@@ -118,6 +128,10 @@ public:
     maybe_null=1; 
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_date_args();
+  }
 };
 
 
@@ -142,6 +156,10 @@ public:
     maybe_null= 1;
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_date_args();
+  }
 };
 
 
@@ -171,6 +189,10 @@ public:
     maybe_null= 1;
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_date_args();
+  }
 };
 
 
@@ -187,6 +209,10 @@ public:
     maybe_null=1;
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_time_args();
+  }
 };
 
 
@@ -203,6 +229,10 @@ public:
     maybe_null=1;
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_time_args();
+  }
 };
 
 
@@ -219,6 +249,10 @@ public:
      maybe_null=1;
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_date_args();
+  }
 };
 
 
@@ -235,6 +269,10 @@ public:
     maybe_null=1;
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_time_args();
+  }
 };
 
 
@@ -265,6 +303,10 @@ public:
     maybe_null=1;
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_date_args();
+  }
 };
 
 
@@ -283,6 +325,10 @@ public:
     maybe_null=1;
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_date_args();
+  }
 };
 
 
@@ -312,6 +358,10 @@ public:
     maybe_null= 1;
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_date_args();
+  }
 };
 
 class Item_func_dayname :public Item_func_weekday
@@ -341,7 +391,7 @@ public:
     (and thus may not be used as a partitioning function)
     when its argument is NOT of the TIMESTAMP type.
   */
-  bool is_timezone_dependent_processor(uchar *int_arg)
+  bool check_valid_arguments_processor(uchar *int_arg)
   {
     return !has_timestamp_args();
   }
@@ -366,6 +416,10 @@ public:
     max_length=10*MY_CHARSET_BIN_MB_MAXLEN;
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_time_args();
+  }
 };
 
 
@@ -621,6 +675,10 @@ public:
   const char *func_name() const { return "from_days"; }
   bool get_date(MYSQL_TIME *res, uint fuzzy_date);
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return has_date_args() || has_time_args();
+  }
 };
 
 
@@ -746,6 +804,42 @@ class Item_extract :public Item_int_func
   bool eq(const Item *item, bool binary_cmp) const;
   virtual void print(String *str, enum_query_type query_type);
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    switch (int_type) {
+    case INTERVAL_YEAR:
+    case INTERVAL_YEAR_MONTH:
+    case INTERVAL_QUARTER:
+    case INTERVAL_MONTH:
+    /* case INTERVAL_WEEK: Not allowed as partitioning function, bug#57071 */
+    case INTERVAL_DAY:
+      return !has_date_args();
+    case INTERVAL_DAY_HOUR:
+    case INTERVAL_DAY_MINUTE:
+    case INTERVAL_DAY_SECOND:
+    case INTERVAL_DAY_MICROSECOND:
+      return !has_datetime_args();
+    case INTERVAL_HOUR:
+    case INTERVAL_HOUR_MINUTE:
+    case INTERVAL_HOUR_SECOND:
+    case INTERVAL_MINUTE:
+    case INTERVAL_MINUTE_SECOND:
+    case INTERVAL_SECOND:
+    case INTERVAL_MICROSECOND:
+    case INTERVAL_HOUR_MICROSECOND:
+    case INTERVAL_MINUTE_MICROSECOND:
+    case INTERVAL_SECOND_MICROSECOND:
+      return !has_time_args();
+    default:
+      /*
+        INTERVAL_LAST is only an end marker,
+        INTERVAL_WEEK depends on default_week_format which is a session
+        variable and cannot be used for partitioning. See bug#57071.
+      */
+      break;
+    }
+    return true;
+  }
 };
 
 
@@ -991,6 +1085,10 @@ public:
     maybe_null=1;
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_valid_arguments_processor(uchar *int_arg)
+  {
+    return !has_time_args();
+  }
 };
 
 
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index cec047d11fc..0b4f956dd7f 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -1130,12 +1130,13 @@ static bool fix_fields_part_func(THD *thd, Item* func_expr, TABLE *table,
   }
 
   /*
-    We don't allow creating partitions with timezone-dependent expressions as
-    a (sub)partitioning function, but we want to allow such expressions when
-    opening existing tables for easier maintenance. This exception should be
-    deprecated at some point in future so that we always throw an error.
+    We don't allow creating partitions with expressions with non matching
+    arguments as a (sub)partitioning function,
+    but we want to allow such expressions when opening existing tables for
+    easier maintenance. This exception should be deprecated at some point
+    in future so that we always throw an error.
   */
-  if (func_expr->walk(&Item::is_timezone_dependent_processor,
+  if (func_expr->walk(&Item::check_valid_arguments_processor,
                       0, NULL))
   {
     if (is_create_table_ind)
diff --git a/sql/table.cc b/sql/table.cc
index 8cd2e9e9bab..fae044f334a 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -1963,8 +1963,8 @@ int open_table_from_share(THD *thd, TABLE_SHARE *share, const char *alias,
     {
       if (work_part_info_used)
         tmp= fix_partition_func(thd, outparam, is_create_table);
-      outparam->part_info->item_free_list= part_func_arena.free_list;
     }
+    outparam->part_info->item_free_list= part_func_arena.free_list;
 partititon_err:
     if (tmp)
     {