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$@mail.ru (view raw or flat)
Thread:
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
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


In response to

Responses

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
Subject: Re: Help-PGRES_FATAL_ERROR

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