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
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.5
• OS: Any
• 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