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

[Jesse Vincent]

On Mon, Jan 26, 2009 at 11:42:36PM -0500, Adam Tingle wrote:
> 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:

That seems to be a safe change to make locally. It's worth forwarding to
rt-bugs at bestpractical.com so it can be evaluated for inclusion in a
future RT (and run through the full regression test suite)

Best,
Jesse
> --- 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

> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com

--