Bug #118806 NULLIF with VARCHAR omits rows unexpectedly

• Submitted: 9 Aug 13:00
• Modified: 11 Aug 5:27
• Reporter: Emily Ong
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0,9.0, 8.0.43, 8.4.6, 9.4.0
• OS: Ubuntu
• CPU Architecture: x86

[9 Aug 13:00] Emily Ong

Description:
Using NULLIF with VARCHAR produces the wrong evaluation when used in a WHERE clause and hence omits rows unexpectedly.

How to repeat:
Consider the following example:

```
CREATE TABLE IF NOT EXISTS t0(c0 VARCHAR(500)) ;
INSERT INTO t0(c0) VALUES(-1457915697);

SELECT
	t0.c0 AS ref0
FROM t0
WHERE (NULLIF(-105689632, t0.c0) IS NOT NULL);
```

Based on the documentation (https://dev.mysql.com/doc/refman/8.4/en/flow-control-functions.html#function_nullif), the NULLIF(expr1, expr2) function:
- Returns NULL if expr1 = expr2 is true
- Otherwise returns expr1.

From the example above, we expect that the return result is:
| ref0 |
| ---- |
| -1457915697 |

But, the actual result is empty:
| ref0 |
| ---- |

This is not reproducible in MariaDB, which is MySQL compatible.

[11 Aug 5:27] MySQL Verification Team

Hello Emily Ong,

Thank you for the report and test case.

regards,
Umesh