[MDEV-32203] Raise notes when an index cannot be used on data type mismatch - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Fix Version/s: 10.6.16, 10.10.7, 10.11.6, 11.0.4, 11.1.3, 11.2.2
Component/s: Data types, Optimizer
Labels:
None

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.

Attachments

Issue Links

causes

MDEV-32388 MSAN / Valgrind errors in Item_func_like::get_mm_leaf upon query from partitioned table

Closed

MDEV-32531 MSAN / Valgrind errors in Item_func_like::get_mm_leaf with temporal field

Closed

relates to

MDEV-32957 Unusable key notes report wrong predicates for > and >=

Closed

MDEV-32148 Inefficient WHERE timestamp_column=datetime_const_expr

Closed

MDEV-32958 Unusable key notes do not get reported for some operations

Closed

MDEV-34600 Wrong unusable key column on char_col BETWEEN 'a' AND 3

Open

MDEV-34601 Unusable key notes do not get reported for IN on data types mismatch

Open

(2 relates to)

Activity

Ascending order - Click to sort in descending order

Michael Widenius added a comment - 2023-10-09 12:08

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.

Michael Widenius added a comment - 2023-10-09 12:08 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.

Sergei Petrunia added a comment - 2023-11-20 12:47

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?

Sergei Petrunia added a comment - 2023-11-20 12:47 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?

Michael Widenius added a comment - 2023-11-21 12:37

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

Michael Widenius added a comment - 2023-11-21 12:37 No, we only check = and <=> for now.

Alexander Barkov added a comment - 2023-12-06 02:02 - edited

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.

Alexander Barkov added a comment - 2023-12-06 02:02 - edited 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.

Alexander Barkov added a comment - 2023-12-11 05:34 - edited

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

Alexander Barkov added a comment - 2023-12-11 05:34 - edited The problem with predicates <, <=, >=, >, IN, BEETWEEN was fixed under terms of MDEV-32958

MariaDB Server

Raise notes when an index cannot be used on data type mismatch