Not the sexiest blog title in the world but I thought I’d knock up a little post on the behaviour of MySQL and SQL Server with integer subtraction. How would you expect a database system to behave with positive and negative data types? Microsoft SQL Server doesn’t really have unsigned data types. All integer types can be positive and negative with the exception of TINYINT. MySQL implements the concept of signedness so we can specify that TINYINT ranges from –128 to 127 or 0 to 255.
What would you expect SQL Server to do with this?
DECLARE @num1 TINYINT = 50, @num2 TINYINT = 75; SELECT @num1 - @num2;
Well it errors. Good RDBMS!
Msg 8115, Level 16, State 2, Line 3 Arithmetic overflow error converting expression to data type tinyint.
What does MySQL do? Lets see.
USE test; CREATE TABLE nums ( num1 INTEGER UNSIGNED NOT NULL, num2 INTEGER UNSIGNED NOT NULL ); INSERT INTO nums ( num1, num2 ) VALUES ( 50, 75 ); SELECT num1 - num2 FROM nums;
Oh dear not a pretty behaviour! Bad RDBMS! A colleague said to me “you need to set sql_mode to traditional”. Mmmmmm, I thought I was running in traditional mode already. As it turns out we need to set the NO_UNSIGNED_SUBTRACTION option.
SET @@SESSION.sql_mode = 'TRADITIONAL,NO_UNSIGNED_SUBTRACTION'; SELECT num1 - num2 FROM nums;
Got to love MySQL for keeping you on your toes!