The wrong result set returned by the left join query  from
the bug test case happened due to several inconsistencies 
and bugs of the legacy mysql code.

The bug test case uses an execution plan that employs a scan
of a materialized IN subquery from the WHERE condition.
When materializing such an IN- subquery the optimizer injects
additional equalities  into the WHERE clause. These equalities
express the constraints imposed by the subquery predicate.
The injected equality of the query in the  test case happens
to belong to the same equality class, and a new equality 
imposing a condition on the rows of the materialized subquery
is inferred from this class. Simultaneously the multiple
equality is added to the ON expression of the LEFT JOIN
used in the main query.
  
The inferred equality of the form f1=f2 is taken into account
when optimizing the scan of  the rows the temporary table 
that is the result of the subquery materialization: only the 
values of the field f1 are read from the table into the record 
buffer. Meanwhile the inferred equality is removed from the
WHERE conditions altogether as a constraint on the fields
of the temporary table that has been used when filling this table. 
This equality is supposed to be removed from the ON expression
when the multiple equalities of the ON expression are converted
into an optimal set of equality predicates. It supposed to be
removed from the ON expression as an equality inferred from only
equalities of the WHERE condition. Yet, it did not happened
due to the following bug in the code.

Erroneously the code tried to build multiple equality for ON
expression twice: the first time, when it called optimize_cond()
for the WHERE condition, the second time, when it called
this function for the HAVING condition. When executing
optimize_con() for the WHERE condition  a reference
to the multiple equality of the WHERE condition is set
in the multiple equality of the  ON expression. This reference
would allow later to convert multiple equalities of the
ON expression into equality predicates. However the 
the second call of build_equal_items() for the ON expression
that happened when optimize_cond() was called for the
HAVING condition reset this reference to NULL.

This bug fix blocks calling build_equal_items() for ON
expressions for the second time. In general, it will be
beneficial for many queries as it removes from ON 
expressions any equalities that are to be checked for the
WHERE condition.
The patch also fixes two bugs in the list manipulation
operations and a bug in the function  
substitute_for_best_equal_field() that resulted
in passing wrong reference to the multiple equalities
of where conditions when processing multiple
equalities  of ON expressions.

The code of substitute_for_best_equal_field() and
the code the helper function eliminate_item_equal()
were also streamlined and cleaned up.
Now the conversion of the multiple equalities into
an optimal set of equality predicates first produces
the sequence of the all equalities processing multiple
equalities one by one, and, only after this, it inserts
the equalities at the beginning of the other conditions.

The multiple changes in the output of EXPLAIN
EXTENDED are mainly the result of this streamlining,
but in some cases is the result of the removal of
unneeded equalities from ON expressions. In
some test cases this removal were reflected in the
output of EXPLAIN resulted in disappearance of 
“Using where” in some rows of the execution plans.

MDEV-3875 Wrong result (missing row) on a DISTINCT query with the same subquery in the SELECT list and GROUP BY

fix remove_dup_with_hash_index() and remove_dup_with_compare() to take NULLs into account

reached by fix_fields() (via reference) before row which it belongs to (on the second execution)
and fix_field for row did not follow usual protocol for Items with argument
(first check that the item fixed then call fix_fields).

Item_row::fix_field fixed.

MDEV-729 lp:998028 - Server crashes on normal shutdown in closefrm after executing a query from MyISAM table

don't write a key value into the record buffer - a key length can be larger then the record length.

MDEV-759 lp:998340 - Valgrind complains on simple selects containing expression DAY(FROM_UNIXTIME(-1))

check item->null_value before using the result of item->val_int()

mysqldump.c: s/SET OPTION/SET/
(OPTION was, hm, optional since 3.21, so there's no need to use SET OPTION even
in the old compatibility modes)

instead of returning Access denied on the incorrect user name,
emulate the complete failed logic procedure, possibly with
the change plugin packet.

  
allow only three failed change_user per connection.
successful change_user do NOT reset the counter

tests/mysql_client_test.c:
  make --error to work for --change_user errors

This bug could result in returning 0 for the expressions of the form 
<aggregate_function>(distinct field) when the system variable  
max_heap_table_size was set to a small enough number.
It happened because the method Unique::walk() did not support
the case when more than one pass was needed to merge the trees
of distinct values saved in an external file.

Backported a fix in grant_lowercase.test from mariadb 5.5.

MDEV-4029 SELECT on information_schema using a subquery locks up the information_schema table due to incorrect mutexes handling
  
Early evaluation of subqueries in the WHERE conditions on I_S.*_STATUS tables,
otherwise the subquery on this same table will try to acquire LOCK_status twice.

The problem was that maybe_null of Item_row and its componetes was unsynced after update_used_tables() (and so pushed_cond_guards was not initialized but then requested).

