MySQL Bugs: #27792: query cache returns wrong result, with certain system variables

Bug #27792	query cache returns wrong result, with certain system variables
Submitted:	12 Apr 2007 20:50	Modified:	17 May 2007 14:09
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.40 4.1.23,5.1BK	OS:	Any
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any
Tags:	qc, query cache, system variables, wrong result

Description:
The query cache returns results, that have been calculated with different settings, and therefore shows incorrect results.

This doesn't apply to all system variables. max_group_concat_len is handled correctly.

but 
 default_week_format
 div_precision_increment  (5.x only)
 ft_boolean_syntax
are not remebered.

see how to repeat and testcase (testcase included in patch)

How to repeat:
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;

set LOCAL default_week_format = 0;
select week('2007-01-04') from t1;
set LOCAL default_week_format = 2;
select week('2007-01-04') from t1; # wrong
reset query cache;
select week('2007-01-04') from t1; # correct

set LOCAL div_precision_increment=2;
select 1/7 from t1;

set LOCAL div_precision_increment=4;
select 1/7 from t1; # wrong
reset query cache;
select 1/7 from t1; # correct

Suggested fix:
see patch

query_cache_notembedded.test expects default value for "query_cache_limit".
However this was modified in my addition to the query_cache.test.

It could be removed from my addition, as it has no influence on the result. Yet the notembedded test, should not depend on previous testcases.

I added the a "set" to the embedded test

patch + test

Attachment: qcache.patch (text/x-patch), 7.90 KiB.

Thank you for the bug report and suggestion patch. Verified as
described.

Update: Because of the growing size of the query cache record, the patch needs some more work. For example rather than saving ft_boolean_syntax, it might be better to clear the entire query cache when this system variable has changed.

Parts of this Bug may now depend on Bug #28234

If ft_query_expansion_limit should be changeable at runtime, it needs to be added here.

If ft_boolean_syntax should be changeable per session, then this may affect clearing the query cache.

----
There is a different way to reduce the size of the structure, but it requires far more work, if interest I can outline the details in a separate bug/feature request. (I will probably do anyway, as it goes along with a separate issue of storing some duplicates)

Queries only need to store and compare those environmental setting, that are affecting the query (max_group_concat_length is only relevant for queries that contain group_concat).
This can be done. I am aware that the query cache is checked before the query is parsed, and that at this time the server is unaware of the presence of this function.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26091

ChangeSet@1.2637, 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.

> <snip> if interest I can outline the details in a separate bug/feature
> request. (I will probably do anyway, as it goes along with a separate issue of
> storing some duplicates)

Please do this! Your ideas are most welcome!

done: see Bug #28241

Pushed into 4.1.23

Pushed into 5.0.42

Pushed into 5.1.19-beta

Noted in 4.1.23, 5.0.42, 5.1.19 changelogs.

Changes to some system variables should invalidate statements in the
query cache, but invalidation did not happen.