This bug is a consequence of the fix in the function add_ref_to_table_cond
for LP bug 826935 that turned out to be not quite correct: it tried to AND
the same generated condition with two different other conditions.
This patch creates a copy of the generated condition if the condition needs
to be ANDed with two different items.

Analysis:
The cause of the bug was that the method
subselect_rowid_merge_engine::partial_match()
was not designed for re-execution within the
same query. Specifically, it didn't cleanup
the bitmap of matching keys after completion.

The test query requires double execution of
the IN predicate because it first checks the
predicate as a constant condition. The second
execution during regular execution used the bitmap
of matching keys produced by the first execution
instead of starting with a clean one.

Solution:
Cleanup the bitmap of matching keys at the end of
the partial matching procedure.

(correction for the previous patch).

Changed a test case to make its result set platform independent.

- convert_subq_to_jtbm() should always restore the used arena.

Analysis:
The cause of the bug was the changed meaning of
subselect_partial_match_engine::has_covering_null_row.
Previously it meant that there is row with NULLs in
all nullable fields of the materialized subquery table.
Later it was changed to mean a row with NULLs in all
fields of this table.

At the same time there was a shortcut in
subselect_rowid_merge_engine::partial_match() that
detected a special case where:
- there is no match in any of the columns with NULLs, and
- there is no NULL-only row that covers all columns with
  NULLs.
With the change in the meaning of has_covering_null_row,
the condition that detected this special case was incomplete.
This resulted in an incorrect FALSE, when the result was a
partial match.

Solution:
Expand the condition that detected the special case with the
correct test for the existence of a row with NULL values in
all columns that contain NULLs (a kind of parially covering
NULL-row).

in the function best_access_path revealed another bug: currently 
table scans on NULL keys used for NOT IN subqueries cannot work 
together with employment of join caches for inner tables of these 
subqueries. Otherwise the result can be wrong as it could be seen 
with the result of the test case constructed for bug #37894 
in the file subselect3_jcl6.result.

of the 5.3 code line after a merge with 5.2 on 2010-10-28
in order not to allow the cost to access a joined table to be equal
to 0 ever.

Expanded data sets for many test cases to get the same execution plans
as before.

BUG#861147: Assertion `fixed == 1' failed in Item_func_eq::val_int() with semijoin + materialization
- convert_subq_to_jtbm() didn't check that subuqery optimization was successful. If it wasn't (in this
  example because of @@max_join_size violation), it would proceed further and eventually crash when 
  trying to execute the un-optimized subquery.

- The problem was that JOIN::save/restore_query_plan() did not save/restore parts of 
  the query plan that are located inside SJ_MATERIALIZATION_INFO structures. This could
  cause parts of one plan to be used with another, which led get_best_combination() to
  constructing non-sensical join plans (and crash).
  Fixed by saving/restoring SJM parts of the query plans.

- check_and_do_in_subquery_rewrites() will not set SUBS_MATERIALIZATION flag when it 
  records that the subquery predicate is to be converted into semi-join. 
  If convert_join_subqueries_to_semijoins() later decides not to convert to semi-join,
  let it set SUBS_MATERIALIZATION flag, if appropriate.

make Item::send() request a date according to the current SQL mode limitations.

