Re: Missing rows with index scan when collation is not "C" (PostgreSQL 9.5)

On Mon, Mar 21, 2016 at 8:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Marc-Olaf Jaschke <marc-olaf(dot)jaschke(at)s24(dot)com> writes:
>> PostgreSQL 9.5 ignores rows with the following test case:
>
> I can reproduce this in 9.5 and HEAD on RHEL6, but 9.4 works as expected.
> I presume that that points the finger at the abbreviated-keys work.
>
> BTW, what I'm seeing in 9.5/HEAD is that all three comparison senses fail:
>
> u8=# set enable_seqscan TO 0;
> SET
> u8=# select * from test where t < 'eai';
> t
> ---
> (0 rows)
>
> u8=# select * from test where t = 'eai';
> t
> ---
> (0 rows)
>
> u8=# select * from test where t > 'eai';
> t
> ---
> (0 rows)

This could plausibly be a consequence of the abbreviated keys work if
strxfrm() and strcoll() return inconsistent results for those strings
for the same locale (say, one says +1 and the other says -1 given
those inputs). I don't have a RHEL6 system handy to test whether that
might be the case here.

If that is the case, I'd argue that's a glibc problem, not our
problem. Of course, we could provide an option to disable abbreviated
keys for the benefit of people who need to work around buggy libc
implementations.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company