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');
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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@palantir.com |
1.408.338.5065
On 3/9/16, 2:00 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs