Bug #120520 Wrong result for IS NOT UNKNOWN when NULLIF() uses CURRENT_USER() as an argument

• Submitted: 21 May 16:34
• Modified: 25 May 19:58
• Reporter: ss w
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.43
• OS: Any
• CPU Architecture: Any

[21 May 16:34] ss w

Description:
A query using the predicate NULLIF(CURRENT_USER(), integer_column) IS NOT UNKNOWN may evaluate inconsistently between WHERE filtering and direct SELECT expression evaluation.

In this case, direct evaluation of the expression indicates that the predicate evaluates to TRUE, while the same predicate in the WHERE clause incorrectly filters out the row and returns an empty result set.

The issue appears to be related to the use of CURRENT_USER() as an argument of NULLIF() together with implicit comparison between string and integer values.

How to repeat:
CREATE TABLE t1321 (c1 INTEGER UNSIGNED);
INSERT INTO t1321 (c1) VALUES (596739277);
SELECT t1321.c1 FROM t1321 WHERE (NULLIF(CURRENT_USER, t1321.c1) IS NOT UNKNOWN);
-- return 0 rows
SELECT SUM(count) FROM (SELECT (NULLIF(CURRENT_USER, t1321.c1) IS NOT UNKNOWN) IS TRUE AS count FROM t1321) AS ta_norec;
-- return 1

[25 May 19:58] Roy Lyseng

Thank you for the bug report.
Verified as described.