BUG##806524: Assertion `join->best_read < 1.7976931348623157e+308 with table_elimination=on and derived_merge=on 
reset_nj_counters() used to rely on the fact that join nests have 
table->table==NULL. This ceased to be true wit new derived table
optimizations. Use test for table->nested_join!=NULL instead.

- Add testcase.

Analysis:
Both the wrong result and the valgrind warning were a result
of incomplete cleanup of the MIN/MAX subquery rewrite. At the
first execution of the query, the non-aggregate subquery is
transformed into an aggregate MIN/MAX subquery. During the
fix_fields phase of the MIN/MAX function, it sets the property
st_select_lex::with_sum_func to true.

The second execution of the query finds this flag to be ON.
When optimization reaches the same MIN/MAX subquery
transformation, it tests if the subquery is an aggregate or not.
Since select_lex->with_sum_func == true from the previous
execution, the transformation executes the second branch that
handles aggregate subqueries. This substitutes the subquery
Item into a Item_maxmin_subselect. At the same time elsewhere
it is assumed that the subquery Item is of type
Item_allany_subselect. Ultimately this results in casting the
actual object to the wrong class, and calling the wrong
any_value() method from empty_underlying_subquery().

Solution:
Cleanup the st_select_lex::with_sum_func property in the case
when the MIN/MAX transformation was performed for a non-aggregate
subquery, so that the transformation can be repeated.

This bug could lead to wrong result sets for a query over a
materialized derived table or view accessed by a multi-component
key.
It happened because the function get_next_field_for_derived_key
was supposed to update its argument, and it did not do it.

Also:
1. simplified the code of the function mysql_derived_merge_for_insert.
2. moved merge of views/dt for multi-update/delete to the prepare stage.
3. the list of the references to the candidates for semi-join now is
   allocated in the statement memory.

mysql-test/r/subselect_scache.result:
  Test with subquery cache on.
mysql-test/t/subselect_scache.test:
  Test with subquery cache on.

Made it 'off' by default.

- {ha_myisam,ha_maria}::index_read_idx_map should also initialize end_range, because index condition 
  function will attempt to check it. We initialize it like index_init() does.

(This is not a real fix for this bug, even though it makes it to no longer repeat)
- Semi-join subquery predicates, i.e. ... WHERE outer_expr IN (SELECT ...) may have null-rejecting properties,
  may allow to convert outer joins into inner.
- When convert_subq_to_sj() injected IN-equality into parent's WHERE/ON clause, it didn't call 
  $new_cond->top_level_item(), which would cause null-rejecting properties to be lost.
- Fixed, now the mentioned outer-to-inner conversion will really take place.

- Added an initial set of feature-specific test cases
- Handled the special case where the materialized subquery of an
  IN predicates consists of only NULL values.
- Fixed a bug where making Item_in_subselect a constant,
  didn't respect its null_value value.

- Make {ha_myisam,ha_maria}::index_read_idx_map check pushed index condition.
- Address review feedback (added comments)

Analysis:

For some of the re-executions of the correlated subquery the
where clause is false. In these cases the execution of the
subquery detects that it must generate a NULL row because of
implicit grouping. In this case the subquery execution reaches
the following code in do_select():

        while ((table= li++))
          mark_as_null_row(table->table);

This code marks all rows in the table as complete NULL rows.
In the example, when evaluating the field t2.f10 for the second
row, all bits of Field::null_ptr[0] are set by the previous call
to mark_as_null_row(). Then the call to Field::is_null()
returns true, resulting in a NULL for the MAX function.

Thus the lines above are not suitable for subquery re-execution
because mark_as_null_row() changes the NULL bits of each table
field, and there is no logic to restore these fields.

Solution:

The call to mark_as_null_row() was added by the fix for bug
lp:613029. Therefore removing the fix for lp:613029 corrects
this wrong result. At the same time the test for lp:613029
behaves correctly because the changes of MWL#89 result in a
different execution path where:
- the constant subquery is evaluated via JOIN::exec_const_cond
- detecting that it has an empty result triggers the branch
  if (zero_result_cause)
    return_zero_rows()
- return_zero_rows() calls mark_as_null_row().

