Hello
Would someone please shed some light on what's wrong with calls like
IF( DATE(d) = "some-date", TIME(d), d )
on DATETIME columns?
Thank you.

I run some tests on mysql 5.0, 5.1 and 5.5: got the same wierd results
everywhere, so I guess I'm missing something, but what is it?
It looks like the engine is trying to use the result of TIME(d) as "d"
in the DATE(d) test, but I can't understand why.

Here's a runnable test and its output:

$ mysql test -vve 'DROP TABLE IF EXISTS dt; SET @rd="2011-08-07",
@rt="10:11:12"; CREATE TABLE dt (d DATETIME); INSERT INTO dt VALUES
(CONCAT_WS(" ",@rd,@rt)),("1234-05-06 07:08:09"); SELECT d, IF(
DATE(d)=@rd, TIME(d), d) wtf1 FROM dt; SELECT d, IF( DATE(d)=@rd,
DATE_FORMAT("%T",d), d) wtf2 FROM dt; SELECT d, IF( DATE(d)=@rd,
REPLACE(d,CONCAT(@rd," "),""), d) z FROM dt;'
--------------
DROP TABLE IF EXISTS dt
--------------

Query OK, 0 rows affected (0.05 sec)

--------------
SET @rd="2011-08-07", @rt="10:11:12"
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
CREATE TABLE dt (d DATETIME)
--------------

Query OK, 0 rows affected (0.16 sec)

--------------
INSERT INTO dt VALUES (CONCAT_WS(" ",@rd,@rt)),("1234-05-06 07:08:09")
--------------

Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

--------------
SELECT d, IF( DATE(d)=@rd, TIME(d), d) wtf1 FROM dt
--------------

+---------------------+---------------------+
| d | wtf1 |
+---------------------+---------------------+
| 2011-08-07 10:11:12 | 2010-11-12 00:00:00 |
| 1234-05-06 07:08:09 | 1234-05-06 07:08:09 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

--------------
SELECT d, IF( DATE(d)=@rd, DATE_FORMAT("%T",d), d) wtf2 FROM dt
--------------

+---------------------+---------------------+
| d | wtf2 |
+---------------------+---------------------+
| 2011-08-07 10:11:12 | NULL |
| 1234-05-06 07:08:09 | 1234-05-06 07:08:09 |
+---------------------+---------------------+
2 rows in set, 1 warning (0.01 sec)

Warning (Code 1292): Incorrect datetime value: '%T'
--------------
SELECT d, IF( DATE(d)=@rd, REPLACE(d,CONCAT(@rd," "),""), d) z FROM dt
--------------

+---------------------+---------------------+
| d | z |
+---------------------+---------------------+
| 2011-08-07 10:11:12 | 10:11:12 |
| 1234-05-06 07:08:09 | 1234-05-06 07:08:09 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

Bye


-ab

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=listsearcharchive@lassosoft.com