--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 09:46 AM 11/04/2005 +0000, you wrote:
>
>
> >Hello,
> >I think, i have found a bug in Firebird 1.5.2.
> >I have a SQL-Statement that works correct with the Version
> >1.5.0 and beta-versions before. Then i have updated to Version 1.5.2
> >and the result of the Statment is false.
> >
> >Here the Statment:
> >
> >SELECT
> > a.HS, a.KONTO, c.MC,
> > a.JAHR, c.ANTKOST, c.ANTST,
> > a.BETRAG * d.WERT * c.GVKOST,
> > a.STEUER * d.WERT * c.GVMWST,
> > b.STEUER * d.WERT * c.GVMWST,
> > e.BEZ
> >FROM
> > KONT c, KONTOBUCHSTAT d, H_ZEITRAUM e, KONTERG a LEFT OUTER JOIN
> >KONTERG b
> > ON
> > -b.JAHR = a.JAHR AND
> > a.Monat = b.MONAT AND
> > a.KONTO = b.KONTO
> >WHERE
> > a.KONTO = c.KONTO AND
> > a.KONTO >= 1000 AND a.KONTO < 30000 AND
> > a.JAHR = :jahr AND
> > a.MONAT = :quartal + 12 AND
> > a.MONAT = e.LFN AND
> > (a.BETRAG <> 0 OR a.STEUER <> 0) AND
> > a.HS = d.TYP AND
> > (c.ANTKOST<>0 OR c.ANTST<>0) AND
> > (a.BETRAG * c.GVKOST>0 OR a.STEUER * c.GVMWST > 0) AND
> > a.STEUER * d.WERT * c.GVMWST <>0
> >ORDER BY
> > 1,3,2;
> >
> >and here a part of the correct Result with FB 1.5.0:
> >
> >"S","2005","Auto
> >tanken","2005","100","100";"-96,00";"-15,36";"NULL";"I. Quartal"
> >"S";"2004";"Betriebsausgaben
> >16%";"2005";"100";"100";"-302,75";"-48,44";"NULL";"I. Quartal"
> >"S";"2021";"Energie";"2005";"30";"30";"-191,60";"-30,66";"-21,46";"I.
> >Quartal"
> >"S";"2039";"Miete
> >Server";"2005";"100";"100";"-172,24";"-27,56";"NULL";"I. Quartal"
> >
> >and here a part of the false Result with FB 1.5.2:
> >
> >"S";"2005";"Energie";"2005";"100";"100";"-96,00";"-15,36";"NULL";"I.
> >Quartal"
> >"S";"2004";"Energie
> >16%";"2005";"100";"100";"-302,75";"-48,44";"NULL";"I. Quartal"
> >"S";"2021";"Energie";"2005";"30";"30";"-191,60";"-30,66";"-21,46";"I.
> >Quartal"
> >"S";"2039";"Energie";"2005";"100";"100";"-172,24";"-27,56";"NULL";"I.
> >Quartal"
> >
> >For better reading i have converted the results in csv-data.
> >Every new row starting with "S".
> >
> >The difference is in the 3. column (c.MC). I became in every row
> >the word "Energie". What is the difference between the rows?
> >The word "Energie" is ok for the third row, not for all others.
> >It is only the row that have not NULL in the 9 Column. This
> >column is the result of the LEFT OUTER JOIN.
> >
> >I hope someone can find the error.
>
> Oh gosh!! what a stellar example of how NOT to write a query!! The

fatal

> error is in mixing implicit (SQL-89) and explicit (SQL-92) join
> syntaxes. Never do it.
>
> Implicit join syntax was what they used to do before outer joins became
> possible. You can still get away with an all-inner-join query using

SQL-89

> syntax (though it's not sensible, when SQL-92 syntax makes it much

easier

> to find your mistakes). It is neither safe nor sensible to try to mix
> them. Whenever you have an outer join in the query, you MUST use

explicit

> joins throughout.
>
> Try this query again, rewritten in explicit join syntax, and you

will most

> likely get a set of results that is different again - but this time,

correct.

>
> SELECT
> a.HS,
> a.KONTO,
> c.MC,
> a.JAHR,
> c.ANTKOST,
> c.ANTST,
> a.BETRAG * d.WERT * c.GVKOST as Val1,
> a.STEUER * d.WERT * c.GVMWST as Val2,
> b.STEUER * d.WERT * c.GVMWST as Val3,
> e.BEZ
> FROM
> KONT c
> join KONTERG a
> on a.KONTO = c.KONTO
> join KONTOBUCHSTAT d
> on a.HS = d.TYP
> join H_ZEITRAUM e
> on a.MONAT = e.LFN
> LEFT JOIN KONTERG b
> ON b.JAHR = a.JAHR AND
> a.Monat = b.MONAT AND
> a.KONTO = b.KONTO
> WHERE
> a.KONTO between 1000 AND 29999
> AND a.JAHR = :jahr
> AND a.MONAT = :quartal + 12
> AND (a.BETRAG <> 0 OR a.STEUER <> 0)
> AND (c.ANTKOST<>0 OR c.ANTST<>0)
> AND (a.BETRAG * c.GVKOST>0 OR a.STEUER * c.GVMWST > 0)
> AND a.STEUER * d.WERT * c.GVMWST <>0
> ORDER BY
> 1,3,2;
>
> (At this point, we're looking at syntax repair, so I'm not

questioning the

> intended purpose of the re-entrant left join on KONTERG!)
>
> ./hb

Thanks for your help. Now it works correct.
the other point, left join on KONTERG, are necessary.

Frank