Обсуждение: BUG #2714: Wrong Result with static number

The following bug has been logged online:

Bug reference:      2714
Logged by:          Frank Schmidt
Email address:      schmidt@dozent.net
PostgreSQL version: 8.1.5
Operating system:   Windows
Description:        Wrong Result with static number
Details:

Follow skript works fine:

SELECT DISTINCT Artikelname, cast(BD1.Einzelpreis * (1 - Rabatt) as
decimal(8,2)) AS Preis, Rabatt
FROM Artikel A1, Bestelldetails BD1
WHERE A1.ArtikelNr = BD1.ArtikelNr
AND BD1.Einzelpreis * (1 - Rabatt) as decimal(8,2))>= ALL (SELECT
Einzelpreis * (1 - Rabatt) as decimal(8,2))
  FROM Bestelldetails);

But when I change the Statement "BD1.Einzelpreis * (1 - Rabatt) as
decimal(8,2))>= " to a static number (like 5) no result will be generated.

SELECT DISTINCT Artikelname, cast(BD1.Einzelpreis * (1 - Rabatt) as
decimal(8,2)) AS Preis, Rabatt
FROM Artikel A1, Bestelldetails BD1
WHERE A1.ArtikelNr = BD1.ArtikelNr
AND 5 >= ALL (SELECT Einzelpreis * (1 - Rabatt) as decimal(8,2))
  FROM Bestelldetails);

I can change the value to anything (real existing data), but nothing
happend.

Whats wrong?

"Frank Schmidt" <schmidt@dozent.net> writes:
> But when I change the Statement "BD1.Einzelpreis * (1 - Rabatt) as
> decimal(8,2))>= " to a static number (like 5) no result will be generated.

> SELECT DISTINCT Artikelname, cast(BD1.Einzelpreis * (1 - Rabatt) as
> decimal(8,2)) AS Preis, Rabatt
> FROM Artikel A1, Bestelldetails BD1
> WHERE A1.ArtikelNr = BD1.ArtikelNr
> AND 5 >= ALL (SELECT Einzelpreis * (1 - Rabatt) as decimal(8,2))
>   FROM Bestelldetails);

I don't think you're showing us what you actually typed, because that
"as decimal(8,2)" isn't valid syntax.  But probably the real issue is
that you've got some NULL entries in Bestelldetails.  An ALL test
cannot succeed (return TRUE) if there are null rows.

            regards, tom lane