From listar@mail.ru Mon Aug 8 06:53:40 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id D21A0B5D8C5 for ; Mon, 8 Aug 2011 03:53:40 -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 22512-02 for ; Mon, 8 Aug 2011 06:53:34 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from smtp1.mail.ru (smtp1.mail.ru [94.100.176.129]) by mail.postgresql.org (Postfix) with ESMTP id E7E48B5D800 for ; Mon, 8 Aug 2011 03:53:33 -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:To:From; bh=2G0y3P7qYO1nUdKHRVOV51maTfF2IYcUSIuPQyZkz90=; b=eFD3Oo9zilsyZ6fZDxQXVL5SPRiGgXB8aui5/6LKR0Usuc8Cls9xPFUJ4Ugs2dtBS5j75fmtV7y+Put8RfI0VVf15CLnLH+2tGZ5iOvGuh78TdQyr7Rw0tMz0y5syJ4w; Received: from [180.183.160.156] (port=27832 helo=ws) by smtp1.mail.ru with asmtp id 1QqJiM-0007Tk-00 for pgsql-sql@postgresql.org; Mon, 08 Aug 2011 10:53:31 +0400 From: "ai" To: Subject: Problem with nested left-joins and coalesce Date: Mon, 8 Aug 2011 13:53:24 +0700 Message-ID: <01a301cc5597$e1b5ea40$a521bec0$@mail.ru> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01A4_01CC55D2.8E169700" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AcxVl3SvsIAP9luNTIeeqoIxnxoedA== 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/22 X-Sequence-Number: 35690 This is a multipart message in MIME format. ------=_NextPart_000_01A4_01CC55D2.8E169700 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: 7bit 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_01A4_01CC55D2.8E169700 Content-Type: text/html; charset="koi8-r" Content-Transfer-Encoding: quoted-printable

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

