MySQL Bugs: #30323: Math on integer columns with different attributes results in incorrect output

Bug #30323	Math on integer columns with different attributes results in incorrect output
Submitted:	8 Aug 2007 21:30	Modified:	9 Aug 2007 19:04
Reporter:	Arlo Gilbert	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.33-log	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
It is worth noting that as of 5.x MySQL no longer intelligently handles math on integer columns of different types.

Given the following table and data:
-- 
-- Table structure for table `test`
-- 

CREATE TABLE `test` (
  `a` smallint(5) unsigned NOT NULL,
  `b` smallint(5) unsigned NOT NULL,
  `c` int(11) NOT NULL
) ENGINE=MyISAM;

INSERT INTO `test` (`a`, `b`, `c`) VALUES 
(0, 0, 1);

Then running the following query:
SELECT SUM((a+b)-c) FROM test;

Gives the following result:
18446744073709551615

MySQL enforces the initial column type on the output of the formula rather than treating the output of (a+b) as standard integers.

The ONLY current workaround for this issue is to make every smallint(5) unsigned column into a int() column  with no unsigned attributes which pretty much defeats the purpose of allowing differing column types :)

How to repeat:
Follow the instructions in the description.

Suggested fix:
Unsure.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

First number is unsigned, so result is unsigned and can not contain -1. See also http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast

I do not see how this can be considered "not a bug".

There is no logical reason to have two unsigned columns (one containing "1" and the other containing "3") to cause a SUM of 1-3 to be anything other than -2.

While I can appreciate that you're busy I would really love even a very brief explanation of the logic or if there is a workaround other than changing all columns to signed.

see the description of NO_UNSIGNED_SUBTRACTION in
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html