Like should use expression index if pattern is besed on system function e.g.
UPPER
----------------------------------------------------------------------------------
Key: CORE-6079
URL: http://tracker.firebirdsql.org/browse/CORE-6079
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Reporter: Karol Bieniaszewski
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
