Question 232105 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL less/greater comparison between booleans produces unexpected results

Why do most(all?) SQL Databases give:

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??

And just do 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 this with PostgreSQL, SQLite3 and MariaDB, and all agree on the same unexpected result.
What am I missing?

Tags:

3

Answers


  1. 0

    I don’t know exactly how NOT(FALSE) is evaluating, but NOT is not a function. If you want to not a boolean literal, then the parentheses should be around the entire expression, i.e. use (NOT FALSE) instead of NOT(FALSE). Consider:

    SELECT (NOT FALSE) < FALSE; -- 0, same as TRUE < FALSE
SELECT (NOT TRUE) < FALSE;  -- 0, same as FALSE < FALSE
    Login or Signup to reply.
  2. 0

    NOT and < are operators. Let’s take a look at operator precedence:

    enter image description here

    https://dev.mysql.com/doc/refman/8.0/en/operator-precedence.html

    From here we can see that < precedes NOT in priority, so

    NOT(FALSE) < FALSE

    is equivalent to

    NOT(FALSE < FALSE)

    and since FALSE is not smaller than itself, the inner expression is 0, that is, it’s FALSE and its negation is true.

    Whereas, if you enforce the operator order you preferred:

    (NOT(FALSE)) < FALSE

    then you will get the result you expected:

    enter image description here

    Login or Signup to reply.
  3. 0

    My answer is about SQLite (but I suspect that the same applies to other dbs).

    An expression like:

    NOT(FALSE) < FALSE;

    is evaluated as:

    NOT((FALSE) < FALSE);

    because the operator < has higher precedence over the operator NOT.
    Since (FALSE) < FALSE evaluates to FALSE, the result of the expression is TRUE.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search