#At file:///data0/martin/bzr/bug47925/5.1bt/ based on revid:svoj@stripped
3171 Martin Hansson 2009-10-15
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 other separator characters than the most commonly used
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 parsing the date strings and comparing
the integer representation of the dates.
@ mysql-test/r/range.result
Bug#47925: Test result
@ mysql-test/t/range.test
Bug#47925: Test case and the obligatory "End of 5.1 tests"
@ sql/item.cc
Bug#47925: Fix + some edit on the comments
modified:
mysql-test/r/range.result
mysql-test/t/range.test
sql/item.cc
=== modified file 'mysql-test/r/range.result'
--- a/mysql-test/r/range.result 2009-10-09 09:30:40 +0000
+++ b/mysql-test/r/range.result 2009-10-15 12:29:11 +0000
@@ -1398,3 +1398,57 @@ a < 10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 range a a 5 NULL 8 Using where; Using index
DROP TABLE t1, t2, t3;
+#
+# 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
+1 SIMPLE t1 range a a 4 NULL 4 Using where; Using index
+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
+1 SIMPLE t2 range a a 9 NULL 4 Using where; Using index
+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
+1 SIMPLE t2 range a a 9 NULL 4 Using where; Using index
+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
+1 SIMPLE t1 range a a 4 NULL 4 Using where; Using index
+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/t/range.test'
--- a/mysql-test/t/range.test 2009-10-09 09:30:40 +0000
+++ b/mysql-test/t/range.test 2009-10-15 12:29:11 +0000
@@ -1171,3 +1171,42 @@ a < 5 OR
a < 10;
DROP TABLE t1, t2, t3;
+
+--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
+EXPLAIN
+SELECT * FROM t1 WHERE a >= '2009/09/23';
+SELECT * FROM t1 WHERE a >= '2009/09/23';
+
+--echo # DATE vs DATETIME
+EXPLAIN
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+
+--echo # DATETIME vs DATETIME
+EXPLAIN
+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
+EXPLAIN
+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-09-29 02:23:38 +0000
+++ b/sql/item.cc 2009-10-15 12:29:11 +0000
@@ -6866,14 +6866,15 @@ 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
@@ -6889,29 +6890,48 @@ int stored_field_cmp_to_item(Field *fiel
{
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);
- if (item->null_value)
- return 0;
- field->val_str(&field_tmp);
-
+ String *item_result, *field_result;
+ item_result= item->val_str(&item_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 characters, and a DATETIME converted
- to string is 19 characters.
+ Some implementations of Item::val_str(String*) actually modify
+ the field Item::null_value, hence we can't check it earlier.
*/
- field_type= field->type();
- if (field_type == MYSQL_TYPE_DATE &&
- item_result->length() == 19)
- field_tmp.append(" 00:00:00");
- else if (field_type == MYSQL_TYPE_DATETIME &&
- item_result->length() == 10)
- item_result->append(" 00:00:00");
-
- return stringcmp(&field_tmp,item_result);
+ if (item->null_value)
+ return 0;
+ field_result= field->val_str(&field_tmp);
+
+ if (field->type() == MYSQL_TYPE_DATE ||
+ field->type() == MYSQL_TYPE_DATETIME)
+ {
+ MYSQL_TIME field_time_value, item_time_value;
+ int ignored_was_cut;
+ enum_mysql_timestamp_type type;
+ /* We allow some invalid dates. e.g. 1999-02-31 */
+ type= str_to_datetime(field_result->ptr(), field_result->length(),
+ &field_time_value, TIME_FUZZY_DATE,
+ &ignored_was_cut);
+
+ DBUG_ASSERT(type != MYSQL_TIMESTAMP_NONE);
+
+ type= str_to_datetime(item_result->ptr(), item_result->length(),
+ &item_time_value, TIME_FUZZY_DATE,
+ &ignored_was_cut);
+
+ DBUG_ASSERT(type != MYSQL_TIMESTAMP_NONE);
+
+ ulonglong field_integer_value, item_integer_value;
+ field_integer_value= TIME_to_ulonglong_datetime(&field_time_value);
+ item_integer_value= TIME_to_ulonglong_datetime(&item_time_value);
+
+ if (field_integer_value > item_integer_value)
+ return 1;
+ if (field_integer_value < item_integer_value)
+ return -1;
+ return 0;
+ }
+ return stringcmp(field_result, item_result);
}
if (res_type == INT_RESULT)
return 0; // Both are of type int
Attachment: [text/bzr-bundle] bzr/martin.hansson@sun.com-20091015122911-w82r45ln4au2rrkn.bundle
