MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:March 15 2010 4:17pm
Subject:Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3400)
Bug#47762
View as plain text  
Hi Martin,

Ok to push after fixing minor mistakes (see below).

Regards, Evgen.

On 03/15/10 13:12, Martin Hansson wrote:
> #At file:///Users/martin/bzr/bug47762/5.1bt-commit/ based on
> revid:staale.smedseng@stripped
>
>   3400 Martin Hansson	2010-03-15
>        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.
>
>      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-03-15 10:12:17 +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/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-03-15 10:12:17 +0000
> @@ -2537,4 +2537,230 @@ 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
>
> === 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-03-15 10:12:17 +0000
> @@ -1054,4 +1054,23 @@ 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-03-15 10:12:17 +0000
> @@ -1,4 +1,4 @@
> -/* 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
> @@ -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 */
>
>   /*
>     Because of the function new_field() all field classes that have static
> @@ -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 @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)
> @@ -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 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-09 08:53:13 +0000
> +++ b/sql/opt_sum.cc	2010-03-15 10:12:17 +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
>
> @@ -559,31 +559,61 @@ 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
> +
> +   This function has numerous side-effects, the most prominent one being that
If you know other side-effect - you should document it here. Otherwise it's 
better to remove this note as it's confusing.
> +   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.
---
> This behavior was previously undocumented, nor was it
> +   very clearly expressed in the code, so it is not known whether it was
> +   actually intentional.
---
This part isn't needed. Probably I wasn't clear enough when explaining this: 
this behavior is intentional and this is the way this optimization is expected 
to work.
> +
> +
> +   @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,
> @@ -620,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 */
> @@ -658,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)
>
> @@ -708,15 +745,18 @@ static bool matching_cond(bool max_fl, T
>         - field {>|>=} const, when searching for MIN
>       */
>
> -    if (is_null)
> +    Item *value= args[between&&  max_fl ? 2 : 1];
> +    if (is_null || (is_null_safe_eq&&  value->is_null()))
Since you know that you're checking '<=>' you could simply use
args[1]->val_int().
>       {
>         part->field->set_null();
>         *key_ptr= (uchar) 1;
>       }
>       else
>       {
> -      store_val_in_field(part->field, args[between&&  max_fl ? 2 : 1],
> -                         CHECK_FIELD_IGNORE);
> +      if (value->is_null())
you don't need this - you already checked it above.
> +        return FALSE;
> +      /* Update endpoints for MAX/MIN, see function comment. */
> +      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);
>
>
>
>
>
Thread
bzr commit into mysql-5.1-bugteam branch (martin.hansson:3400)Bug#47762Martin Hansson15 Mar
  • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3400)Bug#47762Evgeny Potemkin15 Mar