Thread: Issue with retrieving data when the column has @- in it's value

I have an issue where in the same scenario below works on my POC but is having an issue in the original db.

Original db - OS : Red Hat   (postgres13)

POC db - OS : SUSE    (postgres 14)

SELECT char_length("col1"), octet_length("col1"), "col1"
FROM "test_col" WHERE "col1" = 'test_level@-1';

The above query returns data in POC, however, on orig db, it does not return any data.

Can anyone guide me on what else can be checked?

On Fri, 2025-04-04 at 18:41 -0700, Teju Jakkidi wrote:
> I have an issue where in the same scenario below works on my POC but is having an issue in the original db.
>
> Original db - OS : Red Hat   (postgres13)
> POC db - OS : SUSE    (postgres 14)
>
> SELECT char_length("col1"), octet_length("col1"), "col1"
> FROM "test_col" WHERE "col1" = 'test_level@-1';
>
> The above query returns data in POC, however, on orig db, it does not return any data.
>
> On orig db, I have to either a dd trim or COLLATE "C" for it to return data.
>
> The collation is same on both the dbs (en_US.UTF-8) and the os locate is also the same on both.
>
> Column col1 datatype is varchar.
>
> I tried checking if there are any spaces or special characters and did not see anything in the output. 
>
> SELECT char_length("col1"), octet_length("col1"), length("col1"), encode("col1"::bytea, 'escape')
> FROM "test_col" WHERE "col1" = 'test_level@-1';
>
> output is the same for both:
>  char_length | octet_length | length |    encode
> -------------+--------------+--------+---------------
>           13 |           13 |     13 | test_level@-1
>
> Can anyone guide me on what else can be checked?

Find out the primary key of the row in question and look what the bytes are:

   SELECT convert_to(col1, 'UTF8') FROM test_col WHERE pkey = 42;

Compare that on both databases, and compare it with the string literal:

   SELECT convert_to('test_level@-1', 'UTF8') FROM test_col WHERE pkey = 42;

Also, compare the collations shown in the \l output for the database.

Yours,
Laurenz Albe

> On Apr 5, 2025, at 9:18 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> Also, compare the collations shown in the \l output for the database.

And glibc versions...

Scott Ribe <scott_ribe@elevated-dev.com> writes:
> On Apr 5, 2025, at 9:18 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>> Also, compare the collations shown in the \l output for the database.

> And glibc versions...

Yeah, that.  This smells very much like a collation-inconsistency
problem: random punctuation is exactly the sort of input that the
glibc authors tend to change collation behaviors for.  I'm betting
that at some point the problematic database was physically copied
between two platforms with inconsistent collations.  (The OP
mentioning use of two different Linux distros bolsters this
theory.)  That made the index inconsistent with the new platform's
sorting behavior, and weird key lookup failures is exactly what
you get as a result of that.

Short answer: REINDEX whatever index the problematic query is
using, as well as any others on text columns, because they
likely have similar issues you haven't noticed yet.

            regards, tom lane