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

Wrong result (missing row) on LEFT JOIN with a view, DISTINCT

    Details

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

      Description

      The problem appeared in the 5.3 tree with the following revision:

      revno: 3645 [merge]
      revision-id: igor@askmonty.org-20130327155816-ocjsyxyqgcsraf0s
          ------------------------------------------------------------
          revno: 3639.1.1
          revision-id: igor@askmonty.org-20130323043306-2cteo3ndk22af921
          message:
            Fixed bug mdev-4318.
            In some cases, when using views the optimizer incorrectly determined
            possible join orders for queries with nested outer and inner joins.
            This could lead to invalid execution plans for such queries.

      With the data provided in the test case, the test query produces a result set consisting of 31 rows:

      SELECT DISTINCT alias1.id 
      FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3
      ON alias2.id = alias3.i ON alias1.i1 = alias2.i1
      WHERE alias3.i != 3;
      id
      7
      9
      18
      20
      24
      50
      51
      61
      71
      94
      97
      100
      104
      114
      119
      128
      129
      140
      8
      43
      45
      64
      74
      77
      78
      93
      101
      107
      132
      133
      156

      However, a similar query with an extended select list (but the same joins and WHERE clause) produces 32 rows:

      SELECT DISTINCT alias1.id, alias2.id, alias3.i, alias1.i1, alias2.i1 
      FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3
      ON alias2.id = alias3.i ON alias1.i1 = alias2.i1
      WHERE alias3.i != 3;
      id      id      i       i1      i1
      7       7       7       6       6
      9       7       7       6       6
      18      7       7       6       6
      20      7       7       6       6
      24      7       7       6       6
      50      7       7       6       6
      51      7       7       6       6
      61      7       7       6       6
      71      7       7       6       6
      94      7       7       6       6
      97      7       7       6       6
      100     7       7       6       6
      104     7       7       6       6
      114     7       7       6       6
      119     7       7       6       6
      128     7       7       6       6
      129     7       7       6       6
      140     7       7       6       6
      8       8       8       3       3
      43      8       8       3       3
      45      8       8       3       3
      64      8       8       3       3
      74      8       8       3       3
      77      8       8       3       3
      78      8       8       3       3
      93      8       8       3       3
      101     8       8       3       3
      107     8       8       3       3
      132     8       8       3       3
      133     8       8       3       3
      156     7       7       6       6
      151     8       8       3       3

      The last row, with alias1.id=151, is missing from the first list. It seems to satisfy the condition, so I presume 32 rows is the correct result and 31 rows is not.
      SELECT COUNT and other variations of the query also return 32 rows. With join_cache_level=0, the initial query also returns 32 rows.

      Test case:

      CREATE TABLE t1 (
        id INT,
        i1 INT,
        i2 INT DEFAULT 0,
        d1 DATE DEFAULT '2000-01-01',
        d2 DATE DEFAULT '2000-01-01',
        t1 TIME DEFAULT '00:00:00',
        t2 TIME DEFAULT '00:00:00',
        dt1 DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00',
        dt2 DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00',
        c1 VARCHAR(1) NOT NULL,
        c2 VARCHAR(1) NOT NULL
      ) ENGINE=MyISAM;
       
      CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
       
      INSERT INTO t1 (id,i1,c1,c2) VALUES 
      (1,7,'t','t'),(2,4,'k','k'),(3,2,'e','e'),(4,0,'i','i'),(5,1,'t','t'),(6,91,'m','m'),(7,6,'z','z'),(8,3,'c','c'),(9,6,'i','i'),(10,8,'v','v'),
      (11,1,'l','l'),(12,4,'j','j'),(13,5,'w','w'),(14,0,'r','r'),(15,7,'o','o'),(16,9,'o','o'),(17,7,'u','u'),(18,6,'f','f'),(19,0,'l','l'),(20,6,'g','g'),
      (21,1,'e','e'),(22,7,'y','y'),(23,0,'p','p'),(24,6,'v','v'),(25,5,'d','d'),(26,9,'i','i'),(27,5,'z','z'),(28,2,'q','q'),(29,4,'j','j'),(30,9,'m','m'),
      (31,8,'d','d'),(32,5,'r','r'),(33,1,'r','r'),(34,1,'k','k'),(35,4,'p','p'),(36,2,'x','x'),(37,5,'w','w'),(38,0,'k','k'),(39,7,'y','y'),(40,4,'p','p'),
      (41,9,'l','l'),(42,2,'u','u'),(43,3,'r','r'),(44,5,'y','y'),(45,3,'u','u'),(46,9,'t','t'),(47,8,'f','f'),(48,2,'f','f'),(49,2,'q','q'),(50,6,'v','v'),
      (51,6,'u','u'),(52,0,'b','b'),(53,1,'n','n'),(54,2,'p','p'),(55,0,'y','y'),(56,1,'l','l'),(57,1,'c','c'),(58,0,'d','d'),(59,2,'y','y'),(60,7,'l','l'),
      (61,6,'m','m'),(62,9,'q','q'),(63,0,'j','j'),(64,3,'u','u'),(65,4,'w','w'),(66,5,'p','p'),(67,8,'z','z'),(68,5,'u','u'),(69,7,'b','b'),(70,0,'f','f'),
      (71,6,'u','u'),(72,1,'i','i'),(73,9,'s','s'),(74,3,'y','y'),(75,5,'s','s'),(76,8,'x','x'),(77,3,'s','s'),(78,3,'l','l'),(79,8,'b','b'),(80,0,'p','p'),
      (81,9,'m','m'),(82,5,'k','k'),(83,7,'u','u'),(84,0,'y','y'),(85,2,'x','x'),(86,5,'h','h'),(87,5,'j','j'),(88,5,'o','o'),(89,9,'o','o'),(90,1,'c','c'),
      (91,7,'k','k'),(92,9,'t','t'),(93,3,'h','h'),(94,6,'g','g'),(95,9,'r','r'),(96,2,'i','i'),(97,6,'l','l'),(98,4,'a','a'),(99,9,'w','w'),(100,6,'n','n'),
      (101,3,'c','c'),(102,1,'a','a'),(103,9,'z','z'),(104,6,'k','k'),(105,7,'a','a'),(106,7,'g','g'),(107,3,'k','k'),(108,8,'h','h'),(109,7,'y','y'),(110,1,'d','d'),
      (111,0,'p','p'),(112,4,'b','b'),(113,2,'n','n'),(114,6,'l','l'),(115,0,'s','s'),(116,0,'n','n'),(117,8,'o','o'),(118,9,'f','f'),(119,6,'u','u'),(120,1,'j','j'),
      (121,1,'q','q'),(122,8,'y','y'),(123,1,'c','c'),(124,17,'i','i'),(125,1,'v','v'),(126,0,'w','w'),(127,5,'q','q'),(128,6,'n','n'),(129,6,'x','x'),(130,2,'b','b'),
      (131,8,'l','l'),(132,3,'i','i'),(133,3,'d','d'),(134,7,'h','h'),(135,0,'i','i'),(136,1,'n','n'),(137,2,'g','g'),(138,7,'u','u'),(139,0,'d','d'),(140,6,'x','x'),
      (141,1,'a','a'),(142,0,'q','q'),(143,9,'n','n'),(144,8,'r','r'),(145,4,'r','r'),(146,4,'g','g'),(147,0,'u','u'),(148,1,'j','j'),(149,0,'m','m'),(150,4,'u','u'),
      (151,3,'m','m'),(152,9,'p','p'),(153,1,'o','o'),(154,8,'v','v'),(155,9,'f','f'),(156,6,'f','f'),(157,1,'e','e'),(158,5,'l','l'),(159,8,'j','j');
       
      CREATE TABLE t2 (i INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (7),(8);
       
      SELECT DISTINCT alias1.id 
      FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3 
      ON alias2.id = alias3.i ON alias1.i1 = alias2.i1 
      WHERE alias3.i != 3;
       

      EXPLAIN:

      EXPLAIN EXTENDED
      SELECT DISTINCT alias1.id FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3
      ON alias2.id = alias3.i ON alias1.i1 = alias2.i1
      WHERE alias3.i != 3;
      id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
      1       PRIMARY alias1  ALL     NULL    NULL    NULL    NULL    159     100.00  Using where; Using temporary
      1       PRIMARY <derived2>      ref     key0    key0    5       test.alias1.i1  10      100.00  Using where; Distinct
      1       PRIMARY alias3  ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Distinct; Using join buffer (flat, BNL join)
      2       DERIVED t1      ALL     NULL    NULL    NULL    NULL    159     100.00
      Warnings:
      Note    1003    select distinct `test`.`alias1`.`id` AS `id` from `test`.`t1` `alias1` join `test`.`v1` `alias2` join `test`.`t2` `alias3` where ((`test`.`alias3`.`i` = `alias2`.`id`) and (`alias2`.`i1` = `test`.`alias1`.`i1`) and (`alias2`.`id` <> 3))

        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: