#At file:///Users/martinhansson/bzr/bug47925/5.1bt/ based on revid:joro@stripped
3200 Martin Hansson 2009-10-27
Bug#47925: regression of range optimizer and date comparison in 5.1.39!
When a query was using a DATE or DATETIME value formatted
using any other separator characters beside hyphen '-', a
query with a greater-or-equal '>=' condition matching only
the greatest value in an indexed column, the result was
empty if index range scan was employed.
The range optimizer got a new feature between 5.1.38 and
5.1.39 that changes a greater-or-equal condition to a
greater-than if the value matching that in the query was not
present in the table. But the value comparison function
compared the dates as strings instead of dates.
The bug was fixed by splitting the function
get_date_from_str in two: One part that parses and does
error checking. This function is now visible outside the
module. The old get_date_from_str now calls the new
function.
@ mysql-test/r/range.result
Bug#47925: Test result
@ mysql-test/r/select.result
Bug#47925: Wrong test result turned correct. When an endpoint is missing, the only way to be sure that a value is in the range is if it is equal to the present endpoint.
@ mysql-test/t/range.test
Bug#47925: Test case
@ sql/item.cc
Bug#47925: Fix + some edit on the comments
@ sql/item.h
Bug#47925: Changed function signature
@ sql/item_cmpfunc.cc
Bug#47925: Split function in two
@ sql/item_cmpfunc.h
Bug#47925: Declaration of new function
@ sql/opt_range.cc
Bug#47925: Added THD to function call
@ sql/time.cc
Bug#47925: Added microsecond comparison
modified:
mysql-test/r/range.result
mysql-test/r/select.result
mysql-test/t/range.test
sql/item.cc
sql/item.h
sql/item_cmpfunc.cc
sql/item_cmpfunc.h
sql/opt_range.cc
sql/time.cc
=== modified file 'mysql-test/r/range.result'
--- a/mysql-test/r/range.result 2009-10-16 20:19:51 +0000
+++ b/mysql-test/r/range.result 2009-10-27 11:09:55 +0000
@@ -1406,4 +1406,167 @@ INSERT INTO t1 VALUES (1), (NULL);
SELECT * FROM t1 WHERE a <> NULL and (a <> NULL or a <= NULL);
a
DROP TABLE t1;
+#
+# Bug#47925: regression of range optimizer and date comparison in 5.1.39!
+#
+CREATE TABLE t1 ( a DATE, KEY ( a ) );
+CREATE TABLE t2 ( a DATETIME, KEY ( a ) );
+# Make optimizer choose range scan
+INSERT INTO t1 VALUES ('2009-09-22'), ('2009-09-22'), ('2009-09-22');
+INSERT INTO t1 VALUES ('2009-09-23'), ('2009-09-23'), ('2009-09-23');
+INSERT INTO t2 VALUES ('2009-09-22 12:00:00'), ('2009-09-22 12:00:00'),
+('2009-09-22 12:00:00');
+INSERT INTO t2 VALUES ('2009-09-23 12:00:00'), ('2009-09-23 12:00:00'),
+('2009-09-23 12:00:00');
+# DATE vs DATE
+EXPLAIN
+SELECT * FROM t1 WHERE a >= '2009/09/23';
+id select_type table type possible_keys key key_len ref rows Extra
+X X X range a a X X X X
+SELECT * FROM t1 WHERE a >= '2009/09/23';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '20090923';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= 20090923;
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009-9-23';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009.09.23';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009:09:23';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+# DATE vs DATETIME
+EXPLAIN
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+id select_type table type possible_keys key key_len ref rows Extra
+X X X range a a X X X X
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '20090923';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= 20090923;
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009-9-23';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009.09.23';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009:09:23';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+# DATETIME vs DATETIME
+EXPLAIN
+SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00';
+id select_type table type possible_keys key key_len ref rows Extra
+X X X range a a X X X X
+SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '20090923120000';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= 20090923120000;
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009-9-23 12:00:00';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009.09.23 12:00:00';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009:09:23 12:00:00';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+# DATETIME vs DATE
+EXPLAIN
+SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
+id select_type table type possible_keys key key_len ref rows Extra
+X X X range a a X X X X
+SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '20090923000000';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= 20090923000000;
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009-9-23 00:00:00';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009.09.23 00:00:00';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009:09:23 00:00:00';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+DROP TABLE t1, t2;
End of 5.1 tests
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result 2009-10-22 09:40:15 +0000
+++ b/mysql-test/r/select.result 2009-10-27 11:09:55 +0000
@@ -4192,7 +4192,7 @@ Warning 1292 Incorrect datetime value: '
set SQL_MODE=DEFAULT;
select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'
-1
+0
Warnings:
Warning 1292 Truncated incorrect datetime value: ''
select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';
=== modified file 'mysql-test/t/range.test'
--- a/mysql-test/t/range.test 2009-10-16 20:19:51 +0000
+++ b/mysql-test/t/range.test 2009-10-27 11:09:55 +0000
@@ -1181,4 +1181,67 @@ INSERT INTO t1 VALUES (1), (NULL);
SELECT * FROM t1 WHERE a <> NULL and (a <> NULL or a <= NULL);
DROP TABLE t1;
+--echo #
+--echo # Bug#47925: regression of range optimizer and date comparison in 5.1.39!
+--echo #
+CREATE TABLE t1 ( a DATE, KEY ( a ) );
+CREATE TABLE t2 ( a DATETIME, KEY ( a ) );
+
+--echo # Make optimizer choose range scan
+INSERT INTO t1 VALUES ('2009-09-22'), ('2009-09-22'), ('2009-09-22');
+INSERT INTO t1 VALUES ('2009-09-23'), ('2009-09-23'), ('2009-09-23');
+
+INSERT INTO t2 VALUES ('2009-09-22 12:00:00'), ('2009-09-22 12:00:00'),
+ ('2009-09-22 12:00:00');
+INSERT INTO t2 VALUES ('2009-09-23 12:00:00'), ('2009-09-23 12:00:00'),
+ ('2009-09-23 12:00:00');
+
+--echo # DATE vs DATE
+--replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X
+EXPLAIN
+SELECT * FROM t1 WHERE a >= '2009/09/23';
+SELECT * FROM t1 WHERE a >= '2009/09/23';
+SELECT * FROM t1 WHERE a >= '20090923';
+SELECT * FROM t1 WHERE a >= 20090923;
+SELECT * FROM t1 WHERE a >= '2009-9-23';
+SELECT * FROM t1 WHERE a >= '2009.09.23';
+SELECT * FROM t1 WHERE a >= '2009:09:23';
+
+--echo # DATE vs DATETIME
+--replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X
+EXPLAIN
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+SELECT * FROM t2 WHERE a >= '20090923';
+SELECT * FROM t2 WHERE a >= 20090923;
+SELECT * FROM t2 WHERE a >= '2009-9-23';
+SELECT * FROM t2 WHERE a >= '2009.09.23';
+SELECT * FROM t2 WHERE a >= '2009:09:23';
+
+--echo # DATETIME vs DATETIME
+--replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X
+EXPLAIN
+SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00';
+SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00';
+SELECT * FROM t2 WHERE a >= '20090923120000';
+SELECT * FROM t2 WHERE a >= 20090923120000;
+SELECT * FROM t2 WHERE a >= '2009-9-23 12:00:00';
+SELECT * FROM t2 WHERE a >= '2009.09.23 12:00:00';
+SELECT * FROM t2 WHERE a >= '2009:09:23 12:00:00';
+
+--echo # DATETIME vs DATE
+--replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X
+EXPLAIN
+SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
+SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
+SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
+SELECT * FROM t1 WHERE a >= '20090923000000';
+SELECT * FROM t1 WHERE a >= 20090923000000;
+SELECT * FROM t1 WHERE a >= '2009-9-23 00:00:00';
+SELECT * FROM t1 WHERE a >= '2009.09.23 00:00:00';
+SELECT * FROM t1 WHERE a >= '2009:09:23 00:00:00';
+
+DROP TABLE t1, t2;
+
--echo End of 5.1 tests
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2009-10-13 04:43:27 +0000
+++ b/sql/item.cc 2009-10-27 11:09:55 +0000
@@ -6866,72 +6866,60 @@ void resolve_const_item(THD *thd, Item *
}
/**
- Compare the value stored in field, with the original item.
+ Compare the value stored in field with the expression from the query.
- @param field field which the item is converted and stored in
- @param item original item
+ @param field Field which the Item is stored in after conversion
+ @param item Original expression from query
- @return Return an integer greater than, equal to, or less than 0 if
- the value stored in the field is greater than, equal to,
- or less than the original item
+ @return Returns an integer greater than, equal to, or less than 0 if
+ the value stored in the field is greater than, equal to,
+ or less than the original Item. A 0 may also be returned if
+ out of memory.
@note We only use this on the range optimizer/partition pruning,
because in some cases we can't store the value in the field
without some precision/character loss.
*/
-int stored_field_cmp_to_item(Field *field, Item *item)
+int stored_field_cmp_to_item(THD *thd, Field *field, Item *item)
{
-
Item_result res_type=item_cmp_type(field->result_type(),
item->result_type());
if (res_type == STRING_RESULT)
{
char item_buff[MAX_FIELD_WIDTH];
char field_buff[MAX_FIELD_WIDTH];
- String item_tmp(item_buff,sizeof(item_buff),&my_charset_bin),*item_result;
+
+ String item_tmp(item_buff,sizeof(item_buff),&my_charset_bin);
String field_tmp(field_buff,sizeof(field_buff),&my_charset_bin);
- enum_field_types field_type;
- item_result=item->val_str(&item_tmp);
+ String *item_result= item->val_str(&item_tmp);
+ /*
+ Some implementations of Item::val_str(String*) actually modify
+ the field Item::null_value, hence we can't check it earlier.
+ */
if (item->null_value)
return 0;
- field->val_str(&field_tmp);
+ String *field_result= field->val_str(&field_tmp);
- /*
- If comparing DATE with DATETIME, append the time-part to the DATE.
- So that the strings are equally formatted.
- A DATE converted to string is 10 (MAX_DATE_WIDTH) characters,
- and a DATETIME converted to string is 19 (MAX_DATETIME_WIDTH) characters.
- */
- field_type= field->type();
- uint32 item_length= item_result->length();
- if (field_type == MYSQL_TYPE_DATE &&
- item_length == MAX_DATETIME_WIDTH)
- field_tmp.append(" 00:00:00");
- else if (field_type == MYSQL_TYPE_DATETIME)
+ if (field->type() == MYSQL_TYPE_DATE ||
+ field->type() == MYSQL_TYPE_DATETIME)
{
- if (item_length == MAX_DATE_WIDTH)
- item_result->append(" 00:00:00");
- else if (item_length > MAX_DATETIME_WIDTH)
- {
- /*
- We don't store microsecond part of DATETIME in field
- but item_result contains it. As we compare DATETIMEs as strings
- we must trim trailing 0's in item_result's microsecond part
- to ensure "YYYY-MM-DD HH:MM:SS" == "YYYY-MM-DD HH:MM:SS.0000"
- */
- char *end= (char *) item_result->ptr() + item_length - 1;
- /* Trim trailing 0's */
- while (*end == '0')
- end--;
- /* Trim '.' if no microseconds */
- if (*end == '.')
- end--;
- DBUG_ASSERT(end - item_result->ptr() + 1 >= MAX_DATETIME_WIDTH);
- item_result->length(end - item_result->ptr() + 1);
- }
+ enum_mysql_timestamp_type type= MYSQL_TIMESTAMP_ERROR;
+
+ if (field->type() == MYSQL_TYPE_DATE)
+ type= MYSQL_TIMESTAMP_DATE;
+
+ if (field->type() == MYSQL_TYPE_DATETIME)
+ type= MYSQL_TIMESTAMP_DATETIME;
+
+ const char *field_name= field->field_name;
+ MYSQL_TIME field_time, item_time;
+ get_mysql_time_from_str(thd, field_result, type, field_name, &field_time);
+ get_mysql_time_from_str(thd, item_result, type, field_name, &item_time);
+
+ return my_time_compare(&field_time, &item_time);
}
- return stringcmp(&field_tmp,item_result);
+ return stringcmp(field_result, item_result);
}
if (res_type == INT_RESULT)
return 0; // Both are of type int
=== modified file 'sql/item.h'
--- a/sql/item.h 2009-08-28 10:55:59 +0000
+++ b/sql/item.h 2009-10-27 11:09:55 +0000
@@ -3125,4 +3125,4 @@ void mark_select_range_as_dependent(THD
extern Cached_item *new_Cached_item(THD *thd, Item *item);
extern Item_result item_cmp_type(Item_result a,Item_result b);
extern void resolve_const_item(THD *thd, Item **ref, Item *cmp_item);
-extern int stored_field_cmp_to_item(Field *field, Item *item);
+extern int stored_field_cmp_to_item(THD *thd, Field *field, Item *item);
=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc 2009-10-05 05:27:36 +0000
+++ b/sql/item_cmpfunc.cc 2009-10-27 11:09:55 +0000
@@ -637,6 +637,42 @@ int Arg_comparator::set_compare_func(Ite
}
+bool get_mysql_time_from_str(THD *thd, String *str, timestamp_type warn_type,
+ const char *warn_name, MYSQL_TIME *l_time)
+{
+ bool value;
+ int error;
+ enum_mysql_timestamp_type timestamp_type;
+
+ timestamp_type=
+ str_to_datetime(str->ptr(), str->length(), l_time,
+ (TIME_FUZZY_DATE | MODE_INVALID_DATES |
+ (thd->variables.sql_mode &
+ (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE))),
+ &error);
+
+ if (timestamp_type == MYSQL_TIMESTAMP_DATETIME ||
+ timestamp_type == MYSQL_TIMESTAMP_DATE)
+ /*
+ Do not return yet, we may still want to throw a "trailing garbage"
+ warning.
+ */
+ value= FALSE;
+ else
+ {
+ value= TRUE;
+ error= 1; /* force warning */
+ }
+
+ if (error > 0)
+ make_truncated_value_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ str->ptr(), str->length(),
+ warn_type, warn_name);
+
+ return value;
+}
+
+
/**
@brief Convert date provided in a string to the int representation.
@@ -658,43 +694,15 @@ int Arg_comparator::set_compare_func(Ite
@return
converted value. 0 on error and on zero-dates -- check 'failure'
*/
-
-static ulonglong
-get_date_from_str(THD *thd, String *str, timestamp_type warn_type,
- char *warn_name, bool *error_arg)
+static ulonglong get_date_from_str(THD *thd, String *str,
+ timestamp_type warn_type,
+ const char *warn_name, bool *error_arg)
{
- ulonglong value= 0;
- int error;
MYSQL_TIME l_time;
- enum_mysql_timestamp_type ret;
+ int error= get_mysql_time_from_str(thd, str, warn_type, warn_name, &l_time);
+ *error_arg= error;
- ret= str_to_datetime(str->ptr(), str->length(), &l_time,
- (TIME_FUZZY_DATE | MODE_INVALID_DATES |
- (thd->variables.sql_mode &
- (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE))),
- &error);
-
- if (ret == MYSQL_TIMESTAMP_DATETIME || ret == MYSQL_TIMESTAMP_DATE)
- {
- /*
- Do not return yet, we may still want to throw a "trailing garbage"
- warning.
- */
- *error_arg= FALSE;
- value= TIME_to_ulonglong_datetime(&l_time);
- }
- else
- {
- *error_arg= TRUE;
- error= 1; /* force warning */
- }
-
- if (error > 0)
- make_truncated_value_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
- str->ptr(), str->length(),
- warn_type, warn_name);
-
- return value;
+ return TIME_to_ulonglong_datetime(&l_time);
}
=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h 2008-12-12 11:13:11 +0000
+++ b/sql/item_cmpfunc.h 2009-10-27 11:09:55 +0000
@@ -1721,3 +1721,6 @@ inline Item *and_conds(Item *a, Item *b)
}
Item *and_expressions(Item *a, Item *b, Item **org_item);
+
+bool get_mysql_time_from_str(THD *thd, String *str, timestamp_type warn_type,
+ const char *warn_name, MYSQL_TIME *l_time);
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2009-10-23 18:37:57 +0000
+++ b/sql/opt_range.cc 2009-10-27 11:09:55 +0000
@@ -5968,7 +5968,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
switch (type) {
case Item_func::LT_FUNC:
- if (stored_field_cmp_to_item(field,value) == 0)
+ if (stored_field_cmp_to_item(param->thd, field, value) == 0)
tree->max_flag=NEAR_MAX;
/* fall through */
case Item_func::LE_FUNC:
@@ -5983,14 +5983,14 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
case Item_func::GT_FUNC:
/* Don't use open ranges for partial key_segments */
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
- (stored_field_cmp_to_item(field, value) <= 0))
+ (stored_field_cmp_to_item(param->thd, field, value) <= 0))
tree->min_flag=NEAR_MIN;
tree->max_flag= NO_MAX_RANGE;
break;
case Item_func::GE_FUNC:
/* Don't use open ranges for partial key_segments */
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
- (stored_field_cmp_to_item(field,value) < 0))
+ (stored_field_cmp_to_item(param->thd, field, value) < 0))
tree->min_flag= NEAR_MIN;
tree->max_flag=NO_MAX_RANGE;
break;
=== modified file 'sql/time.cc'
--- a/sql/time.cc 2009-06-17 14:56:44 +0000
+++ b/sql/time.cc 2009-10-27 11:09:55 +0000
@@ -969,15 +969,19 @@ calc_time_diff(MYSQL_TIME *l_time1, MYSQ
TIME.second_part is not considered during comparison
*/
-int
-my_time_compare(MYSQL_TIME *a, MYSQL_TIME *b)
+int my_time_compare(MYSQL_TIME *a, MYSQL_TIME *b)
{
my_ulonglong a_t= TIME_to_ulonglong_datetime(a);
my_ulonglong b_t= TIME_to_ulonglong_datetime(b);
if (a_t > b_t)
return 1;
- else if (a_t < b_t)
+ if (a_t < b_t)
+ return -1;
+
+ if (a->second_part > b->second_part)
+ return 1;
+ if (a->second_part < b->second_part)
return -1;
return 0;
Attachment: [text/bzr-bundle] bzr/martin.hansson@sun.com-20091027110955-b2t2op3hej9a8cf0.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)Bug#47925 | Martin Hansson | 27 Oct |
