MySQL Bugs: #29197: Query cache and warnings

Bug #29197	Query cache and warnings
Submitted:	19 Jun 2007 5:23	Modified:	1 Apr 2008 11:26
Reporter:	Lachezar Balev	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S3 (Non-critical)
Version:	5.1.17, 5.0 BK, 4.1 BK	OS:	Any
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any

Description:
I think that there is one more case when a query does not get cached which is not described in the MySQL docs. The query is not cached when the server issues a warning.

How to repeat:
mysql> create table t1 (id int(20));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> show status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 18    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 3     |
+---------------+-------+
1 row in set (0.00 sec)

Now issue this query (it produces a warning) several times and explore the status variables:
mysql> select 'hi' as id from t1 group by id;
mysql> select 'hi' as id from t1 group by id;

mysql> show status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 18    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 5     |
+---------------+-------+
1 row in set (0.00 sec)

No cache hits are observed.

Suggested fix:
I do not think that this behaviour is incorrect. But I think it should be documented.

Thank you for the report.

Verified as described.

It looks like table is optimized out in this query (can be checked with help of EXPLAIN) and query cache do not caches queries without tables, so is does not look like bug.

Oleksandr, this is not exactly a bug, maybe it should be only documented.
The reason is not because the table is optimized. It can be easily checked by adding some more rows in the example given above.

Aster some more debugging it looks like I was wrong, the table is not optimized out, but closed after optimization. So QC do not see locked tables and thinks thatthere is no tables in the query. IMHO it is better to fix QC (check all query tables list instead of locked tables) then write tricky documentation.

During fixing I found yet another cause why the query do not go to the cache: the query produce warning and QC do not store queries with warnings.

I have no idea (maybe yet) how to make the query of such type without warning...