List:General Discussion« Previous MessageNext Message »
From:Suresh Kuna Date:September 9 2011 4:32am
Subject:Re: strange mysql update ..
View as plain text  
Nice Rik!

On Thu, Sep 8, 2011 at 3:19 PM, Rik Wasmus <rik@stripped> wrote:

> > 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>


-- 
Thanks
Suresh Kuna
MySQL DBA

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