How do I force filter evaluation order in SQL(Postgres)?

Question

I have a table that, simplified, looks roughly like this:

id | type | header    | body
===========================================
 1 | A    | {type: A} | {content: "Hi"}
 2 | A    | {type: A} | {content: "Hello"}
 3 | B    | {type: B} | ["Hi","Hello"]

The following query gives me an error:

> select * from Table where header->>'type'='A' and body->>'content' like 'H%'
ERROR:  cannot extract field from a non-object

Which is fair enough, but so does this query:

> select * from (select * from Table where header->>'type'='A') where body->>'content' like 'H%'

Whereas these do not:

> select * from Table where type='A' and body->>'content' like 'H%'
> select * from Table where header->>'type'='A' and body->>'content'='Hello'

I have workaround for this specific case (where the 'like' predicate is improperly being given precedence), but what concerns me is that I apparently can't even rely on parentheses to control evaluation order, even when, such as in this case, it changes what constraints apply to the data. Is there any general way to do this?

Answer 1

You should be able to force evaluation order with case:

select *
from Table
where (case when header->>'type'='A'
            then (case when body->>'content' like 'H%' then 1 end)
       end) = 1;

This is about the only time that I recommend putting case statements in the where clause.

You could also guarantee the order using a CTE:

with t as (
      select t.*
      from table t
      where header->>'type'='A'
     )
select t.*
from t
where body->>'content' like 'H%';

However, this incurs the extra overhead of materializing the intermediate result.

Answer 2

(This is supplementary info; Gordon's answer should be marked correct).

In SQL, the DB is free to execute predicates in any order. There is no short circuiting. Parenthesised groups override default operator precedence to control which operators bind to which operands, but do not force execution order.

A subquery doesn't guarantee anything about execution order. It may, and should where possible, be optimized out by flattening it into the outer query.

The issue you're having is the same as for handling divide by zero, and has the same solutions.

The correct solution is to force execution order with CASE as Gordon shows. You can use a CTE (WITH) or the OFFSET 0 hack, but both will have performance consequences.