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
