silverstripe-framework: Collation-aware sorting sometimes breaking on PostgreSQL

Affected Version

• silverstripe/framework 4.x-dev
• silverstripe/postgresql 2.x-dev

postgres=# \dOS+
                           List of collations
   Schema   |   Name    | Collate | Ctype |         Description          
------------+-----------+---------+-------+------------------------------
 pg_catalog | C         | C       | C     | standard C collation
 pg_catalog | POSIX     | POSIX   | POSIX | standard POSIX collation
 pg_catalog | default   |         |       | database's default collation
 pg_catalog | ucs_basic | C       | C     | 
(4 rows)

postgres=# show lc_collate;
 lc_collate 
------------
 en_US.utf8
(1 row)

Description

Some tests rely on ICU collation, whereas some databases do not support it out of the box. For example PostgreSQL prior to version 10 does not support it out of the box and relies on the operating system.

This makes SilverStripe ORM tests dependent on the Operating System of PostgreSQL. On some operating systems sorting and ordering won’t always work as expected.

PostgreSQL FAQ section Why do my strings sort incorrectly? has more technical details on that.

Steps to Reproduce

• Install silverstripe/installer:4.x-dev
• Install silverstripe/postgres:2.1.0
• Take PostgreSQL 9.6 alpine docker container postgres:9.6-alpine
• Run tests and see it for yourself

Example:

$ ./vendor/bin/phpunit --testsuite core --filter DataListTest::testSortMixedCase

PHPUnit 5.7.27 by Sebastian Bergmann and contributors.

F                                                                   1 / 1 (100%)

Time: 8.29 seconds, Memory: 200.50MB

There was 1 failure:

1) SilverStripe\ORM\Tests\DataListTest::testSortMixedCase
Failed asserting that two arrays are equal.
--- Expected
+++ Actual
@@ @@
 Array (
     0 => 'Bob'
-    1 => 'bonny'
-    2 => 'jane'
-    3 => 'John'
-    4 => 'sam'
-    5 => 'Steve'
+    1 => 'John'
+    2 => 'Steve'
+    3 => 'bonny'
+    4 => 'jane'
+    5 => 'sam'
     6 => 'steven'
 )

/mnt/bench/ins4_pg/vendor/silverstripe/framework/tests/php/ORM/DataListTest.php:629
/mnt/bench/ins4_pg/vendor/phpunit/phpunit/phpunit:52

FAILURES!
Tests: 1, Assertions: 1, Failures: 1.