Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: MySQL unsigned BIGINT issues

  1. #1
    Join Date
    Sep 2013
    Beans
    11

    Post MySQL unsigned BIGINT issues

    Hi folks,

    in the mysql-client the SQL Query: "SELECT 0xFFFFFFFFFFFFFFFF + 0;" // 64 bit number
    gives the following response from my MySQL server:

    +------------------------+
    | 0xffffffffffffffff + 0 |
    +------------------------+
    | 18446744073709552000 |
    +------------------------+

    which is quite close but not close enough.

    The exact value (for an unsigned BIGINT) is: 18446744073709551615

    Actually, I can't use almost any 64 bit hex string for calculations or bit shift operations.
    (if not explicitly casting it with "CAST( 0xffffffffffffffff AS UNSIGNED)" to an unsigned BIGINT, which works).

    With 32 bit hex strings however I didn't experience this problems (though I did not test all of them ).

    can anyone reproduce this behaviour on his/her machine?

    my params:

    uname -a: Linux laptop 3.2.0-39-generic#62-Ubuntu SMP Thu Feb 28 00:28:53 UTC 2013 x86_64 x86_64 x86_64GNU/Linux
    mysql --version: Ver 14.14 Distrib 5.5.32, fordebian-linux-gnu (x86_64) using readline 6.2

    regards

  2. #2
    Join Date
    Nov 2005
    Location
    Bordeaux, France
    Beans
    11,297
    Distro
    Ubuntu 12.04 Precise Pangolin

    Re: MySQL unsigned BIGINT issues

    The value you are trying to use is outside the range of type BIGINT. I don't know what MySQL is supposed to do in that case, but in C, when you try to store an out-of-range value in a signed type, the result is undefined. It could be that MySQL just stores the value "as is" in a C int64_t, which would explain what you get.

    If you want to store a hex string, and not a numeric value, you should use UNSIGNED BIGINT.
    Last edited by Bachstelze; October 21st, 2013 at 09:10 PM.
    「明後日の夕方には帰ってるからね。」


  3. #3
    Join Date
    Sep 2013
    Beans
    11

    Re: MySQL unsigned BIGINT issues

    I know that mixing of signed and unsigned values requires some attention from the programmers side.

    But in this case, the MySql server just doesn't do what it is supposed to do.


    According to the manual of the 5.5 Server:




    12.6.1. Arithmetic Operators

    The usual arithmetic operators are available. The result is determined according to the following rules:
    In the case of -, +, and *, the result is calculated with BIGINT (64-bit) precision if both operands are integers.
    If both operands are integers and any of them are unsigned, the result is an unsigned integer. For subtraction, if the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is signed even if any operand is unsigned.
    If any of the operands of a +, -, /, *,% is a real or string value, the precision of the result is the precision of the operand with the maximum precision.

    the calculation mistake when using the hex representation of numbers is easily proved.

    mysql>SELECT 9223372036854775807 + 1; <-- biggest unsigned BIGINT + 1
    ERROR 1690(22003): BIGINT value is out of range in '(9223372036854775807 +1)' <-- CORRECT


    mysql>SELECT 0x7fffffffffffffff + 1; <-- the same in hex


    +------------------------+
    |0x7FFFFFFFFFFFFFFF + 1 |
    +------------------------+
    | 9223372036854776000 | <-- WRONG
    +------------------------+


    Now before I fill out a bug report, I just would like to know, whether this affects
    only my system or if others can reproduce the observed problem as well.


    Regards

  4. #4
    Join Date
    Nov 2005
    Location
    Bordeaux, France
    Beans
    11,297
    Distro
    Ubuntu 12.04 Precise Pangolin

    Re: MySQL unsigned BIGINT issues

    Well then I have the same behavior on two machines (one Debian and one Ubuntu, both MySQL 5.5).
    「明後日の夕方には帰ってるからね。」


  5. #5
    Join Date
    Sep 2013
    Beans
    11

    [SOLVED] MySQL unsigned BIGINT issues

    Thanks for confirming!

  6. #6

    Re: MySQL unsigned BIGINT issues

    I don't think you've demonstrated that this is a bug.

    The manual section you quote only applies when both operands are unsigned. 0xFFFFFFFFFFFFFFFF is a string, so it is automatically converted to a number when used in a numeric expression -- but the manual is not clear on what kind of "number" it becomes. The assumption that it is unsigned is unwarranted IMO.

    If the list of rules on the aforelinked page can be assumed to apply to arithmetic operations as well as comparisons, then 0xFFFFFFFFFFFFFFFF is converted to a floating-point number first, which could explain the loss of precision.

  7. #7

    Re: MySQL unsigned BIGINT issues

    Wait -- I said "0xFFFFFFFFFFFFFFFF is a string" but that's not necessarily correct; I skipped some reasoning. This page says that hexadecimal literals, in numeric contexts, act like integers with 64-bit precision. It does not specify signedness. Perhaps 0xFFFFFFFFFFFFFFFF doesn't make sense as a numeric literal, since its "numeric value" is greater than a 64-bit signed value can hold, and so it falls back to the string rule. At least, that's what I was thinking when I wrote the previous post.

  8. #8
    Join Date
    Apr 2009
    Beans
    230
    Distro
    Xubuntu 14.04 Trusty Tahr

    Re: MySQL unsigned BIGINT issues

    Code:
    Server version: 5.5.30-cll MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> select 0x7FFFFFFFFFFFFFFF +0;
    +-----------------------+
    | 0x7FFFFFFFFFFFFFFF +0 |
    +-----------------------+
    |   9223372036854776000 |
    +-----------------------+
    1 row in set (0.00 sec)
    
    mysql> select 0x7FFFFFFFFFFFFFFE +0;
    +-----------------------+
    | 0x7FFFFFFFFFFFFFFE +0 |
    +-----------------------+
    |   9223372036854776000 |
    +-----------------------+
    1 row in set (0.00 sec)
    
    mysql> select 0x7FFFFFFFFFFFFFFD +0;
    +-----------------------+
    | 0x7FFFFFFFFFFFFFFD +0 |
    +-----------------------+
    |   9223372036854776000 |
    +-----------------------+
    1 row in set (0.00 sec)

  9. #9
    Join Date
    Sep 2013
    Beans
    11

    Re: MySQL unsigned BIGINT issues

    Quote Originally Posted by trent.josephsen View Post
    ... Perhaps 0xFFFFFFFFFFFFFFFF doesn't make sense as a numeric literal, since its "numeric value" is greater than a 64-bit signed value can hold...

    I suppose we agree, that you can't see on the outside of any hex-literal whether it denotes a signed or unsigned integer.
    Thus each program has to make an educated guess how to interpret the received value.



    The mysql-client for instance never interprets hex-literals inside querys as signed; even not in the case when they should
    be inserted into a column holding signed integers (which is just another annoyance).



    The observed bug is something else.

    There is acertain threshold (0x002000000000000E or 9007199254741006)
    from whereon things start being buggy.

    mysql>SELECT 0x002000000000000E+0;
    +----------------------+
    |0x002000000000000E+0 |
    +----------------------+
    | 9007199254741006 | <-- STILL GOOD
    +----------------------+
    1 row in set(0.00 sec)


    mysql>SELECT 0x002000000000000E+1;
    +----------------------+
    |0x002000000000000E+1 |
    +----------------------+
    | 9007199254741008 | <-- BROKEN FROM HERE ON
    +----------------------+
    1 row in set(0.00 sec)


    In the next days I will report the bug to Oracle directly and see what they have to say about it.


    Regards

  10. #10

    Re: MySQL unsigned BIGINT issues

    That's consistent with IEEE 754's double (a standard for floating-point numbers), which has 53 bits of precision in the mantissa.

    I'm not saying it's not weird, I'm just saying it's not necessarily a bug. This page says that hex values act like integers (64-bit precision) in numeric contexts, but it doesn't specify the semantics for the conversion that must take place. It's not what I would expect, but that very page does warn you to use CAST(... AS UNSIGNED) if you want to always treat a hex string as an unsigned number, which you've already said works.

    The manual also says that "[i]f you use a string in an arithmetic operation, it is converted to a floating-point number during expression evaluation." That would seem to be the rule you're running into. It's a bit weird in light of the act-like-integers rule, but I can see why someone might do things this way.

    Weird? Yes. Poorly documented? Definitely. Buggy? That remains to be seen.

Page 1 of 2 12 LastLast

Bookmarks

Posting Permissions

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