Bug #95889 Functional index seems to malfunction with UNSIGNED column
Submitted: 19 Jun 2019 22:08 Modified: 20 Aug 2019 2:59
Reporter: Manuel Rigger Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.16 OS:Ubuntu
Assigned to: CPU Architecture:x86

[19 Jun 2019 22:08] Manuel Rigger 
Description:
Using functions such as IFNULL or ABS in a functional index causes an error when a column used as an argument to such a function is declared as UNSIGNED and when that column holds a value that exceeds the corresponding signed integer type:

unexpected: ERROR 3752 (22003): Value is out of range for functional index 'i0'

How to repeat:
CREATE TABLE t0(c0 INT UNSIGNED);
INSERT INTO t0(c0) VALUES(4294967294);
CREATE INDEX i0 ON t0(ABS(t0.c0)); -- unexpected: ERROR 3752 (22003): Value is out of range for functional index 'i0'

This also applies to IFNULL (and probably other functions):
CREATE INDEX i1 ON t0(IFNULL(t0.c0, t0.c0)); -- unexpected: ERROR 3752 (22003): Value is out of range for functional index 'i1'

Retrieving the value works as expected:
SELECT ABS(t0.c0) FROM t0; -- 4294967294

Also replacing INT UNSIGNED by BIGINT works as expected.
[20 Jun 2019 5:37] MySQL Verification Team 
Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh
[20 Aug 2019 2:59] Jon Stephens 
Documented fix as follows in the MySQL 8.0.18 changelog:

    Using a function such as IFNULL() or ABS() in a functional index
    led to the error -Value is out of range for functional index...-
    when an UNSIGNED column used as an argument to such a function
    held a value exceeding that of the corresponding signed integer
    type.

Closed.