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

Wrong result (extra rows) with ORDER BY, multiple-column index, InnoDB

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.3.12, 5.5.38, 10.0.12
    • Fix Version/s: 5.5.39, 10.0.13
    • Component/s: None
    • Labels:
      None

      Description

      Initially reported by VAROQUI Stephane on IRC.

      CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB;
       
      INSERT INTO t1 (a,c) VALUES
      (8, 9),(8, 10),(13, 15),(16, 17),(16, 18),(16, 19),(20, 21),
      (20, 22),(20, 24),(20, 25),(20, 26),(20, 27),(20, 28);
       
      SELECT  * FROM t1 WHERE a = 8 AND (b = 1 OR b IS NULL) ORDER BY c;

      Actual result (all rows except for a=8 shouldn't be there):

      +------+------+------+------+
      | a    | b    | c    | d    |
      +------+------+------+------+
      |    8 | NULL |    9 | NULL |
      |    8 | NULL |   10 | NULL |
      |   13 | NULL |   15 | NULL |
      |   16 | NULL |   17 | NULL |
      |   16 | NULL |   18 | NULL |
      |   16 | NULL |   19 | NULL |
      |   20 | NULL |   21 | NULL |
      |   20 | NULL |   22 | NULL |
      |   20 | NULL |   24 | NULL |
      |   20 | NULL |   25 | NULL |
      |   20 | NULL |   26 | NULL |
      |   20 | NULL |   27 | NULL |
      |   20 | NULL |   28 | NULL |
      +------+------+------+------+
      13 rows in set (0.01 sec)

      +------+-------------+-------+-------------+---------------+------+---------+-------------+------+----------+----------------------------------------------------+
      | id   | select_type | table | type        | possible_keys | key  | key_len | ref         | rows | filtered | Extra                                              |
      +------+-------------+-------+-------------+---------------+------+---------+-------------+------+----------+----------------------------------------------------+
      |    1 | SIMPLE      | t1    | ref_or_null | idx           | idx  | 10      | const,const |    3 |   100.00 | Using index condition; Using where; Using filesort |
      +------+-------------+-------+-------------+---------------+------+---------+-------------+------+----------+----------------------------------------------------+
      1 row in set, 1 warning (0.00 sec)
       
      MariaDB [test]> show warnings;
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                                                           |
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where ((`test`.`t1`.`a` = 8) and ((`test`.`t1`.`b` = 1) or isnull(`test`.`t1`.`b`))) order by `test`.`t1`.`c` |
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: