Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mrz4k-00024k-GT for pgsql-bugs@arkaria.postgresql.org; Tue, 30 Nov 2021 09:05:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mrz4j-0003Pl-8u for pgsql-bugs@arkaria.postgresql.org; Tue, 30 Nov 2021 09:05:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mrmhW-0004Ew-Ll for pgsql-bugs@lists.postgresql.org; Mon, 29 Nov 2021 19:52:34 +0000 Received: from mail1.politie.nl ([2a04:9a00:1200:a610::13]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mrmhO-0002hU-Vi for pgsql-bugs@lists.postgresql.org; Mon, 29 Nov 2021 19:52:33 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=politie.nl; i=@politie.nl; q=dns/txt; s=20170705; t=1638215546; x=1669751546; h=from:to:cc:subject:date:message-id:references: in-reply-to:mime-version; bh=wPJIbvefXF1Mp1wv157nXYq9/MwiUvU5KOsVwKVdjHE=; b=AOjzwTzFDV924jTbFVOOYer5eD3/oZhbkbwCxiKLZSBWzxxCUjF5tYss 3Oru5H4GVwZhrBAQdhdxsfVuWTOfThSxrQ61CY32RbSsI7ZbmwdX/IN0f laUgoBCZah4ps3ysZe+OUGbrM7zVsAPRVUKENeRovepC3PXvEcVno+YGO 4=; IronPort-SDR: u+8i+m3GyLPRF5tVrCpuHSAVPGjX4narjza5kl5RZfEGwzFUep2+2qSVtmpWUx5BZreWX4Yg+P iOzEhIW4LEbQ== From: "Poot, Bas (B.J.)" To: "David G. Johnston" CC: Tom Lane , "pgsql-bugs@lists.postgresql.org" Subject: Re: subselect removes rows Thread-Topic: subselect removes rows Thread-Index: AQHX5SmzrYtFt583wEq6z1FltpkbyKwaiOqAgAAb+MqAABvkgIAAJajE Date: Mon, 29 Nov 2021 19:52:24 +0000 Message-ID: <15736efd9ae84d9ebbef14e3e4e8e844@politie.nl> References: <1217100.1638198095@sss.pgh.pa.us> <57ceecb9c50743eeaf8732fcf2185d68@politie.nl>, In-Reply-To: Accept-Language: nl-NL, en-US Content-Language: nl-NL X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.0.72.117] Content-Type: multipart/alternative; boundary="_000_15736efd9ae84d9ebbef14e3e4e8e844politienl_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_15736efd9ae84d9ebbef14e3e4e8e844politienl_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks for the explanation. ok=E9 I read your message 3 times and i think we're lost in translation. What I call a subquery is anything in the 'select' part that is not a colum= n. apparently that's not correct, and I have to use (select xxx) to call it a = subquery. That also solves my problem, since: select col1, col2, (select jsonb_each_text(col2)) from ( select 1 as col1, null::jsonb as col2 union all select 1 as col1, '{}'::jsonb as col2 union all select 1 as col1, '{"a":"2"}'::jsonb as col2 ) t1 Works as I expected. I am sorry for the disturbance.. I guess the error was on my part... Kind regards, Bas ________________________________ Van: David G. Johnston Verzonden: maandag 29 november 2021 19:21 Aan: Poot, Bas (B.J.) CC: Tom Lane; pgsql-bugs@lists.postgresql.org Onderwerp: Re: subselect removes rows On Mon, Nov 29, 2021 at 10:09 AM Poot, Bas (B.J.) > wrote: The problem is that I don't expect (and can't imagine it is correct) that a= (any) sub-select removes rows from the resultset. Ignoring your specific example for the moment your basic complaint seems to= be that: select 1, unnest(array[]::integer[]); --zero rows select 1, (select unnest(array[]::integer[])); -- one row, second column is= null This is actually the reverse of what you are saying though - the subselect = actually allows the row to be returned, not the opposite. In short, it tur= ns the implicit join between the input row and the set returning function i= nto a left join instead of an inner join. This is how things work today - an empty SRF function implicitly inner join= s on the rows of the main query and so, like any inner join, will remove ro= ws from the output if there are no records on the SRF side of the join. Others, or the mailing list archives where this has come up many times, can= provide further insight into the why. I choose not to remember such detai= ls here and just accept it as the behavior. With the addition of lateral j= oins the cases where you have an SRF in the select-list should tend toward = zero anyway (and then you get to be explicit as to inner or outer join). David J. ------------------------- Disclaimer ---------------------------- De informatie verzonden met dit e-mailbericht (en bijlagen) is uitsluitend = bestemd voor de geadresseerde(n) en zij die van de geadresseerde(n) toestem= ming kregen dit bericht te lezen. Kennisneming door anderen is niet toegestaan. De informatie in dit e-mailbericht (en bijlagen) kan vertrouwelijk van aard= zijn en binnen het bereik van een geheimhoudingsplicht en/of een verschoni= ngsrecht vallen. Indien dit e-mailbericht niet voor u bestemd is, wordt u verzocht de afzend= er daarover onmiddellijk te informeren en het e-mailbericht (en bijlagen) t= e vernietigen. Conform het beveiligingsbeleid van de Politie wordt e-mail van en naar de p= olitie gecontroleerd op virussen, spam en phishing en moet deze e-mail vold= oen aan de voor de overheid verplichte mailbeveiligingsstandaarden die zijn= vastgesteld door het Forum Standaardisatie. Mail die niet voldoet aan het beveiligingsbeleid kan worden geblokkeerd waa= rdoor deze de geadresseerde niet bereikt. De geadresseerde wordt hiervan ni= et in kennis gesteld. --------------------------------------------------------------------- The information sent in this E-mail message (including any attachments) is = exclusively intended for the individual(s) to whom it is addressed and for = the individual(s) who has/have had permission from the recipient(s) to read= this message. Access by others is not permitted. The information in this E-mail message (including any attachments) may be o= f a confidential nature and may form part of the duty of confidentiality an= d/or the right of non-disclosure. If you have received this E-mail message in error, please notify the sender= without delay and delete the E-mail message (including any attachments). In conformity with the security policy of the Police, E-mails from and to t= he Police are checked for viruses, spam and phishing and this E-mail must m= eet the standards of the government-imposed E-mail security as set by the S= tandardization Forum. Any E-mail failing to meet said security policy may be blocked as a result = of which it will not reach the intended recipient. The recipient concerned = will not be notified. --------------------------------------------------------------------- --_000_15736efd9ae84d9ebbef14e3e4e8e844politienl_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Thanks for the explanation.


