From: Tom Lane <tgl@sss.pgh.pa.us>
To: fabernal@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18887: Inner join returns non-existent data.
Date: Thu, 10 Apr 2025 13:51:54 -0400
Message-ID: <535955.1744307514@sss.pgh.pa.us> (raw)
In-Reply-To: <18887-6681d7157d178b70@postgresql.org>
References: <18887-6681d7157d178b70@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> I have two tables that share the same primary key composed of three fields.
> When I perform an inner join using all the key fields in the statement, it
> returns non-existent values. The inner join shouldn't return any rows, but
> instead it returns a row with a non-existent value.
> Select rp.codactividadeconomica
> from p_contratos pc
> inner join remuner.remunerp_contratos rp
> on pc.codigoempresa = rp.codigoempresa
> and pc.concoanio = rp.concoanio
> and pc.concosecue = rp.concosecue
> where pc.codigoempresa = 2 and pc.concoanio = 16 and pc.concosecue =
> '017';
I'm inclined to guess that this is a symptom of a corrupted index;
does REINDEX help?
Since one of the columns in question is text (well, char(N)),
a plausible theory about what caused the corruption is an update
of the underlying system that resulted in a collation change.
See
https://wiki.postgresql.org/wiki/Locale_data_changes
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, fabernal@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18887: Inner join returns non-existent data.
In-Reply-To: <535955.1744307514@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox