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

From: gtakahashi(at)palantir(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14010: Multi-valued Index-only scans do not properly handle nulls in search
Date: 2016-03-09 16:51:36
Message-ID: 20160309165136.8903.83559@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14010
Logged by: Glen Takahashi
Email address: gtakahashi(at)palantir(dot)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');

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2016-03-09 18:23:11 Re: BUG #14008: corr, covar_pop function returns different values on the same dataset
Previous Message Nick Cleaton 2016-03-09 16:17:46 streaming replication master can fail to shut down