ok=E9 I read your message 3 times and i think we're lost in translation.=


What I call a subquery is anything in the 'select' part that is not a co= lumn.

apparently that's not correct, and I have to use (select xxx) to call it= a subquery.


That also solves my problem, since:


select 
col1, col2, (select jsonb_each_= text(col2)) 
from (
select 1 as col1, null::jsonb a= s col2
union all
select 1 as col1, '{}'::jsonb a= s col2
union all
select 1 as col1, '{"a&quo= t;:"2"}'::jsonb as col2
) t1

Works as I expected.


I am sorry for the disturbance..= I guess the error was on my part...


Kind regards,

Bas



Van: David G. Johnston <= david.g.johnston@gmail.com>
Verzonden: maandag 29 november 2021 19:21
Aan: Poot, Bas (B.J.)
CC: Tom Lane; pgsql-bugs@lists.postgresql.org
Onderwerp: Re: subselect removes rows
 
On Mon, Nov 29, 2= 021 at 10:09 AM Poot, Bas (B.J.) <bas.poot@politie.nl> wrote:

The problem is that I don't expect (and can't imagine it is correct) tha= t a (any) sub-select removes rows from the resultset.


Ignoring your specific example for the moment your basic complaint seems= to be that:

select 1, unnest(= array[]::integer[]);   --zero rows
select 1, (select unnest(array[]::integer[])); -- one row, second colum= n is null

This is actually the reverse of what you are saying though - the s= ubselect actually allows the row to be returned, not the opposite.  In= short, it turns the implicit join between the input row and the set returning function into a left join instead of a= n inner join.

This is how= things work today - an empty SRF function implicitly inner joins on the ro= ws of the main query and so, like any inner join, will remove rows = from the output if there are no records on the SRF side of the join.=

Others, or = the mailing list archives where this has come up many times, can provide fu= rther insight into the why.  I choose not to remember such details here and just accept it as the behavior. = ; With the addition of lateral joins the cases where you have an SRF in the= select-list should tend toward zero anyway (and then you get to be explici= t as to inner or outer join).

David J.

------------------------- Disclaimer ----------------------------
De informatie verzonden met dit e-mailbericht (en bijlagen) is uitsluitend = bestemd voor de geadresseerde(n) en zij die van de geadresseerde(n) toestem= ming kregen dit bericht te lezen.
Kennisneming door anderen is niet toegestaan.
De informatie in dit e-mailbericht (en bijlagen) kan vertrouwelijk van aard= zijn en binnen het bereik van een geheimhoudingsplicht en/of een verschoni= ngsrecht vallen.
Indien dit e-mailbericht niet voor u bestemd is, wordt u verzocht de afzend= er daarover onmiddellijk te informeren en het e-mailbericht (en bijlagen) t= e vernietigen.
Conform het beveiligingsbeleid van de Politie wordt e-mail van en naar de p= olitie gecontroleerd op virussen, spam en phishing en moet deze e-mail vold= oen aan de voor de overheid verplichte mailbeveiligingsstandaarden die zijn= vastgesteld door het Forum Standaardisatie.
Mail die niet voldoet aan het beveiligingsbeleid kan worden geblokkeerd waa= rdoor deze de geadresseerde niet bereikt. De geadresseerde wordt hiervan ni= et in kennis gesteld.
---------------------------------------------------------------------
The information sent in this E-mail message (including any attachments) is = exclusively intended for the individual(s) to whom it is addressed and for = the individual(s) who has/have had permission from the recipient(s) to read= this message.
Access by others is not permitted.
The information in this E-mail message (including any attachments) may be o= f a confidential nature and may form part of the duty of confidentiality an= d/or the right of non-disclosure.
If you have received this E-mail message in error, please notify the sender= without delay and delete the E-mail message (including any attachments). In conformity with the security policy of the Police, E-mails from and to t= he Police are checked for viruses, spam and phishing and this E-mail must m= eet the standards of the government-imposed E-mail security as set by the S= tandardization Forum.
Any E-mail failing to meet said security policy may be blocked as a result = of which it will not reach the intended recipient. The recipient concerned = will not be notified.
---------------------------------------------------------------------
--_000_15736efd9ae84d9ebbef14e3e4e8e844politienl_--