subselect removes rows

Поиск
Список
Период
Сортировка
От Poot, Bas (B.J.)
Тема subselect removes rows
Дата
Msg-id a5ed0b646f2e465d90954fa79d5837b5@politie.nl
обсуждение исходный текст
Ответы Re: subselect removes rows  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Дерево обсуждения

Hi there,

We recently ran into the following bug. Which is very annoying since you don't expect it to happen.

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


I haven't had the opportunity to test it on postgres 14, but I couldn't find anything about it in the release notes, so I don't expect it works any different in 14.

The workaround is obviously simple if we could use set returning functions in a case:
select 
col1, col2, 
case 
when col2 is null then null 
else jsonb_each_text(col2) 
end
from (
select 1 as col1, null::jsonb as col2
union all
select 1 as col1, '{"a":"2"}'::jsonb as col2
) t1

but we can't.. so that kinda makes it problematic..

Can you please look into it?

Thanks,
Bas Poot

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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: BUG #17303: statement_timeout does not work always
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #17303: statement_timeout does not work always