WL#5416: Restructure MTR subquery tests

Affects: Prototype Only   —   Status: Complete   —   Priority: Medium

Restructure the current MTR subquery tests, into a set of general subquery tests
that can be run with different settings of optimizer_switch.

Main goals:
 - Be able to more systematically test subquery execution with different settings
   of optimizer_switch.
 - Be able to incrementally add optimizer_switch sub-variables to a tree
   without having to operate with different result files in different trees.
   (This will make it easier to incrementally add original 6.0 features now
    residining in mysql-next-mr-opt-backporting to mysql-next-mr)
 - Be able have different defaults for optimizer_switch settings in different 
   trees without having to operate with different result files. 
Basic principles for this task:

1. Test cases will be divided into include test files based on which
   optimizer feature is being tested.  This way, the number of
   interesting combination of optimizer_switch settings are reused.
   (E.g., there is no reason to run an include test file under
   different semijoin settings if semijoin does not apply to the test
   cases in the include test file.

2. The include test files should NOT contain any explicit setting of
   optimizer_switch or optimizer_join_cache_level.

3. Each include test file will be included in a set of test files that
   together covers interesting combinations of optimizer_switch
   settings. Similarly, for possible settings of

4. Each test file will include a check that all optimizer switches
   that will be set for this test exists.  If not, the test will be
   skipped.  This ensures that tests are only run for trees where this
   feature exists.  Hence, we will not have to maintain alternative
   result files for trees where this feature does not exist.

5. Test cases that have any extra requirements to the environment
   (e.g., requires a debug build) will be put in special test files
   which only contain other test cases with the exact same

6. It is not part of this task to correct any bugs revealed by running
   existing test cases in new contexts.  Wrong query results will be
   recorded in result files as is.  If test cases fails with error, a
   bug report will be created and the offending query will be

7. It should be possible to run all tests with different storage
   engines.  Hence, test include files should not specify engine type
   when creating tables.  (NB! This principle will not be followed for
   the first iteration of this task. It will just be ensured that all
   tests that ran with a non-default storage engine before will
   continue to do so.)

For the first iteration of this task, the main focus will be to
prepare for being able to turn off "6.0 features", without having to
operate with large differences between result files of different
trees.  Smaller differences in less optimizer relevant result files
will be accepted since the probability of conflicts during merge will
not be very high. When turning off the "6.0 features", new result
files will be recorded, but the changes to these files will be
null-merged to mysql-next-mr-opt-backporting where the features will
still be on.

Skipping tests for settings that are not supported

For each flag of optimizer_switch (e.g., semijon) , there will be an
include file (e.g., have_semijoin.inc) that checks whether this this
flag is supported.  All tests where this flag is supposed to be on,
will include this test.  E.g.:

  --source include/have_semijoin.inc

In order to skip a test when a certain level of
optimizer_join_cache_level is not supported, or the variable is not
supported at all, one will do as follows:

  let $join_cache_level = 1;
  --source include/have_join_cache_level.inc

That is, set the test variable $join_cache_level to the desired level
and call have_join_cache_level.inc.

Turning off settings only if supported

Tests that are to be run with a certain feature off, should be able to
run also when this feature is not supported.  Hence, the flag for this
feature can not be turned off unconditionally.  Instead, one should
do as follows:

  if (`select locate('semijoin', @@optimizer_switch) > 0`) 
    set optimizer_switch='semijoin=off';

Reorganization of subselect*.test files

  - Will be moved to include/subquery.inc
  - Test cases that are affected by semijoin or subquery
    materialization will be moved to other include files (see below)
  - Will run in following variants:
      subq_basic: Will run with all new switches off
      subq_nomat_nosj: Will run with semijoin and materialization off 
      subq_full: Will run with all switches on
  - Will be moved to include/subquery_sj.inc
  - Will be run in following variants:
    subq_sj_basic: Everything off
    subq_sj_mat_nosj: With subquery materialization, but without semijoin
    subq_sj_firstmatch: With semijoin, but only FirstMatch strategy,
    			     not LooseScan and Materialization
    subq_sj_loosescan: With semijoin, but only LooseScan strategy,
    			    not FirstMatch and Materialization 
    subq_sj_mat: With semijoin, but only Materialization strategy, 
    		      not FirstMatch and Loosescan
    subq_sj_full: Everything on (including ICP/MRR)

  - Will be moved to include/subquery_sj_innodb.inc
  - All test cases that does not require InnoDB will be moved to
  - Will be run in following variants:
    subq_sj_innodb_basic: Everything off
    subq_sj_innodb_full: Everything on

  - Will be moved to include/subquery_sj.inc
  - Will be run in following variants:
    subq_mat_basic: Everything off
    subq_mat: With subquery materialization, 
    	      without semijoin, ICP, and MRR
    subq_mat_full.test: Everything on

  - Test cases will be moved to subselect_innodb.test

  - Test cases that are affected by semijoin or subquery
    materialization will be moved to subquery_sj.inc/subquery_mat.inc
  - Rest will be moved to subquery.inc

  - Test cases will be moved to subselect_sj.inc

  Leave it as is.

  - Will be dropped. Is replaced by subq_nomat_nosj.test

  - Will be dropped. Do not think test is testing what it was actually
    intended for. The intention is better covered by subq_mat.