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

Wrong result (extra rows) with UNION and LIMIT in the union parts

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5, 10.1, 10.0
    • Fix Version/s: 5.5.59
    • Component/s: Optimizer
    • Labels:
      None
    • Sprint:
      5.5.50, 10.1.29

      Description

      Test case

      DROP TABLE IF EXISTS t;
       
      CREATE TABLE t (pk INT PRIMARY KEY, i INT) ENGINE=MyISAM;
      INSERT INTO t VALUES (1,8),(2,0),(3,9),(4,3),(5,4);
       
      ( SELECT t1.i FROM t AS t1, t AS t2 WHERE t1.pk = t2.pk ORDER BY i LIMIT 2 ) 
      UNION 
      ( SELECT t1.i FROM t AS t1, t AS t2 WHERE t1.pk = t2.pk ORDER BY i LIMIT 2 )
      ; 

      Each part of the query is supposed to choose the same 2 rows with minimal i values, and then UNION should remove duplicates and return 2 rows. But the result is different:

      MariaDB [test]> ( SELECT t1.i FROM t AS t1, t AS t2 WHERE t1.pk = t2.pk ORDER BY i LIMIT 2 ) 
          -> UNION 
          -> ( SELECT t1.i FROM t AS t1, t AS t2 WHERE t1.pk = t2.pk ORDER BY i LIMIT 2 )
          -> ; 
      +------+
      | i    |
      +------+
      |    0 |
      |    3 |
      |    4 |
      |    8 |
      +------+
      4 rows in set (0.00 sec)

      EXPLAIN on MariaDB 5.5

      MariaDB [test]> EXPLAIN EXTENDED ( SELECT t1.i FROM t AS t1, t AS t2 WHERE t1.pk = t2.pk ORDER BY i LIMIT 2 )  UNION  ( SELECT t1.i FROM t AS t1, t AS t2 WHERE t1.pk = t2.pk ORDER BY i LIMIT 2 );
      +------+--------------+------------+--------+---------------+---------+---------+------------+------+----------+----------------+
      | id   | select_type  | table      | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra          |
      +------+--------------+------------+--------+---------------+---------+---------+------------+------+----------+----------------+
      |    1 | PRIMARY      | t1         | ALL    | PRIMARY       | NULL    | NULL    | NULL       |    5 |   100.00 | Using filesort |
      |    1 | PRIMARY      | t2         | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.pk |    1 |   100.00 | Using index    |
      |    2 | UNION        | t1         | ALL    | PRIMARY       | NULL    | NULL    | NULL       |    5 |   100.00 | Using filesort |
      |    2 | UNION        | t2         | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.pk |    1 |   100.00 | Using index    |
      | NULL | UNION RESULT | <union1,2> | ALL    | NULL          | NULL    | NULL    | NULL       | NULL |     NULL |                |
      +------+--------------+------------+--------+---------------+---------+---------+------------+------+----------+----------------+
      5 rows in set, 1 warning (0.00 sec)
       
      | Note  | 1003 | (select `test`.`t1`.`i` AS `i` from `test`.`t` `t1` join `test`.`t` `t2` where (`test`.`t2`.`pk` = `test`.`t1`.`pk`) order by `test`.`t1`.`i` limit 2) union (select `test`.`t1`.`i` AS `i` from `test`.`t` `t1` join `test`.`t` `t2` where (`test`.`t2`.`pk` = `test`.`t1`.`pk`) order by `test`.`t1`.`i` limit 2) |

      Reproducible on MariaDB 5.1 - 10.1, on old releases as well.
      Not reproducible on MySQL 5.5, 5.6.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: