> Problem is that MySQL will not use the index if the query says
'lower(column)'.
mysql> explain select a from test where lower(a)=lower('abc');
+-------+-------+---------------+------------+---------+------+------+--
-----------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+-------+---------------+------------+---------+------+------+--
-----------------------+
| test | index | NULL | test_index | 201 | NULL | 3 |
where used; Using index |
+-------+-------+---------------+------------+---------+------+------+--
-----------------------+
1 row in set (0.00 sec)
The above tells me that's not true.
... John
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Ilja Booij
Sent: Thursday, March 04, 2004 10:11 PM
To: dbmail@dbmail.org
Subject: Re: [Dbmail] CAP domain results in "no such user" mail bounce
Hi
John Hansen wrote:
> Ok,...
>
> In mysql you only need an index on column. It does not need an index
> on
> lower(column) as it will still use the index, so that's good news, no?
Problem is that MySQL will not use the index if the query says
'lower(column)'.
>
> You still need constraints tho, other wise it could be possible to add
> two or more identical columns.
Inserting two similar aliases is no problem. In fact, having several
similar aliases is a feature :) Usernames must be unique though.
>
> Unique index on lower() in pg will work,. But dono how to do that in
> mysql.
In MySQL, having a unique index will work. The uniqueness is tested
case-insensitively. I've just run a quick test to verify this.
Ilja
>
> Regards,
>
> John
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> Behalf Of Ilja Booij
> Sent: Thursday, March 04, 2004 1:32 AM
> To: dbmail@dbmail.org
> Subject: Re: [Dbmail] CAP domain results in "no such user" mail bounce
>
> Just when I was ready to commit the changes, I found out that MySQL
> cannot have an index like:
>
> INDEX (lower(alias)), because it cannot have expressions in an index..
>
> So, the whole plan is a no-go. Unless we can come up with some other
> way to do the same thing. We could change all strings that we need to
check
> case insensitively to lowercase before feeding it them to the query
> for instance. Are there any convenient functions functions for this
> (that also work with characters other than A-Z?
>
> Ilja
>
>
>
>>>-----Original Message-----
>>>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
>>>Behalf Of Ilja Booij
>>>Sent: Wednesday, March 03, 2004 11:30 PM
>>>To: dbmail@dbmail.org
>>>Subject: Re: [Dbmail] CAP domain results in "no such user" mail
>>>bounce
>>>
>>>well, after some more reading, I guess it's a bit different:
>>>
>>>IMAP RFC3501 takes no position on case sensitivity in mailbox names.
>>>So, we can support either case sensitive or case insensitive mailbox
>>>names. Currently, mailboxes are case sensitive when using PostgreSQL,
>
>
>>>and case insensitive when using MySQL. This might not be what we
>>>want. We would probably like to have case sensitivity to be
>>>consistent, whichever database backend we use.
>>>
>>>any opinions?
>>>
>>>Ilja
>>>
>>>John Hansen wrote:
>>>
>>>
>>>
>>>>Ilja,
>>>>
>>>>If you wan't RFC'ism, you should put code in the software to do the
>>>>comparison, not in the SQL, mysql is not case sensitive,. So on
>>>>mysql dbmail would not conform to the RFC in this case.
>>>>
>>>>... John
>>>>
>>>>-----Original Message-----
>>>>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
>>>>Behalf Of Ilja Booij
>>>>Sent: Wednesday, March 03, 2004 10:05 PM
>>>>To: dbmail@dbmail.org
>>>>Subject: Re: [Dbmail] CAP domain results in "no such user" mail
>>>>bounce
>>>>
>>>>In DBMail 1.2.3 the queries were all defined in the backend driver.
>>>>In
>>>
>>>
>>>
>>>>DBMail 2.0, the backend driver has become much smaller and simpler,
>>>>and most functionality has been moved to db.c, which is in use by
>>>>all
>>>
>>>
>>>(read:
>>>
>>>
>>>>both) backends.
>>>>
>>>>Anyway, your suggestions should work.
>>>>
>>>>By the way, I don't think we should lowercase the mailboxes, as RFC
>>>>3501 takes no position on case-sensitivity of mailbox names, except
>>>>for "INBOX", which should always be case insensitive.
>>>>
>>>>Case insensitivity is limited to aliases (including domain aliases)
>>>>and usernames, I guess.
>>>>
>>>>Ilja
>>>>
>>>>John Hansen wrote:
>>>>
>>>>
>>>>
>>>>
>>>>>I'm confused,.... aren't the sql queries defined in each backend
>>>>
>>>>
>>>>driver?
>>>>
>>>>
>>>>
>>>>>If not,
>>>>>lower(column)=lower("value%"), and
>>>>>lower(column) like lower("value%")
>>>>>
>>>>>should do the trick.
>>>>>
>>>>>... John
>>>>>-----Original Message-----
>>>>>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
>>>>>Behalf Of Ilja Booij
>>>>>Sent: Wednesday, March 03, 2004 8:47 PM
>>>>>To: dbmail@dbmail.org
>>>>>Subject: Re: [Dbmail] CAP domain results in "no such user" mail
>>>>>bounce
>>>>>
>>>>>We can't use ILIKE, as it's not supported by MySQL. It's also not a
>
>
>>>>>part of SQL92, is it? I'll look for a way around this.
>>>>>
>>>>>Ilja
>>>>>
>>>>>John Hansen wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>Probably a bug from being ported to postgres, as mysql is not case
>
>
>>>>>>sensitive, but postgresql is.
>>>>>>
>>>>>>As such, all comparisons in where clauses should be cast using
>>>>>>lower()
>>>>>
>>>>>
>>>>>
>>>>>>on both sides of the comparison sign. Or in the case of LIKE, use
>>>>>
>>>>>
>>>>>ILIKE.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>Regards,
>>>>>>
>>>>>>John
>>>>>>
>>>>>>-----Original Message-----
>>>>>>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
>>>>>>Behalf Of David
>>>>>>Sent: Tuesday, February 24, 2004 9:47 AM
>>>>>>To: dbmail@dbmail.org
>>>>>>Subject: [Dbmail] CAP domain results in "no such user" mail bounce
>>>>>>
>>>>>>
>>>>>>I noticed that when dbmail checks for a delivery point, if the
>>>>>>domain
>>>
>>>
>>>
>>>>>>name does not match one listed in the aliases table case for case,
>
>
>>>>>>it
>>>
>>>
>>>
>>>>>>will bounced the mail with "so such user". Has this comparison
>>>>>>always
>>>>>
>>>>>
>>>>>
>>>>>>been case sensitive? For example, if [EMAIL PROTECTED] is in the
>>>>>>aliases
>>>>>
>>>>>
>>>>>
>>>>>>table and then I get a mail address to [EMAIL PROTECTED], dbmail
>>>>>>rejects
>>>
>>>
>>>
>>>>>>it. Actually it will reject anything not spelled exactly as
>>>>>>"example.com". This is the error message generated.
>>>>>>
>>>>>>dbmail/smtp[31935]: bounce.c,bounce: sending 'no such user' bounce
>
>
>>>>>>for
>>>>>
>>>>>
>>>>>
>>>>>>destination [EMAIL PROTECTED]
>>>>>>
>>>>>>Is this a postfix problem, PostgreSQL problem, or a dbmail
>
> problem?
>
>>>>>>__________________________________
>>>>>>Do you Yahoo!?
>>>>>>Yahoo! Mail SpamGuard - Read only the mail you want.
>>>>>>http://antispam.yahoo.com/tools
>>>>>>_______________________________________________
>>>>>>Dbmail mailing list
>>>>>>Dbmail@dbmail.org
>>>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>>>>_______________________________________________
>>>>>>Dbmail mailing list
>>>>>>Dbmail@dbmail.org
>>>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>>>
>>>>>
>>>>>_______________________________________________
>>>>>Dbmail mailing list
>>>>>Dbmail@dbmail.org
>>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>>>
>>>>>
>>>>>_______________________________________________
>>>>>Dbmail mailing list
>>>>>Dbmail@dbmail.org
>>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>>
>>>>
>>>>_______________________________________________
>>>>Dbmail mailing list
>>>>Dbmail@dbmail.org
>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>>
>>>>
>>>>_______________________________________________
>>>>Dbmail mailing list
>>>>Dbmail@dbmail.org
>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>
>>>
>>>_______________________________________________
>>>Dbmail mailing list
>>>Dbmail@dbmail.org
>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>
>>>
>>>_______________________________________________
>>>Dbmail mailing list
>>>Dbmail@dbmail.org
>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>
>>_______________________________________________
>>Dbmail mailing list
>>Dbmail@dbmail.org
>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>
> _______________________________________________
> Dbmail mailing list
> Dbmail@dbmail.org
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>
>
> _______________________________________________
> Dbmail mailing list
> Dbmail@dbmail.org
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
