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

From: Marc-Olaf Jaschke <marc-olaf(dot)jaschke(at)s24(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Missing rows with index scan when collation is not "C" (PostgreSQL 9.5)
Date: 2016-03-21 20:40:37
Message-ID: 111D0E27-A8F3-4A84-A4E0-B0FB703863DF@s24.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Hi,

PostgreSQL 9.5 ignores rows with the following test case:

=========================

\l+

Encoding | Collate | Ctype
UTF8 | de_DE.UTF-8 | de_DE.UTF-8
...

create table test (t) as values ('eai'), ('e aí');

select * from test where t = 'eai';
t
-----
eai
(1 row)

create index on test(t);

set enable_seqscan = false;

select * from test where t = 'eai';
t
---
(0 rows)

select t from test where t = 'eai' collate "C";
t
-----
eai
(1 row)

alter table test alter column t type text collate "C";
select * from test where t = 'eai';
t
-----
eai
(1 row)

alter table test alter column t type text collate "de_DE.utf8";
select * from test where t = 'eai';
t
---
(0 rows)

set enable_seqscan = true;

select * from test where t = 'eai';
t
-----
eai
(1 row)

=========================

I was able to reproduce this with

cat /etc/debian_version
6.0.1
PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
/lib/libc.so.6 > GNU C Library (Debian EGLIBC 2.11.3-3) stable release version 2.11.3, by Roland McGrath et al.

CentOS release 6.7 (Final)
PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
ldd --version
ldd (GNU libc) 2.12

I was not able to reproduce this with

OSX (10.11.3 (15D21))
PostgreSQL 9.5alpha1 on x86_64-apple-darwin14.3.0, compiled by Apple LLVM version 6.1.0 (clang-602.0.53) (based on LLVM 3.6.0svn), 64-bit

OSX (10.11.3 (15D21))
PostgreSQL 9.5.1 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit

Ubuntu 12.04.5 LTS
PostgreSQL 9.3.11 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
ldd --version
ldd (Ubuntu EGLIBC 2.15-0ubuntu10.13) 2.15

CentOS release 6.7 (Final)
PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
ldd --version
ldd (GNU libc) 2.12

Red Hat Enterprise Linux Server release 7.2 (Maipo)
PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
ldd --version
ldd (GNU libc) 2.17

Best regards,
Marc-Olaf Jaschke

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2016-03-21 21:04:36 Re: BUG #14038: substring cuts unicode char in half, allowing to save broken utf8 into table
Previous Message Reece Pegues 2016-03-21 17:10:45 Re: BUG #14038: substring cuts unicode char in half, allowing to save broken utf8 into table

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2016-03-21 21:13:12 Re: Relax requirement for INTO with SELECT in pl/pgsql
Previous Message Merlin Moncure 2016-03-21 20:24:44 Relax requirement for INTO with SELECT in pl/pgsql