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

The following bug has been logged on the website:

Bug reference:      18356
Logged by:          Ed Herrmann
Email address:      ewherrmann@gmail.com
PostgreSQL version: 16.0
Operating system:   Linux, MacOS
Description:

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

If the keys are specified directly, it will still work as normal, but thows
the casting error when the keys are being selected from another table.

Works:
                    SELECT id, mytdata.key, mytdata.value::integer
                    FROM my_table as myt,
                        jsonb_each_text(myt.data) as mytdata
                    WHERE mytdata.key IN ( "week01", "week02", "week03" )
Casting Error:
                    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 )

PG Bug reporting form <noreply@postgresql.org> writes:
> - 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"'. 

Could you provide an actually self-contained example?  People
have other things to do than guess about the data and table details
needed to reproduce this.

            regards, tom lane

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

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

Ed Herrmann <ewherrmann@gmail.com> writes:
> 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?

Do an EXPLAIN on the problem query, and then try to make an example
with no private data that shows the same plan.

            regards, tom lane