0

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
    Because it is not a sum but a substraction? 3 - 5 would be -2 which is out of UNSIGNED range? – ypercubeᵀᴹ Jun 25 at 16:55
  • :facepalm: thanks – chiliNUT Jun 25 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.