MySQL Bugs: #1507: date/datetime conversion in IF()

Bug #1507	date/datetime conversion in IF()
Submitted:	9 Oct 2003 4:49	Modified:	15 Oct 2003 5:09
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.14-standard	OS:	Linux (linux, rh 9.0, kernel 2.4)
Assigned to:		CPU Architecture:	Any

Description:
Dear all,
 
anyone knows why the following is going wrong (I expect 1 in the columns Total and Open)? I suppose it is because MySQL is internally casting the IF expression (which is DATE vs DATE or DATE vs DATETIME) internally into a STRING before comparison, but not sure about that 
 
(String) '2003-10-06' >= (String) '2003-10-06'  ==> true
(String) '2003-10-06' >= (String) '2003-10-06 00:00:00'  ===> false
 
If the IF statement really casts the two dates (DATE, DATETIME) into a STRING, i think this is a bug. And a pretty nasty one.
 
NOTE: You have to run the SQL statement on the next day of the entered date in the table. So like the date is now you have to run the script on 2003/10/07 (octobre 7, 2003).
 
Again to point out the problem: Apparently it is a difference to substract 24 HOURS or 1 DAY from a DATE (not DATETIME) and then using this result in an IF clause (CASE too??, ifnull()???, any other expression?).
 
Thanx for any feedback!
 
Cheers
/rudy

How to repeat:
HERE my Table definition:
 
CREATE TABLE `tpr` (
  `tprnr` int(10) unsigned NOT NULL auto_increment,
  `projectid` int(10) unsigned NOT NULL default '0',
  `severityid` char(3) NOT NULL default 'L',
  `statusid` char(3) NOT NULL default 'ENT',
  `entered` date NOT NULL default '0000-00-00',
  `entered_by` varchar(20) NOT NULL default '',
  `assigned` date default NULL,
  `assigned_to` varchar(20) default NULL,
  `fixed` date default NULL,
  `released` date default NULL,
  `released_by` varchar(20) default NULL,
  `tested` date default NULL,
  `tested_by` varchar(20) default NULL,
  `finished` date default NULL,
  `closed` date default NULL,
  `quote` int(10) unsigned default NULL,
  `quoted` date default NULL,
  `quoted_by` varchar(20) default NULL,
  `screenid` int(10) unsigned default NULL,
  `screenname` varchar(80) default NULL,
  `subject` varchar(80) default NULL,
  `description` text,
  `solution` text,
  `testreport` text,
  `is_parked` char(1) NOT NULL default 'N',
  `creusr` varchar(20) default NULL,
  `credat` date default NULL,
  `modusr` varchar(20) default NULL,
  `moddat` date default NULL,
  PRIMARY KEY  (`tprnr`),
  KEY `idx1` (`projectid`)
) TYPE=MyISAM
 
 
mysql> select * from tpr where tprnr = 368\G
*************************** 1. row ***************************
      tprnr: 368
  projectid: 1
 severityid: H
   statusid: ENT
    entered: 2003-10-06
 entered_by: rmetzger
   assigned: NULL
assigned_to: NULL
      fixed: NULL
   released: NULL
released_by: NULL
     tested: NULL
  tested_by: NULL
   finished: NULL
     closed: NULL
      quote: NULL
     quoted: NULL
  quoted_by: NULL
   screenid: NULL
 screenname: NULL
    subject: finish invoicing module
