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

Wrong result (missing rows) on a NOT IN subquery with NULL values, materialization=off, TokuDB

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 5.5.34, 10.0.6
    • 5.5.35, 10.0.8
    • None

    Description

      I set it to Minor because the combination of pre-conditions (NOT IN subquery with two values in the left expression and with NULLs, non-default materialization=off, TokuDB) makes it a corner case.

      Test case:

      CREATE TABLE t1 (a INT) ENGINE=TokuDB;
      INSERT INTO t1 VALUES (NULL),(3);
       
      CREATE TABLE t2 (b INT, INDEX(b)) ENGINE=TokuDB;
      INSERT INTO t2 VALUES (4),(5);
       
      SELECT * FROM t1 t1a, t1 t1b WHERE ( t1a.a, t1b.a ) NOT IN ( SELECT b, b FROM t2 );
       
      SET optimizer_switch = 'materialization=off';
       
      SELECT * FROM t1 t1a, t1 t1b WHERE ( t1a.a, t1b.a ) NOT IN ( SELECT b, b FROM t2 );

      The semantics of this NOT NULL is a bit vague for me here, but considering the following

      MariaDB [test]> select (null, null) not in (select 4, 4 union select 5, 5);
      +-----------------------------------------------------+
      | (null, null) not in (select 4, 4 union select 5, 5) |
      +-----------------------------------------------------+
      |                                                NULL |
      +-----------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select (null, 3) not in (select 4, 4 union select 5, 5);
      +--------------------------------------------------+
      | (null, 3) not in (select 4, 4 union select 5, 5) |
      +--------------------------------------------------+
      |                                                1 |
      +--------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select (3, null) not in (select 4, 4 union select 5, 5);
      +--------------------------------------------------+
      | (3, null) not in (select 4, 4 union select 5, 5) |
      +--------------------------------------------------+
      |                                                1 |
      +--------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select (3, 3) not in (select 4, 4 union select 5, 5);
      +-----------------------------------------------+
      | (3, 3) not in (select 4, 4 union select 5, 5) |
      +-----------------------------------------------+
      |                                             1 |
      +-----------------------------------------------+
      1 row in set (0.00 sec)

      I presume the correct result is the one that is returned with materialization=on:

      MariaDB [test]> SELECT * FROM t1 t1a, t1 t1b WHERE ( t1a.a, t1b.a ) NOT IN ( SELECT b, b FROM t2 );
      +------+------+
      | a    | a    |
      +------+------+
      |    3 | NULL |
      | NULL |    3 |
      |    3 |    3 |
      +------+------+
      3 rows in set (0.01 sec)

      and the result with materialization=off is wrong:

      MariaDB [test]> SET optimizer_switch = 'materialization=off';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> SELECT * FROM t1 t1a, t1 t1b WHERE ( t1a.a, t1b.a ) NOT IN ( SELECT b, b FROM t2 );
      +------+------+
      | a    | a    |
      +------+------+
      |    3 |    3 |
      +------+------+
      1 row in set (0.00 sec)

      Also reproducible on mariadb-5.5.30-tokudb-7.0.1-linux-x86_64, but not on mysql-5.5.30-tokudb-7.1.0-linux-x86_64.

      Could not reproduce with InnoDB or MyISAM.

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.