List:General Discussion« Previous MessageNext Message »
From:(Hal Date:August 26 2011 2:23pm
Subject:Re: Wrong conversion to timestamp from character string
View as plain text  
>>>> 2011/08/18 18:30 +0300, Marius Feraru >>>>
Thanks for your time reading my message, but I don't understand what
is the "result context" that you are talking about. Could you please
elaborate?
<<<<<<<<
Well, if an expression is an argument to, say, "CONCAT", the expression s result is
character string. An argument to, say, "POW" is number. But the second and third
arguments to "IF" have the same type, the type of the "IF" s context, and an expression
that is an operand to "SELECT" may have any type: the result context does not require
anything.

Now, your expression IF( DATE(d) = "some-date", TIME(d), d ) is an operand to "SELECT",
and no type is required of it--but the types are not the same, wherefore there is at
least one conversion, surely that the bare "d" is made character string. But it seems
that instead "TIME(d)", a character string, is converted to some timestamp, a date. I
wrote that I believe this an optimizer error because the least characterward tweak to
this is enough to make it that which you seek: either concatenating empty string to
"TIME(d)", thereby overriding any tendency the optimizer has to consider it other than a
character string, or by concatenating empty string to the whole expression, thereby
making the "IF" s result context character string, not any type.

Consider this:

-- 1) create test table containing a single DATETIME column
CREATE TABLE dt (d DATETIME);
-- 2) insert two test records
INSERT INTO dt VALUES ("2011-08-07 10:11:12"),("1234-05-06 07:08:09");

SELECT d, ADDTIME(IF( DATE(d)='2011-08-07', TIME(d), d), '1 1:1:1.000002') as x FROM dt;

The outcome is really screwy:

+---------------------+----------------------------+
| d                   | x                          |
+---------------------+----------------------------+
| 2011-08-07 10:11:12 | 2035-12-13 02:00:00        |
| 1234-05-06 07:08:09 | 1234-05-07 08:09:10.000002 |
+---------------------+----------------------------+

Surely there is a noteworthy bug here.

Thread
Extraneous warning 1292 (Incorrect datetime value)Marius Feraru7 Aug
  • Re: Extraneous warning 1292 (Incorrect datetime value)Dan Nelson7 Aug
    • Re: Extraneous warning 1292 (Incorrect datetime value)Marius Feraru7 Aug
      • Re: Extraneous warning 1292 (Incorrect datetime value)Marius Feraru18 Aug
        • Re: Wrong conversion to timestamp from character stringhsv26 Aug
  • Re: Extraneous warning 1292 (Incorrect datetime value)hsv12 Aug