bk commit into 4.1 tree (thek:1.2637) BUG#27792

[kpettersson]

Below is the list of changes that have just been committed into a local
4.1 repository of thek. When thek 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-05-04 13:47:01+02:00, thek@adventure.(none) +5 -0
  Bug #27792 query cache returns wrong result, with certain system variables
   - Queries in the query cache are identified by the individual 
     characters in the query statement, the current database and 
     the current environment expressed as a set of system variable
     flags.
   - Since the set of environment flags didn't properly describe the
     current environment unexpected results were returned from the 
     query cache.
   - Query cache is now cleared when the variable ft_boolean_syntax is 
     updated.
   - An identification flag for the variable default_week_format is 
     added to the query cache record.
   
  Thanks to Martin Friebe who has supplied significant parts of this patch.

  mysql-test/r/query_cache.result@stripped, 2007-05-04 13:47:00+02:00, thek@adventure.(none)
+52 -0
    Added test case

  mysql-test/t/query_cache.test@stripped, 2007-05-04 13:47:00+02:00, thek@adventure.(none) +45
-0
    Added test case

  sql/mysql_priv.h@stripped, 2007-05-04 13:47:00+02:00, thek@adventure.(none) +1 -0
    - Added missing flags to reflect the significant local environment
      settings of the cached query.

  sql/set_var.cc@stripped, 2007-05-04 13:47:00+02:00, thek@adventure.(none) +6 -2
    - Added query cache flush when system variable ft_boolean_syntax is
      updated since this also invalidates all cached result sets using this
      variable.

  sql/sql_cache.cc@stripped, 2007-05-04 13:47:00+02:00, thek@adventure.(none) +2 -0
    - Added another local system variable as an identification flag
      for cached queries.

# 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:	thek
# Host:	adventure.(none)
# Root:	/home/thek/Development/cpp/bug27792/my41-bug27792

--- 1.388/sql/mysql_priv.h	2007-01-31 11:47:04 +01:00
+++ 1.389/sql/mysql_priv.h	2007-05-04 13:47:00 +02:00
@@ -426,6 +426,7 @@ struct Query_cache_query_flags
   ulong sql_mode;
   ulong max_sort_length;
   ulong group_concat_max_len;
+  ulong default_week_format;
   MY_LOCALE *lc_time_names;
 };
 #define QUERY_CACHE_FLAGS_SIZE sizeof(Query_cache_query_flags)

