Bug #119650 IN operator returns unexpected result when one of values in the list is NULL

• Submitted: 9 Jan 2:04
• Modified: 22 Apr 20:58
• Reporter: chi zhang
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 9.5.0
• OS: Any
• CPU Architecture: Any

[9 Jan 2:04] chi zhang

Description:
Hi,

In the following test case, there is only one value 123 in the column c0 of table t1.

There is an IN operation in the SELECT, whose left operand is c0, and right operand contains `true` and `NULL`. I expected the result of this expression is NULL, as in the document https://dev.mysql.com/doc/refman/9.5/en/comparison-operators.html#operator_in, `To comply with the SQL standard, IN() returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.` However, this expression returns 0.

```
CREATE TABLE t1(c0 DECIMAL) ;
INSERT INTO t1(c0) VALUES(123);
SELECT c0 IN (true, (- ((NULL) > (CAST(-1147964344 AS DECIMAL))))) FROM t1; -- 0
```

How to repeat:
```
CREATE TABLE t1(c0 DECIMAL) ;
INSERT INTO t1(c0) VALUES(123);
SELECT c0 IN (true, (- ((NULL) > (CAST(-1147964344 AS DECIMAL))))) FROM t1;
```

[9 Jan 7:32] Roy Lyseng

Thank you for the bug report.
Verified as described.

[22 Apr 20:58] Edward Gilmore

Posted by developer:
 
Added the following note to the MySQL Server 9.7.0 release notes:
		
IN operator returned an unexpected result if one of values in the list was NULL.