#At file:///mnt/sda7/work/mysql-5.1-bugteam/ based on revid:aelkin@stripped
3414 Gleb Shchepa 2010-03-22
Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type
(Original patch by Sinisa Milivojevic)
The YEAR(4) value of 2000 was equal to the "bad" YEAR(4) value of 0000.
The get_year_value() function has been modified to not adjust bad
YEAR(4) value to 2000.
@ mysql-test/r/type_year.result
Test case for bug #49910.
@ mysql-test/t/type_year.test
Test case for bug #49910.
@ sql/item_cmpfunc.cc
Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type
The get_year_value() function has been modified to not adjust bad
YEAR(4) value to 2000.
modified:
mysql-test/r/type_year.result
mysql-test/t/type_year.test
sql/item_cmpfunc.cc
=== modified file 'mysql-test/r/type_year.result'
--- a/mysql-test/r/type_year.result 2010-01-13 10:28:42 +0000
+++ b/mysql-test/r/type_year.result 2010-03-22 08:33:25 +0000
@@ -309,4 +309,36 @@ yyyy c4
2069 2069
DROP TABLE t2, t4;
#
+# Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type
+#
+CREATE TABLE t1 (y YEAR NOT NULL, s VARCHAR(4));
+INSERT INTO t1 (s) VALUES ('bad');
+Warnings:
+Warning 1364 Field 'y' doesn't have a default value
+INSERT INTO t1 (y, s) VALUES (0, 0), (2000, 2000), (2001, 2001);
+SELECT * FROM t1 ta, t1 tb WHERE ta.y = tb.y;
+y s y s
+0000 bad 0000 bad
+0000 0 0000 bad
+0000 bad 0000 0
+0000 0 0000 0
+2000 2000 2000 2000
+2001 2001 2001 2001
+SELECT * FROM t1 WHERE t1.y = 0;
+y s
+0000 bad
+0000 0
+SELECT * FROM t1 WHERE t1.y = 2000;
+y s
+2000 2000
+SELECT ta.y AS ta_y, ta.s, tb.y AS tb_y, tb.s FROM t1 ta, t1 tb HAVING ta_y = tb_y;
+ta_y s tb_y s
+0000 bad 0000 bad
+0000 0 0000 bad
+0000 bad 0000 0
+0000 0 0000 0
+2000 2000 2000 2000
+2001 2001 2001 2001
+DROP TABLE t1;
+#
End of 5.1 tests
=== modified file 'mysql-test/t/type_year.test'
--- a/mysql-test/t/type_year.test 2010-01-13 10:28:42 +0000
+++ b/mysql-test/t/type_year.test 2010-03-22 08:33:25 +0000
@@ -134,5 +134,21 @@ SELECT * FROM t4 WHERE yyyy > 123;
DROP TABLE t2, t4;
--echo #
+--echo # Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type
+--echo #
+
+CREATE TABLE t1 (y YEAR NOT NULL, s VARCHAR(4));
+INSERT INTO t1 (s) VALUES ('bad');
+INSERT INTO t1 (y, s) VALUES (0, 0), (2000, 2000), (2001, 2001);
+
+SELECT * FROM t1 ta, t1 tb WHERE ta.y = tb.y;
+SELECT * FROM t1 WHERE t1.y = 0;
+SELECT * FROM t1 WHERE t1.y = 2000;
+
+SELECT ta.y AS ta_y, ta.s, tb.y AS tb_y, tb.s FROM t1 ta, t1 tb HAVING ta_y = tb_y;
+
+DROP TABLE t1;
+
+--echo #
--echo End of 5.1 tests
=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc 2010-03-14 16:01:45 +0000
+++ b/sql/item_cmpfunc.cc 2010-03-22 08:33:25 +0000
@@ -1190,12 +1190,21 @@ get_year_value(THD *thd, Item ***item_ar
/*
Coerce value to the 19XX form in order to correctly compare
YEAR(2) & YEAR(4) types.
+ Here we are converting all item values but YEAR(4) fields since
+ 1) YEAR(4) already has a regular YYYY form and
+ 2) we don't want to convert zero/bad YEAR(4) values to the
+ value of 2000.
*/
- if (value < 70)
- value+= 100;
- if (value <= 1900)
- value+= 1900;
-
+ Item *real_item= item->real_item();
+ if (!(real_item->type() == Item::FIELD_ITEM &&
+ ((Item_field *)real_item)->field->type() == MYSQL_TYPE_YEAR &&
+ ((Item_field *)real_item)->field->field_length == 4))
+ {
+ if (value < 70)
+ value+= 100;
+ if (value <= 1900)
+ value+= 1900;
+ }
/* Convert year to DATETIME of form YYYY-00-00 00:00:00 (YYYY0000000000). */
value*= 10000000000LL;
Attachment: [text/bzr-bundle] bzr/gshchepa@mysql.com-20100322083325-smaikxlco1d4gf0w.bundle
Thread |
---|
• bzr commit into mysql-5.1-bugteam branch (gshchepa:3414) Bug#49910 | Gleb Shchepa | 22 Mar |