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 Improvement
    • Status: Open
    • Priority: Major 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

        Issue Links

          Activity

          Stephane Claret created issue -
          Rick Hillegas made changes -
          Field Original Value New Value
          Link This issue relates to DERBY-4791 [ DERBY-4791 ]
          Rick Hillegas made changes -
          Link This issue relates to DERBY-455 [ DERBY-455 ]
          Mamta A. Satoor made changes -
          Labels derby_triage10_10
          Gavin made changes -
          Workflow jira [ 12608538 ] Default workflow, editable Closed status [ 12802031 ]

            People

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

              Dates

              • Created:
                Updated:

                Development