Svein Erling Tysvær wrote:

>That's at least not true for Fb 1.0.2, which I am still using.
>
>I simply did:
>
>select * from rdb$fields f
>left join rdb$database d on d.rdb$relation_id = f.rdb$segment_length
>
>

Yes, I get the same result here... Very strange...

>and it returned all records in rdb$fields even though many had NULL in
>segment_length.
>
>Are you saying that this has changed in Firebird 1.5 and that the above query
>would return only those records that had did not have a NULL value in
>rdb$fields.rdb$segment_length ???
>
>

No, I noticed on two different DBs, one of which was using IB 6.0, and
the other was, I think, using FB 1.5. I say "I think", because I noticed
this on my development machine, which has both IB 6 and FB 1.5, and so
I'm not sure which one I was using at the time.

Anyway, in that case, I must have misinterpreted the problem. Let me
show you my query:

SELECT tr.date_trait, tr.quant as dose, tr.remarques, tt.libelle as
TraitLib,
pa.libelle as ParcLib, tr.traitement, tr.parcelle,
var.libelle as LibVariete, (pa.surface * tr.quant) as quantite, tr.dar,
po.libelle as operateur,
pt.libelle as tracteur, pm.libelle as materiel
FROM pfi_trtm tr
JOIN pfi_trai tt ON tt.code = tr.traitement
JOIN pfi_parc pa ON pa.code = tr.parcelle
JOIN pfi_tytr ty ON ty.code = tt.type
JOIN pfi_var var ON var.code = tr.variete
full join pfi_materiel pm on pm.code = tr.materiel
full join pfi_tracteur pt on pt.code = tr.tracteur
full join pfi_oper po on po.code = tr.operateur
WHERE tr.date_trait >= :date_min AND tr.date_trait <= :date_max
AND (Tt.code IN (...))
AND (pa.code IN (...))
AND (Ty.code IN (...))
AND (Var.code IN (...))
ORDER BY var.libelle, tr.date_trait, pa.libelle, tt.libelle, tr.quant,
tr.remarques

The three full joins here were added because the original query
(identical, but with left outer joins) did not yield any results when
tr.materiel or tr.tracteur or tr.operateur were null. Since these are
relatively new fields, they didn't have values for the old data. So the
statistics didn't work correctly, because almost everything was excluded.

I spent a long time on this problem, so I'm sure this is what the
problem was due to. I corrected the problem at first, by simply creating
a fictitious line in pfi_materiel, pfi_tracteur and pfi_oper, and then
updating the existing data to point to these new rows. This solved the
problem, such that the original query (without any full joins) gave the
correct results. However, after a while, the old data was put back (The
database I updated is the central server of a centralised production
group. Each individual producer has their own database, and their data
gets sent down to the server every now and then, so of course, this
crushed my changes). They then had the same problem as before, so I went
over there, and tried to figure out why my left outer joins were acting
that way... Until I tried a full join, and it worked...

In this instance, the full join worked correctly (though I was surprised
that the left outer joins didn't). However, since then, a couple days
ago, I had a similar situation. Here's my second query:

select d.*, (select etat from pr_get_etat_dossier(d.code)) as etat_dossier
from dossiers d
where code = :code

To this query, I needed to add a lookup into another table, called
communes. So I tried this:

select d.*, (select etat from pr_get_etat_dossier(d.code)) as etat_dossier,
c1.nom, c2.nom, c3.nom
from dossiers d
left outer join communes c1 on c1.code = d.inhumation_commune
left outer join communes c2 on c2.code = d.cremation_commune
left outer join communes c3 on c3.code = d.ceremonie_commune
where code = :code

This however, gave me no records (the three "_commune" fields were
recently added). <sigh>
I didn't do a lot of tests to see if it was always so, because I had
already come to the conclusion that that was how left outer joins worked
(I just tried again, and now it works, using FB1.5). Remembering my
former experience with left outer joins, I tried a full join, like this:

select d.*, (select etat from pr_get_etat_dossier(d.code)) as etat_dossier,
c1.nom, c2.nom, c3.nom
from dossiers d
full join communes c1 on c1.code = d.inhumation_commune
full join communes c2 on c2.code = d.cremation_commune
full join communes c3 on c3.code = d.ceremonie_commune
where code = :code

This might well have worked, but I wasn't able to test it because it was
disasterously slow. The "dossiers" table contains nearly 200 fields, and
some 1000 records. The communes table contains only 85 records. I just
tried it in IBPlanalyser, and it says 141 ms prepare time, 204858 ms
execution time, and 4 ms fetch time. Here's the plan:

PLAN (F INDEX (I_FACTURES_DOSSIER))(F INDEX (I_FACTURES_DOSSIER))(F
INDEX (I_FACTURES_DOSSIER))
PLAN JOIN (C3 INDEX (RDB$PRIMARY22),JOIN (C2 NATURAL,JOIN (C1 NATURAL,D
NATURAL)))

IBPlanalyser also shows 7 675 753 sequential reads from communes (which
contains 85 records don't forget!), and 7 677 048 sequential reads from
dossiers!

Any ideas what all this could be due to?

Thanks!
Jonathan Neve.