Review main Merged

88030[BUGFIX] Avoid PostgreSQL type issue in LiveSearch providers

Stefan Bürk CR +2 V +2 Hard +132 −123 updated 02/07/25, 11:26

Commit message

With #106040 general search field lists has been streamlined to
retrieve it from TcaSchema single source of truth, streamlining
related code for the LiveSearch related provider implementation.

PostgreSQL is really picky related to value or field types when
used in query constraints, for example using SQL functions which
expects string (TEXT) values but recieves an integer or another
type and awarding this with errors like following:

  Undefined function: 7 ERROR: function lower(integer) does not exist

Using integer field values for LIKE queries does not work for
PostgreSQL, beside not making much sense to search for '%admin%'
on a integerfield like 'pages.uid'.

Integer values may still valid to be searched partly on integer
fields, requiring to cast values to a compatible TEXT type first.
Searching for non-integer values does not make much sense, but at
least should not break with an error.

This change completes #106040 work for all databases ...

* Removing like search constraints for hardcoded 'uid' and
  'pid' fields in case the search tearm is not an integer.
* Enhancing `ExpressionBuilder` methods `like()` and `notLike()`
  to cast provided field to text field representation only for
  PostgreSQL, literally providing matching behaviour from MySQL,
  MariaDB and SQLite for integer columns.
* Modifing `LikeWildcardTest` functional test to reflect the added
  type-cast to `ExpressionBuilder` methods and simplifing the setup
  to easier read the expected results instead of obscure build up.
* Casting field value to text representation on PostgresSQL for the
  direct created lower-case like query constraints in the providers.
* Slightly modifing the constraint build code for searchfields to
  use `AND` composition only when a second part is added to make
  it less confusing.

TCA field search option `case` is not really enforcable in all
cases, requiring specific collation on column and values which
is not ensured. Corresponding todo's are added, out-of-scope of
this change and a bigger general issue.

Review

  • Code review CR +2 Done
    • Stefan Bürk +2
    • Anja Leichsenring +1
    • Christian Kuhn +1
    • Benni Mack +1
  • Verified V +2 Done
    • Stefan Bürk +2
    • Anja Leichsenring +1
    • Christian Kuhn +1
    • core-ci +1 CI

Issues 3

Files 6

Review log 3347

