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.

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

- The problem was that the code that made the check whether the subquery is an AND-part of the WHERE 
  clause didn't work correctly for nested subqueries. In particular, grand-child subquery in HAVING was 
  treated as if it was in the WHERE, which eventually caused an assert when replace_where_subcondition
  looked for the subquery predicate in the WHERE and couldn't find it there.

- The fix: Removed implementation of "thd_marker approach". thd->thd_marker was used to determine the 
  location of subquery predicate: setup_conds() would set accordingly it when making the 

    {where|on_expr}->fix_fields(...)

  call so that AND-parts of the WHERE/ON clauses can determine they are the AND-parts. 
  Item_cond_or::fix_fields(), Item_func::fix_fields(), Item_subselect::fix_fields (this one was missed),
  and all other items-that-contain-items had to reset thd->thd_marker before calling fix_fields() for 
  their children items, so that the children can see they are not AND-parts of WHERE/ON.
- The "thd_marker approach" required that a lot of code in different locations maintains correct value of
  thd->thd_marker, so it was replaced with:
- The new approach with mark_as_condition_AND_part does not keep context in thd->thd_marker. Instead, 
  setup_conds() now calls

    {where|on_expr}->mark_as_condition_AND_part()

  and implementations of that function make sure that: 
   - parts of AND-expressions get the mark_as_condition_AND_part() call
   - Item_in_subselect objects record that they are AND-parts of WHERE/ON

sql-bench/crash-me.sh:
  Test types with microseconds
  Fixed some warnings from perl
  Code cleanups
  Don't write comments to .cfg file if run with --noverbose

client/mysql.cc:
  Don't remove MariaDB executable comments.
mysql-test/r/comments.result:
  Test MariaDB executable comments.
mysql-test/r/mysql.result:
  Test MariaDB executable comments.
mysql-test/t/comments.test:
  Test MariaDB executable comments.
mysql-test/t/mysql.test:
  Test MariaDB executable comments.
sql/sql_cache.cc:
  Don't delete MariaDB executable comments.
sql/sql_lex.cc:
  Handle MariaDB executable comments

Analysis:
Constant table optimization of the outer query finds that
the right side of the equality is a constant that can
be used for an eq_ref access to fetch one row from t1,
and substitute t1 with a constant. Thus constant optimization
triggers evaluation of the subquery during the optimize
phase of the outer query.

The innermost subquery requires a plan with a temporary
table because with InnoDB tables the exact count of rows
is not known, and the empty tables cannot be optimzied
way. JOIN::exec for the innermost subquery substitutes
the subquery tables with a temporary table.

When EXPLAIN gets to print the tables in the innermost
subquery, EXPLAIN needs to print the name of each table
through the corresponding TABLE_LIST object. However,
the temporary table created during execution doesn't
have a corresponding TABLE_LIST, so we get a null
pointer exception.

Solution:
The solution is to forbid using expensive constant
expressions for eq_ref access for contant table
optimization. Notice that eq_ref with a subquery
providing the value is still possible during regular
execution.

Analysis:
During the first execution of the query through the stored
procedure, the optimization phase calls
substitute_for_best_equal_field(), which calls
Item_in_optimizer::transform(). The latter replaces
Item_in_subselect::left_expr with args[0] via assignment.
In this test case args[0] is an Item_outer_ref which is
created/deallocated for each re-execution. As a result,
during the second execution Item_in_subselect::left_expr
pointed to freed memory, which resulted in a crash.

Solution:
The solution is to use change_item_tree(), so that the
origianal left expression is restored after each execution.

Analysis:
Partial matching is used even when there are no NULLs in
a materialized subquery, as long as the left NOT IN operand
may contain NULL values.

This case was not handled correctly in two different places.
First, the implementation of parital matching did not clear
the set of matching columns when the merge process advanced
to the next row.

Second, there is no need to perform partial matching at all
when the left operand has no NULLs.

Solution:
First fix subselect_rowid_merge_engine::partial_match() to
properly cleanup the bitmap of matching keys when advancing
to the next row.

Second, change subselect_partial_match_engine::exec() so
that when the materialized subquery doesn't contain any
NULLs, and the left operand of [NOT] IN doesn't contain
NULLs either, the method returns without doing any
unnecessary partial matching. The correct result in this
case is in Item::in_value.

sql/sql_select.cc:
  items' cmp_type()'s must match, not result_type()'s

When the WHERE/HAVING condition of a subquery has been transformed
by the optimizer the pointer stored the 'where'/'having' field 
of the SELECT_LEX structure used for the subquery must be updated
accordingly. Otherwise the pointer may refer to an invalid item.
This can lead to the reported assertion failure for some queries
with correlated subqueries   

The bug is a duplicate of MySQL's Bug#11764086,
however MySQL's fix is incomplete for MariaDB, so
this fix is slightly different.

In addition, this patch renames
Item_func_not_all::top_level() to is_top_level_item()
to make it in line with the analogous methods of
Item_in_optimizer, and Item_subselect.

Analysis:
It is possible to determine whether a predicate is
NULL-rejecting only if it is a top-level one. However,
this was not taken into account for Item_in_optimizer.
As a result, a NOT IN predicate was erroneously
considered as NULL-rejecting, and the NULL-complemented
rows generated by the outer join were rejected before
being checked by the NOT IN predicate.

Solution:
Change Item_in_optimizer to be considered as
NULL-rejecting only if it a top-level predicate.

opt_range.cc: modified print_key() so that it doesn't do memory re-allocs when
printing multipart keys over varchar columns. When it did, key printout in 
debug trace was interrupted with my_malloc/free printouts.