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

Wrong result (missing rows) with impossible condition in NOT IN subquery

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0.4, 5.5.32, 5.3.12
    • Fix Version/s: 5.5.33a, 10.0.6, 5.3.13
    • Component/s: None
    • Labels:
      None

      Description

      The first test case returns 1 row on 5.3, 5.5, 10.0, and two rows on 5.2, MySQL 5.5, MySQL 5.6. Two rows is the correct result.

      CREATE TABLE t1 (a INT, c1 VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (2,'x'),(8,'d');
       
      CREATE TABLE t2 (m INT, n INT, c2 VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (0, 5, 'x'),(1, 4,'p');
       
      SELECT * FROM t1 WHERE c1 NOT IN ( 
        SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c 
        WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND ( t2b.m != a OR t2b.m = t2a.m )
      );

      Actual result:

      a	c1
      8	d

      Expected result:

      a	c1
      2	x
      8	d

      The second test case is a variation of the first one, only instead of the constant '0' condition we use IN subquery from a constant (empty) table. This test case returns the correct result on 5.3, but fails on 5.5.

      CREATE TABLE t1 (a INT, c1 VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (2,'x'),(8,'d');
       
      CREATE TABLE t2 (m INT, n INT, c2 VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (0, 5, 'x'),(1, 4,'p');
       
      CREATE TABLE t3 (i INT) ENGINE=MyISAM;
       
      SELECT * FROM t1 WHERE c1 NOT IN ( 
        SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c 
        WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 1 IN ( SELECT i FROM t3 ) ) AND ( t2b.m != a OR t2b.m = t2a.m )
      );

      5.3 result:

      a	c1
      2	x
      8	d

      5.5 result:

      a	c1
      8	d

      bzr version-info
      revision-id: igor@askmonty.org-20130830040242-tns2ir3y9rht6n19
      revno: 3687
      branch-nick: 5.3

      bzr version-info
      revision-id: psergey@askmonty.org-20130903144107-csv06zecpkp27oj0
      revno: 3876
      branch-nick: 5.5

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: