[#CORE-6079] "LIKE" queries should use expression index if expression uses system functions e.g. UPPER

Firebird Core

"LIKE" queries should use expression index if expression uses system functions e.g. UPPER

Created: 07/Jun/19 10:15 AM Updated: 07/Jun/19 03:48 PM

Component/s:

Engine

Affects Version/s:

None

Fix Version/s:

None

QA Status:

No test

Description

« Hide

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(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(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.

All

Comments

Change History

Subversion Commits

Sort Order:

Karol Bieniaszewski made changes - 07/Jun/19 10:15 AM

Field	Original Value	New Value
Priority	Major [ 3 ]	Minor [ 4 ]

Karol Bieniaszewski made changes - 07/Jun/19 10:44 AM

Summary

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

Sean Leyne made changes - 07/Jun/19 03:48 PM

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
Description	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(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(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.	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(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(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.