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

Unusable key notes report wrong predicates for > and >=

Details

    Description

      Unusable key notes report wrong predicates for > and >=

      SET note_verbosity=unusable_keys;
      		 
      CREATE OR REPLACE TABLE t1 (a INT, i CHAR(2), KEY(i));
      		 
      DELIMITER $$
      		 
      FOR i IN 1..31
      		 
      DO
      		 
        INSERT INTO t1 VALUES (a, 10+i);
      		 
      END FOR;
      		 
      $$
      		 
      DELIMITER ;
      		 
      EXPLAIN SELECT * FROM t1 WHERE i>30 ORDER BY i LIMIT 5;
      		 
      SHOW WARNINGS;


      +-------+------+---------------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                     |
      		 
      +-------+------+---------------------------------------------------------------------------------------------+
      		 
      |  Note | 1105 | Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "30" of type `int` |
      		 
      +-------+------+---------------------------------------------------------------------------------------------+

      Notice, when the > operator is used, it says >= in the note.

      And the other way around:

      EXPLAIN SELECT * FROM t1 WHERE i>=30 ORDER BY i LIMIT 5;
      		 
      SHOW WARNINGS;


      +-------+------+--------------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                    |
      		 
      +-------+------+--------------------------------------------------------------------------------------------+
      		 
      |  Note | 1105 | Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "30" of type `int` |
      		 
      +-------+------+--------------------------------------------------------------------------------------------+

      When the >= operator is used, it says > in the note.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-32203 Raise notes when an index cannot be used on data type mismatch

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32958 Unusable key notes do not get reported for some operations

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34600 Wrong unusable key column on char_col BETWEEN 'a' AND 3

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34601 Unusable key notes do not get reported for IN on data types mismatch

          • Major - Major loss of function.
          • 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
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.6.17 [ 29518 ]
            Fix Version/s 10.11.7 [ 29519 ]
            Fix Version/s 11.0.5 [ 29520 ]
            Fix Version/s 11.1.4 [ 29024 ]
            Fix Version/s 11.2.3 [ 29521 ]
            Fix Version/s 11.3.2 [ 29522 ]
            Fix Version/s 11.4.1 [ 29523 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 11.3 [ 28565 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov 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.