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

The patch also fixes an unrelated compiler warning.

Analysis:
The temporary table created during SJ-materialization
might be used for sorting for a group by operation. The
sort buffers for this internal temporary table were not
cleared by the execution code after each subquery
re-execution. This resulted in a memory leak detected
by valgrind.

Solution:
Cleanup the sort buffers for the semijon tables as well.


sql/item_subselect.cc:
  - Fix a compiler warning and add logic to revert to table
    scan partial match when there are more rows in the materialized
    subquery than there can be bits in the NULL bitmap index used
    for partial matching.
sql/opt_subselect.cc:
  - Fixed a memory leak detected by valgrind

Stop attempts to apply IN/ALL/ANY optimizations to so called "fake_select"
(used for ordering and filtering results of union) in union subquery execution.

per-file comments:
  mysql-test/t/gis-precise.test
        number-to-string conversion differs on Windows.
        Have to tolerate this while GIS data is stored in doubles.
  sql/spatial.cc
        prev_x initialization added.

Analysis:
The bug is a result of an incomplete fix for bug lp:869036.
That fix didn't take into account that there may be a case
when ther are no NULLs in the materialized subquery, however
all columns without NULLs may not be grouped in the only
non-null index. This is the case when the left subquery expression
has nullable columns.

Solution:
The patch handles two missing sub-cases of the case when there are
no value (non-null matches) for any outer expression, and there are
both NULLs and non-NUll values in the outer reference.
a) If the materialized subquery contains no NULLs there cannot be a
   partial match, because there are no NULLs in those columns where
   the outer reference has no NULLs.
b) If the materialized subquery contains NULLs, but there exists a
   column, such that its corresponding outer expression has no NULL,
   and this column also has no NULL. Then there cannot be a partial
   match either.

- Break down POSITION/advance_sj_state() into four classes 
  representing potential semi-join strategies.

- Treat all strategies uniformly (before, DuplicateWeedout 
  was special as it was the catch-all strategy. Now, we're 
  still relying on it to be the catch-all, but are able to 
  function,e.g. with firstmatch=on,duplicate_weedout=off.

- Update test results (checked)

So removed.

the depth of subquery nestedness to less than 31
(sizeof(ulong)-1).

This bug in the function Loose_scan_opt::check_ref_access_part1 could lead
to choosing an invalid execution plan employing a loose scan access to a
semi-join table even in the cases when such access could not be used at all.
This could result in wrong answers for some queries with IN subqueries.

Analysis:
The optimizer distinguishes two kinds of 'constant' conditions:
expensive ones, and non-expensive ones. The non-expensive conditions
are evaluated inside make_join_select(), and if false, already the
optimizer detects empty query results.

In order to avoid arbitrarily expensive optimization, the evaluation of
expensive constant conditions is delayed until execution. These conditions
are attached to JOIN::exec_const_cond and evaluated in the beginning of
JOIN::exec. The relevant execution logic is:

JOIN::exec()
{
  if (! join->exec_const_cond->val_int())
  {
    produce an empty result;
    stop execution
  }
  continue execution
  execute the original WHERE clause (that contains exec_const_cond)
 ...
}

As a result, when an expensive constant condition is
TRUE, it is evaluated twice - once through
JOIN::exec_const_cond, and once through JOIN::cond.
When the expensive constant condition is a subquery,
predicate, the subquery is evaluated twice. If we have
many levels of subqueries, this logic results in a chain
of recursive subquery executions that walk a perfect
binary tree. The result is that for subquries with depth N,
JOIN::exec is executed O(2^N) times.

Solution:
Notice that the second execution of the constant conditions
happens inside do_select(), in the branch:
if (join->table_count == join->const_tables) { ... }
In this case exec_const_cond is equivalent to the whole WHERE
clause, therefore the WHERE clause has already been checked in
the beginnig of JOIN::exec, and has been found to be true.
The bug is addressed by not evaluating the WHERE clause if there
was exec_const_conds, and it was TRUE.

set to 'on' by default.