MySQL Bugs: #95954: CAST of negative function return value to UNSIGNED malfunctions with BIGINT

Bug #95954	CAST of negative function return value to UNSIGNED malfunctions with BIGINT
Submitted:	24 Jun 2019 15:09	Modified:	25 Oct 2019 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

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.

Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh

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.

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.

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.

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.