MySQL Bugs: #63427: results for "not in" and "!=" for the same values are different

Bug #63427	results for "not in" and "!=" for the same values are different
Submitted:	25 Nov 2011 9:56	Modified:	22 Dec 2011 19:48
Reporter:	Steven Hartland	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.5.11-log, 5.5.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When performing a select on a table filtering on a float column the results for "not in" and "!=" are different with the results for not in being wrong.

How to repeat:
CREATE TABLE `float_test` (
  `value` float(8,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into float_test values( 0 );
insert into float_test values( 0.3 );
insert into float_test values( 0.38 );

select *
from float_test
where value not in ( 0.3, 0.38 );

The results from this select will show all values in the table e.g. 0, 0.3 & 0.38 even though 0.3 and 0.38 should be excluded

select *
from float_test
where value != 0.3
and value != 0.38;

The result here shows the correct answer only showing the value 0

Suggested fix:
The not in case should behave identically to that of the != case

Please read the Manual regarding float problems:

http://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

h:\dbs>55

h:\dbs>h:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.18-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use test
Database changed
mysql 5.5 >CREATE TABLE `float_test` (
    ->   `value` float(8,2) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.17 sec)

mysql 5.5 >
mysql 5.5 >insert into float_test values( 0 );
Query OK, 1 row affected (0.10 sec)

mysql 5.5 >insert into float_test values( 0.3 );
Query OK, 1 row affected (0.07 sec)

mysql 5.5 >insert into float_test values( 0.38 );
Query OK, 1 row affected (0.08 sec)

mysql 5.5 >
mysql 5.5 >select *
    -> from float_test
    -> where value not in ( 0.3, 0.38 );
+-------+
| value |
+-------+
|  0.00 |
|  0.30 |
|  0.38 |
+-------+
3 rows in set (0.01 sec)

mysql 5.5 >select *
    -> from float_test
    -> where value != 0.3
    -> and value != 0.38;
+-------+
| value |
+-------+
|  0.00 |
+-------+
1 row in set (0.01 sec)

mysql 5.5 >drop table float_test;
Query OK, 0 rows affected (0.04 sec)

mysql 5.5 >CREATE TABLE `float_test` (
    ->   `value` decimal(8,2) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)

mysql 5.5 >
mysql 5.5 >insert into float_test values( 0 );
Query OK, 1 row affected (0.04 sec)

mysql 5.5 >insert into float_test values( 0.3 );
Query OK, 1 row affected (0.03 sec)

mysql 5.5 >insert into float_test values( 0.38 );
Query OK, 1 row affected (0.03 sec)

mysql 5.5 >
mysql 5.5 >select *
    -> from float_test
    -> where value not in ( 0.3, 0.38 );
+-------+
| value |
+-------+
|  0.00 |
+-------+
1 row in set (0.00 sec)

mysql 5.5 >
mysql 5.5 >select *
    -> from float_test
    -> where value != 0.3
    -> and value != 0.38;
+-------+
| value |
+-------+
|  0.00 |
+-------+
1 row in set (0.00 sec)

mysql 5.5 >

Yes we already know from the page linked that using decimal instead of float works due to the way floats aren't accurately stored; but the point of this bug is to what "not in" and "!=" should NEVER produce different results.

As you can see from the results of the test "value != 0.3 and value != 0.38" produce different results than "value not in ( 0.3, 0.38 )".

So the issue is the different results of two logically identical pieces of SQL, not that equivalence on floats can be inaccurate.

Hope that makes sense?

my pow: they both return the same, undefined behavior

Seems this bug was closed again without any reason, when there's clearly seriously nasty undefined behaviour going no which is not related to the man page like as it makes no mention of not in and != returning different results.

re-opening as there clearly is an issue there.

Please read comment by Jonas: "my pow: they both return the same, undefined behavior"

Even fact that != will return same result is not guaranteed, so don't expect same result for "not in" either. This is not a bug.

Sorry to be a pain but I believe there was a typo in Jonas comment and instead he mean't as otherwise it doesn't make sence:-
my pow: they "should" both return the same, undefined behaviour

I can also find no reference in the "not in" documentation to state that it can return different results than multiple "!=" tests. As "!=" and "not in" are logically identical there is really is no reason they should.

If this behaviour is documentation for "not in" then please link it, otherwise I'm afraid I still class this as a serious bug given logically identical SQL produces two different results.

For reference the expected behaviour of not in is documented here:-
http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html

Thank you for the feedback.

Makes sense. Verified as described.

Also tests showed this is really conversion problem: numbers in IN clause are treated as DECIMAL while value is FLOAT.

Other workaround:

mysql> select * from float_test where value not in ( select 0.3  from dual union select 0.38 from dual);
+-------+
| value |
+-------+
|  0.00 |
+-------+
1 row in set (0.00 sec)

mysql> select * from float_test where cast(value as decimal(8,2)) not in ( 0.30
, 0.38 );
+-------+
| value |
+-------+
|  0.00 |
+-------+
1 row in set (0.00 sec)