Re: SQL queries not matching on certain fields

Broken index?

I had a similar problem a while ago. We were streaming a DB from a Debian
box to an alpine docker image. The underlying system libraries were a
little different and that resulted in broken index behavior.

On Wed, Apr 3, 2019 at 3:06 PM Felix Ableitner <me(at)nutomic(dot)com> wrote:

> Hello,
>
> I'm having a very strange problem with the Postgres database for my
> website. Some SQL queries are not matching on certain fields. I am running
> these commands via the psql command.
>
> Here is a query that works as expected:
>
> # SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='emma';
> id | preferredUsername
> -------+-------------------
> 48952 | emma
> 58672 | emma
> (2 rows)
>
> The following query should work as well, because the username exists. But
> in fact, it consistently returns nothing:
>
> # SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='mailab';
> id | preferredUsername
> ----+-------------------
>
> (0 rows)
>
> There are some workarounds which fix the WHERE statement, all of the
> following work as expected:
>
> SELECT id, "preferredUsername" FROM actor WHERE trim("preferredUsername")='mailab';
> SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername" ILIKE 'mailab'; SELECT id, "preferredUsername" FROM actor WHERE md5("preferredUsername")=md5('mailab');
>
>
> Now you might think that there is something wrong with the encoding, or
> the field contains some extra whitespace. But SHOW SERVER_ENCODING and SHOW
> SERVER_ENCODING show UTF8. Length and m5 sum are also exactly as expected.
> And I checked the individual bytes with get_byte(), all of them are in the
> range 97-122.
>
> About the setup, this is Postgres 10 running in Docker, on an Ubuntu VPS
> (see below for all versions etc). I had this problem before on the same
> setup, so I did an export to text file with pg_dump, and imported into a
> completely new database with psql. That fixed the problem for a few days,
> but it came back soon after.
>
> The problem only seems to affect one or two specific columns, and only a
> few specific rows in those columns. Most other rows work normally. Affected
> columns also randomly start working again after a few days, and other
> columns get affected. I havent noticed any kind of pattern.
>
> You can find the table definition here:
> https://gitlab.com/snippets/1840320
>
> Version info:
>
> Postgres Docker Image: postgres:10.7-alpine
> Docker version: 18.09.2
> OS: Ubuntu 18.04.2
>
> Please tell me if you have any idea how to fix or debug this. I already
> asked multiple people, and no one has a clue what is going on.
>
> Best,
> Felix Ableitner
>