On Friday, April 10, 2020, PG Bug reporting form <
noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 16356
Logged by: Vale Violet Mote
Email address: valefbonetwo@gmail.com
PostgreSQL version: 12.2
Operating system: Win10
Description:
Live question: https://stackoverflow.com/questions/61147921
Paste of text:
But as soon as I attach the where clause, it fails:
```sql
select id, value from (
select id, value::jsonb from public.test_table natural join
public.test_types
where value_type = 'json') only_json
where only_json.value ? 'color' = true
```
```
SQL Error [22P02]: ERROR: invalid input syntax for type json
Detail: Token "a" is invalid.
Where: JSON data, line 1: a
```
It's somehow resurrected the value of 'a' that was well-eliminated prior to
this where clause. So what gives? Why does the join cause it to apply the
last where clause (which should happen logically last) too early?
PostgreSQL is trying to be helpful by re-arranging things to execute in the most efficient way possible. It cannot adapt those optimizations on the fly in response to data. Since your model has issues that make this optimization fail you need to make it so the optimization cannot be applied. Adding “offset 0” to the subquery should accomplish this.
- Moving it to a "with".
It would have in prior versions. I think 12 removed the optimization fence that used to be in place here.
The best solution is not to have column content formats vary, though I get that is not always possible.
David J.