Bug #86046 FROM sub query is cached by mistake
Submitted: 23 Apr 7:18 Modified: 7 Jul 9:09
Reporter: Meiji Kimura Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S2 (Serious)
Version:5.6, 5.6.36 OS:Any
Assigned to:
Tags: query cache

[23 Apr 7:18] Meiji Kimura
Description:
In the environment query cache is enable, autocommit is enable, (2) both queries returns 'empty', and (4) both queries should return result sets. But only MySQL 5.6 returns 'empty' based on wrong cache from 'select * from (select * from t1) a;

(1) [Transaction A]
drop table if exists t1;
create table t1(i1 int, i2 int, v3 varchar(20));

start transaction;
insert into t1 values(1,1,'a'),(2,1,'b'),(3,2,'c'),(4,2,'d');

(2) [Transaction B]
select * from t1;
select * from (select * from t1) a; -- wrong cached in 5.6 only

(3) [Transaction A]
commit;

(4) [Transaction B]
select * from t1;
select * from (select * from t1) a; -- Empty set returns based on wrong cache

How to repeat:
1.Procedures
 Please use mysql sandbox, and add these lines vim my.sandbox.cnf
autocommit=1
query_cache_size=16M
query_cache_type=1

then restart with ./restart.

-- [Transaction A]
drop table if exists t1;
create table t1(i1 int, i2 int, v3 varchar(20));

start transaction;
insert into t1 values(1,1,'a'),(2,1,'b'),(3,2,'c'),(4,2,'d');

-- [Transaction B]
show global status like 'qcache%cache%';
select * from t1;
show global status like 'qcache%cache%';
select * from (select * from t1) a; -- wrong cached in 5.6 only
show global status like 'qcache%cache%';

-- [Transaction A]
commit;

-- [Transaction B]
show global status like 'qcache%cache%';
select * from t1;
show global status like 'qcache%cache%';
select * from (select * from t1) a; -- wrong cached in 5.6 only
show global status like 'qcache%cache%';

2.Results
[Right behavior on 5.5, 5.7, 8.0]
(2) 
mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 2     |
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t1;
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 3     |
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from (select * from t1) a; -- wrong cached in 5.6 only
Empty set (0.01 sec)

mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 4     |
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
2 rows in set (0.00 sec)

(4) 
mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 4     |
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t1;
+------+------+------+
| i1   | i2   | v3   |
+------+------+------+
|    1 |    1 | a    |
|    2 |    1 | b    |
|    3 |    2 | c    |
|    4 |    2 | d    |
+------+------+------+
4 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 4     |
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from (select * from t1) a; -- wrong cached in 5.6 only
+------+------+------+
| i1   | i2   | v3   |
+------+------+------+
|    1 |    1 | a    |
|    2 |    1 | b    |
|    3 |    2 | c    |
|    4 |    2 | d    |
+------+------+------+
4 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 4     |
| Qcache_queries_in_cache | 2     |
+-------------------------+-------+
2 rows in set (0.00 sec)

[Wrong behavior on 5.6]
(2)
mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 13    |
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t1;
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 14    |
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from (select * from t1) a; -- wrong cached in 5.6 only
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 14    |
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
2 rows in set (0.00 sec)

(4)
mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 14    |
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t1;
+------+------+------+
| i1   | i2   | v3   |
+------+------+------+
|    1 |    1 | a    |
|    2 |    1 | b    |
|    3 |    2 | c    |
|    4 |    2 | d    |
+------+------+------+
4 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 14    |
| Qcache_queries_in_cache | 2     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from (select * from t1) a; -- wrong cached in 5.6 only
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 14    |
| Qcache_queries_in_cache | 2     |
+-------------------------+-------+
2 rows in set (0.00 sec)

Suggested fix:
Don't cache 'select * from (select * from t1) a;' into query cache in this situation.
[23 Apr 8:01] Meiji Kimura
I tested these on MySQL 5.5.55 and 5.6.36.

MySQL 5.7 or later has a similar bugs (more complicated sub query)
See Bug#86047
[24 Apr 6:13] Umesh Shastry
Hello Meiji-San,

Thank you for the report and test case.
Verified as described with 5.6.36 build.

Thanks,
Umesh
[7 Jul 9:09] Erlend Dahl
MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/
[3 Aug 2:53] Meiji Kimura
As a result of fix for Bug#86047, This problem was solved in MySQL 5.6.37.
[3 Aug 3:05] Meiji Kimura
[Right behavior on MySQL 5.6.37]

(2)
mysql [localhost] {msandbox} (test) > -- [Transaction B]
mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: test

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
2 rows in set (0.02 sec)

mysql [localhost] {msandbox} (test) > select * from t1;
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 1     |
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from (select * from t1) a; -- wrong cached in 5.6 only
Empty set (0.01 sec)

mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 2     |
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
2 rows in set (0.00 sec)

(4)
mysql [localhost] {msandbox} (test) > -- [Transaction B]
mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 2     |
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t1;
+------+------+------+
| i1   | i2   | v3   |
+------+------+------+
|    1 |    1 | a    |
|    2 |    1 | b    |
|    3 |    2 | c    |
|    4 |    2 | d    |
+------+------+------+
4 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 2     |
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from (select * from t1) a; -- wrong cached in 5.6 only
+------+------+------+
| i1   | i2   | v3   |
+------+------+------+
|    1 |    1 | a    |
|    2 |    1 | b    |
|    3 |    2 | c    |
|    4 |    2 | d    |
+------+------+------+
4 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_not_cached       | 2     |
| Qcache_queries_in_cache | 2     |
+-------------------------+-------+
2 rows in set (0.00 sec)