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

On Fri, Jan 13, 2017 at 11:38 AM, <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,
>

​From what?

> with lots of surprising results for empty
> and null arrays.
>
>

>
> test=> select * from foo where outgoing=null;
> name | outgoing
> ------+----------
> (0 rows)

> unexpected: wanted one row here.
>
>
​​While you can configure the system to yield "true" for "null = null" by
default it does not - I suspect your prior system has this configuration
option turned on. This is not specific to arrays.

​
https://www.postgresql.org/docs/9.6/static/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-CLIENTS
​

test=> select * from foo where outgoing != '{43,67}';
> name | outgoing
> ------+----------
> two | {}
> (1 row)

> unexpected: wanted two rows here. Where did the null row go?
>

​Again, null != (non-null) yields null, not true​, and would work this way
regardless of the option mentioned above...

> 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, and another that
> doesn't have a length...
>

Empty arrays and nulls, when provided to array_length, report NULL for all
dimensions.​

​David J.​