Open qRoC opened 2 years ago
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?
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
@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?
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.
Thanks for your research 👍
this proposal looks good to me, let me know if you want to work on this 😄
In PG correct way to generate boolean comparison via predicate
IS
, likeIS TRUE
,IS NOT TRUE
, etc.https://www.postgresql.org/docs/9.0/datatype-boolean.html