[jira] Resolved: (DERBY-4791) LIKE operator optimizations and concatenation

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-4791?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Knut Anders Hatlen resolved DERBY-4791.
---------------------------------------

       Resolution: Fixed
    Fix Version/s: 10.7.1.0
         Assignee: Knut Anders Hatlen

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.

> 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
>            Assignee: Knut Anders Hatlen
>             Fix For: 10.7.1.0
>
>         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.