Обсуждение: subselect removes rows
selectcol1, col2, jsonb_each_text(col2)from (select 1 as col1, null::jsonb as col2union allselect 1 as col1, '{"a":"2"}'::jsonb as col2) t1
selectcol1, col2,casewhen col2 is null then nullelse jsonb_each_text(col2)endfrom (select 1 as col1, null::jsonb as col2union allselect 1 as col1, '{"a":"2"}'::jsonb as col2) t1
De informatie verzonden met dit e-mailbericht (en bijlagen) is uitsluitend bestemd voor de geadresseerde(n) en zij die van de geadresseerde(n) toestemming 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 verschoningsrecht vallen.
Indien dit e-mailbericht niet voor u bestemd is, wordt u verzocht de afzender daarover onmiddellijk te informeren en het e-mailbericht (en bijlagen) te vernietigen.
Conform het beveiligingsbeleid van de Politie wordt e-mail van en naar de politie gecontroleerd op virussen, spam en phishing en moet deze e-mail voldoen aan de voor de overheid verplichte mailbeveiligingsstandaarden die zijn vastgesteld door het Forum Standaardisatie.
Mail die niet voldoet aan het beveiligingsbeleid kan worden geblokkeerd waardoor deze de geadresseerde niet bereikt. De geadresseerde wordt hiervan niet 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 of a confidential nature and may form part of the duty of confidentiality and/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 the Police are checked for viruses, spam and phishing and this E-mail must meet the standards of the government-imposed E-mail security as set by the Standardization 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.
---------------------------------------------------------------------
"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 onpostgres 13 (which we currently 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=# 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
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 outer 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.
With kind regards,
Bas
Verzonden: maandag 29 november 2021 16:01
Aan: Poot, Bas (B.J.)
CC: pgsql-bugs@lists.postgresql.org
Onderwerp: Re: subselect removes rows
> 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 currently 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=# 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
De informatie verzonden met dit e-mailbericht (en bijlagen) is uitsluitend bestemd voor de geadresseerde(n) en zij die van de geadresseerde(n) toestemming 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 verschoningsrecht vallen.
Indien dit e-mailbericht niet voor u bestemd is, wordt u verzocht de afzender daarover onmiddellijk te informeren en het e-mailbericht (en bijlagen) te vernietigen.
Conform het beveiligingsbeleid van de Politie wordt e-mail van en naar de politie gecontroleerd op virussen, spam en phishing en moet deze e-mail voldoen aan de voor de overheid verplichte mailbeveiligingsstandaarden die zijn vastgesteld door het Forum Standaardisatie.
Mail die niet voldoet aan het beveiligingsbeleid kan worden geblokkeerd waardoor deze de geadresseerde niet bereikt. De geadresseerde wordt hiervan niet 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 of a confidential nature and may form part of the duty of confidentiality and/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 the Police are checked for viruses, spam and phishing and this E-mail must meet the standards of the government-imposed E-mail security as set by the Standardization 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.
---------------------------------------------------------------------
"Poot, Bas (B.J.)" <bas.poot@politie.nl> writes: > 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. I'm really not sure what you're saying here. If you're complaining about jsonb_each_text returning no rows for empty input, that behavior hasn't changed, and it's hard to see what else it could do. Your example isn't showing any other behavior that seems odd. regards, tom lane
"Poot, Bas (B.J.)" <bas.poot@politie.nl> writes:
> 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.
I'm really not sure what you're saying here. If you're complaining
about jsonb_each_text returning no rows for empty input, that behavior
hasn't changed, and it's hard to see what else it could do. Your
example isn't showing any other behavior that seems odd.
regards, tom lane
col1, col2, j.col2_item
from (
select 1 as col1, null::jsonb as col2
union all
select 1 as col1, '{"a":"2"}'::jsonb as col2
) t1
left join lateral
( select jsonb_each_text(t1.col2) as col2_item
) as j on true ;
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.
Thanks for the explanation.
oké 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 column.
apparently that's not correct, and I have to use (select xxx) to call it a subquery.
That also solves my problem, since:
Works as I expected.
I am sorry for the disturbance.. I guess the error was on my part...
Kind regards,
Bas
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
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.
De informatie verzonden met dit e-mailbericht (en bijlagen) is uitsluitend bestemd voor de geadresseerde(n) en zij die van de geadresseerde(n) toestemming 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 verschoningsrecht vallen.
Indien dit e-mailbericht niet voor u bestemd is, wordt u verzocht de afzender daarover onmiddellijk te informeren en het e-mailbericht (en bijlagen) te vernietigen.
Conform het beveiligingsbeleid van de Politie wordt e-mail van en naar de politie gecontroleerd op virussen, spam en phishing en moet deze e-mail voldoen aan de voor de overheid verplichte mailbeveiligingsstandaarden die zijn vastgesteld door het Forum Standaardisatie.
Mail die niet voldoet aan het beveiligingsbeleid kan worden geblokkeerd waardoor deze de geadresseerde niet bereikt. De geadresseerde wordt hiervan niet 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 of a confidential nature and may form part of the duty of confidentiality and/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 the Police are checked for viruses, spam and phishing and this E-mail must meet the standards of the government-imposed E-mail security as set by the Standardization 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.
---------------------------------------------------------------------