Wrong SQL emitted for NOT with binary operator

[lukaszsamson]

Elixir version

Erlang/OTP 28 [erts-16.4.0.1] [source] [64-bit] [smp:12:12] [ds:12:12:10] [async-threads:1] [jit] Elixir 1.20.2 (compiled with Erlang/OTP 28)

Database and Version

PostgreSQL all versions, affects others

Ecto Versions

Ecto 3.14.0 Ecto SQL 3.14.0

Database Adapter and Versions (postgrex, myxql, etc)

postgrex 0.22.2, affects others

Current behavior

PostgreSQL and other adapters do not parenthesize NOT expressions when they are operands to binary operators.

Example:

from(p in "posts", select: (not p.b) < p.c)

currently emits SQL equivalent to:

SELECT NOT (p0."b") < p0."c" FROM "posts" AS p0

In PostgreSQL and other dialects, comparison operators bind tighter than NOT, so this is parsed as:

NOT (p0."b" < p0."c")

instead of:

(NOT p0."b") < p0."c"

This changes the meaning of the query.

See
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-PRECEDENCE
https://dev.mysql.com/doc/refman/9.7/en/operator-precedence.html
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql?view=sql-server-ver17

Expected behavior

Unary NOT expressions should be parenthesized in rendered SQL