Extraneous warning 1292 (Incorrect datetime value)

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=gcdmg-mysql-2@m.gmane.org

Re: Extraneous warning 1292 (Incorrect datetime value)

In the last episode (Aug 07), Marius Feraru said:
> --------------
> 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'

I think you need to swap your arguments to DATE_FORMAT. The date comes
first, then the format string.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Extraneous warning 1292 (Incorrect datetime value)

On Sun, Aug 7, 2011 at 20:34, Dan Nelson wrote:
> I think you need to swap your arguments to DATE_FORMAT
Of course, sorry about that stupid example, I was just trying to build
up from the original problem and failed. Thanks :)

The problem still stands though. Any ideas?

-ab

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Extraneous warning 1292 (Incorrect datetime value)

>>>> 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?
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.
<<<<<<<<
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.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Extraneous warning 1292 (Incorrect datetime value)

Found at http://lists.mysql.com/mysql/225525 that "hsv" wrote on 12 Aug 2011:
> 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:
> ...
> 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.
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?
Please notice that the DATE_FORMAT misusage was not a part of the
problem, the IF( DATE(d) = "some-date", TIME(d), d ) was. That is step
#3:

-- 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");
-- 3) THE test: can mysql properly select date/time in an IF?
SELECT d, IF( DATE(d)="2011-08-07", TIME(d), d) x FROM dt;
-- PLEASE notice the WRONG time on the first row
+---------------------+---------------------+
| d | x |
+---------------------+---------------------+
| 2011-08-07 10:11:12 | 2010-11-12 00:00:00 |
| 1234-05-06 07:08:09 | 1234-05-06 07:08:09 |
+---------------------+---------------------+
-- 4) as proved by:
SELECT DATE(d), TIME(d) from dt;
+------------+----------+
| DATE(d) | TIME(d) |
+------------+----------+
| 2011-08-07 | 10:11:12 |
| 1234-05-06 | 07:08:09 |
+------------+----------+

What am I doing wrong?!
Thanks again.

++ Please keep a CC to my email address when replying. Thanks.
-ab

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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;

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.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org