Why am I getting a BIGINT out of range error when summing 2 integer values that should be within range?
SELECT
*,
numberSent - cd
FROM
(SELECT
e.id,
e.numberSent,
COUNT(d.id) cd
FROM
emails e
JOIN deliveries d
ON e.id = d.emailId
WHERE e.numberSent > 0
GROUP BY e.id) a
returns with an error
Error Code: 1690
BIGINT UNSIGNED value is out of range in '(`a`.`numberSent` - `a`.`cd`)'
I'm confused because numberSent
is an unsigned integer (not a bigint) and its largest value is 613545. The largest value of the count value cd
is 523082 so the sum is well in the range of an integer.
Relevant table structure:
CREATE TABLE `emails` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`numberSent` INT(10) UNSIGNED DEFAULT '0',
PRIMARY KEY (`id`),
) ENGINE=INNODB;
CREATE TABLE `deliveries` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`emailId` INT(11) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `delivery_emailid_index` (`emailId`),
KEY `delivery_created_at_index` (`created_at`),
) ENGINE=INNODB;
3 - 5
would be-2
which is out of UNSIGNED range? – ypercubeᵀᴹ Jun 25 '20 at 16:55