Bug #12689 SLEEP() can get incorrectly optimized away in WHERE and stored in query cache
Submitted: 19 Aug 2005 20:23 Modified: 26 Aug 2005 20:13
Reporter: Jim Winstead Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:
Assigned to: Jim Winstead CPU Architecture:Any

[19 Aug 2005 20:23] Jim Winstead
Description:
SELECT * FROM t1 WHERE SLEEP(1) will only result in a sleep of 1 second, regardless of the number of rows in t1.

such a query will also get cached by the query cache, but should not.

How to repeat:
set global query_cache_size=1355776;
create table t1 (a int);
insert into t1 values (1),(2),(3),(4),(5);
create table t2 (a datetime default null, b datetime default null);
insert into t2 set a = now();
select a from t1 where sleep(1);
update t2 set b = now() where b is null;
insert into t2 set a = now();
select a from t1 where sleep(1);
update t2 set b = now() where b is null;
select * from t2;
drop table t2;
drop table t1;
set global query_cache_size=default;
[19 Aug 2005 22:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28551
[22 Aug 2005 19:50] Jim Winstead
Fixed in 5.0.12.
[26 Aug 2005 20:13] Paul Dubois
Noted in 5.0.12 changelog.
In query cache section, added SLEEP() to list of
functions that cannot be cached.