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

Hi,

On 2017-01-13 18:38:24 +0000, linasvepstas@gmail.com wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      14494
> Logged by:          Linas Vepstas
> Email address:      linasvepstas@gmail.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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs