Bug #2335 Inconsistancy with handling of Years and NOW() function

• Submitted: 9 Jan 2004 8:27
• Modified: 11 Jan 2004 23:43
• Reporter: Martin Pollard
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.17
• OS: Windows (WinXP)
• Assigned to: Ramil Kalimullin
• CPU Architecture: Any

[9 Jan 2004 8:27] Martin Pollard

Description:
There is a slight inconsistancy about the way mysql handles Year fields and the NOW() operator.  It seems to truncate it for inserts but doesn't do the same for selects.

How to repeat:
mysql> INSERT INTO date_test VALUES(NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `date_test` WHERE adatefield=NOW();
Empty set (0.00 sec)

mysql> SELECT * FROM `date_test` WHERE adatefield=YEAR(NOW());
+------------+
| adatefield |
+------------+
|       2004 |
+------------+
1 row in set (0.00 sec)

mysql> describe date_test;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| adatefield | year(4) |      | PRI | 0000    |       |
+------------+---------+------+-----+---------+-------+

[9 Jan 2004 8:33] Dean Ellis

Verified against 4.0.18 and 4.1.2, noting the manual's comment, "As the result of a function that returns a value that is acceptable in a YEAR context, such as NOW()" from the "YEAR Type" chapter, which implies that this should work.

CREATE TABLE dtest ( a DATE );
INSERT INTO dtest VALUES ( NOW() );
SELECT * FROM dtest WHERE a = NOW();
ALTER TABLE dtest MODIFY a YEAR;
SELECT * FROM dtest WHERE a = NOW();
DROP TABLE dtest;

[11 Jan 2004 23:43] Ramil Kalimullin

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html