[rt-users] RT 3.8.2 / Pg 8.3.5: Case-insensitive searching on custom fields workaround

[Adam Tingle]

I've discovered a workaround to the issue below that seems to work. I'm
wondering if anyone can tell if this will break anything else in RT? If I
remember correctly, MySQL is case-insensitive by default, so is there really
any difference in having PostgreSQL behave the same way? Here is the diff:

--- SearchBuilder.pm    2009-01-13 12:00:22.000000000 -0500
+++ /usr/local/share/request-tracker3.8/lib/RT/SearchBuilder.pm 2009-01-26
22:58:12.000000000 -0500
@@ -277,17 +277,17 @@
 This Limit sub calls SUPER::Limit, but defaults "CASESENSITIVE" to 1, thus
 making sure that by default lots of things don't do extra work trying to
 match lower(colname) agaist lc($val);

 =cut

 sub Limit {
     my $self = shift;
-    my %args = ( CASESENSITIVE => 1,
+    my %args = ( CASESENSITIVE => 0,
                  @_ );

     return $self->SUPER::Limit(%args);
 }

 =head2 ItemsOrderBy

 If it has a SortOrder attribute, sort the array by SortOrder.


---------- Forwarded message ----------
From: Adam Tingle <adamtingle at gmail.com>
Date: Fri, Jan 23, 2009 at 10:09 PM
Subject: RT 3.8.2 / Pg 8.3.5: Case-insensitive searching on custom fields
not working?
To: rt-users at lists.bestpractical.com


When I do a search for a ticket subject or content, RT queries PostgreSQL
using the ILIKE operator and returns results on case-insensitive basis.
However, when I search for a custom field, RT queries the database using the
LIKE operator and returns results on a case-sensitive basis.

I've enabled the option log_statement = 'all' in postgresql.conf and see the
following output in postgresql-8.3-main.log.

Here is sample output from a search on ticket subject:

LOG:  statement: SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN
Transactions Transactions_1  ON ( Transactions_1.ObjectId = main.id ) JOIN
Attachments Attachments_2  ON ( Attachments_2.TransactionId =
Transactions_1.id )  WHERE (Transactions_1.ObjectType = 'RT::Ticket') AND
(main.Status != 'deleted') AND ( (  ( Attachments_2.Content ILIKE
'%searchterm%' )  ) ) AND (main.Type = 'ticket') AND (main.EffectiveId =
main.id)

Here is sample output from a search on a custom field:

LOG:  statement: SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1  ON (
ObjectCustomFieldValues_1.CustomField = '1' ) AND (
ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_1.Disabled = '0' ) AND (
ObjectCustomFieldValues_1.ObjectId = main.id )  WHERE (main.Status !=
'deleted') AND ( (  (  (  ( ObjectCustomFieldValues_1.Content LIKE
'%searchterm%' OR  (  ( ObjectCustomFieldValues_1.Content = '' OR
ObjectCustomFieldValues_1.Content IS NULL )  AND
ObjectCustomFieldValues_1.LargeContent LIKE '%searchterm%' )  )  )  )  ) )
AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)

Is this a bug or expected results? I'd really like to have case-insensitive
search on CFs.

Thanks RT!

-- 
...................
Adam Tingle
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090126/066e02d5/attachment.htm>