Re: BUG #14494: Regression - Null arrays are not queryable

Hi,

On 2017-01-13 18:38:24 +0000, linasvepstas(at)gmail(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14494
> Logged by: Linas Vepstas
> Email address: linasvepstas(at)gmail(dot)com
> PostgreSQL version: 9.6.1
> Operating system: Debian unstable
> Description:
>
> Array handling appears to have regressed,

Regressed from what? Because these don't look like they'd have changed
recently.

> test=> select * from foo where outgoing=null;
> name | outgoing
> ------+----------
> (0 rows)
>
> unexpected: wanted one row here.

Comparisons with NULL result in NULL, which evaluates to false in a
WHERE clause. Try WHERE outgoing IS NULL;

> test=> select * from foo where outgoing != '{43,67}';
> name | outgoing
> ------+----------
> two | {}
> (1 row)
>
> unexpected: wanted two rows here. Where did the null row go?

Same answer as above. WHERE outgoing IS DISTINCT FROM ...;

>
> test=> select * from foo where array_length(outgoing,1)=0 ;
> name | outgoing
> ------+----------
> (0 rows)
>
> Huh? there is at least one array whose length is zero,

An empty array doesn't have a dimension 1 and thus results in NULL. Yes,
that's a bit confusing.

> and another that doesn't have a length...

You mean the NULL one? Why would you expect a 0 as a result then?

Greetings,

Andres Freund