#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0

    Mysql strange behaviour


    Can somebody explain this?

    mysql> select 1346878417 <= cast('2012-09-06' as datetime), x <= cast('2012-09-06' as datetime) from (select 1346878417 as x) t;
    +----------------------------------------------+------------------------------------+
    | 1346878417 <= cast('2012-09-06' as datetime) | x <= cast('2012-09-06' as datetime) |
    +----------------------------------------------+------------------------------------+
    | 1 | 0 |
    +----------------------------------------------+------------------------------------+
    1 row in set (0.01 sec)

    Shouldn't the two columns be equal?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by wsxedc
    Shouldn't the two columns be equal?
    one would think so

    but there is obviously some implicit casting going on, right?

    also, why are you comparing a unix epoch to a datetime? they are fundamentally incompatible

    you should probably be using the FROM_UNIXTIME() function somewhere
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Just to clarify a bit.

    As Rudy said there are some implicit conversion going on since you are casting to a datetime and then you are comparing to what in the first comparison is a numeric value.
    Unfortunately in the second when you go through an anonymous table that isn't defined the second value ends up as a string in that comparison.
    So in the first case to be able to perform the comparison the datetime is implicitly cast to a integer while in the second case since x is a string the implicit cast of datetime is to string.

    If I cast x as a numeric value you get the same result:
    Code:
    mysql> select 1346878417 <= cast('2012-09-06' as datetime), cast(x as signed integer) <= cast('2012-09-06' as
    datetime) from (select 1346878417 as x) t;
    +----------------------------------------------+-------------------------------------------------------------+
    
    | 1346878417 <= cast('2012-09-06' as datetime) | cast(x as signed integer) <= cast('2012-09-06' as datetime) |
    
    +----------------------------------------------+-------------------------------------------------------------+
    
    |                                            1 |                                                           1 |
    
    +----------------------------------------------+-------------------------------------------------------------+
    But as Rudy said using proper functions to explicitly convert to the right format to make the comparison possible is an infinitely better approach.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo