Re: BUG #16356: Can't perform json operations on casted text in where clause when a join is used. WHERE is too early

On Friday, April 10, 2020, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16356
> Logged by: Vale Violet Mote
> Email address: valefbonetwo(at)gmail(dot)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.