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 <adamtin...@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@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
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com