Re: BUG #6154: wrong result with nested left-joins

Oops.. I just saw, that I send you "correct" (already with "workaround")
sql-code instead of "wrong"...
my mistake =(( don't kill me - I spend too much time with this piece of code
today...

But I'm sure that you got the point from my description of the results...
but just in case here is "wrong" (difference is in "SELECT sub3.key3,
sub4.value2 FROM" ):
SELECT * FROM
(
SELECT 1 as key1
) sub1
LEFT JOIN
(
SELECT sub3.key3, sub4.value2 FROM
(
SELECT 1 as key3
) sub3
LEFT JOIN
(
SELECT sub5.key5, COALESCE(sub6.value1, 1) as
value2
FROM
(
SELECT 1 as key5
) sub5
LEFT JOIN
(
SELECT 1 as key6, value1
FROM
(
SELECT
NULL::integer as value1
) sub7
WHERE false
) sub6 ON false

)
sub4 ON sub4.key5=sub3.key3
)
sub2 ON sub1.key1 = sub2.key3

А.И.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, August 08, 2011 10:02 PM
To: Kevin Grittner
Cc: listar; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #6154: wrong result with nested left-joins

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> "listar" <listar(at)mail(dot)ru> wrote:
>> PostgreSQL version: 8.4.5

>> Description: wrong result with nested left-joins

>> And this is the problem - value2 can't be NULL because of COALESCE in
>> sub4 (at least I think that it can't be =))

> This works correctly in release 9.0.4 and development HEAD. I don't
> still have any machines handy which are running 8.4, but you might
> want to try it on the latest bug-fix version of 8.4 (currently
> 8.4.8) to see if the fix was back-patched.

It works for me too in 8.4.recent; but I believe the relevant fix is in
8.4.5, which makes me doubt the OP's report of his server version.

http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=dc9cc887b7
4bfa0d40829c4df66dead509fdd8f6

regards, tom lane