Sorry, my mistake. =( Now I understood the whole problem. 2011/8/8 ai <lis...@mail.ru>
> 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-ow...@postgresql.org [mailto: > pgsql-sql-ow...@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 <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**** > > **** > > ** ** >
