Description:
IF, CASE and COALESCE gives different results for INSERT and SELECT when the input arguments are signed and unsigned integers.
How to repeat:
CREATE TABLE source(bt INTEGER, bf INTEGER, i8u BIGINT UNSIGNED, i8s BIGINT);
INSERT INTO source VALUES
(1,0,0,-9223372036854775808), (1,0,18446744073709551615,9223372036854775807);
CREATE TABLE target
SELECT IF(bt,i8u,i8s) AS u, IF(bf,i8u,i8s) AS s
FROM source;
SHOW CREATE TABLE target;
| target | CREATE TABLE `target` (
`u` decimal(20,0) DEFAULT NULL,
`s` decimal(20,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
SELECT IF(bt,i8u,i8s) AS u, IF(bf,i8u,i8s) AS s
FROM source;
+------+----------------------+
| u | s |
+------+----------------------+
| 0 | -9223372036854775808 |
| -1 | 9223372036854775807 |
+------+----------------------+
SELECT * FROM target;
+----------------------+----------------------+
| u | s |
+----------------------+----------------------+
| 0 | -9223372036854775808 |
| 18446744073709551615 | 9223372036854775807 |
+----------------------+----------------------+
DROP TABLE target;
CREATE TABLE target
SELECT CASE WHEN bt THEN i8u ELSE i8s END AS u,
CASE WHEN bf THEN i8u ELSE i8s END AS s
FROM source;
SELECT CASE WHEN bt THEN i8u ELSE i8s END AS u,
CASE WHEN bf THEN i8u ELSE i8s END AS s
FROM source;
+------+----------------------+
| u | s |
+------+----------------------+
| 0 | -9223372036854775808 |
| -1 | 9223372036854775807 |
+------+----------------------+
SELECT * FROM target;
+----------------------+----------------------+
| u | s |
+----------------------+----------------------+
| 0 | -9223372036854775808 |
| 18446744073709551615 | 9223372036854775807 |
+----------------------+----------------------+
DROP TABLE target;
CREATE TABLE target
SELECT CASE bt WHEN TRUE THEN i8u WHEN FALSE THEN i8s END AS u,
CASE bf WHEN TRUE THEN i8u WHEN FALSE THEN i8s END AS s
FROM source;
SELECT CASE bt WHEN TRUE THEN i8u WHEN FALSE THEN i8s END AS u,
CASE bf WHEN TRUE THEN i8u WHEN FALSE THEN i8s END AS s
FROM source;
+------+----------------------+
| u | s |
+------+----------------------+
| 0 | -9223372036854775808 |
| -1 | 9223372036854775807 |
+------+----------------------+
SELECT * FROM target;
+----------------------+----------------------+
| u | s |
+----------------------+----------------------+
| 0 | -9223372036854775808 |
| 18446744073709551615 | 9223372036854775807 |
+----------------------+----------------------+
DROP TABLE target;
CREATE TABLE target
SELECT COALESCE(i8u, i8s) AS u, COALESCE(i8s, i8u) AS s
FROM source;
SELECT COALESCE(i8u, i8s) AS u, COALESCE(i8s, i8u) AS s
FROM source;
+---------------------+----------------------+
| u | s |
+---------------------+----------------------+
| 0 | -9223372036854775808 |
| 9223372036854775807 | 9223372036854775807 |
+---------------------+----------------------+
SELECT * FROM target;
+----------------------+----------------------+
| u | s |
+----------------------+----------------------+
| 0 | -9223372036854775808 |
| 18446744073709551615 | 9223372036854775807 |
+----------------------+----------------------+
DROP TABLE source, target;
Suggested fix:
Fix result_type() and field_type() calculations so that they are consistent.