WL#7082: Move permanent transformations from JOIN::optimize () to JOIN::prepare ()

Status: Complete

GOAL: save CPU time, memory, avoid future "prepared statement" bugs, make code

MEANS: Refactor how the optimizer does permanent transformations to queries
(semijoin, in-to-exists, outer-to-inner-join).

Pushed to 5.7 on March 3rd, 2014.

User Documentation

No user documentation required.
No functional requirement.
Non-functional requirement: all queries should work as before, same results, no
performance decrease. This is a refactoring task.

This is how DML execution generally works. There may be unknown exceptions.

Immediately executed statement works like this:
 - prepare a single mem_root/arena for the lifetime
 - parse query, populate SELECT_LEX and TABLE_LIST structures.
 - prepare query, resolve and transform
 - make prepared data available working data structures
 - optimize query, applied to the working data structures
 - execute query
 - cleanup after execution
 - delete data structures

Prepared statement works like this:
a) PREPARE stmt:
 - prepare a mem_root for permanent representation of the statement
 - parse
 - prepare (resolve and transform)
b) EXECUTE stmt:
 - prepare a mem_root for statement execution
 - make a working copy of prepared data available in execution mem_root
 - prepare (resolve only)
 - optimize
 - execute
 - cleanup after query execution
 - revert preparation
 - either cleanup statement or repeat execution

"optimize" in (b) phase contains a few query transformations which are actually
permanent; they are permanent but because optimization happens in execution
phase (with execution memroot), making them permanent forces complicated and
buggy code.
This WL aims at moving those transformations to the (a) phase.


Take flatten_subqueries() and simplify_joins() to JOIN::prepare().

Then remove the second copy of "conds" in JOIN::optimize() (first copy is in
      conds ? conds->real_item()->copy_andor_structure(thd) : NULL;
which creates new AND/OR items, which has the bad effect that arguments of old
AND/OR items may be restored (by rollback_item_tree_changes()) in vain (causing

Remove sl->prep_where; when JOIN::prepare() ends, sl->where should not change
anymore (pointed content can still change), after rollback_item_tree_changes()
it should thus be fine for the next execution.
So this is implemented: select_lex->where becomes permanent version.
join->conds is the optimized copy, valid for one single execution.
Remove sl->prep_having, and TABLE_LIST::prep_join_cond too.
Advantage: before this worklog, we had that many calls to copy_andor_structure()
for the WHERE clause:
– prepare (): one call (in fix_prepare_information ())
– optimize (): one call for the first optimization (after simplify_joins ())
– at the start of each execution of the statement: one call (in
So, for one PREPARE and N EXECUTEs, that made 1 + 2 + (N-1) = N+2 calls.
After this worklog, it will be N (one call at the start of every optimize ()).
So we save two calls, which saves time and memory.
For the HAVING clause, we save one call, and also one call per JOIN condition.

Some variables are renamed:
- for easier identification/search: JOIN::conds to where_cond
- for consistency with the first renaming:
  SELECT_LEX::where to where_cond
  SELECT_LEX::having to having_cond
  JOIN::having to having_cond 

Branch is at my:trunk-wl7082.

We also make JOIN::prepare() an almost-JOIN-less member of SELECT_LEX.