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"...=20
my mistake =3D(( don't kill me - I spend too much time with this piece of c=
ode
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=20
(
                SELECT sub3.key3, sub4.value2 FROM
                (
                               SELECT 1 as key3
                ) sub3
                LEFT JOIN=20
                (
                               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=3Dsub3.key3
)
sub2 ON sub1.key1 =3D sub2.key3


=E1.=E9.


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

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> "listar" <listar@mail.ru> wrote:
>> PostgreSQL version: 8.4.5

>> Description:        wrong result with nested left-joins
=20
>> And this is the problem - value2 can't be NULL because of COALESCE in=20
>> sub4 (at least I think that it can't be =3D))
=20
> This works correctly in release 9.0.4 and development HEAD.  I don't=20
> still have any machines handy which are running 8.4, but you might=20
> 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=3Dpostgresql.git&a=3Dcommitdiff&h=3Ddc9=
cc887b7
4bfa0d40829c4df66dead509fdd8f6

            regards, tom lane