(

=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, 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 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 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

 

------=_NextPart_000_01A4_01CC55D2.8E169700-- From cgourofino@hotmail.com Mon Aug 8 15:03:14 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 22823B5DC67 for ; Mon, 8 Aug 2011 12:03:14 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 55805-01-2 for ; Mon, 8 Aug 2011 15:03:07 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yw0-f46.google.com (mail-yw0-f46.google.com [209.85.213.46]) by mail.postgresql.org (Postfix) with ESMTP id 3D1EBB5DC23 for ; Mon, 8 Aug 2011 12:03:05 -0300 (ADT) Received: by ywf7 with SMTP id 7so266210ywf.19 for ; Mon, 08 Aug 2011 08:03:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:reply-to:sender:in-reply-to:references:from:date :x-google-sender-auth:message-id:subject:to:cc:content-type; bh=HTKJAzK3AebmCVU/DchdtkKEVxb70kEXLeYO9jzZqAE=; b=nxre9DllXDGJyvgHlC24qUVcAQaxqpRUEX+26/mtYrr2q26La1U+qqq0/oZNBDKGHo FjdKsFly2SA49TVK77KDzwIXUPRjypE07U3Wt3XRBQHfAqu+xCLIqngDqG/Vz8CKOLGv NFQtE+nPmLueyk2tIlz812vK+iMIuanScnEeQ= Received: by 10.142.149.13 with SMTP id w13mr5850372wfd.311.1312815783181; Mon, 08 Aug 2011 08:03:03 -0700 (PDT) MIME-Version: 1.0 Reply-To: cgourofino@hotmail.com Received: by 10.142.218.18 with HTTP; Mon, 8 Aug 2011 08:02:43 -0700 (PDT) In-Reply-To: <01a301cc5597$e1b5ea40$a521bec0$@mail.ru> References: <01a301cc5597$e1b5ea40$a521bec0$@mail.ru> From: Carla Date: Mon, 8 Aug 2011 12:02:43 -0300 X-Google-Sender-Auth: M7Qhvc5IsrmrX9Kad8vNwGuENxw Message-ID: Subject: Re: Problem with nested left-joins and coalesce To: ai Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd15224697f4404a9ffbe69 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.024 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FORGED_HOTMAIL_RCVD2=0.874, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201108/23 X-Sequence-Number: 35691 --000e0cd15224697f4404a9ffbe69 Content-Type: text/plain; charset=KOI8-R 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**** > > ** ** > --000e0cd15224697f4404a9ffbe69 Content-Type: text/html; charset=KOI8-R Content-Transfer-Encoding: quoted-printable 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&qu= ot;.
Try running "SELECT sub3.key3, sub4.value2, value2 FROM ...&q= uot; and you'll get the result:

key1;key3;value2;value2<= /p>1;1;null;1
It happens because the first column &= quot;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 postgresq= l...

It's hard to explain, but e= asy to show =3D)

here we are:

=9A

SELECT * FROM

(

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

) sub1

=

LEFT JOIN =

(

<= p class=3D"MsoNormal"> =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A SELECT s= ub3.key3, 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 SE= LECT 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 SELECT sub5.ke= y5, 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<= /p>

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

=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=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<= u>

=9A=9A=9A=9A= =9A=9A=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 (<= u>

=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=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::inte= ger 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=3Ds= ub3.key3

)

sub2 ON sub1.key1 =3D sub2.key3

=9A<= u>

The result of this query:

key1;key3;va= lue2

1;= 1;NULL

=9A

And this is the problem - value2 c= an't be NULL because of COALESCE in sub4 (at least I think that it can&= #39;t be =3D))

Anyway if we'll change <= /u>

SELECT sub= 3.key3, sub4.value2 FROM

with

SELECT sub3.key3, value2 FROM

we will = got correct result:

key1;key3;value2

1;1;1

<= p class=3D"MsoNormal">Is there something wrong with my= mind&hands? or is it a bug?

=9A

Thanks in advance!

=9A

Kind regards

Alex

=9A


--000e0cd15224697f4404a9ffbe69-- From listar@mail.ru Mon Aug 8 15:35:16 2011 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-- From cgourofino@hotmail.com Mon Aug 8 19:14:29 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 5895EB5DBF0 for ; Mon, 8 Aug 2011 16:14:29 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 75923-06 for ; Mon, 8 Aug 2011 19:14:22 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yi0-f46.google.com (mail-yi0-f46.google.com [209.85.218.46]) by mail.postgresql.org (Postfix) with ESMTP id 1922DB5DBE0 for ; Mon, 8 Aug 2011 16:14:21 -0300 (ADT) Received: by yie30 with SMTP id 30so1107498yie.19 for ; Mon, 08 Aug 2011 12:14:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:reply-to:sender:in-reply-to:references:from:date :x-google-sender-auth:message-id:subject:to:cc:content-type; bh=1Jd5l5rnFOLn9EWu8VazIN+fBgHTdznpZUcFEBakzGk=; b=CWJsiFpELvz13AQ9VCVfk0E78zzf53VMKzF7IyfPmN5PzO8az4vjzWCgNdbJFWKsCe 6lEyKoC6qfXElybzElKQtU/8ys+EfV3n/7dpkw+2niOzCGRi32xn/QBq5Z97m03W5dIp Y5Z/EosOLT0jyMHk8bAszHdM52qapf56dQmds= Received: by 10.143.96.3 with SMTP id y3mr6248716wfl.368.1312830861114; Mon, 08 Aug 2011 12:14:21 -0700 (PDT) MIME-Version: 1.0 Reply-To: cgourofino@hotmail.com Received: by 10.142.218.18 with HTTP; Mon, 8 Aug 2011 12:14:01 -0700 (PDT) In-Reply-To: <01bd01cc55e0$bd6a3030$383e9090$@mail.ru> References: <01a301cc5597$e1b5ea40$a521bec0$@mail.ru> <01bd01cc55e0$bd6a3030$383e9090$@mail.ru> From: Carla Date: Mon, 8 Aug 2011 16:14:01 -0300 X-Google-Sender-Auth: vZRZNEJ84JkGgu9DAByUtRgjnss Message-ID: Subject: Re: Problem with nested left-joins and coalesce To: ai Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd247b0207d3704aa0341de X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.024 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FORGED_HOTMAIL_RCVD2=0.874, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201108/25 X-Sequence-Number: 35693 --000e0cd247b0207d3704aa0341de Content-Type: text/plain; charset=KOI8-R 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:* 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**** > > **** > > ** ** > --000e0cd247b0207d3704aa0341de Content-Type: text/html; charset=KOI8-R Content-Transfer-Encoding: quoted-printable Sorry, my mistake. =3D( Now I understood the whole problem.

2011/8/8 ai <listar@mail.ru>

Hi Carla!=

 

Well, maybe I too simplified my production code and now I can&rs= quo;t see something very simple, BUT I’m pretty sure that there isn&r= squo;t  any original column value2 in sub4 except that I created with = COALESCE…

 

Meanwhile, I want to no= te, that I made a little mistake in presented example: instead of “wr= ong” full example I wrote a “correct” one (but I’m = sure you understood this because of my further explanation of “workar= ound”  =3D))

 

My mistake =3D(( don= 9;t kill me - I spend too much time with this piece of code today...=

 

but just in case here i= s "wrong" (difference is in that "SELECT sub3.key3, sub4.val= ue2 FROM" ):

 

SELECT * FROM

(

    &nbs= p;           SELECT 1 as = key1

) sub1

LEFT JOIN

(

   &nbs= p;            SELECT= sub3.key3, sub4.value2 FROM

           = ;     (

 &n= bsp;            = ;            &n= bsp;    SELECT 1 as key3

          &nbs= p;     ) sub3

 &= nbsp;           &nbs= p;  LEFT JOIN

          &nbs= p;     (

 &= nbsp;           &nbs= p;            &= nbsp;    SELECT sub5.key5, COALESCE(sub6.value1, 1) as value= 2

          &nbs= p;            &= nbsp;       FROM

            &n= bsp;            = ;      (

          &nbs= p;            &= nbsp;           &nbs= p;           SELECT 1 as = key5

      =             &nb= sp;            ) sub= 5

          &nbs= p;            &= nbsp;       LEFT JOIN

          &nbs= p;            &= nbsp;       (

          &nbs= p;            &= nbsp;           &nbs= p;           SELECT 1 as = key6, value1

    &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;    FROM

          &nbs= p;            &= nbsp;           &nbs= p;           (<= /u>

        &nb= sp;            =             &nb= sp;            =             &nb= sp;    SELECT NULL::integer as value1

          &nbs= p;            &= nbsp;           &nbs= p;           ) sub7

       &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp; WHERE false

          &nbs= p;            &= nbsp;       ) sub6 ON false

          &nbs= p;     )

 &= nbsp;           &nbs= p;  sub4 ON sub4.key5=3Dsub3.key3

)

sub2 ON sub1.key1 =3D sub2.k= ey3

 

best regards,<= /u>

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@p= ostgresql.org
Subject: Re: [SQL] Problem with nested left-joi= ns and coalesce

 

Hi Alex!
When you wrote "COALE= SCE(sub6.value1, 1) as value2", you created a column "valu= e2" that is different of the original column "sub4.value2".
Try running "SELECT sub3.key3, sub4.value2, value2 FROM ..." and = you'll get the result:

<= span lang=3D"EN-US">key1;key3;value2;value2

1;1;null;1
It happens because the first column &quo= t;value2" (i.e. "sub4.value2") doesn't have COALESCE on = it.

2011/8/8 ai= <listar@mail.ru= >

Hi!<= /u>

I have strange issue wit= h nested left-joins in postgresql...

It's hard to explain, but easy to show =3D)=

here we are:<= /span>

 <= /span>

SELECT * FROM<= /u>

(

     = ;           SELECT 1 as k= ey1

) sub1

=

LEFT JOIN =

(

<= p class=3D"MsoNormal">          =        SELECT sub3.key3, value2 FROM

  &nb= sp;            = (

&nbs= p;            &= nbsp;           &nbs= p;     SELECT 1 as key3

     &= nbsp;          ) sub3

  &nb= sp;            = LEFT JOIN

           &nbs= p;    (

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

     = ;            &n= bsp;            = ; FROM

     &= nbsp;           &nbs= p;             = (

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

     &= nbsp;           &nbs= p;             = ) sub5

=             &nb= sp;            =       LEFT JOIN

         =             &nb= sp;         (<= /p>

    &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;    SELECT 1 as key6, value1

         =             &nb= sp;            =              FR= OM

&nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;        (

<= p class=3D"MsoNormal">          =             &nb= sp;            =             &nb= sp;            =     SELECT NULL::integer as value1

<= p class=3D"MsoNormal">     &n= bsp;            = ;            &n= bsp;            = ;    ) sub7

     &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;    WHERE false

        &= nbsp;           &nbs= p;          ) sub6 ON false

 

=

     &= nbsp;          )=

   &n= bsp;            sub4= ON sub4.key5=3Dsub3.key3

)

sub2 ON sub1.key1 =3D sub2.key3

 

The result of this query:

key1;key3;va= lue2

1;= 1;NULL

 

=

And this is the problem - value= 2 can't be NULL because of COALESCE in sub4 (at least I think that it c= an't be =3D))

Anyway if we'll change

SELECT sub= 3.key3, sub4.value2 FROM

with

SELECT sub3.key3, value2 FROM

we will = got correct result:

key1;key3;value2

1;1;1

<= p class=3D"MsoNormal">Is there something wrong with my= mind&hands? or is it a bug?

 

Thanks in advance!

 

Kind regards

Alex

 

 


--000e0cd247b0207d3704aa0341de--