[MDEV-9369] IN operator with ( num, NULL ) gives inconsistent result Created: 2016-01-06  Updated: 2016-03-21  Resolved: 2016-03-21

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 5.3.13, 5.5, 10.0, 10.1
Fix Version/s: 10.1.13

Type: Bug Priority: Major
Reporter: Dylan Su Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: upstream-fixed

Sprint: 5.5.48-0, 10.1.13

 Description   

Output:

mysql> select (1,null) not in ((2,2),(3,3)), (1,null) not in ((2,2)), (1,null) not in ((3,3));
 
+-------------------------------+-------------------------+-------------------------+
 
| (1,null) not in ((2,2),(3,3)) | (1,null) not in ((2,2)) | (1,null) not in ((3,3)) |
 
+-------------------------------+-------------------------+-------------------------+
 
|                          NULL |                       1 |                       1 |
 
+-------------------------------+-------------------------+-------------------------+
 
1 row in set (0.00 sec)
 
 
 
mysql> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.1.10-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)

Recreate:

select (1,null) not in ((2,2),(3,3)), (1,null) not in ((2,2)), (1,null) not in ((3,3));

Problem:
========
(1,null) not in ((2,2),(3,3)) is expected to return true.

 Comments   
Comment by Elena Stepanova [ 2016-01-11 ]

Thanks for the report and the test case.
Also reproducible with MySQL 5.6, but seems to be fixed in 5.7.

Comment by Sergei Golubchik [ 2016-03-16 ]

ok to push

Generated at Thu Feb 08 07:34:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.