bugfix: TIME_FORMAT() allowed some non-time format specifiers

it contradicted the manual and was inconsistent

5 files changed, 44 insertions, 8 deletions

diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result
index 8439c40fbf9..16dbdf7969c 100644
--- a/mysql-test/r/date_formats.result
+++ b/mysql-test/r/date_formats.result
@@ -558,3 +558,18 @@ SET NAMES latin1;
 #
 # End of 5.1 tests
 #
+select time_format('2001-01-01 02:02:02', '%d.%m.%Y');
+time_format('2001-01-01 02:02:02', '%d.%m.%Y')
+NULL
+select time_format('2001-01-01 02:02:02', '%d %T');
+time_format('2001-01-01 02:02:02', '%d %T')
+NULL
+select time_format('01 02:02:02', '%d %T');
+time_format('01 02:02:02', '%d %T')
+NULL
+select time_format('01 02:02:02', '%T');
+time_format('01 02:02:02', '%T')
+26:02:02
+select time_format('2001-01-01 02:02:02', '%T');
+time_format('2001-01-01 02:02:02', '%T')
+02:02:02
diff --git a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result
index 9413dbdace7..01d20e4975a 100644
--- a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result
+++ b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result
@@ -2911,16 +2911,16 @@ drop table t1;
 set sql_warnings = 0;
 # TIME_FORMAT()
 set sql_warnings = 1;
-create table t1 (a datetime, b varchar(10) as (time_format(a,"%d.%m.%Y")));
+create table t1 (a datetime, b varchar(10) as (time_format(a,"%H.%i.%S")));
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` datetime DEFAULT NULL,
-  `b` varchar(10) GENERATED ALWAYS AS (time_format(`a`,'%d.%m.%Y')) VIRTUAL
+  `b` varchar(10) GENERATED ALWAYS AS (time_format(`a`,'%H.%i.%S')) VIRTUAL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
-insert into t1 values ('2001-01-01 02:02:02',default);
+insert into t1 values ('2001-01-01 02:03:04',default);
 select * from t1;
 a	b
-2001-01-01 02:02:02	01.01.2001
+2001-01-01 02:03:04	02.03.04
 drop table t1;
 set sql_warnings = 0;
diff --git a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc
index 4a95ea75534..4c20a104037 100644
--- a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc
+++ b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc
@@ -1204,8 +1204,8 @@ let $rows = 1;
 --source suite/vcol/inc/vcol_supported_sql_funcs.inc
 
 --echo # TIME_FORMAT()
-let $cols = a datetime, b varchar(10) as (time_format(a,"%d.%m.%Y"));
-let $values1 = '2001-01-01 02:02:02',default;
+let $cols = a datetime, b varchar(10) as (time_format(a,"%H.%i.%S"));
+let $values1 = '2001-01-01 02:03:04',default;
 let $rows = 1;
 --source suite/vcol/inc/vcol_supported_sql_funcs.inc
 
diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test
index 972543aefc2..3bf6fabbf6d 100644
--- a/mysql-test/t/date_formats.test
+++ b/mysql-test/t/date_formats.test
@@ -366,3 +366,12 @@ SET NAMES latin1;
 --echo #
 --echo # End of 5.1 tests
 --echo #
+
+#
+# TIME_FORMAT and non-time format specifiers
+#
+select time_format('2001-01-01 02:02:02', '%d.%m.%Y');
+select time_format('2001-01-01 02:02:02', '%d %T');
+select time_format('01 02:02:02', '%d %T');
+select time_format('01 02:02:02', '%T');
+select time_format('2001-01-01 02:02:02', '%T');
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 599d818cf3f..6e476dfa746 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -477,14 +477,14 @@ static bool make_date_time(DATE_TIME_FORMAT *format, MYSQL_TIME *l_time,
     {
       switch (*++ptr) {
       case 'M':
-        if (!l_time->month)
+        if (type == MYSQL_TIMESTAMP_TIME || !l_time->month)
           return 1;
         str->append(locale->month_names->type_names[l_time->month-1],
                     (uint) strlen(locale->month_names->type_names[l_time->month-1]),
                     system_charset_info);
         break;
       case 'b':
-        if (!l_time->month)
+        if (type == MYSQL_TIMESTAMP_TIME || !l_time->month)
           return 1;
         str->append(locale->ab_month_names->type_names[l_time->month-1],
                     (uint) strlen(locale->ab_month_names->type_names[l_time->month-1]),
@@ -534,26 +534,38 @@ static bool make_date_time(DATE_TIME_FORMAT *format, MYSQL_TIME *l_time,
 	}
 	break;
       case 'Y':
+        if (type == MYSQL_TIMESTAMP_TIME)
+          return 1;
 	length= (uint) (int10_to_str(l_time->year, intbuff, 10) - intbuff);
 	str->append_with_prefill(intbuff, length, 4, '0');
 	break;
       case 'y':
+        if (type == MYSQL_TIMESTAMP_TIME)
+          return 1;
 	length= (uint) (int10_to_str(l_time->year%100, intbuff, 10) - intbuff);
 	str->append_with_prefill(intbuff, length, 2, '0');
 	break;
       case 'm':
+        if (type == MYSQL_TIMESTAMP_TIME)
+          return 1;
 	length= (uint) (int10_to_str(l_time->month, intbuff, 10) - intbuff);
 	str->append_with_prefill(intbuff, length, 2, '0');
 	break;
       case 'c':
+        if (type == MYSQL_TIMESTAMP_TIME)
+          return 1;
 	length= (uint) (int10_to_str(l_time->month, intbuff, 10) - intbuff);
 	str->append_with_prefill(intbuff, length, 1, '0');
 	break;
       case 'd':
+	if (type == MYSQL_TIMESTAMP_TIME)
+	  return 1;
 	length= (uint) (int10_to_str(l_time->day, intbuff, 10) - intbuff);
 	str->append_with_prefill(intbuff, length, 2, '0');
 	break;
       case 'e':
+	if (type == MYSQL_TIMESTAMP_TIME)
+	  return 1;
 	length= (uint) (int10_to_str(l_time->day, intbuff, 10) - intbuff);
 	str->append_with_prefill(intbuff, length, 1, '0');
 	break;