Bug #67548 SLEEP() accepts smaller time argument than microseconds - contradicts docs
Submitted: 10 Nov 2012 13:06 Modified: 22 Feb 2013 3:26
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.1.63, 5.5.28, 5.6.8 OS:Any
Assigned to:
Triage: Needs Triage: D4 (Minor)

[10 Nov 2012 13:06] Peter Laursen
Description:
http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_sleep

"Sleeps (pauses) for the number of seconds given by the duration argument, then returns 0. If SLEEP() is interrupted, it returns 1. The duration may have a fractional part given in microseconds."

However smaller fractional parts of a second than a microsecond are accepted.

How to repeat:
SELECT SLEEP(0.000000000000000000000000000001);
-- executes with no error or warning

Suggested fix:
If the argument is truncated (what I believe it must be in such case - if not I have the fastest computer on Earth!) either an error or a warning should occur, I think. And behaviour should be documented.
[10 Nov 2012 13:07] Peter Laursen
I forgot: Also "SELECT SLEEP(-99);" executes with no error or warning.  

This simply makes no sense! I assume it truncates to "SELECT SLEEP(0);"
[10 Nov 2012 13:11] Peter Laursen
Not sure what category to choose for this, really! Tried my best!
[10 Nov 2012 13:59] Sveta Smirnova
Thank you for the report.

Verified as described. Since this report about lack of the warning mostly I think category is correct.
[10 Nov 2012 16:34] Peter Laursen
A few more 'oddities':

1) You may actually use FLOAT notation:

SELECT 25E-1; -- "2.5"
SELECT SLEEP(25E-1); -- Sleeps for 2.5 secs

-- but now see the behavior with "25E+13". This number is withing the MAXINT range
SELECT 25E+13; -- "250000000000000"
.. but
SELECT SLEEP(25E+13) -- returns "0" immediately. 
-- "25E+13" is truncated to "0" it seems. 

So what is the max argument supplied to SLEEP() that does not truncate? "25E+9" truncates but "25E+8" does not on my environment.

2)
Also those statements execute with the argument truncated to "0" without a warning/error:

SELECT SLEEP(NULL);
SELECT SLEEP('a');
[11 Nov 2012 10:22] Peter Laursen
At least the behavior with SLEEP(NULL) differs from other functions like

SELECT SIN(NULL);
-- returns NULL

It could also be an option to return NULL in all cases where no SLEEP occurs do to argument being "0" or "NULL" (explicitly set or implicitly due to truncation).
[22 Feb 2013 3:26] Paul Dubois
Noted in 5.7.1 changelog.

SLEEP() produced no warning or error for NULL or negative arguments. 
Now it produces a warning, or an error in strict SQL mode. 

Will also change the SLEEP() description to say that it permits a
fractional part in the argument, without being specific about
"microseconds."