Description:
Related to Bug #22902
The SYSDATE() function has non-deterministic behavior after 5.0.13 unless the --sysdate-is-now startup option is used. SYSDATE() behaves exactly the same as NOW() within stored procedures with this option enabled.
In Bug #22902 it was suggested that there is nothing that can be done to make SYSDATE() deterministic in standalone queries and have it function in a non-deterministic manner within stored procedures.
* in oracle this is no way that I know of to use a non-deterministic SYSDATE in a normal query that will not use an index.
Also the status of the sysdate-is-now option is not exposed to the user.
How to repeat:
mysql> select sysdate(), sleep(5), sysdate() from dual;
+---------------------+----------+---------------------+
| sysdate() | sleep(5) | sysdate() |
+---------------------+----------+---------------------+
| 2007-09-11 18:44:23 | 0 | 2007-09-11 18:44:28 |
+---------------------+----------+---------------------+
1 row in set (5.01 sec)
Restarted with --sysdate-is-now
mysql> select sysdate(), sleep(5), sysdate() from dual;
+---------------------+----------+---------------------+
| sysdate() | sleep(5) | sysdate() |
+---------------------+----------+---------------------+
| 2007-09-11 18:45:35 | 0 | 2007-09-11 18:45:35 |
+---------------------+----------+---------------------+
1 row in set (5.01 sec)
Suggested fix:
The status of the sysdate-is-now option is not exposed to the user.
* sysdate-is-now should be exposed as a variable [ or SQL_MODE ] that can be set at runtime so you set it one way or the other on a connection basis. Either that or force the new sysdate behavior for stored procedures even if sysdate-is-now is set. Or just make sysdate-is-now default behavior for queries not in stored procedures.
