Re: Question on not-in and array-eq

On Wed, Dec 8, 2021 at 8:15 AM Zhenghua Lyu <zlyu(at)vmware(dot)com> wrote:

> I run the SQL without array expr in other DBs(orcale, sqlite, ...), they
> all behave
> the same as Postgres.
>
> It seems a bit confusing for me that 'not in' and 'in' the same subquery
> both return 0
> rows, but the table contains data.
>

Because of this dynamic the reliable negation of "in" is "not (... in ...)"
as opposed to "not in".

https://www.postgresql.org/docs/current/functions-comparison.html

"If the expression is row-valued, then IS NULL is true when the row
expression itself is null or when all the row's fields are null, while IS
NOT NULL is true when the row expression itself is non-null and all the
row's fields are non-null. Because of this behavior, IS NULL and IS NOT
NULL do not always return inverse results for row-valued expressions; in
particular, a row-valued expression that contains both null and non-null
fields will return false for both tests."

The implications of the IS NULL treatment extends to equality checks and
thus the "[NOT] IN" expression.

Also, manually using array expression behaves differently from the first
> SQL. For not in case,
> I step in the code, and find array_eq will consider null = null as true,
> however ExecSubPlan will
> consider null as unprovable and exclude that row.
>
> How to understand the result? It seems SQL standard does not mention array
> operation for null
> value.
>

When comparing two non-null array variables the result will be either true
or false. If either of the array variables, as a whole, is null the result
will be null. This is due to the general rule that operations on null
values result in null. And the general desire to make array comparisons
behave in the manner expected by users as opposed to the surprising result
that row-valued values provide. The two simply are defined to behave
differently - mainly due to the fact that for row-valued data we choose to
adhere to the SQL Standard.

David J.