with temporary tables

There were two problems the test case from this bug was
triggering:

1. JOIN::rollup_init() was supposed to wrap all constant Items
into another object for queries with the WITH ROLLUP modifier
to ensure they are never considered as constants and therefore
are written into temporary tables if the optimizer chooses to
employ them for DISTINCT/GROUP BY handling.

However, JOIN::rollup_init() was called before
make_join_statistics(), so Items corresponding to fields in
const tables could not be handled as intended, which was
causing all kinds of problems later in the query execution. In
particular, create_tmp_table() assumed all constant items
except "hidden" ones to be removed earlier by remove_const()
which led to improperly initialized Field objects for the
temporary table being created. This is what was causing crashes
and valgrind errors in storage engines.

2. Even when the above problem had been fixed, the query from
the test case produced incorrect results due to some
DISTINCT/GROUP BY optimizations being performed by the
optimizer that are inapplicable in the WITH ROLLUP case.

Fixed by disabling inapplicable DISTINCT/GROUP BY optimizations
when the WITH ROLLUP modifier is present, and splitting the
const-wrapping part of JOIN::rollup_init() into a separate
method which is now invoked after make_join_statistics() when
the const tables are already known.

mysql-test/r/olap.result:
  Added a test case for bug #48131.
mysql-test/t/olap.test:
  Added a test case for bug #48131.
sql/sql_select.cc:
  1. Disabled inapplicable DISTINCT/GROUP BY optimizations when
  the WITH ROLLUP modifier is present.
  2. Split the const-wrapping part of JOIN::rollup_init() into a
  separate method.
sql/sql_select.h:
  Added rollup_process_const_fields() declaration.

  subquery returning multiple rows

Error handling was missing when handling subqueires in WHERE 
and when assigning a SELECT result to a @variable.
This caused crash(es). 

Fixed by adding error handling code to both the WHERE 
condition evaluation and to assignment to an @variable.

having clause...

The fix for bug 46184 was not very complete. It was not covering
views using temporary tables and multiple tables in a FROM clause.
Fixed by reverting the fix for 46184 and making a more general
check that is checking at the right execution stage and for all
of the non-supported cases.
Now PROCEDURE ANALYZE on non-top level SELECT is also forbidden.
Updated the analyse.test and subselect.test accordingly.

Queries with nested outer joins may lead to crashes or 
bad results because an internal data structure is not handled
correctly.
The optimizer uses bitmaps of nested JOINs to determine
if certain table can be placed at a certain place in the
JOIN order.
It does maintain a bitmap describing in which JOINs 
last placed table is nested.
When it puts a table it makes sure the bit of every JOIN that
contains the table in question is set (because JOINs can be nested).
It does that by recursively setting the bit for the next enclosing
JOIN when this is the first table in the JOIN and recursively 
resetting the bit if it's the last table in the JOIN.
When it removes a table from the join order it should do the
opposite : recursively unset the bit if it's the only remaining 
table in this join and and recursively set the bit if it's removing
the last table of a JOIN.
There was an error in how the bits was set for the upper levels :
when removing a table it was setting the bit for all the enclosing 
nested JOINs even if there were more tables left in the current JOIN
(which practically means that the upper nested JOINs were not affected).
Fixed by stopping the recursion at the relevant level.

mysql-test/r/join.result:
  Bug #42116: test case
mysql-test/t/join.test:
  Bug #42116: test case
sql/sql_select.cc:
  Bug #41116: don't go up and set the bits if more tables in
  at the current JOIN level

The 'rpl_get_master_version_and_clock' test verifies if the slave I/O 
thread tries to reconnect to master when it tries to get the values of 
the UNIX_TIMESTAMP, SERVER_ID from master under network disconnection. 
So the master server is restarted for making the transient network 
disconnection. Restarting master server can bring two problems as following:

1. The time out error is encountered sporadically. The slave I/O thread tries 
   to reconnect master ten times, which is set in my.cnf. So in the test 
   framework sporadically the slave I/O thread really stoped when it can't 
   reconnect to master in the ten times successfully before the master starts, 
   then the time out error will be encountered while waiting for the slave to 
   start.

2. These warnings and errors are produced in server log file when 
   the slave I/O thread tries to get the values of the UNIX_TIMESTAMP, 
   SERVER_ID from master under the transient network disconnection.

