Review 13.4 Merged

88072[BUGFIX] Avoid PostgreSQL type issue in LiveSearch providers

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

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
  • Verified V +2 Done
    • Stefan Bürk +2
    • core-ci +1 CI

Issues 3

Files 6

Review log 34

  1. Stefan Bürk commented on patch set 1 02/07/25, 11:05

    Cherry Picked from branch main.
  2. core-ci voted on patch set 1 V +1 02/07/25, 11:13 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/73714

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

  4. Stefan Bürk merged patch set 2 02/07/25, 11:27