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

Unusable key notes do not get reported for some operations

Details

    Description

      The equality predicate produces notes about unusable keys as expected:

      CREATE OR REPLACE TABLE t1 (
      		 
        c1 varchar(10),
      		 
        KEY(c1)
      		 
      ) CHARACTER SET latin1;
      		 
      INSERT INTO t1 VALUES ('a');
      		 
      INSERT INTO t1 VALUES ('b');
      		 
      INSERT INTO t1 VALUES ('c');
      		 
      INSERT INTO t1 VALUES ('d');
      		 
      INSERT INTO t1 VALUES ('e');
      		 
      INSERT INTO t1 VALUES ('f');
      		 
      INSERT INTO t1 VALUES ('g');
      		 
      INSERT INTO t1 VALUES ('h');
      		 
      INSERT INTO t1 VALUES ('i');
      		 
      INSERT INTO t1 VALUES ('j');
      		 
      SET note_verbosity=all;
      		 
      EXPLAIN SELECT * FROM t1 WHERE c1=10;
      		 
      SHOW WARNINGS;


      +-------+------+-------------------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                         |
      		 
      +-------+------+-------------------------------------------------------------------------------------------------+
      		 
      | Note  | 1105 | Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` = "10" of type `int` |
      		 
      +-------+------+-------------------------------------------------------------------------------------------------+

      But these predicates do not produce notes:

      EXPLAIN SELECT * FROM t1 WHERE c1<10;
      		 
       
      		 
      EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 10 AND 11;
      		 
       
      		 
      EXPLAIN SELECT * FROM t1 WHERE c1 IN (_latin1'a' COLLATE latin1_german2_ci,'b');

      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-32957 Unusable key notes report wrong predicates for > and >=

          • Major - Major loss of function.
          • 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
            Priority Major [ 3 ] Critical [ 2 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Description The equality predicate produces notes about unusable keys as expected:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (
              c1 varchar(10),
              KEY(c1)
            ) CHARACTER SET latin1;
            INSERT INTO t1 VALUES ('a');
            INSERT INTO t1 VALUES ('b');
            INSERT INTO t1 VALUES ('c');
            INSERT INTO t1 VALUES ('d');
            INSERT INTO t1 VALUES ('e');
            INSERT INTO t1 VALUES ('f');
            INSERT INTO t1 VALUES ('g');
            INSERT INTO t1 VALUES ('h');
            INSERT INTO t1 VALUES ('i');
            INSERT INTO t1 VALUES ('j');
            SET notes_verbosity=all;
            EXPLAIN SELECT * FROM t1 WHERE c1=10;
            SHOW WARNINGS;
            {code}
            {noformat}
            +-------+------+-------------------------------------------------------------------------------------------------+
            | Level | Code | Message |
            +-------+------+-------------------------------------------------------------------------------------------------+
            | Note | 1105 | Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` = "10" of type `int` |
            +-------+------+-------------------------------------------------------------------------------------------------+
            {noformat}

            But these predicates do not produce notes:
            {code:sql}
            EXPLAIN SELECT * FROM t1 WHERE c1<10;

            EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 10 AND 11;

            EXPLAIN SELECT * FROM t1 WHERE c1 IN (_latin1'a' COLLATE latin1_german2_ci,'b');
            {code}
            		The equality predicate produces notes about unusable keys as expected:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (
              c1 varchar(10),
              KEY(c1)
            ) CHARACTER SET latin1;
            INSERT INTO t1 VALUES ('a');
            INSERT INTO t1 VALUES ('b');
            INSERT INTO t1 VALUES ('c');
            INSERT INTO t1 VALUES ('d');
            INSERT INTO t1 VALUES ('e');
            INSERT INTO t1 VALUES ('f');
            INSERT INTO t1 VALUES ('g');
            INSERT INTO t1 VALUES ('h');
            INSERT INTO t1 VALUES ('i');
            INSERT INTO t1 VALUES ('j');
            SET note_verbosity=all;
            EXPLAIN SELECT * FROM t1 WHERE c1=10;
            SHOW WARNINGS;
            {code}
            {noformat}
            +-------+------+-------------------------------------------------------------------------------------------------+
            | Level | Code | Message |
            +-------+------+-------------------------------------------------------------------------------------------------+
            | Note | 1105 | Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` = "10" of type `int` |
            +-------+------+-------------------------------------------------------------------------------------------------+
            {noformat}

            But these predicates do not produce notes:
            {code:sql}
            EXPLAIN SELECT * FROM t1 WHERE c1<10;

            EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 10 AND 11;

            EXPLAIN SELECT * FROM t1 WHERE c1 IN (_latin1'a' COLLATE latin1_german2_ci,'b');
            {code}
            bar Alexander Barkov made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Michael Widenius [ monty ]
            Status Confirmed [ 10101 ] In Review [ 10002 ]
            bar Alexander Barkov made changes -
            Assignee Michael Widenius [ monty ] Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            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 Stalled [ 10000 ] Closed [ 6 ]
            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.