5

Using PostgreSQL 9.5. It seems like Postgres is being inconsistent with how it compares strings similar to لى. A unique constraint is considering some strings equal that GROUP BY is considering different.

I have a select query that is using GROUP BY on a TEXT column and an aggregate function on some other column to ensure uniqueness of the TEXT column in the output, and I'm inserting the results into a table with a primary key (and thus unique) constraint on the TEXT column.

Looks similar to this; I've just changed the table names for simplicity:

 INSERT INTO mytable ( % mytable has string TEXT PRIMARY KEY, score INT
            SELECT 
            sq1.string string, sq2.score / sq1.score
            FROM
            (
                SELECT n.string string, SUM(n.score) score
                FROM
                othertable1 n % has string TEXT (non-unique) and score INT
                GROUP BY string
            ) sq1,
            (
                SELECT n.string string, SUM(n.score) score
                FROM
                othertable2 n % has string TEXT (non-unique) and score INT
                GROUP BY string
            ) sq2
            WHERE sq1.string = sq2.string
            ORDER BY score DESC LIMIT 100000
        );

This should never fail, right? It fails: violation of unique constraint due on key لى.

And it has worked many times before with other data sets containing millions of rows in PG 9.3; I don't know whether لى was in the data back then. I know Arabic has decorations you can put on the letters, so I wonder if that's tripping it up.

Does anyone have an alternative explanation, or should I report this as a bug once I can reproduce it more easily?

UPDATE: Confirmed that the query runs successfully on a PostgreSQL 9.3 server with the same data. There are some moving parts here, so I'm trying to find exactly what the problematic strings are so I can make a simple list of queries anyone can run to expose a bug.

UPDATE 2: Argh, I can't get my database to give me a set of strings I can copy into a table and expose a bug. I've been trying to do it with COPY TO. Something along the way keeps stripping the Arabic text of the differences that are making it fail, I think. But I tried a simpler query, and it's also failing. It's more obvious that this should work:

INSERT INTO mytable ( % mytable has string TEXT PRIMARY KEY, score INT
    SELECT n.string string, SUM(n.score) score
    FROM
    othertable2 n % has string TEXT (non-unique) and score INT
    GROUP BY string
);

I'm still working towards getting something others can try because, of course, a bug report is useless if I say that it only works on my data.

UPDATE 3: I ran it again with different data and ran into the same problem with Cyrillic characters У and В. Making a table containing them didn't reveal anything. Same problem as with the Arabic text, I think. Something is getting stripped along the way.

UPDATE 4: This is definitely a bug. I'm still trying to figure out how to report this. I found a workaround:

WITH glitch(string, score) AS (
    SELECT n.string string, SUM(n.score) score
    FROM
    othertable2 n % has string TEXT (non-unique) and score INT
    GROUP BY string
)
INSERT INTO mytable ( % mytable has string TEXT PRIMARY KEY, score INT
    SELECT DISTINCT ON(string) * FROM glitch
);
  • 1
    Sounds like a decomposed-vs-precomposed issue. PostgreSQL doesn't normalize utf-8 and expects applications to do that. A mistake in my opinion. Or it could be different COLLATE settings? – Craig Ringer Feb 21 '16 at 9:38
  • All my TEXT columns are using default COLLATE settings, and my locale is en_US.UTF-8. – sudo Feb 21 '16 at 18:46
  • 2
    I'm more inclined to suspect a bug in your query at this point. I agree that it looks reasonably sensible - you're joining the product of two GROUP BY subqueries grouped on string on the join condition that they be equal, so this join shouldn't be able to produce more than one output row for each distinct string input. So assuming that mytable is empty at the start I think it looks OK - but I'm not sure. You really need to boil this down to a minimal query and input dataset. I don't see any problems with simple tests here. – Craig Ringer Feb 22 '16 at 0:49
  • I agree. I've been trying to find a way to reproduce this easily, but I've been having trouble finding the bad strings, mainly because I don't know how Arabic works, and the data is so large that I can't possibly look through it manually. I'm running a query right now that should only output the duplicates... it's just taking a while. – sudo Feb 22 '16 at 1:15
  • 1
    @CraigRinger What do you mean by "normalizing UTF-8"? I think you mean normalizing the Unicode strings, which encoding it is in (UTF-8, UTF-32, or whatever) is a separate issue. – Flimm May 31 '18 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.