Bug #86047 FROM sub query with 'group by' is cached by mistake

• Submitted: 23 Apr 2017 7:58
• Modified: 2 May 2017 17:23
• Reporter: Meiji Kimura
• Status: Closed
• Category: MySQL Server: Query Cache
• Severity: S2 (Serious)
• Version: 5.6,5.7,8.0, 5.6.36
• OS: Any
• CPU Architecture: Any
• Tags: query cache, regression

[23 Apr 2017 7:58] Meiji Kimura

Description:
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 MySQL 5.6 or later version returns 'empty' based on wrong cache from 'select * from (select i2,sum(i1) from t1 group by i2) b;'.

(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 i2,sum(i1) from t1 group by i2) b; -- wrong cached in both 5.6/5.7/8.0

(3) [Transaction A]
commit;

(4) [Transaction B]
select * from t1;
select * from (select * from t1) a; -- Empty set returns based on wrong cache
select * from (select i2,sum(i1) from t1 group by i2) b; -- Empty set returns based on wrong cache in 5.6/5.7/8.0

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 i2,sum(i1) from t1 group by i2) b; -- wrong cached in both 5.6/5.7/8.0
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 i2,sum(i1) from t1 group by i2) b; -- wrong cached in both 5.6/5.7/8.0
show global status like 'qcache%cache%';

2.Results
[Right behavior on 5.5]
(2) 
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;
Empty set (0.00 sec)

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

mysql [localhost] {msandbox} (test) > select * from (select i2,sum(i1) from t1 group by i2) b; -- wrong cached in both 5.6/5.7/8.0
Empty set (0.00 sec)

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

mysql [localhost] {msandbox} (test) > select * from (select i2,sum(i1) from t1 group by i2) b; -- wrong cached in both 5.6/5.7/8.0
+------+---------+
| i2   | sum(i1) |
+------+---------+
|    1 |       3 |
|    2 |       7 |
+------+---------+
2 rows in set (0.00 sec)

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

[Wrong behavior on 5.6,5.7,8.0]
(2)
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.01 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       | 5     |
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from (select i2,sum(i1) from t1 group by i2) b; -- wrong cached in both 5.6/5.7/8.0
Empty set (0.00 sec)

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

mysql [localhost] {msandbox} (test) > select * from (select i2,sum(i1) from t1 group by i2) b; -- wrong cached in both 5.6/5.7/8.0
Empty set (0.00 sec)

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

I checked these on 5.5.55, 5.6.36, 5.7.18, 8.0.1

Suggested fix:
Don't cache 'select * from (select i2,sum(i1) from t1 group by i2) b;' into query cache in this situation.

[23 Apr 2017 8:04] MySQL Verification Team

Only MySQL 5.6 has similar bug (FROM subquery is cached by mistake).
See Bug#86046

[24 Apr 2017 6:41] MySQL Verification Team

Hello Meiji-San,

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

Thanks,
Umesh

[2 May 2017 17:23] Paul DuBois

Posted by developer:
 
Noted in 5.6.37, 5.7.19, 8.0.2 changelogs.

Queries could be cached incorrectly, leading to incorrect query
results, under these circumstances: InnoDB table; rows are being
inserted but have not yet been committed; a query uses the table as a
base table in a derived table; the optimizer chooses to materialize
the derived table.