[jira] [Updated] (DERBY-5149) LIKE UPPER( ) result in a table scan even if a valid index exists on

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-5149?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor updated DERBY-5149:
-----------------------------------

    Labels: derby_triage10_10  (was: )
    
> <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
>              Labels: derby_triage10_10
>
> 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.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira