Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 212ADB5DBE0 for ; Mon, 8 Aug 2011 12:35:16 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 27566-01 for ; Mon, 8 Aug 2011 15:35:09 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from smtp12.mail.ru (smtp12.mail.ru [94.100.176.89]) by mail.postgresql.org (Postfix) with ESMTP id C27FEB5DBBF for ; Mon, 8 Aug 2011 12:35:07 -0300 (ADT) DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=mail.ru; s=mail; h=Content-Type:MIME-Version:Message-ID:Date:Subject:In-Reply-To:References:Cc:To:From; bh=32polGow0XQSP1u6VxbIIiYY5o7ncUWFA8BPQmG67Uc=; b=pyfxVt01X7cE4NK0YW/l6uJ5VTqWV6Gof8/Uoo8uMEa+XnzdoZEITcMpxY0H51CTR6qFmh33nd9S337Jf5lOqxBciXckcq62NRnoN/FvtZEiMi/2y6gUVjpYg2ixXnag; Received: from [180.183.160.156] (port=28831 helo=ws) by smtp12.mail.ru with asmtp id 1QqRr4-0007Hy-00; Mon, 08 Aug 2011 19:35:03 +0400 From: "ai" To: Cc: References: <01a301cc5597$e1b5ea40$a521bec0$@mail.ru> In-Reply-To: Subject: Re: Problem with nested left-joins and coalesce Date: Mon, 8 Aug 2011 22:34:56 +0700 Message-ID: <01bd01cc55e0$bd6a3030$383e9090$@mail.ru> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01BE_01CC561B.69CD26E0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQEmoLigCF2X765KE3iHo3f14M5SFQJMDhtzlkwZJmA= Content-Language: ru X-Spam: Not detected X-Mras: Ok X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201108/24 X-Sequence-Number: 35692 This is a multipart message in MIME format. ------=_NextPart_000_01BE_01CC561B.69CD26E0 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: 7bit 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 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 ------=_NextPart_000_01BE_01CC561B.69CD26E0 Content-Type: text/html; charset="koi8-r" Content-Transfer-Encoding: quoted-printable

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 =9Aany 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” = =9A=3D))

 

My mistake =3D(( 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

(

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A SELECT 1 as = key1

) sub1

LEFT JOIN

(

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A SELECT sub3.key3, = sub4.value2 FROM

=9A = =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A(

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A SELECT 1 as key3

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A ) = sub3

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A LEFT JOIN =

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A(

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A SELECT sub5.key5, COALESCE(sub6.value1, 1) as = value2

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A FROM

=9A=9A=9A = =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A(

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A = SELECT 1 as key5

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A ) sub5

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A LEFT JOIN

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A (

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A = SELECT 1 as key6, value1

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A = FROM

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A = (

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A SELECT NULL::integer as = value1

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A ) = sub7

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A WHERE = false

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A ) sub6 ON false

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A)

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A sub4 ON = sub4.key5=3Dsub3.key3

)

sub2 ON sub1.key1 =3D 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 = =3D)

here we are:

 

SELECT * FROM

(

          =       SELECT 1 as key1

) sub1

LEFT JOIN

(

          =       SELECT sub3.key3, value2 = FROM

          =       (

          =             &= nbsp;        SELECT 1 as = key3

          =       ) sub3

          =       LEFT JOIN

          =       (

          =             &= nbsp;        SELECT sub5.key5, = COALESCE(sub6.value1, 1) as value2

          =             &= nbsp;        = FROM

          =             &= nbsp;        = (

          =             &= nbsp;           &n= bsp;            = SELECT 1 as key5

          =             &= nbsp;        ) = sub5

          =             &= nbsp;        LEFT = JOIN

          =             &= nbsp;        = (

          =             &= nbsp;           &n= bsp;            = SELECT 1 as key6, value1

          =             &= nbsp;           &n= bsp;            = FROM

          =             &= nbsp;           &n= bsp;            = (

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;    SELECT NULL::integer as = value1

          =             &= nbsp;           &n= bsp;            ) = sub7

          =             &= nbsp;           &n= bsp;            = WHERE false

          =             &= nbsp;        ) sub6 ON = false

 

          =       )

          =       sub4 ON = sub4.key5=3Dsub3.key3

)

sub2 ON sub1.key1 =3D 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 = =3D))

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

 <= /o:p>

 

------=_NextPart_000_01BE_01CC561B.69CD26E0--