- added join cache indication in EXPLAIN (Extra column).
 - prefer filesort over full scan over 
   index for ORDER BY (because it's faster).
 - when switching from REF to RANGE because
   RANGE uses longer key turn off sort on
   the head table only as the resulting 
   RANGE access is a candidate for join cache
   and we don't want to disable it by sorting
   on the first table only. 


mysql-test/r/archive_gis.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/compress.result:
  bug #27531:
   - join cache in EXPLAIN. 
   - prefer filesort over full scan over index for ORDER BY.
mysql-test/r/ctype_utf8.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/derived.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/distinct.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/func_group.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/func_group_innodb.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/gis.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/greedy_optimizer.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/group_by.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/group_min_max.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/index_merge_myisam.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/information_schema.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/innodb_gis.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/innodb_mysql.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/join.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/join_nested.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/key_diff.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/myisam.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/ndb_condition_pushdown.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/ndb_gis.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/range.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/row.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/select.result:
  bug #27531:
   - join cache in EXPLAIN.
   - prefer filesort over full scan over index for ORDER BY.
mysql-test/r/ssl.result:
  bug #27531:
   - join cache in EXPLAIN.
   - prefer filesort over full scan over index for ORDER BY.
mysql-test/r/ssl_compress.result:
  bug #27531:
   - join cache in EXPLAIN.
   - prefer filesort over full scan over index for ORDER BY.
mysql-test/r/subselect.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/subselect3.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/union.result:
  bug #27531: join cache in EXPLAIN
mysql-test/r/view.result:
  bug #27531: join cache in EXPLAIN
sql/sql_select.cc:
  bug #27531:
   - join cache in EXPLAIN.
   - prefer filesort over full scan over
     index for ORDER BY.
   - disable sorting on the first table only
     when switching from REF to RANGE.

into  magare.gmz:/home/kgeorge/mysql/work/B27531-5.1-opt-after-merge


mysql-test/r/join.result:
  Auto merged
mysql-test/t/join.test:
  Auto merged
sql/sql_select.cc:
  Auto merged

into  magare.gmz:/home/kgeorge/mysql/work/B27531-5.0-opt


mysql-test/t/join.test:
  Auto merged
sql/sql_select.cc:
  Auto merged
mysql-test/r/join.result:
  SCCS merged

When checking for applicability of join cache
we must disable its usage only if there is no
temp table in use.
When a temp table is used we can use join
cache (and it will not make the result-set 
unordered) to fill the temp table. The filesort() 
operation is then applied to the data in the temp 
table and hence is not affected by join cache
usage.
Fixed by narrowing the condition for disabling 
join cache to exclude the case where temp table
is used.


mysql-test/r/join.result:
  Bug #27531: test case
mysql-test/t/join.test:
  Bug #27531: test case
sql/sql_select.cc:
  Bug #27531: 
  Disable join cache only if not using temp table

into  magare.gmz:/home/kgeorge/mysql/work/B27531-5.1-opt


mysql-test/r/cast.result:
  Auto merged
mysql-test/r/join_outer.result:
  Auto merged
mysql-test/t/cast.test:
  Auto merged
mysql-test/t/join_outer.test:
  Auto merged
sql/item_func.cc:
  Auto merged
sql/item_subselect.cc:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_select.h:
  Auto merged
mysql-test/r/subselect.result:
  merge of 5.0-opt -> 5.1-opt
mysql-test/t/subselect.test:
  merge of 5.0-opt -> 5.1-opt

into  mysql.com:/home/gluh/MySQL/Merge/5.0-opt

allow select into out file from I_S if user has FILE privilege
otherwise issue an error


mysql-test/r/outfile.result:
  test result
mysql-test/t/outfile.test:
  test case
sql/sql_parse.cc:
  allow select into out file from I_S if user has FILE privilege
  otherwise issue an error

into  magare.gmz:/home/kgeorge/mysql/autopush/B27807-5.0-opt

into  moonbone.local:/mnt/gentoo64/work/23656-bug-5.0-opt-mysql


mysql-test/r/cast.result:
  Auto merged
mysql-test/t/cast.test:
  Auto merged
sql/item_func.cc:
  Manual merge

into  magare.gmz:/home/kgeorge/mysql/autopush/B27807-5.0-opt


sql/sql_select.cc:
  Auto merged
mysql-test/r/subselect.result:
  merge to 5.0-opt
mysql-test/t/subselect.test:
  merge to 5.0-opt

Non-correlated scalar subqueries may get executed
in EXPLAIN at the optimization phase if they are
part of a right hand sargable expression.
If the scalar subquery uses a temp table to 
materialize its results it will replace the 
subquery structure from the parser with a simple
select from the materialization table.
As a result the EXPLAIN will crash as the 
temporary materialization table is not to be shown
in EXPLAIN at all.
Fixed by preserving the original query structure
right after calling optimize() for scalar subqueries
with temp tables executed during EXPLAIN.


mysql-test/r/subselect.result:
  Bug #27807: test case
mysql-test/t/subselect.test:
  Bug #27807: test case
sql/item_subselect.cc:
  Bug #27807: preserve the join structure
sql/sql_select.cc:
  Bug #27807: introduce initialization function for tmp_join
sql/sql_select.h:
  Bug #27807: introduce initialization function for tmp_join

The generic string to int conversion was used by the Item_func_signed and
the Item_func_unsigned classes to convert DATE/DATETIME values to the
SIGNED/UNSIGNED type. But this conversion produces wrong results for such
values.

Now if the item which result has to be converted can return its result as
longlong then the item->val_int() method is used to allow the item to carry
out the conversion itself and return the correct result.
This condition is checked in the Item_func_signed::val_int() and the
Item_func_unsigned::val_int() functions.


mysql-test/t/cast.test:
  Added a test case for the bug#23656: Wrong conversion result of a DATETIME to integer using CAST function.
mysql-test/r/cast.result:
  Added a test case for the bug#23656: Wrong conversion result of a DATETIME to integer using CAST function.
sql/item_func.cc:
  Bug#23656: Wrong conversion result of a DATETIME to integer using CAST function.
  Now if the item which result has to be converted can return its result as
  longlong then the item->val_int() method is used to allow the item to carry
  out the conversion itself and return the correct result.
  This condition is checked in the Item_func_signed::val_int() and the
  Item_func_unsigned::val_int() functions.

'not exists' optimization is applied.

In fact 'not exists' optimization did not work anymore after the patch
introducing the evaluate_join_record function had been applied.

Corrected the evaluate_join_record function to respect the 'not_exists'
optimization.


mysql-test/r/join_outer.result:
  Added a test case for bug #28188.
mysql-test/t/join_outer.test:
  Added a test case for bug #28188.
sql/sql_select.cc:
  Fixed bug #28188: performance degradation for outer join queries to which
  'not exists' optimization is applied.
  
  Corrected the evaluate_join_record function to respect the 'not_exists'
  optimization.

into  mysql.com:/d2/hf/mrg/mysql-5.1-opt

into  mysql.com:/d2/hf/mrg/mysql-5.1-opt


mysql-test/r/olap.result:
  Auto merged
mysql-test/r/type_datetime.result:
  Auto merged
mysql-test/t/type_datetime.test:
  Auto merged
sql/item_func.h:
  Auto merged
sql/sql_select.cc:
  Auto merged

into  mysql.com:/home/hf/work/27123/my51-27123


mysql-test/r/partition.result:
  Auto merged
mysql-test/t/partition.test:
  Auto merged
sql/field.cc:
  Auto merged
sql/ha_ndbcluster.cc:
  Auto merged
sql/key.cc:
  Auto merged
sql/field.h:
  merging

into  olga.mysql.com:/home/igor/dev-opt/mysql-5.0-opt-bug24856


sql/item_func.h:
  Auto merged
sql/sql_select.cc:
  Auto merged
mysql-test/r/olap.result:
  Manual merge.
mysql-test/t/olap.test:
  Manual merge.

some rollup rows (rows with NULLs for grouping attributes) if GROUP BY
list contained constant expressions.

This happened because the results of constant expressions were not put
in the temporary table used for duplicate elimination. In fact a constant
item from the GROUP BY list of a ROLLUP query can be replaced for an
Item_null_result object when a rollup row is produced . 

Now the JOIN::rollup_init function wraps any constant item referenced in
the GROYP BY list of a ROLLUP query into an Item_func object of a special
class that is never detected as constant item. This ensures creation of
fields for such  constant items in temporary tables and guarantees right
results when the result of the rollup operation first has to be written
into a temporary table, e.g. in the cases when duplicate elimination is
required.  


mysql-test/r/olap.result:
  Added a test case for bug #24856.
mysql-test/t/olap.test:
  Added a test case for bug #24856.
sql/item_func.h:
  Fixed bug #24856: the result set of a ROLLUP query with DISTINCT could lack
  some rollup rows (rows with NULLs for grouping attributes) if GROUP BY
  list contained constant expressions.
  
  Itroduced class Item_func_rollup_const derived from Item_func. The object of
  this class are never detected as constant items.
  We use them for wrapping constant items from the GROUP BY list of any ROLLUP
  query. This wrapping allows us to ensure writing constant items into temporary
  tables whenever the result of the ROLLUP operation has to be written into a
  temporary table, e.g. when ROLLUP is used together with DISTINCT in the SELECT
  list.
sql/sql_select.cc:
  Fixed bug #24856: the result set of a ROLLUP query with DISTINCT could lack
  some rollup rows (rows with NULLs for grouping attributes) if GROUP BY
  list contained constant expressions.
  
  Now the JOIN::rollup_init function wraps any constant item referenced in
  the GROYP BY list of a ROLLUP query into an Item_func object of a special
  class that is never detected as constant item. This ensures creation of
  fields for such  constant items in temporary tables and guarantees right
  results when the result of the rollup operation first has to be written
  into a temporary table, e.g. in the cases when duplicate elimination is
  required.

storage/ndb/src/mgmapi/mgmapi.cpp:
  DBUG_ENTER requires DBUG_CHECK_TIMEDOUT_RET

into  moonbone.local:/mnt/gentoo64/work/16377-bug-5.0-opt-mysql

  The test case for the bug#27590 is altered.


mysql-test/t/type_datetime.test:
  The test case for the bug#27590 is altered.
mysql-test/r/type_datetime.result:
  The test case for the bug#27590 is altered.

into  mysql.com:/d2/hf/mrg/mysql-5.1-opt


BitKeeper/deleted/.del-CMakeLists.txt~1:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
storage/innobase/handler/ha_innodb.cc:
  Auto merged
tests/mysql_client_test.c:
  Auto merged
mysql-test/include/mix1.inc:
  merging
mysql-test/r/innodb_mysql.result:
  merging

into  mysql.com:/d2/hf/mrg/mysql-5.1-opt


BitKeeper/deleted/.del-CMakeLists.txt~1:
  Auto merged
mysql-test/r/type_datetime.result:
  Auto merged
mysql-test/r/windows.result:
  Auto merged
mysql-test/t/windows.test:
  Auto merged
sql/field.cc:
  Auto merged
sql/field.h:
  Auto merged
sql/item.cc:
  Auto merged
sql/item.h:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_subselect.h:
  Auto merged
sql/item_sum.cc:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_yacc.yy:
  Auto merged
storage/innobase/handler/ha_innodb.cc:
  Auto merged
tests/mysql_client_test.c:
  Auto merged
mysql-test/include/mix1.inc:
  merging
mysql-test/r/innodb_mysql.result:
  merging

into  mysql.com:/d2/hf/mrg/mysql-5.0-opt

into  mysql.com:/d2/hf/mrg/mysql-5.0-opt


CMakeLists.txt:
  Auto merged
sql/ha_innodb.cc:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
tests/mysql_client_test.c:
  Auto merged
mysql-test/r/innodb_mysql.result:
  merging
mysql-test/t/innodb_mysql.test:
  merging

into  mysql.com:/d2/hf/mrg/mysql-4.1-opt

into  mysql.com:/d2/hf/mrg/mysql-5.1-opt


mysql-test/r/alter_table.result:
  Auto merged
mysql-test/r/distinct.result:
  Auto merged
mysql-test/r/heap.result:
  Auto merged
mysql-test/r/ps_2myisam.result:
  Auto merged
mysql-test/r/ps_3innodb.result:
  Auto merged
mysql-test/r/ps_4heap.result:
  Auto merged
mysql-test/r/ps_5merge.result:
  Auto merged
mysql-test/r/ps_7ndb.result:
  Auto merged
mysql-test/r/query_cache.result:
  Auto merged
BitKeeper/deleted/.del-CMakeLists.txt~1:
  Auto merged
BitKeeper/deleted/.del-ps_6bdb.result:
  Auto merged
mysql-test/t/alter_table.test:
  Auto merged
mysys/my_error.c:
  Auto merged
sql/field.cc:
  Auto merged
sql/field.h:
  Auto merged
sql/item.cc:
  Auto merged
sql/item.h:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/item_sum.cc:
  Auto merged
sql/key.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/table.cc:
  Auto merged
tests/mysql_client_test.c:
  Auto merged
mysql-test/include/mix1.inc:
  merging
mysql-test/r/group_by.result:
  SCCS merged
mysql-test/r/innodb_mysql.result:
  merging
mysql-test/r/join.result:
  merging
mysql-test/r/subselect.result:
  merging
mysql-test/r/type_datetime.result:
  SCCS merged
mysql-test/r/windows.result:
  SCCS merged
mysql-test/t/group_by.test:
  SCCS merged
mysql-test/t/join.test:
  merging
mysql-test/t/subselect.test:
  merging
mysql-test/t/type_datetime.test:
  merging
mysql-test/t/windows.test:
  SCCS merged
sql/item_timefunc.cc:
  merging
sql/sql_base.cc:
  SCCS merged
storage/innobase/handler/ha_innodb.cc:
  merging

into  gshchepa.loc:/home/uchum/work/bk-trees/mysql-5.0-opt-13191


sql/key.cc:
  Patch to eliminate compilation errors under VC after bug #13191 fix.

into  gshchepa.loc:/home/uchum/work/bk-trees/mysql-5.0-opt-13191


mysql-test/r/innodb_mysql.result:
  SCCS merged
mysql-test/t/innodb_mysql.test:
  SCCS merged
sql/field.cc:
  Merge with 4.1, fix of bug #13191.
sql/field.h:
  Merge with 4.1, fix of bug #13191.
sql/key.cc:
  Merge with 4.1, fix of bug #13191.

into  gshchepa.loc:/home/uchum/work/bk-trees/mysql-4.1-opt-13191

into  gshchepa.loc:/home/uchum/work/bk-trees/mysql-5.0-opt