[
https://issues.apache.org/jira/browse/DERBY-4791?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-4791:
--------------------------------------
Attachment: fold-constants.diff
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.
> LIKE operator optimizations and concatenation
> ---------------------------------------------
>
> Key: DERBY-4791
> URL: https://issues.apache.org/jira/browse/DERBY-4791
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.6.1.0
> Environment: All
> Reporter: Uriah Eisenstein
> Attachments: fold-constants.diff
>
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
