[compatibility] SYSDATE() is different from mysql

[tshqin]

1. What did you do?
   If possible, provide a recipe for reproducing the error.
   SELECT SYSDATE(), SLEEP(1), SYSDATE(), SLEEP(2), SYSDATE();

2. What did you expect to see?

mysql> SELECT SYSDATE(), SLEEP(1), SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(1) | SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+----------+---------------------+
| 2017-08-18 22:33:51 |        0 | 2017-08-18 22:33:52 |        0 | 2017-08-18 22:33:54 |
+---------------------+----------+---------------------+----------+---------------------+
1 row in set (3.01 sec)

3. What did you see instead?

in TiDB SYSDATE() behaves the same as now(), it returns statement begin time but not execute time

MySQL [(none)]> SELECT SYSDATE(), SLEEP(1), SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(1) | SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+----------+---------------------+
| 2017-08-18 22:33:44 |        0 | 2017-08-18 22:33:44 |        0 | 2017-08-18 22:33:44 |
+---------------------+----------+---------------------+----------+---------------------+
1 row in set (3.00 sec)

4. What version of TiDB are you using (tidb-server -V)?
   Git Commit Hash: 365e4ab
   Git Commit Branch: master
   UTC Build Time: 2017-08-16 01:24:27

[shenli]

@tshqin Thanks for your feedback!
@XuHuaiyu @zz-jason PTAL

[zz-jason]

functon SYS_DATE() is being refactored in this pr: #4211

[zz-jason]

The reason to cause this error is the constant folding. Function SYSDATE() is folded during the plan building stage, which should not be, you can check this by running:

desc SELECT SYSDATE(), SLEEP(1), SYSDATE(), SLEEP(2), SYSDATE();

To fix this, we should set deterministic with false of every function which should not be folded.

[zz-jason]

to #4080

[zz-jason]

TiDB > desc SELECT SYSDATE(), SLEEP(1), SYSDATE(), SLEEP(2), SYSDATE();
+--------------+--------------+-------------+------+-----------------------------------------------------------------+-------+
| id           | parents      | children    | task | operator info                                                   | count |
+--------------+--------------+-------------+------+-----------------------------------------------------------------+-------+
| TableDual_1  | Projection_2 |             | root | rows:1                                                          |     1 |
| Projection_2 |              | TableDual_1 | root | sysdate(), sleep(cast(1)), sysdate(), sleep(cast(2)), sysdate() |     1 |
+--------------+--------------+-------------+------+-----------------------------------------------------------------+-------+
2 rows in set (0.00 sec)

TiDB > SELECT SYSDATE(), SLEEP(1), SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(1) | SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+----------+---------------------+
| 2017-08-24 10:49:13 |        0 | 2017-08-24 10:49:14 |        0 | 2017-08-24 10:49:16 |
+---------------------+----------+---------------------+----------+---------------------+
1 row in set (3.00 sec)

TiDB > select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: 0.8.0
Git Commit Hash: 986fb645f3654801afb4da7e1fc72a6aff5fa2e3
Git Branch: master
UTC Build Time: 2017-08-24 02:48:28 |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

fixed.

[tshqin]

the issue reappears in v2.0:

MySQL [(none)]> select sysdate(), sleep(1),sysdate(),sleep(2),sysdate();
+---------------------+----------+---------------------+----------+---------------------+
| sysdate()           | sleep(1) | sysdate()           | sleep(2) | sysdate()           |
+---------------------+----------+---------------------+----------+---------------------+
| 2018-09-28 10:09:50 |        0 | 2018-09-28 10:09:50 |        0 | 2018-09-28 10:09:50 |
+---------------------+----------+---------------------+----------+---------------------+
1 row in set (3.00 sec)

MySQL [(none)]> desc select sysdate(), sleep(1),sysdate(),sleep(2),sysdate();
+--------------+--------------+-------------+------+-----------------------------------------------------------------------------------+-------+
| id           | parents      | children    | task | operator info                                                                     | count |
+--------------+--------------+-------------+------+-----------------------------------------------------------------------------------+-------+
| TableDual_4  | Projection_3 |             | root | rows:1                                                                            | 1.00  |
| Projection_3 |              | TableDual_4 | root | 2018-09-28 10:14:18, sleep(1), 2018-09-28 10:14:18, sleep(2), 2018-09-28 10:14:18 | 1.00  |
+--------------+--------------+-------------+------+-----------------------------------------------------------------------------------+-------+
2 rows in set (0.00 sec)

MySQL [(none)]> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                          |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v2.0.6
Git Commit Hash: b13bc08462a584a085f377625a7bab0cc0351570
Git Branch: release-2.0
UTC Build Time: 2018-08-06 10:57:46
GoVersion: go version go1.10.2 linux/amd64
TiKV Min Version: 2.0.0-rc.4.1 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[alivxxx]

Looks like it is introduced in #4756 . Maybe we should also add the desc select sysdate(), sleep(1),sysdate(),sleep(2),sysdate(); in the test when fixing this issue, so we can know it is unfoldable in the later.
PTAL @zz-jason @XuHuaiyu @shenli