Bug #70032 Query cache works for partition table if there is a derived table
Submitted: 14 Aug 2013 11:51 Modified: 7 Jul 2017 9:08
Reporter: Mr. Left Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S2 (Serious)
Version:5.7.1, 5.6.* OS:Linux (Ubuntu 12.10)
Assigned to: CPU Architecture:Any
Tags: partition table, query cache

[14 Aug 2013 11:51] Mr. Left
Description:
As in the refman said:
http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html
"Query cache not supported.  The query cache is not supported for partitioned tables. Beginning with MySQL 5.6.5, the query cache is automatically disabled for queries involving partitioned tables, and cannot be enabled for such queries. (Bug #53775)"

But when there is a partition table which is used in a derived table, the query cache work for the query.

Such as: SELECT COUNT(*) FROM (select * from partition_table where ....) tb;

How to repeat:
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (c1)
(PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (2) ENGINE = InnoDB) */

INSERT INTO t1 VALUES(1), (2);

select count(*) from (select * from t1 where c1 = 1) tb2;
SHOW STATUS LIKE 'Qcache_hits';
select count(*) from (select * from t1 where c1 = 1) tb2;
SHOW STATUS LIKE 'Qcache_hits';

Suggested fix:
I do not know if it is a bug, but also i tried to fix it.

=== modified file 'sql/sql_cache.cc'
--- sql/sql_cache.cc	2013-02-28 15:36:00 +0000
+++ sql/sql_cache.cc	2013-08-14 11:41:27 +0000
@@ -3800,7 +3800,7 @@
       */
       DBUG_ASSERT(table->s->db_type() == heap_hton ||
                   table->s->db_type() == myisam_hton);
-      DBUG_RETURN(0);
+      continue;
     }
 
     /*
[14 Aug 2013 11:52] Mr. Left
patch for this bug

Attachment: patch.diff (text/x-patch), 332 bytes.

[6 Jun 2014 13:57] Miguel Solorzano
Thank you for the bug report.
[7 Jul 2017 9:08] Erlend Dahl
MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/