Derby
  1. Derby
  2. DERBY-4791

LIKE operator optimizations and concatenation

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      All

      Description

      While queries of the form "<column> LIKE ' <string-constant>%' " are optimized into index scans, the equivalent expression using a concatenation "<column> LIKE ' <string-constant>' || '%' " would result in a table scan.
      Queries of the form "<column> LIKE ?" are optimizable using an internally generated parameter, so it doesn't seem far-fetched to generate such a parameter for a concatenation of strings (or other string expressions) as well, once its value has been calculated. This is of course limited to cases where the result of the string expression can be calculated once, i.e. it is independent of columns in the query.
      It is sometimes possible to work around this by manually adding the " x >= '<string-constant>' AND x < '<string-constant>\uffff...' " condition.

      1. fold-constants.diff
        6 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
          Hide
          Knut Anders Hatlen added a comment -

          I'm marking this issue as resolved since the patch addresses the case described in the issue description. I think there were some other cases mentioned in the derby-user thread that prompted this issue (for example 'abc' || ?), and that's why I left it open after checking in the fix. But I see that only concatenation of string literals is mentioned explicitly in this issue, and that has been fixed. If there are more cases that need to be fixed, we should open separate JIRA issues for them.

          Show
          Knut Anders Hatlen added a comment - I'm marking this issue as resolved since the patch addresses the case described in the issue description. I think there were some other cases mentioned in the derby-user thread that prompted this issue (for example 'abc' || ?), and that's why I left it open after checking in the fix. But I see that only concatenation of string literals is mentioned explicitly in this issue, and that has been fixed. If there are more cases that need to be fixed, we should open separate JIRA issues for them.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for the quick review, Bryan! Committed revision 993074.

          Show
          Knut Anders Hatlen added a comment - Thanks for the quick review, Bryan! Committed revision 993074.
          Hide
          Bryan Pendleton added a comment -

          This looks like a fine approach to me. Thanks for putting the patch together. +1.

          Show
          Bryan Pendleton added a comment - This looks like a fine approach to me. Thanks for putting the patch together. +1.
          Hide
          Knut Anders Hatlen added a comment -

          All the regression tests ran cleanly with the patch, so I'm setting the patch available flag to mark it as ready for review.

          Show
          Knut Anders Hatlen added a comment - All the regression tests ran cleanly with the patch, so I'm setting the patch available flag to mark it as ready for review.
          Hide
          Knut Anders Hatlen added a comment -

          One part of this issue, making Derby handle "LIKE 'ab' || '%'" the same way as it handles "LIKE 'ab%'", should be possible to solve using the constant folding mechanism added in DERBY-4416.

          I've attached a patch (fold-constants.diff) that implements an override of evaluateConstantExpressions() for ConcatentationOperatorNode. Unfortunately, this change alone didn't make LIKE use indexes, because the constant folding happens right before the statement optimization phase. LIKE decides whether to add extra scan predicates earlier than that, during the bind phase. I therefore made ConcatenationOperatorNode's bindExpression() method invoke the constant folding as well, and then it seems to be happening early enough so that LikeEscapeOperatorNode is able to pick it up.

          The patch also contains a test case that verifies that indexes are used. I haven't run any other tests on the patch yet.

          Show
          Knut Anders Hatlen added a comment - One part of this issue, making Derby handle "LIKE 'ab' || '%'" the same way as it handles "LIKE 'ab%'", should be possible to solve using the constant folding mechanism added in DERBY-4416 . I've attached a patch (fold-constants.diff) that implements an override of evaluateConstantExpressions() for ConcatentationOperatorNode. Unfortunately, this change alone didn't make LIKE use indexes, because the constant folding happens right before the statement optimization phase. LIKE decides whether to add extra scan predicates earlier than that, during the bind phase. I therefore made ConcatenationOperatorNode's bindExpression() method invoke the constant folding as well, and then it seems to be happening early enough so that LikeEscapeOperatorNode is able to pick it up. The patch also contains a test case that verifies that indexes are used. I haven't run any other tests on the patch yet.

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Uriah Eisenstein
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development