Thread: BUG #14010: Multi-valued Index-only scans do not properly handle nulls in search

The following bug has been logged on the website:

Bug reference:      14010
Logged by:          Glen Takahashi
Email address:      gtakahashi@palantir.com
PostgreSQL version: 9.3.6
Operating system:   Red Hat Enterprise Linux Server release 6.7
Description:

Example table:

 a |   b
---+--------
 a | b
 a | [NULL]
 a | [NULL]
 (repeated 100's of times)
 b | a

select a,b from test_table where (a,b) > ('a','a') order by a,b;
returns:
 a | b
---+---
 a | b
 b | a
(2 rows)

create index on test_table (a,b);
The same query now returns:
 a | b
---+---
 a | b
(1 row)

However, the query without using `order by` returns the right values!
select a,b from test where (a,b) > ('a','a');
a | b
---+---
 a | b
 b | a
(2 rows)

If there are sufficiently small enough number of nulls in between (I got
differing numbers from 100-200 depending on the table), the query will
instead use a Quicksort for what I can only assume is optimization to avoid
reading random pages, and will actually return the right value. I was able
to get this to reproduce 100% of the time when using > 256 nulls in between
('a','b') and ('b','a');

gtakahashi@palantir.com writes:
> select a,b from test_table where (a,b) > ('a','a') order by a,b;
> returns:
>  a | b
> ---+---
>  a | b
>  b | a
> (2 rows)

> create index on test_table (a,b);
> The same query now returns:
>  a | b
> ---+---
>  a | b
> (1 row)

Ugh.  This bug just passed its tenth birthday ... kind of astonishing
that nobody found it before.  Will fix, thanks for the report!

            regards, tom lane

Glen Takahashi <gtakahashi@palantir.com> writes:
> Is the fix for this one easy to apply? Would it be feasible for me to
> backport and bring into PostgreSQL 9.3.6?

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1ab7a160f9d983ba738022c0b4dc62a67848b932

            regards, tom lane

SXMgdGhlIGZpeCBmb3IgdGhpcyBvbmUgZWFzeSB0byBhcHBseT8gV291bGQgaXQgYmUgZmVhc2li
bGUgZm9yIG1lIHRvDQpiYWNrcG9ydCBhbmQgYnJpbmcgaW50byBQb3N0Z3JlU1FMIDkuMy42Pw0K
X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fDQpHbGVuIFRha2FoYXNo
aSB8IFBhbGFudGlyIFRlY2hub2xvZ2llcyB8IGd0YWthaGFzaGlAcGFsYW50aXIuY29tIHwNCjEu
NDA4LjMzOC41MDY1DQoNCg0KDQoNCk9uIDMvOS8xNiwgMjowMCBQTSwgIlRvbSBMYW5lIiA8dGds
QHNzcy5wZ2gucGEudXM+IHdyb3RlOg0KDQo+Z3Rha2FoYXNoaUBwYWxhbnRpci5jb20gd3JpdGVz
Og0KPj4gc2VsZWN0IGEsYiBmcm9tIHRlc3RfdGFibGUgd2hlcmUgKGEsYikgPiAoJ2EnLCdhJykg
b3JkZXIgYnkgYSxiOw0KPj4gcmV0dXJuczoNCj4+ICBhIHwgYiANCj4+IC0tLSstLS0NCj4+ICBh
IHwgYg0KPj4gIGIgfCBhDQo+PiAoMiByb3dzKQ0KPg0KPj4gY3JlYXRlIGluZGV4IG9uIHRlc3Rf
dGFibGUgKGEsYik7DQo+PiBUaGUgc2FtZSBxdWVyeSBub3cgcmV0dXJuczoNCj4+ICBhIHwgYiAN
Cj4+IC0tLSstLS0NCj4+ICBhIHwgYg0KPj4gKDEgcm93KQ0KPg0KPlVnaC4gIFRoaXMgYnVnIGp1
c3QgcGFzc2VkIGl0cyB0ZW50aCBiaXJ0aGRheSAuLi4ga2luZCBvZiBhc3RvbmlzaGluZw0KPnRo
YXQgbm9ib2R5IGZvdW5kIGl0IGJlZm9yZS4gIFdpbGwgZml4LCB0aGFua3MgZm9yIHRoZSByZXBv
cnQhDQo+DQo+CQkJcmVnYXJkcywgdG9tIGxhbmUNCg0K