BUG##849717: Crash in Item_func::fix_fields on second execution of a prepared statement with semijoin
- If convert_join_subqueries_to_semijoins() decides to wrap Item_in_subselect in Item_in_optimizer, 
  it should do so in prep_on_expr/prep_where, too, as long as they are present.
  There seems to be two possibilities of how we arrive in this function:
  - prep_on_expr/prep_where==NULL, and will be set later by simplify_joins()
  - prep_on_expr/prep_where!=NULL, and it is a copy_and_or_structure()-made copy of on_expr/where.
  the latter can happen for some (but not all!) nested joins. This bug was that we didn't handle this case.

- Mrr_ordered_index_reader::interrupt_read() and resume_read() should 
  save/restore not just index lookup tuple, but entire index tuple.
  Key parts that are not used for index lookup can be still used in
  pushed index condition. Failure to save/restore will cause the index
  condition to be evaluated over the wrong values.

Fixed that automatic killing of delayed insert thread (in flush, alter table etc) will not abort auto-repair of MyISAM table.
Give more information when finding an error in a MyISAM table.
When killing system thread, use KILL_SYSTEM_THREAD instead of KILL_CONNECTION to make it easier to ignore the signal in sensitive context (like auto-repair)
Added new kill level: KILL_SERVER that will in the future to be used to signal killed by shutdown.
Add more warnings about killed connections when warning level > 3

include/myisamchk.h:
  Added counting of printed info/notes
mysys/mf_iocache.c:
  Remove duplicate assignment
sql/handler.cc:
  Added test of KILL_SERVER
sql/log.cc:
  Ignore new 'kill' error ER_NEW_ABORTING_CONNECTION when requesting query error code.
sql/mysqld.cc:
  Add more warnings for killed connections when warning level > 3
sql/scheduler.cc:
  Added checks for new kill signals
sql/slave.cc:
  Ignore new kill signal ER_NEW_ABORTING_CONNECTION
sql/sp_head.cc:
  Fixed assignment to bool
  Added testing of new kill signals
sql/sql_base.cc:
  Use KILL_SYSTEM_THREAD to auto-kill system threads
sql/sql_class.cc:
  Add more warnings for killed connections when warning level > 3
  thd_killed() now ignores KILL_BAD_DATA and THD::KILL_SYSTEM_THREAD as these should not abort sensitive operations.
sql/sql_class.h:
  Added KILL_SYSTEM_THREAD and KILL_SERVER
sql/sql_connect.cc:
  Added handling of KILL_SERVER
sql/sql_insert.cc:
  Use KILL_SYSTEM_THREAD to auto-kill system threads
  Added handling of KILL_SERVER
sql/sql_parse.cc:
  Add more warnings for killed connections when warning level > 3
  Added checking that thd->abort_on_warning is reset at end of query.
sql/sql_show.cc:
  Update condition for when a query is 'killed'
storage/myisam/ha_myisam.cc:
  Added counting of info/notes printed
storage/myisam/mi_check.c:
  Always print an an error if we find data errors when checking/repairing a MyISAM table.
  When a repair was killed, don't retry repair.
  Added assert if sort_get_next_record() returned an error without an error message.
  Removed nonsence check "if (sort_param->read_cache.error < 0)" in repair.
storage/myisam/myisamchk.c:
  Added counting of notes printed
storage/pbxt/src/thread_xt.cc:
  Better error message.

- The bug was caused by outer join being incorrectly converted into inner because of 
  invalid return values of Item_direct_view_ref::not_null_tables().
- Provided a correct Item_direct_view_ref::not_null_tables() function.

- Let join buffering code correctly take into account rowids needed 
  by DuplicateElimination when it is calculating minimum record sizes.
- In JOIN_CACHE::write_record_data, added asserts that prevent us from 
  writing beyond the end of the buffer.

The problem was that optimization code did not take into account later feature when instad of NOT before BETWEEN it has negated flag into the Item_func_between inherited from Item_func_neg_opt. So optimizer tried process NOT BETWEEN as BETWEEN.

The patch just switches off the optimisation for NOT BETWEEN as it was before when NOT function was really used.

remove incorrect DBUG_ASSERT().
Fix incorrectly used cmp_item::get_comparator() in Item_func_case and Item_equal

For any query JOIN::optimize() should call the method
SELECT::save_leaf_tables after the last transformation
that utilizes the statement memory rather than the 
execution memory.
  

