Missing rows with index scan when collation is not "C" (PostgreSQL 9.5) - Mailing list pgsql-bugs

Hi,

PostgreSQL 9.5 ignores rows with the following test case:

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=


\l+
=E2=80=A6
Encoding | Collate | Ctype   =20
UTF8 | de_DE.UTF-8 | de_DE.UTF-8=20
...

create table test (t) as values ('eai'), ('e a=C3=AD');

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

create index on test(t);

set enable_seqscan =3D false;

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

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

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


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

set enable_seqscan =3D true;

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

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=



I was able to reproduce this with

cat /etc/debian_version=20
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
=09
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)=20
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