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 1mrk9h-00043e-UP for pgsql-bugs@arkaria.postgresql.org; Mon, 29 Nov 2021 17:09:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mrk9g-0005NR-Nf for pgsql-bugs@arkaria.postgresql.org; Mon, 29 Nov 2021 17:09:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mripz-0004RY-Po for pgsql-bugs@lists.postgresql.org; Mon, 29 Nov 2021 15:45:04 +0000 Received: from mail2.politie.nl ([2a04:9a00:1200:a610::14]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mripw-00052I-3J for pgsql-bugs@lists.postgresql.org; Mon, 29 Nov 2021 15:45:02 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=politie.nl; i=@politie.nl; q=dns/txt; s=20170705; t=1638200700; x=1669736700; h=from:to:cc:subject:date:message-id:references: in-reply-to:mime-version; bh=8HVOdqpGIFQFM7It4Ido+PO6ZMw3WWHEGRwEzz5GjTg=; b=qNZXdtUjRhUoLXFPXiCYmRKDFXCJLGiiKhruppGNNQjwcBJ8OieOuCu7 cbyweNpvCQ9ZKkvoNwU87c9S2AOE2k+ItP1BkRJYFJkGWZK2+BIk76eVs mwVs3bgKsKcpLwWBp5GgPmuO7Gy/ysPFlTmB+okJZH9Qp6Wi7RnDodUqW I=; IronPort-SDR: YCgbsXYEXj8yprVKCIRLCDev6oUaRoo36l7e1Oms6NvV2LC0dg0lOTXFY0uCsYatYZ9DzA6ito qOSJHUJ3JBmQ== From: "Poot, Bas (B.J.)" To: Tom Lane CC: "pgsql-bugs@lists.postgresql.org" Subject: Re: subselect removes rows Thread-Topic: subselect removes rows Thread-Index: AQHX5SmzrYtFt583wEq6z1FltpkbyKwaiOqAgAAb+Mo= Date: Mon, 29 Nov 2021 15:44:54 +0000 Message-ID: <57ceecb9c50743eeaf8732fcf2185d68@politie.nl> References: ,<1217100.1638198095@sss.pgh.pa.us> In-Reply-To: <1217100.1638198095@sss.pgh.pa.us> 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_57ceecb9c50743eeaf8732fcf2185d68politienl_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_57ceecb9c50743eeaf8732fcf2185d68politienl_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi there, Thanks for looking in to it. 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. my table (t1 in my example) has 3 rows. I don't use any where clauses or inner joins, so I expect 3 rows in my oute= r query. An other argument is, that I have 2 columns in my table (col1 and col2) My action on col2 (in this case using jsonb_each_text) should not have ANY = effect on my col1, yet it has. By the way, I also noticed that it happens with empty json objects. select col1, col2, 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 With kind regards, Bas ________________________________ Van: Tom Lane Verzonden: maandag 29 november 2021 16:01 Aan: Poot, Bas (B.J.) CC: pgsql-bugs@lists.postgresql.org Onderwerp: Re: subselect removes rows "Poot, Bas (B.J.)" writes: > The bug is as follows: > When using certain commands in a sub-select, it removes the row. > This should never happen. It should return null instead. It worked in pos= tgres 10, but somehow doesn't work anymore on postgres 13 (which we current= ly have) > Example query: > select > col1, col2, jsonb_each_text(col2) > from ( > select 1 as col1, null::jsonb as col2 > union all > select 1 as col1, '{"a":"2"}'::jsonb as col2 > ) t1 Your claim is quite unclear ... but AFAICS, that query produces exactly the same results in v10 as in later versions. regression=3D# select col1, col2, jsonb_each_text(col2) from ( select 1 as col1, null::jsonb as col2 union all select 1 as col1, '{"a":"2"}'::jsonb as col2 ) t1 ; col1 | col2 | jsonb_each_text ------+------------+----------------- 1 | {"a": "2"} | (a,2) (1 row) regards, tom lane ------------------------- 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_57ceecb9c50743eeaf8732fcf2185d68politienl_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi there,


Thanks for looking in to it.


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.


my table (t1 in my example) has 3 rows.

I don't use any where clauses or inner joins, so I expect 3 rows in my o= uter query.


An other argument is, that I have 2 columns in my table (col1 and col2)<= /p>

My action on col2 (in this case using jsonb_each_text) should not have A= NY effect on my col1, yet it has.


By the way, I also noticed that it= happens with empty json objects.


select 
col1, col2, jsonb_each_text(col= 2) 
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

With kind regards,

Bas





Van: Tom Lane <tgl@sss= .pgh.pa.us>
Verzonden: maandag 29 november 2021 16:01
Aan: Poot, Bas (B.J.)
CC: pgsql-bugs@lists.postgresql.org
Onderwerp: Re: subselect removes rows
 
"Poot, Bas (B.J.)" <bas.poot@politie.= nl> writes:
> The bug is as follows:
> When using certain commands in a sub-select, it removes the row.
> This should never happen. It should return null instead. It worked in = postgres 10, but somehow doesn't work anymore on postgres 13 (which we curr= ently have)

> Example query:

> select
> col1, col2, jsonb_each_text(col2)
> from (
> select 1 as col1, null::jsonb as col2
> union all
> select 1 as col1, '{"a":"2"}'::jsonb as col2
> ) t1

Your claim is quite unclear ... but AFAICS, that query produces exactly
the same results in v10 as in later versions.

regression=3D# select
col1, col2, jsonb_each_text(col2)
from (
select 1 as col1, null::jsonb as col2
union all
select 1 as col1, '{"a":"2"}'::jsonb as col2
) t1
;
 col1 |    col2    | jsonb_each_text ------+------------+-----------------
    1 | {"a": "2"} | (a,2)
(1 row)

            &nb= sp;           regards, to= m lane
------------------------- 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_57ceecb9c50743eeaf8732fcf2185d68politienl_--