MySQL Bugs: #73249: Inconsistent behavior when doing subtraction on columns

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

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.

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

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?