The return type of UNIX_TIMESTAMP() is confusing in MySQL

[wjhuang2016]

General Question

According to #9729,

select @a:=FROM_UNIXTIME(1);
select unix_timestamp(@a);

will should return 1 instead of 1.000000.
And select unix_timestamp(1) will also return 1.

When I try to solve this problem, I found that in MySQL:

mysql> create table a(id char(12));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into a values("2017-00-02");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT UNIX_TIMESTAMP(id) from a;
+--------------------+
| UNIX_TIMESTAMP(id) |
+--------------------+
|           0.000000 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP("2017-00-02");
+------------------------------+
| UNIX_TIMESTAMP("2017-00-02") |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

and

mysql> SELECT UNIX_TIMESTAMP("");
+--------------------+
| UNIX_TIMESTAMP("") |
+--------------------+
|           0.000000 |
+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP("1");
+---------------------+
| UNIX_TIMESTAMP("1") |
+---------------------+
|            0.000000 |
+---------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP("00000");
+-------------------------+
| UNIX_TIMESTAMP("00000") |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

Finaly, in TiDB:

mysql> SELECT UNIX_TIMESTAMP("");
+--------------------+
| UNIX_TIMESTAMP("") |
+--------------------+
|               NULL |
+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP("1");
+---------------------+
| UNIX_TIMESTAMP("1") |
+---------------------+
|                NULL |
+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP("00000");
+-------------------------+
| UNIX_TIMESTAMP("00000") |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

In MySQL Doc:https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_unix-timestamp
We can see that:

The return value is an integer if no argument is given or the argument does not include a fractional seconds part, or DECIMAL if an argument is given that includes a fractional seconds part.

The return type of UNIX_TIMESTAMP() is so confusing in MySQL that we need to make it clear to implement UNIX_TIMESTAMP() in TiDB properly.

[wjhuang2016]

@eurekaka PTAL.

[jackysp]

Thanks for your report, @wjhuang2016 !
Yes, we need to make it clear in TiDB.

[eurekaka]

@morgo @kolbe Please help us confirm the behavior here.

[morgo]

I don't have a good explanation for it, but I tried a couple more examples:

mysql [localhost:8015] {msandbox} ((none)) > SELECT UNIX_TIMESTAMP("2017-00-00");
+------------------------------+
| UNIX_TIMESTAMP("2017-00-00") |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8015] {msandbox} ((none)) > SELECT UNIX_TIMESTAMP("2017-00-99");
+------------------------------+
| UNIX_TIMESTAMP("2017-00-99") |
+------------------------------+
|                     0.000000 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

Is it possible that invalid values result in decimal return values unless the reason it is invalid is because of a zero value?

[wjhuang2016]

@morgo I think it's more complecate here.

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

MySQL does not accept TIMESTAMP values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special “zero” value '0000-00-00 00:00:00'

mysql> select cast("2017-00-99" as date);
+----------------------------+
| cast("2017-00-99" as date) |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2017-00-99' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql>  select cast("2017-00-00" as date);
+----------------------------+
| cast("2017-00-00" as date) |
+----------------------------+
| 2017-00-00                 |
+----------------------------+
1 row in set (0.00 sec)

So, "2017-00-00" is a valid date, which doesn't follow the doc

[kolbe]

I think the specifics of why MySQL does what it does in these different cases is sort of irrelevant. There's lots of historical stuff that's happened with date/time handling in MySQL, so it's not very surprising that there are some surprising return values. I guess the question is whether TiDB wants to prioritize MySQL compatibility or prioritize some kind of well-justified handling of erroneous inputs.

I don't think there are probably a large number of users relying on the data type of a completely nonsensical value given to the UNIX_TIMESTAMP() function, so I propose that UNIX_TIMESTAMP() in TiDB return a decimal only when invoked with an argument with fractional second components and return an integer in all other cases. Returning NULL instead of a numeric value certainly makes more sense for truly invalid values, but it breaks MySQL compatibility, so I nominate @morgo to make the definitive recommendation as the MySQL-compatibility guru.

[morgo]

I am fine with not being fully compatible with MySQL in these specific cases / declaring the MySQL behavior as a bug.

In general though, it can be difficult to judge when a case is important, so I appreciate the CC. Thanks!

[wjhuang2016]

I think the specifics of why MySQL does what it does in these different cases is sort of irrelevant. There's lots of historical stuff that's happened with date/time handling in MySQL, so it's not very surprising that there are some surprising return values. I guess the question is whether TiDB wants to prioritize MySQL compatibility or prioritize some kind of well-justified handling of erroneous inputs.

I don't think there are probably a large number of users relying on the data type of a completely nonsensical value given to the UNIX_TIMESTAMP() function, so I propose that UNIX_TIMESTAMP() in TiDB return a decimal only when invoked with an argument with fractional second components and return an integer in all other cases. Returning NULL instead of a numeric value certainly makes more sense for truly invalid values, but it breaks MySQL compatibility, so I nominate @morgo to make the definitive recommendation as the MySQL-compatibility guru.

I'm in favor of your proposal. We should return a decimal only when invoked with an argument with fractional second components.
And I think SELECT UNIX_TIMESTAMP(id) from a; would be a exception. Before we eval the column we need to decide the return type of the function.