If the duplicate elimination strategy is used for a semi-join and potentially
one of the block-based join algorithms can be employed to join the inner
tables of the semi-join then sorting of the head (first non-constant) table
for a query with ORDER BY / GROUP BY cannot be used.

The function setup_sj_materialization_part1() forgot to set the value
of TABLE::map for any materialized IN subquery. 
This could lead to wrong results for queries with subqueries that were
converted to queries with semijoins.

        Coinciding nodes can appear as a result of DOUBLE inaccuracy.
        We should test that before we start the loop.

        Also the spatial relations can be calculated faster if we check
        MBR relations first. And we do have the shape's MBR-s now.

per-file comments:
  sql/gcalc_slicescan.cc
        set_extent() method added.
bug #901655 ST_BUFFER asserts with a coplicated shape.
  sql/gcalc_slicescan.h
        set_extent() method declared.
bug #901655 ST_BUFFER asserts with a coplicated shape.
  sql/gcalc_tools.cc
bug #901655 ST_BUFFER asserts with a coplicated shape.
        checks for equal nodes added.
  sql/item_geofunc.cc
bug #901655 ST_BUFFER asserts with a coplicated shape.
        MBR for the shapes calculated, and MBR checks added before we
        start the heavy calculations.
  sql/spatial.h
bug #901655 ST_BUFFER asserts with a coplicated shape.
        MBR::buffer() method implemented.

- if we're considering FirstMatch access with one inner table, and 
  @@optimizer_switch has semijoin_with_cache flag, calculate costs
  as if we used join cache (because we will be able to do so)

- opt_sum_query() should not assume that join tables from sj-materialization
  have known numbers of rows.

- Part2: safety and code cleanup

The execution plan cannot use sorting on the first table from the
sequence of the joined tables if it plans to employ the block-based
hash join algorithm.

- Part 1 of the fix: for semi-join merged subqueries, calling child_join->optimize() until we're done with all
  PS-lifetime optimizations in the parent.

The optimizer must ignore any possible hash join key when looking for the 
query execution plan with join_cache_level set to 0.  

- Make create_tmp_table() set KEY_PART_INFO attributes for the keys it creates.
  This wasn't needed before but is needed now, when temp. tables that are 
  results of SJ-Materialization are being used for joins.
  This particular bug depended on HA_VAR_LENGTH_PART being set,
  but also added code to set HA_BLOB_PART and HA_NULL_PART when appropriate.

KEYUSE elements for a possible hash join key are not sorted by field
numbers of the second table T of the hash join operation. Besides
some of these KEYUSE elements cannot be used to build any key as their
key expressions depend on the tables that are planned to be accessed
after the table T. 
The code before the patch did not take this into account and, as a result,
execition of a query the employing block-based hash join algorithm could
cause a crash or return a wrong result set. 

in EXPLAIN as select_type==MATERIALIZED. 

Before, we had select_type==SUBQUERY and it was difficult to tell materialized
subqueries from uncorrelated scalar-context subqueries.

If has been decided that the first match strategy is to be used to join table T
from a semi-join nest while no buffer can be employed to join this table
then no join buffer can be used to join any table in the join sequence between
the first one belonging to the semi-join nest and table T.

 

Added some file to ignore

Increased number of locks in thr_lock (used only when testing)


include/my_global.h:
  Patch for CYGWIN
mysys/my_getsystime.c:
  Patch for CYGWIN
mysys/thr_lock.c:
  Increase number of locks for testing

The tables from the same semi-join or outer join nest cannot use
join buffers if in the join sequence of the query execution plan
they are separated by a table that is planned to be joined without
usage of a join buffer.

The cause of the wrong result was that Item_ref_null_helper::get_date()
didn't use a method of the *_result() family, and fetched the data
for the field from the current row instead of result_field. Changed to
use the correct *_result() method, like to all other similar methods
of Item_ref_null_helper.

        DISJOINT can't be properly optimized with the RTree keys in MyISAM also.

per-file comments:
  storage/myisam/rt_index.c
bug 857066 Wrong result with ST_DISJOINT when using an index.
        don't optimize DISJOINT with the RTree keys.

        the ST_DISJOINT can't be properly optimized with the RTree key
        at the moment.

per-file comments:
  storage/maria/ma_rt_index.c
bug 857066 Wrong result with ST_DISJOINT when using an index
        disabled optimization for the DISJOINT case.

Analysis:
lp:894397 was a consequence of a prior incorrect fix of lp:833777
which didn't take into account that even when all tables are
constant there may be correlated conditions, and the where clause
is not equivalent to the constant conditions.

Solution:
When there are constant tables only, evaluate only the conditions
that reference outer fields, because the constant conditions are
already checked, and the where clause doesn't have other conditions
than constant ones, and outer referencing ones. The fix for
lp:894397 also fixes lp:833777.

The problem was that when we have single row subquery with no rows
Item_cache(es) which represent result row was not null and being
requested via element_index() returned random value.

The fix is setting all Item_cache(es) in NULL before executing the
query (reset() method) which guaranty NULL value of whole query
or its elements requested in any way if no rows was found.

set_null() method was added to Item_cache to guaranty correct NULL
value in case of reseting the cache.

and 'derived_with_keys'. Now they are set on by default.

- Make functions that operate on SJ_TMP_TABLE be member functions
- Make Loose_scan_opt data members private

- Make EXPLAIN display "Start temporary" at the start of the fanout (it used to display
  at the first table whose rowid gets into temp. table which is not that useful for
  the user)
- Updated test results (all checked)

http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=577135