BUG#952372: Server crashes on 2nd execution of PS in find_field_in_tables with semijoin+materialization
- The problem was that convert_subq_to_jtbm() attached the semi-join
  TABLE_LIST object into the wrong list: they used to attach it to the 
  end of parent_lex->leaf_tables.head()->next_local->...->next_local.
  This was apparently inccorect, as one can construct an example where 
  JTBM nest is attached to a table that is inside some mergeable VIEW, which
  breaks (causes crash) for name resolution on the subsequent statement
  re-execution.
- Solution: Attach to the "right" list. The "wording" was copied from
  st_select_lex::handle_derived.

- The bug would show up 
   - when using PS (so that we get re-execution)
   - the left_expr of the subquery is a reference to viewname.column_name, so that it crashes
      when one tries to use it without having called fix_fields for it.
   - when using SJ-Materialization, which makes use of sj_subq_pred->left_expr expression

- The fix is to have setup_conds() fix sj_subq_pred->left_expr for semi-join nests it finds.

The function create_hj_key_for_table() that builds the descriptor of
the hash join key to access a table of a materialized subquery must
ignore any equi-join predicate depending on the tables not belonging
to the subquery.

This is needed as last log entry may be a DDL that is not processed and then a table may be left in 'not properly closed state' even if information is correct in it.

Problem is that subquery execution can't be called during prepare/optimize phase.

Also small fix for subquery test suite.

This bug in the function JOIN::drop_unused_derived_keys() could
leave the internal structures for a materialized derived table
in an inconsistent state. This led to a not quite correct EXPLAIN
output when no additional key had been created to access the table.
It also may lead to more serious consequences: so, the test case
added with this fix caused a crash in mariadb-5.5.20.  

This bug appeared after the patch for bug 939009 that in the
function merge_key_fields forgot to reset a proper value for
the val field in the result of the merge operation of the key
field created for a regular key access and  the key field
created to look for a NULL key.

Adjusted the results of the test case for bug 939009 that
actually were incorrect. 

Fixed lp:925377 "Querying myisam table metadata while 'alter table..enable keys' is running may corrupt the table"
Fixed wrong mutex order bug in Aria when flush_log_for_bitmap() was called when table is not yet marked for change.

include/my_base.h:
  Added flag that table is opened only for status
mysql-test/r/myisam-big.result:
  Test case for lp:925377
mysql-test/t/myisam-big.test:
  Test case for lp:925377
sql/sql_base.cc:
  If thd->version == 0 (happens only when we are opening a table that is flushed under  MYSQL_LOCK_IGNORE_FLUSH), open the table in HA_OPEN_FOR_STATUS mode
storage/maria/ma_bitmap.c:
  Fixed wrong mutex order bug in Aria when flush_log_for_bitmap() was called when table is not yet marked for change.
storage/maria/ma_dbug.c:
  Ignore last_version <= 1 as these are either flushed or only opened for status
storage/maria/ma_open.c:
  Use last_version=1 as a marker that table was opened with HA_OPEN_FOR_STATUS.
  In this case we just open a new version of the table in read only mode.
storage/myisam/mi_create.c:
  Update prototype
storage/myisam/mi_dbug.c:
  Ignore last_version <= 1 as these are either flushed or only opened for status
storage/myisam/mi_open.c:
  Use last_version=1 as a marker that table was opened with HA_OPEN_FOR_STATUS.
  If HA_OPEN_FOR_STATUS is used, we will not assert if there is an old not-to-be-used version of the table existing.
  In this case we just open a new version of the table in read only mode.
storage/myisam/myisamdef.h:
  Updated prototype

make sure that stored routines are evaluated (that is, de facto - cached) in convert_const_to_int().
revert the fix for lp:806943 because it cannot be repeated anymore.
add few tests for convert_const_to_int()

The issue was that Aria allowed too long keys to be created (so that the internal buffer was not big enough to hold the whole key).
Key lengths is now limited to HA_MAX_KEY_LENGTH (1000), as for MyISAM.
Fixed failure in  "_ma_apply_redo_index: Assertion `new_page_length == 0", as found by buildbot.

mysql-test/suite/maria/r/maria.result:
  Updated results
