Re: subselect removes rows

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:

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 <david(dot)g(dot)johnston(at)gmail(dot)com>
Verzonden: maandag 29 november 2021 19:21
Aan: Poot, Bas (B.J.)
CC: Tom Lane; pgsql-bugs(at)lists(dot)postgresql(dot)org
Onderwerp: Re: subselect removes rows

On Mon, Nov 29, 2021 at 10:09 AM Poot, Bas (B.J.) <bas(dot)poot(at)politie(dot)nl<mailto:bas(dot)poot(at)politie(dot)nl>> 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 turns the implicit join between the input row and the set returning function into a left join instead of an inner join.

This is how things work today - an empty SRF function implicitly inner joins on the rows 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 further 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 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) 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.
---------------------------------------------------------------------