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

range optimizer: "x < y" is sargable, while "y > x" is not

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.35, 10.0.7
    • Fix Version/s: 10.0.8
    • Component/s: None
    • Labels:
      None

      Description

      Query plans produced by range optimizer depend on whether the condition has form "X < Y", or "Y >X".

      Example:

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table one_k(a int);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
      alter table one_k add key(a);

      explain select * from ten, one_k where one_k.a < ten.a;
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                          |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
      |    1 | SIMPLE      | ten   | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                                |
      |    1 | SIMPLE      | one_k | ALL  | a             | NULL | NULL    | NULL | 1148 | Range checked for each record (index map: 0x1) |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+

      now, the same query with changed WHERE:

      mysql> explain select * from ten, one_k where ten.a > one_k.a;
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                                        |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------------------+
      |    1 | SIMPLE      | ten   | ALL   | NULL          | NULL | NULL    | NULL |   10 |                                                              |
      |    1 | SIMPLE      | one_k | index | a             | a    | 5       | NULL | 1148 | Using where; Using index; Using join buffer (flat, BNL join) |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------------------+

      The problem can be only observed if there is a table field on the other side of the comparison. Besides "range checked for each record", the optimizer may miss regular plans:

      create table t2 (a int primary key, b int);
      insert into t2 select a,a from ten;

      mysql> explain select * from t2, one_k where one_k.a < t2.b  and t2.a=1;
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
      |    1 | SIMPLE      | t2    | const | PRIMARY       | PRIMARY | 4       | const |    1 |                          |
      |    1 | SIMPLE      | one_k | range | a             | a       | 5       | NULL  |    1 | Using where; Using index |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+

      mysql> explain select * from t2, one_k where t2.b > one_k.a   and t2.a=1;
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
      |    1 | SIMPLE      | t2    | const | PRIMARY       | PRIMARY | 4       | const |    1 |                          |
      |    1 | SIMPLE      | one_k | index | a             | a       | 5       | NULL  | 1148 | Using where; Using index |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              psergey Sergei Petrunia
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: