Support sub query expressions (ANY, SOME, ALL, EXISTS) in PostgreSQL and MySQL

[manhrev]

I can't find a way to express sub query expressions, for examples:

# ANY / SOME / ALL subquery
...
WHERE book_type = ANY (?)

# EXISTS subquery
...
WHERE EXISTS(
   SELECT 1 FROM ...
)

Currenty, I workaround this by using F() but I don't think this is the corect approach:

sm.Where(
   psql.F("EXISTS", existQuery),
)

I think we should add dedicated starters for those expressions, such as Exists(...Expression), Any(...Expression), etc. Both PostgreSQL and MySQL support those operator, while SQLite supports EXISTS only.
Actually, IN and NOT IN belong to the same category of operators and are already supported through operator chaining but other operators that I mention can't be implemented like that.

Bonus: I also can’t find a way to express the unary minus operator (-). Should this be added as a starter as well?

[stephenafamo]

I think Exists and UnaryMinus can be starters, however, ANY, SOME and ALL should be included as chain methods specific to Postgres (similar to BetweenSymmetric)

[manhrev]

I agree on Exists and Minus (unary) starters case.
But implement ANY, SOME, ALL as chain methods is not the best solution. For example, for the ANY keyword, there are a few possible implementation strategies:

1. Different chain methods
   We would need to define a chain method for every compatible operator combined with ANY, such as EQAny(val bob.Expression) Expression, GTAny(val bob.Expression) Expression, etc.

2. A single chain method with an operator parameter
   Use one chain method like ANY(op string, val bob.Expression) Expression, where op can be "EQ", "GT", etc.

What do you think about this?

[manhrev]

I implemented ANY, SOME, ALL as starters. Starters without chaining are the best option, but there's currently no ways to implement it

[stephenafamo]

but there's currently no ways to implement it

Actually there is. This is done by returning the base interface bob.Expression instead of the dialect specific Expression type. An example of this is the chained As method.