MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:damien Date:June 18 2007 9:16pm
Subject:bk commit into 5.0 tree (dkatz:1.2535) BUG#29053
View as plain text  
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-18 17:16: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 the documented caching behaviour and test cases for the reported bug. 

  mysql-test/r/query_cache.result@stripped, 2007-06-18 17:16:16-04:00, dkatz@stripped +52 -1
    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@stripped, 2007-06-18 17:16:16-04:00, dkatz@stripped +34 -0
    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@stripped, 2007-06-18 17:16:16-04:00, dkatz@stripped +6 -2
    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.

# 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-18 17:16:16 -04:00
@@ -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;

--- 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-18 17:16:16 -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-18 17:16:16 -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#29053damien18 Jun