MySQL Bugs: #83765: unreasonable behavior for extracting year from time

Bug #83765	unreasonable behavior for extracting year from time
Submitted:	10 Nov 2016 2:46	Modified:	10 Nov 2016 7:09
Reporter:	帅 Bang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.34, 5.7.16	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
mysql> select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 18:10:42  |
+-----------+
1 row in set (0.00 sec)

quite ok.  what CURTIME returns is current time, neither current datetime nor current timestamp.

mysql> select extract(YEAR from CURTIME());
+------------------------------+
| extract(YEAR from CURTIME()) |
+------------------------------+
|                            2016 |
+------------------------------+
1 row in set (0.00 sec)

IMHO, this is unreasonable since there is no year part in terms of a time value.

If CURTIME() were a datetime value or timestamp value, i think this could be quite ok.

How to repeat:
select CURTIME();
select extract(YEAR from CURTIME());

Suggested fix:
it is highly, highly, highly recommended for mysql to  return 0 or NULL with a warning when select extract(YEAR from CURTIME()) is performed

Hello Bang,

Thank you for the report and test case.
Verified as described with 5.6.34/5.7.16.

Thanks,
Umesh

-- 5.6.34 -- affected

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.34: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.34-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, 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> select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 05:24:36  |
+-----------+
1 row in set (0.00 sec)

mysql> select extract(YEAR from CURTIME());
+------------------------------+
| extract(YEAR from CURTIME()) |
+------------------------------+
|                         2016 |
+------------------------------+
1 row in set (0.00 sec)

-- 5.7.16 - affected

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.16: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 05:25:26  |
+-----------+
1 row in set (0.00 sec)

mysql> select extract(YEAR from CURTIME());
+------------------------------+
| extract(YEAR from CURTIME()) |
+------------------------------+
|                         2016 |
+------------------------------+
1 row in set (0.00 sec)

mysql>

Looks like regression to me

-- 5.5.53

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.5.53: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.53-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, 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> select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 05:28:47  |
+-----------+
1 row in set (0.00 sec)

mysql> select extract(YEAR from CURTIME());
+------------------------------+
| extract(YEAR from CURTIME()) |
+------------------------------+
|                         NULL |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

Posted by developer:
 
This is documented behavior (https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-conversion.html):

For conversion of TIME values to other temporal types, the value of CURRENT_DATE() is used for the date part. The TIME is interpreted as elapsed time (not time of day) and added to the date. This means that the date part of the result differs from the current date if the time value is outside the range from '00:00:00' to '23:59:59'. 

This conversion behavior is also seen when inserting the value of CURTIME() into columns of different type:

mysql> CREATE TABLE t25 (
    ->   col_int INTEGER,
    ->   col_char VARCHAR(20),
    ->   col_year YEAR,
    ->   col_date DATE,
    ->   col_time TIME,
    ->   col_datetime DATETIME);
Query OK, 0 rows affected (0,00 sec)

mysql> INSERT INTO t25 VALUES (curtime(), curtime(), curtime(), curtime(), curtime(), curtime());
Query OK, 1 row affected, 1 warning (0,01 sec)

mysql> SELECT * FROM t25;
+---------+----------+----------+------------+----------+---------------------+
| col_int | col_char | col_year | col_date   | col_time | col_datetime        |
+---------+----------+----------+------------+----------+---------------------+
|   80236 | 08:02:36 |     2016 | 2016-11-10 | 08:02:36 | 2016-11-10 08:02:36 |
+---------+----------+----------+------------+----------+---------------------+
1 row in set (0,00 sec)