mysql-test/suite/maria/r/maria3.result:
  Updated results. Added test for bug fix
mysql-test/suite/maria/t/maria3.test:
  Updated results. Added test for bug fix
mysql-test/suite/maria/t/optimize.test:
  Updated test for new max key length
storage/maria/ha_maria.cc:
  Limit key to HA_MAX_KEY_LENGTH.
storage/maria/ma_key_recover.c:
  Limit used page length to max page size (this is in line with the code that writes the entry to the log).
  This fixes failure in  "_ma_apply_redo_index: Assertion `new_page_length == 0", as found by buildbot.
storage/maria/ma_search.c:
  Extra DBUG
storage/maria/ma_write.c:
  Added test to detect errors earlier.

A better fix for this bug will be pulled from mariadb-5.2.

The field key_cache_mem_size of the KEY_CACHE structure must be
initialized in the function init_key_cache() and updated in the
function resize_key_cache().

Cause of the bug is uninitialized items before evaluation HAVING clasue in case of empty result.

The result of materialization of the right part of an IN subquery predicate
is placed into a temporary table. Each row of the materialized table is
distinct. A unique key over all fields of the temporary table is defined and
created. It allows to perform key look-ups into the table.
The table created for a materialized subquery can be accessed by key as
any other table. The function best_access-path search for the best access
to join a table to a given partial join. With some where conditions this
function considers a possibility of a ref_or_null access. If such access
employs the unique key on the temporary table then when estimating
the cost this access the function tries to use the array rec_per_key. Yet,
such array is not built for this unique key. This causes a crash of the server.

Rows returned by the subquery that contain nulls don't have to be placed
into temporary table, as they cannot be match any row produced by the
left part of the subquery predicate. So all fields of the temporary table
can be defined as non-nullable. In this case any ref_or_null access
to the temporary table does not make any sense and it does not make sense
to estimate such an access.

The fix makes sure that the temporary table for a materialized IN subquery
is defined with columns that are all non-nullable. The also ensures that 
any row with nulls returned by the subquery is not placed into the
temporary table.  

- Enable subquery materialization for CREATE TABLE ... SELECT.

Problem was a crash in internal temporary (Maria) files when row length exceeded 65535

mysql-test/suite/maria/r/maria3.result:
  Added test case
mysql-test/suite/maria/t/maria3.test:
  Added test case
storage/maria/ma_open.c:
  Added support for row length > 65535.
  This fixes crash when using tables with longer row lengths.

- After the exec_const_cond->val_int() call, check for error and return.
  (if we don't do it, we will eventually hit an error when trying to set status OK in 
   the diagnostics area, which already has an error status).

Removed const declarations to get rid of compiler warnings. The changes are similar to what is done in innobase in MySQL 5.5

BUILD/SETUP.sh:
  By default, build also with innodb-plugin
mysql-test/mysql-test-run.pl:
  Also search in lib64 directory for plugins (This is used at least on OpenSuse 12.1 when using default build scripts)
mysql-test/r/lock_multi.result:
  Allow test to be re-run even if it crashed.
mysql-test/t/lock_multi.test:
  Allow test to be re-run even if it crashed.
scripts/make_binary_distribution.sh:
  Ensure that libexecdir is named libexec (was not on OpenSuse 12.1)
scripts/mysql_config.sh:
  Fixed detection of lib64 was used.

This also fixes a (not likely) crashing bug when forcing a thread that was doing a table lock to re-open it's files, for example by creating a trigger.


mysys/thr_lock.c:
  Added more checking to find wrong locks.
  Removed one, not needed, parameter to thr_lock
sql/lock.cc:
  Fixed mysql_lock_tables() to retry with new sql_lock if lock fails. This was needed as table may be closed and reopened between retry's and then the old sql_lock will point to stale data.
sql/mysql_priv.h:
  Updated prototype
sql/sql_base.cc:
  Ensure that all tables are closed if opening of system table failes; This fixes the assert in THD::restore_backup_open_tables_state
sql/sql_handler.cc:
  Updated variable type

 execution which mtr --embedded does not support

Sort counters by their names (otherwise, the order of SHOW STATUS output is sometimes sorted and sometimes not)

 Handler_mrr_key_refills, Handler_mrr_rowid_refills.