even in the cases when there existed range/index-merge scans that
were cheaper than the full table scan.
This was a defect/bug of the implementation of mwl #128. 
Now hash join can work not only with full table scan of the joined
table, but also with full index scan, range and index-merge scans.
Accordingly, in the cases when hash join is used the column 'type'
in the EXPLAINs can contain now 'hash_ALL', 'hash_index', 'hash_range'
and 'hash_index_merge'. If hash join is coupled with a range/index_merge
scan then the columns 'key' and 'key_len' contain info not only on
the used hash index, but also on the indexes used for the scan.   

with the test case added by this patch.
The bug cannot be reproduced with the same test case for the main
5.3 tree because the backported fix for bug 59696 masks the 
problem that causes the crash in the mentioned test case. It's not
clear weather this fix masks this problem in all possible cases. 

Anyway the patch for bug 698882 introduced some inconsistent data
structures that could contain indirect references to deleted object.
It happened when two Item_equal objects were merged and the Item_field
list of the second object was joined to such list of the first object.
This operation required adjustment of the backward pointers in 
Item fields from the joined list. However the adjustment was missing 
and this caused crashes in the tree for mwl#128.

Now the backward pointers are set only when Item_equal items are
completely built and are not changed anymore.
 

* declaration in the middle of the block in C file.
* round() is only available in C99.

The patch fixed the following optimizer defect: when performing
substitution for best equal fields into where conditions to be
able to do their evaluations as soon as possible the optimizer 
skipped conditions over views. That could lead to suboptimal 
execution of queries that used views.  
Slightly changed the test case to demonstrate the performance
improvements if this fix.

This bug could manifest itself when hash join over a varchar column
with NULL values in some rows was used. It happened because the
function key_buf_cmp erroneously returned FALSE when one of the joined
key fields was null while the second was not.
Also fixed two other bugs in the functions key_hashnr and key_buf_cmp 
that could possibly lead to wrong results for some queries that
used hash join over several columns with nulls.
Also reverted the latest addition of the test case for bug #45092. It
had been already backported earlier.

This was another bug in the patch for bug 698882. The new
code from this patch did not ensured that substitutions
of fields for best equal fields were performed on all
AND-OR levels. As a result substitutions for best fields
in some predicates that had been used by the range optimizer
were not actually performed while range plans could employ
these substitutions. This could lead to inconsistent data
structures and ultimately to a crash.

The bug was in the code of the patch fixing bug 698882.
With improper casting the method store_key_field::change_source_field
was called for the elements of the array TABLE_REF::key_copy that
were either of a different type or not allocated at all. This caused
crashes in some queries. 

hash join over equi-join conditions without supporting indexes.

of sort_intersect scans.
 

hash join over equi-join conditions without supporting indexes.

(incorrect block size)

(less not-needed copies of key pages)

storage/maria/ma_rkey.c:
  Fixed wrong test if SEARCH_SAVE_BUFF should be set.
  Now we assume that if we are doing HA_READ_KEY_EXACT,
  we don't have to copy the last key buffer
  (in other words, it's not likely this will be followed
  by a read-next call)

Aria and MyISAM in create_internal_tmp_table_from_heap()
(safe, as duplicates are impossible).
This gives a HUGE speed boost!

sql/opt_subselect.cc:
  Fixed problem with wrong recinfo in create_duplicate_weedout_tmp_tabl()
  Tagged the table with 'no_rows' so that when we create the table on disk,
  we only store the index data. This gave us a major speedup!
sql/sql_select.cc:
  create_tmp_table_from_heap() now uses bulk_insert + repair_by_sort
  when creating Aria/MyISAM tables from HEAP tables.
  This gives a HUGE speed boost!
storage/maria/ha_maria.cc:
  Extended bulk_insert() to recreate UNIQUE keys for
  internal temporary tables
storage/maria/ma_open.c:
  Initialize m_info->lock.type properly for temporarly tables
  (needed for start_bulk_insert())
storage/maria/ma_write.c:
  Don't check uniques that are disabled
storage/myisam/ha_myisam.cc:
  Extended bulk_insert() to recreate UNIQUE keys for
  internal temporary tables.

This will also enable us in the future to collect statistics for
writes to internal tmp tables.

sql/handler.h:
  Added ha_write_tmp_row()
sql/opt_subselect.cc:
  ha_write_row -> ha_write_tmp_row
sql/sql_class.h:
  Added ha_write_tmp_row()
sql/sql_select.cc:
  ha_write_row -> ha_write_tmp_row

This makes the keys smaller (no row pointer) and gives us proper errors if we
use the table wrongly.

sql/sql_select.cc:
  Use NO_RECORD for tables that doesn't need row data.
storage/maria/Makefile.am:
  Added ma_norec.c
storage/maria/ma_check.c:
  Added support for NO_RECORD record format (don't store any row data)
storage/maria/ma_norec.c:
  Added support for NO_RECORD record format
storage/maria/ma_open.c:
  Added support for NO_RECORD record format
storage/maria/ma_search.c:
  Added support for 0 size row pointers (used with NO_RECORD)
storage/maria/ma_test1.c:
  Added testing of NO_RECORD record format.
storage/maria/maria_chk.c:
  Added support for NO_RECORD
storage/maria/maria_def.h:
  Added support for NO_RECORD
storage/maria/unittest/ma_test_all-t:
  Added testing of NO_RECORD record format

temptables, not "uniques", that are hash-based keys.

sql/sql_expression_cache.cc:
  Don't set uniques (we don't want or need an unique
  constraint on this table)

This was needed as the old code caused us to have LOTS of duplicate
hash values when used by optimizer.

include/m_ctype.h:
  Made my_hash_sort_bin() exernal
storage/maria/ma_unique.c:
  Better hash for packed numeric data for unique handling.
  This was needed as the old code caused us to have LOTS
  of duplicate hash values when used by optimizer.

mysys/my_handler.c:
  Fixed typo

Made sure that the optimal fields are used by TABLE_REF objects
when building index access keys to joined tables.
Fixed a bug in the template function that sorts the elements of
a list using the bubble sort algorithm. The bug caused poor
performance of the function. Also added an optimization that
skips comparison with the most heavy elements that has been 
already properly placed in the list.
Made the comparison of the fields belonging to the same Item_equal
more granular: fields belonging to the same table are also ordered
according to some rules.

An assertion failure was triggered for a 6-way join query that used two
join buffers.
The failure happened because every call of JOIN_CACHE::join_matching_records
saved and restored status of all tables that were accessed before the table
join_tab. It must do it only for those of them that follow the last table 
using a join buffer.

- Backport testcases
- We have a different fix because we've fixed part of the problem as part of fix for LPBUG#602574.

Backport testcase: BUG#45863 "Assertion failed: (fixed == 0), function fix_fields(), file item.cc, line 4448"
(The fix was backported with subquery code backport)

3150 Olav Sandstaa     2010-05-07
Test cases for the following "ICP for InnoDB" bugs:

Bug#40992 InnoDB: Crash when engine_condition_pushdown is on
Bug#35080 Innodb crash at mem_block_get_len line 72
Bug#41996 multi-table delete crashes server (InnoDB table)
Bug#43448 Server crashes on multi table delete with Innodb

All these bugs are duplicates of either one or both of Bug 43360 or 36981.

(backporting of olav@sun.com-20100226091930-qxvakxmcp6463t5w)

Test case backport for: Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for null-safe operator <=> NULL

(the bug itself was fixed during the subquery code backport)