List:Commits« Previous MessageNext Message »
From:Martin Hansson Date:February 25 2010 10:42am
Subject:bzr commit into mysql-5.1-bugteam branch (martin.hansson:3354) Bug#47762
View as plain text  
#At file:///data0/martin/bzr/bug47762/5.1bt/ based on revid:magne.mahre@stripped

 3354 Martin Hansson	2010-02-25
      Bug#47762: Incorrect result from MIN() when WHERE tests NOT
      NULL column for NULL
      
      The optimization to read MIN() and MAX() values from an
      index did not properly handle comparisons with NULL
      values. Fixed by giving up the particular optimization step
      if there are non-NULL safe comparisons with NULL values, as 
      the result is NULL anyway.
     @ sql/field.h
        Bug#47762: Only commenting

    added:
      mysql-test/include/min_null_cond.inc
    modified:
      mysql-test/r/group_min_max.result
      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-02-25 10:42:43 +0000
@@ -0,0 +1,81 @@
+--echo # Field to the left
+
+--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;
+
+# Test deactivated, this will be fixed by Bug#49504
+
+#--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);
+--echo # NULL to the left
+
+--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 NULL = a;
+SELECT MIN( a ) FROM t1 WHERE NULL = a;
+
+# Test deactivated, this will be fixed by Bug#49504
+
+#--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 NULL <> a;
+#SELECT MIN( a ) FROM t1 WHERE NULL <> a;
+
+--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 NULL > a;
+SELECT MIN( a ) FROM t1 WHERE NULL > a;
+
+--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 NULL < a;
+SELECT MIN( a ) FROM t1 WHERE NULL < a;
+
+--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 NULL <=> a;
+SELECT MIN( a ) FROM t1 WHERE NULL <=> a;
+
+--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 (SELECT a FROM t1 WHERE a < 0) = a;
+SELECT MIN( a ) FROM t1 WHERE (SELECT a FROM t1 WHERE a < 0) = a;

=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2010-02-09 08:53:13 +0000
+++ b/mysql-test/r/group_min_max.result	2010-02-25 10:42:43 +0000
@@ -2537,4 +2537,302 @@ 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);
+# Field to the left
+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	Using where; Using index
+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
+# NULL to the left
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE NULL = a;
+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 NULL = a;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE NULL > a;
+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 NULL > a;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE NULL < a;
+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 NULL < a;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE NULL <=> a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Using where; Using index
+SELECT MIN( a ) FROM t1 WHERE NULL <=> a;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE (SELECT a FROM t1 WHERE a < 0) = a;
+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 (SELECT a FROM t1 WHERE a < 0) = a;
+MIN( a )
+NULL
+INSERT INTO t1 VALUES (NULL), (NULL);
+# Field to the left
+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	Using where; Using index
+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
+# NULL to the left
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE NULL = a;
+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 NULL = a;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE NULL > a;
+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 NULL > a;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE NULL < a;
+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 NULL < a;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE NULL <=> a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	x	x	x	x	x	x	Using where; Using index
+SELECT MIN( a ) FROM t1 WHERE NULL <=> a;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE (SELECT a FROM t1 WHERE a < 0) = a;
+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 (SELECT a FROM t1 WHERE a < 0) = a;
+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);
+# Field to the left
+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 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
+# NULL to the left
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE NULL = a;
+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 NULL = a;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE NULL > a;
+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 NULL > a;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE NULL < a;
+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 NULL < a;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE NULL <=> a;
+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 NULL <=> a;
+MIN( a )
+NULL
+EXPLAIN
+SELECT MIN( a ) FROM t1 WHERE (SELECT a FROM t1 WHERE a < 0) = a;
+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 (SELECT a FROM t1 WHERE a < 0) = a;
+MIN( a )
+NULL
+DROP TABLE t1;
 End of 5.1 tests

=== modified file 'mysql-test/t/group_min_max.test'
--- a/mysql-test/t/group_min_max.test	2010-02-09 08:53:13 +0000
+++ b/mysql-test/t/group_min_max.test	2010-02-25 10:42:43 +0000
@@ -1054,4 +1054,24 @@ 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-01-21 17:20:24 +0000
+++ b/sql/field.h	2010-02-25 10:42:43 +0000
@@ -55,7 +55,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 NULL bit is stored inside a record. If this Field is a NOT 
+     NULL field, this member is 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)
@@ -261,6 +265,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 NULLable.
+  */
   inline bool real_maybe_null(void) { return null_ptr != 0; }
 
   enum {

=== modified file 'sql/opt_sum.cc'
--- a/sql/opt_sum.cc	2010-02-09 08:53:13 +0000
+++ b/sql/opt_sum.cc	2010-02-25 10:42:43 +0000
@@ -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
 
@@ -559,31 +559,44 @@ 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
+
+   @note NULL handling: If the comparison is between a field and NULL, the
+   return value is always false, even for NULL-safe comparisons.
+
+   @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, 
@@ -622,8 +635,8 @@ static bool matching_cond(bool max_fl, T
   bool eq_type= 0;                            // =, <=> or IS NULL
   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:
@@ -695,9 +708,15 @@ static bool matching_cond(bool max_fl, T
     *key_part_used|= (key_part_map) 1 << (part - keyinfo->key_part);
   }
 
+  /*
+    We pre-evaluate the condition on the keypart. Calls to is_null() below
+    depend on this for some Item's. This way we also avoid getting an if-test
+    with side effects.
+  */
+  bool possibly_null_valued= cond->val_int() == 0;
   if (org_key_part_used != *key_part_used ||
       (is_field_part && 
-       (between || eq_type || max_fl == less_fl) && !cond->val_int()))
+       (between || eq_type || max_fl == less_fl) && possibly_null_valued))
   {
     /*
       It's the first predicate for this part or a predicate of the
@@ -715,6 +734,9 @@ static bool matching_cond(bool max_fl, T
     }
     else
     {
+      Item *value= args[between && max_fl ? 2 : 1];
+      if (value->is_null())
+        return FALSE;
       store_val_in_field(part->field, args[between && max_fl ? 2 : 1],
                          CHECK_FIELD_IGNORE);
       if (part->null_bit) 
@@ -737,13 +759,19 @@ static bool matching_cond(bool max_fl, T
   }
   else if (eq_type)
   {
-    if ((!is_null && !cond->val_int()) ||
+    if ((!is_null && possibly_null_valued) ||
         (is_null && !test(part->field->is_null())))
      return 0;                       // Impossible test
   }
   else if (is_field_part)
     *range_fl&= ~(max_fl ? NO_MIN_RANGE : NO_MAX_RANGE);
-  return 1;  
+
+  if (between && (args[1]->is_null() || args[2]->is_null()))
+    return FALSE;
+  if (!is_null && args[1]->is_null())
+    return FALSE;
+
+  return TRUE;
 }
 
 


Attachment: [text/bzr-bundle] bzr/martin.hansson@sun.com-20100225104243-poss0h9m6aaj5hta.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (martin.hansson:3354) Bug#47762Martin Hansson25 Feb
Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3354)Bug#47762Jørgen Løland1 Mar
  • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3354)Bug#47762Martin Hansson2 Mar