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

            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.