Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-5149

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

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.7.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Bug behavior facts:
      Performance

      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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                stefcl Stephane Claret
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated: