Bug #45092: join buffer contains two blob columns one of which is used in the key employed to access the joined table

Date: Mon, 01 Nov 2010 15:15:25 -0000
3272 Roy Lyseng        2010-11-01
Bug#52068: Optimizer generates invalid semijoin materialization plan

When MaterializeScan semijoin strategy was used and there were one
or more outer dependent tables before the semijoin tables, the scan
over the materialized table was not properly reset for each row of
the prefix outer tables.

Example: suppose we have a join order:

  ot1 SJ-Mat-Scan(it2 it3)  ot4

Notice that this is called a MaterializeScan, even though there is an
outer table ahead of the materialized tables. Usually a MaterializeScan
has the outer tables after the materialized table, but this is
a special (but legal) case with outer dependent tables both before and
after the materialized table.

For each qualifying row from ot1, a new scan over the materialized
table must be set up. The code failed to do that, so all scans after
the first one returned zero rows from the materialized table.

- Cause: handler::in_range_check_pushed_down was not reset when a 
  command would call handler->idx_cond_push() without later calling
  handler->index_end().
- Fix: reset the variable in handler->reset(), too (like we do with other
  Index Condition Pushdown members).

When stored in a key buffer any varchar field has a length prefix
that always takes 2 bytes.

Added a test case for bug #695442 - a duplicate of bug 694092.

The bug was the result of a bad merge maria-5.2-wl21 -> 5.3.

One of the hash functions employed by the BNLH join algorithm
calculates the the value of hash index for key value utilizing
every byte of the key buffer. To make this calculation valid
one has to ensure that for any key value unused bytes of the 
buffer are filled with with a certain filler. We choose 0 as
a filler for these bytes.

Added an optional boolean parameter with_zerofill to the function
key_copy. If the value of the parameter is TRUE all unused bytes
of the key buffer is filled with 0. 

- Backport the testcase (the fix itself was included with the subquery optimizations backport)

In some cases the function make_cond_for_index() was mistaken
when detecting index only pushdown conditions for a table: 
a pushdown condition that was not index only could be marked
as such.
It happened because the procedure erroneously used the markers
for index only conditions that remained from the calls of
this function that extracted the index conditions for other 
tables.
Fixed by erasing index only markers as soon as they are need
anymore.

Lifted the limitation that hash join could not be used over 
varchar fields with non-binary collation.

- Merge with 5.3-main

set thd->password appropriately also for cases when a user was not found.

- Fixes for the second fix: take into account case where we don't need to save/restore the scan.

Switch from "Disable identical key handling optimization when
  IndexConditionPushdown is used" approach
To 
 an approach where we save/restore index tuple and so can use index condition pushdown.

mwl#21.

It was missing from EXTRA_DIST in Makefile.am.

 - Address Monty's review feedback, portion 3

- Address Monty's review feedback, portion 2

- Better warnings

Analysis:
The assert failed because st_select_lex::print() was called for subqueries
as follows:

Item_subselect::print() ->
  subselect_single_select_engine::print() -> st_select_lex::print()

It was Item_subselect::fix_fields() that set the thd by calling set_thd(),
so when this print() was called before fix_fields(), subselect_engine::thd
was NULL.

Solution:
The patch makes all constructors of all subselect_engine classes to take
a THD parameter. The default subselect_single_select_engine engine is created
early during parse time, in the Item_subselect::init call, so we pass the
correct THD object already at this point.

- Address review feedback: change return type of RANGE_SEQ_IF::next()

(Includes patch for overrun detected by valgrind thanks to previous my_alloca() -> my_malloc() patch)

mysys/thr_lock.c:
  Fixed typo that caused compile failure