- Make subquery_types_allow_materialization() detect a case where 
  create_tmp_table() would create a blob column which would make it 
  impossible to use materialization
  Non-semi-join materialization worked because it detected that this case
  and felt back to use IN->EXISTS. Semi-join Materialization cannot easily
  fallback, so we have to detect this case early.

- Don't use join buffering for tables that are within ranges that are 
  covered by LooseScan strategy.

- get_bound_sj_equalities() would produce incorrect bitmap when non-first 
  equality was bound, which resulted in invalid LooseScan plans.

  
  ALL subquery should return TRUE if subquery rowa set is empty independently
  of left part.  The problem was that Item_func_(eq,ne,gt,ge,lt,le) do not
  call execution of second argument if first is NULL no in this case subquery
  will not be executed and when Item_func_not_all calls any_value() of the
  subquery or aggregation function which report that there was rows. So for
  NULL < ALL (SELECT...) result was FALSE instead of TRUE.
  
  Fix is just swapping of arguments of Item_func_(eq,ne,gt,ge,lt,le) (with
  changing the operation if it is needed) so that result will be the same
  (for examole a < b is equal to b > a). This fix exploit the fact that
  first argument will be executed in any case.

- Item_in_subselect::inject_in_to_exists_cond() should not call 
  ((Item_cond*)join->conds)->argument_list()->concat(join->cond_equal->current_level)
  as that makes two lists share their tail, and the cond_equal list
  will end up containing non-Item_equal objects when substitute_for_best_equal_field()
  walks through join->conds and replaces all Item_equal objects with Item_func_eq objects.
  - So, instead of using List::concat(), manually copy entries from one list to another.
 

- Make find_all_keys() not to rely on table->tmp_set remaining constant during execution
  quick_index_merge_select->reset() may change it.

Fixed lp:828514 "Assertion `! is_set()' failed in Diagnostics_area::set_ok_status with derived table + subquery + concurrent DML"


sql/item_subselect.cc:
  Added check of error condtions (safety)
sql/sql_join_cache.cc:
  Added DBUG to some functions.
  Added error checking for calls to check_match(); This fixed the bug.
sql/sql_select.cc:
  Moved variable assignment to be close to where it's used (cleanup)

- setup_sj_materialization() code failed to take into account that it can be that 
  the first [in join order ordering] table inside semi-join-materialization nest 
  is also an inner table wrt an outer join (that is embedded in the semi-join).  
  This can happen when all of the tables that are inside the semi-join but not inside
  the outer join are constant.
- Made a trivial to not assume that table's embedding join nest is the semi-join 
  nest: instead, walk up the outer join nests until we reach the semi-join nest.

- Make JOIN::get_partial_cost_and_fanout() be able to handle join plans with 
  semi-join-materialization nests.

Added --debug-assert-on-error variable which, if set, will cause safe_mutex to assert if it founds an error.

include/my_sys.h:
  Added my_assert_on_error
mysys/my_static.c:
  Added my_assert_on_error
mysys/thr_mutex.c:
  Assert when found wrong mutex usage if my_assert_on_error is set
sql/mysqld.cc:
  Added setting of my_assert_on_error

For ANY subqueries NULLs should be ignored (if there is other values) when finding max min.
For ALL subqueries NULLs should be saved if they found.

Optimisation for ALL suqbueries if NULL is possible in the SELECT list with max/min aggregate function switched off.

Some test changed where NULL is not used but optimization with max/min aggregate function important so NOT NULL added.

mysql-test/r/explain.result:
  Forced old optimization.
mysql-test/r/subselect.result:
  Forced old optimization.
  
  New test suite.
mysql-test/t/explain.test:
  Forced old optimization.
mysql-test/t/subselect.test:
  Forced old optimization.
  
  New test suite.
sql/item_subselect.cc:
  Store converted subquery type.
  
  Switch off aggregate function optimisation for ALL and nulls.
sql/sql_class.cc:
  Fixed NULL comparison.
sql/sql_class.h:
  Store converted subquery type.

Added logging of all errors from my_read/my_write/my_pread/my_pwrite/my_open & my_malloc to mysqld error log if one sets log-warning to 10 or 11
The idea is that my_global_flags is ored to the MyFlags parameter for the above functions if the MY_WME flag is not set.
As the my_global_flags has ME_JUST_INFO (mark error as 'note') and possible ME_NOREFRESH (write error to log) this will force mysqld to log the not critical error to the log as a note.


 

