[Rt-devel] SearchBuild and case insensitive matching on postgres

[Palle Girgensohn]

--On Thursday, May 13, 2004 22:32:55 -0400 Jesse Vincent 
<jesse at bestpractical.com> wrote:
>
> On Thu, May 13, 2004 at 09:38:12PM -0400, Jesse Vincent wrote:
>>
>> On Fri, May 14, 2004 at 03:05:31AM +0200, Palle Girgensohn wrote:
>> > Hi,
>> >
>> > I've modified SearchBuilder/Handle/Pg.pm according to the enclosed
>> > diff,  which makes use of lower() instead of ilike. This will make a
>> > difference:  postgresql will use indices created with "on
>> > (lower(field))" when lower is  used explicitally in the query. ILIKE
>> > is not the same thing as lower() =  xx, though, since many strange
>> > things occur with locales. Hence ILIKE never  uses indices, it just
>> > cannot logically do this. (A postgresql developer had  this to say:
>> > "It's a big leap to assume that ILIKE is equivalent to LIKE on
>> > lower().  Think about Turkish i/I, German esstet (did I spell that
>> > right?),  ch in various languages, etc etc." )
>>
>> You want to look at the latest development version of
>> DBIx::SearchBuilder::Handle::Pg, as it has a slightly more elegant
>> version of what you're trying to do here.
>
> So, to be more exact, RT 3.0 doesn't have the infrastructure needed to
> let us do LOWER('query') = LOWER('colname') so we fake it with lc. When
> talking to newer RT branches, we do the right thing. I've just released
> this version of SearchBuilder as 1.00_01. I'd be thrilled if you could
> test it out and tell us how you do. If there are other new indexes that
> you find to help, I'll add them to the core.

My main concern is not search criterias using LIKE, but joins on primary 
keys (integers) using LIKE. There should be a simple equality, no LIKEs. I 
can't find where and how RT decides that they are search criterias and not 
joins.

I will try DBIx-SearchBuilder-1.00_1, I'll get back about this.

/Palle