SQL queries not matching on certain fields

From: Felix Ableitner <me(at)nutomic(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: SQL queries not matching on certain fields
Date: 2019-04-03 13:06:03
Message-ID: 084039d1-bddb-2fc6-1ad3-4be7562fd9bb@nutomic.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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:

|SELECTid, "preferredUsername"FROMactor
WHEREtrim("preferredUsername")='mailab';||SELECTid, "preferredUsername"FROMactor WHERE"preferredUsername"ILIKE
'mailab';|
|||SELECTid, "preferredUsername"FROMactor
WHEREmd5("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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steven Lembark 2019-04-03 13:18:25 Move vs. copy table between databases that share a tablespace?
Previous Message Pavan Teja 2019-04-03 11:48:27 Re: Seeded Replication