New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
"LIKE" queries should use expression index if expression uses system functions e.g. UPPER [CORE6079] #6329
Comments
Modified by: @livius2priority: Major [ 3 ] => Minor [ 4 ] |
Modified by: @livius2summary: Like should use expression index if pattern is besed on system function e.g. UPPER => "LIKE" should use expression index if pattern is besed on system function e.g. UPPER |
Modified by: Sean Leyne (seanleyne)description: CREATE TABLE NAMES ------------------------------------------------------------------------------------ SELECT * FROM NAMES N WHERE UPPER(http://N.NAME COLLATE PXW_PLK) LIKE 'RDB%SYSTEM_FLAG%' ------------------------------------------------------------------------------------ but this one not - because of function used in LIKE pattern. ------------------------------------------------------------------------------------ I understand current logic, because someone can use custom function and change order of characters. => CREATE TABLE NAMES ------------------------------------------------------------------------------------ SELECT * FROM NAMES N WHERE UPPER(http://N.NAME COLLATE PXW_PLK) LIKE 'RDB%SYSTEM_FLAG%' ------------------------------------------------------------------------------------ but this one not - because of function used in LIKE pattern. ------------------------------------------------------------------------------------ I understand current logic, because someone can use custom function and change order of characters. summary: "LIKE" should use expression index if pattern is besed on system function e.g. UPPER => "LIKE" queries should use expression index if expression uses system functions e.g. UPPER |
This is not about expression indices, but about indices in general. |
Submitted by: @livius2
CREATE TABLE NAMES
(
NAME VARCHAR(100)
);
commit;
INSERT INTO names
SELECT RF.RDB$FIELD_NAME FROM RDB$RELATION_FIELDS RF;
commit;
CREATE ASCENDING INDEX IXAE_NAMES__NAME ON NAMES COMPUTED BY(UPPER(NAME COLLATE PXW_PLK));
commit;
------------------------------------------------------------------------------------
SELECT * FROM NAMES N WHERE UPPER(http://N.NAME COLLATE PXW_PLK) LIKE 'RDB%SYSTEM_FLAG%'
PLAN (N INDEX (IXAE_NAMES__NAME))
this works as expected
------------------------------------------------------------------------------------
SELECT * FROM NAMES N WHERE UPPER(http://N.NAME COLLATE PXW_PLK) LIKE UPPER('RDB%SYSTEM_FLAG%')
PLAN (N NATURAL)
but this one not - because of function used in LIKE pattern.
------------------------------------------------------------------------------------
I understand current logic, because someone can use custom function and change order of characters.
But system function UPPER does not change anything in like pattern, and index still can be used.
I do not know which other (only system) functions can be used this way but UPPER and LOWER are obvious one.
The text was updated successfully, but these errors were encountered: