As one single patch.

semijoin, outer-join-to-inner, parenthesis-removal,
join-condition-to-where-clause are moved from JOIN::optimize() to
JOIN::prepare(), which is renamed to select_lex::prepare().

Old approach for WHERE/HAVING conditions:
at end of preparation, save copies of conditions in
sl->prep_where/having, then allow oneself to trash sl->where/having in
optimization; in optimization make sure to keep in sync
sl->where/having with join->conds/having. At start of next execution,
recreate sl->where/having from the "prep_" copies.
New approach:
At end of preparation, sl->where/having is considered frozen. In
optimization, make trashable copies of it, and use only those
copies. Ditch them at end of execution.

Some functions like mysql_select() are made to use select_lex->where;
in the pre-patch situation select_lex->where was passed as argument
AND the function assumed that this argument was
==select_lex->where... this change makes code simpler.

Made some functions which use JOIN to rather use, and belong to, SELECT_LEX:
record_join_nest_info
simplify_joins
convert_subquery_to_semijoin
resolve_subquery
flatten_subqueries.
They try to use JOIN as little as reasonably possible.

Moved JOIN::prepare() to select_lex, and simplified its signature
(arguments can be found in select_lex).
Made setup_conds() member of select_lex, with less arguments.
Removed arguments from setup_ref_array().

Simplified setup_wild(), more JOIN members are made private,
JOIN::join_list removed, reset_nj_counters goes to select_lex.

JOIN::table_list is now used only in optimization/execution.

JOIN_TAB::on_expr_ref, JOIN/select_lex::where/conds/having are
renamed, some getters/setters are added.

Simplified select_lex::first_cond_optimization: rename member to be
more specific, and removed argument in make_join_statistics().

I remove some hacks which came in previous PS fixes (see the WL for
bug numbers), because they become superfluous.

Changes in opt trace tests: trace blocks for
semijoin/outer-join-to-inner move from the join_optimization block to
the join_preparation block. In ps-specific tests, where the shown
trace is of EXECUTE, it implies that now the trace starts with the
transformation already done (by PREPARE).

See the WL text for overview of goal and code changes.

Additional details are in these commit comments.

@  mysql-test/suite/opt_trace/include/bugs.inc

bug was fixed long ago

@  mysql-test/suite/opt_trace/r/bugs_no_prot_all.result

Also fixed a bad number for the query block where the view is merged
("in_select#").

@  mysql-test/suite/opt_trace/r/general2_no_prot.result

fixed trace of view merging/materialization

@  mysql-test/suite/opt_trace/r/general_ps_prot_all.result

Around line 7866, we see:
"original_condition": "(1 and (`t2`.`s` = 'c') and (`t6`.`d` = `f1`()))",
"steps": [
  {
    "transformation": "equality_propagation",
-    "resulting_condition": "(1 and multiple equal('c', `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
+    "resulting_condition": "(1 and (`t6`.`d` = `f1`()) and multiple equal('c', `t2`.`s`))"
A multiple equality is not created anymore for t6.d=f1(). I think this
is ok. Creation of such item requires f1() to be a constant (see 
check_simple_equality() which looks for field=constant), and f1()
should not be considered constant, it is a stored function which may
return a different value for each row of t2.

@  mysql-test/t/sp.test

Tests added along the way, when fixing bugs in the prototype

@  mysql-test/r/subquery_all.result
Result changes in EXPLAIN: they come from the can_skip_order change in sql_union.cc:
we used to pass a NULL pointer to prepare(), now we instead empty the list in select_lex;
this has the advantage of showing the optimization in the query printed by EXPLAIN;
and this alternative technique looks ok because remove_redundant_subquery_clauses
does it too.

@  sql/item.h

removed one hack ("real_items" argument), added chop_ref

@  sql/item_cmpfunc.cc

removed one hack ("real_items" argument)

@  sql/item_cmpfunc.h

removed one hack ("real_items" argument)

@  sql/item_subselect.cc

About the removal of "// did we changed top item of WHERE condition":
- in-to-exists is, as before, a permanent transformation
- pre-patch, Item_in_subselect::fix_fields() would be passed &JOIN::conds as "ref"
argument, so when it changes the condition (injects outer=inner
equality in subquery's WHERE), it changes *ref, which changes
JOIN::conds, but because it is a permanent transformation, it also
needs to manually "keep in sync" select_lex->where.
- post-patch, fix_fields() operates on &select_lex->where_cond, and
JOIN::where_cond is not "alive" yet (it starts its life in
JOIN::optimize()), so no manual syncing is needed.

Likewise, no manual syncing of having_for_explain is needed.

@  sql/sql_base.cc

No manual syncing needed (see comment of item_subselect.cc).

@  sql/sql_delete.cc

Don't pass "conds", just use select_lex->where_cond as input. When we
want to optimize the condition, we make a copy of it.

@  sql/sql_lex.cc

Part of the end-of-prepare job of
st_select_lex::fix_prepare_information() has moved to 
the start-of-optimize get_optimizable_conditions().
One real_item() is removed, in this move.

@  sql/sql_optimizer.cc

Get trashable copies at start of JOIN::optimize() and use only them.
Removed dead code in #ifdef.
Transformations move to JOIN::prepare(), and the horror of "let's
update prep_where because we did permanent transformations in
JOIN::optimize()", is gone - this saves some copying and memory allocations.
In simplify_joins(), the part:
          /* If join condition has a pending rollback in THD::change_list */
          join->thd->change_item_tree_place(table->join_cond_ref(), &conds);
was useless: "conds" is a local variable, &conds could never be found
when change_item_tree_place() searches. So I replace "conds" by an
Item** passed in argument. In a test file I added some queries which
used to break (lack a rollback) due to this useless code.
In record_join_nest_info(), prep_join_cond; the corresponding job is
now in get_optimizable_join_conditions().
In replace_subcondition(), removed useless manual syncing.
At the end of flatten_subqueries(), same.

@  sql/sql_prepare.cc

use setup_fields_with_no_wrap, makes less code lines.
In reinit_stmt_before_use(), don't recreate select_lex->where_cond, it is
already good (== made of permanent items); only need to clean up those
items, to make them ready for reusal.

@  sql/sql_resolver.cc

JOIN::prepare now operates only on select_lex->where, not JOIN::conds
which is now reserved for JOIN::optimize.
JOIN::prepare does transformations at its end.
Some functions now done at "prepare" time are moved to this file.

@  sql/sql_union.cc

We work around an oddity of IN->EXISTS (whose effects were only a
strange WHERE in the trace, when I started trusting
select_lex->where_cond instead of always passing conds=NULL
to JOIN::prepare() of the fake select lex).

@  sql/sql_update.cc

When we start optimizing UPDATE, we get trashable conditions.

@  sql/sql_view.cc

Fixed a bad number for the query block where the view is merged
("in_select#"), this is visible in bugs_no_prot_all.result file.

@  sql/table.h

In TABLE_LIST, m_join_cond becomes the permanent condition,
m_optim_join_cond a trashable copy created at start of optimization.
Moved all optimization-only members to one place.