Analysis:
JOIN::optimize performs constant optimization of GROUP by clauses
by calling remove_const():
    group_list= remove_const(this, (old_group_list= group_list), conds,
                             rollup.state == ROLLUP::STATE_NONE,
			     &simple_group);
If it turns out that a GROUP clause references a field that is
computed by a single-row subquery, then the said optimization
performs premature execution of the subquery referenced by the
group clause.

Solution:
Block the evaluation of subqueries similarly to the approach
for the WHERE and JOIN..ON clauses.

Analysis:
Single-row subqueries are not considered expensive and are
evaluated both during EXPLAIN in to detect errors like
"Subquery returns more than 1 row", and during optimization to
perform constant optimization.

The cause for the failed ASSERT is in JOIN::join_free, where we set
  bool full= (!select_lex->uncacheable && !thd->lex->describe);
Thus for EXPLAIN statements full == FALSE, and as a result the call to
JOIN::cleanup doesn't call JOIN_TAB::cleanup which should have
called table->disable_keyread().

Solution:
Consider all kinds of subquery predicates as expensive.

Analysis:
This another instance of the problem fixed in LP BUG#675981 -
evaluation of subqueries during EXPLAIN when the subquery plan
is incomplete because JOIN::optimize() generally doesn't create
complete execution plans for EXPLAIN statements.

In this case the call path is:
mysql_explain_union -> outer_join.exec -> outer_join.init_execution ->
create_sort_index -> filesort -> find_all_keys ->
SQL_SELECT::skip_record -> outer_where_clause.val_int -> ...
-> subselect_join.exec -> ... -> sub_select_cache

When calling sub_select_cache JOIN_TAB::cache is NULL because the cache
objects are not created for EXPLAIN statements.

Solution:
Delay the call to init_execution() after all EXPLAIN related processing
is completed. Thus init_execution() is not called at all during EXPLAIN.

Cause:
The optimize() phase for the subquery selected to use join buffering via setting
JOIN_TAB::next_select= sub_select_cache in make_join_readinfo, however, the call
to check_join_cache_usage() from make_join_readinfo didn't create the corresponding
JOIN_CACHE_BNL object because of the condition:
    if ((options & SELECT_DESCRIBE) ||
        (((tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache))) &&
         !tab->cache->init()))
Since EXPLAIN for subqueries runs regular execution, the constant predicates that
were delayed to be evaluated at the exec() phase, were evaluated during EXPLAIN.
As a result the outer JOIN::exec called JOIN::exec for the subquery, while the
subquery execution plan was no properly created, which resulted in an failed ASSERT.

Fix:
The patch blocks evaluation of constant expensive conditions during EXPLAIN. Notice
that these conditions are "constant" with respect to the outer query, thus in
general they could be arbitrarily expensive, which may result in very slow EXPLAINs.

This is a backport of the fix for
MySQL BUG#52317: Assertion failing in Field_varstring::store () at field.cc:6833

The orginal comment by Oystein is:

In order for EXPLAIN to print const-refs, a Store_key_const_item object
is created. This is different for normal execution of subqueries where
a temporary store_key_item object is used instead. The problem is that
EXPLAIN will execute subqueries.  This leads to a scenario where a
store_key_const_item object it told to write to its underlying field.
This results in a failing assert since the write set of the underlying
table does not reflect this.  

The resolution is to do the same trick as for store_key_item::copy_inner().
That is, temporarily change the write set to allow writes to all columns.
This is only necessary in debug version since non-debug version does not
contain asserts on write_set.

sql/sql_select.h:
  Temporarily change write_set in store_key_const_item::copy_inner() to
  allow initialization of underlying field.  This is necessary since 
  subqueries are executed for EXPLAIN.  (For normal execution, 
  store_key_item::copy_inner is used.)

  anticipate different execution paths resulting in different
  thd->proc_info.
- Fixed subselect_cache to contain correct results. The results
  are currently wrong in 5.3, but are correct in 5.2, and 5.3-mwl89.

The cause for the bug was two-fold:
1. Incorrect detection of whether a table is the first one in a query plan -
  "used_table & 1" actually checks if used_table is table with number "1".
2. Missing logic to delay the evaluation of (expensive) constant conditions
  during the execution phase.

The fix adds/changes:
The patch:
- removes incorrect treatment of expensive predicates from make_cond_for_table,
  and lets the caller decide when to evaluate expensive predicates.
- saves expensive constant conditions in JOIN::exec_const_cond,
  which is evaluated once in the beginning of JOIN::exec.

The bug itself got fixed after merging with the main 5.3.

Merge 5.3-mwl89 into 5.3 main.

There is one remaining test failure in this merge:
innodb_mysql_lock2. All other tests have been checked to
deliver the same results/explains as 5.3-mwl89, including
the few remaining wrong results.

This bug in the MRR code for Maria engine caused wrong results when
MRR was used to scan ranges for each record.

Added test cases for bugs 669420 and 669423 (as a duplicate of 669420).

The fixes for #643424 was part of the fix for #652727, that's why both
fixes are pushed together.

- The cause for #643424 was the improper use of get_partial_join_cost(),
  which assumed that the 'n_tables' parameter was the upper bound for
  query plan node indexes.
  Fixed by generalizing get_partial_join_cost() as a method that computes
  the cost of any partial join.

