From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | ewherrmann(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select |
Date: | 2024-02-21 20:23:53 |
Message-ID: | 2df3715aa60088b5d781c6b4b1dc1ec5ab62669b.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Wed, 2024-02-21 at 19:13 +0000, PG Bug reporting form wrote:
> - Prior to v16, this statement would work for casting values of a k/v jsonb
> pair to an integer:
> SELECT id, mytdata.key, mytdata.value::integer
> FROM my_table as myt,
> jsonb_each_text(myt.data) as mytdata
> WHERE mytdata.key IN ( SELECT key from week_key_table
> )
>
> - Where week_key_table stores keys such as "week01", "week02", and
> "week03".
> - And where the jsonb has some keys with alphanumeric values and some keys
> with numeric values, such as: { "key_figure": "Volume", "week01": "0",
> "week02": "0", "week03": "0"}
>
> 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.
Try forcing the optimizer's hand with something like
WITH elems AS MATERIALIZED (
SELECT myt.id, mytdata.key, mytdata.value
FROM my_table AS myt,
jsonb_each_text(myt.data) AS mytdata
WHERE mytdata.key IN (SELECT key FROM week_key_table)
)
SELECT id, key, value::integer
FROM elems;
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-02-21 20:34:48 | Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select |
Previous Message | Tom Lane | 2024-02-21 20:17:58 | Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select |