Re: CAST(xx AS DATE) incorrect in MySQL 4.1a

Hello,

Joacim Larsson wrote:
> I have a table (temessage) with a column called eme_last_send of type
> DATETIME.
> The table in the case below only contain one row with today's date.
> 
> I use the CAST function to remove the time fields:

Best way to do it is to use DATE_FORMAT()

> mysql> SELECT CAST(eme_last_send AS DATE),CURRENT_DATE FROM temessage WHERE
> CAST(eme_last_send AS DATE)<> CURRENT_DATE;
> +-----------------------------+--------------+
> | CAST(eme_last_send AS DATE) | CURRENT_DATE |
> +-----------------------------+--------------+
> | 2003-11-26                  | 2003-11-26   |
> +-----------------------------+--------------+
> 
> However, I did NOT expect this row to appear in this query! It seems like
> CAST(eme_last_send AS DATE) is not the same as CURRENT_DATE?
> 
> Workaround:
> 
> mysql> SELECT CAST(eme_last_send AS DATE),CURRENT_DATE FROM temessage WHERE
> LEFT(eme_last_send,10)<>CURRENT_DATE;
> Empty set (0.00 sec)
> 
> mysql>
> 
> 
> This should be easily reproducible and I think this is a bug.

I can't repeat it with latest 4.1 from source tree.

mysql> SELECT dt, CAST(dt AS DATE), CURRENT_DATE from test;
+---------------------+------------------+--------------+
| dt                  | CAST(dt AS DATE) | CURRENT_DATE |
+---------------------+------------------+--------------+
| 2003-11-26 16:38:51 | 2003-11-26       | 2003-11-26   |
+---------------------+------------------+--------------+

mysql> CREATE TABLE test (dt DATETIME);
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO dt VALUES (NOW());
Query OK, 1 row affected (0.04 sec)

mysql> SELECT dt, CAST(dt AS DATE), CURRENT_DATE from test WHERE CAST(dt AS
DATE) <> CURRENT_DATE;
Empty set (0.00 sec)


mysql> SELECT dt, CAST(dt AS DATE), CURRENT_DATE from test WHERE CAST(dt AS
DATE) = CURRENT_DATE;
+---------------------+------------------+--------------+
| dt                  | CAST(dt AS DATE) | CURRENT_DATE |
+---------------------+------------------+--------------+
| 2003-11-26 16:38:51 | 2003-11-26       | 2003-11-26   |
+---------------------+------------------+--------------+


> -Joacim
> 
> 


-- 
 Are you MySQL certified? -> http://www.mysql.com/certification
 For technical support contracts, visit https://order.mysql.com/?ref=msal
    __  ___     ___ ____  __
   /  |/  /_ __/ __/ __ \/ /    Mr. Alexander Keremidarski <[email protected]>
  / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Developer
 /_/  /_/\_, /___/\___\_\___/   Sofia, Bulgaria
         <___/  www.mysql.com




-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]