jonahgao commented on issue #11282: URL: https://github.com/apache/datafusion/issues/11282#issuecomment-2210921179
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. ```sh postgres=# select 1 is unknown; ERROR: argument of IS UNKNOWN must be type boolean, not type integer LINE 1: select 1 is unknown; ``` ```sh DataFusion CLI v39.0.0 > select 1 is unknown; type_coercion caused by Error during planning: Cannot infer common argument type for comparison operation Int64 IS DISTINCT FROM Boolean ``` In DuckDB: ```sh D select 1 is unknown; ┌─────────────┐ │ (1 IS NULL) │ │ boolean │ ├─────────────┤ │ false │ └─────────────┘ ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
