SQL comparison between boolean values ​​can lead to unexpected results

Question

Why do most (all?) SQL databases give the following results:

SELECT FALSE < FALSE; -- FALSE / 0 Ok
SELECT TRUE < FALSE; -- FALSE / 0 Ok

SELECT NOT(FALSE) < FALSE; -- TRUE / 1 What?
SELECT NOT(TRUE) < FALSE; -- TRUE / 1 What? ? 

Just to double-check:

SELECT NOT(TRUE) = FALSE; -- TRUE / 1 Ok
SELECT NOT(FALSE) = TRUE; -- TRUE / 1 Ok

In Postgres I can also check:

SELECT pg_typeof(TRUE), pg_typeof(NOT(FALSE));
  -- boolean | boolean

I've tried PostgreSQL, SQLite3 and MariaDB and they all gave the same unexpected results.

What am I missing?

Answer 1

I'm not sure how NOT(FALSE) is evaluated, but NOT is not a function. If you want to negate a boolean literal, then parentheses should be placed around the entire expression, i.e. use (NOT FALSE) instead of NOT(FALSE). Consider the following example:

SELECT (NOT FALSE) < FALSE; -- 0，与 TRUE < FALSE 相同
SELECT (NOT TRUE) < FALSE; -- 0，与 FALSE < FALSE 相同