Negative values on unsigned int with union all

Question

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

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

Answer 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.