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: "'Kevin Grittner'" <Kevin(dot)Grittner(at)wicourts(dot)gov>,"'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #6154: wrong result with nested left-joins
Date: 2011-08-08 17:50:33
Message-ID: 01ce01cc55f3$afa6cd60$0ef46820$@mail.ru (view raw or flat)
Thread:
Lists: pgsql-bugs
Kevin and all others, thanks for your work!


-----Original Message-----
From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov] 
Sent: Tuesday, August 09, 2011 12:32 AM
To: ai; 'Tom Lane'
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: RE: [BUGS] BUG #6154: wrong result with nested left-joins

"ai" <listar(at)mail(dot)ru> wrote:
 
> Oops.. I just saw, that I send you "correct" (already with
"workaround")
> sql-code instead of "wrong"... 
 
> here is "wrong" (difference is in "SELECT sub3.key3, sub4.value2
FROM" ):
 
OK, that is still happening, and looks wrong to me.  The outermost LEFT JOIN
is between this:
 
test=# SELECT * FROM (SELECT 1 as key1) sub1;
 key1
------
    1
(1 row)
 
as sub1 and this:
 
test=#       SELECT sub3.key3, sub4.value2
test-#         FROM (SELECT 1 as key3) sub3
test-#         LEFT JOIN
test-#           (
test(#             SELECT sub5.key5, COALESCE(sub6.value1, 1) as
value2
test(#               FROM (SELECT 1 as key5) sub5
test(#               LEFT JOIN
test(#                 (
test(#                   SELECT 1 as key6, value1
test(#                     FROM (SELECT NULL::integer as value1) sub7
test(#                     WHERE false
test(#                 ) sub6 ON false
test(#           ) sub4 ON sub4.key5=sub3.key3;
 key3 | value2
------+--------
    1 |      1
(1 row)
 
as sub2 joining on sub1.key1 = sub2.key3.  Yet:
 
test=# SELECT *
test-#   FROM (SELECT 1 as key1) sub1
test-#   LEFT JOIN
test-#     (
test(#       SELECT sub3.key3, sub4.value2
test(#         FROM (SELECT 1 as key3) sub3
test(#         LEFT JOIN
test(#           (
test(#             SELECT sub5.key5, COALESCE(sub6.value1, 1) as
value2
test(#               FROM (SELECT 1 as key5) sub5
test(#               LEFT JOIN
test(#                 (
test(#                   SELECT 1 as key6, value1
test(#                     FROM (SELECT NULL::integer as value1) sub7
test(#                     WHERE false
test(#                 ) sub6 ON false
test(#           ) sub4 ON sub4.key5=sub3.key3
test(#     ) sub2 ON sub1.key1 = sub2.key3;
 key1 | key3 | value2
------+------+--------
    1 |    1 |
(1 row)
 
This is on HEAD from today.  Clearly there's a problem.
 
-Kevin


In response to

pgsql-bugs by date

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

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