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

Unusable key notes report wrong predicates for > and >=

    XMLWordPrintable

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

          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.