Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>