Function dayname is incompatible 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 dayname("1962-03-01")+0;
select dayname("1962-03-02")+0;
select dayname("1962-03-03")+0;
select dayname("1962-03-04")+0;
select dayname("1962-03-05")+0;
select dayname("1962-03-06")+0;
select dayname("1962-03-07")+0;
select dayname("1962-03-08")+0;
select dayname("1962-03-01")+1;
select dayname("1962-03-01")+2;
select dayname("1962-03-01")+3;
select dayname("1962-03-01")+4;
select dayname("1962-03-01")+5;
select dayname("1962-03-01")+6;
select dayname("1962-03-01")+7;
select dayname("1962-03-01")+2333;
select dayname("1962-03-01")+2.333;

select dayname("1962-03-01")>2;
select dayname("1962-03-01")<2;
select dayname("1962-03-01")=3;
select dayname("1962-03-01")!=3;
select dayname("1962-03-01")<4;
select dayname("1962-03-01")>4;
select !dayname("1962-03-01");
select dayname("1962-03-01")&1;
select dayname("1962-03-01")&3;
select dayname("1962-03-01")&7;
select dayname("1962-03-01")|1;
select dayname("1962-03-01")|3;
select dayname("1962-03-01")|7;
select dayname("1962-03-01")^1;
select dayname("1962-03-01")^3;
select dayname("1962-03-01")^7;

2. What did you expect to see?

mysql> select dayname("1962-03-01")+0;
+-------------------------+
| dayname("1962-03-01")+0 |
+-------------------------+
|                       3 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-02")+0;
+-------------------------+
| dayname("1962-03-02")+0 |
+-------------------------+
|                       4 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-03")+0;
+-------------------------+
| dayname("1962-03-03")+0 |
+-------------------------+
|                       5 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-04")+0;
+-------------------------+
| dayname("1962-03-04")+0 |
+-------------------------+
|                       6 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-05")+0;
+-------------------------+
| dayname("1962-03-05")+0 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-06")+0;
+-------------------------+
| dayname("1962-03-06")+0 |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-07")+0;
+-------------------------+
| dayname("1962-03-07")+0 |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-08")+0;
+-------------------------+
| dayname("1962-03-08")+0 |
+-------------------------+
|                       3 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-01")+1;
+-------------------------+
| dayname("1962-03-01")+1 |
+-------------------------+
|                       4 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-01")+2;
+-------------------------+
| dayname("1962-03-01")+2 |
+-------------------------+
|                       5 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-01")+3;
+-------------------------+
| dayname("1962-03-01")+3 |
+-------------------------+
|                       6 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-01")+4;
+-------------------------+
| dayname("1962-03-01")+4 |
+-------------------------+
|                       7 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-01")+5;
+-------------------------+
| dayname("1962-03-01")+5 |
+-------------------------+
|                       8 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-01")+6;
+-------------------------+
| dayname("1962-03-01")+6 |
+-------------------------+
|                       9 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-01")+7;
+-------------------------+
| dayname("1962-03-01")+7 |
+-------------------------+
|                      10 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-01")+2333;
+----------------------------+
| dayname("1962-03-01")+2333 |
+----------------------------+
|                       2336 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select dayname("1962-03-01")+2.333;
+-----------------------------+
| dayname("1962-03-01")+2.333 |
+-----------------------------+
|                       5.333 |
+-----------------------------+
1 row in set (0.00 sec)

3. What did you see instead?
   In TiDB

mysql> select dayname("1962-03-01")+0;
+-------------------------+
| dayname("1962-03-01")+0 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-02")+0;
+-------------------------+
| dayname("1962-03-02")+0 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-03")+0;
+-------------------------+
| dayname("1962-03-03")+0 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-04")+0;
+-------------------------+
| dayname("1962-03-04")+0 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-05")+0;
+-------------------------+
| dayname("1962-03-05")+0 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-06")+0;
+-------------------------+
| dayname("1962-03-06")+0 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-07")+0;
+-------------------------+
| dayname("1962-03-07")+0 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-08")+0;
+-------------------------+
| dayname("1962-03-08")+0 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-01")+1;
+-------------------------+
| dayname("1962-03-01")+1 |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-01")+2;
+-------------------------+
| dayname("1962-03-01")+2 |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-01")+3;
+-------------------------+
| dayname("1962-03-01")+3 |
+-------------------------+
|                       3 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-01")+4;
+-------------------------+
| dayname("1962-03-01")+4 |
+-------------------------+
|                       4 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-01")+5;
+-------------------------+
| dayname("1962-03-01")+5 |
+-------------------------+
|                       5 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-01")+6;
+-------------------------+
| dayname("1962-03-01")+6 |
+-------------------------+
|                       6 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-01")+7;
+-------------------------+
| dayname("1962-03-01")+7 |
+-------------------------+
|                       7 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-01")+2333;
+----------------------------+
| dayname("1962-03-01")+2333 |
+----------------------------+
|                       2333 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayname("1962-03-01")+2.333;
+-----------------------------+
| dayname("1962-03-01")+2.333 |
+-----------------------------+
|                       2.333 |
+-----------------------------+
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-212-g0681b8125
Git Commit Hash: 0681b8125080045ee0d4747ce61923164c6f2dd1
Git Branch: master
UTC Build Time: 2019-03-14 04:39:19
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]

After rewriting expression dayname("1962-03-01")+0, it will generate a plus node having two children function dayname and constant 0.
Because the retType of dayname is string but it is int for constant 0, the plus node will wrap a cast function above dayname to convert its retType to real.
And in TiDB the result of casting nonnumerical stings like "Thursday" to real is 0.
But In MySQL, results of dayname can be regarded as number when doing numerical calculation, "Monday" is regarded as 0, "Tuesday" is 1 and so on.
Now, each builtin functions in TiDB only has one retType and it is string for dayname, so this issue is not easy to fix now.