MySQL Bugs: #29318: Statements prepared with PREPARE and with one parameter don't use query cache

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

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

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.

queued in 5.1-runtime

Pushed into 5.1.21-beta

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.

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.