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: lou shuai (OCA)
• Status: Won't fix
• Category: MySQL Server: Query Cache
• Severity: S2 (Serious)
• Version: 5.7.1, 5.6.*
• OS: Linux (Ubuntu 12.10)
• CPU Architecture: Any
• Tags: partition table, query cache

[14 Aug 2013 11:51] lou shuai

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] lou shuai

patch for this bug

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

[6 Jun 2014 13:57] MySQL Verification Team

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/