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, 6 insertions, 2 deletions

diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index d1da36dfa5b..9c75c08c62a 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -4363,8 +4363,12 @@ select_option:
           }
 	| SQL_CACHE_SYM
 	  {
-            /* Honor this flag only if SQL_NO_CACHE wasn't specified. */
-            if (Lex->select_lex.sql_cache != SELECT_LEX::SQL_NO_CACHE)
+            /*
+             Honor this flag only if SQL_NO_CACHE wasn't specified AND
+             we are parsing the outermost SELECT in the query.
+            */
+            if (Lex->select_lex.sql_cache != SELECT_LEX::SQL_NO_CACHE &&
+                Lex->current_select == &Lex->select_lex)
             {
               Lex->safe_to_cache_query=1;
 	      Lex->select_lex.options|= OPTION_TO_QUERY_CACHE;