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

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.4(EOL)
    • Optimizer
    • 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

          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-8787 Return Impossible WHERE instead of Full table scan on some admittedly false field=const expressions

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-15758 Split Item_bool_func::get_mm_leaf() into virtual methods in Field and Type_handler

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant

          • Major - Major loss of function.
          • Closed

          Activity

            There are no comments yet on this issue.

            People

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

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.