Bugzilla – Bug 18108
Alphapagebar contains accented characters not ordered the same as the list
Last modified: 2014-08-21 10:15:16 UTC
Relating to the Album Artists view in the Web UI, I notice that I have a small discrepancy between the content of the artists, and the alphapagebar shortcuts. "1 2 3 5 7 8 Å A B C D E F G H I J K L M N O P Q R S T U V W X Y Z" I have an artist Morgan Ågren that appears within the middle of the artists beginning with "A". If I press the "Å", it takes me to that artist in the list, but it seems odd that this is in the jump strip, as the "A" that follows it will jump backwards to the start of artists beginning with "A". It seems that the artist list treats the accented Å character as a standard A for sorting purposes (otherwise it would appear before artists starting "A"), but doesn't group these starting letters together in the alphapagebar.
Michael suggested: >How about... sort the page bar the same way we sort the items? "Morgan Ågren" appears in the artist list, half way through the artists beginning with A. The issue is that there could be several artists beginning with "Å" appearing in the artists list, and they would not be sorted together, as it appears to be ignoring accents for sorting purposes. So it is not really correct for Å to appear before or after A in the alphapagebar. I assume it would jump to the first artist beginning with that letter, which could be before or after the first artist beginning with A.
Some investigation showed me that the COLLATE parameter only works with ORDER BY, but not with GROUP BY. As we're using grouping to create the alpha bar, but sorting to create the actual list, this results in the reported inconsistency. Yet have to figure out how to work around this issue.
It seems that SQLite's GROUP BY actually uses a specified COLLATE sequence, but result is different if an ORDER BY follows. Tested with SQLite Expert Pro 3.5 with the built-in ICU support enabled. Tests made with the following data in the contributors table: id namesort 20 ADELE 21 ALANIS MORISSETTE 22 Å NIKKI YANOFSKY 23 A FINE FRENZY 24 XYZ The collation was loaded using this command: SELECT icu_load_collation('de_DE', 'de_DE') Test 1: SELECT SUBSTR(contributors.namesort,1,1), count(distinct contributors.id) FROM contributors GROUP BY SUBSTR(contributors.namesort,1,1) COLLATE de_DE returned: A 3 Å 1 X 1 Test 2: SELECT SUBSTR(contributors.namesort,1,1), count(distinct contributors.id) FROM contributors GROUP BY SUBSTR(contributors.namesort,1,1) returned: A 3 X 1 Å 1 So the collation sequence is in fact used by the GROUP BY. The ICU ordering for a given locale can be checked here: http://demo.icu-project.org/icu-bin/locexp. E.g. for english and german locales Å is considered a variant of A, whereas for danish and swedish locales Å is sorted after Z. Test3: SELECT SUBSTR(contributors.namesort,1,1), count(distinct contributors.id) FROM contributors GROUP BY SUBSTR(contributors.namesort,1,1) COLLATE de_DE ORDER BY contributors.namesort COLLATE de_DE returned this strange result: Å 1 A 3 X 1 Test 4: SELECT SUBSTR(contributors.namesort,1,1), count(distinct contributors.id) FROM contributors GROUP BY SUBSTR(contributors.namesort,1,1) COLLATE de_DE ORDER BY contributors.namesort returned: A 3 X 1 Å 1 So the GROUP BY clause seems to have no effect when the ORDER BY is following.
>So the GROUP BY clause seems to have no effect when the ORDER BY is following. I meant the COLLATE in the GROUP BY clause seems to have no effect when the ORDER BY is following.