Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE
- Code cleanup
- Query plan change is due to s/ha_rows JOIN_TAB::read_time/double JOIN_TAB::read_time/

- Better comments

- Better comments
- Use more appropriate return types for functions
- Provide handling where it was missing.

- for Item-based materialization, it was "materialized subselect"
- for SJ-Materialization it was "subquery%d"

  subqueries that are AND-parts of the WHERE
- Make test results handle same-cost QEP races

  subqueries that are AND-parts of the WHERE
- Update test results

  subqueries that are AND-parts of the WHERE
- Update test results (checked, we get 0 because the subquery is actually
  evaluated by the EXPLAIN).

  non-semijoin materialized subqueries that are AND-parts of the WHERE
- Update test results.

  subqueries that are AND-parts of the WHERE
- First code (needs cleanup).

invoke proper Item_int constructor.

- Add Item_in_subselect::get_identifier() that returns subquery's id
- Change select_describe() to produce output in new format
- Update test results (checked)

of which satisfied the ORDER BY clause).

mysql-test/r/union.result:
  Update test results (checked, it should be "DEPENDENT SUBQUERY" as it will actually run subquery's join multiple times. There is no reason to do that but executor will still do it)

Fix for the PBXT copy of subselect.test.

Bug#48623: Multiple subqueries are optimized incorrectly
      
The function setup_semijoin_dups_elimination() has a major loop that
goes through every table in the JOIN object. Usually, there is a normal
"plus one" increment in the for loop that implements this, but each semijoin
nest is treated as one entity and there is another increment that skips past
the semijoin nest to the next table in the JOIN object. However, when
combining these two increments, the next joined table is skipped, and if that
happens to be the start of another semijoin nest, the correct processing
for that nest will not be carried out.

mysql-test/r/subselect_sj.result:
  Added test results for bug#48623
mysql-test/r/subselect_sj_jcl6.result:
  Added test results for bug#48623
mysql-test/t/subselect_sj.test:
  Added test case for bug#48623
sql/opt_subselect.cc:
  Omitted the "plus one" increment in the for loop, added "plus one"
  in the remaining switch case, fixed coding style issue in remaining
  increment operations.

Fix two problems:
1. Let optimize_semijoin_nests() reset sj_nest->sjmat_info irrespectively  
   of value of optimizer_flag. We need this in case somebody has turned optimization
   off between reexecutions of the same statement.

2. Do not pull out constant tables out of semi-join nests. The problem is that pullout 
   operation is not undoable, and if a table is constant because it is 1/0-row table it 
   may cease to be constant on the next execution. Note that tables that are constant
   because of possible eq_ref(const) access will still be pulled out as they are
   considered functionally-dependent.

Bug#48213 Materialized subselect crashes if using GEOMETRY type

The problem occurred because during semi-join a materialized table
was created which contained a GEOMETRY column, which is a specialized
BLOB column.  This caused an segmentation fault because such tables will
have extra columns, and the semi-join code was not prepared for that.

The solution is to disable materialization when Blob/Geometry columns would 
need to be materialized.  Blob columns cannot be used for index look-up 
anyway, so it does not makes sense to use materialization.

This fix implies that it is detected earlier that subquery materialization
can not be used.  The result of that is that in->exist optimization may
be performed for such queries.  Hence, extended query plans for such
queries had to be updated.

mysql-test/r/subselect_mat.result:
  Update extended query plan for subqueries that cannot use materialization
  due to Blobs.
mysql-test/r/subselect_sj.result:
  Updated result file.
mysql-test/r/subselect_sj_jcl6.result:
  Update result file.
mysql-test/t/subselect_sj.test:
  Add test case for Bug#48213 that verifies that semi-join works when subquery select list contain Blob columns.  Also verify that materialization is not
  used.
sql/opt_subselect.cc:
  Disable materialization for semi-join/subqueries when the subquery select list
  contain Blob columns.

BUG#50019: Wrong result for IN-subquery with materialization
- Fix equality substitution in presense of semi-join materialization, lookup and scan variants
  (started off from fix by Evgen Potemkin, then modified it to work in all cases)

2. don't copy st_mysql_plugin structure unnecessary (sizeof hasn't changed)