The problem: in an uncorrelated subquery, JOIN execution code may make 
irreversible cleanups after it has been executed (because the subquery 
won't be executed again). In particular, JTBM nests and their injected 
IN-equalities will be invalidated (the materialized table will be dropped
and TABLE structure freed).
Solution: don't walk into Item_subselect if represents an uncorrelated 
subselect that has already been executed. The idea is that the subselect
will not be executed again (calling Item_subselect_xxx::val_int() will fetch
the cached value), so it should not matter what is inside the subselect.

Added suppression message for valgrind failure found on OpenSuSE 11.1

mysql-test/mysql-test-run.pl:
  Fix for issue found in buildbot where mysqld.*.err files was missing
  Patch by Kristian Nielsen
mysql-test/valgrind.supp:
  Added suppression message for valgrind failure found on OpenSuSE 11.1
sql/mysqld.cc:
  Added missing space to comment

Now the aria_log_control_file and log file is not touched if aria files are not written to.

storage/maria/ma_checkpoint.c:
  Fixed wrong test if log file has increased.
  Don't reset pages_to_flush_before_next_checkpoint as this would cause the checkpoint to happen at next checkpoint interval

- are_tables_local() failed to recognize the fact that OUTER_REF_TABLE_BIT is ok 
  for SJ-Materialization. This caused zero-length ref access to be constructed, which
  led to an assert.

on Windows, kill <connection> can and almost always will return client error 2013 ("Lost connection...") on the killed connection,
On the server side, if connection is "sleeping" KILL will close the socket, thus  socket error on client is expected.

Analysis:
The crash is a result of the same cause as all similar
bugs (lp:827416, lp:718763, lp:778413, lp:806943,
lp:611690). The general pattern is that some optimization
requires the evaluation of some condition (e.g. the WHERE
clause), and this condition contains a subquery, such that
the subquery itself requires a temporary table for its
execution. During the subquery execution the original
tables in the FROM clause are replaced by the temporary
table needed for the final GROUP or ORDER operation. All
this happens during optimization of the outer query. Later
when EXPLAIN is run for the subquery, explain attempts to
print the name of the tables in the FROM clause, but it
finds there a temporary table without a corresponding
TABLE_LIST object. The attempt to print the name of a
NULL table list results in a crash.

Solution:
This patch extends the fix to bug lp:702301, and dissalows
constant substitution of aggregate functions if the filter
condition used to check MIN/MAX keys is an expensive condition.

- Make get_post_group_estimate() take into account semi-join materialization nests.

Threat ER_CONNECTION_KILLED same as ER_SERVER_SHUTDOWN in replication (to get rid of a possible warning in error log)

sql/slave.cc:
  Threat ER_CONNECTION_KILLED same as ER_SERVER_SHUTDOWN

One can set @@global.max_user_connections to -1 to block anyone, except SUPER user, to login.
If max_user_connection is 0, one can't change it without a restart (needed to get user connections counting to work correctly)


mysql-test/r/system_mysql_db.result:
  Changed max_user_connections to handle negative numbers.
mysql-test/r/user_limits-2.result:
  New test case that one can't change max_user_connection if it was 0
mysql-test/r/user_limits.result:
  Fixed wrong error messages.
mysql-test/r/variables.result:
  Store / restore max_user_connections (needed as there is now a --master.opt file that changes it)
mysql-test/t/subselect_mat_cost-master.opt:
  Enable slow query log (as this test found some errors in slow query logging)
mysql-test/t/user_limits-2.test:
  New test case that one can't change max_user_connection if it was 0
mysql-test/t/user_limits-master.opt:
  Set max_user_connections (as one can't change it if it was 0)
mysql-test/t/user_limits.test:
  Test max_user_connections -1
mysql-test/t/variables-master.opt:
  Set max_user_connections (as one can't change it if it was 0)
mysql-test/t/variables.test:
  Set/restore max_user_connections
scripts/Makefile.am:
  Add a text message to mysql_fix_privilege_tables.sql that it's automaticly generated
scripts/mysql_system_tables.sql:
  Change max_user_connections to signed
scripts/mysql_system_tables_fix.sql:
  Change max_user_connections to signed
sql/item_func.cc:
  Change SHOW_INT to be signed.
  (Needed for max_user_connections and it's probably a bug that it was not originally signed)
sql/log.cc:
  Remove some code that was not needed (All these variables are reset at start of query)
sql/mysql_priv.h:
  Made max_user_connections signed.
  Added max_user_connections_checking
sql/mysqld.cc:
  Added max_user_connections_checking so that we know if max_user_connections was 0 at startup
  (Which means that we will not do connection counting for accounts that don't have user resource limits)
  Set thd->start_utime at same time as thr_create_utime. (Before start_utime could be < thr_create_utime which lead to wrong query counting)
sql/set_var.cc:
  Don't allow one to change 'max_user_connections' if it was 0 at startup.
sql/sql_acl.cc:
  Change user_connection counting to be negative.
sql/sql_connect.cc:
  If max_user_connections is < 0 then only SUPER user can login.
  Fixed wrong variable names for error messages.
  Fixed wrong initial value for questions.
  Set thd->start_utime and thd->thr_create_utime at startup.  Needed to get time_out_user_resource_limits() to work.
sql/sql_show.cc:
  SHOW_INT is now negative
sql/sql_yacc.yy:
  Support negative values for MAX_USER_CONNECTIONS
sql/structs.h:
  Make user connect counting work with signed numbers.

- Fix wrong loop bounds in setup_semijoin_dups_elimination()

Reset 'examined_rows_count' in union to not count same rows twice

mysql-test/r/subselect_mat_cost.result:
  Test also slow query logging
mysql-test/t/subselect_mat_cost.test:
  Test also slow query logging
sql/sql_union.cc:
  Reset 'examined_rows_count' in union to not count same rows twice