or() and and() operators do not work with subqueries

[colearendt]

Environment

• PostgreSQL version: postgres:12
• PostgREST version: recent - since filtering on main table with embedded table criteria(inner join) #1075 and Correct inner join to use proper aliases #1978
• Operating system: MacOS

Description of issue

Using the test tables from #1978, you can craft a disjointed filter based on outer tables using syntax like:

/client?select=*,contact!inner(*)&or=(id.eq.1,id.eq.2)

Similarly, ever since #1075 you can filter on contact (sub-query) parameters:

/client?select=*,contact!inner(*)&contact.name=eq.Wally%20Walton

However, it seems you cannot similarly use the disjointed (or and() / other similar operators) with the subquery syntax

/client?select=*,contact!inner(*)&or=(contact.name.eq.Wally%20Walton,contact.name.eq.Wilma%20Wellers)
# {
#    "details": "unexpected \"a\" expecting \"not\" or operator (eq, gt, ...)",
#    "message": "\"failed to parse logic tree ((contact.name.eq.Wally Walton,contact.name.eq.Wilma Wellers))\" (line 1, column 12)"
# }

(the "a" is in "name" - "n" is valid b/c of "not", but the "a" throws the error)

I have tried a couple of variants (parentheses, equals, different filters, etc.) to see if there is a way to escape the column name, but could not come up with anything functional.

Similar:

/client?select=*,contact!inner(*)&or=(contact.id.eq.1,contact.id.eq.2)
# {
#     "details": "unexpected \"i\" expecting \"not\" or operator (eq, gt, ...)",
#     "message": "\"failed to parse logic tree ((contact.id.eq.1,contact.id.eq.2))\" (line 1, column 12)"
# }

I suspect this limitation exists regardless of !inner, however, subquery filters are more common / useful after #1075, which would explain how this limitation has not shown up before.

[bnjmnt4n]

https://postgrest.org/en/v8.0/api.html#embedded-filters recommends the following syntax:

GET /films?select=*,roles(*)&roles.or=(character.eq.Gummo,character.eq.Zeppo) HTTP/1.1

[colearendt]

Ahh interesting, thanks! So I presume by that syntax that you cannot have "or"s across sub-tables?

[steve-chavez]

So I presume by that syntax that you cannot have "or"s across sub-tables?

No, each sub-table filters are independent. If you see the shape of the query(here), you'll note the sub-table WHEREs are isolated.

[steve-chavez]

Tracking issue on #2563