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 )


There was a typo in my original examples:

SELECT id, mytdata.key, mytdata.value::integer
should be:
SELECT id, mytdata.key, mytdata.value::float::integer

I don't think it matters for getting an error, but it would matter given the specific error about double precision.

On Wed, Feb 21, 2024 at 2:14 PM PG Bug reporting form <noreply@postgresql.org> wrote:
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



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