Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.18, 5.5, 10.0, 10.1, 10.2
-
None
-
OS X El Capitan, MariaDB installed through Homebrew
-
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.