bzr push into mysql-trunk branch (gleb.shchepa:3351 to 3352)

[Gleb Shchepa]

 3352 Gleb Shchepa	2011-04-02 [merge]
      auto-merge 5.5-->trunk (bug 11766094)

    modified:
      mysql-test/r/func_group.result
      mysql-test/t/func_group.test
      sql/item.cc
 3351 Jorgen Loland	2011-04-01
      BUG#11882131: OPTIMIZER CHOOSES FILESORT WHEN REVERSE 
                    INDEX SCAN COULD BE USED
      
      Consider the following case:
      
      CREATE TABLE t1 (a INT,KEY (a));
      INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
      SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
      
      This query could have been resolved by GROUP range access
      if it hadn't been for the descending ordering [1]. 
      
      To access this table, covering index scan is first chosen. 
      Later an attempt to avoid sorting is made by calling 
      test_if_skip_sort_order(). Range analysis now decides that
      GROUP range access is the most efficient access method, but
      since this access method cannot produce records in 
      descending order, it is scrapped by 
      test_if_skip_sort_order() before concluding that filesort 
      is required after all. 
      
      In this case, test_if_skip_sort_order() fails to check if 
      the descending ordering can be resolved by scanning the 
      covering index in reverse order instead. Because of this, 
      the resulting execution plan is to 1) scan the index and 2) 
      sort the result instead of simply do 1) scan the index in 
      reverse order.
      
      This patch adds an interesting_order parameter to 
      test_quick_select(). This parameter ensures that only range 
      access plans that can produce rows in requested order are 
      considered.
      
      The gains from this change include:
       1) Optimizer will not spend time to calculate whether or not
          an unusable range access plan is cheap.
       2) Before, if two range access plans P1 and P2 were considered, 
          and P1 could produce the requested ordering but P2 could not,
          P2 would still be returned from test_quick_select() if it was
          cheaper than P1. test_if_skip_sort_order() would then discard
          the range access plan as not usable. With this patch, P2 will
          not be considered, so test_quick_select() will instead return
          the best *usable* plan P1.
       3) Due to #2, the aforementioned deficiency in 
          test_if_skip_sort_order() is no longer an issue: If 
          test_quick_select() returns a range access plan, that plan 
          will be able to resolve the requested ordering.
     @ mysql-test/r/order_by_icp_mrr.result
        BUG#11882131: Changed test output
     @ mysql-test/r/order_by_none.result
        BUG#11882131: Changed test output
     @ sql/item_sum.cc
        Struct ORDER variable "bool asc" replaced with "enum_order order"
     @ sql/opt_range.cc
        Add parameter "interesting_order" to test_quick_select() and make the method only consider range access plans that are able to produce rows in requested order. Also add variable PARAM::order, defining whether the range access plan needs to produce ASC/DESC ordering (or no order)
     @ sql/opt_range.h
        Add method QUICK_SELECT_I::reverse_sort_possible().
     @ sql/sql_lex.cc
        Struct ORDER variable "bool asc" replaced with "enum_order order"
     @ sql/sql_parse.cc
        Struct ORDER variable "bool asc" replaced with "enum_order order"
     @ sql/sql_select.cc
        When calling test_quick_select(): define whether ascending/descending ordering will be required.
     @ sql/sql_update.cc
        Struct ORDER variable "bool asc" replaced with "enum_order order"
     @ sql/table.h
        Struct ORDER variable "bool asc" replaced with "enum_order order"

    modified:
      mysql-test/r/order_by_icp_mrr.result
      mysql-test/r/order_by_none.result
      sql/item_sum.cc
      sql/opt_range.cc
      sql/opt_range.h
      sql/sql_lex.cc
      sql/sql_parse.cc
      sql/sql_select.cc
      sql/sql_update.cc
      sql/table.h
=== modified file 'mysql-test/r/func_group.result'
--- a/mysql-test/r/func_group.result	2011-02-02 09:21:41 +0000
+++ b/mysql-test/r/func_group.result	2011-04-02 19:42:50 +0000
@@ -1737,6 +1737,15 @@ SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',
 SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
 SELECT RELEASE_LOCK('aaaaaaaaaaaaaaaaa');
 #
+# Bug #11766094 - 59132: MIN() AND MAX() REMOVE UNSIGNEDNESS 
+#
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (18446668621106209655);
+SELECT MAX(LENGTH(a)), LENGTH(MAX(a)), MIN(a), MAX(a), CONCAT(MIN(a)), CONCAT(MAX(a)) FROM t1;
+MAX(LENGTH(a))	LENGTH(MAX(a))	MIN(a)	MAX(a)	CONCAT(MIN(a))	CONCAT(MAX(a))
+20	20	18446668621106209655	18446668621106209655	18446668621106209655	18446668621106209655
+DROP TABLE t1;
+#
 End of 5.1 tests
 #
 # Bug#52123 Assertion failed: aggregator == aggr->Aggrtype(),

=== modified file 'mysql-test/t/func_group.test'
--- a/mysql-test/t/func_group.test	2011-02-02 09:21:41 +0000
+++ b/mysql-test/t/func_group.test	2011-04-02 19:42:50 +0000
@@ -1118,6 +1118,16 @@ SELECT RELEASE_LOCK('aaaaaaaaaaaaaaaaa')
 
 --enable_result_log
 
+
+--echo #
+--echo # Bug #11766094 - 59132: MIN() AND MAX() REMOVE UNSIGNEDNESS 
+--echo #
+
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (18446668621106209655);
+SELECT MAX(LENGTH(a)), LENGTH(MAX(a)), MIN(a), MAX(a), CONCAT(MIN(a)), CONCAT(MAX(a)) FROM t1;
+DROP TABLE t1;
+
 --echo #
 --echo End of 5.1 tests
 

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2011-03-30 07:42:03 +0000
+++ b/sql/item.cc	2011-04-02 19:42:50 +0000
@@ -7601,7 +7601,7 @@ String *Item_cache_int::val_str(String *
   DBUG_ASSERT(fixed == 1);
   if (!has_value())
     return NULL;
-  str->set(value, default_charset());
+  str->set_int(value, unsigned_flag, default_charset());
   return str;
 }
 

No bundle (reason: useless for push emails).