--- 1.97/sql/sql_cache.cc	2006-07-04 14:40:16 +02:00
+++ 1.98/sql/sql_cache.cc	2007-05-04 13:47:00 +02:00
@@ -813,6 +813,7 @@ void Query_cache::store_query(THD *thd, 
     flags.sql_mode= thd->variables.sql_mode;
     flags.max_sort_length= thd->variables.max_sort_length;
     flags.group_concat_max_len= thd->variables.group_concat_max_len;
+    flags.default_week_format= thd->variables.default_week_format;
     flags.lc_time_names= thd->variables.lc_time_names; 
     STRUCT_LOCK(&structure_guard_mutex);
 
@@ -1016,6 +1017,7 @@ Query_cache::send_result_to_client(THD *
   flags.sql_mode= thd->variables.sql_mode;
   flags.max_sort_length= thd->variables.max_sort_length;
   flags.group_concat_max_len= thd->variables.group_concat_max_len;
+  flags.default_week_format= thd->variables.default_week_format;
   flags.lc_time_names= thd->variables.lc_time_names; 
   memcpy((void *)(sql + (tot_length - QUERY_CACHE_FLAGS_SIZE)),
 	 &flags, QUERY_CACHE_FLAGS_SIZE);

--- 1.62/mysql-test/r/query_cache.result	2006-09-12 17:05:18 +02:00
+++ 1.63/mysql-test/r/query_cache.result	2007-05-04 13:47:00 +02:00
@@ -1008,3 +1008,55 @@ Variable_name	Value
 Qcache_hits	1
 drop table t1;
 set GLOBAL query_cache_size=0;
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+set GLOBAL query_cache_type=1;
+set GLOBAL query_cache_limit=10000;
+set GLOBAL query_cache_min_res_unit=0;
+set GLOBAL query_cache_size= 100000;
+reset query cache;
+set LOCAL default_week_format = 0;
+select week('2007-01-04');
+week('2007-01-04')
+0
+select week('2007-01-04') from t1;
+week('2007-01-04')
+0
+0
+0
+set LOCAL default_week_format = 2;
+select week('2007-01-04');
+week('2007-01-04')
+53
+select week('2007-01-04') from t1;
+week('2007-01-04')
+53
+53
+53
+drop table t1;
+CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
+INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
+('Full-text indexes', 'are called collections'),
+('Only MyISAM tables','support collections'),
+('Function MATCH ... AGAINST()','is used to do a search'),
+('Full-text search in MySQL', 'implements vector space model');
+set GLOBAL ft_boolean_syntax='+ -><()~*:""&|';
+select *, MATCH(a,b) AGAINST("+called +collections" IN BOOLEAN MODE) as x from t1;
+a	b	x
+MySQL has now support	for full-text search	0
+Full-text indexes	are called collections	1
+Only MyISAM tables	support collections	0
+Function MATCH ... AGAINST()	is used to do a search	0
+Full-text search in MySQL	implements vector space model	0
+set GLOBAL ft_boolean_syntax='- +><()~*:""&|';
+select *, MATCH(a,b) AGAINST("+called +collections" IN BOOLEAN MODE) as x from t1;
+a	b	x
+MySQL has now support	for full-text search	0
+Full-text indexes	are called collections	0
+Only MyISAM tables	support collections	0
+Function MATCH ... AGAINST()	is used to do a search	0
+Full-text search in MySQL	implements vector space model	0
+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;

--- 1.48/mysql-test/t/query_cache.test	2005-12-01 13:26:16 +01:00
+++ 1.49/mysql-test/t/query_cache.test	2007-05-04 13:47:00 +02:00
@@ -729,4 +729,49 @@ drop table t1;
 
 set GLOBAL query_cache_size=0;
 
+#
+# Bug #27792 query cache returns wrong result, with certain system variables
+#
+
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+set GLOBAL query_cache_type=1;
+set GLOBAL query_cache_limit=10000;
+set GLOBAL query_cache_min_res_unit=0;
+set GLOBAL query_cache_size= 100000;
+
+# default_week_format
+reset query cache;
+set LOCAL default_week_format = 0;
+select week('2007-01-04');
+select week('2007-01-04') from t1;
+
+set LOCAL default_week_format = 2;
+select week('2007-01-04');
+select week('2007-01-04') from t1;
+
+drop table t1;
+
+CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
+INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
+                       ('Full-text indexes', 'are called collections'),
+                          ('Only MyISAM tables','support collections'),
+             ('Function MATCH ... AGAINST()','is used to do a search'),
+        ('Full-text search in MySQL', 'implements vector space model');
+
+
+set GLOBAL ft_boolean_syntax='+ -><()~*:""&|';
+
+select *, MATCH(a,b) AGAINST("+called +collections" IN BOOLEAN MODE) as x from t1;
+
+# swap +/-
+set GLOBAL ft_boolean_syntax='- +><()~*:""&|';
+
+select *, MATCH(a,b) AGAINST("+called +collections" IN BOOLEAN MODE) as x from 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;
+
+
 # End of 4.1 tests

--- 1.191/sql/set_var.cc	2007-01-29 16:19:57 +01:00
+++ 1.192/sql/set_var.cc	2007-05-04 13:47:00 +02:00
@@ -490,7 +490,7 @@ static sys_var_rand_seed1	sys_rand_seed1
 static sys_var_rand_seed2	sys_rand_seed2("rand_seed2");
 
 static sys_var_thd_ulong        sys_default_week_format("default_week_format",
-					                &SV::default_week_format);
+                                                     &SV::default_week_format);
 
 sys_var_thd_ulong               sys_group_concat_max_len("group_concat_max_len",
                                                          &SV::group_concat_max_len);
@@ -992,7 +992,6 @@ bool update_sys_var_str(sys_var_str *var
   return 0;
 }
 
-
 static bool sys_update_init_connect(THD *thd, set_var *var)
 {
   return update_sys_var_str(&sys_init_connect, &LOCK_sys_init_connect, var);
@@ -1032,6 +1031,11 @@ static bool sys_update_ftb_syntax(THD *t
 {
   strmake(ft_boolean_syntax, var->value->str_value.c_ptr(),
 	  sizeof(ft_boolean_syntax)-1);
+
+  #ifdef HAVE_QUERY_CACHE
+  query_cache.flush();
+  #endif /* HAVE_QUERY_CACHE */
+
   return 0;
 }