Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder Stooshie's Avatar
    Join Date
    Mar 2008
    Location
    Dundee, Scotland
    Posts
    380
    Thanks
    9
    Thanked 39 Times in 39 Posts

    Question Why would a query with an integer parameter on string column return some rows?

    In the query:
    Code:
    SELECT string_column WHERE  string_column = 0;
    # notice it is 0 not "0" or '0'
    some rows are being returned.

    I would expect all, none or an error, but not some rows, but I get:
    Code:
    aa4443c-121b142b48d--7fb1
    aa4443c-121b142b48d--7faf
    aa4443c-121b142b48d--7fae
    B2A0682-123DCFC39AA--7E19
    B2609A6-122CB3D8A7A--7EC3
    .
    .
    .
    B2609A6-122CB3D8A7A--7BAA
    -C9673C4-12351158721--7E51
    -C9673C4-12351158721--7E51
    -C9673C4-12351158721--7E51
    (The values returned are correct).

    93 rows are returned dispersed throughout the table and there are 903 rows in the table

    Is there any reason for this?

    Thanks,

    Andrew.
    Regards, Stooshie
    O

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,566
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    I think the reason is that MySQL is BRAIN DEAD!!!

    No other DB would do this.

    But look here:
    Code:
    mysql> select n, n=0 from foo;
    +----------------------------+------+
    | n                          | n=0  |
    +----------------------------+------+
    | adam                       |    1 |
    | bob                        |    1 |
    | xyz                        |    1 |
    | 11111111111111111111111111 |    0 |
    | 000000000000000000         |    1 |
    | !                          |    1 |
    | @                          |    1 |
    | #                          |    1 |
    | a                          |    1 |
    | A                          |    1 |
    | 0                          |    1 |
    | 1                          |    0 |
    | 10                         |    0 |
    +----------------------------+------+
    13 rows in set (0.00 sec)
    And then also look here:
    Code:
    mysql> select n,n=0,n=1,n=2,n=3,n=4 from foo where n in ('adam','0','1');
    +------+------+------+------+------+------+
    | n    | n=0  | n=1  | n=2  | n=3  | n=4  |
    +------+------+------+------+------+------+
    | adam |    1 |    0 |    0 |    0 |    0 |
    | 0    |    1 |    0 |    0 |    0 |    0 |
    | 1    |    0 |    1 |    0 |    0 |    0 |
    +------+------+------+------+------+------+
    So clearly the rule is this: "If the value is convertible to a number, then the numeric value is tested. But all values not convertible to a number are equal to numeric zero but not to any other numeric value."

    What an UTTERLY perverted definition!!

    I would LOVE to hear somebody on the MySQL team justify this definition.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder Stooshie's Avatar
    Join Date
    Mar 2008
    Location
    Dundee, Scotland
    Posts
    380
    Thanks
    9
    Thanked 39 Times in 39 Posts
    Thanks Pedant for doing that testing. At least it makes it clear what MySQL is actually doing.

    Playing devil's advocate, I suppose the thinking would be that it will only return 0 when there is, literally, nothing in there (e.g. 0, "0", "" and null)?

    Well maybe!
    Regards, Stooshie
    O

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,566
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Of course, other databases wouldn't even allow the query. They would give you Type Mismatch Error or something akin. Only MySQL is so sloppy that it allows comparing numbers to strings. Ugh.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •