cost of lower() with sql2 LIKE operator

[Torgeir Veimo <to...@gmail.com>]

I'm wondering what the cost is of doing either lower(propname) or
lower(selectorname.*) when used with the SQL2 LIKE operator. Also, is
lower() strictly needed with the LIKE operator?

My limited testing show about a 10% slowdown when using
lower(propname), but it's just a very rudimentary test. I'd also
expect different results with property and lucene indexes?

-- 
-Tor

Re: cost of lower() with sql2 LIKE operator

[Piotr Tajduś <pi...@skg.pl>]

Not sure about "like", but when you have full text lucene index 
(analyzed=true on indexed property) operator contains(selector,'value') 
is case insensitive. In case of searching by property with operator = 
(in both property and lucene index) search is case sensitive, so I 
suppose query engine is index traversing values (there will be more 
information in log if you enable debug or trace on 
org.apache.jackrabbit.oak.query). You can also make function based index:
https://jackrabbit.apache.org/oak/docs/query/lucene.html#function-based-indexing

On 18.09.2019 12:35, Torgeir Veimo wrote:
> I'm wondering what the cost is of doing either lower(propname) or
> lower(selectorname.*) when used with the SQL2 LIKE operator. Also, is
> lower() strictly needed with the LIKE operator?
>
> My limited testing show about a 10% slowdown when using
> lower(propname), but it's just a very rudimentary test. I'd also
> expect different results with property and lucene indexes?
>