Bug 453156 - DATE function used in WHERE clause - broken
Summary: DATE function used in WHERE clause - broken
Alias: None
Product: Red Hat Enterprise Linux 5
Classification: Red Hat
Component: mysql
Version: 5.2
Hardware: x86_64
OS: Linux
Target Milestone: rc
: ---
Assignee: Tom Lane
QA Contact:
URL: http://bugs.mysql.com/bug.php?id=29898
Depends On:
TreeView+ depends on / blocked
Reported: 2008-06-27 16:03 UTC by Adrya Stembridge
Modified: 2013-07-03 03:19 UTC (History)
5 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Last Closed: 2009-09-02 09:46:26 UTC
Target Upstream Version:

Attachments (Terms of Use)

System ID Private Priority Status Summary Last Updated
Red Hat Product Errata RHSA-2009:1289 0 normal SHIPPED_LIVE Moderate: mysql security and bug fix update 2009-09-01 13:32:14 UTC

Description Adrya Stembridge 2008-06-27 16:03:56 UTC
Description of problem:

Using the DATE() function in the where clause does not return any records after
a null is encountered.  It will work if you use TRIM or CAST on the results from
the DATE function.

Version-Release number of selected component (if applicable):

How reproducible:

  id tinyint(3)   NOT NULL      AUTO_INCREMENT,
  d1 datetime     NOT NULL,
  d2 datetime     DEFAULT NULL,

INSERT INTO Table1(d1, d2)
VALUES ('2007-07-19 08:30:00', '2007-07-19 08:30:00'),
('2007-07-19 08:31:00', '2007-07-19 08:31:00'),
('2007-07-19 08:32:00', '2007-07-19 08:32:00'),
('2007-07-19 08:33:00', NULL),
('2007-07-19 08:34:00', '2007-07-19 08:34:00'),
('2007-07-19 08:35:00', NULL),
('2007-07-19 08:36:00', '2007-07-19 08:36:00'),
('2007-07-19 08:37:00', '2007-07-19 08:37:00'),
('2007-07-19 08:38:00', NULL),
('2007-07-19 08:39:00', '2007-07-19 08:39:00');

FROM Table1
WHERE DATE(d1) = '2007-07-19';
/* returns all 10 records */

FROM Table1
WHERE DATE(d2) = '2007-07-19';
/* returns only 3 records */

FROM Table1
WHERE trim(DATE(d2)) = '2007-07-19';
/* returns all 7 records */

FROM Table1
WHERE CAST(DATE(d2) AS char(10)) = '2007-07-19';
/* returns all 7 records */

Additional info:
MySQL resolved this in build 5.0.51a.

Comment 1 Jay Young 2008-11-15 18:31:05 UTC
Tom, this is impacting us too.  Not sure of the scope until we begin auditing all our reporting code.  It'd be really, really nice to get this either backported - or if at all possible - a point release update.   I just eyeballed the reported package list for U3 and didn't see a mysql update in the package list.

Comment 2 Tom Lane 2008-11-16 03:36:19 UTC
No, there's no mysql update in U3.  I've begun lobbying for one in U4, but to be honest grunt engineers don't get to make those decisions.  Nagging your local Red Hat support contact would be a good way to get it moved up the priority list.

Comment 7 errata-xmlrpc 2009-09-02 09:46:26 UTC
An advisory has been issued which should help the problem
described in this bug report. This report is therefore being
closed with a resolution of ERRATA. For more information
on therefore solution and/or where to find the updated files,
please follow the link below. You may reopen this bug report
if the solution does not work for you.


Note You need to log in before you can comment on or make changes to this bug.