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