CAST(xx AS DATE) incorrect in MySQL 4.1a

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:

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.

-Joacim


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

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
/ /|_/ / // /\ \/ /_/ / /__ 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/bugs?unsub=gcdmb-bugs@m.gmane.org

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:salle@mysql.com]
Sent: den 26 november 2003 15:43
To: listuser@icetsystems.se
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
/ /|_/ / // /\ \/ /_/ / /__ 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/bugs?unsub=listuser@icetsystems.se



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org