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');
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 |