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 > 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 > > ** ** >
Re: [SQL] Problem with nested left-joins and coalesce
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: [email protected] [mailto:[email protected]] On Behalf Of Carla Sent: Monday, August 08, 2011 10:03 PM To: ai Cc: [email protected] 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 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
Re: [SQL] Problem with nested left-joins and coalesce
Sorry, my mistake. =( Now I understood the whole problem. 2011/8/8 ai > 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:* [email protected] [mailto: > [email protected]] *On Behalf Of *Carla > *Sent:* Monday, August 08, 2011 10:03 PM > *To:* ai > *Cc:* [email protected] > *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 > > 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
