Re: 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(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');

gtakahashi(at)palantir(dot)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

Is the fix for this one easy to apply? Would it be feasible for me to
backport and bring into PostgreSQL 9.3.6?
__________________________________________
Glen Takahashi | Palantir Technologies | gtakahashi(at)palantir(dot)com |
1.408.338.5065

On 3/9/16, 2:00 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>gtakahashi(at)palantir(dot)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(at)palantir(dot)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