in greedy_search with LEFT JOINs and unique keys
- Backport the fix for BUG#806524 from MariaDB 5.3

The not_null_tables() of Item_func_not_all and Item_in_optimizer was inherited from
Item_func by mistake. It made the optimizer think that  subquery
predicates with ALL/ANY/IN were null-rejecting. This could trigger invalid
conversions of outer joins into inner joins.

It is problem of constant propagated to ref* access method (the problem was hiden by using debug binaries for testing).

The failures are  missing entries in the slow query log.  The reason for the failure  are sleep() calls  with short duration 10ms, which is less than the default system timer resolution for various WaitForXXXObject functions  (15.6 ms) and thus can't work reliably.
The fix is to make sleeps tiny bit longer (20ms from 10ms) in the test.

let x = `SELECT <something>`

The fix is to detect the condition "no active connection",  to report error and die.
Note, that the check for no active connection was already in place for ordinary commands, 
and was missing only for assign-variable command.

In 5.3 we substitute constants in ref access values it can't be null so we do not need add NOT NULL for early NULL filtering.

Optimization of aggregate functions detected constant under max() and evalueted it, but condition in the WHWRE clause (which is always FALSE) was not taken into account

The patch backports two patches from mysql 5.6:
- BUG#12640437: USING SQL_BUFFER_RESULT RESULTS IN A DIFFERENT QUERY OUTPUT
- Bug#12578908: SELECT SQL_BUFFER_RESULT OUTPUTS TOO MANY ROWS WHEN GROUP IS OPTIMIZED AWAY

Original comment:
-----------------
3714 Jorgen Loland	2012-03-01
      BUG#12640437 - USING SQL_BUFFER_RESULT RESULTS IN A DIFFERENT 
                     QUERY OUTPUT
      
      For all but simple grouped queries, temporary tables are used to
      resolve grouping. In these cases, the list of grouping fields is
      stored in the temporary table and grouping is resolved
      there (e.g. by adding a unique constraint on the involved
      fields). Because of this, grouping is already done when the rows
      are read from the temporary table.
      
      In the case where a group clause may be optimized away, grouping
      does not have to be resolved using a temporary table. However, if
      a temporary table is explicitly requested (e.g. because the
      SQL_BUFFER_RESULT hint is used, or the statement is
      INSERT...SELECT), a temporary table is used anyway. In this case,
      the temporary table is created with an empty group list (because
      the group clause was optimized away) and it will therefore not
      create groups. Since the temporary table does not take care of
      grouping, JOIN::group shall not be set to false in 
      make_simple_join(). This was fixed in bug 12578908. 
      
      However, there is an exception where make_simple_join() should
      set JOIN::group to false even if the query uses a temporary table
      that was explicitly requested but is not strictly needed. That
      exception is if the loose index scan access method (explain
      says "Using index for group-by") is used to read into the 
      temporary table. With loose index scan, grouping is resolved 
      by the access method. This is exactly what happens in this bug.

This is a backport of the fix for MySQL bug #13723054 in 5.6.

Original comment:
      The crash is caused by arbitrary memory area owerwriting in case of
      BLOB fields during attempt to copy BLOB field key image into record
      buffer(record buffer is too small to get BLOB key part image).
      note:
      QUICK_GROUP_MIN_MAX_SELECT can not work with BLOB fields
      because it uses record buffer as temporary buffer for key values
      however this case is filtered out by covering_keys() check
      in get_best_group_min_max() as BLOBs always require key length
      modificator in the key declaration and if the key has a BLOB
      then it can not be covered key.
      The fix is to use 'max_used_key_length' key length instead of 0.

Analysis:
Spcifically the crash in this bug was a result of the call to key_copy()
that copied the whole key, inlcuding the BLOB field which is not used
for index access. Copying the blob field overwrote memory as far as the
function parameter 'key_info'. As a result the contents of key_info was
all 0, which resulted in a crash when this key_info was accessed few
lines below in key_cmp().

MDEV-214 lp:967242 Wrong result with JOIN, AND in ON condition, multi-part key, GROUP BY, subquery and OR in WHERE

The problem was in the code (update_const_equal_items()) which marked
index parts constant independently of the place where the equality was used.
In the test suite it marked t2_1.c part constant despite the fact that
it connected by OR with other expression.

Solution is to mark constant only top equalities connected with AND.

