[MDEV-32958] Unusable key notes do not get reported for some operations Created: 2023-12-06  Updated: 2023-12-11  Resolved: 2023-12-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.6, 11.3
Fix Version/s: 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3, 11.3.2, 11.4.1

Type: Bug Priority: Critical
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-32203 Raise notes when an index cannot be u... Closed
relates to MDEV-32957 Unusable key notes report wrong predi... Closed

 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');



 Comments   
Comment by Alexander Barkov [ 2023-12-08 ]

Hello monty,

please review a patch:

https://github.com/MariaDB/server/commit/cb51a6bd29fb943f4419a51d08ae2fcf155057ce

Thanks!

Generated at Thu Feb 08 10:35:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.