Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Location
    Lincs, UK
    Posts
    152

    MySQL query doing odd things - removing negative sign for no apparant reason!

    I've got a problem with an SQL query that is behaving badly. Its probably really obvious, but I can't see the problem!
    I have a table with 3 fields:
    Code:
    money    bigint(20)
    income   int(12)
    userID   mediumint(7)
    I have a row that has a negative money and a value of 0 for income. When I update the money value to add "0" to it, nothing happens (as expected). When I update the money field to add the income field (which is equal to 0) to it, MySQL flips the sign to make the money field positive.

    The following queries show the problem:
    Code:
    mysql> SELECT money, income from users where userID=327961;
    +----------------------+--------+
    | money                | income |
    +----------------------+--------+
    | -9223372036854775807 |      0 |
    +----------------------+--------+
    1 row in set (0.00 sec)
    
    mysql> UPDATE `users` SET money = money + 0 WHERE userID =327961;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0
    
    mysql> SELECT money, income from users where userID=327961;
    +----------------------+--------+
    | money                | income |
    +----------------------+--------+
    | -9223372036854775807 |      0 |
    +----------------------+--------+
    1 row in set (0.00 sec)
    
    mysql> UPDATE `users` SET money = money + income WHERE userID =327961;
    Query OK, 1 row affected, 1 warning (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 1
    
    mysql> SELECT money, income from users where userID=327961;
    +---------------------+--------+
    | money               | income |
    +---------------------+--------+
    | 9223372036854775807 |      0 |
    +---------------------+--------+
    1 row in set (0.00 sec)
    
    mysql>
    Notice that there is no negative sign after the third select!!

    So, we are saying that executing an update where 0 is specified in the query results in the correct action but if you take the 0 from another field it takes the negative sign away...

    This for various reasons is a rather urgent problem. This problem has only appeared after upgrading from MySQL 4.0 to MySQl 5.1. This problem does not occur in MySQL 4.1.

    Any suggestions greatly appreciated!

    Alex
    Last edited by alex-davies; 01-10-2007 at 05:26 PM. Reason: Mistake in line 1 :)

  2. #2
    Join Date
    Dec 2006
    Location
    Charlotte NC
    Posts
    155
    It appears that the money column definition "money bigint(20)" may be too small for the value you are trying populating with it. Try increasing the definition to "money bigint(32)".
    Caro.net :: Engineered Hosting
    Engineered Hosting solutions including Cloud, Dedicated, Colocation, and Managed Services.

Posting Permissions

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