for writing and reading

- better comments
- rename variables to better reflect their meaning

- First part of the fix: enable Early NULLs filtering to work when WHERE clause is present

BUG#628785: multi_range_read.cc:430: int DsMrr_impl::dsmrr_init(): Assertion `do_sort_keys || do_rowid_fetch' failed 
- Make Ds_MrrImpl::check_cpk_scan() follow the execution code' logic: don't
  do MRR scans on clustered PK when mrr_sort_keys=off.

- When find_all_keys() checks which table columns are needed for table scan
  that is done before the sorting, it should also analyze pushed index condition.
  This is achieved by remembering/checking pre-index-pushed condition.

- More test coverage

- Relax overly-strict assert added in previous commit
- Run generic MRR testsuite for Maria engine, too.

- Address review feedback, step 1 

- Make testcase stable 

- don't allocate space for rowid buffer when we don't really need it.
- fix buffer iterator

Incorrect limitation on number of parameters removed.

mysql-test/r/subselect_cache.result:
  Test suite added.
mysql-test/t/subselect_cache.test:
  Test suite added.
sql/sql_class.h:
  Iterator changed.
sql/sql_expression_cache.cc:
  Incorrect limitation removed.
  Removing NULL references from subquery parameter list added.

Removed indirect reference in equalities for cache index lookup.

We should use a direct reference because some optimization of the
query may optimize out a condition predicate and if the outer reference
is the only element of the condition predicate the indirect reference
becomes NULL.

We can resolve correctly the indirect reference in
Expression_cache_tmptable::make_equalities because it is called before
optimization of the cached subquery.


mysql-test/r/subquery_cache.result:
  The test suite for the bug added.
mysql-test/t/subquery_cache.test:
  The test suite for the bug added.
sql/sql_expression_cache.cc:
  Removed indirect reference in equalities for cache index lookup.

Added get_tmp_table_item() to cache wrapper as it has all not simple Items (Item_func, Item_field, Item_subquery).

mysql-test/r/subquery_cache.result:
  Tests for bugs fixed.
mysql-test/t/subquery_cache.test:
  Tests for bugs fixed.
sql/item.cc:
  Added get_tmp_table_item() to cache wrapper as it has all not simple Items (Item_func, Item_field, Item_subquery).
sql/item.h:
  Added get_tmp_table_item() to cache wrapper as it has all not simple Items (Item_func, Item_field, Item_subquery).

check_join_cache_usage() by the change:

Revno: 2793
Revision Id: igor@askmonty.org-20091221022615-kx5ieiu0okmiupuc
Timestamp: Sun 2009-12-20 18:26:15 -0800

Backport into MariaDB-5.2 the following:
WL#2771 "Block Nested Loop Join and Batched Key Access Join"

- Let DS-MRR/CPK take advantage of materialized keys, when possible

- Lots of TODO comments
- add mrr_sort_keys flag to @@optimizer_switch
- [from Igor] SQL layer part passes HA_MRR_MATERIALIZED_KEYS flag
- Don't call rnd_pos() many times in a row if sorted rowid buffer
  has the same rowid value for multiple consequive (rowid, range_id) pairs.

was not cleaned up between PS re-executions. The reason was two-fold:
- a merge with mysql-6.0 missed select_union::cleanup() that should
  have cleaned up the temp table, and
- the subclass of select_union used by materialization didn't call
  the base class cleanup() method.

- Merge with current 5.3

- Let "mysqld  --help --verbose" list all optimizer options
- Make it possible to add new @@optimizer_switch flags w/o causing .result 
  changes all over the testsuite:
  = Remove "select @@optimizer_switch" from tests that do not need all switches
  = Move @@optimizer_switch-specific tests to t/optimizer_switch.test