MySQL Bugs: #54147: Valid date string in query results in wrong result on Linux sever.

Bug #54147	Valid date string in query results in wrong result on Linux sever.
Submitted:	1 Jun 2010 14:18	Modified:	11 Feb 2018 11:25
Reporter:	Thomas Lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.51a-3ubuntu5.5 (Ubuntu), 5.0.91, 5.1.48, 5.6.99 bzr	OS:	Linux (Ubuntu 8.04.4 LTS)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
  I have a table which contains a DATE field. If the query is like "... datefield='2010-6-1' ...", the result will be wrong. But if the query is like "... datefield='2010-06-01' ...", the result will be correct.

How to repeat:
See the attachment for the minimal test case database dump.

Below are four test SQLs. The 1st SQL will generate wrong result. The other  SQLs will generate correct results.

/* Specify the cusId in WHERE clause and the date format is yyyy-m-d */
SELECT c.*,cp.cplId AS nowCplId, cp.rplId AS nowRplId, cpn.cplId AS nextCplId, cpn.rplId AS nextRplId FROM Customer AS c LEFT JOIN CustomerPlan AS cp ON c.cusId=cp.cusId AND cp.cplEffDate='2010-5-1' LEFT JOIN RatePlan AS r ON cp.rplId=r.rplId LEFT JOIN CustomerPlan AS cpn ON c.cusId=cpn.cusId AND cpn.cplEffDate='2010-6-1' LEFT JOIN RatePlan AS rn ON cpn.rplId=rn.rplId WHERE c.cusId=17;

/* Specify the cusId in WHERE clause and the date format is yyyy-mm-dd */
SELECT c.*,cp.cplId AS nowCplId, cp.rplId AS nowRplId, cpn.cplId AS nextCplId, cpn.rplId AS nextRplId FROM Customer AS c LEFT JOIN CustomerPlan AS cp ON c.cusId=cp.cusId AND cp.cplEffDate='2010-05-01' LEFT JOIN RatePlan AS r ON cp.rplId=r.rplId LEFT JOIN CustomerPlan AS cpn ON c.cusId=cpn.cusId AND cpn.cplEffDate='2010-06-01' LEFT JOIN RatePlan AS rn ON cpn.rplId=rn.rplId WHERE c.cusId=17;

/* Do not specifiy any filter and the cusId(17) record will be correct. The date format is yyyy-m-d */
SELECT c.*,cp.cplId AS nowCplId, cp.rplId AS nowRplId, cpn.cplId AS nextCplId, cpn.rplId AS nextRplId FROM Customer AS c LEFT JOIN CustomerPlan AS cp ON c.cusId=cp.cusId AND cp.cplEffDate='2010-5-1' LEFT JOIN RatePlan AS r ON cp.rplId=r.rplId LEFT JOIN CustomerPlan AS cpn ON c.cusId=cpn.cusId AND cpn.cplEffDate='2010-6-1' LEFT JOIN RatePlan AS rn ON cpn.rplId=rn.rplId;

/* Workaround to get the desired cusId with two inequality filters. The date format is yyyy-m-d */
SELECT c.*,cp.cplId AS nowCplId, cp.rplId AS nowRplId, cpn.cplId AS nextCplId, cpn.rplId AS nextRplId FROM Customer AS c LEFT JOIN CustomerPlan AS cp ON c.cusId=cp.cusId AND cp.cplEffDate='2010-5-1' LEFT JOIN RatePlan AS r ON cp.rplId=r.rplId LEFT JOIN CustomerPlan AS cpn ON c.cusId=cpn.cusId AND cpn.cplEffDate='2010-6-1' LEFT JOIN RatePlan AS rn ON cpn.rplId=rn.rplId WHERE c.cusId>=17 AND c.cusId<=17;

The minimal test case database dump

Attachment: create-table-BugTest.sql.txt (text/plain), 7.03 KiB.

Thank you for the bug report. Could you please try the latest released version 5.0.91 and then comment the result. Thanks in advance.

http://downloads.mysql.com/archives.php?p=mysql-5.0&v=5.0.91

Yes. 5.0.91 also has this issue on Ubuntu 8.04 LTS.

Thank you for the feedback.

Verified as described.

Expected result:

17      XXX     XXX666666       8       no      8@b.c   no      0966666666      NULL            2010-05-04 14:08:39     2010-05-04 14:04:30     9       3       10      3

Actual result:

17      XXX     XXX666666       8       no      8@b.c   no      0966666666      NULL            2010-05-04 14:08:39     2010-05-04 14:04:30     NULL    NULL    NULL    NULL

4.1 is not affected.

Posted by developer:
 
Fixed in 5.6.40 and up.