I observed something similar many years ago, though I don't remember the exact
error message. I think I solved it by simply using shorter aliases and removing
redundant spaces and words
(e.g. I would change from
from
mv$pdv1 pedidovend0_
left outer join
crt1 carteira1_
on pedidovend0_.numcrt1=carteira1_.numcrt1
to
from mv$pdv1 p0
left join crt1 c1
on p0.numcrt1=c1.numcrt1
)
The reason for my problem in the first place was me trying to flatten a result
set in Firebird rather than some statistical package (16 left joins or so since
there could be up to 16 rows per person, if each of these 16 rows could have up
to four records in another table, then you end up with 64 potential fields for
each 'real' field - needless to say, the vast majority of the result fields
would be <null>). I think the max length of the SQL statement might have been
16 or 32Kb and without removing your spaces, your query is 18Kb. Though I don't
know whether this was a Firebird restriction or had to do with the tool that I
used.
One thing is the length of the query itself, another thing is the row size of
your result set (here the max size at least was 64Kb, don't know whether it has
increased or not). Normally, this can be avoided by defining your fields as
BLOB rather than long (VAR)CHAR fields.
I'd also like to point out that it is normally better to have all your (inner)
JOINs before any LEFT JOINs, if possible. The reason is that Firebirds
optimizer freely rearranges all tables until it encounters a LEFT JOIN, but
after that, the order is fixed (at least on Firebird 1.5 and probably 2.1,
don't know about Firebird 2.5). So, in your case, Firebird cannot choose the
order of the tables in the PLAN at all, if you'd put 'inner join cad1 conta2_'
above 'left outer join crt1 carteira1_', then the optimizer could have chosen
whether it should have conta2_ or pedidovend0_ as the first table in the plan.
However, in your case it is very unlikely that you get any better plan by
following my advice, your [LEFT] JOIN and WHERE clauses indicate that there's
only one sensible plan that could be created (provided of course, that
pedidovend0_.nummv$pdv1 and the fields to the right in you JOIN fields are
indexed).
HTH,
Set
