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