Show 1322 earlier messages
  1. Stefan Bürk uploaded patch set 1 02/05/25, 09:11

  2. core-ci voted on patch set 1 V +1 02/05/25, 09:17 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73564

  3. core-ci commented on patch set 1 02/05/25, 09:39 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73565

  4. Stefan Bürk uploaded patch set 2 V +1 02/05/25, 09:45

  5. core-ci voted on patch set 2 V +1 02/05/25, 09:51 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73566

  6. Stefan Bürk updated the commit message in patch set 3 V +1 02/05/25, 09:54

  7. core-ci voted on patch set 3 V +1 02/05/25, 10:01 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73567

  8. Stefan Bürk uploaded patch set 4 V +1 02/05/25, 11:52

  9. core-ci voted on patch set 4 V -1 02/05/25, 12:02 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73575

  10. Stefan Bürk uploaded patch set 5 V -1 02/05/25, 12:32

  11. core-ci voted on patch set 5 V +1 02/05/25, 12:39 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73578

  12. core-ci commented on patch set 5 02/05/25, 12:59 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73579

  13. Stefan Bürk updated the commit message in patch set 6 V +1 02/05/25, 13:01

  14. core-ci voted on patch set 6 V +1 02/05/25, 13:07 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73583

  15. Oliver Hader commented on patch set 6 02/05/25, 13:53

    ExpressionBuilderTest.php:149

    I don't think we should aim for this level of SQL complexity in general...
  16. Oliver Hader commented on patch set 6 02/05/25, 14:25

    DatabaseRecordProvider.php:380

    I'm not much into PostgreSQL anymore these days... I'm wondering, why we might not just use * `LIKE` (MySQL) & `ILIKE` (Postgres) and * `LIKE BINARY` (MySQL) & `LIKE` (Postgres) here. That might result in adding a new parameter like ``` $expr->like( string $fieldName, mixed $value, ?string $escapeChar = null, bool $caseInsensitive = true ) ``` Or having new dedicated methods `$expr->caseInsensitiveLike` and `$expr->caseInsensitiveNotLike`
  17. Stefan Bürk commented on patch set 6 02/05/25, 16:27

    DatabaseRecordProvider.php:380 Reply

    Thanks for the input, will look into it again. Coming into mind (not verified): * `LIKE BINARY` for integer fields may not work for integer fields * There was something with collation, need to check that again / test it.
  18. Stefan Bürk updated the commit message in patch set 7 V +1 02/06/25, 08:40

  19. Stefan Bürk commented on patch set 7 CR -2 02/06/25, 08:40

    Testing an alternative ...
  20. core-ci voted on patch set 7 V +1 02/06/25, 08:54 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73633

  21. Stefan Bürk uploaded patch set 8 V +1 02/06/25, 13:06

    Copied Votes: * Code-Review-2 (copy condition: "changekind:NO_CHANGE OR **is:MIN**")
  22. Stefan Bürk commented on patch set 8 CR 0 02/06/25, 13:06

    DatabaseRecordProvider.php:380 Reply

    Talking about `like()/notLike()` in `ExpressionBuilder` to be case-sensitive/case-insensitve we may implement this that way. But we still have an issue wit SQLite then. SQLite LIKE is case-insensitive by default and there is no way to make it case-sensitive directly. SQLite provides a `GLOB` feature which can be used to do it in a case-sensitive manner, but that requires a replacement of the wildchar `%` with `*` for the `$value`, which is not that easy because it could be a placeholder `:dcValue1` and would require to inject SQL code arount the value to do it in the query. I did not touched these places here as they are here for a longtime now, even not making much sense. A real enforcment of `case-sensitive` is not that easy, and not in the scope of this bugfix and should be discusses and solved in a dedicated change. Main goal of this change is and should be to have it working for postgres again, and not dealing with "revealed" bigger issues in the same change. We could either cast the field for the current lower() for postgres generally. Another solution may be to read the (cached) database schema information to determine the database field type and add it for postgres only if it is not a compatible string type (TEXT). Another thing to remember is, that LIKE (MySQL/MariaDB) may become case-sensitive if the column colation is a `*_BIN` collation - and the LOWER() trick is then the only way to ensure a case-insensitive check here. I would still postpone the general case-senstivie/case-insensitve implementatio, enforcment posibilities or drop discussion into another change and concentrate within this change to * keep the generalized searchFields change * make it working for Postgress in case DB field integer is hit. Don't know if we really should revert #106040. Changed it to do the cast only for postgres. Would that be fine for you as a direct bugfix ? https://review.typo3.org/c/Packages/TYPO3.CMS/+/88051 adds additional tests, some as preparation for later deeper/bigger work on the general case-sensitive/case-insensitive topic - but allows to enable integer like tests for postgres with this change.

    ExpressionBuilderTest.php:149 Reply

    If I understand this correctly, you mean that we should not cast this for all platforms in general, only for Postgres ? If we want to have compat for like()/notLike() the same over all platforms as much as possible, we need to add a generic cast to text for postgres. Doing this conditional based reading the table/field identifier may not be suitable due to beeing aliased - and would require to read database schema (we have a cached one). Or is your take away let's crash that on postgres ? I changed the field cast for like/notLike only for PostgreSQL and not for all platforms, and reverted the unit-test modification. Hope that is okay for you.
  1. core-ci voted on patch set 8 V +1 02/06/25, 13:15 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73655

  2. core-ci commented on patch set 8 02/06/25, 13:33 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73656

  3. Stefan Bürk rebased onto patch set 9 V +1 02/06/25, 14:22

  4. core-ci voted on patch set 9 V +1 02/06/25, 14:28 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73666

  5. Stefan Bürk updated the commit message in patch set 10 V +1 02/06/25, 14:29

  6. core-ci voted on patch set 10 V +1 02/06/25, 14:36 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73667

  7. Stefan Bürk commented on patch set 10 02/06/25, 14:56

    DatabaseRecordProvider.php:380 Reply

  8. Stefan Bürk commented on patch set 10 02/06/25, 14:57

    Reply

    Done
  9. Christian Kuhn voted on patch set 10 CR +1 V +1 02/06/25, 17:06

  10. Anja Leichsenring voted on patch set 10 CR +1 V +1 02/07/25, 08:59

  11. Oli Bartsch voted on patch set 10 CR +1 V +1 02/07/25, 09:09

  12. Benni Mack commented on patch set 10 CR -1 02/07/25, 10:24

    I suggest we revert the original patch, this is fixing an issue we need to address separately
  13. Oli Bartsch voted on patch set 10 CR 0 V 0 02/07/25, 10:26

  14. Benni Mack commented on patch set 10 02/07/25, 10:31

    PageRecordProvider.php:343

    looks like we're actually replacing searchConstraint instead of doing this additive?
  15. Stefan Bürk uploaded patch set 11 CR +1 V +1 02/07/25, 10:51

  16. Stefan Bürk commented on patch set 11 02/07/25, 10:52

    PageRecordProvider.php:343 Reply

    Good catch ! $searchConstraint should be the first element passed to the `and()` here, similar to how it is done in `DatabaseRecordProvider`. Pushed the fix for it.
  17. Stefan Bürk commented on patch set 11 02/07/25, 10:55

    DatabaseRecordProvider.php:380 Reply

    Reesolving this discussion as this is a dedicated thing (now) and needs to be addressed in a dedicated change (for example the linked poc) anyway.

    ExpressionBuilderTest.php:149 Reply

    Minimized / reduced it only to PostgreSQL. Resolving this discussion for now, as this is required to have the same behaviour like MySQL/MariaDB/SQLite.
  18. core-ci voted on patch set 11 V +1 02/07/25, 10:57 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73710

  19. Benni Mack commented on patch set 11 CR +1 02/07/25, 10:57

    Thanks, I think it's OK (re-stating my comment before)
  20. Stefan Bürk commented on patch set 11 02/07/25, 11:00

    Reply

    You mean, no need to revert the other patch and take this fix ? (Just to make that clear for other reviewers).
  21. Benni Mack commented on patch set 11 02/07/25, 11:01

    Reply

    yes
  22. Christian Kuhn voted on patch set 11 CR +1 V +1 02/07/25, 11:02

  23. Anja Leichsenring voted on patch set 11 CR +1 V +1 02/07/25, 11:04

  24. Stefan Bürk voted on patch set 11 CR +2 V +2 02/07/25, 11:26

  25. Stefan Bürk merged patch set 12 02/07/25, 11:26