Mailing List Archive: dbsearch.c:db_search

dbsearch.c:db_search_range() bad query???

Apr 2, 2004, 8:42 AM

Post #1 of 5 (404 views)

Hallo everyone,

I'm working on some private modifications to dbmail for my company and noticed something strange in the db_search_range() function in dbsearch.c...

I was testing the IMAP search functionality when I came across the fact that my searches were turning up bad (no results when there should be) and set debug to maximum and found that it was due to a bad query.

in dbsearch.c (line 359):
snprintf(query, DEF_QUERYSIZE,
"SELECT messageblk FROM messageblks "
"WHERE message_idnr = '%llu' "
"ORDER BY messageblk_idnr", msg_idnr);

yet there is _no_ message_idnr in the messageblks table! did the messages table get left out of the FROM clause accidentally? changing 'FROM messageblks' to 'FROM messageblks,messages' seems to make the query work, but I'm wondering how that query might have ended up the way it is?

Blessings in Jesus,
Paul F. De La Cruz

Re: dbsearch.c:db_search_range() bad query??? [ In reply to ]

paul at nfg

Apr 2, 2004, 11:08 AM

Post #2 of 5 (392 views)

Permalink

Paul F. De La Cruz wrote:
> in dbsearch.c (line 359): snprintf(query, DEF_QUERYSIZE, "SELECT
> messageblk FROM messageblks " "WHERE message_idnr = '%llu' " "ORDER
> BY messageblk_idnr", msg_idnr);
>
> yet there is _no_ message_idnr in the messageblks table! did the
> messages table get left out of the FROM clause accidentally? changing
> 'FROM messageblks' to 'FROM messageblks,messages' seems to make the
> query work, but I'm wondering how that query might have ended up the
> way it is?

Smells like dbmail-1.x to me. Now the physmessage table has been
inserted between messages and messageblks. So you probably need
something like:

snprintf(query, DEF_QUERYSIZE,
"SELECT messageblk FROM messageblks"
"LEFT JOIN messages ON "
"messages.physmessage_id=messageblks.physmessage_id "
"WHERE messages.message_idnr = '%llu' "
"ORDER BY messageblk_idnr", msg_idnr);

Will this work for postgresql ? Looks like ansi sql to me, but I'm not sure.

--
________________________________________________________________
Paul Stevens mailto:paul@nfg.nl
NET FACILITIES GROUP PGP: finger paul@nfg.nl
The Netherlands________________________________http://www.nfg.nl

Re: dbsearch.c:db_search_range() bad query??? [ In reply to ]

polito at blackbeanie

Apr 2, 2004, 11:22 AM

Post #3 of 5 (393 views)

Permalink

That works on postgresql, but if that looks like dbmail-1.x, how'd it end up on the CVS version of dbmail? I checked out the source just a minute ago and sure enough, it has the bad query in it.

On Fri, Apr 02, 2004 at 08:08:21PM +0200, Paul J Stevens wrote:
> Paul F. De La Cruz wrote:
> >in dbsearch.c (line 359): snprintf(query, DEF_QUERYSIZE, "SELECT
> >messageblk FROM messageblks " "WHERE message_idnr = '%llu' " "ORDER
> >BY messageblk_idnr", msg_idnr);
> >
> >yet there is _no_ message_idnr in the messageblks table! did the
> >messages table get left out of the FROM clause accidentally? changing
> >'FROM messageblks' to 'FROM messageblks,messages' seems to make the
> >query work, but I'm wondering how that query might have ended up the
> >way it is?
>
> Smells like dbmail-1.x to me. Now the physmessage table has been
> inserted between messages and messageblks. So you probably need
> something like:
>
> snprintf(query, DEF_QUERYSIZE,
> "SELECT messageblk FROM messageblks"
> "LEFT JOIN messages ON "
> "messages.physmessage_id=messageblks.physmessage_id "
> "WHERE messages.message_idnr = '%llu' "
> "ORDER BY messageblk_idnr", msg_idnr);
>
> Will this work for postgresql ? Looks like ansi sql to me, but I'm not sure.

Re: dbsearch.c:db_search_range() bad query??? [ In reply to ]

paul at nfg

Apr 2, 2004, 2:32 PM

Post #4 of 5 (401 views)

Permalink

dbmail2 is a refactoring of the dbmail1 codebase. Not a total rewrite.
So there's a lot of code from dbmail1 in there. This looks to me like a
piece of code that was overlooked by Ilja when he refactored the db
layer by introducing the physmessage table.

Searching is apparently a little tested part of dbmail2 . However, with
Leif's implementation of the SORT extension, that is bound to change.

Paul F. De La Cruz wrote:
> That works on postgresql, but if that looks like dbmail-1.x, how'd it
> end up on the CVS version of dbmail? I checked out the source just a
> minute ago and sure enough, it has the bad query in it.
>
> On Fri, Apr 02, 2004 at 08:08:21PM +0200, Paul J Stevens wrote:
>
>> Paul F. De La Cruz wrote:
>>
>>> in dbsearch.c (line 359): snprintf(query, DEF_QUERYSIZE, "SELECT
>>> messageblk FROM messageblks " "WHERE message_idnr = '%llu' "
>>> "ORDER BY messageblk_idnr", msg_idnr);
>>>
>>> yet there is _no_ message_idnr in the messageblks table! did the
>>> messages table get left out of the FROM clause accidentally?
>>> changing 'FROM messageblks' to 'FROM messageblks,messages' seems
>>> to make the query work, but I'm wondering how that query might
>>> have ended up the way it is?
>>
>> Smells like dbmail-1.x to me. Now the physmessage table has been
>> inserted between messages and messageblks. So you probably need
>> something like:
>>
>> snprintf(query, DEF_QUERYSIZE, "SELECT messageblk FROM messageblks"
>> "LEFT JOIN messages ON "
>> "messages.physmessage_id=messageblks.physmessage_id " "WHERE
>> messages.message_idnr = '%llu' " "ORDER BY messageblk_idnr",
>> msg_idnr);
>>
>> Will this work for postgresql ? Looks like ansi sql to me, but I'm
>> not sure.
>
> _______________________________________________ Dbmail-dev mailing
> list Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>

--
________________________________________________________________
Paul Stevens mailto:paul@nfg.nl
NET FACILITIES GROUP PGP: finger paul@nfg.nl
The Netherlands________________________________http://www.nfg.nl

Re: dbsearch.c:db_search_range() bad query??? [ In reply to ]

ilja at ic-s

Apr 5, 2004, 12:38 AM

Post #5 of 5 (393 views)

Permalink

Paul J Stevens wrote:
> dbmail2 is a refactoring of the dbmail1 codebase. Not a total rewrite.
> So there's a lot of code from dbmail1 in there. This looks to me like a
> piece of code that was overlooked by Ilja when he refactored the db
> layer by introducing the physmessage table.

You're right.. it seems like I overlooked this one. I'll fix it right
away. I'll test the query you provided in an earlier message.

Ilja