This patch does three things:
- It adds the possibility to force the execution of top-level [NOT] IN
  subquery predicates via the IN=>EXISTS transformation. This is done by
  setting both optimizer switches partial_match_rowid_merge and
  partial_match_table_scan to "off".
- It adjusts all test cases where the complete optimizer_switch is
  selected because now we have two more switches.
- For those test cases where the plan changes because of the new available
  strategies, we switch off both partial match strategies in order to
  force the "old" IN=>EXISTS strategy. This is done because most of these
  test cases specifically test bugs in this strategy.

sql/opt_subselect.cc:
  Adds the possibility to force the execution of top-level [NOT] IN
  subquery predicates via the IN=>EXISTS transformation. This is done by
  setting both optimizer switches partial_match_rowid_merge and
  partial_match_table_scan to "off".

storage/myisam/mi_create.c:
  Don't set HA_OPTION_NULL_FIELDS if table is not using CHECKSUM as this makes the table incompatible with MySQL.

- Now DELETE IGNORE skips over rows with a foreign key constraints (as it was supposed to do)


mysql-test/r/foreign_key.result:
  Test case for Bug#44987 DELETE IGNORE and FK constraint
mysql-test/t/foreign_key.test:
  Test case for Bug#44987 DELETE IGNORE and FK constraint
sql/sql_delete.cc:
  Firx for Bug#44987 DELETE IGNORE and FK constraint
  Now DELETE IGNORE skips over rows with a foreign key constraints (as it was supposed to do)
  Bug fix inspired by: Moritz Mertinkat

Add some extra error output and code cleanup in an attempt to fix/debug
a rare random testsuite problem in check_warnings, where the exit code
from mysqltest is somehow corrupted inside mysql-test-run.pl.

include/my_global.h:
  Fix compiler warnings on some platforms.
mysql-test/lib/My/SafeProcess.pm:
  Move dereference of $? subprocess exit code closer to where it is generated,
  to make the code more robust and on the chance that this will fix the
  occasional problems in check_warnings we see in Buildbot.
mysql-test/mysql-test-run.pl:
  When check_warnings failed, also log the mysqld server for which it failed.
sql/sql_lex.cc:
  Fix compiler warning about possibly uninitialised value, by rewriting a for()
  loop that is always executed at least once into a do .. while() loop with an
  assert.
sql/table.cc:
  Fix compiler warning about uninitialised value.
storage/federatedx/ha_federatedx.cc:
  Fix uninitialised variable.
storage/maria/ma_delete.c:
  Fix compiler warning about uninitialised value.
storage/maria/ma_loghandler.c:
  Fix compiler warning about uninitialised value.
storage/myisam/ft_stopwords.c:
  Fix compiler warning.
storage/myisam/mi_write.c:
  Fix compiler warning about possibly uninitialised value, by rewriting a while()
  loop that is always executed at least once into a do .. while() loop with an
  assert.
storage/xtradb/btr/btr0cur.c:
  Fix compiler warning about possibly uninitialised value.
support-files/compiler_warnings.supp:
  Fix warning suppression to cover all cases in yassl.
vio/viossl.c:
  Fix compiler warning.

 - Adjust timing in test case, to avoid test failures caused by high load
   on machines and consequent race conditions in the test case.

 - Add another variant of Valgrind suppressions for memory leak in system
   libraries when unloading dynamic object files.


mysql-test/r/information_schema.result:
  Adjust timing to avoid test failures due to races.
mysql-test/t/information_schema.test:
  Adjust timing to avoid test failures due to races.
mysql-test/valgrind.supp:
  Add another variant of valgrind suppression for leak in system libs.

query:
- When subquery is located in ORDER BY, EXPLAIN will run as follows:
  select_describe() will run JOIN::prepare()/optimize() for the subquery;
  then at some point subselect_single_select_engine::prepare() will be called,
  which will create another join and run join->prepare().

  In mainline mysql this is not a problem because subquery's join will be 
  destroyed after the first call. 
  In MariaDB, it won't (table elimination needs to keep JOIN objects around
  for longer in order to know which tables were eliminated when constructing
  EXPLAIN EXTENDED warning).

  Fix the problem of memory leak by calling select_lex->cleanup() in 
  subselect_single_select_engine::prepare().