--- In firebird-support@yahoogroups.com, Jonathan Neve wrote:

> 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?

Well, three communes each fully joined to dossiers could be as bad as
85*85*85*1000 = 614m. It would be better trying to find the real problem and not
make a full join several times. Could it be as simple as you forgetting to use
an alias for 'code' in the where clause and that IB compared it to communes.code
rather than dossiers.code (I expect Firebird to return an error about ambiguous
code)?

Set