Details
-
Type:
Bug
-
Status: Closed (View Workflow)
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.1.18, 5.5, 10.0, 10.1, 10.2
-
Fix Version/s: 5.5.55
-
Component/s: Data Manipulation - Subquery
-
Labels:None
-
Environment:OS X El Capitan, MariaDB installed through Homebrew
-
Sprint:10.0.30
Description
I'm confused by the behaviour of `NULL` in a `NOT IN` expression. (MariaDB 10.1.18):
MariaDB [(none)]> select 1 from dual where null not in (1);
|
Empty set (0.00 sec)
|
|
|
MariaDB [(none)]> select 1 from dual where null not in (select 1);
|
Empty set (0.00 sec)
|
|
|
MariaDB [(none)]> select 1 from dual where null not in (select 1 from dual);
|
Empty set (0.00 sec)
|
This is as I expected, since `NULL` is not equal or unequal to anything.
MariaDB [(none)]> select 1 from dual where null not in (select 1 from mysql.user);
|
+---+
|
| 1 |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.00 sec)
|
Huh? How can that be? Maybe it's because I can't read the mysql.user table?
MariaDB [(none)]> select distinct 1 from mysql.user;
|
+---+
|
| 1 |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.00 sec)
|
No, it returns a value. When selecting the one from a actual table, not `dual`, or an inline list expression, the behaviour is different.
Is this a bug in MariaDB? An old MySQL server (5.5.40) I tried this with gave the expected empty set for all queries.