After making the following query:
SELECT CP.code
FROM cart_products AS CP
INNER JOIN products AS P
ON P.code = CP.code
WHERE CP.cartID = 22207
AND (P.stock - CP.quantity) > 0
I got the following error:
#1690 - BIGINT UNSIGNED value is out of range in (`DB_NAME`.`p`.`stock` - `DB_NAME`.`cp`.`quantity`)'
The structure of the tables is as follows:
CREATE TABLE `products` (
`productID` int(10) UNSIGNED NOT NULL,
`code` tinytext COLLATE latin1_general_ci NOT NULL,
`stock` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
CREATE TABLE `cart_products` (
`cartID` int(10) UNSIGNED NOT NULL,
`code` tinytext COLLATE latin1_general_ci NOT NULL,
`quantity` mediumint(8) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
How can I do the query without getting this error?
The error is caused by an " unsigned integer overflow "
The solution I found was to convert (
CAST
) the unsigned (eg: UNSIGNED
) integers to signed (eg: SIGNED
)Solution:
The error happens because the calculation between 2 unsigned integers is expected to also produce an unsigned integer. But if the calculation can result in a negative number (which is your case), obviously, this is not valid.
One solution is the
CAST
one you mention in your answer.But simpler would be to use the following equivalent condition to avoid the calculation and the risk of a negative number: