Details
-
New Feature
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
Conditions looking like
SUBSTR(str_col, 1, n) = const_str
|
where the prefix of str_col is compared against a constant value can be rewritten as range conditions as
const_str_low <= str_col <= const_str_high
|
which will improve the optimizer capabilities of building better execution plan (for example, using indexes on column str_col and applying range optimizations).
The similar transformation is already implemented for conditions like
str_col LIKE 'foo%' |
aiming for the same goal.
Similar rewrite is implemented for DATE() and YEAR() function at MDEV-8320, and that approach may be used as a reference.
Attachments
Issue Links
- relates to
-
MDEV-35564 cannot convert backslash to swe7
- Closed
-
MDEV-4414 INDEX - SUBSTRING, LEFT and others string functions that could be optimized with index
- Open
Testing at the current main f0961301c81c7f5b009c012c076abc326b203b4a, I'm getting the same "Illegal mix of collations" error, despite it containing the
MDEV-35041patch.So I cannot easily compare LIKE with SUBSTR in this case.
However, if we do explain format=json select * from t where c like 'b%_c%';, then the error disappears and the output has access_type range. So something fishy is happening with the escape here