This is in the "Upgrading from 3.23" manual: http://www.mysql.com/doc/en/Upgrading-from-3.23.html
"Note: when you use subtraction between integer values where one is of type UNSIGNED, the result will be unsigned. In other words, before upgrading to MySQL 4.0, you should check your application for cases where you are subtracting a value from an unsigned entity and want a negative answer or subtracting an unsigned value from an integer column. You can disable this behaviour by using the --sql-mode=NO_UNSIGNED_SUBTRACTION option when starting mysqld. See section 6.3.5 Cast Functions." In order to get your selects to work without changing column types look at the cast functions: http://www.mysql.com/doc/en/Cast_Functions.html "...If you are using numerical operations (like +) and one of the operands is unsigned integer, the result will be unsigned. You can override this by using the SIGNED and UNSIGNED cast operators..." Mike On Thursday 20 November 2003 17.10, Mark Marshall wrote: > I've been running this query for quite some time that basically says: > > SELECT (A + B + C) - (X + Y + Z) AS Variance > FROM ..... > > Up until now, this has been working correctly and showing up as > anything from -100 to +100. Now all of a sudden, it's showing up as > 18446744073709551613 instead of -1, 18446744073709551614 instead of -2, > etc. > > Now, A, B, C, X, Y & Z are all defined as "UNSIGNED" in the database > table. But again, this WAS working as of MySQL 3.23, and we've been > running it for well over a year. Now, on version 4.x, I started getting > the big numbers. > > I can only assume that there is some sort of precedence thing that > changed. Can anyone shed some light on this? Or maybe tell me what I > need to do differently to make this work now? > > Thanks, > Mark > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
