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.
Bug
Major
