Svein Erling wrote:

>--- In firebird-support@yahoogroups.com, Jonathan Neve wrote:
>
>
>>Do you mean that the joins aren't being used for anything? The
>>reason is that I omitted the where clause, in which these are used.
>>Obviously, I need them, otherwise I wouldn't bother posting this
>>question.
>>
>>My point was that the joins weren't behaving correctly. Whether or
>>not I use the joins, they should still function correctly...
>>
>>
>
>Then, why don't you show us the entire select, Jonathan?
>

Ok, here it is :

SELECT distinct o.code, o.date_ordre, o.etabli, o.cuve, o.chef_fab,
o.prev, o.date_charg, o.type_of
FROM ordre_fab o
left outer JOIN ordre_fabcorps ofc on ofc.code = o.code and o.type_of = "PX"
left outer JOIN ordre_fabcorps_sf sfc on (sfc.code = o.code and
o.type_of = "SF")
left outer join produits p on ofc.produit = p.code
where (p.formule = :formule) or (sfc.semifini = :formule)
ORDER BY o.date_ordre


The reason I had originally omitted part of the query, was simply
because the additionnal details actually make no difference. So I had
simplified the query, so that you didn't need to waste time on the
details that don't cause any problem, but could rather see precisely
where the problem is right away...

Anyway, if you prefer it this way I don't mind... (Actually, this is a
somewhat reconstitued query, because I have by now found a workaround.
Still, I just tried this query again, and the behaviour is still, as far
as I can see, illogical. If you're interested (not that it makes any
difference...), my current query is below. There is of course no problem
with this query, it yields what I wanted the first one to yield.)

SELECT o.code, o.date_ordre, o.etabli, o.cuve, o.chef_fab, o.prev,
o.date_charg, o.type_of, p.formule, cast(f.libelle as varchar(100)) as
formulelib, sum(ofc.quant) as tonnage
FROM ordre_fab o
JOIN ordre_fabcorps ofc on ofc.code = o.code
JOIN produits p on p.code = ofc.produit
join formules f on f.code = p.formule
WHERE o.date_ordre >= :dmin and o.date_ordre < (:dmax + 1)
and o.type_of = 'PX'
and (%w_form) /*These are simply macroes for an RxQuery*/
and (%w_cheffab)
group by o.code, o.date_ordre, o.etabli, o.cuve, o.chef_fab, o.prev,
o.date_charg, o.type_of, p.formule, f.libelle

union all

SELECT o.code, o.date_ordre, o.etabli, o.cuve, o.chef_fab, o.prev,
o.date_charg, o.type_of, sf.code, cast(sf.libelle as varchar(100)),
sum(sfc.quant)
FROM ordre_fab o
JOIN ordre_fabcorps_sf sfc on sfc.code = o.code
join semifinis sf on sf.code = sfc.semifini
WHERE o.date_ordre >= :dmin and o.date_ordre < (:dmax + 1)
and o.type_of = 'SF'
and (%w_semifini)
and (%w_cheffab)
group by o.code, o.date_ordre, o.etabli, o.cuve, o.chef_fab, o.prev,
o.date_charg, o.type_of, sf.code, sf.libelle

ORDER BY 1

>I'm not
>conserned if Firebird gives strange results to meaningless queries,
>but if the results should happen to be weird in useful cases, then
>that would be a problem that ought to be addressed.
>
>

Well, this was a useful case. The query was by no means meaningless, I
had just simplified it a bit. Besides, FireBird should be able to handle
even meaningless queries.

If a certain query doesn't give the results I would expect, it can
either be because I'm doing something wrong, or else because there's a
bug in FireBird. Either way, it seems to me that it's worth
investigating. If the problem comes from me, then I would like to learn
from my mistakes, and if it's a bug, I would like it to get reported,
taken seriously, and, ultimately, fixed. Don't you agree with this?

>For myself, I cannot see any point in left joins when you don't select
>anything from the right tables. Though that may be due to limited
>experience.
>
>

Well, I'm using them in the where clause in this particular case, with
an OR. In other words, in my query above, a certain record may join
either on ordre_fabcorps or on ordre_fabcorps_sf, but it must join on
one of the two, otherwise the where clause excludes it. But I agree with
you that without such a where clause, there would be no point making it
a LEFT OUTER join.

Thanks!

Jonathan Neve.


[Non-text portions of this message have been removed]