diff options
| -rw-r--r-- | mysql-test/r/query_cache.result | 53 | ||||
| -rw-r--r-- | mysql-test/t/query_cache.test | 34 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 8 |
3 files changed, 92 insertions, 3 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; 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 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; |