Fix is to set  maybe_null  flag for Item_func_last_day.

unnecessary date->string->date conversion

Create an Item_cache based on item's cmp_type, not result_type in 
subselect_engine.

Use result_field in Item_cache_temporal::cache_value(),
just like all other Item_cache*::cache_value() do.

  Points and lines should disappear if we got negative D.
  To make it work properly inside the GEOMETRYCOLLECTION,
  we add the empty operation there.

bug #986977 Assertion `!cur_p->event' failed in Gcalc_scan_iterator::arrange_event(int, int).
  The double->inernal coord conversion produced -0 (minus zero) on some data.
  That minus-zero produces invalid comparison results when compared agains plus-zero.
  So we fixed the gcalc_set_double() to avoid it.

per-file comments:
  mysql-test/r/gis-precise.result
        result updated.
  mysql-test/t/gis-precise.test
        tests for #977021 and #986977 added.
  sql/gcalc_slicescan.cc
        bug #986977. The gcalc_set_double fixed to not produce minus-zero.
  sql/item_geofunc.cc
        bug #977021. Add the NOOP for the disappearing features.

MDEV-216 lp:976104 - Assertion `0' failed in my_message_sql on UPDATE IGNORE, or unknown error on release build

Don't send_error at the end of mysql_multi_update() if select failed.
The error, if there was any, was already sent by mysql_select

Analysis:

The reason for the wrong result is the interaction between constant
optimization (in this case 1-row table) and subquery optimization.

- First the outer query is optimized, and 'make_join_statistics' finds that
table t2 has one row, reads that row, and marks the whole table as constant.
This also means that all fields of t2 are constant.

- Next, we optimize the subquery in the end of the outer 'make_join_statistics'.
The field 'f2' is considered constant, with value '3'. The subquery predicate
is rewritten as the constant TRUE.

- The outer query execution detects early that the whole query result is empty
and calls 'return_zero_rows'. Since the query is with implicit grouping, we
have to produce one row with special values for the aggregates (depending on
each aggregate function), and NULL values for all non-aggregate fields.  This
function calls 'no_rows_in_result' to set each aggregate function to the
default value when it aggregates over an empty result, and then calls
'send_data', which in turn evaluates each Item in the SELECT list.

- When evaluation reaches the subquery predicate, it executes the subquery
with field 'f2' having a constant value '3', and the subquery produces the
incorrect result '7'.

Solution:

Implement Item::no_rows_in_result for all subquery predicates. In order to
make this work, it is also needed to make all val_* methods of all subquery
predicates respect the Item_subselect::forced_const flag. Otherwise subqueries
are executed anyways, and override the default value set by no_rows_in_result
with whatever result is produced from the subquery evaluation.

remove a redundant line in Makefile.am

install all private headers in mysql/private/

As part of derived tables redesign, values for VIEW_ALGORITHM_MERGE and VIEW_ALGORITHM_TMPTABLE have changed from (former values 1 rsp 2 , new values 5 rsp 9).

This lead to the problem that views, created with version 5.2  or earlier would not work in all situations  (e.g "SHOW CREATE VIEW"), or with mysqldump.

The fix is to restore backward compatibility for the from file, and convert algorithm={1,2} in the frm to {5,9} when reading .frm from disk, and store backward compatible values when writing from to disk. 

Also allow processing correct processing for "invalid" .frms created with MariaDB 5.3/5.5 GA releases (where algorithm stored in memory matched the one stored in frm).

Fixed incorrect type casting which made all fields (except very first) changes to materialized table incorrect.
Saved list of view/derived table used items after expanding '*'.

BUG#978479: Wrong result (extra rows) with derived_with_keys+loosescan+semijoin=ON, materialization=OFF
- Part#2: Don't try to construct a LooseScan access on indexes that do not guarantee 
  index-ordered reads.

BUG#978479: Wrong result (extra rows) with derived_with_keys+loosescan+semijoin=ON, materialization=OFF
Part#1: make EXPLAIN's plan match the one by actual execution: 
Item_subselect::used_tables() should return the same value irrespectively 
of whether we're running an EXPLAIN or a SELECT.

(for example, one of them sets client capabilities by copying server capabilities)

We cannot fix them - let's tolerate them

This can result in bad deadlocks (e.g loader lock), seen in latest crash reports.