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

Hi,

Thanks for a quick reply.

In such case it must have been fixed in the latest src files. I tried the
exact same commands as you used below and got the same problem as before.

The only other thing I can think of is that I'm using Swedish char-set and
InnoDB tables, but I don't think that matters.

-Joacim

-----Original Message-----
From: Alexander Keremidarski [mailto:[email protected]]
Sent: den 26 november 2003 15:43
To: [email protected]
Cc: mysql bugs
Subject: 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]



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