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

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "ai" <listar(at)mail(dot)ru>,"'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:31:44
Message-ID: 4E3FD730020000250003FC3D@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message ai 2011-08-08 17:50:33 Re: BUG #6154: wrong result with nested left-joins
Previous Message ai 2011-08-08 15:31:30 Re: BUG #6154: wrong result with nested left-joins