work in the IN subqueries

The pushdown into the materialized derived table/view wasn't done because
optimize() for the derived was called before any conditions that can
be pushed down were extracted. So optimize() in
convert_join_subqueries_to_semijoins() method is called too early and is
unnecessary. The second optimize() call in mysql_handle_single_derived()
is enough.

The implementations of the convert_to_basic_const_item() virtual
function for the Item_cache classes should call cache_value() when
value_cached == NULL.

           Item::derived_field_transformer_for_having

The crash occurred due to an inappropriate handling of multiple equalities
when pushing conditions into materialized views/derived tables. If equalities
extracted from a multiple equality can be pushed into a materialized
view/derived table they should be plainly conjuncted with other pushed
predicates rather than form a separate AND sub-formula.

with condition_pushdown_for_derived=on

This bug is a consequence of the bug mdev-14368 fixed in 5.5.59.

It's better to prohibit pushdown of conditions that involve
regexp_substr() and regexp_replace() into materialized derived
tables / views until proper implementations of the get_copy()
virtual method are provided for those functions.

When an equality that can be pushed into a materialized derived
table / view is extracted from multiple equalities and their
operands are cloned then if they have some pointers to Item_equal
objects those pointers must be set to NULL in the clones. Anyway
they are not valid in the pushed predicates.

This patch corrects the fix for mdev-12845.

This patch fills in a serious flaw in the
code that supports condition pushdown into
materialized views / derived tables.

If a predicate happened to contain a reference
to a mergeable view / derived table and it does
not depended directly on the target materialized
view / derived table then the predicate was not
considered as a subject to pusdown to this view
/ derived table.

Condition pushdown into derived tables / views with side effects
is not allowed.

The fields st_select_lex::cond_pushed_into_where and
st_select_lex::cond_pushed_into_having should be re-initialized
for the unit specifying a derived table at every re-execution
of the query that uses this derived table, because the result
of condition pushdown may be different for different executions.

The fix for bug mdev-11488 introduced the virtual method
convert_to_basic_const_item for the class Item_cache.
The implementation of this method for the class Item_cache_str
was not quite correct: the server could crash if the cached item
was null.
A similar problem could appear for the implementation of
this method for the class Item_cache_decimal. Although I could not
reproduce the problem I decided to change the code appropriately.

When a condition containing NULLIF is pushed into a materialized
view/derived table the clone of the Item_func_nullif item must
be processed in a special way to guarantee that the first argument
points to the same item as the third argument.

The patch for bug mdev-10882 tried to fix it by providing an
implementation of the virtual method build_clone for the class
Item_cache. It's turned out that it is not easy provide a valid
implementation for Item_cache::build_clone(). At the same time
if the condition that can be pushed into a materialized view
contains a cached item this item can be substituted for a basic
constant of the same value. In such a way we can avoid building
proper clones for Item_cache objects when constructing pushdown
conditions.

The bug was fixed by the patch for mdev-10882.

The implementation of the virtual method build_clone for
the class Item_cache was missing.

The fix for bug 11072 was not complete though it also fixed
the bug mdev-10800.
This patch resolves the problems of all three bugs.

There were no implementations for the virtual functions
exclusive_dependence_on_table_processor and
exclusive_dependence_on_table_processor. As a result
the procedure pushdown_cond_for_derived erroneously
detected some conditions with outer references as pushable
into materialized view / derived table.

The class Item_func_nop_all missed an implementation
of the virtual method get_copy.
As a result if the condition that can be pushed into
into a materialized view / derived table contained
an ANY subselect then the pushdown condition was built
incorrectly.

Do not push conditions from where into materialized inner tables
of outer joins: this is not valid.

In a general case the conditions with outer fields cannot
be pushed into materialized views / derived tables.
However if the outer field in the condition refers to a
single row table then the condition may be pushable.
In this case a special care should be taken for outer
fields when pushing the condition into a materialized view /
derived table.

If a materialized derived table / view is specified by a unit
with SELECTs containing ORDER BY ... LIMIT then condition pushdown
cannot be done for these SELECTs.
If a materialized derived table / view is specified by a unit
containing global ORDER BY ... LIMIT then condition pushdown
cannot be done for this unit.

The condition pushed into WHERE/HAVING of a materialized
view/derived table may differ for different executions of
the same prepared statement. That's why the should be
ANDed with the existing WHERE/HAVING conditions only after all
permanent transformations of these conditions has been
performed.

Do not push conditions into materialized views/derived tables
marked with the flag 'fill_me'.

This bug in the code of Item_ref::build_clone could
cause corruption of items in where conditions.
Also made sure that equality predicates extracted
from multiple equality items to be pushed into
materialized views were cloned.

for materialized views and derived tables: there were no
push-down if the view was defined as union of selects
without aggregation. Added test cases with such unions.

Adjusted result files after the merge of the code for mdev-9197.