Analysis:

When the method JOIN::choose_subquery_plan() decided to apply
the IN-TO-EXISTS strategy, it set the unit and select_lex
uncacheable flag to UNCACHEABLE_DEPENDENT_INJECTED unconditionally.
As result, even if IN-TO-EXISTS injected non-correlated predicates,
the subquery was still treated as correlated.

Solution:
Set the subquery as correlated only if the injected predicate(s) depend
on the outer query.

remove the offending assert.
take the test case from mysql Bug#58015

backport dmitry.shulga@oracle.com-20120209125742-w7hdxv0103ymb8ko from mysql-trunk:

  Patch for bug#11764747 (formerly known as 57612): SET GLOBAL READ_ONLY=1 cannot
  progress when a table is locked with LOCK TABLES.
  
  The reason for the bug was that mysql server makes a flush of all open tables
  during handling of statement 'SET GLOBAL READ_ONLY=1'. Therefore if some of
  these tables were locked by "LOCK TABLE ... READ" from a different connection,
  then execution of statement 'SET GLOBAL READ_ONLY=1' would be waiting for
  the lock for such table even if the table was locked in a compatible read mode.
  
  Flushing of all open tables before setting of read_only system variable
  is inherited from 5.1 implementation since this was the only possible approach
  to ensure that there isn't any pending write operations on open tables.
  
  Start from version 5.5 and above such behaviour is guaranteed by the fact
  that we acquire global_read_lock before setting read_only flag. Since
  acquiring of global_read_lock is successful only when there isn't any 
  active write operation then we can remove flushing of open tables from
  processing of SET GLOBAL READ_ONLY=1.
  
  This modification changes the server behavior so that read locks held
  by other connections (LOCK TABLE ... READ) no longer will block attempts
  to enable read_only.

Take only test cases from MDEV-136 Non-blocking "set read_only"

Analysis:
The crash is a result of Item_cache_temporal::example not being set
(it is NULL). It turns out that the value of Item_cache_temporal
may be set directly by calling Item_cache_temporal::store_packed
without ever setting the "example" of this Item_cache. Therefore
the failing assertion is too narrow.

Solution:
Remove the assert.
In principle we could overwrite this method for Item_cache_temporal,
but it doesn't make sense just for this assert.

- Set index columns to be read when using index_merge, even if TABLE->no_keyread is 
  set for the table (happens for multi-table UPDATEs)

The constructor for Query_log_event allocated 2 bytes too few for
extra space needed by Query cache. (Not sure if this is reproducible
in practice, as there are often a couple of extra bytes allocated
for unused string zero terminators, but better safe than sorry).

CHEAP SQ: Valgrind warnings "Memory lost" with IN and EXISTS nested subquery, materialization+semijoin

Analysis:
The memory leak was a result of the interaction of semi-join optimization
with early optimization of constant subqueries. The function:
setup_jtbm_semi_joins() created a dummy temporary table "dummy_table"
in order to make some JOIN_TAB objects complete. Normally, such temporary
tables are freed inside JOIN_TAB::cleanup.

However, the inner-most subquery is pre-optimized, which allows the
optimization fo the MAX subquery to determine that its WHERE is TRUE,
and thus to compute the result of the MAX during optimization. This
ultimately allows the optimize phase of the outer query to find that
it WHERE clause is FALSE. Once JOIN::optimize finds that the result
set is empty, it sets zero_result_cause, and returns *before* it ever
reached make_join_statistics(). As a result the query plan has no
JOIN_TABs at all. Since the temporary table is supposed to be cleanup
via JOIN_TAB::cleanup, this never happens because there is no JOIN_TAB
for this table. Hence we get a memory leak.

Solution:
Whenever there are no JOIN_TABs, iterate over all table reference in
JOIN::join_list, and free the ones that contain semi-join temporary
tables.

Analysis:

When a subquery that needs a temp table is executed during
the prepare or optimize phase of the outer query, at the end
of the subquery execution all the JOIN_TABs of the subquery
are replaced by a new JOIN_TAB that selects from the temp table.
However that temp table has no corresponding TABLE_LIST.
Once EXPLAIN execution reaches its last phase, it tries to print
the names of the subquery tables through its TABLE_LISTs, but in
the case of this bug there is no such TABLE_LIST (it is NULL),
hence a crash.

Solution:
The fix is to block subquery evaluation inside
Item_func_like::fix_fields and Item_func_like::select_optimize()
using the Item::is_expensive() test.

Patch for mdev-287: CHEAP SQ: A query with subquery in SELECT list, EXISTS, inner joins takes hundreds times longer

Analysis:

The fix for lp:944706 introduces early subquery optimization.
While a subquery is being optimized some of its predicates may be
removed. In the test case, the EXISTS subquery is constant, and is
evaluated to TRUE. As a result the whole OR is TRUE, and thus the
correlated condition "b = alias1.b" is optimized away. The subquery
becomes non-correlated.

The subquery cache is designed to work only for correlated subqueries.
If constant subquery optimization is disallowed, then the constant
subquery is not evaluated, the subquery remains correlated, and its
execution is cached. As a result execution is fast.

However, when the constant subquery was optimized away, it was neither
cached by the subquery cache, nor it was cached by the internal subquery
caching. The latter was due to the fact that the subquery still appeared
as correlated to the subselect_XYZ_engine::exec methods, and they
re-executed the subquery on each call to Item_subselect::exec.

