List:Commits« Previous MessageNext Message »
From:Martin Hansson Date:March 16 2010 4:19pm
Subject:bzr commit into mysql-pe branch (martin.hansson:3975) Bug#47762
View as plain text  
#At file:///data0/martin/bzr/bug47762/6.0bt/ based on revid:mattias.jonsson@stripped

 3975 Martin Hansson	2010-03-16 [merge]
      Merge of fix for Bug#47762. A test in func_group now works correctly.

    added:
      mysql-test/include/min_null_cond.inc
    modified:
      mysql-test/r/func_group.result
      mysql-test/r/group_min_max.result
      mysql-test/t/func_group.test
      mysql-test/t/group_min_max.test
      sql/field.h
      sql/opt_sum.cc
=== added file 'mysql-test/include/min_null_cond.inc'
--- a/mysql-test/include/min_null_cond.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/min_null_cond.inc	2010-03-16 14:51:00 +0000
@@ -0,0 +1,49 @@
+--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a = NULL;
+SELECT MIN( a ) FROM t1 WHERE a = NULL;
+
+--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a <> NULL;
+SELECT MIN( a ) FROM t1 WHERE a <> NULL;
+
+--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a > NULL;
+SELECT MIN( a ) FROM t1 WHERE a > NULL;
+
+--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a < NULL;
+SELECT MIN( a ) FROM t1 WHERE a < NULL;
+
+--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
+SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
+
+--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
+
+--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
+
+--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
+
+--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
+SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
+
+--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a IS NULL;
+SELECT MIN( a ) FROM t1 WHERE a IS NULL;

=== modified file 'mysql-test/r/func_group.result'
--- a/mysql-test/r/func_group.result	2009-12-23 10:53:54 +0000
+++ b/mysql-test/r/func_group.result	2010-03-16 16:18:48 +0000
@@ -1788,10 +1788,6 @@ id	select_type	table	type	possible_keys	
 # 3) Test that subquery materialization is setup for query with
 #    premature optimize() exit due to "Select tables optimized away"
 #
-# NOTE: The result of this query is actually wrong; it should be NULL
-# See BUG#47762. Even so, the test case is still needed to test
-# that the HAVING subquery does not crash the server
-# 
 SELECT MIN(pk)
 FROM t1
 WHERE pk=NULL
@@ -1799,7 +1795,7 @@ HAVING ('m') IN ( 
 SELECT v
 FROM t2);
 MIN(pk)
-2
+NULL
 
 EXPLAIN
 SELECT MIN(pk)
@@ -1809,7 +1805,7 @@ HAVING ('m') IN ( 
 SELECT v
 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
 
 #

=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2010-02-25 13:55:28 +0000
+++ b/mysql-test/r/group_min_max.result	2010-03-16 16:18:48 +0000
@@ -2537,6 +2537,232 @@ a
 1
 2
 DROP TABLE t1;
+# 
+# Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column
+#            for NULL
+#
+## Test for NULLs allowed
+CREATE TABLE t1 ( a INT, KEY (a) );
+INSERT INTO t1 VALUES (1), (2), (3);
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a = NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a = NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a <> NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a <> NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a > NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a > NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a < NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a < NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	No matching min/max row
+SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+x	x	x	x	x	x	x	x	x	Using where; Using index
+SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	No matching min/max row
+SELECT MIN( a ) FROM t1 WHERE a IS NULL;
+MIN( a )
+NULL
+INSERT INTO t1 VALUES (NULL), (NULL);
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a = NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a = NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a <> NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a <> NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a > NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a > NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a < NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a < NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Select tables optimized away
+SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+x	x	x	x	x	x	x	x	x	Using where; Using index
+SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Select tables optimized away
+SELECT MIN( a ) FROM t1 WHERE a IS NULL;
+MIN( a )
+NULL
+DROP TABLE t1;
+## Test for NOT NULLs
+CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY);
+INSERT INTO t1 VALUES (1), (2), (3);
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a = NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a = NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a <> NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a <> NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a > NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a > NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a < NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a < NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	No matching min/max row
+SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+x	x	x	x	x	x	x	x	x	Using where; Using index
+SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE a IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Impossible WHERE
+SELECT MIN( a ) FROM t1 WHERE a IS NULL;
+MIN( a )
+NULL
+DROP TABLE t1;
 End of 5.1 tests
 #
 # WL#3220 (Loose index scan for COUNT DISTINCT)

=== modified file 'mysql-test/t/func_group.test'
--- a/mysql-test/t/func_group.test	2009-11-25 00:34:26 +0000
+++ b/mysql-test/t/func_group.test	2010-03-16 16:18:48 +0000
@@ -1153,10 +1153,6 @@ FROM t2);
 --echo # 3) Test that subquery materialization is setup for query with
 --echo #    premature optimize() exit due to "Select tables optimized away"
 --echo #
---echo # NOTE: The result of this query is actually wrong; it should be NULL
---echo # See BUG#47762. Even so, the test case is still needed to test
---echo # that the HAVING subquery does not crash the server
---echo # 
 SELECT MIN(pk)
 FROM t1
 WHERE pk=NULL

=== modified file 'mysql-test/t/group_min_max.test'
--- a/mysql-test/t/group_min_max.test	2010-02-25 13:55:28 +0000
+++ b/mysql-test/t/group_min_max.test	2010-03-16 16:18:48 +0000
@@ -1054,6 +1054,25 @@ SELECT 1 AS c, b FROM t1 WHERE b IN (1,2
 SELECT a FROM t1 WHERE b=1;
 DROP TABLE t1;
 
+--echo # 
+--echo # Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column
+--echo #            for NULL
+--echo #
+
+--echo ## Test for NULLs allowed
+CREATE TABLE t1 ( a INT, KEY (a) );
+INSERT INTO t1 VALUES (1), (2), (3);
+--source include/min_null_cond.inc
+INSERT INTO t1 VALUES (NULL), (NULL);
+--source include/min_null_cond.inc
+DROP TABLE t1;
+
+--echo ## Test for NOT NULLs
+CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY);
+INSERT INTO t1 VALUES (1), (2), (3);
+--source include/min_null_cond.inc
+DROP TABLE t1;
+
 --echo End of 5.1 tests
 
 

=== modified file 'sql/field.h'
--- a/sql/field.h	2010-02-27 09:25:56 +0000
+++ b/sql/field.h	2010-03-16 16:18:48 +0000
@@ -1,7 +1,7 @@
 #ifndef FIELD_INCLUDED
 #define FIELD_INCLUDED
 
