The bug was caused by an incorrect code of the function
Item_direct_view_ref::replace_equal_field introduced in the
patch for bugs 717577, 724942. The function erroneously
returned the wrapped field instead of the Item_direct_view_ref
object itself in the cases when no replacement happened.

The bug masked two other minor bugs that could result in not
quite correct output of the EXPLAIN command for some queries.
They were fixed in the patch as well.
 

- Scavenged subquery tests from testcases other than t/subselect*.test and put them into single file 

- Set the default
- Adjust the testcases so that 'new' tests are run with optimizations turned on.
- Pull out relevant tests from "irrelevant" tests and run them with optimizations on.
- Run range.test and innodb.test with both mrr=on and mrr=off

The bug itself has been fixed by MWL#89.

The bug itself has been fixed by MWL#89.

The offending query returns a wrong result set because the optimizer
erroneously eliminated the where condition evaluated it to TRUE.
The cause of this wrong transformation was that the flag maybe_null
for an inner table of the outer join was not set to TRUE after the 
table had replaced the wrapping view.
Now the function SELECT_LEX::update_used_tables resets the value
of the maybe_null flag for each leaf table of the query after all
merges of views have been done.

 

The bug itself has been fixed by MWL#89.

The bug itself has been fixed by MWL#89.

Analysis:
This bug is yet another incarnation of the generic problem
where optimization of the outer query triggers evaluation
of a subquery, and this evaluation performs a destructive
change to the subquery plan. Specifically a temp table is
created for the DISTINCT operation that replaces the
original subquery table. Later, select_describe() attempts
to print the table name, however, there is no corresponding
TABLE_LIST object to the internal temp table, so we get a
crash. Execution works fine because it is not interested in
the corresponding TABLE_LIST object (or its name).

Solution:
Similar to other such bugs, block the evaluation of expensive
Items in convert_const_to_int().

The function generate_derived_keys_for_table incorrectly handled
the cases when a materialized view or derived table could be accessed
by different keys on the same fields if these keys depended on the
same tables.
  

Adjust PBXT test results.

- SELECT_LEX::merge_subquery should not set "(*in_subq)->emb_on_expr_nest= derived" for subqueries that 
  are in the ON expressions of semi-joins.

BUG#803365: Crash in pull_out_semijoin_tables with outer join + semijoin + derived tables in maria-5.3 with WL#106
- Don't perform table pullout out of semi-join nests that have nested outer joins.

The bug lp:798213 exposes a design flaw in --innodb-release-locks-early.
It does not work with InnoDB crash recovery, so it breaks transactional
integrety. So remove the feature.

  semijoin=on,firstmatch=on,loosescan=on
to
  semijoin=off,firstmatch=off,loosescan=off
Adjust the testcases:
- Modify subselect*.test and join_cache.test so that all tests
  use the same execution paths as before (i.e. optimizations that
  are being tested are enabled)
- Let all other test files run with the new default settings (i.e.
  with new optimizations disabled)
- Copy subquery testcases from these files into t/subselect_extra.test
  which will run them with new optimizations enabled.

Analysis:
This bug consists of two related problems that are
result of too early evaluation of single-row subqueries
during the optimization phase of the outer query.

Several optimizer code paths try to evaluate single-row
subqueries in order to produce a constant and use that
constant for further optimzation.

When the execution of the subquery peforms destructive
changes to the representation of the subquery, and these
changes are not anticipated by the subsequent optimization
phases of the outer query, we tipically get a crash or
failed assert.

Specifically, in this bug the inner-most suqbuery with
DISTINCT triggers a substitution of the original JOIN
object by a single-table JOIN object with a temp table
needed to perform the DISTINCT operation (created by
JOIN::make_simple_join).

This substitution breaks EXPLAIN because:
a) in the first example JOIN::cleanup no longer can
reach the original table of the innermost subquery, and
close all indexes, and
b) in this second test query, EXPLAIN attempts to print
the name of the internal temp table, and crashes because
the temp table has no name (NULL pointer instead).

