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?)
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?)
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?)
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