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.

Missing initialization of the bitmap not_null_tables_cache to 0 
in the function Item_func::eval_not_null_tables caused this bug.
This function is called indirectly from the function
SELECT_LEX::update_used_tables after merging mergeable views and
derived tables into the main query. The leaf tables of resulting
query may change their bitmap numbers after this merge. That's why
the not_null_tables_cache bitmaps must be updated. Due to the bug 
mentioned above the result of the re-evaluation of the 
not_null_tables_cache turned out to be incorrect in some cases.
This could trigger an invalid conversion of outer joins into 
inner joins leading to invalid query result sets.

Also removed an implicit conversion from int to bool in the function
SELECT_LEX::update_used_tables.

The value of THD::used tables should be re-evaluated after merges
of views and derived tables into the main query. 
Now it's done in the function SELECT_LEX::update_used_tables.
The re-evaluation of the 'used_table' bitmaps for the items
in HAVING, GROUP BY and ORDER BY clauses has been added as well.
 

Semi-join fixes: make COST_VECT objects survive add_io(add_io_cnt=0, add_avg_cost=...) calls without
getting NaN in internal fields.

The bug was caused by an incorrect code of the function
Item_direct_view_ref::replace_equal_field introduced in the
patch for bugs 717577, 724942. The function erroneously
returned the wrapped field instead of the Item_direct_view_ref
object itself in the cases when no replacement happened.

The bug masked two other minor bugs that could result in not
quite correct output of the EXPLAIN command for some queries.
They were fixed in the patch as well.
 

- Scavenged subquery tests from testcases other than t/subselect*.test and put them into single file 

- Set the default
- Adjust the testcases so that 'new' tests are run with optimizations turned on.
- Pull out relevant tests from "irrelevant" tests and run them with optimizations on.
- Run range.test and innodb.test with both mrr=on and mrr=off

The bug itself has been fixed by MWL#89.

The bug itself has been fixed by MWL#89.

The offending query returns a wrong result set because the optimizer
erroneously eliminated the where condition evaluated it to TRUE.
The cause of this wrong transformation was that the flag maybe_null
for an inner table of the outer join was not set to TRUE after the 
table had replaced the wrapping view.
Now the function SELECT_LEX::update_used_tables resets the value
of the maybe_null flag for each leaf table of the query after all
merges of views have been done.

 

The bug itself has been fixed by MWL#89.

The bug itself has been fixed by MWL#89.

Analysis:
This bug is yet another incarnation of the generic problem
where optimization of the outer query triggers evaluation
of a subquery, and this evaluation performs a destructive
change to the subquery plan. Specifically a temp table is
created for the DISTINCT operation that replaces the
original subquery table. Later, select_describe() attempts
to print the table name, however, there is no corresponding
TABLE_LIST object to the internal temp table, so we get a
crash. Execution works fine because it is not interested in
the corresponding TABLE_LIST object (or its name).

Solution:
Similar to other such bugs, block the evaluation of expensive
Items in convert_const_to_int().