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 | emma58672 | 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
