At 12:39 PM 29/03/2005 +0000, you wrote:

>Hi,
>
>I found that the problem is in the JOIN.
>It seems that the FIRST, SKIP is applied before the JOIN is executed.

FIRST and SKIP are *always* applied to the final output set, so there's
another explanation somewhere.

>The second table (msg_to) has more than one record with the same
>common_id. FB actually applies the FIRST and the SKIP to the records
>from the msg_to & messages tables, rather than to the JOINed result
>set. Then the JOIN is applied and the returned results are
>from 'messages' after what remained from msg_to.
>
>Here is the SQL again, if someone has ideas:
>
>select first 30 skip 100 messages.*, msg_to.to_email, msg_to.to_name,
>msg_to.to_type from messages,msg_to where (messages.common_id =
>msg_to.common_id) order by messages.msg_key asc, msg_to.to_type desc
>
>I need to apply the FIRST, SKIP to the result set aftet the JOIN.
>Any help will be highly appreciated!

Are you certain that the unlimited set would return more than 107 rows? If
there are not 30 rows left to return, you won't get 30 rows. That is, the
initial 100 rows in the set will be skipped before the query begins
returning rows. It will stop when it has output 30 rows or when it has run
out of rows, whichever comes first.

Remember that the inner join is an exclusive join so, potentially, some
Messages rows will never get into the unlimited set because of
no-matches. Also, think of your ordering scheme here and what happens if
it actually causes all but 7 records to be skipped.

Your use of the implicit join might be the problem (though I don't *know*
of any reason why it should be). Still, try the explicit join.

select first 30 skip 100
m.*,
mt.to_email,
mt.to_name,
mt.to_type
from messages m
join msg_to mt
on m.common_id = mt.common_id
order by m.msg_key asc, mt.to_type desc

If it makes a difference, then make a reproducible test case with your
output numbers and present a bug report.

./hb