Hal?sz S?ndor Re: Wrong conversion to timestamp from character string
Aug 26, 2011; 10:23
Hal?sz S?ndor
Re: Wrong conversion to timestamp from character string
>>>> 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;
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=listsearcharchive@lassosoft.com
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!