Consider this inconsistency: test-db=> select id from project_type_variants where id in (0); id ---- (0 rows)
test-db=> select id from project_type_variants where id in (null); id ---- (0 rows)
test-db=> select id from project_type_variants where id not in (0); id ---- 1 2 3 (3 rows)
test-db=> select id from project_type_variants where id not in (null); id ---- (0 rows)
Needs to work consistently, because (null) is a required workaround for:
test-db=> select id from project_type_variants where id in (); ERROR: syntax error at or near ")"
There is no inconsistency, this is how comparison to null works
where id in (null)
is NOT equivalent to (if that was allowed) checking if id is in with the empty set:
where id in ()
The expression `id in (null)` is equivalent to `id = null` which is always null (or more accurately Uknown) and where unknown will have the same result as where false.
Workarounds:
where id in (select null::int where false)
where id not in (select null::int where false)
or avoiding the cast:
where id in (select id from project_type_variants where false)
where id not in (select id from project_type_variants where false)
