0

When I do union all on queries with unsigned int fields, it returns negative results.

Can someone explain this behavior to me ? How can I have real ids in "ticketIn" ?

Schema (MySQL v5.7)

CREATE TABLE IF NOT EXISTS `history` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `ticket` INT(11) unsigned NOT NULL,
  `group` INT(11) unsigned NOT NULL,
  `prev_group` INT(11) unsigned NOT NULL,
  `date_mod` DATETIME NOT NULL,
  PRIMARY KEY(Id)
);

-- data
INSERT INTO history
    (`ticket`, `group`, `prev_group`, `date_mod`)
VALUES
    (2201100001, 53042, 0, '2022-01-10 00:04:05.000'),
    (2201100002, 53042, 0, '2022-01-10 00:10:01.000'),
    (2201100003, 53042, 0, '2022-01-10 00:10:02.000'),
    (2201100003, 52973, 53042, '2022-01-10 00:11:25.000'),
    (2201100004, 53043, 0, '2022-01-10 00:54:41.000'),
    (2201100004, 52972, 53043, '2022-01-10 01:08:20.000')
;

Query #1

SET
  @s = CAST('2022-01-10' AS DATE);

Query #2

SELECT
  DATE_FORMAT(`history_out`.date_mod, '%Y-%m-%d') AS "thedate",
  `history_out`.group,
  `history_out`.ticket AS "ticketOut",
  NULL AS "ticketIn",
  `history_out`.id
FROM
  history `history_out`
WHERE
  (`history_out`.prev_group IN (53042))
  AND (`history_out`.date_mod >= @s)
UNION
ALL
SELECT
  DATE_FORMAT(`history_in`.date_mod, '%Y-%m-%d') AS "thedate",
  `history_in`.group,
  NULL AS "ticketOut",
  `history_in`.ticket AS "ticketIn",
  `history_in`.id
FROM
  history `history_in`
WHERE
  (`history_in`.group IN (53042))
  AND (`history_in`.date_mod >= @s);
thedate group ticketOut ticketIn id
2022-01-10 52973 2201100003 4
2022-01-10 53042 -2093867295 1
2022-01-10 53042 -2093867294 2
2022-01-10 53042 -2093867293 3

View on DB Fiddle

2
  • Actually, this appends only in MySQL <= 5.7 and MariaDb <= 10.2 (dbfiddle.uk/…)
    – Martial
    Jan 10 at 10:44
  • -2093867293 smells like a large (> 2^31) INT UNSIGNED being displayed as SIGNED.
    – Rick James
    Jan 11 at 17:09
1

Try this:

SELECT
  DATE_FORMAT(`history_out`.date_mod, '%Y-%m-%d') AS "thedate",
  `history_out`.group,
  `history_out`.ticket AS "ticketOut",
  cast(NULL as unsigned int) AS "ticketIn",
  `history_out`.id
FROM
  history `history_out`
WHERE
  (`history_out`.prev_group IN (53042))
  AND (`history_out`.date_mod >= @s)
UNION
ALL
SELECT
  DATE_FORMAT(`history_in`.date_mod, '%Y-%m-%d') AS "thedate",
  `history_in`.group,
  NULL AS "ticketOut",
  `history_in`.ticket AS "ticketIn",
  `history_in`.id
FROM
  history `history_in`
WHERE
  (`history_in`.group IN (53042))
  AND (`history_in`.date_mod >= @s);

dbfiddle.uk

PS. when I was working with RDBMS, the first select in a union decided the format of the columns. MySQL 5.7 may be as old.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.