[DERBY-5149] <column> LIKE UPPER( <string constant> ) result in a table scan even if a valid index exists on <column>

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.7.1.1
• Component/s: SQL
• Labels: derby_triage10_10
• Urgency: Normal
• Bug behavior facts: Performance
• Assignee: Unassigned
• Reporter: Stephane Claret
• Created: 24/Mar/11 10:21
• Updated: 28/Sep/12 04:49

Description

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

Issue Links

• relates to: DERBY-455 Add support for creating index on expressions
• relates to: DERBY-4791 LIKE operator optimizations and concatenation