You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When the parameter is a boolean, IS NULL and IS UNKNOWN are equivalent. According to the SQL standard
The Boolean data type comprises the distinct truth values True and False. Unless prohibited by a NOT NULL constraint, the Boolean data type also supports the truth value Unknown as the null value. This specification does not make a distinction between the null value of the Boolean data type and the truth value Unknown; they may be used interchangeably to mean exactly the same thing.
UNKNOWN is the third truth value. So the difference is that UNKNOWN can only be applied to boolean types. DataFusion and PostgreSQL adhere to this, but DuckDB does not.
postgres=# select 1 is unknown;
ERROR: argument of IS UNKNOWN must be type boolean, not type integer
LINE 1: select1 is unknown;
DataFusion CLI v39.0.0
>select1 is unknown;
type_coercion
caused by
Error during planning: Cannot infer common argument typefor comparison operation Int64 IS DISTINCT FROM Boolean
In DuckDB:
D select1 is unknown;
┌─────────────┐
│ (1 IS NULL) │
│ boolean │
├─────────────┤
│ false │
└─────────────┘
Which in turn means we need those duplicated constructs on the SQL layer, but we could then desugar and replace IS [NOT] UNKNOWN with IS [NOT] NULL at runtime.
Reacted by Andrew Lamb, Jonah Gao and Daniël Heres
Activity
[-][DISCUSSION] Potentially consolidate [/-][+][DISCUSSION] Potentially consolidate Expr::IsNotUnknown, `IsKnown`, etc[/+]jonahgao commentedon Jul 5, 2024
When the parameter is a boolean,
IS NULLandIS UNKNOWNare equivalent. According to the SQL standardUNKNOWNis the third truth value. So the difference is thatUNKNOWNcan only be applied to boolean types. DataFusion and PostgreSQL adhere to this, but DuckDB does not.In DuckDB:
findepi commentedon Jul 5, 2024
Indeed, from SQL 2016 spec
I didn't know about this syntax.
Thanks @alamb @jonahgao . So we don't want to unify. Just make sure the
IS [NOT] ( TRUE | FALSE | UNKNOWN )is applicable to booleans only.findepi commentedon Jul 5, 2024
Which in turn means we need those duplicated constructs on the SQL layer, but we could then desugar and replace
IS [NOT] UNKNOWNwithIS [NOT] NULLat runtime.alamb commentedon Jul 5, 2024
I like the idea of desugaring (I think that would map to consolidating some of the Enum variants) 🚀