Bug #95954 CAST of negative function return value to UNSIGNED malfunctions with BIGINT
Submitted: 24 Jun 15:09 Modified: 25 Oct 12:18
Reporter: Manuel Rigger Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.16, 5.7.26, 5.6.44 OS:Any
Assigned to: CPU Architecture:Any

[24 Jun 15:09] Manuel Rigger
Description:
When functions such as COALESCE() or IFNULL() receive a BIGINT column argument, casting a negative return value to UNSIGNED unexpectedly yields zero.

How to repeat:
CREATE TABLE t0(c0 BIGINT UNSIGNED);
INSERT INTO t0(c0) VALUES(NULL);
SELECT * FROM t0 WHERE CAST(COALESCE(t0.c0, -1) AS UNSIGNED); -- expected: row is selected, actual: no row is selected
SELECT * FROM t0 WHERE CAST(IFNULL(t0.c0, -1) AS UNSIGNED); -- expected: row is selected, actual: no row is selected

This potential bug also occurs when the expression is evaluated right after the SELECT:

SELECT CAST(COALESCE(t0.c0, -1) AS UNSIGNED) IS TRUE FROM t0; -- expected: 1, actual: 0
SELECT CAST(COALESCE(t0.c0, -1) AS UNSIGNED) FROM t0; -- expected: 18446744073709551615, actual: 0

When sequentially evaluating the expressions, everything seems to work as expected:

SELECT COALESCE(t0.c0, -1) FROM t0; -- -1
SELECT CAST(-1 AS UNSIGNED); -- 18446744073709551615

When replacing the BIGINT with another integer type, the bug is also not triggered.
[25 Jun 5:08] Umesh Shastry
Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh
[4 Aug 19:12] Oleksandr Peresypkin
A patch for version 8.0

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 95954-fix-cast-unsigned-v8.0.patch (application/octet-stream, text), 2.45 KiB.

[4 Aug 19:13] Oleksandr Peresypkin
A patch for version 5.7

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 95954-fix-cast-unsigned-v5.7.patch (application/octet-stream, text), 2.39 KiB.

[4 Aug 19:14] Oleksandr Peresypkin
A patch for version 5.6

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 95954-fix-cast-unsigned-v5.6.patch (application/octet-stream, text), 2.39 KiB.

[11 Nov 17:34] Jon Stephens
Documented fix in the MySQL 8.0.19 changelog as follows:

    When a function such as COALESCE() or IFNULL() was passed a
    BIGINT column value, casting a negative return value from this
    function to UNSIGNED unexpectedly yielded zero.

    Our thanks to Oleksandr Peresypkin for this contribution.

Closed.