summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/include/min_null_cond.inc49
-rw-r--r--mysql-test/r/group_min_max.result226
-rw-r--r--mysql-test/t/group_min_max.test19
-rw-r--r--sql/field.h13
-rw-r--r--sql/opt_sum.cc96
5 files changed, 369 insertions, 34 deletions
diff --git a/mysql-test/include/min_null_cond.inc b/mysql-test/include/min_null_cond.inc
new file mode 100644
index 00000000000..fcfaad43e57
--- /dev/null
+++ b/mysql-test/include/min_null_cond.inc
@@ -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;
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 745fe11704c..604e93af4a0 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -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
diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
index df385dfa7d2..6aec0e3677f 100644
--- a/mysql-test/t/group_min_max.test
+++ b/mysql-test/t/group_min_max.test
@@ -1054,4 +1054,23 @@ SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b;
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
diff --git a/sql/field.h b/sql/field.h
index 55604193687..5bfcc9f21a6 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -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 {
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index 70d6d0a5b17..8a3fe6c3ae8 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -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(TABLE_LIST *tables)
@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,57 @@ bool simple_pred(Item_func *func_item, Item **args, bool *inv_order)
/**
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,
@@ -620,17 +646,20 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo,
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 +687,10 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo,
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 +741,16 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo,
- 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);
View Lorry Controller Status