Issue Details (XML | Word | Printable)

Key: CORE-6079
Type: Improvement Improvement
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Karol Bieniaszewski
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
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: Ascending order - Click to sort in descending 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.