logging_ok:

  Logging to logging@openlogging.org accepted
func_group.result, func_group.test:
  Added a test case for bug #8893.
opt_sum.cc:
  A misplaced initialization for the returned parameter
  prefix_len in the function find_key_for_maxmin caused
  usage of a wrong key prefix by the min/max optimization
  in cases when the matching index was not the first index
  that contained the min/max field.


sql/opt_sum.cc:
  A misplaced initialization for the returned parameter
  prefix_len in the function find_key_for_maxmin caused
  usage of a wrong key prefix by the min/max optimization
  in cases when the matching index was not the first index
  that contained the min/max field.
mysql-test/t/func_group.test:
  Added a test case for bug #8893.
mysql-test/r/func_group.result:
  Added a test case for bug #8893.
BitKeeper/etc/logging_ok:
  Logging to logging@openlogging.org accepted

4 files changed, 58 insertions, 1 deletions

diff --git a/BitKeeper/etc/logging_ok b/BitKeeper/etc/logging_ok
index a37994b7791..a04676ef503 100644
--- a/BitKeeper/etc/logging_ok
+++ b/BitKeeper/etc/logging_ok
@@ -67,6 +67,7 @@ hf@deer.(none)
 hf@deer.mysql.r18.ru
 hf@genie.(none)
 igor@hundin.mysql.fi
+igor@linux.local
 igor@rurik.mysql.com
 ingo@mysql.com
 jan@hundin.mysql.fi
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 304afda7b72..c22d0d3bec8 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -754,3 +754,29 @@ show columns from t2;
 Field	Type	Null	Key	Default	Extra
 f2	datetime			0000-00-00 00:00:00	
 drop table t2, t1;
+CREATE TABLE t1(
+id int PRIMARY KEY,
+a  int,
+b  int,
+INDEX i_b_id(a,b,id),
+INDEX i_id(a,id)
+);
+INSERT INTO t1 VALUES 
+(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
+SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
+MAX(id)
+NULL
+DROP TABLE t1;
+CREATE TABLE t1(
+id int PRIMARY KEY,
+a  int,
+b  int,
+INDEX i_id(a,id),
+INDEX i_b_id(a,b,id)
+);
+INSERT INTO t1 VALUES 
+(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
+SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
+MAX(id)
+NULL
+DROP TABLE t1;
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 1ee812e5c2f..78e7d3c5ed6 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -496,3 +496,33 @@ drop table t2;
 create table t2 select f2 from (select now() f2 from t1) a;
 show columns from t2;
 drop table t2, t1;
+
+#
+# Bug 8893: wrong result for min/max optimization with 2 indexes
+#
+
+CREATE TABLE t1(
+  id int PRIMARY KEY,
+  a  int,
+  b  int,
+  INDEX i_b_id(a,b,id),
+  INDEX i_id(a,id)
+);
+INSERT INTO t1 VALUES 
+  (1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
+SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
+DROP TABLE t1;
+
+# change the order of the last two index definitions
+
+CREATE TABLE t1(
+  id int PRIMARY KEY,
+  a  int,
+  b  int,
+  INDEX i_id(a,id),
+  INDEX i_b_id(a,b,id)
+);
+INSERT INTO t1 VALUES 
+  (1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
+SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
+DROP TABLE t1;
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index 613b2a4aec1..4ab506cc4e1 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -624,7 +624,6 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref,
 {
   if (!(field->flags & PART_KEY_FLAG))
     return 0;                                        // Not key field
-  *prefix_len= 0;
 
   TABLE *table= field->table;
   uint idx= 0;
@@ -637,6 +636,7 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref,
     KEY_PART_INFO *part,*part_end;
     key_part_map key_part_to_use= 0;
     uint jdx= 0;
+    *prefix_len= 0;
     for (part= keyinfo->key_part, part_end= part+keyinfo->key_parts ;
          part != part_end ;
          part++, jdx++, key_part_to_use= (key_part_to_use << 1) | 1)