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

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.6, 11.6(EOL)
Fix Version/s: 10.6
Component/s: Data types, Optimizer
Labels:
None

Description

SET @@note_verbosity='all';

CREATE OR REPLACE TABLE t1 (f char(8), KEY(f));

INSERT INTO t1 VALUES (''),('');

SELECT * FROM t1 WHERE f BETWEEN 'a' AND 3;

SHOW WARNINGS;

+---------+------+----------------------------------------------------------------------------------------------------------------------------+

| Level   | Code | Message                                                                                                                    |

+---------+------+----------------------------------------------------------------------------------------------------------------------------+

| Note    | 1105 | Cannot use key `f` part[0] for lookup: `test`.`t1`.`f` of collation `utf8mb4_uca1400_ai_ci` >= "'a'" of collation `binary` |

| Warning | 1292 | Truncated incorrect DECIMAL value: ''                                                                                      |

| Warning | 1292 | Truncated incorrect DECIMAL value: 'a'                                                                                     |

| Warning | 1292 | Truncated incorrect DECIMAL value: ''                                                                                      |

| Warning | 1292 | Truncated incorrect DECIMAL value: 'a'                                                                                     |

+---------+------+----------------------------------------------------------------------------------------------------------------------------+

Notice, the warning about the unusable key is incorrect. It should report a wrong data type instead, because the comparison is done using the DECIMAL data type.

If I change the order of the BETNEEN values, it correctly reports the data type problem:

SELECT * FROM t1 WHERE f BETWEEN 3  AND 'a';

SHOW WARNINGS;

+---------+------+--------------------------------------------------------------------------------------------+

| Level   | Code | Message                                                                                    |

+---------+------+--------------------------------------------------------------------------------------------+

| Note    | 1105 | Cannot use key `f` part[0] for lookup: `test`.`t1`.`f` of type `char` >= "3" of type `int` |

| Warning | 1292 | Truncated incorrect DOUBLE value: ''                                                       |

| Warning | 1292 | Truncated incorrect DOUBLE value: 'a'                                                      |

| Warning | 1292 | Truncated incorrect DOUBLE value: ''                                                       |

| Warning | 1292 | Truncated incorrect DOUBLE value: 'a'                                                      |

+---------+------+--------------------------------------------------------------------------------------------+

Attachments

Issue Links

relates to

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

Closed

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

Closed

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

Closed

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

Open

Activity

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2024-07-16 06:42

Updated:: 2024-07-16 06:50

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server