Bug#48623: Multiple subqueries are optimized incorrectly
      
The function setup_semijoin_dups_elimination() has a major loop that
goes through every table in the JOIN object. Usually, there is a normal
"plus one" increment in the for loop that implements this, but each semijoin
nest is treated as one entity and there is another increment that skips past
the semijoin nest to the next table in the JOIN object. However, when
combining these two increments, the next joined table is skipped, and if that
happens to be the start of another semijoin nest, the correct processing
for that nest will not be carried out.

mysql-test/r/subselect_sj.result:
  Added test results for bug#48623
mysql-test/r/subselect_sj_jcl6.result:
  Added test results for bug#48623
mysql-test/t/subselect_sj.test:
  Added test case for bug#48623
sql/opt_subselect.cc:
  Omitted the "plus one" increment in the for loop, added "plus one"
  in the remaining switch case, fixed coding style issue in remaining
  increment operations.

Fix two problems:
1. Let optimize_semijoin_nests() reset sj_nest->sjmat_info irrespectively  
   of value of optimizer_flag. We need this in case somebody has turned optimization
   off between reexecutions of the same statement.

2. Do not pull out constant tables out of semi-join nests. The problem is that pullout 
   operation is not undoable, and if a table is constant because it is 1/0-row table it 
   may cease to be constant on the next execution. Note that tables that are constant
   because of possible eq_ref(const) access will still be pulled out as they are
   considered functionally-dependent.

Bug#48213 Materialized subselect crashes if using GEOMETRY type

The problem occurred because during semi-join a materialized table
was created which contained a GEOMETRY column, which is a specialized
BLOB column.  This caused an segmentation fault because such tables will
have extra columns, and the semi-join code was not prepared for that.

The solution is to disable materialization when Blob/Geometry columns would 
need to be materialized.  Blob columns cannot be used for index look-up 
anyway, so it does not makes sense to use materialization.

This fix implies that it is detected earlier that subquery materialization
can not be used.  The result of that is that in->exist optimization may
be performed for such queries.  Hence, extended query plans for such
queries had to be updated.

mysql-test/r/subselect_mat.result:
  Update extended query plan for subqueries that cannot use materialization
  due to Blobs.
mysql-test/r/subselect_sj.result:
  Updated result file.
mysql-test/r/subselect_sj_jcl6.result:
  Update result file.
mysql-test/t/subselect_sj.test:
  Add test case for Bug#48213 that verifies that semi-join works when subquery select list contain Blob columns.  Also verify that materialization is not
  used.
sql/opt_subselect.cc:
  Disable materialization for semi-join/subqueries when the subquery select list
  contain Blob columns.

BUG#50019: Wrong result for IN-subquery with materialization
- Fix equality substitution in presense of semi-join materialization, lookup and scan variants
  (started off from fix by Evgen Potemkin, then modified it to work in all cases)

This patch does three things:
- It adds the possibility to force the execution of top-level [NOT] IN
  subquery predicates via the IN=>EXISTS transformation. This is done by
  setting both optimizer switches partial_match_rowid_merge and
  partial_match_table_scan to "off".
- It adjusts all test cases where the complete optimizer_switch is
  selected because now we have two more switches.
- For those test cases where the plan changes because of the new available
  strategies, we switch off both partial match strategies in order to
  force the "old" IN=>EXISTS strategy. This is done because most of these
  test cases specifically test bugs in this strategy.

sql/opt_subselect.cc:
  Adds the possibility to force the execution of top-level [NOT] IN
  subquery predicates via the IN=>EXISTS transformation. This is done by
  setting both optimizer switches partial_match_rowid_merge and
  partial_match_table_scan to "off".

Automerge with 5.3-subqueries

* Implemented a second partial matching strategy via table scan.
  This strategy is a fallback when there is no memory for rowid merging.

* Refactored the selection and creation of partial matching strategies,
  so that the choice of strategy is encapsulated in a separate method
  choose_partial_match_strategy().

* Refactored the representation of partial match strategies so that:
  - each strategy is represented by a polymorphic class, and
  - the base class for all partial match strategies contains common
    execution code.

