WL#1569: Prepared Statements: implement support of Query Cache

Make query cache available for queries running through prepared statement  
protocol. IMHO it will be needed more as the new protocol becomes popular.  
  
I think it will be enough to store bound data with query text and other 
parameters which already are stored in the query cache.  

Note added by Trudy Pelzer, 2006-08-06
At the Dev-MT Offsite meeting in Santa Cruz, Brian
and Monty made the following time estimate for this
task:
- prepared statements (WL#1569 and others); 4+ mths
* This is 4 tasks:
** WL#1569 "Prepared Statements: implement support of Query Cache"
** WL#? include all SQL
** WL#? update server for max ps statements
** WL#? make ps able to recreate itself if it doesn't exist
* Brian: Prepared statements aren't useful until this 4-part
task is complete.
* Current estimate: Update for qcache: 1 mth. Update client library 
to recreate ps if it goes missing: 1 week. Update server to know 
about max number of statements it remembers: 2 weeks. Include all 
sql: 2 months (this includes needed discussion but not the extra
time QA needs to adjust mysqltest for the new feature).

Note added by Trudy Pelzer, 2007-05-25
Kostja and Guilhem agree that sufficient work has been done
(fixing BUG#735 and BUG#26842) that this WL#1569 can be considered
complete. The other 3 tasks that Monty and Brian determined should
be done to make prepared statements fully useful will still be 
deferred; see WL#1803 for a complete list of work that still needs
to be done to make prepared statements full-featured.


  
  

In order to support the query cache in the binary protocol,
we need to enable caching of server responses to COM_EXECUTE commands.

Insertion into the cache
------------------------
A query cache key consists of:
- query attributes
- query text

For the binary protocol the cache key will be generated as follows:

- key header will be extended with a flag that indicates that the
  data is in binary format.

This will separate all COM_EXECUTE responses from all COM_QUERY
responses in the cache.

- to create a query text for the key we will expand parameter data
  into query text in the same fashion as it currently happens for
  replication of prepared statements: parameter markers in the
  query text are substituted with string representation of
  parameter data.

This approach of generating the key has the drawback that the
types of placeholders are not taken into account.
As a result, the server will return the same response to a query
which supplies the same data but in a different type.
In theory, a change of the type of a parameter may lead to a
different result set being returned to the client, but no example
when this can actually happen is known at this point.

Support of 5.0 SQL features:

 - currently if a query uses a stored function it is not cached.
   The reason is that a function may be non-deterministic or
   produce a side effect, and thus from the caching point of view
   is treated similarly to the built-in non-deterministic
   functions such as RAND() and CURRENT_TIME().

   In future, when we start taking into account
   function characteristics, such as CONTAINS SQL and
   DETERMINISTIC, we will have to implement cache
   invalidation based on function name, to invalidate all
   responses that use a function when the function definition
   changes. This is not
 - if a query uses a view, it is inserted into the cache
   after the view was expanded (merged) and its tables were
   inserted into the list of all tables used in the query.
   Prepared statements in that respect are identical to the
   conventional statements, except that view expansion happens even
   earlier: at statement prepare stage.

Cache lookup
------------

Cache lookup will happen at a subsequent call to COM_EXECUTE.
The expanded statement text and environment information, such as
the current time zone, character set, etc. will be used to generate
a lookup key.

Invalidation
------------

Query tables in a prepared statement are not changed between
executions unless a prepared statement uses a stored function,
trigger or a view. Fortunately, currently no query that involves
stored functions and triggers can be cached. If a view was
changed, the query cache result is properly invalidated, whereas
the corresponding prepared statement is not (which is subject of
BUG#12713).  There is no problem for the query cache posed by this
bug, as it the result in the query cache is invalidated
independently of the statement cache. In all other respects PS query
invalidation works identically to invalidation of
conventional query results.

Limitations
------------
Support of Query Cache for SQL prepared statements is not a subject of this
worklog.

Other
-------
As current behaviour was clearly identified by many customers as a bug, a bug
report was re-opened for this task: BUG#735 "Prepared Statements: there is no
support for Query Cache". Kostja: This is the closest thing I was able to find.

Fixed by patches for BUG#735 and BUG#29318;
no further LLD needed.