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

Range search does not work well for decimal_10_2_column<10.999

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.4
    • Component/s: Optimizer
    • Labels:
      None

      Description

      CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;
      INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);
      explain SELECT * FROM t1 WHERE a<10.999; -- this rounds to 11.00
      

      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL |    6 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      

      Notice, it uses full table scan. Looks wrong. The expected behavior would be to use range search and exclude all values of 11. The constant 10.999 rounds to 11.00 in case of DECIMAL(10,2). So in the above condition, 10.999 and 11 should be equal.

      Note, if I now change the constant from 10.999 to 11:

      CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;
      INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);
      explain SELECT * FROM t1 WHERE a<11;
      

      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      |    1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL |    1 | Using index condition |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      

      it correctly starts to use range search.

      Note, the reverse operation (greater-than) works fine. These two scripts:

      CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;
      INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);
      explain SELECT * FROM t1 WHERE a>11.001; -- this rounds to 11.00
      

      CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;
      INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);
      explain SELECT * FROM t1 WHERE a>11;
      

      both use range search as expected:

      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      |    1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL |    1 | Using index condition |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      

      The problem possibly happens because the code in opt_range.cc in case of the less-than operation does not call stored_field_cmp_to_item() and therefore does not notice when the constant rounds to a greater value.

      On the contrary, for all other operations (less-or-equal, greater-than, greater-or-equal) the function stored_field_cmp_to_item() is called, so truncation/rounding are taken into account.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: