Review main Merged

84451[BUGFIX] Use ILIKE for like queries with PostgreSQL

Georg Ringer CR +2 V +2 Easy +27 −1 updated 06/08/24, 19:04

Commit message

Use ILIKE instead of LIKE for PostgreSQL as this is case insensitive and
to be in sync with the ExpressionBuilder.

Review

  • Code review CR +2 Done
    • Stefan Bürk +2
    • Garvin Hicking +1
  • Verified V +2 Done
    • Stefan Bürk +2
    • Garvin Hicking +1
    • core-ci +1 CI

Issues 1

Files 3

Review log 1519

  1. Georg Ringer uploaded patch set 1 05/31/24, 19:33

  2. Georg Ringer commented on patch set 1 05/31/24, 19:33

    This change is ready for review.
  3. core-ci voted on patch set 1 V +1 05/31/24, 19:37 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/58534

  4. Andreas Kießling commented on patch set 1 CR +1 V +1 05/31/24, 20:24

    Thanks for taking care!
  5. Stefan Bürk commented on patch set 1 06/03/24, 07:31

    @georg.ringer@gmail.com can we cover this with a functional test ?
  6. Georg Ringer uploaded patch set 2 CR +1 V +1 06/03/24, 19:26

  7. core-ci voted on patch set 2 V -1 06/03/24, 19:39 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/58634

  8. Georg Ringer uploaded patch set 3 V -1 06/03/24, 19:41

  9. core-ci voted on patch set 3 V +1 06/03/24, 19:48 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/58638

  10. Garvin Hicking commented on patch set 3 06/03/24, 20:32

    The performance of ILIKE is a bit (30-50%) slower than LIKE&LOWER, see https://www.visuality.pl/posts/ilike-vs-like-lower-postgres-stories What I'm wonder is that if we generally cast LIKE tk ILIKE, people won't be able to use the "real" PGSQL like anymore, not being able to perform exact case matching, at least using the built-in querybuilder. I don't have an idea what is better, or how to makee both work, so just dropping that here.
  11. Stefan Bürk commented on patch set 3 06/03/24, 21:52

    Reply

    Main question is what people expect. In old days, ilike has been used here at that place, so this is basically a restore that the extbase `like` behaves the same on all systems (cross-dbms same outcome). That was already the reson to use `ILIKE` in the `ExpressionBuilder->like()` implementation or the QueryBuilder. Literally, if some really needs to call like or Postgres there is still the way to create a QueryBuilder instance out of it before adding the like/ilike and add it manually on the `ConcreteQueryBuilder` instance / ExpressionBuilder using the `like()`or comparision() method. The point is, that `%BloG%` should return records with `Blog` and `BLOG` the same on all platforms, as most users (developers) even don't know this special treatment and circumstance on Postgres. Note: The Typo3DbQueryParser needs some love somewhere in the future - and eventually the QueryBuilding step in extbase anyway.
  12. Stefan Bürk commented on patch set 3 06/03/24, 21:54

    Reply

    Think about extensions not testing postgres with Extbase implementation and relaying on that. Doing case-sensitve like on MySQL/MariaDB requires special column charset settings to enforce that behaviour which is a even more unknown knowledge to the majority of TYPO3 Developers. However, you have kind of a valid point on that, which we need to address in a more global scope (for v14 I guess) - as this also needs some additional pre-work in different areas.
  13. Stefan Bürk commented on patch set 3 06/07/24, 21:10

    Reply

    Done
  14. Georg Ringer rebased onto patch set 4 V +1 06/07/24, 21:10

  15. core-ci voted on patch set 4 V +1 06/07/24, 21:14 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/58885

  16. Stefan Bürk voted on patch set 4 CR +1 V +1 06/07/24, 22:58

  17. Garvin Hicking voted on patch set 4 CR +1 V +1 06/08/24, 01:24

  18. Stefan Bürk voted on patch set 4 CR +2 V +2 06/08/24, 18:58

  19. Stefan Bürk merged patch set 5 06/08/24, 19:04