- The cause of #652727 was that JOIN::choose_subquery_plan() incorrectly
  deleted the contents of the old keyuse array in the cases when an injected
  plan would not provide more key accesses, and reoptimization was not actually
  performed.

Fixed compiler warnings.

include/queues.h:
  Added prototype for queue_replace()
mysys/queues.c:
  Fixed wrong queue_replace()
mysys/thr_alarm.c:
  Added DBUG_PRINT
sql/item_subselect.cc:
  Check return value of ha_rnd_init().
  (Fixes compiler warnings)
sql/sql_class.cc:
  Fixed wrong test
sql/sql_show.cc:
  Removed not used variable.

don't set view_prepare_mode when opening a base table
(either in SHOW CREATE or in I_S.TABLES)

The bug could cause wrong results for queries over Maria tables when
index condition pushdown was used.

Also fix missing zero termination in DBUG_PRINT, causing garbage output
in --debug output.

The set of Ordered keys of a rowid merge engine is dense. Thus when
we decide not to create a key for a column that has only NULLs, this
column shouldn't be counted.

Notice that the caller has already precomputed the correct total
number of keys that should be created.

Post-review fix - avoid re-evaluation of the having clause
when it evaluates to true.

The main.ps_ddl test does SELECT * FROM mysql.general_log; that can be really
expensive with --valgrind if previous test cases put lots of data in the
general log since last server restart. Fix by truncating the log at test start.

sql/sql_expression_cache.cc:
  Type of the variable fixed.
sql/sql_expression_cache.h:
  Type of the variable fixed.

The cause for this bug is that MariaDB 5.3 still processes derived tables
(subqueries in the FROM clause) by fully executing them during the parse
phase. This will be remedied by MWL#106 once merged into the main 5.3.

The assert statement is triggered when MATERIALIZATION is ON for EXPLAIN
EXTENDED for derived tables with an IN subquery as follows:
- mysql_parse calls JOIN::exec for the derived table as if it is regular
  execution (not explain).
- When materialization is ON, this call goes all the way to
  subselect_hash_sj_engine::exec, which creates a partial match engine
  because of NULL presence.
- In order to proceed with normal execution, the hash_sj engine substitutes
  itself with the created partial match engine.
- After the parse phase it turns out that this execution was part of
  EXPLAIN EXTENDED, which in turn calls
  Item_cond::print -> ... -> Item_subselect::print,
  which calls engine->print().
  Since subselect_hash_sj_engine::exec substituted the current
  Item_subselect engine with a  partial match engine, eventually we call
  its ::print() method. However the partial match engines are designed only
  for execution, hence there is no implementation of this print() method.

The fix temporarily removes the assert, until this code is merged with
MWL#106.

The bug was a result of missing logic to handle the case
when there are 'expensive' predicates that are not evaluated
during constant table optimization. Such is the case for
the IN predicate, which is considered expensive if it is
computed via materialization. In general this bug can be
triggered with any expensive predicate instead of IN.

When FALSE constant predicates are not evaluated during constant
optimization, the execution path changes so that instead of
setting JOIN::zero_result_cause after make_join_select, and
exiting JOIN::exec via the call to return_zero_rows(), execution
ends in JOIN::exec in the branch:
if (join->tables == join->const_tables)
{
  ...
  else if (join->send_row_on_empty_set())
     ...
     rc= join->result->send_data(*columns_list);
}
Unlike return_zero_rows(), this branch didn't evaluate the
having clause of the query.

The patch adds a call to evaluate the HAVING clause of a query even
when all tables are constant, because even for an empty result set
some aggregate functions may produce a NULL value.

bugfix: engine defined table options were not showing up in INFORMATION_SCHEMA.TABLES.CREATE_OPTIONS

Added missing logic to handle the case when subquery tables are optimized
away early during optimization.

- Added more tests to the MWL#89 specific test, and made the test more modular.
- Updated test files.
- Fixed a memory leak.
- More comments.

mysql-test/r/subselect_mat.result:
  - Updated the test file to reflect the new optimizer switches related to
    materialized subquery execution.
  - Added one extra test to test all cases that expose BUG#40037 (this is an old bug from 5.x).
  - Updated the test result with correct results that expose BUG#40037.
mysql-test/t/subselect_mat.test:
  - Updated the test file to reflect the new optimizer switches related to
    materialized subquery execution.
  - Added one extra test to test all cases that expose BUG#40037 (this is an old bug from 5.x).
  - Updated the test result with correct results that expose BUG#40037.
sql/sql_select.cc:
  Fixed a memory leak reported by Valgrind.

BUG#26447 prefer a clustered key for an index scan, as secondary index is always slower
  ... which was fixed to cause
BUG#35850 queries take 50% longer
  ... and was reverted

and

BUG#39653 prefer a secondary index for an index scan, as clustered key is always slower
  ... which was fixed to cause
BUG#55656 mysqldump takes six days instead of half an hour
  ... and was amended with a special case workaround


sql/opt_range.cc:
  move get_index_only_read_time() into the handler class
sql/sql_select.cc:
  use cost not an index length when choosing a cheaper index