Fix  updates Item_row::maybe_null on update_used_tables().

MDEV-3900 Optimizer difference between MySQL and MariaDB with stored functions in WHERE clause of UPDATE or DELETE statements

Analysis
The reason for the less efficient plan was result of a prior design decision -
to limit the eveluation of constant expressions during optimization to only
non-expensive ones. With this approach all stored procedures were considered
expensive, and were not evaluated during optimization. As a result, SPs didn't
participate in range optimization, which resulted in a plan with table scan
rather than index range scan.

Solution
Instead of considering all SPs expensive, consider expensive only those SPs
that are non-deterministic. If an SP is deterministic, the optimizer will
checj if it is constant, and may eventually evaluate it during optimization.

Don't reset maybe_null in update_used_tables(); This breaks ROLLUP
This fixed failing test in group_by.test

Subquery turned into constant too late to be excluded from grouping list so test for constant added to the create_temp_table().

The original patch with the implementation of virtual columns
did not support INSERT DELAYED into tables with virtual columns.
This patch fixes the problem.

The bug could lead to a wrong estimate of the number of expected rows
in the output of the EXPLAIN commands for queries with GROUP BY.
This could be observed in the test case for LP bug 934348.

MDEV-4020 : Make sure strmov symbol is exported by client library  on Linux (even if the server and libraries itself use stpcpy instead of it)

It is a workaround that allows myodbc built by certain distributions' (CentOS,Fedora) to peacefully coexist with mariadb client libraries.
The problem is that MyODBC in these distros needs strmov() to be exported by mysql client shared library, or else myodbc fails to load.

MDEV-3942 FROM_DAYS(<timestamp column>) returns different result in MariaDB comparing to MySQL: NULL vs 0000-00-00

fixed a regression, introduced while fixing MDEV-456

from a MERGE view.

The problem was in the lost ability to be null for the table of a left join if it
is a view/derived table.

It hapenned because setup_table_map(), was called earlier then we merged
the view or derived.

Fixed by propagating new maybe_null flag during Item::update_used_tables().

Change in join_outer.test and join_outer_jcl6.test appeared because
IS NULL reported no used tables (i.e. constant) for argument which could not be
NULL and new maybe_null flag was propagated for IS NULL argument (Item_field)
because table the Item_field belonged to changed its maybe_null status.

MDEV-3928: Assertion `example' failed in Item_cache::is_expensive_processor with a 2-level IN subquery

Analysis:
The following call stack shows that it is possible to set Item_cache::value_cached, and the relevant value
without setting Item_cache::example.

#0 Item_cache_temporal::store_packed at item.cc:8395
#1 get_datetime_value at item_cmpfunc.cc:915
#2 resolve_const_item at item.cc:7987
#3 propagate_cond_constants at sql_select.cc:12264
#4 propagate_cond_constants at sql_select.cc:12227
#5 optimize_cond at sql_select.cc:13026
#6 JOIN::optimize at sql_select.cc:1016
#7 st_select_lex::optimize_unflattened_subqueries at sql_lex.cc:3161
#8 JOIN::optimize_unflattened_subqueries at opt_subselect.cc:4880
#9 JOIN::optimize at sql_select.cc:1554

The fix is to set Item_cache_temporal::example even when the value is
set directly by Item_cache_temporal::store_packed. This makes the
Item_cache_temporal object consistent.

taking a change done to main 5.1 by Dmitri Lenev.

This is the original comment:

> committer: Dmitry Lenev <Dmitry.Lenev@oracle.com>
> branch nick: mysql-5.1-15954896
> timestamp: Wed 2012-12-05 19:26:56 +0400
> message:
>   Bug #15954896 "SP, MULTI-TABLE DELETE AND LONG ALIAS".

  Using too long table aliases in stored routines might
  have caused server crashes.

  Code in sp_head::merge_table_list() which is responsible
  for collecting information about tables used in stored
  routine was not aware of the fact that table alias might
  have arbitrary length. I.e. it assumed that table alias
  can't be longer than NAME_LEN bytes and allocated buffer
  for a key identifying table accordingly.

  This patch fixes the issue by ensuring that we use
  dynamically allocated buffer for table key when table
  alias is too long. By default stack based buffer is used
  in which NAME_LEN bytes are reserved for table alias.

The failure is caused by failing stat() call . C Runtime function stat() uses old struct with 32bit st_size member,
and since Visual Studio 2010 , it returns an error on st_size overflow (i.e on files larger than 4GB)

Fix replaces stat() by my_stat(), the later is backed by 64bit-able stat64().

Fixed algorithm of detecting of first real table in view/subquery-in-the-FROM-clase.