You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Stephane Claret (JIRA)" <ji...@apache.org> on 2011/03/24 11:22:05 UTC
[jira] [Created] (DERBY-5149) LIKE UPPER( ) result in a table scan even if a valid index exists on
<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
[jira] [Commented] (DERBY-5149) LIKE UPPER( ) result in a table scan even if a valid index exists on
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5149?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13010650#comment-13010650 ]
Rick Hillegas commented on DERBY-5149:
--------------------------------------
I suspect that the optimizer is not smart enough to see the right side of the comparison as a constant so I would say this is related to DERBY-4791.
> <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
[jira] [Updated] (DERBY-5149) LIKE UPPER( ) result in a table scan even if a valid index exists on
Posted by "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