"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.