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:t...@sss.pgh.pa.us] 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 "Kevin Grittner" <kevin.gritt...@wicourts.gov> writes: > "listar" <lis...@mail.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs