RE: CAST(xx AS DATE) incorrect in MySQL 4.1a - Joacim Larsson - com.mysql.lists.bugs

3 messages in com.mysql.lists.bugsRE: CAST(xx AS DATE) incorrect in MyS...

From	Sent On	Attachments
Joacim Larsson	26 Nov 2003 06:15
Alexander Keremidarski	26 Nov 2003 06:42
Joacim Larsson	26 Nov 2003 06:58

Subject:	RE: CAST(xx AS DATE) incorrect in MySQL 4.1a
From:	Joacim Larsson (list...@icetsystems.se)
Date:	11/26/2003 06:58:04 AM
List:	com.mysql.lists.bugs

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:sal...@mysql.com] Sent: den 26 november 2003 15:43 To: list...@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