pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select
7+ messages / 4 participants
[nested] [flat]
* BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select
@ 2024-02-21 19:13 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 3 replies; 7+ messages in thread
From: PG Bug reporting form @ 2024-02-21 19:13 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: ewherrmann@gmail.com
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 )
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select
@ 2024-02-21 19:19 Ed Herrmann <ewherrmann@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 0 replies; 7+ messages in thread
From: Ed Herrmann @ 2024-02-21 19:19 UTC (permalink / raw)
To: ewherrmann@gmail.com; pgsql-bugs@lists.postgresql.org
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 )
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select
@ 2024-02-21 20:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2024-02-21 20:17 UTC (permalink / raw)
To: ewherrmann@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select
@ 2024-02-21 20:23 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2024-02-21 20:23 UTC (permalink / raw)
To: ewherrmann@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select
@ 2024-02-21 20:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2024-02-21 20:34 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: ewherrmann@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select
@ 2024-02-21 20:51 Ed Herrmann <ewherrmann@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Ed Herrmann @ 2024-02-21 20:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select
@ 2024-02-21 20:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ed Herrmann <ewherrmann@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2024-02-21 20:56 UTC (permalink / raw)
To: Ed Herrmann <ewherrmann@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-02-21 20:56 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-21 19:13 BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select PG Bug reporting form <noreply@postgresql.org>
2024-02-21 19:19 ` Ed Herrmann <ewherrmann@gmail.com>
2024-02-21 20:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-21 20:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-02-21 20:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-21 20:51 ` Ed Herrmann <ewherrmann@gmail.com>
2024-02-21 20:56 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox