Bug #29318 Statements prepared with PREPARE and with one parameter don't use query cache
Submitted: 23 Jun 2007 17:09 Modified: 12 Jul 2007 17:34
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.0 and up OS:Linux
Assigned to: Guilhem Bichot CPU Architecture:Any

[23 Jun 2007 17:09] Guilhem Bichot
Description:
I fixed BUG#735 but this case:
prepare stmt1 from "select * from t1 where c1=?";
set @a=1;
execute stmt1 using @a;
was deliberately excluded from the scope of the fix, because parameter substitution (to form a query which can be compared to the query cache) inserted names of user variables into the query, and user variables don't work with the query cache.
This special case of prepared statements not using the query cache is a known documented limitation:
http://dev.mysql.com/doc/refman/5.1/en/query-cache-how.html
Now, after BUG#26842's fix, substitution inserts the value of parameters, not user variables, so we can lift the limitation.

How to repeat:
prepare stmt1 from "select * from t1 where c1=?";
set @a=1;

do
execute stmt1 using @a;
multiple times and observe that SHOW STATUS LIKE "Qcache_hits"
does not vary.

Suggested fix:
I have a patch
[23 Jun 2007 17:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29451

ChangeSet@1.2527, 2007-06-23 19:16:51+02:00, guilhem@gbichot3.local +4 -0
  Fix for BUG#29318 "Statements prepared with PREPARE and with one
  parameter don't use query cache"
  Thanks to the fix of BUG#26842, statements prepared with SQL PREPARE
  and having parameters can now use the query cache.
[25 Jun 2007 13:55] Guilhem Bichot
queued in 5.1-runtime
[2 Jul 2007 15:05] Bugs System
Pushed into 5.1.21-beta
[12 Jul 2007 11:17] Konstantin Osipov
Paul,
1) no, it applies to all SQL prepared statements with arbitrary number of parameters
2) The limitation was not present in binary protocol prepared statements.
[12 Jul 2007 17:34] Paul DuBois
Noted in 5.1.21 changelog.

Previously, prepared statements processed using PREPARE and EXECUTE
were not subject to caching in the query cache if they contained any
? parameter markers. This limitation has been lifted.

Also updated the query cache section.