[EMAIL PROTECTED] wrote:
> My IMAP client fails when I try to check my account (different clients
> exhibit different behaviors), and I find a failed SQL query in my logs.
>
> The query is done using double-quotes. At jnorell's recomendation, I ran
> the query by hand using single-quotes, and it went through successfully.
> My runtime follows:
[...]
> This has already been fixed in the trunk for some weeks now. The debian
> packages will be upgraded asap (after the breakage from the modular setup
> has been fixed).
The double-quotes versus single-quotes issue has been fixed but the
query itself is still in need of some attention (I'm looking at -trunk
from subversion, updated as of this afternoon).
The problem is that under PostgreSQL and SQLite, the query is doing a
case-sensitive compare (while MySQL is doing a case-insensitive one).
This causes the query to return NIL for pretty much all requests, as
the client doesn't always pass header values that always
case-sensitively match the header names stored in the database.
This doesn't seem to cause Thunderbird too much trouble but Mutt
doesn't like it at all. It doesn't end up showing anything but
"blank" entries on the summary page.
The solution is to do a comparison of upper-case-converted values. I
modified the query so that it compares the upper-case conversion of
the headername field against the upper-case conversions of the
requested header names, so that it now looks like this (this is in
dm_imaputil.c at around line 738):
h = g_list_join((GList *)headers,"'),upper('");
g_string_printf(q,"SELECT physmessage_id,headername,headervalue "
"FROM %sheadervalue v "
"JOIN %sheadername n ON v.headername_id=n.id "
"WHERE physmessage_id IN (%s) "
"AND upper(headername) %s IN (upper('%s'))",
DBPFX, DBPFX, r->str, not?"NOT":"", h->str);
I've attached a diff generated by 'svn diff dm_imaputil.c'
The "upper" function is available in MySQL, PostgreSQL, and SQLite,
and is ANSI SQL standard conformant, so the above should be considered
reasonably portable. Both PostgreSQL and SQLite will treat character
string comparisons via IN and '=' in a case-sensitive fashion. MySQL
gets this wrong, at least by default. So does MSSQL, but I think that
can be changed on a per-database level.
Note that this same basic approach should probably be taken whenever a
case-insensitive character string comparison is needed.
Note that in general, you should *not* initially test your SQL queries
with MySQL. Use PostgreSQL instead, since it's much more conformant
with the ANSI SQL standard, and stick to ANSI SQL if you can. I'm
sure that has been said before, but this bug clearly illustrates why.
--
Kevin Brown [EMAIL PROTECTED]
Index: dm_imaputil.c
===================================================================
--- dm_imaputil.c (revision 1938)
+++ dm_imaputil.c (working copy)
@@ -735,13 +735,13 @@
GTree *t;
r = g_list_join((GList *)ids,",");
- h = g_list_join((GList *)headers,"','");
+ h = g_list_join((GList *)headers,"'),upper('");
g_string_printf(q,"SELECT physmessage_id,headername,headervalue "
"FROM %sheadervalue v "
"JOIN %sheadername n ON v.headername_id=n.id "
"WHERE physmessage_id IN (%s) "
- "AND headername %s IN ('%s')",
+ "AND upper(headername) %s IN (upper('%s'))",
DBPFX, DBPFX, r->str, not?"NOT":"", h->str);
g_string_free(r,TRUE);
