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

This message has been hidden because the message violated policies.

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> 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?

The documentation explicitly disclaims any specific evaluation order for
WHERE clauses, cf

/docs/current/sql-expressions.html#SYNTAX-EXPRESS-EVAL

If you really need to, you can force the matter by putting some type of
optimization fence into the sub-select ("OFFSET 0" is the traditional
way, or you can use WITH ... AS MATERIALIZED in v12 and up). This is
typically disastrous for performance, of course. Not being able to
filter rows before joining would make the join much slower.

I'll just note that the sort of EAV schema that you have here is widely
agreed to be an anti-pattern in database design.

regards, tom lane

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.