include/my_sys.h:
  Moved MY_SYNC_DIR to ensure it never clashes with ME_JUST_INFO
  Added my_global_flags
mysql-test/Makefile.am:
  Removed not used bugs directory
mysys/my_init.c:
  Added my_global_flags, a variable that is ored to MyFlags in a those mysys functions we want extra logging.
mysys/my_malloc.c:
  Added support for my_global_flags
mysys/my_open.c:
  Added support for my_global_flags
mysys/my_pread.c:
  Added support for my_global_flags
mysys/my_read.c:
  Added support for my_global_flags
mysys/my_static.c:
  Added my_global_flags
mysys/my_write.c:
  Added support for my_global_flags
sql/mysqld.cc:
  Set my_global_flags for warning levels 10 & 11
sql/sql_base.cc:
  Don't increment unhandled errors for notes or warnings.

Analysis:
In the test query semi-join merges the inner-most subquery
into the outer subquery, and the optimization of the merged
subquery finds some new index access methods. Later the
IN-EXISTS transformation is applied to the unmerged subquery.
Since the optimizer is instructed to not consider
materialization, it reoptimizes the plan in-place to take into
account the new IN-EXISTS conditions. Just before reoptimization
JOIN::choose_subquery_plan resets the query plan, which also
resets the access methods found during the semi-join merge.
Then reoptimization discovers there are no new access methods,
but it leaves the query plan in its reset state. Later semi-join
crashes because it assumes these access methods are present.

Solution:
When reoptimizing in-place, reset the query plan only after new
access methods were discovered. If no new access methods were
discovered, leave the current plan as it was.

Added more printing of errors to myisamchk.

mysys/mf_iocache.c:
  Write error message if failed seek.
sql/table.cc:
  Fixed buffer overflow bug:
  - It's not enough to check for mysql_version to to detect partion indicator as the version may have been updated by mysql_upgrade.
storage/myisam/ha_myisam.cc:
  Don't log same error twice.
  Don't reset log_all_errors if it's set
storage/myisam/mi_check.c:
  Fixed bug that caused repair() to not report error if called twice (as when doing retry)
  More printing of errors.
storage/myisam/sort.c:
  Set my_errno in case of out of memory errors.

Added variable ARIA_CHECKPOINT_LOG_ACTIVITY to allow one to specify how often we should do a checkpoint.
Added more error printing to log if log_warnings > 2
Give an error if checkpoint record is not correct,

mysql-test/suite/maria/r/compat_aliases.result:
  Added ARIA_CHECKPOINT_LOG_ACTIVITY
mysql-test/suite/maria/r/maria3.result:
  Added ARIA_CHECKPOINT_LOG_ACTIVITY
storage/maria/ha_maria.cc:
  Added ARIA_CHECKPOINT_LOG_ACTIVITY
  Added more error printing to log if log_warnings > 2
  Added db and table name to error message when printing to log
storage/maria/ma_check.c:
  Fixed bug where we didn't reset some variables between repair() calls
storage/maria/ma_checkpoint.c:
  Made maria_checkpoint_min_activity global.
  Don't do checkpoint if no data logged.
  Changed test for if we should do checkpoint to test separately for if log has grown or if we have had a lot of of cache writes.
storage/maria/ma_recovery.c:
  Give an error if checkpoint record is not correct
storage/maria/trnman.c:
  Don't print not needed long_transaction_id entries for checkpoints.

BUG##836491: Crash in Item_field::Item_field from add_ref_to_table_cond() with semijoin+materialization
- Let create_tmp_table set KEY_PART_INFO::fieldnr. It is needed in add_ref_to_table_cond(), and possibly other places.

Added logging of all messages (also system warnings) one gets during a MyISAM recovery or auto-recovery.


sql/mysqld.cc:
  Log errors if thd->log_all_errors is set
sql/sql_class.cc:
  Add log_all_errors
sql/sql_class.h:
  Add log_all_errors
storage/myisam/ha_myisam.cc:
  Write db and table name for all logged errors
  Log errors also during auto_recovery
  During auto_recovery, set thd->log_all_errors if log_warnings >2 to ensure that system errors are also logged to file