description: - create summary reports (total invoice amount per month,
 
   solution: NULL
 testreport: NULL
  is_parked: N
     creusr: rmetzger
     credat: 2003-10-06
     modusr: rmetzger
     moddat: 2003-10-06
1 row in set (0.00 sec)
 
NOTE NOTE NOTE: the date in 'entered' has to be "yesterday"
-----------------------------------------------------------------------------
The statement which is going WRONG:
 
SELECT s.statusid, s.status, count(tpr.tprnr),entered,
              sum( if ( ( CASE WHEN s.statusid = 'ENT' THEN tpr.entered
                               WHEN s.statusid = 'ASS' THEN tpr.assigned
                               WHEN s.statusid = 'FIX' THEN tpr.fixed
                               WHEN s.statusid = 'REL' THEN tpr.released
                               WHEN s.statusid = 'TOK' THEN tpr.tested
                               WHEN s.statusid = 'TNO' THEN tpr.tested
                               WHEN s.statusid = 'FIN' THEN tpr.finished
                               WHEN s.statusid = 'CLS' THEN tpr.closed
                               ELSE '1990-01-01' /*IW,IT*/
                      END ) >= date_sub( curdate(), INTERVAL 24 HOUR ),
                             1, 0 ) )
            FROM status s
            LEFT JOIN tpr ON tpr.statusid = s.statusid
                         AND tpr.projectid = 1
                         AND tpr.is_parked = 'N'
                         AND tpr.tprnr = 368
           GROUP BY s.sort_idx, s.statusid, s.status
           ORDER BY s.sort_idx
 
-----------------------------------------------------------------------------
The statement which is WORKING:
 
SELECT s.statusid, s.status, count(tpr.tprnr),entered,
              sum( if ( ( CASE WHEN s.statusid = 'ENT' THEN tpr.entered
                               WHEN s.statusid = 'ASS' THEN tpr.assigned
                               WHEN s.statusid = 'FIX' THEN tpr.fixed
                               WHEN s.statusid = 'REL' THEN tpr.released
                               WHEN s.statusid = 'TOK' THEN tpr.tested
                               WHEN s.statusid = 'TNO' THEN tpr.tested
                               WHEN s.statusid = 'FIN' THEN tpr.finished
                               WHEN s.statusid = 'CLS' THEN tpr.closed
                               ELSE '1990-01-01' /*IW,IT*/
                      END ) >= date_sub( curdate(), INTERVAL 1 DAY ),
                             1, 0 ) )
            FROM status s
            LEFT JOIN tpr ON tpr.statusid = s.statusid
                         AND tpr.projectid = 1
                         AND tpr.is_parked = 'N'
                         AND tpr.tprnr = 368
           GROUP BY s.sort_idx, s.statusid, s.status
           ORDER BY s.sort_idx

Hello,

I got same result from both your queries so I can't see the difference.
You didn't provide status table structure and content nor results of both queries.

However you are slightly wrong where saying that:

> (String) '2003-10-06' >= (String) '2003-10-06'  ==> true
> (String) '2003-10-06' >= (String) '2003-10-06 00:00:00'  ===> false

When comparing DATE to DATETIME both they are compared as strings *but* before that DATE value is converted to DATETIME by adding zero hour:minute:seconds part.

So above is incorrect. It is:

(String) '2003-10-06' >= (String) '2003-10-06'  ==> true

'2003-10-06' -> '2003-10-06 00:00:00' ->
  (String) '2003-10-06 00:00:00' >= (String) '2003-10-06 00:00:00'  ==> true

This is described in manual.

Dear Alex!

You can easily reproduce this bug by this simple statement:

mysql> select IF ( date_sub( curdate(), INTERVAL 24 HOUR ) = date_sub( curdate(), INTERVAL 1 DAY ), 'Y', 'N' );
+--------------------------------------------------------------------------------------------------+
| IF ( date_sub( curdate(), INTERVAL 24 HOUR ) = date_sub( curdate(), INTERVAL 1 DAY ), 'Y', 'N' ) |
+--------------------------------------------------------------------------------------------------+
| N                                                                                                |
+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 4.0.14-standard |
+-----------------+
1 row in set (0.00 sec)

As a result, I would expect 'Y' in above query (I mean, it should not make a difference if i substract 24 HOURs from a DATE or 1 DAY) (not, curdate() gives a DATE, not DATETIME). But as you can see, i get 'N' as the result.

For version, please see above. I am using RH 9.0, Kernel 2.4. I installed the RPM.

If you need any more information, as server vars, .. please let me know.

Cheers
/rudy