>>>> 2011/08/07 18:20 +0300, Marius Feraru >>>>
Would someone please shed some light on what's wrong with calls like
IF( DATE(d) = "some-date", TIME(d), d )
on DATETIME columns?
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.
Function TIME yields a string, not any timestamp type, but "d" is of some such type.
Unhappily, the HTML help that I downloaded is of no help in this case, when the result
context has no type, unless it is supposed that one stops at the first that matches:
Expression Return Value
expr2 or expr3 returns a string string
expr2 or expr3 returns a floating-point value floating-point
expr2 or expr3 returns an integer integer
but it looks as if the string is coerced to "d" s type. But since if empty string is
concatenated to the TIME-result it is as you wish, and the value of TIME(d) =
DATE_FORMAT(d, '%T') is 1, it seems to me an optimizer error.
As for the warning, your arguments to "DATE_FORMAT" are backwards, and corrected that
yields the outcome that you seek.