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.

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.

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().

The function generate_derived_keys_for_table incorrectly handled
the cases when a materialized view or derived table could be accessed
by different keys on the same fields if these keys depended on the
same tables.
  

Adjust PBXT test results.

- SELECT_LEX::merge_subquery should not set "(*in_subq)->emb_on_expr_nest= derived" for subqueries that 
  are in the ON expressions of semi-joins.

BUG#803365: Crash in pull_out_semijoin_tables with outer join + semijoin + derived tables in maria-5.3 with WL#106
- Don't perform table pullout out of semi-join nests that have nested outer joins.

The bug lp:798213 exposes a design flaw in --innodb-release-locks-early.
It does not work with InnoDB crash recovery, so it breaks transactional
integrety. So remove the feature.

  semijoin=on,firstmatch=on,loosescan=on
to
  semijoin=off,firstmatch=off,loosescan=off
Adjust the testcases:
- Modify subselect*.test and join_cache.test so that all tests
  use the same execution paths as before (i.e. optimizations that
  are being tested are enabled)
- Let all other test files run with the new default settings (i.e.
  with new optimizations disabled)
- Copy subquery testcases from these files into t/subselect_extra.test
  which will run them with new optimizations enabled.

Analysis:
This bug consists of two related problems that are
result of too early evaluation of single-row subqueries
during the optimization phase of the outer query.

Several optimizer code paths try to evaluate single-row
subqueries in order to produce a constant and use that
constant for further optimzation.

When the execution of the subquery peforms destructive
changes to the representation of the subquery, and these
changes are not anticipated by the subsequent optimization
phases of the outer query, we tipically get a crash or
failed assert.

Specifically, in this bug the inner-most suqbuery with
DISTINCT triggers a substitution of the original JOIN
object by a single-table JOIN object with a temp table
needed to perform the DISTINCT operation (created by
JOIN::make_simple_join).

This substitution breaks EXPLAIN because:
a) in the first example JOIN::cleanup no longer can
reach the original table of the innermost subquery, and
close all indexes, and
b) in this second test query, EXPLAIN attempts to print
the name of the internal temp table, and crashes because
the temp table has no name (NULL pointer instead).

Solution:
a) fully disable subquery evaluation during optimization
in all cases - both for constant propagation and range
optimization, and
b) change JOIN::join_free() to perform cleanup irrespective
of EXPLAIN or not.

- Fixed multi-user problem with one thread doing inserts and another doing scans that gave error 175
- Fixed bug that caused assert in move_to_next_bitmap() & _ma_read_bitmap_page()
- Much more DBUG_ASSERT(!maria_assert_if_crashed_table) to detect errors early
- EXTERNAL_LOCKING -> MARIA_EXTERNAL_LOCKING (to use same define everywhere

storage/maria/ma_bitmap.c:
  More secure handling of first_bitmap_with_space (now we also take care of wrong values)
  Don't set page to -1; This fixed unlikely bug that caused assert in move_to_next_bitmap() & _ma_read_bitmap_page()
storage/maria/ma_blockrec.c:
  More DBUG_ASSERT()'s
  Fixed multi-user problem with one thread doing inserts and another doing scans that gave error 175
  (We should use data_file_length from start of scan, not new value as new bitmap page may not yet be in page cache)
storage/maria/ma_check.c:
  EXTERNAL_LOCKING -> MARIA_EXTERNAL_LOCKING (to use same define everywhere)
storage/maria/ma_checkpoint.c:
  Made maria_checkpoint_min_activity static so that one can change it in debugger while testing.
  Fixed long standing performance problem that caused write of state info at checkpoint for any file that was ever changed since open.
storage/maria/ma_create.c:
  EXTERNAL_LOCKING -> MARIA_EXTERNAL_LOCKING (to use same define everywhere
storage/maria/ma_dynrec.c:
  Added missing MARIA_EXTERNAL_LOCKING (minor performance improvement)
storage/maria/ma_locking.c:
  EXTERNAL_LOCKING -> MARIA_EXTERNAL_LOCKING (to use same define everywhere
storage/maria/ma_open.c:
  EXTERNAL_LOCKING -> MARIA_EXTERNAL_LOCKING (to use same define everywhere
storage/maria/ma_pagecache.c:
  Added assert to detect reads outside of data file
storage/maria/maria_def.h:
  Added checkpoint_state to cache state writes in checkpoint

The assert conditions in the functions Item_direct_ref_to_ident::transform
and Item_direct_ref_to_ident::compile could be not valid after constant
propagation when fields and field references may be substituted for constants.
Not only these invalid asserts have been removed, but the functions containing
them have been removed as well because now Item_ref::transform and 
Item_ref::compile can be used instead of them.

include/Makefile.am:
  Added missing \
mysql-test/mysql-test-run.pl:
  Suppress warning when ha_archive is not compiled
mysql-test/r/variables-big.result:
  Updated results

If no index is used to access a materialized derived table or view
then the value of TABLE_REF::key for this table must be (-1).