Bug #29053 SQL_CACHE in UNION causes non-deterministic functions to be cached

Changed code to enforce that SQL_CACHE only in the first SELECT is used to turn on caching(as documented), but any SQL_NO_CACHE will turn off caching (not documented, but a useful behaviour, especially for machine generated queries). Added test cases to explicitly test the documented caching behaviour and test cases for the reported bug. 


mysql-test/r/query_cache.result:
  Added non-bug specific tests that ensure that only SQL_CACHE in the first SELECT is respected when encountered by the parser. These tests validate what is already documented, that only the outer most SELECTS can use the SQL_CACHE option to turn on caching. Because it would break existing SQL applications, we do not return an error if the SQL_CACHE expression is found in nested SELECTs. Also added test to validate nested SELECT can contain SQL_NO_CACHE and it will always turn off caching for the whole query. 
  
  Also added a bug specific test case to validate that the buggy behavior as reported has been fixed.
mysql-test/t/query_cache.test:
  Added non-bug specific tests that ensure that only SQL_CACHE in the first SELECT is respected when encountered by the parser. These tests validate what is already documented, that only the outer most SELECTS can use the SQL_CACHE option to turn on caching. Because it would break existing SQL applications, we do not return an error if the SQL_CACHE expression is found in nested SELECTs. Also added test to validate nested SELECT can contain SQL_NO_CACHE and it will always turn off caching for the whole query. 
  
  Also added a bug specific test case to validate that the buggy behavior as reported has been fixed.
sql/sql_yacc.yy:
  Added an explicit check to make sure "SELECT SQL_CACHE" only works on the first select in a query.
  
  The parser will always hit the outermost SELECT first, and if the SQL_CACHE option is found it sets the safe_to_query flag in the lex. Then, if there are subseqent "uncachable" subqueries or functions, as it parses those elements it sets the safe_to_query to 0. However, this cause problems if nested SELECTs also used the SQL_CACHE option, because then it would set back safe_to_query to 1, even though there are uncacheable expressions previously parsed.
  
  By adding the check to ensure only the first SELECT can turn caching on, it means a subsequent SQL_CACHE option can't turn caching back on after a uncacheable subsequery was already encountered.

1 files changed, 34 insertions, 0 deletions

diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index 342ef5b6990..965ebf5df62 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -89,7 +89,11 @@ show status like "Qcache_queries_in_cache";
 select sql_cache * from t1 union select * from t1;
 set query_cache_type=2;
 select sql_cache * from t1 union select * from t1;
+
+# all sql_cache statements, except for the first select, are ignored.
 select * from t1 union select sql_cache * from t1;
+select * from t1 where a IN (select sql_cache a from t1);
+select * from t1 where a IN (select a from t1 union select sql_cache a from t1);
 show status like "Qcache_hits";
 show status like "Qcache_queries_in_cache";
 set query_cache_type=on;
@@ -102,6 +106,15 @@ show status like "Qcache_queries_in_cache";
 # SELECT SQL_NO_CACHE
 #
 select sql_no_cache * from t1;
+# sql_no_cache can occur in any nested select to turn on cacheing for the whole
+# expression and it will always override a sql_cache statement.
+select * from t1 union select sql_no_cache * from t1;
+select * from t1 where a IN (select sql_no_cache a from t1);
+select * from t1 where a IN (select a from t1 union select sql_no_cache a from t1);
+select sql_cache sql_no_cache * from t1;
+select sql_cache  * from t1 union select sql_no_cache * from t1;
+select sql_cache * from t1 where a IN (select sql_no_cache a from t1);
+select sql_cache * from t1 where a IN (select a from t1 union select sql_no_cache a from t1);
 show status like "Qcache_queries_in_cache";
 drop table t1;
 #
@@ -994,4 +1007,25 @@ drop table t1;
 
 set GLOBAL query_cache_size= default;
 
+#
+# Bug #29053 SQL_CACHE in UNION causes non-deterministic functions to be cached
+#
+
+set GLOBAL query_cache_size=1000000;
+
+create table t1 (a char);
+insert into t1 values ('c');
+
+let $q1= `select RAND() from t1 union select sql_cache 1 from t1;`;
+let $q2= `select RAND() from t1 union select sql_cache 1 from t1;`; 
+
+# disabling the logging of the query because the times are different each run.
+--disable_query_log
+eval select a  from t1 where "$q1" = "$q2";
+--enable_query_log
+
+drop table t1;
+
+set GLOBAL query_cache_size= default;
+
 # End of 5.0 tests