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

Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.4.0
    • Data types, Optimizer
    • None

    Description

      The range optimizer decides to use "ref" search when testing an equality between an indexed integer column and an out-of-range integer constant:

      CREATE OR REPLACE TABLE t1 (a TINYINT, KEY(a));
      		 
      INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
      		 
      EXPLAIN SELECT * FROM t1 WHERE a=200;


      +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
      		 
      |    1 | SIMPLE      | t1    | ref  | a             | a    | 2       | const |    1 | Using where; Using index |
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

      The same happens with the BIT data type:

      CREATE OR REPLACE TABLE t1 (a BIT(7), KEY(a));
      		 
      INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
      		 
      EXPLAIN SELECT * FROM t1 WHERE a=200;


      +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
      		 
      |    1 | SIMPLE      | t1    | ref  | a             | a    | 2       | const |    1 | Using where; Using index |
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

      This is wrong. It should return with "Impossible WHERE".

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18898 SELECT using wrong index when using operator IN with mixed types

          • Major - Major loss of function.
          • Closed
          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-16784 Range search does not work well for decimal_10_2_column<10.999

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28531 update on out of range criteria should be Impossible Where

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Fix Version/s 10.3 [ 22126 ]
            bar Alexander Barkov made changes -
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 5.5 [ 15800 ]
            Affects Version/s 10.0 [ 16000 ]
            Affects Version/s 10.1 [ 16100 ]
            bar Alexander Barkov made changes -
            Component/s Data types [ 13906 ]
            bar Alexander Barkov made changes -
            Description The range optimizer decides to use "ref" search when testing and equality with an out-of-range integer constant:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (a TINYINT, KEY(a));
            INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
            EXPLAIN SELECT * FROM t1 WHERE a=200;
            {code}
            {noformat}
            +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
            | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
            +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
            | 1 | SIMPLE | t1 | ref | a | a | 2 | const | 1 | Using where; Using index |
            +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
            {noformat}


            The same happens with the {{BIT}} data type:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (a BIT(7), KEY(a));
            INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
            EXPLAIN SELECT * FROM t1 WHERE a=200;
            {code}
            {noformat}
            +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
            | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
            +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
            | 1 | SIMPLE | t1 | ref | a | a | 2 | const | 1 | Using where; Using index |
            +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
            {noformat}

            This is wrong. It should return with "Impossible WHERE".             		The range optimizer decides to use "ref" search when testing an equality between an indexed integer column and an out-of-range integer constant:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (a TINYINT, KEY(a));
            INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
            EXPLAIN SELECT * FROM t1 WHERE a=200;
            {code}
            {noformat}
            +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
            | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
            +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
            | 1 | SIMPLE | t1 | ref | a | a | 2 | const | 1 | Using where; Using index |
            +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
            {noformat}


            The same happens with the {{BIT}} data type:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (a BIT(7), KEY(a));
            INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
            EXPLAIN SELECT * FROM t1 WHERE a=200;
            {code}
            {noformat}
            +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
            | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
            +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
            | 1 | SIMPLE | t1 | ref | a | a | 2 | const | 1 | Using where; Using index |
            +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
            {noformat}

            This is wrong. It should return with "Impossible WHERE".
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2018-04-03 10:01:34.0 2018-04-03 10:01:34.59
            bar Alexander Barkov made changes -
            Fix Version/s 10.3.6 [ 23003 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            bar Alexander Barkov added a comment - - edited

            Closed it a mistake. Reopening.

            bar Alexander Barkov added a comment - - edited Closed it a mistake. Reopening.
            bar Alexander Barkov made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.3.6 [ 23003 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.4.0 [ 23115 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            Affects Version/s 10.4 [ 22408 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 86344 ] MariaDB v4 [ 154078 ]
            danblack Daniel Black made changes -

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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