#At file:///home/ram/mysql/mysql-5.1-bugteam/ based on revid:alexey.kopytov@stripped
3182 Ramil Kalimullin 2009-10-18 [merge]
Fix for bug#47963: Wrong results when index is used
Problem: using null microsecond part in a WHERE condition
(e.g. WHERE date_time_field <= "YYYY-MM-DD HH:MM:SS.0000")
may lead to wrong results due to improper DATETIMEs
comparison in some cases.
Fix: comparing DATETIMEs as strings we must trim trailing 0's
in such cases.
@ mysql-test/r/innodb_mysql.result
Fix for bug#47963: Wrong results when index is used
- test result.
@ mysql-test/t/innodb_mysql.test
Fix for bug#47963: Wrong results when index is used
- test case.
@ sql/item.cc
Fix for bug#47963: Wrong results when index is used
- comparing DATETIMEs as strings we must trim trailing 0's in the
microsecond part to ensure
'YYYY-MM-DD HH:MM:SS.000' == 'YYYY-MM-DD HH:MM:SS'
modified:
mysql-test/r/innodb_mysql.result
mysql-test/t/innodb_mysql.test
sql/item.cc
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result 2009-10-05 13:16:27 +0000
+++ b/mysql-test/r/innodb_mysql.result 2009-10-13 04:43:27 +0000
@@ -2209,4 +2209,46 @@ EXPLAIN SELECT * FROM t1 FORCE INDEX(PRI
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using where
DROP TABLE t1;
+#
+# Bug #47963: Wrong results when index is used
+#
+CREATE TABLE t1(
+a VARCHAR(5) NOT NULL,
+b VARCHAR(5) NOT NULL,
+c DATETIME NOT NULL,
+KEY (c)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00');
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00';
+a b c
+TEST TEST 2009-10-09 00:00:00
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0';
+a b c
+TEST TEST 2009-10-09 00:00:00
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00';
+a b c
+TEST TEST 2009-10-09 00:00:00
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0';
+a b c
+TEST TEST 2009-10-09 00:00:00
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000';
+a b c
+TEST TEST 2009-10-09 00:00:00
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001';
+a b c
+TEST TEST 2009-10-09 00:00:00
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
+a b c
+EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+DROP TABLE t1;
End of 5.1 tests
=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test 2009-07-07 12:52:34 +0000
+++ b/mysql-test/t/innodb_mysql.test 2009-10-13 04:43:27 +0000
@@ -461,4 +461,33 @@ EXPLAIN SELECT * FROM t1 FORCE INDEX(PRI
DROP TABLE t1;
+--echo #
+--echo # Bug #47963: Wrong results when index is used
+--echo #
+CREATE TABLE t1(
+ a VARCHAR(5) NOT NULL,
+ b VARCHAR(5) NOT NULL,
+ c DATETIME NOT NULL,
+ KEY (c)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00');
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00';
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0';
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00';
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0';
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000';
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001';
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
+EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
+DROP TABLE t1;
+
+
--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-13 04:43:27 +0000
@@ -6900,17 +6900,37 @@ int stored_field_cmp_to_item(Field *fiel
/*
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.
+ 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_result->length() == 19)
+ item_length == MAX_DATETIME_WIDTH)
field_tmp.append(" 00:00:00");
- else if (field_type == MYSQL_TYPE_DATETIME &&
- item_result->length() == 10)
- item_result->append(" 00:00:00");
-
+ else if (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);
+ }
+ }
return stringcmp(&field_tmp,item_result);
}
if (res_type == INT_RESULT)
Attachment: [text/bzr-bundle] bzr/ramil@mysql.com-20091018162655-z4dlolfx5s0zem8l.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (ramil:3182) Bug#47963 | Ramil Kalimullin | 18 Oct |
