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

LP:1009187 - Wrong result for a query with [NOT] IN subquery predicate if the left part of the predicate is explicit NULL

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      Let's create and populate tables t1 and subq with the following commands:

      CREATE TABLE t1 (pk INT NOT NULL, i INT);
      INSERT INTO t1 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL);
      CREATE TABLE t2 (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk));
      INSERT INTO t2 VALUES (0,0), (1,1), (2,2), (3,3);

      Then the query
      SELECT * FROM t1 WHERE NULL NOT IN (SELECT i FROM t2 WHERE t2.pk = t1.pk)
      is expected to return an empty set.

      However in MariaDB 5.1/5.2/5.3/5.5 we have:

      MariaDB [test]> SELECT * FROM t1 WHERE NULL NOT IN (SELECT i FROM t2 WHERE t2.pk = t1.pk);
      --------+

      pk i

      --------+

      1 NULL
      2 NULL
      3 NULL

      --------+

      We also have wrong results for the query
      SELECT * FROM t1 WHERE NULL IN (SELECT i FROM t2 WHERE t2.pk = t1.pk) IS UNKNOWN:

      MariaDB [test]> SELECT * FROM t1 WHERE NULL IN (SELECT i FROM t2 WHERE t2.pk = t1.pk) IS UNKNOWN;
      --------+

      pk i

      --------+

      0 NULL

      --------+

      This bug supposedly is fixed mysql-5.6 (see http://bugs.mysql.com/bug.php?id=58628)

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            igor Igor Babaev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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