To fix problem 1, increase the master retry count to sixty times, 
so that the slave I/O thread has enough time to reconnect master 
successfully.
To fix problem 2, suppress these warnings and errors by mtr suppression, 
because they are expected.


mysql-test/suite/rpl/t/rpl_get_master_version_and_clock-slave.opt:
  Added the *.opt file for increasing master retry count to 
  sixty times.
mysql-test/suite/rpl/t/rpl_get_master_version_and_clock.test:
  Added mtr suppression for suppressing warnings and errors 
  in server log file.

Bug#46539 Various crashes on INSERT IGNORE SELECT + SELECT FOR UPDATE.

If a transaction was rolled back inside InnoDB due to a deadlock
or lock wait timeout, and the statement had IGNORE clause,
the server could crash at the end of the statement or on shutdown.

This was caused by the error handling infrastructure's attempt to 
ignore a non-ignorable error.

When a transaction rollback request is raised, switch off 
current_select->no_error flag, so that the following error
won't be ignored.

Instead, we could add !thd->is_fatal_sub_stmt_error to
my_message_sql(), but since in write_record() we switch
off no_error, the same approach is used in 
thd_mark_transaction_to_rollback().

@todo: call thd_mark_transaction_to_rollback() from 
handler::print_error(), then we can easily make sure
that the error reported by print_error is not ignored.

mysql-test/r/innodb_lock_wait_timeout_1.result:
  Update results (Bug#46539).
mysql-test/t/innodb_lock_wait_timeout_1.test:
  Add a test case for Bug#46539
sql/sql_class.cc:
  When a transaction rollback request is raised, switch of 
  current_select->no_error flag, so that the following error
  won't be ignored.

test result fix

mysql-test/suite/funcs_1/r/is_columns_mysql.result:
  test result fix
mysql-test/suite/funcs_1/r/is_statistics.result:
  test result fix

Append the description of the 'decode-rows' value for --base64-output argument.

BUG#47073 - valgrind errs, corruption,failed repair of partition,
            low myisam_sort_buffer_size

Fixed race conditions discovered with the provided test case and
stabilized test case.

include/myisam.h:
  Serialize submission of messages from multi-threaded REPAIR.
mysql-test/r/myisam.result:
  REPAIR output highly depend on threads activity. Disabled
  result log to make test case deterministic.
mysql-test/t/myisam.test:
  REPAIR output highly depend on threads activity. Disabled
  result log to make test case deterministic.
storage/myisam/ha_myisam.cc:
  Serialize submission of messages from multi-threaded REPAIR.
storage/myisam/mi_check.c:
  Serialize submission of messages from multi-threaded REPAIR.
storage/myisam/sort.c:
  Only master thread is allowed to detach write cache from
  the share.

"What do you mean, there's a bug? There isn't even code!"

There was some token code for plug-in variables of the SET type,
but clearly this never worked, or was subject to massive bit rot
since. Bug-fixes ... fail-safes ... tests -- fais au mieux, mon chou!

mysys/my_getopt.c:
  SETs set-up should set up a default value, but no min/max bounding.
mysys/typelib.c:
  fail-safe requested by serg: don't try to skip separator when we're
  already at end of string.
sql/sql_plugin.cc:
  check_func_set:
  Initialize error_len as find_set() will only update it on error,
  and we're using the value to see whether an error has occurred (!= 0),
  so we'd better not have a random val in there.
  
  value_ptr:
  There's no guarantee we're handed string lengths, so play it safe!
  Use prepared string lengths where possible for minimum speed gain,
  otherwise determine on the fly!

BUG#41597 - After rename of user, there are additional grants
            when grants are reapplied.

Fixed build failure on Windows. Added missing cast.

sql/sql_acl.cc:
  Fixed build failure on Windows. Added missing cast.

Problem 1:
column_priv_hash uses utf8_general_ci collation
for the key comparison. The key consists of user name,
db name and table name. Thus user with privileges on table t1
is able to perform the same operation on T1
(the similar situation with user name & db name, see acl_cache).
So collation which is used for column_priv_hash and acl_cache
should be case sensitive.
The fix:
replace system_charset_info with my_charset_utf8_bin for
column_priv_hash and acl_cache
Problem 2:
The same situation with proc_priv_hash, func_priv_hash,
the only difference is that Routine name is case insensitive.
So the fix is to use my_charset_utf8_bin for
proc_priv_hash & func_priv_hash and convert routine name into lower
case before writing the element into the hash and
before looking up the key.
Additional fix: mysql.procs_priv Routine_name field collation
is changed to utf8_general_ci.
It's necessary for REVOKE command
(to find a field by routine hash element values).
Note: 
It's safe for lower-case-table-names mode too because
db name & table name are converted into lower case
(see GRANT_NAME::GRANT_NAME).


mysql-test/include/have_case_insensitive_fs.inc:
  test case
mysql-test/r/case_insensitive_fs.require:
  test case
mysql-test/r/grant_lowercase_fs.result:
  test result
mysql-test/r/lowercase_fs_off.result:
  test result
mysql-test/r/ps_grant.result:
  test result
mysql-test/r/system_mysql_db.result:
  changed Routine_name field collation to case insensitive
mysql-test/t/grant_lowercase_fs.test:
  test case
mysql-test/t/lowercase_fs_off.test:
  test case
scripts/mysql_system_tables.sql:
  changed Routine_name field collation to case insensitive
scripts/mysql_system_tables_fix.sql:
  changed Routine_name field collation to case insensitive
sql/sql_acl.cc:
  Problem 1:
  column_priv_hash uses utf8_general_ci collation
  for the key comparison. The key consists of user name,
  db name and table name. Thus user with privileges on table t1
  is able to perform the same operation on T1
  (the similar situation with user name & db name, see acl_cache).
  So collation which is used for column_priv_hash and acl_cache
  should be case sensitive.
  The fix:
  replace system_charset_info with my_charset_utf8_bin for
  column_priv_hash and acl_cache
  Problem 2:
  The same situation with proc_priv_hash, func_priv_hash,
  the only difference is that Routine name is case insensitive.
  So the fix is to use my_charset_utf8_bin for
  proc_priv_hash & func_priv_hash and convert routine name into lower
  case before writing the element into the hash and
  before looking up the key.
  Additional fix: mysql.procs_priv Routine_name field collation
  is changed to utf8_general_ci.
  It's necessary for REVOKE command
  (to find a field by routine hash element values).
  Note: 
  It's safe for lower-case-table-names mode too because
  db name & table name are converted into lower case
  (see GRANT_NAME::GRANT_NAME).

      
If the first argument to GeomFromWKB function is a geometry
field then the function just returns its value.
However in doing so it's not preserving first argument's 
null_value flag and this causes unexpected null value to
be returned to the calling function.
      
Fixed by updating the null_value of the GeomFromWKB function
in such cases (and all other cases that return a NULL e.g.
because of not enough memory for the return buffer).

Problem: involving a spatial index for "non-spatial" queries
(that don't containt MBRXXX() functions) may lead to failed assert.

Fix: don't use spatial indexes in such cases.


mysql-test/r/gis-rtree.result:
  Fix for bug#48258: Assertion failed when using a spatial index
    - test result.
mysql-test/t/gis-rtree.test:
  Fix for bug#48258: Assertion failed when using a spatial index
    - test case.
sql/opt_range.cc:
  Fix for bug#48258: Assertion failed when using a spatial index
    - allow only spatial functions (MBRXXX) for itMBR keyparts.

line 138 when forcing a spatial index

Problem: "Spatial indexes can be involved in the search 
for queries that use a function such as MBRContains() 
or MBRWithin() in the WHERE clause".
Using spatial indexes for JOINs with =, <=> etc.
predicates is incorrect.

Fix: disable spatial indexes for such queries.


mysql-test/r/select.result:
  Fix for bug#47019: Assertion failed: 0, file .\rt_mbr.c, 
  line 138 when forcing a spatial index
    - test result.
mysql-test/t/select.test:
  Fix for bug#47019: Assertion failed: 0, file .\rt_mbr.c, 
  line 138 when forcing a spatial index
    - test case.
sql/sql_select.cc:
  Fix for bug#47019: Assertion failed: 0, file .\rt_mbr.c, 
  line 138 when forcing a spatial index
    - disable spatial indexes for queries which use 
  non-spatial conditions (e.g. NATURAL JOINs).

Removed the verify callback, as it's not needed to verify even self
signed certificates and is a security problem.