Bug #83148 IF and friends gives wrong type for signed and unsigned integer
Submitted: 26 Sep 2016 8:57 Modified: 13 Dec 2016 17:01
Reporter: Roy Lyseng Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any

[26 Sep 2016 8:57] Roy Lyseng
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.
[13 Dec 2016 17:01] Paul DuBois
Posted by developer:
 
Noted in 8.0.1 changelog.

CASE, COALESCE(), IF() and IFNULL() could merge a mix of signed and
unsigned arguments incorrectly and produce an incorrect result type.
[26 Jan 2017 19:12] Erlend Dahl
Bug#83923 bit(n) data type confused when used with IF(), IFNULL()...

was marked as a duplicate
[10 Apr 2023 6:56] Lucas Lu
This bug still exists in latest MySQL 5.7 version. Can we get a fix on that too?
[16 Apr 2023 19:29] Roy Lyseng
5.7 is out of support in a short time.
We recommend upgrading to 8.0.