Solution:
a) fully disable subquery evaluation during optimization
in all cases - both for constant propagation and range
optimization, and
b) change JOIN::join_free() to perform cleanup irrespective
of EXPLAIN or not.

- Fixed multi-user problem with one thread doing inserts and another doing scans that gave error 175
- Fixed bug that caused assert in move_to_next_bitmap() & _ma_read_bitmap_page()
- Much more DBUG_ASSERT(!maria_assert_if_crashed_table) to detect errors early
- EXTERNAL_LOCKING -> MARIA_EXTERNAL_LOCKING (to use same define everywhere

storage/maria/ma_bitmap.c:
  More secure handling of first_bitmap_with_space (now we also take care of wrong values)
  Don't set page to -1; This fixed unlikely bug that caused assert in move_to_next_bitmap() & _ma_read_bitmap_page()
storage/maria/ma_blockrec.c:
  More DBUG_ASSERT()'s
  Fixed multi-user problem with one thread doing inserts and another doing scans that gave error 175
  (We should use data_file_length from start of scan, not new value as new bitmap page may not yet be in page cache)
storage/maria/ma_check.c:
  EXTERNAL_LOCKING -> MARIA_EXTERNAL_LOCKING (to use same define everywhere)
storage/maria/ma_checkpoint.c:
  Made maria_checkpoint_min_activity static so that one can change it in debugger while testing.
  Fixed long standing performance problem that caused write of state info at checkpoint for any file that was ever changed since open.
storage/maria/ma_create.c:
  EXTERNAL_LOCKING -> MARIA_EXTERNAL_LOCKING (to use same define everywhere
storage/maria/ma_dynrec.c:
  Added missing MARIA_EXTERNAL_LOCKING (minor performance improvement)
storage/maria/ma_locking.c:
  EXTERNAL_LOCKING -> MARIA_EXTERNAL_LOCKING (to use same define everywhere
storage/maria/ma_open.c:
  EXTERNAL_LOCKING -> MARIA_EXTERNAL_LOCKING (to use same define everywhere
storage/maria/ma_pagecache.c:
  Added assert to detect reads outside of data file
storage/maria/maria_def.h:
  Added checkpoint_state to cache state writes in checkpoint

The assert conditions in the functions Item_direct_ref_to_ident::transform
and Item_direct_ref_to_ident::compile could be not valid after constant
propagation when fields and field references may be substituted for constants.
Not only these invalid asserts have been removed, but the functions containing
them have been removed as well because now Item_ref::transform and 
Item_ref::compile can be used instead of them.

include/Makefile.am:
  Added missing \
mysql-test/mysql-test-run.pl:
  Suppress warning when ha_archive is not compiled
mysql-test/r/variables-big.result:
  Updated results

If no index is used to access a materialized derived table or view
then the value of TABLE_REF::key for this table must be (-1).

Added progress reporting for alter table, LOAD DATA INFILE and for aria tables: check table, repair table, analyze table.
- The client gets a progress report message that triggers a callback function if requested with mysql_options(MYSQL_PROGRESS_CALLBACK, function)
- Added Progress field last to 'show processlist'
- Stage, Max_stage and Progress field added to information_schema.progresslist
- The 'mysql' client by defaults enables progress reports when the output is a tty.
- Added progress_report_time time variable to configure how often progress reports is sent to client
Added read only system variable 'in_transaction' which is 1 if we have executed a BEGIN statement.


client/client_priv.h:
  Added OPT_REPORT_PROGRESS
client/mysql.cc:
  Added option --progress-reports (on by default if not batch mode)
  Progress reports is written to stdout for long running commands
include/Makefile.am:
  Added mysql/service_progress_report.h
include/myisamchk.h:
  Added variables to be able to do progress reporting in Aria and later in MyISAM
include/mysql.h:
  Added new mysql_options() parameter: MYSQL_PROGRESS_CALLBACK
include/mysql.h.pp:
  Added new mysql_options() parameter: MYSQL_PROGRESS_CALLBACK
include/mysql/plugin.h:
  Added functions for reporting progress.
include/mysql/plugin_auth.h.pp:
  Added functions for reporting progress.
include/mysql_com.h:
  Added CLIENT_PROGRESS mysql_real_connect() flag.
include/sql_common.h:
  Added callback function for reporting progress
mysql-test/r/old-mode.result:
  Ensure that SHOW PROGRESSLIST doesn't have the Progress column in old mode.
mysql-test/suite/funcs_1/datadict/datadict_priv.inc:
  Added new column
mysql-test/suite/funcs_1/datadict/processlist_priv.inc:
  Test all new PROCESSLIST columns
mysql-test/suite/funcs_1/r/is_columns_is.result:
  Updated results
mysql-test/suite/funcs_1/r/is_columns_is_embedded.result:
  Updated results
mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result:
  Updated results
mysql-test/suite/funcs_1/r/is_tables_is_embedded.result:
  Updated results
mysql-test/suite/funcs_1/r/processlist_priv_no_prot.result:
  Updated results
mysql-test/suite/funcs_1/r/processlist_priv_ps.result:
  Updated results
mysql-test/suite/funcs_1/r/processlist_val_no_prot.result:
  Updated results
mysql-test/suite/funcs_1/r/processlist_val_ps.result:
  Updated results
mysql-test/suite/pbxt/r/pbxt_locking.result:
  Updated results
mysql-test/suite/pbxt/r/skip_name_resolve.result:
  Updated results
mysql-test/t/old-mode.test:
  Ensure that SHOW PROGRESSLIST doesn't have the Progress column in old mode.
plugin/handler_socket/handlersocket/Makefile.am:
  Added -lmysqlservices
scripts/mytop.sh:
  Made 'State' field width dynamic.
  Added 'Progress' to process list display.
sql-common/client.c:
  Added handling of progress messages.
  Removed check_license() function.
sql/mysql_priv.h:
  Added opt_progress_report_time
sql/mysqld.cc:
  Added progress_report_time time variable to configure how often progress reports is sent to client
sql/protocol.cc:
  Added net_send_progress_packet()
sql/protocol.h:
  New prototypes
sql/set_var.cc:
  Added variables progress_report_time and in_transaction
sql/sql_acl.cc:
  Safety fix: Made client_capabilities ulonglong
sql/sql_class.cc:
  Added interface functions for progress reporting
sql/sql_class.h:
  Added varibles in THD for progress reporting.
  Added CF_REPORT_PROGRESS
sql/sql_load.cc:
  Added progress reporting for LOAD DATA INFILE
sql/sql_parse.cc:
  Added CF_REPORT_PROGRESS for top level commands for which it's safe to send progress reports to client
sql/sql_show.cc:
  Added Progress field last to 'show processlist'
  Stage, Max_stage and Progress field added to information_schema.progresslist
sql/sql_table.cc:
  Added progress reporting for ALTER TABLE
  Added THD as argument to copy_data_between_tables()
storage/maria/ha_maria.cc:
  Added progress reporting for check table, repair table, analyze table
  Fixed a bug in start_bulk_insert() that caused alter table to always run with all keys enabled.
storage/maria/ma_check.c:
  Added progress reporting
  Remember old state before starting repair. This removes some warnings from optimize_table if create-with-sort fails.
storage/maria/ma_check_standalone.h:
  Added dummy reporting function for standalone Aria programs.
storage/maria/ma_sort.c:
  Added progress reporting
storage/maria/maria_chk.c:
  Updated version
storage/maria/maria_def.h:
  Added new prototypes
tests/mysql_client_test.c:
  Added test case for progress reporting

mysql-test/suite/pbxt/r/status.result:
  Updated result file from change in old commit