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