Server crashed during a sort order optimization
of a dependent subquery:

SELECT
    (SELECT t1.a FROM t1, t2
      WHERE t1.a = t2.b AND t2.a = t3.c
      ORDER BY t1.a)
  FROM t3;


Bitmap of tables, that the reference to outer table
column uses, in addition to the regular table bit
has the OUTER_REF_TABLE_BIT bit set.
The only_eq_ref_tables function traverses this map
bit by bit simultaneously with join->map2table list.
Obviously join->map2table never contains an entry
for the OUTER_REF_TABLE_BIT pseudo-table, so the
server crashed there.


The only_eq_ref_tables function has been modified
to traverse regular table bits only like the
update_depend_map function (resetting of the
OUTER_REF_TABLE_BIT there is enough, but
resetting of the whole set of PSEUDO_TABLE_BITS
is used there for sure).


mysql-test/r/order_by.result:
  Added test case for bug #39844.
mysql-test/t/order_by.test:
  Added test case for bug #39844.
sql/sql_select.cc:
  Bug #39844: Query Crash Mysql Server 5.0.67
  
  The only_eq_ref_tables function has been modified
  to traverse regular table bits only like the
  update_depend_map function (resetting of the
  OUTER_REF_TABLE_BIT there is enough, but
  resetting of the whole set of PSEUDO_TABLE_BITS
  is used there for sure).

The problem is that the function used by the server to increase
the thread's priority (pthread_setschedparam) has the unintended
side-effect of changing the calling thread scheduling policy,
possibly overwriting a scheduling policy set by a sysadmin.

The solution is to rely on the pthread_setschedprio function, if
available, as it only changes the scheduling priority and does not
change the scheduling policy. This function is usually available on
Solaris and Linux, but it use won't work by default on Linux as the
the default scheduling policy only accepts a static priority 0 -- this
is acceptable for now as priority changing on Linux is broken anyway.

configure.in:
  Check for the existence of the pthread_setschedprio function.
include/my_pthread.h:
  Use the pthread_setschedprio function to set the thread priority
  if the function is available.

The problem is that MySQL's 'fast' mutex implementation uses the
random() routine to determine the spin delay. Unfortunately, the
routine interface is not thead-safe and some implementations (eg:
glibc) might use a internal lock to protect the RNG state, causing
excessive locking contention if lots of threads are spinning on
a MySQL's 'fast' mutex. The code was also misusing the value
of the RAND_MAX macro, this macro represents the largest value
that can be returned from the rand() function, not random().

The solution is to use the quite simple Park-Miller random number
generator. The initial seed is set to 1 because the previously used
generator wasn't being seeded -- the initial seed is 1 if srandom()
is not called.

Futhermore, the 'fast' mutex implementation has several shortcomings
and provides no measurable performance benefit. Therefore, its use is
not recommended unless it provides directly measurable results.


include/my_pthread.h:
  Add field to keep the RNG state.
mysys/thr_mutex.c:
  Use a palliative per-mutex rng state to determine the spin delay.
  The RNG is not thread-safe but jumping a few sequences in the RNG
  is harmless.

The problem is that the offset argument of the limit clause
might be truncated on a 32-bits server built without big
tables support. The truncation was happening because the
original 64-bits long argument was being cast to a 32-bits
(ha_rows) offset counter.

The solution is to check if the conversing resulted in value
truncation and if so, the offset is set to the maximum possible
value that can fit on the type.

mysql-test/r/limit.result:
  Add test case result for Bug#37075
mysql-test/t/limit.test:
  Add test case for Bug#37075
sql/sql_lex.cc:
  Check for truncation of the offset value. If value was
  truncated, set to the maximum possible value.

The problem is that field names constructed due to wild-card
expansion done inside a stored procedure could point to freed
memory if the expansion was performed after the first call to
the stored procedure.

The problem was solved by patch for Bug#38691. The solution
was to allocate the database, table and field names in the
in the statement memory instead of table memory.

mysql-test/r/sp.result:
  Add test case result for Bug#38823
mysql-test/t/sp.test:
  Add test case for Bug#38823
sql/item.cc:
  Remark that this also impacts wildcard expansion inside SPs.

Select with a "NULL NOT IN" condition containing complex
subselect from the same table as in the outer select failed
with an assertion.


