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.

0 files changed, 0 insertions, 0 deletions