Hi Mark, Keep in mind that the CAST() function doesn't work in MySQL 3.23. If you want something that will work with 3.23 and 4.0+, you can just add 0.0 to your expression:
SELECT 0 - unsigned_col + 0.0 AS alias FROM ... The result will have ".0" on the end then, but I think you can take care of that with FLOOR(expr) or TRUNCATE(expr, 0). Hope that helps. Matt ----- Original Message ----- From: "Mark Marshall" Sent: Thursday, November 20, 2003 11:05 AM Subject: Re: Why does -1 show up as 18446744073709551613? > That would be it! > > Not sure how I missed that. > > Thank you! > Mark > > > >>> Mikael Fridh <[EMAIL PROTECTED]> 11/20/03 11:44AM >>> > 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]
