For Postgres DB, sorting by text field doesn't work

[vlad-ghita]

For Postgres DB, sorting by text field doesn't work.

Details

Question	Answer
Relevant Bolt Version	5.1.0
Install type	Composer install
BC Break	Not relevant
PHP version	Not relevant
Web server	Not relevant (symfony server) (whatever that is)
Database	Postgres 13.5

Reproduction

1. Have a Postgres DB
2. Have a content type with text field (eg: name).

countries:
    name: Countries
    singular_name: Country
    fields:
        name:
            type: text

3. Browse contenttype listing in admin.
4. Sorting on name field doesn't work.

Bug summary

This query is not working as expected:

SELECT b0_.id             AS id_0,
       b0_.content_type   AS content_type_1,
       b0_.status         AS status_2,
       b0_.created_at     AS created_at_3,
       b0_.modified_at    AS modified_at_4,
       b0_.published_at   AS published_at_5,
       b0_.depublished_at AS depublished_at_6,
       b0_.author_id      AS author_id_7,
       b2_.value          AS b2_value,
       LOWER(CAST(b2_.value AS CHAR)) AS b2_value_cast
FROM bolt_content b0_
         LEFT JOIN bolt_field b1_ ON b0_.id = b1_.content_id AND b1_.type IN
                                                                 ('generic', 'checkbox', 'collection', 'text', 'slug', 'imagelist', 'block', 'file', 'email', 'hidden', 'filelist',
                                                                  'select', 'html', 'templateselect', 'markdown', 'data', 'textarea', 'set', 'embed', 'number', 'date', 'image',
                                                                  'article', 'redactor')
         LEFT JOIN bolt_field_translation b2_ ON b1_.id = b2_.translatable_id
WHERE b0_.content_type = 'countries'
  AND b0_.status <> 'unknown'
  AND b1_.name = 'title'
  AND b0_.id IN (7, 6, 5, 9, 8)
ORDER BY LOWER(CAST(b2_.value AS CHAR)) DESC

Specifically, order field should be cast to TEXT, not CHAR.

Culprit code is in Cast.php at line 38.

[vlad-ghita]

@bobdenotter

Fix should be pretty simple IMO.

Please have a look at specified line. https://github.com/bolt/core/blob/master/src/Doctrine/Query/Cast.php#L38