| Bug #29053 | SQL_CACHE in UNION causes non-deterministic functions to be cached | ||
|---|---|---|---|
| Submitted: | 12 Jun 2007 18:35 | Modified: | 23 Jun 2007 10:05 |
| Reporter: | Philip Stoev | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.37/5.0BK/5.1BK | OS: | Any |
| Assigned to: | Bugs System | Target Version: | |
| Tags: | qc | ||
[12 Jun 2007 20:42]
Miguel Solorzano
Thank you for the bug report. Verified as described.
mysql> create table t1 (a char);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values ('a');
Query OK, 1 row affected (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.02 sec)
mysql> select now() from t1 union select sql_cache 1 from t1;
+---------------------+
| now() |
+---------------------+
| 2007-06-12 15:40:15 |
| 1 |
+---------------------+
2 rows in set (0.00 sec)
mysql> select now() from t1 union select sql_cache 1 from t1;
+---------------------+
| now() |
+---------------------+
| 2007-06-12 15:40:15 |
| 1 |
+---------------------+
2 rows in set (0.00 sec)
mysql> show status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 1 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select version();
+-------------------+
| version() |
+-------------------+
| 5.1.20-beta-debug |
+-------------------+
1 row in set (0.00 sec)
mysql>
[15 Jun 2007 22:45]
Bugs System
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/28907 ChangeSet@1.2535, 2007-06-15 16:45:20-04:00, dkatz@damien-katzs-computer.local +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.
[15 Jun 2007 23:01]
Philip Stoev
Thank you for patching this. In fact, Lex->safe_to_cache_query=1 is redundant because it is 1 by default and the code is setting is back to 1. Maybe it would be best to entirely remove this line -- unfortunately with all those caching variables, I can not be sure.
[18 Jun 2007 17:37]
Bugs System
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/29009 ChangeSet@1.2535, 2007-06-18 11:36:52-04:00, dkatz@damien-katzs-computer.local +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 cazching behaviour and test cases for the reported bug.
[18 Jun 2007 21:37]
Damien Katz
Philip, the previous submission comments hopefully explain a little more clearly of how the fix works.
[18 Jun 2007 23:16]
Bugs System
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/29037 ChangeSet@1.2535, 2007-06-18 17:16:20-04:00, dkatz@damien-katzs-computer.local +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.
[22 Jun 2007 20:07]
Bugs System
Pushed into 5.1.20-beta
[22 Jun 2007 20:09]
Bugs System
Pushed into 5.0.46
[23 Jun 2007 10:05]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of
that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available
version, including the bug fix. More information about accessing the source trees is
available at
http://dev.mysql.com/doc/en/installing-source.html
Documented bugfix in 5.0.46 and 5.1.20 changelogs.
Description: In a UNION, if (one of the) last queries has a SQL_CACHE option, the entire query is cached even if any previous statements within the UNION contain non-deterministic functions. In other words, this query: select now() from t1 union select sql_cache 1 from t1; will be cached and now() will return wrong values until the query is purged out of the cache. While it is unlikely that a human will put a sql_cache option right in the middle of a UNION, this can certainly happen if the SQL was pieced together automatically in some software. How to repeat: create table t1 (a char); insert into t1 values ('a'); flush status; mysql> select now() from t1 union select sql_cache 1 from t1; +---------------------+ | now() | +---------------------+ | 2007-05-31 03:21:28 | | 1 | +---------------------+ 2 rows in set (0.00 sec) mysql> select now() from t1 union select sql_cache 1 from t1; +---------------------+ | now() | +---------------------+ | 2007-05-31 03:21:28 | | 1 | +---------------------+ 2 rows in set (0.00 sec) mysql> show status like 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 1 | +---------------+-------+ 1 row in set (0.01 sec) NOW() returns the same timestamp for both queries and Qcache_hits indicates a cache hit. Suggested fix: in sql_yacc.yy "select_option:" will set Lex->safe_to_cache_query=1 which will override any previous parsing operations that may have set safe_to_cache_query to 0. While a quick fix is certainly possible -- never revert safe_to_cache_query to 1 if it was 0 before, it appears to me that the entire safe_to_cache_query mechanism is open to further issues like that one.