The failure was caused by a concatenation of circumstances:
1) an inner select was optimized by make_join_statistics to use
   the QUICK_RANGE_SELECT access method (that implies an index
   scan of the table);
2) a subselect was independent (constant) from the outer select;
3) a condition was pushed down into inner select.

During the evaluation of a constant IN expression an optimizer
temporary changed the access method from index scan to table
scan, but an engine handler was already initialized for index
access by make_join_statistics. That caused an assertion.


Unnecessary index initialization has been removed from
the QUICK_RANGE_SELECT::init method (QUICK_RANGE_SELECT::reset
reinvokes this initialization).


mysql-test/r/subselect3.result:
  Added test case for bug #37894.
mysql-test/t/subselect3.test:
  Added test case for bug #37894.
sql/opt_range.cc:
  Bug #37894: Assertion in init_read_record_seq in handler.h line 1444
  
  Unnecessary index initialization has been removed from
  the QUICK_RANGE_SELECT::init method (QUICK_RANGE_SELECT::reset
  reinvokes this initialization).

            with COALESCE and JOIN

The server returned to a client the VARBINARY column type
instead of the DATE type for a result of the COALESCE,
IFNULL, IF, CASE, GREATEST or LEAST functions if that result
was filesorted in an anonymous temporary table during
the query execution.

For example:
  SELECT COALESCE(t1.date1, t2.date2) AS result
    FROM t1 JOIN t2 ON t1.id = t2.id ORDER BY result;


To create a column of various date/time types in a
temporary table the create_tmp_field_from_item() function
uses the Item::tmp_table_field_from_field_type() method
call. However, fields of the MYSQL_TYPE_NEWDATE type were
missed there, and the VARBINARY columns were created
by default.
Necessary condition has been added.


mysql-test/r/metadata.result:
  Added test case for bug #39283.
mysql-test/t/metadata.test:
  Added test case for bug #39283.
sql/sql_select.cc:
  Bug #39283: Date returned as VARBINARY to client for queries
              with COALESCE and JOIN
  
  To create a column of various date/time types in a
  temporary table the create_tmp_field_from_item() function
  uses the Item::tmp_table_field_from_field_type() method
  call. However, fields of the MYSQL_TYPE_NEWDATE type were
  missed there, and the VARBINARY columns were created
  by default.
  Necessary condition has been added.

 - fixed an unitialized memory read
 - fixed a compilation warning
 - added a suppression for FC9 x86_64

mysql-test/valgrind.supp:
  Bug #3214: added a suppression for FC9 x86_64
sql/item_func.cc:
  Bug #32124
   - fixed an unitialized memory read
   - fixed a compilation warning

           derived table cause crash

When a multi-UPDATE command fails to lock some table, and
subsequently succeeds, the tables need to be reopened if
they were altered. But the reopening procedure failed for
derived tables.

Extra cleanup has been added.


mysql-test/r/lock_multi.result:
  Added test case for bug #38499.
mysql-test/t/lock_multi.test:
  Added test case for bug #38499.
sql/sql_union.cc:
  Bug#38499: flush tables and multitable table update with
             derived table cause crash
  
  Obsolete assertion has been removed.
sql/sql_update.cc:
  Bug#38499: flush tables and multitable table update with
             derived table cause crash
  
  Extra cleanup for derived tables has been added:
  1) unit.cleanup(),
  2) unit->reinit_exec_mechanism().

 Fixed the handling of system variable retrieval
in prepared statements : added a cleanup method
that clears up the cache and restores the 
original scope of the variable (which is overwritten
at fix_fields()).

sql/item_func.cc:
  ug #32124: 
   - preserve the requested variable scope
   - clean up the cache and restore the variable
     scope for prepared statements.
sql/item_func.h:
  Bug #32124: preserve the requested variable scope

TRIGGERS.SQL_MODE, EVENTS.SQL_MODE, TRIGGERS.DEFINER:
field type is changed to VARCHAR.


mysql-test/r/information_schema.result:
  result fix
mysql-test/r/show_check.result:
  result fix
mysql-test/suite/funcs_1/r/is_columns_is.result:
  result fix
mysql-test/suite/funcs_1/r/is_events.result:
  result fix
mysql-test/suite/funcs_1/r/is_triggers.result:
  result fix
sql/sql_show.cc:
  TRIGGERS.SQL_MODE, EVENTS.SQL_MODE, TRIGGERS.DEFINER:
  field type is changed to VARCHAR.

