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: | |
| 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 |
[9 Oct 2003 4:49]
[ name withheld ]
[15 Oct 2003 5:09]
Alexander Keremidarski
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.
[15 Oct 2003 5:35]
[ name withheld ]
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
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.