Bug #73249 Inconsistent behavior when doing subtraction on columns
Submitted: 9 Jul 2014 20:06 Modified: 14 Jul 2014 17:18
Reporter: CJ Keeney Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.5.36-log, 5.6.38, 5.6.20 OS:Linux
Assigned to: CPU Architecture:Any
Tags: decimal, INT, signed, UNSIGNED

[9 Jul 2014 20:06] CJ Keeney
Description:
subtraction across two columns, ie:

    select col1 - col2 from t

has inconsistent behavior depending on column type.

If the columns are unsigned integers, then a negative difference causes an overflow.
If the columns are unsigned decimals, then a negative difference is simply returned, and the result appears to be signed just fine.

This is possibly related to the fact that the mantissa of decimals does not include the bit for the sign, but it does for integers.

How to repeat:
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` int(10) unsigned DEFAULT NULL,
  `col2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `col1`, `col2`) VALUES
	(1, 5, 10);

CREATE TABLE IF NOT EXISTS `test2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` decimal(10,0) unsigned DEFAULT NULL,
  `col2` decimal(10,0) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `test2` (`id`, `col1`, `col2`) VALUES
	(1, 5, 10);

/* this query runs fine */
select col1 - col2 from test2;
/* this query overflows */
select col1 - col2 from test;

Suggested fix:
Subtraction across unsigned decimals resulting in a negative difference should result in an overflow or warning or something, or sql_mode should default to NO_UNSIGNED_SUBTRACTION so that unsigned ints and unsigned decimals behave identically.
[14 Jul 2014 12:07] Umesh Shastry
Hello Keeney,

Thank you for the report.
Verified as described.

Please see some details given about DECIMAL http://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html

Thanks,
Umesh
[14 Jul 2014 17:18] CJ Keeney
Yes I read over that before I submitted the bug report.  I think the issue is that the mantissa of the decimal does not include the sign bit, but the mantissa of integers and similar types does include the sign bit.  I think by default these 2 operations should behave identically.  Do you agree or just think this is a case of RTFM?