Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11078

NULL NOT IN (non-empty subquery) should never return results

    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
    • 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.

        Attachments

          Activity

            People

            • Assignee:
              varun Varun Gupta
              Reporter:
              ComaVN Roel Harbers
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: