Bug #113185 Unexpected Results when using NULLIF Function in Filter

• Submitted: 22 Nov 2023 14:49
• Modified: 23 Nov 2023 4:19
• Reporter: Suyang Zhong
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.33, 8.1.0, 8.0.35
• OS: Any
• CPU Architecture: Any

[22 Nov 2023 14:49] Suyang Zhong

Description:
Consider the test case below.  It is unexpected that both queries return the row since the expression `NULLIF` cannot be evaluated to `TRUE` at the same time.

mysql> CREATE TABLE t1(c0 INT);
;Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> SELECT * FROM t1 WHERE NULLIF(-1, c0*(1/true));
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t1 WHERE NULLIF(-1, c0*(1/true)) IS NULL; 
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE t1(c0 INT);
INSERT INTO t1 VALUES (1);

SELECT * FROM t1 WHERE NULLIF(-1, c0*(1/true));          -- 1
SELECT * FROM t1 WHERE NULLIF(-1, c0*(1/true)) IS NULL;  -- 1

[22 Nov 2023 15:03] MySQL Verification Team

Hi Mr. Zhonh,

Thank you for your bug report.

However, this is not a bug.

This is evident from the full and proper output from the test case:

mysql> CREATE TABLE t1(c0 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> select NULLIF(-1, c0*(1/true)) from t1;
+-------------------------+
| NULLIF(-1, c0*(1/true)) |
+-------------------------+
|                      -1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE NULLIF(-1, c0*(1/true));
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

You should differentiate a select list from the WHERE condition. As NULLIF() returns -1 and -1 is NOT 0, hence the values of the columns from the table are returned !!!!!

Not a bug.

[23 Nov 2023 4:19] Suyang Zhong

Dear Verification Team,

Thank you for your response regarding the issue I reported, I appreciate your time in reviewing it. 

I believe there might have been a misunderstanding about the nature of the issue. It is expected that the `NULLIF()` expression returns -1 and the first SELECT query returns the values of the table. However, it is unexpected that the second SELECT query returns any value, because if `NULLIF` returns `-1` and thus `NULLIF  IS NULL` should be evaluated to 0, and no value should be returned. In this case, the SELECT query returns 1, which is unexpected. 

I kindly request that the team reevaluate this matter in light of this additional information. I am confident that a closer look will help in recognizing the issue as a bug.

Sincerely,

Suyang