* Added an estimate of the memory needed for the rowid merge strategy,
  and the system variable "rowid_merge_buff_size" to control the maximum
  memory to be used by the rowid merge algorithm.

* Added two optimizer_switch system variables to control the choice of
  partial match strategy:
  "partial_match_rowid_merge", "partial_match_table_scan".

* Fixed multiple problems with deallocation of resources by the partial
  match strategies.


sql/mysql_priv.h:
  * Added two optimizer_switch system variables to control the choice of
    partial match strategy:
    "partial_match_rowid_merge", "partial_match_table_scan".
sql/mysqld.cc:
  * Added two optimizer_switch system variables to control the choice of
    partial match strategy:
    "partial_match_rowid_merge", "partial_match_table_scan".
  * Added a system variable "rowid_merge_buff_size" to control the maximum
    memory to be used by the rowid merge algorithm.
sql/set_var.cc:
  * Added a system variable "rowid_merge_buff_size" to control the maximum
    memory to be used by the rowid merge algorithm.
sql/sql_class.h:
  * Added a system variable "rowid_merge_buff_size" to control the maximum
    memory to be used by the rowid merge algorithm.
support-files/build-tags:
  Newer versions of BZR require the recursive flag in order to list all files.

- The problem was that DuplicateWeedout strategy setup code wasn't aware of the 
  fact that join buffering will be used and applied optimization that doesn't work
  together with join buffering. Fixed by making DuplicateWeedout setup code to have 
  a pessimistic check about whether there is a chance that join buffering will be 
  used.
- Make JOIN_CACHE_BKA::init() correctly process Copy_field elements that denote saving
  current rowids in the join buffer.

mysql-test/r/subselect_sj2.result:
  Update test results
mysql-test/r/subselect_sj2_jcl6.result:
  Update test results
mysql-test/r/subselect_sj_jcl6.result:
  Testcase
mysql-test/t/subselect_sj2.test:
  Update test results
mysql-test/t/subselect_sj_jcl6.test:
  Testcase
sql/opt_subselect.cc:
  - The problem was that DuplicateWeedout strategy setup code wasn't aware of the 
    fact that join buffering will be used and applied optimization that doesn't work
    together with join buffering. Fixed by making DuplicateWeedout setup code to have 
    a pessimistic check about whether there is a chance that join buffering will be 
    used.
sql/sql_join_cache.cc:
  Make JOIN_CACHE_BKA::init() correctly process Copy_field elements that denote saving current rowids in the join buffer.
sql/sql_select.cc:
  Added a question note

The function JOIN_CACHE::read_all_record_fields could return 0
for an incremental join cache in two cases:
1. there were no more records in the associated join buffer
2. there was no table fields stored in the join buffer.
As a result the function JOIN_CACHE::get_record() could
return prematurely and did not read all needed fields from
join buffers into the record buffer.

Now the function JOIN_CACHE::read_all_record_fields returns
-1 if there are no more records in the associated join buffer.

Made sure that join buffers could be used for inner tables of
any semi-join when the first match strategy is employed.

it's working on the WHERE clause.

Re-worked fix of Tor Didriksen:
 The problem was that fix_after_pullout() after semijoin conversion 
 wasn't propagated from the view to the underlying table. 
 On subesequent executions of the prepared statement, 
 we would mark the underlying table as 'dependent' and the predicate 
 anlysis would lead to a different (and illegal) execution plan.

This patch mainly adds sorting of all indexes for partial matching
according to their NULL selectivity. The patch also fixes a related bug
in subselect_rowid_merge_engine::test_null_row() where the wrong matched
indexes were skipped.

In addition the patch:
- adds few ::print() methods,
- renames few variables that had similar names but different purpose.

for table elimination in debug builds.
(part 2)

for table elimination in debug builds.

In Item_ref::fix_fields() do invoke mark_as_dependent() for outside 
references in all cases (see email for more details)

sql/item.cc:
  In Item_ref::fix_fields() do invoke mark_as_dependent() for outside references in all cases.
