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
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• 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.