MDEV-30007 SIGSEGV in st_select_lex_unit::is_derived_eliminated, runtime error: member access within null pointer of type 'struct TABLE' in st_select_lex_unit::is_derived_eliminated()

Take into account case of a degenerate subquery (like SELECT 1).

Tests with checking metadata or that cannot be run with
the view-protocol are excluded from --view-protocol.
For tests that do not allow the use of an additional connection,
the util connection is disabled with "--disable_service_connection".
Also cases with bugs for --view-protocol are disabled.

SELECT_LEX::first_select()->join is NULL for degenerate derived tables
which are known to have just one row and so were already materialized
by the optimizer.
This commit adds a check for this.

Elimination of unnecessary tables from SQL queries is already present
in MariaDB. But it only works for regular tables and not for derived ones.

Imagine we have a view:
  CREATE VIEW v1 AS SELECT a, b, max(c) AS maxc FROM t1 GROUP BY a, b

Due to "GROUP BY a, b" the values of combinations {a, b} are unique,
and this fact can be treated as like derived table "v1" has a unique key
on fields {a, b}.

Suppose we have a SQL query:
  SELECT t2.* FROM t2 LEFT JOIN v1 ON t2.a=v1.a and t2.b=v1.b

1. Since {v1.a, v1.b} is unique and both these fields are bound to t2,
   "v1" is functionally dependent on t2.
   This means every record of "t2" will be either joined with
   a single record of "v1" or NULL-complemented.
2. No fields of "v1" are present on the SELECT list

These two facts allow the server to completely exclude (eliminate)
the derived table "v1" from the query.

The function mysql_derived_merge() erroneously did not mark newly formed
AND formulas in ON conditions with the flag abort_on_null. As a result
not_null_tables() calculated incorrectly for these conditions. This
could prevent conversion of embedded outer joins into inner joins.

Changed a test case from table_elim.test to preserve the former execution
plan.

MDEV-6066: Merge new defaults from 5.6 and 5.7 (defaults changed, QC can be stopped with no-zero size)

- For INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, table elimination should 
  check which tables are referenced in the ON DUPLICATE KEY UPDATE clause.

Fix two problems in table elimination code:
- Before marking a "value" as bound, check if it is already bound. Marking the 
  same value as bound twice could confuse a module that depends on this value, 
  because Dep_module_XXX use counters to know when they become bound.

- When checking whether field is part of a key, ignore "extended keys" property.

  in greedy_search with LEFT JOINs and unique keys
- Backport the fix for BUG#806524 from MariaDB 5.3

- In MySQL 5.5, print_join() was re-worked to print "FROM dual" when all
  tables are constant. This change didn't work together with table 
  elimination.

set to 'on' by default.

- Make eliminate_tables_for_list() take into account that it is not possible
  to eliminate a table if it is used in the upper-side ON expressions. Example:

    xxx JOIN (t1 LEFT JOIN t2 ON cond ) ON func(t2.columns)

  Here it would eliminate t2 which is not possible because of use of t2.columns.

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.

- Fix for MySQL BUG#52357 added NESTED_JOIN::is_fully_covered() which would 
  not take into account that MariaDB's table elimination could eliminate tables
  from join plan (and so, from join nest).
  Fixed the check in the function to compare post-table-elimination numbers.

Save no-records constant tables in JOIN::const_table_map before we invoke
eliminate_tables(). Failure to do so caused crash when the same table was
marked as constant two times

mysql-test/r/table_elim.result:
  LPBUG#524025 Running RQG outer_join test leads to crash
  - Testcase
mysql-test/t/table_elim.test:
  LPBUG#524025 Running RQG outer_join test leads to crash
  - Testcase
sql/sql_select.cc:
  LPBUG#524025 Running RQG outer_join test leads to crash
  Save no-records constant tables in JOIN::const_table_map before we invoke eliminate_tables(). Failure to do so caused crash when the same table was marked as constant two times.

- When analying multiple equalities, take into account that they 
  may not have a single table field that belongs to one of the tables
  that we're trying to eliminate (and they are not useful for table
  elimination in that case)

mysql-test/r/table_elim.result:
  LPBUG#523593: Running RQG optimizer_no_subquery crashes MariaDB
  - Testcase
mysql-test/t/table_elim.test:
  LPBUG#523593: Running RQG optimizer_no_subquery crashes MariaDB
  - Testcase

include/my_global.h:
  MWL#17: Table elimination: fixes for windows
  - Add ALIGN_MAX_UNIT (assume malloc returns data aligned to this much)
mysql-test/r/table_elim.result:
  MWL#17: Table elimination: fixes for windows
  - Use only lower-case as EXPLAIN [EXTENDED] changes case of table names
    on windows
mysql-test/t/table_elim.test:
  MWL#17: Table elimination: fixes for windows
  - Use only lower-case as EXPLAIN [EXTENDED] changes case of table names
    on windows
sql/opt_table_elimination.cc:
  MWL#17: Table elimination: fixes for windows
  - Add extra alignment-padding-space for stack-allocated buffers.

- Fix a trivial problem when OR-merging two multi-equalities
- Amend testsuite to provide full gcov coverage

mysql-test/r/table_elim.result:
  MWL#17: Table elimination:
  - Amend testsuite to provide full gcov coverage
mysql-test/t/table_elim.test:
  MWL#17: Table elimination:
  - Amend testsuite to provide full gcov coverage

- More test coverage
- Remove unused code

- Add more testcases.
- Fix trivial compile failure
- Remove handling of "column IN (one_element)". This is converted to equality 
  elsewhere

mysql-test/r/table_elim.result:
  MWL#17: Table elimination
  - Add more testcases.
  - Fix trivial compile failure
mysql-test/t/table_elim.test:
  MWL#17: Table elimination
  - Add more testcases
sql/mysqld.cc:
  MWL#17: Table elimination
  - Fix trivial compile failure
sql/opt_table_elimination.cc:
  MWL#17: Table elimination
  - Add more testcases.
  - Remove handling of "column IN (one_element)".This is converted to equality 
    elsewhere

- Correctly handle the case where we have multi-table DELETE and a table
  that we're deleting from looks like it could be eliminated.

- More testcases
- Set correct dependencies for non-bound multi-equalities.

mysql-test/r/table_elim.result:
  MWL#17: Table elimination
  - More testcases
mysql-test/t/table_elim.test:
  MWL#17: Table elimination
  - More testcases
sql/opt_table_elimination.cc:
  MWL#17: Table elimination
  - Set correct dependencies for non-bound multi-equalities.

- More testcases

- More dbug printouts
- More testcases

mysql-test/r/table_elim.result:
  MWL#17: Table elimination
  - More tests
mysql-test/t/table_elim.test:
  MWL#17: Table elimination
  - More tests
sql/opt_table_elimination.cc:
  MWL#17: Table elimination
  - Code cleanup
sql/sql_select.cc:
  MWL#17: Table elimination
  - Code cleanup
sql/sql_select.h:
  MWL#17: Table elimination
  - Code cleanup
sql/table.h:
  MWL#17: Table elimination
  - Code cleanup

- More testcases
- Let add_ft_key() set keyuse->usable

mysql-test/r/table_elim.result:
  MWL#17: Table elimination
  - More testcases
mysql-test/t/table_elim.test:
  MWL#17: Table elimination
  - More testcases
sql/sql_select.cc:
  MWL#17: Table elimination
  - Let add_ft_key() set keyuse->usable

- Do not show eliminated tables in the output of EXPLAIN EXTENDED

- Make elimination work with aggregate functions. The problem was that aggregate functions 
  reported all table bits in used_tables(), and that prevented table elimination. Fixed by 
  making aggregate functions return more correct value from used_tables(). 

mysql-test/r/ps_11bugs.result:
  MWL#17: Table elimination
  - Update test results. The difference is because of Item_ref change: outer references to constants
    are now recognized as constants, too.
mysql-test/r/subselect.result:
  - Update test results. The difference is because of Item_ref change: outer references to constants
    are now recognized as constants, too.
mysql-test/r/table_elim.result:
  MWL#17: Table elimination
  - Check that elimination works in presense of aggreagate functions
mysql-test/t/table_elim.test:
  MWL#17: Table elimination
  - Check that elimination works in presense of aggreagate functions
sql/item.h:
  MWL#17: Table elimination
  - Add Item_ref::const_item() which calls (*ref)->const_item(). Before this diff Item_ref used the 
    default implementation of const_item(){ return used_tables()==0; }. This is no longer true, as 
    COUNT(*) now has used_tables()==0 but const_item()==FALSE.
sql/item_sum.cc:
  MWL#17: Table elimination
  - Make Item_sum() and it descendants not to return all bits in used_tables(). This is needed 
    because otherwise table elimination can't work in presense of aggregate functions
  - COUNT(*) now has used_tables()==0 and const_item()==FALSE. Had to change 
    Item_ref::const_item() to account for this.
sql/item_sum.h:
  MWL#17: Table elimination
  - Add comments

- First code. Elimination works for simple cases, passes the testsuite.
- Known issues:
  = No elimination is done for aggregate functions.
  = EXPLAIN EXTENDED shows eliminated tables (I think it better not)
  = No benchmark yet
  = The code needs some polishing.

mysql-test/r/table_elim.result:
  MWL#17: Table elimination
  - Testcases
mysql-test/t/table_elim.test:
  MWL#17: Table elimination
  - Testcases
sql/sql_select.cc:
  MWL#17: Table elimination
sql/sql_select.h:
  MWL#17: Table elimination
  - Added JOIN_TAB::eliminated (is JOIN_TAB the best place to store this flag?)
sql/table.h:
  MWL#17: Table elimination
  - ADded NESTED_JOIN::n_tables. We need to have the number of real tables remaining in an outer join nest.