Sv: SQL queries not matching on certain fields

[Andreas Joseph Krogh <andreas@xxxxxxxxxx>]

På onsdag 03. april 2019 kl. 15:06:03, skrev Felix Ableitner <me@xxxxxxxxxxx>:

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

 

Does disabling index-scan make a difference?

 

SET enable_indexscan to off;
 

How about dumping the relevant data and reloading it into another similar (but smaller) table, can you reproduce it then?

 

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@xxxxxxxxxx

www.visena.com