Add rel.True, rel.False, rel.NotTrue, rel.NotFalse filters

[qRoC]

In PG correct way to generate boolean comparison via predicateIS, like IS TRUE, IS NOT TRUE, etc.

https://www.postgresql.org/docs/9.0/datatype-boolean.html

[lafriks]

I have not seen anywhere in docs that writing WHERE col IS TRUE is correct way to do in postgres 🤔

All docs and samples usually refer when filtering using boolean columns for true values like WHERE col or for false values WHERE NOT col. Also even if rel.True and rel.False would be added, I don't see reason for adding rel.NotTrue and rel.NotFalse filters, how would they differ from rel.False and rel.True respectively?

[qRoC]

null values for column with bool type in unknown state:

• where column = true (null value processed as false)
• where column != true (null value processed as true)
• where column IS TRUE (null value processed as unknown)
• where column IS NOT TRUE (null value processed as unknown)

Prepare test table:

create table test
(
    b_null bool
);

INSERT INTO test (b_null) VALUES (null);
INSERT INTO test (b_null) VALUES (true);
INSERT INTO test (b_null) VALUES (false);

Select with != true and != false return only one record, but IS NOT TRUE, IS NOT FALSE returns 2 records:

SELECT COUNT(1) FROM test WHERE b_null != true; -- 1
SELECT COUNT(1) FROM test WHERE b_null IS NOT TRUE; -- 2

IS/NOT IS are predicates, and have their own behavior compared to operators

[Fs02]

@qRoC thanks for the suggestion!

I actually I'm not aware of this and never seen it used in other orm or programming language so far.
and from your example, IS NOT TRUE is like alternative of b_null != true or b_null is null?

could you give more research about this syntax support in other database (sqlite, mysql and mssql) and proposal to support them?

[qRoC]

This behavior defined in ISO SQL:1999:

TRUE OR UNKNOWN   -> TRUE
TRUE AND UNKNOWN  -> UNKNOWN
TRUE = UNKNOWN    -> UNKNOWN

FALSE OR UNKNOWN  -> UNKNOWN
FALSE AND UNKNOWN -> FALSE
FALSE = UNKNOWN   -> UNKNOWN

NOT UNKNOWN       -> UNKNOWN

Basic SQL comparison operators always return UNKNOWN when comparing anything with NULL. For this reason exists IS [NOT] NULL predicate.

---

The IS [NOT] (TRUE|FALSE|UNKNOWN) defined in optional feature F571.

mysql 8:

SELECT NULL IS TRUE; -- 0
SELECT NULL IS FALSE; -- 0
SELECT NULL IS UNKNOWN; -- 1

sqllite3:

SELECT NULL IS TRUE; -- 0
SELECT NULL IS FALSE; -- 0
SELECT NULL IS UNKNOWN; -- SQL error or missing database (no such column: UNKNOWN)

IS NOT TRUE is like alternative of b_null != true or b_null is null?

Yes, this method is used if the database does not support F571.

[Fs02]

Thanks for your research 👍

this proposal looks good to me, let me know if you want to work on this 😄