Below is the list of changes that have just been committed into a local
5.0 repository of dkatz. When dkatz does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-06-15 16:45:20-04:00, dkatz@stripped +3 -0
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 this behaviour.
mysql-test/r/query_cache.result@stripped, 2007-06-15 16:45:15-04:00, dkatz@stripped +52 -1
Test that SQL_CACHE from only the first SELECT is used to turn on caching and that any SQL_NO_CACHE turns off caching.
mysql-test/t/query_cache.test@stripped, 2007-06-15 16:45:15-04:00, dkatz@stripped +34 -0
Test that SQL_CACHE from only the first SELECT is used to turn on caching and that any SQL_NO_CACHE turns off caching.
sql/sql_yacc.yy@stripped, 2007-06-15 16:45:16-04:00, dkatz@stripped +2 -1
Added an explicit check to make sure "SELECT SQL_CACHE" only works on the first select in a query.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: dkatz
# Host: damien-katzs-computer.local
# Root: /Users/dkatz/50_win
--- 1.522/sql/sql_yacc.yy 2007-06-05 17:04:34 -04:00
+++ 1.523/sql/sql_yacc.yy 2007-06-15 16:45:16 -04:00
@@ -4364,7 +4364,8 @@ 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)
+ 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;
--- 1.81/mysql-test/r/query_cache.result 2007-06-13 14:23:22 -04:00
+++ 1.82/mysql-test/r/query_cache.result 2007-06-15 16:45:15 -04:00
@@ -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
@@ -1409,6 +1454,12 @@ drop table t1;
set GLOBAL query_cache_type=default;
set GLOBAL query_cache_limit=default;
set GLOBAL query_cache_min_res_unit=default;
+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;
set GLOBAL query_cache_size=1000000;
create table t1 (a char);
--- 1.60/mysql-test/t/query_cache.test 2007-06-13 14:23:22 -04:00
+++ 1.61/mysql-test/t/query_cache.test 2007-06-15 16:45:15 -04:00
@@ -89,7 +89,11 @@ show status like "Qcache_queries_in_cach
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_cach
# 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;
#
@@ -986,6 +999,27 @@ let $q1= `select UUID(), a from t1`;
let $q2= `select UUID(), a from t1`;
# disabling the logging of the query because the UUIDs 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;
+
+#
+# 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
| Thread |
|---|
| • bk commit into 5.0 tree (dkatz:1.2535) BUG#29053 | damien | 15 Jun |
