The cause for this bug is that the method JOIN::get_examined_rows iterates over all
JOIN_TABs of the join assuming they are just a sequence. In the query above, the
innermost subquery is merged into its parent query. When we call
JOIN::get_examined_rows for the second-level subquery, the iteration that
assumes sequential order of join tabs goes outside the join_tab array and calls
the method JOIN_TAB::get_examined_rows on uninitialized memory. 

The fix is to iterate over JOIN_TABs in a way that takes into account the nested
semi-join structure of JOIN_TABs. In particular iterate as select_describe.

The patch enables back constant subquery execution during
query optimization after it was disabled during the development
of MWL#89 (cost-based choice of IN-TO-EXISTS vs MATERIALIZATION).

The main idea is that constant subqueries are allowed to be executed
during optimization if their execution is not expensive.

The approach is as follows:
- Constant subqueries are recursively optimized in the beginning of
  JOIN::optimize of the outer query. This is done by the new method
  JOIN::optimize_constant_subqueries(). This is done so that the cost
  of executing these queries can be estimated.
- Optimization of the outer query proceeds normally. During this phase
  the optimizer may request execution of non-expensive constant subqueries.
  Each place where the optimizer may potentially execute an expensive
  expression is guarded with the predicate Item::is_expensive().
- The implementation of Item_subselect::is_expensive has been extended
  to use the number of examined rows (estimated by the optimizer) as a
  way to determine whether the subquery is expensive or not.
- The new system variable "expensive_subquery_limit" controls how many
  examined rows are considered to be not expensive. The default is 100.

In addition, multiple changes were needed to make this solution work
in the light of the changes made by MWL#89. These changes were needed
to fix various crashes and wrong results, and legacy bugs discovered
during development.

The code to re-enable checkpointing after UNLOCK TABLES was lost in the 5.5
merge, so re-add it back in.

5.5 version. for cmake, not autotools.

mtr should do it as well, to avoid differences in test output. 

This fixes sys_vars.secure_file_priv on FreeBSD9.0 buildbot.

is gone.

The problem was increment of aborted_threads variable due to thd->killed which was set when threadpool connection was terminated .  The fix is not to set thd->killed anymore, there is no real reason for doing it..

Added a test that checks that status variable aborted_clients does not grow for ordinary disconnects, and that successful KILL increments this variable.

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

  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.

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 '*'.