[MDEV-11078] NULL NOT IN (non-empty subquery) should never return results - Jira

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.

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server