Bug #55362 MySQL does not properly evaluate NOT + NOT + IN() in WHERE
Submitted: 19 Jul 2010 12:19 Modified: 20 Jul 2010 6:33
Reporter: Philip Stoev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.5.6-m3-debug, 5.0, 5.1 bzr OS:Any
Assigned to: CPU Architecture:Any

[19 Jul 2010 12:19] Philip Stoev
Description:
This expression

( NOT ( NOT ( 6 ) ) ) IN ( 6 )

when used in a WHERE seems to evaluate to TRUE, even though it is FALSE when evaluated in a SELECT list.

How to repeat:
CREATE TABLE t1 (f1 integer);
INSERT INTO t1 VALUES (1);
SELECT ( NOT ( NOT ( 6 ) ) ) IN ( 3 ) FROM t1 WHERE ( NOT ( NOT ( 6 ) ) ) IN ( 6 ) ;

This example returns 0 , which is a contradiction -- if the expression is FALSE, then there WHERE should match no rows and no rows should be returned.
[19 Jul 2010 12:38] Sveta Smirnova
Thank you for the report.

Verified as described.
[20 Jul 2010 1:19] Davi Arnaut
From the manual:

* expr IN (value,...)

Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted.

--

If the constant value on the right is evaluated as a boolean, the evaluation seems right. Could anyone confirm?
[20 Jul 2010 5:05] Philip Stoev
The same issue is repeatable when the expression contains field names and not just constants. I used constants in order to simplify the test case to the max.

Either way, it seems to me that the expression should evaluate to the same value in both the WHERE and in the SELECT list.
[20 Jul 2010 6:33] Sveta Smirnova
Expression itself returns 0:

mysql> select ( NOT ( NOT ( 6 ) ) ) IN ( 6 ) ;
+--------------------------------+
| ( NOT ( NOT ( 6 ) ) ) IN ( 6 ) |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.09 sec)