Thread: Problem with nested left-joins and coalesce
<div class="WordSection1"><p class="MsoNormal"><span lang="EN-US">Hi!</span><p class="MsoNormal"><span lang="EN-US">I havestrange issue with nested left-joins in postgresql...</span><p class="MsoNormal"><span lang="EN-US">It's hard to explain,but easy to show =)</span><p class="MsoNormal"><span lang="EN-US">here we are:</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">SELECT * FROM</span><p class="MsoNormal"><span lang="EN-US">(</span><pclass="MsoNormal"><span lang="EN-US"> SELECT 1 as key1</span><p class="MsoNormal"><spanlang="EN-US">) sub1</span><p class="MsoNormal"><span lang="EN-US">LEFT JOIN </span><p class="MsoNormal"><spanlang="EN-US">(</span><p class="MsoNormal"><span lang="EN-US"> SELECT sub3.key3, value2FROM</span><p class="MsoNormal"><span lang="EN-US"> (</span><p class="MsoNormal"><span lang="EN-US"> SELECT 1 as key3</span><p class="MsoNormal"><span lang="EN-US"> ) sub3</span><p class="MsoNormal"><span lang="EN-US"> LEFT JOIN </span><p class="MsoNormal"><spanlang="EN-US"> (</span><p class="MsoNormal"><span lang="EN-US"> SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2</span><p class="MsoNormal"><spanlang="EN-US"> FROM</span><p class="MsoNormal"><span lang="EN-US"> (</span><p class="MsoNormal"><span lang="EN-US"> SELECT 1 as key5</span><p class="MsoNormal"><span lang="EN-US"> ) sub5</span><p class="MsoNormal"><span lang="EN-US"> LEFT JOIN</span><p class="MsoNormal"><span lang="EN-US"> (</span><p class="MsoNormal"><span lang="EN-US"> SELECT 1 as key6, value1</span><p class="MsoNormal"><span lang="EN-US"> FROM</span><p class="MsoNormal"><span lang="EN-US"> (</span><p class="MsoNormal"><span lang="EN-US"> SELECT NULL::integer as value1</span><p class="MsoNormal"><spanlang="EN-US"> ) sub7</span><p class="MsoNormal"><spanlang="EN-US"> WHERE false</span><p class="MsoNormal"><spanlang="EN-US"> ) sub6 ON false</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US"> )</span><p class="MsoNormal"><span lang="EN-US"> sub4 ON sub4.key5=sub3.key3</span><p class="MsoNormal"><span lang="EN-US">)</span><p class="MsoNormal"><spanlang="EN-US">sub2 ON sub1.key1 = sub2.key3</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">The result of this query:</span><p class="MsoNormal"><span lang="EN-US">key1;key3;value2</span><pclass="MsoNormal"><span lang="EN-US">1;1;NULL</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">And this is the problem - value2 can't be NULL because of COALESCEin sub4 (at least I think that it can't be =))</span><p class="MsoNormal"><span lang="EN-US">Anyway if we'll change</span><p class="MsoNormal"><span lang="EN-US">SELECT sub3.key3, sub4.value2 FROM</span><p class="MsoNormal"><spanlang="EN-US">with</span><p class="MsoNormal"><span lang="EN-US">SELECT sub3.key3, value2 FROM</span><pclass="MsoNormal"><span lang="EN-US">we will got correct result:</span><p class="MsoNormal"><span lang="EN-US">key1;key3;value2</span><pclass="MsoNormal"><span lang="EN-US">1;1;1</span><p class="MsoNormal"><span lang="EN-US">Isthere something wrong with my mind&hands? or is it a bug?</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">Thanks in advance!</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;color:#1F497D;mso-fareast-language:RU">Kindregards</span><p class="MsoNormal"><span lang="EN-US"style="font-size:10.0pt;color:#1F497D;mso-fareast-language:RU">Alex</span><p class="MsoNormal"> </div>
When you wrote "COALESCE(sub6.value1, 1) as value2", you created a column "value2" that is different of the original column "sub4.value2".
Try running "SELECT sub3.key3, sub4.value2, value2 FROM ..." and you'll get the result:
key1;key3;value2;value21;1;null;1
It happens because the first column "value2" (i.e. "sub4.value2") doesn't have COALESCE on it.
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
Hi Carla!
Well, maybe I too simplified my production code and now I can’t see something very simple, BUT I’m pretty sure that there isn’t any original column value2 in sub4 except that I created with COALESCE…
Meanwhile, I want to note, that I made a little mistake in presented example: instead of “wrong” full example I wrote a “correct” one (but I’m sure you understood this because of my further explanation of “workaround” =))
My mistake =(( don't kill me - I spend too much time with this piece of code today...
but just in case here is "wrong" (difference is in that "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
best regards,
alex
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Carla
Sent: Monday, August 08, 2011 10:03 PM
To: ai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Problem with nested left-joins and coalesce
Hi Alex!
When you wrote "COALESCE(sub6.value1, 1) as value2", you created a column "value2" that is different of the original column "sub4.value2".
Try running "SELECT sub3.key3, sub4.value2, value2 FROM ..." and you'll get the result:
key1;key3;value2;value2
1;1;null;1
It happens because the first column "value2" (i.e. "sub4.value2") doesn't have COALESCE on it.
2011/8/8 ai <listar@mail.ru>
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
Hi Carla!
Well, maybe I too simplified my production code and now I can’t see something very simple, BUT I’m pretty sure that there isn’t any original column value2 in sub4 except that I created with COALESCE…
Meanwhile, I want to note, that I made a little mistake in presented example: instead of “wrong” full example I wrote a “correct” one (but I’m sure you understood this because of my further explanation of “workaround” =))
My mistake =(( don't kill me - I spend too much time with this piece of code today...
but just in case here is "wrong" (difference is in that "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
best regards,
alex
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Carla
Sent: Monday, August 08, 2011 10:03 PM
To: ai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Problem with nested left-joins and coalesce
Hi Alex!
When you wrote "COALESCE(sub6.value1, 1) as value2", you created a column "value2" that is different of the original column "sub4.value2".
Try running "SELECT sub3.key3, sub4.value2, value2 FROM ..." and you'll get the result:key1;key3;value2;value2
1;1;null;1
It happens because the first column "value2" (i.e. "sub4.value2") doesn't have COALESCE on it.2011/8/8 ai <listar@mail.ru>
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