List:General Discussion« Previous MessageNext Message »
From:Rik Wasmus Date:September 8 2011 9:49am
Subject:Re: strange mysql update ..
View as plain text  
> I fired the update statement in a wrong way ..like this ..
> 
> update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845
> limit 1 ;
> ( I forgot to use where . instead of where I used and )
> update user_info set login_date='2011-08-05 04:15:05' where user_id
> =16078845 limit 1 ; ( this is the query intended )
> 
> after the update ..I got this message ..
> mysql> update user_info set login_date='2011-08-05 04:15:05' and user_id
> =16078845 limit 1;
> Query OK, 1 row affected, 1 warning (0.02 sec)
> Rows matched: 1  Changed: 1  Warnings: 0
> 
> It shows that one record is affected and one row changed ..
> I did show warnings ..the output is like this ..
> 
> | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05'
 
> So my question is what happened exactly ?
> Why no records updated ?

A lot of casting:

(1) login_date='2011-08-05 04:15:05' and user_id =16078845;

And implies boolean, so the result is the either true or false. MySQL doesn't 
like using non-numbers as booleans (the '2011-08-05 04:15:05') , this is the 
double spoken of. 

(2) login_date = false (or true, but that doesn't matter) 

But MySQL doesn't know booleans, to a number it is:

(3) login_date = 0

But the column is a DATETIME (or TIMESTAMP) column, 0 or 1 is an incorrect 
value, cast to:

(4) login_date = 0000-00-00 00:00:00

So, somewhere there's (or was, may be overwritten) a record with that value, 
just 1 due to the limit 1, otherwise, the whole table would have that as a 
login_date (doesn't matter wether it was true or false).


Check out:
DB 5.1.58-1-log:(none)  mysql> SELECT 1 AND 1;
+---------+
| 1 AND 1 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

DB 5.1.58-1-log:(none)  mysql> SELECT 0 AND 1;
+---------+
| 0 AND 1 |
+---------+
|       0 |
+---------+
1 row in set (0.01 sec)

DB 5.1.58-1-log:(none)  mysql> SELECT '1' AND 1;
+-----------+
| '1' AND 1 |
+-----------+
|         1 |
+-----------+
1 row in set (0.03 sec)

DB 5.1.58-1-log:(none)  mysql> SELECT 'a' AND 1;
+-----------+
| 'a' AND 1 |
+-----------+
|         0 |
+-----------+
1 row in set, 1 warning (0.03 sec)

DB 5.1.58-1-log:(none)  mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.01 sec)
-- 
Rik Wasmus
Thread
strange mysql update ..umapathi b8 Sep
  • Re: strange mysql update ..Ananda Kumar8 Sep
    • Re: strange mysql update ..umapathi b8 Sep
      • Fwd: strange mysql update ..umapathi b9 Sep
        • Re: strange mysql update ..Derek Downey9 Sep
  • Re: strange mysql update ..Rik Wasmus8 Sep
    • Re: strange mysql update ..Suresh Kuna9 Sep