---------- Original Message -----------
From: "brian.matchey" <brian.matchey@...>

> I have a problem where a select query works on one user's data but not
> another's. (Firebird server 2.5.1.26351) Strangely, if I reverse the
> join and from statements, the query works for both – why?
>
> This query fails for one user: (by fail, I mean it never returns)
> Select
> TRANSACTIONS.INVOICENUMBER,
> TRANSACTIONS.ITEMNUMBER
> >From TRANSACTIONS
> LEFT JOIN COMMISSIONS ON
> (COMMISSIONS.INVOICENUMBER=TRANSACTIONS.INVOICENUMBER) AND
> (COMMISSIONS.ITEMNUMBER=TRANSACTIONS.ITEMNUMBER) WHERE
> (Commissions.Status IS NOT NULL) AND (Commissions.InvoiceNumber = '-2')
>
> This one works for that same user:
> Select
> TRANSACTIONS.INVOICENUMBER,
> TRANSACTIONS.ITEMNUMBER
> >From COMMISSIONS
> LEFT JOIN TRANSACTIONS ON
> (TRANSACTIONS.INVOICENUMBER=COMMISSIONS.INVOICENUMBER) AND
> (TRANSACTIONS.ITEMNUMBER=COMMISSIONS.ITEMNUMBER) WHERE
> (Commissions.Status IS NOT NULL) AND (Commissions.InvoiceNumber = '-2')

------- End of Original Message -------

In Firebird, a LEFT JOIN forces the optimizer into a particular PLAN -- starting
with the "left" side, then doing lookups against the "right" side. By switching
the direction of the join, besides the logical issues involved (one-to-many or
one-to-one? it matters here!) which changes the number and content of rows
returned, it also changes the physical approach taken by the server, changing
which index it primarily navigates, etc. -- which makes a big difference in
performance.

Could you post the PLANs for each of these statements? I'm pretty sure we'll see
a significant difference in the indexing involved.

-Philip