The attribute not_null_tables could be calculated incorrectly in the
function SELECT_LEX::update_used_tables for queries over views 
with row items in the WHERE clause. It happened because no 
implementation of the virtual callback function eval_not_null_tables
was provided for the class Item_row.
Also slightly optimized the code calculating the value of the maybe_null
flag for tables in the function SELECT_LEX::update_used_tables.

Analysis:
This is a bug in MWL#68, where it was incorrectly assumed
that if there is a match in the only non-null key, then
if there is a covering NULL row on all remaining NULL-able
columns there is a partial match. However, this is not the case,
because even if there is such a null-only sub-row, it is not
guaranteed to be part of the matched sub-row. The matched sub-row
and the NULL-only sub-row may be parts of different rows.

In fact there are two cases:
- there is a complete row with only NULL values, and
- all nullable columns contain only NULL values.

These two cases were incorrectly mixed up in the class member
  subselect_partial_match_engine::covering_null_row_width.


Solution:
The solution is to:
- split covering_null_row_width into two members:
  has_covering_null_row, and has_covering_null_columns, and
- take into account each state during initialization and
  execution.

class for Item_func_xor. Added the implementation of the
subst_argument_checker virtual method that the objects of this 
class used to use before the patch.
Reverted the previous result changes in sunselect_sj and
subselect_sj_jcl6.
  

In addition to the bug fix explained below, the patch performs
few renames, and adds some comments to avoid similar problems.

Analysis:
The failed assert was due to a bug in MWL#68, where it was
incorrectly assumed that the size of the bitmap
subselect_rowid_merge_engine::null_only_columns should be
the same as the size of the array of Ordered_keys.

The bitmap null_only_columns contains bits to mark columns
that contain only NULLs. Therefore the indexes of the bits
to be set in null_only_columns are different from the indexes
of the Ordered_keys. If there is a NULL-only column that appears
in a table after the last partial match column with Ordered_key,
this NULL-only column would require setting a bit with index
bigger than the size of the bitmap null_only_columns.

Accessing such a bit caused the failed assert.

Solution:
Upon analysis, it turns out that null_only_columns is not needed
at all, because we are looking for partial matches, and having
such columns guarantees that there is a partial match for any
corresponding outer value.

Therefore the patch removes
  subselect_rowid_merge_engine::null_only_columns.

The bitmap of used tables must be evaluated for the select list of every
materialized derived table / view and saved in a dedicated field.
This is also applied to materialized subqueries.

Port of code for: (part of testcase is in mysql-test/t/subquery*.test and will be ported separately)

Bug#11766642: crash in Item_field::register_field_in_read_map 
              with view

(Former 59793)

Prior to the refactoring in this patch, Item_cond_xor behaved 
partially as an Item_cond and partially as an Item_func. The
reasoning behind this was that XOR is currently not optimized
(thus should be Item_func instead of Item_cond), but it was 
planned optimize it in the future (thus, made Item_cond anyway 
to ease optimization later). 

Even though Item_cond inherits from Item_func, there are 
differences between these two. One difference is that the 
arguments are stored differently. Item_cond stores them in a 
list while Item_func store them in an args[]. 

BUG no 45221 was caused by Item_cond_xor storing arguments in 
the list while users of the objects would look for them in 
args[]. The fix back then was to store the arguments in both 
locations.

In this bug, Item_cond_xor initially gets two Item_field 
arguments. These are stored in the list inherited from 
Item_cond and in args[] inherited from Item_func. During
resolution, find_field_in_view() replaces the Item_fields 
stored in the list with Item_direct_view_refs, but args[] 
still points to the unresolved Item_fields. This shows that 
the fix for 45221 was incorrect.

The refactoring performed in this patch removes the confusion
by making the XOR item an Item_func period. A neg_transformer() 
is also implemented for Item_func_xor to improve performance 
when negating XOR expressions. An XOR is negated by negating 
one of the operands.

Auto-generated names for view field items must be allocated in
the statement memory, not in the execution memory of the statement.

The cause of the crash is sj_nest->sj_subq_pred->unit->first_select()->item_list
contains "stale" items for the second execution. By "stale" I mean that they have
item->fixed==FALSE, and they are Item_field object instead of Item_direct_view_ref.

The solution is to use sj_nest->sj_subq_pred->unit->first_select()->ref_pointer_array.
Surprisingly, that array contains items that are ok.

Oracle team has introduced and is using NESTED_JOIN::sj_inner_exprs, but we go without that
and always copy the ref_pointer_array.