Fix cases where SLEEP() calls would get optimized away or cached. (Bug #12689)

mysql-test/r/func_misc.result:
  Add new results
mysql-test/t/func_misc.test:
  Add new regression test.
sql/item_create.cc:
  When SLEEP() is used, mark the statement as uncacheable.
sql/item_func.h:
  Flag SLEEP() as not constant, and set RAND_TABLE_BIT so that it is
  always evaluated.

4 files changed, 54 insertions, 0 deletions

diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index 670b8754e30..36666fc827d 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -70,3 +70,27 @@ select timediff(b, a) >= '00:00:03' from t1;
 timediff(b, a) >= '00:00:03'
 1
 drop table t1;
+set global query_cache_size=1355776;
+create table t1 (a int);
+insert into t1 values (1),(1),(1);
+create table t2 (a datetime default null, b datetime default null);
+insert into t2 set a = now();
+select a from t1 where sleep(1);
+a
+update t2 set b = now() where b is null;
+insert into t2 set a = now();
+select a from t1 where sleep(a);
+a
+update t2 set b = now() where b is null;
+insert into t2 set a = now();
+select a from t1 where sleep(1);
+a
+update t2 set b = now() where b is null;
+select timediff(b, a) >= '00:00:03' from t2;
+timediff(b, a) >= '00:00:03'
+1
+1
+1
+drop table t2;
+drop table t1;
+set global query_cache_size=default;
diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test
index f4cbacb93bb..4a618a56483 100644
--- a/mysql-test/t/func_misc.test
+++ b/mysql-test/t/func_misc.test
@@ -55,3 +55,26 @@ select sleep(3);
 update t1 set b = now();
 select timediff(b, a) >= '00:00:03' from t1;
 drop table t1;
+
+#
+# Bug #12689: SLEEP() gets incorrectly cached/optimized-away
+#
+set global query_cache_size=1355776;
+create table t1 (a int);
+insert into t1 values (1),(1),(1);
+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(a);
+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 timediff(b, a) >= '00:00:03' from t2;
+drop table t2;
+drop table t1;
+set global query_cache_size=default;
+
+# End of 5.0 tests
diff --git a/sql/item_create.cc b/sql/item_create.cc
index 8798bf889fc..77476e41d0b 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -356,6 +356,7 @@ Item *create_func_sha(Item* a)
 
 Item *create_func_sleep(Item* a)
 {
+  current_thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
   return new Item_func_sleep(a);
 }
 
diff --git a/sql/item_func.h b/sql/item_func.h
index 1f25b762b70..384cb486f7c 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -893,7 +893,13 @@ class Item_func_sleep :public Item_int_func
 {
 public:
   Item_func_sleep(Item *a) :Item_int_func(a) {}
+  bool const_item() const { return 0; }
   const char *func_name() const { return "sleep"; }
+  void update_used_tables()
+  {
+    Item_int_func::update_used_tables();
+    used_tables_cache|= RAND_TABLE_BIT;
+  }
   longlong val_int();
 };