sql/item.h:
  Change Field_enumerator to enumerate Item_field-s not Field-s.
sql/item_subselect.cc:
  Change Field_enumerator to enumerate Item_field-s not Field-s.
sql/opt_table_elimination.cc:
  Change Field_enumerator to enumerate Item_field-s not Field-s.

  Don't go into branch that calls upper_refs.empty() more than once per
  PREPARE or EXECUTE
* Avoid crashing when processing references to outside from subquery's HAVING
  (will explain in more details in email)

sql/item.h:
  * Avoid crashing when processing references to outside from subquery's HAVING
    (will explain in more details in email)
sql/item_subselect.cc:
  * Better self-recursion protection in Item_subselect::fix_fields. 
    Don't go into branch that calls upper_refs.empty() more than once per
    PREPARE or EXECUTE

prepared statements: re-collect list of upper refs on every PS re-execution.

This patch implements correct NULL semantics for materialized subquery execution.
The implementation has the following properties and main limitations:
- It passes all query result tests, but fails a number of EXPLAIN tests because of
  changed plans.
- The EXPLAIN output for partial matching is not decided yet.
- It works only when all necessary indexes fit into main memory. Notice that these
  are not the general B-tree/Hash indexes, but instead much more compact ones,
  therefore this limitation may not be a problem in many practical cases.
- It doesn't contain specialized tests.
- In several places the implementation uses methods that are modified copies of
  other similar methods. These cases need to be refactored to avoid code duplication.
- Add a test if the predicate is top-level just before deciding on partial matching.
  If it is top-level, use a more efficient exec method (index lookup).
- Add sorting of indexes according to their selectivity. The code is almost there.
- Needs more comments, and to sync existing ones with the implementation.

sql/item_cmpfunc.h:
  Expose the Arg_comparator of a comparison predicate. This makes it possible to
  directly get the comparison result {-1,0,1}, which is not possible through the
  val_XXX() methods which "fold" such results into a boolean.
sql/item_subselect.cc:
  The core of the implementation of MWL#68.
sql/item_subselect.h:
  The core of the implementation of MWL#68.
sql/opt_subselect.cc:
  Removed the limitation for materialized subquery execution that it is applicable only
  for top-level predicates.
sql/sql_class.cc:
  New class select_materialize_with_stats that collects data statistics about
  the data being inserted into the target table.
sql/sql_class.h:
  New class select_materialize_with_stats that collects data statistics about
  the data being inserted into the target table.
sql/sql_select.cc:
  - more complete initialization of the TABLE object of a temp table.
  - call setup_subquery_materialization at one more exit point.

- More test results updates (checked)

- Update test results
- More comments
- Add Item_in_optimizer::transform() which was lost in backport

- Factor out subquery code into sql/opt_subselect.{h,cc}
- Stop using the term "confluent" (was used due to misreading the dictionary)

- Variant #3 of the fix. It also
 = Unifies code with table elimination's 
 = is able to handle FROM-subquery pullout.

- Fix valgrind failure: do initialize Item::is_expensive_cache.

XOR conditions are not optimized, and Item_cond_xor therefore
acts like type Func_item even though it inherits from Item_cond.
A subtle difference between Item_func and Item_cond is that
you can get the children Items from the former by calling
arguments(), and from the latter by calling argument_list().
However, since Item_cond_xor inherits from Item_cond,
arguments() did not return any Items.

The fact that Item_cond_xor::arguments() did not return it's
children items lead to a problem for make_cond_for_index();
the method accepted that XOR items on unindexed columns were
pushed using ICP. ICP evaluation of non-indexed columns
does not (and should not) work.

The fix for this bug is to make Item_cond_xor return it's
children items when the arguments() method is used. This makes
Item_cond_xor behave more like Item_func and in turn allows
make_cond_for_index() to discover any conflicting children
Items.

This is a temporary fix and should be removed when Item_cond_xor
 is optimized.

process temporary table overflow correctly.

- Backport into Maria DB 5.3, part 1