The problem was that PACK_KEYS and MAX_ROWS clause in ALTER TABLE did not trigger
table reconstruction.
The fix is to rebuild a table if PACK_KEYS or MAX_ROWS are specified.


mysql-test/r/alter_table.result:
  test result
mysql-test/t/alter_table.test:
  test case
sql/sql_table.cc:
  The problem was that PACK_KEYS and MAX_ROWS clause in ALTER TABLE did not trigger
  table reconstruction.
  The fix is to rebuild a table if PACK_KEYS or MAX_ROWS are specified.

Fixed a compilation warning

Hide "Table doesn't exist" errors if the table belongs to a merge table.


mysql-test/r/merge.result:
  result fix
mysql-test/t/merge.test:
  test case
sql/sql_base.cc:
  Hide "Table doesn't exist" errors if the table belongs to a merge table.

The problem: table_open_method is not calculated properly if '*' is used in 'select'
The fix: added table_open_method calculation for such case


mysql-test/r/information_schema.result:
  test result
mysql-test/t/information_schema.test:
  test case
sql/sql_show.cc:
  The problem: table_open_method is not calculated properly if '*' is used in 'select'
  The fix: added table_open_method calculation for such case

upgrade from <=5.0.46 to >=5.0.48

Problem: 'check table .. for upgrade' doesn't detect 
incompatible collation changes made in 5.0.48.

Fix: check for incompatible collation changes.


sql/handler.cc:
  Fix for bug#39585: innodb and myisam corruption after binary 
  upgrade from <=5.0.46 to >=5.0.48
    - check for incompatible collation changes made in 5.0.48:
  bug #29461
    latin7_general_ci
    latin7_general_cs
    latin7_estonian_cs
    latin2_hungarian_ci
    koi8u_general_ci
    cp1251_ukrainian_ci
    cp1250_general_ci
  bug #29499, bug #27562
    ascii_general_ci

                  
The code to get read the value of a system variable was extracting its value 
on PREPARE stage and was substituting the value (as a constant) into the parse tree.
Note that this must be a reversible transformation, i.e. it must be reversed before
each re-execution.
Unfortunately this cannot be reliably done using the current code, because there are
other non-reversible source tree transformations that can interfere with this
reversible transformation.
Fixed by not resolving the value at PREPARE, but at EXECUTE (as the rest of the 
functions operate). Added a cache of the value (so that it's constant throughout
the execution of the query). Note that the cache also caches NULL values.
Updated an obsolete related test suite (variables-big) and the code to test the 
result type of system variables (as per bug 74).

mysql-test/extra/rpl_tests/rpl_insert_id.test:
  Bug #32124: removed ambiguous testcase
mysql-test/r/innodb_data_home_dir_basic.result:
  Bug #32124: fixed wrong test case
mysql-test/r/innodb_flush_method_basic.result:
  Bug #32124: fixed wrong test case
mysql-test/r/ps_11bugs.result:
  Bug #32124: test case
mysql-test/r/ssl_capath_basic.result:
  Bug #32124: fixed wrong test case
mysql-test/r/ssl_cipher_basic.result:
  Bug #32124: fixed wrong test case
mysql-test/r/variables.result:
  Bug #32124: system vars are shown as such in EXPLAIN EXTENDED, not as constants.
mysql-test/suite/rpl/r/rpl_insert_id.result:
  Bug #32124: removed ambiguous testcase
mysql-test/t/ps_11bugs.test:
  Bug #32124: test case
sql/item.cc:
  Bug #32124: placed the code to convert string to longlong or double 
  to a function (so that it can be reused)
sql/item.h:
  Bug #32124: placed the code to convert string to longlong or double 
  to a function (so that it can be reused)
sql/item_func.cc:
  Bug #32124: moved the evaluation of system variables at runtime (val_xxx).
sql/item_func.h:
  Bug #32124: moved the evaluation of system variables at runtime (val_xxx).
sql/set_var.cc:
  Bug #32124: removed the code that calculated the system variable's value 
  at PREPARE
sql/set_var.h:
  Bug #32124: removed the code that calculated the system variable's value 
  at PREPARE
tests/mysql_client_test.c:
  Bug #32124 : removed the reading of the system variable, because its max
  length is depended on the system charset and client charset and can't be
  easily calculated.