-/* Copyright 2000-2008 MySQL AB, 2008, 2009 Sun Microsystems, Inc.
+/* Copyright (c) 2000, 2010 Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -14,7 +14,7 @@
 
    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
-   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
+   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA */
 
 /*
   Because of the function new_field() all field classes that have static
@@ -57,7 +57,11 @@ public:
   static void operator delete(void *ptr_arg, size_t size) { TRASH(ptr_arg, size); }
 
   uchar		*ptr;			// Position to field in record
-  uchar		*null_ptr;		// Byte where null_bit is
+  /**
+     Byte where the @c NULL bit is stored inside a record. If this Field is a
+     @c NOT @c NULL field, this member is @c NULL.
+  */
+  uchar		*null_ptr;
   /*
     Note that you can use table->in_use as replacement for current_thd member 
     only inside of val_*() and store() members (e.g. you can't use it in cons)
@@ -275,6 +279,9 @@ public:
   inline void set_notnull(my_ptrdiff_t row_offset= 0)
     { if (null_ptr) null_ptr[row_offset]&= (uchar) ~null_bit; }
   inline bool maybe_null(void) { return null_ptr != 0 || table->maybe_null; }
+  /**
+     Signals that this field is NULL-able.
+  */
   inline bool real_maybe_null(void) { return null_ptr != 0; }
 
   enum {

=== modified file 'sql/opt_sum.cc'
--- a/sql/opt_sum.cc	2010-02-25 13:55:28 +0000
+++ b/sql/opt_sum.cc	2010-03-16 16:18:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (C) 2000-2003 MySQL AB
+/* Copyright (c) 2000, 2010 Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -11,7 +11,7 @@
 
    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
-   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
+   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA */
 
 
 /**
@@ -96,7 +96,7 @@ static ulonglong get_exact_record_count(
   @param conds                 WHERE clause
 
   @note
-    This function is only called for queries with sum functions and no
+    This function is only called for queries with aggregate functions and no
     GROUP BY part. This means that the result set shall contain a single
     row only
 
@@ -563,31 +563,57 @@ bool simple_pred(Item_func *func_item, I
 /**
   Check whether a condition matches a key to get {MAX|MIN}(field):.
 
-     For the index specified by the keyinfo parameter, index that
-     contains field as its component (field_part), the function
-     checks whether the condition cond is a conjunction and all its
-     conjuncts referring to the columns of the same table as column
-     field are one of the following forms:
-     - f_i= const_i or const_i= f_i or f_i is null,
-     where f_i is part of the index
-     - field {<|<=|>=|>|=} const or const {<|<=|>=|>|=} field
-     - field between const1 and const2
-
-  @param[in]     max_fl         Set to 1 if we are optimising MAX()
-  @param[in,out] ref            Reference to the structure we store the key
-    value
-  @param[in]     keyinfo        Reference to the key info
-  @param[in]     field_part     Pointer to the key part for the field
-  @param[in]     cond           WHERE condition
-  @param[in,out] key_part_used  Map of matchings parts
-  @param[in,out] range_fl       Says whether including key will be used
-  @param[out]    prefix_len     Length of common key part for the range
-    where MAX/MIN is searched for
+   For the index specified by the keyinfo parameter and an index that
+   contains the field as its component (field_part), the function
+   checks whether 
+
+   - the condition cond is a conjunction, 
+   - all of its conjuncts refer to columns of the same table, and
+   - each conjunct is on one of the following forms:
+     - f_i = const_i or const_i = f_i or f_i IS NULL,
+       where f_i is part of the index
+     - field {<|<=|>=|>|=} const
+     - const {<|<=|>=|>|=} field
+     - field BETWEEN const_1 AND const_2
+
+   As a side-effect, the key value to be used for looking up the MIN/MAX value
+   is actually stored inside the Field object. An interesting feature is that
+   the function will find the most restrictive endpoint by over-eager
+   evaluation of the @c WHERE condition. It continually stores the current
+   endpoint inside the Field object. For a query such as
+
+   @code
+   SELECT MIN(a) FROM t1 WHERE a > 3 AND a > 5;
+   @endcode
+
+   the algorithm will recurse over the conjuction, storing first a 3 in the
+   field. In the next recursive invocation the expression a > 5 is evaluated
+   as 3 > 5 (Due to the dual nature of Field objects as value carriers and
+   field identifiers), which will obviously fail, leading to 5 being stored in
+   the Field object.
+   
+   @param[in]     max_fl         Set to true if we are optimizing MAX(),
+                                 false means we are optimizing %MIN()
+   @param[in, out] ref           Reference to the structure where the function 
+                                 stores the key value
+   @param[in]     keyinfo        Reference to the key info
+   @param[in]     field_part     Pointer to the key part for the field
+   @param[in]     cond           WHERE condition
+   @param[in,out] key_part_used  Map of matchings parts. The function will output
+                                 the set of key parts actually being matched in 
+                                 this set, yet it relies on the caller to 
+                                 initialize the value to zero. This is due 
+                                 to the fact that this value is passed 
+                                 recursively.
+   @param[in,out] range_fl       Says whether endpoints use strict greater/less 
+                                 than.
+   @param[out]    prefix_len     Length of common key part for the range
+                                 where MAX/MIN is searched for
 
   @retval
-    0        Index can't be used.
+    false    Index can't be used.
   @retval
-    1        We can use index to get MIN/MAX value
+    true     We can use the index to get MIN/MAX value
 */
 
 static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, 
@@ -624,17 +650,20 @@ static bool matching_cond(bool max_fl, T
     return 0;                                 // Not operator, can't optimize
 
   bool eq_type= 0;                            // =, <=> or IS NULL
+  bool is_null_safe_eq= FALSE;                // The operator is NULL safe, e.g. <=> 
   bool noeq_type= 0;                          // < or >  
   bool less_fl= 0;                            // < or <= 
-  bool is_null= 0;
-  bool between= 0;
+  bool is_null= 0;                            // IS NULL
+  bool between= 0;                            // BETWEEN ... AND ... 
 
   switch (((Item_func*) cond)->functype()) {
   case Item_func::ISNULL_FUNC:
     is_null= 1;     /* fall through */
   case Item_func::EQ_FUNC:
+    eq_type= TRUE;
+    break;
   case Item_func::EQUAL_FUNC:
-    eq_type= 1;
+    eq_type= is_null_safe_eq= TRUE;
     break;
   case Item_func::LT_FUNC:
     noeq_type= 1;   /* fall through */
@@ -662,6 +691,10 @@ static bool matching_cond(bool max_fl, T
   if (!simple_pred((Item_func*) cond, args, &inv))
     return 0;
 
+  if (!is_null_safe_eq && !is_null &&
+      (args[1]->is_null() || (between && args[2]->is_null())))
+    return FALSE;
+
   if (inv && !eq_type)
     less_fl= 1-less_fl;                         // Convert '<' -> '>' (etc)
 
@@ -712,15 +745,16 @@ static bool matching_cond(bool max_fl, T
       - field {>|>=} const, when searching for MIN
     */
 
-    if (is_null)
+    if (is_null || (is_null_safe_eq && args[1]->is_null()))
     {
       part->field->set_null();
       *key_ptr= (uchar) 1;
     }
     else
     {
-      store_val_in_field(part->field, args[between && max_fl ? 2 : 1],
-                         CHECK_FIELD_IGNORE);
+      /* Update endpoints for MAX/MIN, see function comment. */
+      Item *value= args[between && max_fl ? 2 : 1];
+      store_val_in_field(part->field, value, CHECK_FIELD_IGNORE);
       if (part->null_bit) 
         *key_ptr++= (uchar) test(part->field->is_null());
       part->field->get_key_image(key_ptr, part->length, Field::itRAW);


Attachment: [text/bzr-bundle] bzr/martin.hansson@sun.com-20100316161848-2vprueqvrnke7e38.bundle
Thread
bzr commit into mysql-pe branch (martin.hansson:3975) Bug#47762Martin Hansson16 Mar