<column> LIKE UPPER( <string constant> ) result in a table scan even if a valid
index exists on <column>
--------------------------------------------------------------------------------------------------------
Key: DERBY-5149
URL: https://issues.apache.org/jira/browse/DERBY-5149
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.7.1.1
Reporter: Stephane Claret
I am currently trying to use generated columns to do some some case insensitive
search query, here's a simplified version of my table :
CREATE TABLE PRODUCTS (
ID VARCHAR(100) NOT NULL,
NAME VARCHAR(100) NOT NULL,
UPPERNAME VARCHAR(100) DEFAULT GENERATED ALWAYS AS (
UPPER(NAME) )
);
CREATE UNIQUE INDEX PRIMARY_KEY_F ON PRODUCTS (ID ASC);
CREATE INDEX PRODUCTS_UNAME ON PRODUCTS (UPPERNAME ASC);
ALTER TABLE PRODUCTS ADD CONSTRAINT CONSTRAINT_F PRIMARY KEY (ID);
The table is filled with about 30k records.
When running the following query
SELECT id, name
FROM PRODUCTS
WHERE uppername LIKE 'PC%'
the index is correctly used while this one :
SELECT id, name
FROM PRODUCTS
WHERE uppername LIKE UPPER('pc%')
triggers a table scan. I have not tested yet but I suspect it works the same
for every SQL function (not only UPPER).
This behavior could (should?) be optimized when the right operand of LIKE or
"=" is a function taking a constant in parameter.
This might be linked to this issue :
https://issues.apache.org/jira/browse/DERBY-4791
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
