expression: `unix_timestamp` is not compatible with MySQL

[qw4990]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

select unix_timestamp("20121010x");

2. What did you expect to see?
   In MySQL

mysql> select unix_timestamp("20121010x");
+-----------------------------+
| unix_timestamp("20121010x") |
+-----------------------------+
|                    0.000000 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

3. What did you see instead?
   In TiDB

mysql> select unix_timestamp("20121010x");
+-----------------------------+
| unix_timestamp("20121010x") |
+-----------------------------+
|                        NULL |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta.1-64-g7edf4c0c6
Git Commit Hash: 7edf4c0c65129e584695aa0273314457eed04364
Git Branch: master
UTC Build Time: 2019-04-04 09:19:42
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[qw4990]

The reason for this issue is below:
When building unixTimestamp, since the first argument is string, it will be cast to datetime by builtinCastStringAsTimeSig.
In builtinCastStringAsTimeSig, it will ignore InvalidErr and return Null.
In MySQL, if it's a invalid string, unixTimestamp will return 0, but in TiDB, unixTimestamp doesn't know it's a invalid string.

[wjhuang2016]

#9861 has discussed this problem.
As @kolbe pointed out, return NULL makes more sense than return 0.
Should we really need to reutrn 0 here?

[qw4990]

#9861 has discussed this problem.
As @kolbe pointed out, return NULL makes more sense than return 0.
Should we really need to reutrn 0 here?

Oh it's my fault that I don't see your discussion before.
And I agree with you that return NULL is more reasonable than 0.
This issue will be closed.