Solution:

The solution is to update the correlated status of the subquery after it has
been optimized. This status consists of:
- st_select_lex::is_correlated
- Item_subselect::is_correlated
- SELECT_LEX::uncacheable
- SELECT_LEX_UNIT::uncacheable
The status is updated by st_select_lex::update_correlated_cache(), and its
caller st_select_lex::optimize_unflattened_subqueries. The solution relies
on the fact that the optimizer already called
st_select_lex::update_used_tables() for each subquery. This allows to
efficiently update the correlated status of each subquery without walking
the whole subquery tree.

Notice that his patch is an improvement over MySQL 5.6 and older, where
subqueries are not pre-optimized, and the above analysis is not possible.

Otherwise yum on fedora will not install them
(rpm will, yum on centos and rhel will).

Build MariaDB-compat rpm by repackaging files from MariaDB-shared-5.3.*.rpm
Or RHEL6/CentOS6 make all other MariaDB rpms depend on MariaDB-compat.

        Optimizator fails using index with ST_Within(g, constant_poly).

per-file comments:
  mysql-test/r/gis-rt-precise.result
        test result fixed.
  mysql-test/r/gis-rtree.result
        test result fixed.
  mysql-test/suite/maria/r/maria-gis-rtree-dynamic.result
        test result fixed.
  mysql-test/suite/maria/r/maria-gis-rtree-trans.result
        test result fixed.
  mysql-test/suite/maria/r/maria-gis-rtree.result
        test result fixed.
  storage/maria/ma_rt_index.c
        Use MBR_INTERSECT mode when optimizing the select WITH ST_Within.
  storage/myisam/rt_index.c
        Use MBR_INTERSECT mode when optimizing the select WITH ST_Within.

but restart it on upgrade, if it was already running

use my.cnf includes to split one big my.cnf file in server and client parts.

remove "Provides: mysql-libs" (doesn't help on CentOS 6)

could lead an to exponential growth of the imerge lists.

- In JOIN::exec(), make the having->update_used_tables() call before we've
  made the JOIN::cleanup(full=true) call. The latter frees SJ-Materialization
  structures, which correlated subquery predicate items attempt to walk afterwards.

Analysis:
The problem in the original MySQL bug is that the range optimizer
performs its analysis in a separate MEM_ROOT object that is freed
after the range optimzier is done. During range analysis get_mm_tree
calls Item_func_like::select_optimize, which in turn evaluates its
right argument. In the test case the right argument is a subquery.

In MySQL, subqueries are optimized lazyly, thus the call to val_str
triggers optimization for the subquery. All objects needed by the
subquery plan end up in the temporary MEM_ROOT used by the range
optimizer. When execution ends, the JOIN::cleanup process tries to
cleanup objects of the subquery plan, but all these objects are gone
with the temporary MEM_ROOT. The solution for MySQL is to switch the
mem_root.

In MariaDB with the patch for bug lp:944706, all constant subqueries
that may be used by the optimization process are preoptimized. Therefore
Item_func_like::select_optimize only triggers subquery execution, and
the above problem is not present.

The patch however adds a test whether the evaluated right argument of
the LIKE predicate is expensive. This is consistent with our approach
not to evaluate expensive expressions during optimization.

- Correct testcases.

- Disable IndexConditionPushdown for reverse scans.

  
  Analysis:
  The optimizer detects an empty result through constant table optimization.
  Then it calls return_zero_rows(), which in turns calls inderctly
  Item_maxmin_subselect::no_rows_in_result(). The latter method set "value=0",
  however "value" is pointer to Item_cache, and not just an integer value.
  
  All of the Item_[maxmin | singlerow]_subselect::val_XXX methods does:
    if (forced_const)
      return value->val_real();
  which of course crashes when value is a NULL pointer.
  
  Solution:
  When the optimizer discovers an empty result set, set
  Item_singlerow_subselect::value to a FALSE constant Item instead of NULL.

configure with cmake -DRPM=distro

    Handle the 'set read_only=1' in lighter way, than the FLUSH TABLES READ LOCK;
    For the transactional engines we don't wait for operations on that tables to finish.

per-file comments:
 mysql-test/r/read_only_innodb.result
MDEV-136 Non-blocking "set read_only".
       test result updated.
 mysql-test/t/read_only_innodb.test
MDEV-136 Non-blocking "set read_only".
       test case added.
  sql/mysql_priv.h
MDEV-136 Non-blocking "set read_only".
        The close_cached_tables_set_readonly() declared.
  sql/set_var.cc
MDEV-136 Non-blocking "set read_only".
         Call close_cached_tables_set_readonly() for the read_only::set_var.
   sql/sql_base.cc
 MDEV-136 Non-blocking "set read_only".
         Parameters added to the close_cached_tables implementation,
         close_cached_tables_set_readonly declared.
         Prevent blocking on the transactional tables if the
         set_readonly_mode is on.

BUG#1000269: Wrong result (extra rows) with semijoin+materialization, IN subqueries, join_cache_level>0
- make make_cond_after_sjm() correctly handle OR clauses where one branch refers to the semi-join table
  while the other branch refers to the non-semijoin table.

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.

sql/slave.cc:
  add mutex protection, like in sql_parse.cc

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.