Skip site navigation (1) Skip section navigation (2)

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

From: "ai" <listar(at)mail(dot)ru>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,"'Kevin Grittner'" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #6154: wrong result with nested left-joins
Date: 2011-08-08 15:31:30
Message-ID: 01bc01cc55e0$4592ddf0$d0b899d0$ (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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

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 1 as key1
) sub1
                SELECT sub3.key3, sub4.value2 FROM
                               SELECT 1 as key3
                ) sub3
                LEFT JOIN 
                               SELECT sub5.key5, COALESCE(sub6.value1, 1) as
                                               SELECT 1 as key5
                               ) sub5
                               LEFT JOIN
                                               SELECT 1 as key6, value1
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.

			regards, tom lane

In response to


pgsql-bugs by date

Next:From: Kevin GrittnerDate: 2011-08-08 17:31:44
Subject: Re: BUG #6154: wrong result with nested left-joins
Previous:From: Tom LaneDate: 2011-08-08 15:08:07

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group