Re: [BUGS] BUG #14494: Regression - Null arrays are not queryable - Mailing list pgsql-bugs

From David G. Johnston
Subject Re: [BUGS] BUG #14494: Regression - Null arrays are not queryable
Date
Msg-id CAKFQuwb=n31=3F0DQDuWLRrjdLRQUrc7hSE_DYTFByrWyK5Lug@mail.gmail.com
Whole thread Raw
In response to [BUGS] BUG #14494: Regression - Null arrays are not queryable  (linasvepstas@gmail.com)
List pgsql-bugs
On Fri, Jan 13, 2017 at 11:38 AM, <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,

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


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

pgsql-bugs by date:

Previous
From: Andres Freund
Date:
Subject: Re: [BUGS] BUG #14494: Regression - Null arrays are not queryable
Next
From: zszabo@chemaxon.com
Date:
Subject: [BUGS] BUG #14495: Cost of comparator is not taken into account in sorting