Unable to rank with any database collation that does not order all alphanumeric characters the same as ASCII

Description

This bug is caused by specific collation type such as Danish/Norwegian, Swedish/Finish treat the alphabetical ordering quite differently. For example, in Danish/Norwegian two letters "aa" are treated as a single letter, and comes after "z" in the alphabet. This causes the lexorank ordering to be wrong.

Symptoms

The following error message is seen in JIRA Agile when attempting an issue ranking operation.

Diagnosis

• 1. Check your collation type if it affects alphabetical ordering, ie Danish/Norwegian
  2. Run the following query :

     SELECT 'ROWS_IN_BUCKET_0', "FIELD_ID", coalesce(COUNT(1), 0) AS rows_in_bucket FROM "AO_60DB71_LEXORANK" WHERE "RANK" LIKE '0|%' GROUP BY "FIELD_ID"
     UNION
     SELECT 'ROWS_IN_BUCKET_1', "FIELD_ID", coalesce(COUNT(1), 0) AS rows_in_bucket FROM "AO_60DB71_LEXORANK" WHERE "RANK" LIKE '1|%' GROUP BY "FIELD_ID"
     UNION
     SELECT 'ROWS_IN_BUCKET_2', "FIELD_ID", coalesce(COUNT(1), 0) AS rows_in_bucket FROM "AO_60DB71_LEXORANK" WHERE "RANK" LIKE '2|%' GROUP BY "FIELD_ID" ORDER BY "FIELD_ID";
     

  3. The results should show that there is a balancing going on (there will be 2 rows of results, with a number of issues in one bucket and another number in the other bucket)
  4. Enable DEBUG Logging for the balancer : com.atlassian.greenhopper.service.lexorank.balance
  5. From there, we can see that there is something wrong with the balancer. Notably, this line:

     New rank[1|hzzzvz:] for issue[id=31321] for rank field[id=10890] already exists, retrying balance oepration
     

     This error shows that the Balancer is trying to insert a rank value that already exists.

Workarounds

Please refer to Unable to rank and/or balancer fails to complete due to incorrect database collation