From: ai <listar@mail.ru>
To: pgsql-sql@postgresql.org
Subject: Problem with nested left-joins and coalesce
Date: Mon, 8 Aug 2011 13:53:24 +0700
Message-ID: <01a301cc5597$e1b5ea40$a521bec0$@mail.ru> (raw)
Hi!
I have strange issue with nested left-joins in postgresql...
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
Is there something wrong with my mind&hands? or is it a bug?
Thanks in advance!
Kind regards
Alex
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: listar@mail.ru
Subject: Re: Problem with nested left-joins and coalesce
In-Reply-To: <01a301cc5597$e1b5ea40$a521bec0$@mail.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox