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 <lis...@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**** > > ** ** >
