[MDEV-32203] Raise notes when an index cannot be used on data type mismatch Created: 2023-09-19  Updated: 2024-01-20  Resolved: 2023-10-09

Status: Closed
Project: MariaDB Server
Component/s: Data types, Optimizer
Fix Version/s: 10.6.16, 10.10.7, 10.11.6, 11.0.4, 11.1.3, 11.2.2

Type: Task Priority: Critical
Reporter: Alexander Barkov Assignee: Michael Widenius
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Problem/Incident
causes MDEV-32388 MSAN / Valgrind errors in Item_func_l... Closed
causes MDEV-32531 MSAN / Valgrind errors in Item_func_l... Closed
Relates
relates to MDEV-32957 Unusable key notes report wrong predi... Closed
relates to MDEV-32148 Inefficient WHERE timestamp_column=da... Closed
relates to MDEV-32958 Unusable key notes do not get reporte... Closed

 Description   

In some cases an index on a column cannot be used by the range optimizer because of data type mismatch.

Example 1

In this example the index on the VARCHAR column cannot be used for integer lookups:

CREATE OR REPLACE TABLE t1 (a VARCHAR(10), KEY(a));
 
DELIMITER $$
 
FOR i IN 1..99
 
DO
 
  INSERT INTO t1 VALUES (i);
 
END FOR;
 
$$
 
DELIMITER ;
 
EXPLAIN SELECT * FROM t1 WHERE a=1;


+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
 
|    1 | SIMPLE      | t1    | index | a             | a    | 13      | NULL | 99   | Using where; Using index |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
 
1 row in set, 1 warning (0.000 sec)

Example 2

In this example the index cannot be used because the field gets wrapped into a CONVERT() function during argument character set aggregation, which makes the index not applicable:

CREATE OR REPLACE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, KEY(a));
 
DELIMITER $$
 
FOR i IN 1..99
 
DO
 
  INSERT INTO t1 VALUES (i);
 
END FOR;
 
$$
 
DELIMITER ;
 
EXPLAIN SELECT * FROM t1 WHERE a=_utf8mb3'1' COLLATE utf8mb3_bin;


+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
 
|    1 | SIMPLE      | t1    | index | NULL          | a    | 13      | NULL | 99   | Using where; Using index |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

Let's make it easier to analyze

Under terms of this tasks we'll add SQL notes to SELECT and other statements using the range optimizer:

  • when an index cannot be used due to data type mismatch
  • when an index cannot be used because the column get wrapped into CONVERT()

Additionally, let's print those notes in the Slow Query Log if log_slow_verbosity=query_plan,explain is set.

This will make it easier to catch and analyze sub-optimal queries.

 Comments   
Comment by Michael Widenius [ 2023-10-09 ]

This feature is documented at:
https://jira.mariadb.org/browse/MDEV-32203 "Raise notes when an index cannot be used on data type mismatch"

One can configure exactly when the notes will be given, including also if the notes should be written to the slow query log.

Comment by Sergei Petrunia [ 2023-11-20 ]

Ok it works for equalities:

MariaDB [test]>  select * from t1 where t1.varchar_col=123;
 
+-------------+------+
 
| varchar_col | b    |
 
+-------------+------+
 
| 123         |  123 |
 
+-------------+------+
 
1 row in set, 1 warning (0.011 sec)
 
 
 
MariaDB [test]>  show warnings;
 
+-------+------+------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                |
 
+-------+------+------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1105 | Cannot use key `varchar_col_idx` part[0] for lookup: `test`.`t1`.`varchar_col` of type `varchar` = "123" of type `int` |
 
+-------+------+------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)

But not for IN:

MariaDB [test]>  select * from t1 where t1.varchar_col in (1,2,3);
 
+-------------+------+
 
| varchar_col | b    |
 
+-------------+------+
 
| 1           |    1 |
 
| 2           |    2 |
 
| 3           |    3 |
 
+-------------+------+
 
3 rows in set (0.015 sec)

is this intentional?

Comment by Michael Widenius [ 2023-11-21 ]

No, we only check = and <=> for now.

Comment by Alexander Barkov [ 2023-12-06 ]

Non-equality dyadic comparison predicates also do not raise warnings:

SET note_verbosity=all;
 
CREATE TABLE t1 (a VARCHAR(10), KEY(a));
 
DELIMITER $$
 
FOR i IN 10..99 DO
 
  INSERT INTO t1 VALUES (CONCAT(a, i));
 
END FOR;
 
$$
 
DELIMITER ;
 
SELECT * FROM t1 WHERE a < _latin1'a10' COLLATE latin1_german2_ci;


Empty set (0.003 sec)

This should eventually be fixed.

Comment by Alexander Barkov [ 2023-12-11 ]

The problem with predicates <, <=, >=, >, IN, BEETWEEN was fixed under terms of MDEV-32958

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