Bug 453156
| Summary: | DATE function used in WHERE clause - broken | ||
|---|---|---|---|
| Product: | Red Hat Enterprise Linux 5 | Reporter: | Adrya Stembridge <astembridge> |
| Component: | mysql | Assignee: | Tom Lane <tgl> |
| Status: | CLOSED ERRATA | QA Contact: | |
| Severity: | urgent | Docs Contact: | |
| Priority: | low | ||
| Version: | 5.2 | CC: | byte, hhorak, jason.young, kvolny, patrickm |
| Target Milestone: | rc | ||
| Target Release: | --- | ||
| Hardware: | x86_64 | ||
| OS: | Linux | ||
| URL: | http://bugs.mysql.com/bug.php?id=29898 | ||
| Whiteboard: | |||
| Fixed In Version: | Doc Type: | Bug Fix | |
| Doc Text: | Story Points: | --- | |
| Clone Of: | Environment: | ||
| Last Closed: | 2009-09-02 09:46:26 UTC | Type: | --- |
| Regression: | --- | Mount Type: | --- |
| Documentation: | --- | CRM: | |
| Verified Versions: | Category: | --- | |
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |
| Cloudforms Team: | --- | Target Upstream Version: | |
| Embargoed: | |||
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. 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. 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. http://rhn.redhat.com/errata/RHSA-2009-1289.html |
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): 5.0.45 How reproducible: CREATE TABLE Table1 ( id tinyint(3) NOT NULL AUTO_INCREMENT, d1 datetime NOT NULL, d2 datetime DEFAULT NULL, PRIMARY KEY (id) ); 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'); SELECT * FROM Table1 WHERE DATE(d1) = '2007-07-19'; /* returns all 10 records */ SELECT * FROM Table1 WHERE DATE(d2) = '2007-07-19'; /* returns only 3 records */ SELECT * FROM Table1 WHERE trim(DATE(d2)) = '2007-07-19'; /* returns all 7 records */ SELECT * 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.