Problem was in try to check/use Item_direct_ref of derived view when we have to use real Item_field under it.

- If LooseScan is used with quick select, require that quick select produces 
  data in key order (this disables use of MRR, which can return data in arbitrary order).

Applied the fix for bug #12546542 from the mysql-5.6 code line:
JOIN_CACHE::join_records forgot to reset JOIN_TAB::first_unmatched
in some cases.

not be reproduced in the latest release of mariadb-5.3 as it was was fixed
by Sergey Petrunia when working on the problems concerning outer joins within
in subqueries converted to semi-joins.

BUG#922254: Assertion `0' failed at item_cmpfunc.cc:5899: Item* Item_equal::get_first(JOIN_TAB*, Item*)
Fixed Item* Item_equal::get_first(JOIN_TAB *context, Item *field_item) to work correctly in the case where:
- context!= NO_PARTICULAR_TAB, it points to a table within SJ-Materialization nest
- field_item points to an item_equal that has a constant Item_field but does not have any fields
  from tables that are within semi-join nests.

QUERY_CACHE_DB_LENGTH_SIZE 0, just like it is done with
QUERY_CACHE_FLAGS_SIZE.

- Disable use of join cache when we're using FirstMatch strategy, and the join
  order is such that subquery's inner tables are interleaved with outer.  Join 
  buffering code is incapable of handling such join orders.

- The testcase requires use of @@debug_optimizer_prefer_join_prefix to hit the bug, 
  but I'm pushing it anyway (including the mention of the variable in .test file), 
  so that it can be found and enabled when/if we get something comparable in the 
  main tree.

The problem was that LooseScan execution code assumed that tab->key holds 
the index used for looseScan. This is only true when range or full index
scan are used. In case of ref access, the index is in tab->ref.key (and 
tab->index==0 which explains how LooseScan passed tests with ref access: they 
used one index)

Fixed by setting/using loosescan_key, which always the correct index#.

 Handler_mrr_extra_key_sorts.

of mysql-5.6 code line. The bugs could not be reproduced in the latest release
of mariadb-5.3 as they were fixed either when the code of subquery optimization
was back-ported from mysql-6.0 or later when some other bugs were fixed.

of mysql-5.6 code line. The bugs could not be reproduced in the latest release
of mariadb-5.3 as they were fixed either when the code of subquery optimization
was back-ported from mysql-6.0 or later when some other bugs were fixed.

- equality substitution code was geared towards processing WHERE/ON clauses.
  that is, it assumed that it was doing substitions on the code that 
   = wasn't attached to any particular join_tab yet
   = was going to be fed to make_join_select() which would take the condition
     apart and attach various parts of it to tables inside/outside semi-joins.
- However, somebody added equality substition for ref access. That is, if 
  we have a ref access on TBL.key=expr, they would do equality substition in
  'expr'. This possibility wasn't accounted for.
- Fixed equality substition code by adding a mode that does equality 
  substition under assumption that the processed expression will be 
  attached to a certain particular table TBL.

- setup_semijoin_dups_elimination() would incorrectly set join_tab->do_firstmatch 
  when the join order had outer tables interleaved with inner.

If the expression for a derived table of a query contained a LIMIT
clause the estimate of the number of rows in this derived table
returned by the EXPLAIN command could be badly off since the
optimizer ignored the limit number from the LIMIT clause when
getting the estimate. 
The call of the method SELECT_LEX_UNIT->set_limit added in the code
of mysql_derived_optimize() will be needed also in maria-5.5 where
parameters in the LIMIT clause are supported. 

handler counter Handler_read_rnd_deleted.

Removed unused code merged from 5.2. In 5.3 we fix this problem by checking if we put max/min group function
without GROUP BY artificially in case of MODE_ONLY_FULL_GROUP_BY sql mode.

Fix for LP BUG#908269 Wrong result with subquery in select list, EXISTS, constant MyISAM/Aria table.

Problem: When building the condition for JOIN::outer_ref_cond the optimizer forgot to take into account
that this condition could depend on constant tables as well.

mysql-test/r/information_schema_all_engines.result:
  Update result
mysql-test/t/information_schema_all_engines.test:
  Added --sorted-results as tables in information_schema are not sorted.

Split IO threads into ones that handle only read completion and ones that handle only write completion, as it was originally done, but got lost with "completion port" patch. The reason we need to have dedicated read and dedicated write threads is that read completion routine can block waiting for write io to complete, and in rare cases where all io threads are handling async reads, it can deadlock.

- Create/use do_copy_nullable_row_to_notnull() function for ref access, which is used 
  when copying from not-NULL field in table that can be NULL-complemented to not-NULL field.

The patch for bug 685411 erroneously removed a call of engine->set_thd()
from Item_subselect::fix_fields().