Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select
| От | Ed Herrmann |
|---|---|
| Тема | Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select |
| Дата | |
| Msg-id | CAOfL=zrXvNgWZX-vWpqaJCT_SGtSG0ze=4e5K4iGVe_5QW3oUQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select
|
| Список | pgsql-bugs |
I tried unsuccessfully to recreate the error with this self-contained example:
SELECT mytdata.key, mytdata.value::float::integer
FROM (VALUES('{"key_figure": "volume", "w01": 0, "w02": 3, "w03": 2.0}'::jsonb)) AS myt (weekdata),
jsonb_each_text(myt.weekdata) AS mytdata
-- WHERE mytdata.key IN ('w01', 'w02', 'w03')
WHERE mytdata.key IN (
SELECT myk.key from (VALUES('w01'), ('w02'), ('w03')) AS myk (key)
)
This example does not cause the same error and returns results as expected. If it has to do with the execution plan as Laurenz suggested, do you have a suggestion on how to better create a self-contained example where it would behave more like an actual table select to get the desired list of keys?
On Wed, Feb 21, 2024 at 3:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Wed, 2024-02-21 at 19:13 +0000, PG Bug reporting form wrote:
>> However as of v16, this same statement causes the error: 'invalid input
>> syntax for type double precision: "Volume"'.
> That's not a bug. PostgreSQL just chose a different execution plan
> that happened to evaluate the SELECT list elements before filtering out
> the rows.
I'm not sure if it's a bug or not. I think we do promise that WHERE
filtering happens before SELECT list evaluation, and this query looks
like it ought to meet that condition. But without a self-contained
example it's hard to be entirely sure what's happening.
regards, tom lane
В списке pgsql-bugs по дате отправления: