MySQL Bugs: #79452: In create table, default for time column can't be specified with time operator

Bug #79452	In create table, default for time column can't be specified with time operator
Submitted:	30 Nov 2015 9:41	Modified:	30 Nov 2015 11:02
Reporter:	Su Dylan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6/5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Output:
=======
mysql> create table t5(c5 time default time '10:10:11');
ERROR 1067 (42000): Invalid default value for 'c5'
mysql> create table t5(c5 time default '10:10:11');
ERROR 1050 (42S01): Table 't5' already exists
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
In create table, default for time column can't be specified with time operator

How to repeat:
create table t5(c5 time default time '10:10:11');
create table t5(c5 time default '10:10:11');

Suggested fix:
Create table stmt should succeed with default value for time column specified with time operator.

Thank you for the bug report. Only 5.6/5.7 affected.

C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.97-Win X64 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test
Database changed
mysql 5.0 > drop table t5;
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > create table t5(c5 time default time '10:10:11');
Query OK, 0 rows affected (0.01 sec)

mysql 5.0 > show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `c5` time default '10:10:11'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.28 Source distribution PULL: 2015-NOV-07

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test
Database changed
mysql 5.6 > create table t5(c5 time default time '10:10:11');
ERROR 1067 (42000): Invalid default value for 'c5'

C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.74-Win X64 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 > use test
Database changed
mysql 5.1 > create table t5(c5 time default time '10:10:11');
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 > show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `c5` time DEFAULT '10:10:11'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.47 Source distribution PULL: 2015-NOV-07

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > use test
Database changed
mysql 5.5 > create table t5(c5 time default time '10:10:11');
Query OK, 0 rows affected (0.05 sec)

mysql 5.5 > show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `c5` time DEFAULT '10:10:11'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.11 Source distribution PULL: 2015-NOV-25

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > create table t5(c5 time default time '10:10:11');
ERROR 1067 (42000): Invalid default value for 'c5'