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, 52 insertions, 1 deletions

diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 53a0977a16a..b0f3fb77c0e 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -179,12 +179,22 @@ a
 1
 2
 3
+select * from t1 where a IN (select sql_cache a from t1);
+a
+1
+2
+3
+select * from t1 where a IN (select a from t1 union select sql_cache a from t1);
+a
+1
+2
+3
 show status like "Qcache_hits";
 Variable_name	Value
 Qcache_hits	4
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	2
+Qcache_queries_in_cache	1
 set query_cache_type=on;
 reset query cache;
 show status like "Qcache_queries_in_cache";
@@ -195,6 +205,41 @@ a
 1
 2
 3
+select * from t1 union select sql_no_cache * from t1;
+a
+1
+2
+3
+select * from t1 where a IN (select sql_no_cache a from t1);
+a
+1
+2
+3
+select * from t1 where a IN (select a from t1 union select sql_no_cache a from t1);
+a
+1
+2
+3
+select sql_cache sql_no_cache * from t1;
+a
+1
+2
+3
+select sql_cache  * from t1 union select sql_no_cache * from t1;
+a
+1
+2
+3
+select sql_cache * from t1 where a IN (select sql_no_cache a from t1);
+a
+1
+2
+3
+select sql_cache * from t1 where a IN (select a from t1 union select sql_no_cache a from t1);
+a
+1
+2
+3
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
 Qcache_queries_in_cache	0
@@ -1416,3 +1461,9 @@ insert into t1 values ('c');
 a
 drop table t1;
 set GLOBAL query_cache_size= default;
+set GLOBAL query_cache_size=1000000;
+create table t1 (a char);
+insert into t1 values ('c');
+a
+drop table t1;
+set GLOBAL query_cache_size= default;