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.

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

- Merge with 5.3-main

- 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.

 - 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()

pointers.

- Make Mrr_ordered_index_reader() save the rowid across scan interruptions

Also
- Fix compiler warning for setup_buffer_sizes()
- Add commented key_copy/key_restore for better handling of a similar issue
  with index record being destroyed by scan interruption (which causes 
  incorrect evaluation of pushed index condition later on).

Fixed compiler warning

mysql-test/r/named_pipe.result:
  Fixed test results for windows builds
mysql-test/r/shm.result:
  Fixed test results for windows builds
sql/item_cmpfunc.cc:
  Fixed compiler warning about uninitialized "first"

Problem : mtr reports test failure because it sees
[ERROR] mysqld got exception 0x80000003 ;
in the .err file

The exception comes from DBUG_EXECUTE_IF (.. abort())

Fix:  use DBUG_ABORT instead of abort() - it does not throw 
any exceptions.

The reason for failure is that DBUG_EXECUTE_IF in mi_open()
only worked for Unix-formatted file names, due to strstr(name, "/crashed")

The fix change strstr() above to strstr(name, "crashed"), to it can work with 
Windows file names as well.

 
Reason: inconsistent compilation, federatedx is compiled without SAFEMALLOC
flag, while anything else is compiled with SAFEMALLOC.

As a consequence, my_hash_init used inside federatedx initialization does not
provide correct caller info parameters (file, line) , so they are initialized with 
whatever is on stack. When info about allocated memory is output in
COM_DEBUG command, the server crashes trying to output string starting at
0xcccccccccccccccc.

The fix is to remove SAFEMALLOC  preprocessor flags 
from every CMakeLists.txt, except the top-level one.

Also, SAFEMALLOC is not defined by default now, instead
there is WITH_DEBUG_FULL CMake option which adds 
-DSAFEMALLOC to C and C++ flags in debug compilation. 
This option is off by  default, because 
1) Debug C runtime already has heap debugging builtin with 
 overwrite and leak detection
2)safemalloc considerably slows down the tests.


Note also  that 
- SAFEMALLOC is gone  in MySQL5.5
- On Windows, heap related overflows can also be found using free pageheap utility
(that is also part of application verifier). This is even more efficient if there are no other layers 
on top of Windows heap allocator, e.g  it is most efficient with release version.

- Fixed problem with oqgraph and 'make dist'

Note that after this merge we have a problem show in join_outer where we examine too many rows in one specific case (related to BUG#57024).
This will be fixed when mwl#128 is merged into 5.3.

- Moved end_pagecache() to after maria_close()
- Flush page cache before closing files (in maria_pack)
Fixed test suite failure for PBXT

mysql-test/suite/pbxt/r/select.result:
  We mask out the 'rows' column because it may differ from run to run
mysql-test/suite/pbxt/t/select.test:
  We mask out the 'rows' column because it may differ from run to run
storage/maria/maria_chk.c:
  Moved end_pagecache() to after maria_close() to not trigger end-consistency testing of pagecache.
storage/maria/maria_pack.c:
  Moved end_pagecache() to after maria_close() to not trigger end-consistency testing of pagecache.
  flush page cache before closing files.

The assert removed because it do not take into account case when previous and this buffers used for first time but previous buffer was not sent to disk yet (i.e. previous buffer was never sent to disk yet).

The Aria multithread test unlocked.

storage/maria/unittest/ma_pagecache_consist.c:
  Fixed pagecache shutdown.
storage/maria/unittest/ma_pagecache_rwconsist.c:
  Fixed pagecache shutdown.
storage/maria/unittest/ma_test_loghandler_multithread-t.c:
  The Aria multithread test unlocked.
storage/maria/unittest/ma_test_loghandler_pagecache-t.c:
  Fixed pagecache shutdown.