Bug in watcher search for Postgres

It seems that searching for matching users to add to a watcher list is
not case-insensitive with Postgres. For example, my ID has
organization of “MailerMailer LLC” but searching for organization
contains mail returns no matches. Searching for Mail does return
matches.

I’m suspecting that there are other places where case-insensitivity
would be desirable, but isn’t, due to the MySQL origins of the queries
(which is case insensitive by default.)

The fix is to find the queries and make them something like:

$x = lc($x);
then run query:

select whatever from sometable where lower(fieldname) like ‘%$x%’

I haven’t dug into the code to fix this just yet.

RT version 2.0.2 (with the 2.0.3 patch)… (how does one verify this
from the UI?)

Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

“RC” == Rafael Corvalan Rafael.Corvalan@linkvest.com writes:

RC> If someone in the list is a postgres specialist, maybe he could tell us
RC> if there is a way to configure a column table to do case insensitive
RC> searches…

There is no such mechanism in PostgreSQL as of 7.1.x. I doubt there
will be, other than using triggers or some funkyness.

You can however, use functions like lower() in unique indexes to
prevent duplicates. Basically, it is a PITA with email addresses
since most of the time they are case-insensitive, but technically the
local address part (left of the @) is not supposed to be altered by
any MTA since it is truly locally interpreted.

I say screw it and store all email addresses lower-cased.

In fact, but this is not the only problem with Postgres. If you are a
registered user with the e-mail "myname@macompany.com" and you send an
e-mail with the “From” field set to "MyName@company.com", you are not
the same user…
Jesse told me to use the method CanonicalizeAddress() in config.pm to
correct that (putting a lc() call), but I think this will not correct
your problem.

If someone in the list is a postgres specialist, maybe he could tell us
if there is a way to configure a column table to do case insensitive
searches…-----Original Message-----
From: Vivek Khera [mailto:khera@kcilink.com]
Sent: lundi, 30. juillet 2001 23:11
To: RT Users
Subject: [rt-users] bug in watcher search for Postgres

It seems that searching for matching users to add to a watcher list is
not case-insensitive with Postgres. For example, my ID has organization
of “MailerMailer LLC” but searching for organization contains mail
returns no matches. Searching for Mail does return matches.

I’m suspecting that there are other places where case-insensitivity
would be desirable, but isn’t, due to the MySQL origins of the queries
(which is case insensitive by default.)

The fix is to find the queries and make them something like:

$x = lc($x);
then run query:

select whatever from sometable where lower(fieldname) like ‘%$x%’

I haven’t dug into the code to fix this just yet.

RT version 2.0.2 (with the 2.0.3 patch)… (how does one verify this
from the UI?)

Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

rt-users mailing list
rt-users@lists.fsck.com http://lists.fsck.com/mailman/listinfo/rt-users

Ok. DBIx-SearchBuilder 0.42-test1 is up on ftp.fsck.com/pub/rt/devel

If people could test out its case insensitivity, especially for full text
searches, I’d appreciate it.

    -jOn Tue, Jul 31, 2001 at 12:39:52PM +0200, Rafael Corvalan wrote:

In fact, but this is not the only problem with Postgres. If you are a
registered user with the e-mail "myname@macompany.com" and you send an
e-mail with the “From” field set to "MyName@company.com", you are not
the same user…
Jesse told me to use the method CanonicalizeAddress() in config.pm to
correct that (putting a lc() call), but I think this will not correct
your problem.

If someone in the list is a postgres specialist, maybe he could tell us
if there is a way to configure a column table to do case insensitive
searches…

-----Original Message-----
From: Vivek Khera [mailto:khera@kcilink.com]
Sent: lundi, 30. juillet 2001 23:11
To: RT Users
Subject: [rt-users] bug in watcher search for Postgres

It seems that searching for matching users to add to a watcher list is
not case-insensitive with Postgres. For example, my ID has organization
of “MailerMailer LLC” but searching for organization contains mail
returns no matches. Searching for Mail does return matches.

I’m suspecting that there are other places where case-insensitivity
would be desirable, but isn’t, due to the MySQL origins of the queries
(which is case insensitive by default.)

The fix is to find the queries and make them something like:

$x = lc($x);
then run query:

select whatever from sometable where lower(fieldname) like ‘%$x%’

I haven’t dug into the code to fix this just yet.

RT version 2.0.2 (with the 2.0.3 patch)… (how does one verify this
from the UI?)


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/


rt-users mailing list
rt-users@lists.fsck.com http://lists.fsck.com/mailman/listinfo/rt-users


rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

jesse reed vincent – root@eruditorum.orgjesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

Linux is like a Vorlon. It is incredibly powerful, gives terse,
cryptic answers and has a lot of things going on in the background.

It works great for the search.
Rafael-----Original Message-----
From: Jesse [mailto:jesse@fsck.com]
Sent: mardi, 31. juillet 2001 19:39
To: Rafael Corvalan
Cc: Vivek Khera; RT Users
Subject: Re: [rt-users] bug in watcher search for Postgres

Ok. DBIx-SearchBuilder 0.42-test1 is up on ftp.fsck.com/pub/rt/devel

If people could test out its case insensitivity, especially for full
text searches, I’d appreciate it.

    -j