Обсуждение: BUG #6154: wrong result with nested left-joins
The following bug has been logged online:
Bug reference: 6154
Logged by: listar
Email address: listar@mail.ru
PostgreSQL version: 8.4.5
Operating system: Linux 2.6.36-gentoo-r5
Description: wrong result with nested left-joins
Details:
It's hard to explain, but easy to show =)
here we are:
SELECT * FROM
(
SELECT 1 as key1
) sub1
LEFT JOIN
(
SELECT sub3.key3, 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
The result of this query:
key1;key3;value2
1;1;NULL
And this is the problem - value2 can't be NULL because of COALESCE in sub4
(at least I think that it can't be =))
Anyway if we'll change
SELECT sub3.key3, sub4.value2 FROM
with
SELECT sub3.key3, value2 FROM
we will got correct result:
key1;key3;value2
1;1;1
"listar" <listar@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. http://www.postgresql.org/support/versioning -Kevin
Kevin, thanks a lot for your answer! As my servers are production systems and as I have an acceptable workaround for my needs, I will wait around with a postgresql upgrade =) I just tried to help with discovering of a potential bug =)) Anyway I'm very glad to know, that in new version it's not an issue already =) Kind regards Alexey Isaiko Television department consultant All-Russian Research Institute of TV and Radio Broadcasting Russian Media Group TV-channels: RUTV, Hip Hop Hit -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Monday, August 08, 2011 8:52 PM To: listar; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #6154: wrong result with nested left-joins "listar" <listar@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. http://www.postgresql.org/support/versioning -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > "listar" <listar@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=dc9cc887b74bfa0d40829c4df66dead509fdd8f6 regards, tom lane
SELECT VERSION(); produces "PostgreSQL 8.4.5 on x86_64-pc-linux-gnu, compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.4.4-r2 p1.3, pie-0.4.5) 4.4.4, 64-bit" Best regards, Alex -----Original Message----- From: Tom Lane [mailto:tgl@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.Grittner@wicourts.gov> writes: > "listar" <listar@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
Oops.. I just saw, that I send you "correct" (already with "workaround")
sql-code instead of "wrong"...=20
my mistake =3D(( don't kill me - I spend too much time with this piece of c=
ode
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=20
(
SELECT sub3.key3, sub4.value2 FROM
(
SELECT 1 as key3
) sub3
LEFT JOIN=20
(
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=3Dsub3.key3
)
sub2 ON sub1.key1 =3D sub2.key3
=E1.=E9.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
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=20
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> "listar" <listar@mail.ru> wrote:
>> PostgreSQL version: 8.4.5
>> Description: wrong result with nested left-joins
=20
>> And this is the problem - value2 can't be NULL because of COALESCE in=20
>> sub4 (at least I think that it can't be =3D))
=20
> This works correctly in release 9.0.4 and development HEAD. I don't=20
> still have any machines handy which are running 8.4, but you might=20
> 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=3Dpostgresql.git&a=3Dcommitdiff&h=3Ddc9=
cc887b7
4bfa0d40829c4df66dead509fdd8f6
regards, tom lane
"ai" <listar@mail.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
Kevin and all others, thanks for your work!
-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Tuesday, August 09, 2011 12:32 AM
To: ai; 'Tom Lane'
Cc: pgsql-bugs@postgresql.org
Subject: RE: [BUGS] BUG #6154: wrong result with nested left-joins
"ai" <listar@mail.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
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> "ai" <listar@mail.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.
Yeah, I see it too. It seems to be evaluating the placeholder for the
COALESCE expression at the wrong join level. Not sure why, yet.
regards, tom lane
I wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> "ai" <listar@mail.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.
> Yeah, I see it too. It seems to be evaluating the placeholder for the
> COALESCE expression at the wrong join level. Not sure why, yet.
Found it --- the code only fails for the case where the COALESCE
subexpression is referenced in the upper targetlist, but not the quals
(ie, not ON or WHERE). Patch committed, thanks for the report!
regards, tom lane
Thank you! =)
Best Regards, Alex
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, August 09, 2011 11:53 AM
To: ai
Cc: Kevin Grittner; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #6154: wrong result with nested left-joins
I wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> "ai" <listar@mail.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.
> Yeah, I see it too. It seems to be evaluating the placeholder for the
> COALESCE expression at the wrong join level. Not sure why, yet.
Found it --- the code only fails for the case where the COALESCE
subexpression is referenced in the upper targetlist, but not the quals (ie,
not ON or WHERE). Patch committed, thanks for the report!
regards, tom lane