Re: 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(at)mail(dot)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****
>
> ** **
>