This worklog task will contain a solution for the following problem:

Problem Description
-------------------
Query optimizer makes certain query rewrites on PREPARE phase. Some of those
rewrites depend on the properties of the underlying tables. The problem is 
caused by this scenario:

ProblemScenario:
  1. PREPARE stmt ;   (optimizer makes the rewrite)
  2. ALTER TABLE ...; (properties of the queried tables change and the
                       rewrite becomes invalid)
  3. EXECUTE stmt;    (Boom! crash or wrong query results)

Problems of this kind are observed in BUG#27430, WL#3740, BUG#27690 (list not
exhaustive).
According to Dlenev, the problem is likely not limited to prepared statements
but affects Stored Routines and Triggers as well.

Solution
--------
We should detect relevant changes and undo the invalidated rewrites.
Change detection can be perfomed as follows:

1. Let every re-write record the dependencies - conditions that need to be 
   satisfied in order for the rewrite to be valid. (For example, BUG#27430 
   fix will remember whether IN-compared expressions were NOT NULL or not)
   On EXECUTE we will check if the required conditions are still true.

2. [Ann said this how it was done in Firebird]: Introduce certain kinds of 
   locks. Prepared statement will hold locks on objects it depends on,
   attempts to modify those objects will notify the prepared statement that
   it needs to re-prepare itself.

3. Let each object (table, VIEW, etc) have a modification timestamp. The 
   prepared statement will remember and compare timestamps. Since timestamps
   only increase we can just remember and compare the sum of timestamps of
   all used objects.

Approach #1 is the most precise - we will detect only "relevant" changes.
Apporach #3 seems to be the easiest to implement in the current architecture.


If we detected a change, we may 

1. Re-do the relevant steps/transformations. This is theoretically the most
   efficient way, but it is hard to implement because different rewrites 
   depend on each other.

2. Re-prepare the entire statement. This is less efficent but is easier to
   implement. This seems to be more feasible option.

(Note: According to Kostja & Dlenev, failing the statement and producing an
error is not an option)

We should also add a statistic counter to count the number of re-prepares
(request from Jan Kneschke)

(setting the supervisor to "None needed" as I don't know who should it be)

History

=======

The original email thread is here:

Thu, 29 Mar 2007 15:57:16 +0400

From: Sergey Petrunia <sergefp@mysql.com>

To: dev-public@mysql.com...

Subject: Optimizer query rewrites and prepared statements: design, comments?



Interfacing with query optimizer module

=======================================



In the light of this WL entry, the query optimizer should work as follows:



* All query rewrites that depend on query text and DDLs of the queried 

  tables/VIEWs should be done at PREPARE phase and be permanent for the lifetime

  of the statement.



* The code that is invoked at EXECUTE can assume that DDLs of the used tables 

  are the same as they were at PREPARE phase.

You must be logged in to tag this worklog

No Comments yet