88030[BUGFIX] Avoid PostgreSQL type issue in LiveSearch providers
Commit message
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
- typo3/sysext/backend/Classes/RecordList/DatabaseRecordList.php+0 −1
- typo3/sysext/backend/Classes/Search/LiveSearch/DatabaseRecordProvider.php5+26 −30
- typo3/sysext/backend/Classes/Search/LiveSearch/PageRecordProvider.php2+26 −29
- typo3/sysext/core/Classes/Database/Query/Expression/ExpressionBuilder.php+2 −2
- typo3/sysext/core/Tests/Functional/Database/Query/Expression/ExpressionBuilder/LikeTest.php+0 −13
- typo3/sysext/indexed_search/Tests/Functional/Utility/LikeWildcardTest.php+78 −48
Review log 3347
Show 1322 earlier messages
-
Stefan Bürk uploaded patch set 1 02/05/25, 09:11
-
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
-
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
-
Stefan Bürk uploaded patch set 2
V +102/05/25, 09:45 -
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
-
Stefan Bürk updated the commit message in patch set 3
V +102/05/25, 09:54 -
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
-
Stefan Bürk uploaded patch set 4
V +102/05/25, 11:52 -
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
-
Stefan Bürk uploaded patch set 5
V -102/05/25, 12:32 -
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
-
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
-
Stefan Bürk updated the commit message in patch set 6
V +102/05/25, 13:01 -
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
-
Oliver Hader commented on patch set 6 02/05/25, 13:53
-
Oliver Hader commented on patch set 6 02/05/25, 14:25
DatabaseRecordProvider.php:380I'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` -
Stefan Bürk commented on patch set 6 02/05/25, 16:27
DatabaseRecordProvider.php:380ReplyThanks 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. -
Stefan Bürk updated the commit message in patch set 7
V +102/06/25, 08:40 -
Stefan Bürk commented on patch set 7 CR -2 02/06/25, 08:40
Testing an alternative ... -
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
-
Stefan Bürk uploaded patch set 8
V +102/06/25, 13:06Copied Votes: * Code-Review-2 (copy condition: "changekind:NO_CHANGE OR **is:MIN**") -
Stefan Bürk commented on patch set 8 CR 0 02/06/25, 13:06
DatabaseRecordProvider.php:380ReplyTalking 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:149ReplyIf 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.
-
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
-
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
-
Stefan Bürk rebased onto patch set 9
V +102/06/25, 14:22 -
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
-
Stefan Bürk updated the commit message in patch set 10
V +102/06/25, 14:29 -
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
-
Stefan Bürk commented on patch set 10 02/06/25, 14:56
DatabaseRecordProvider.php:380Reply -
Stefan Bürk commented on patch set 10 02/06/25, 14:57
Reply
Done -
Christian Kuhn voted on patch set 10 CR +1 V +1 02/06/25, 17:06
-
Anja Leichsenring voted on patch set 10 CR +1 V +1 02/07/25, 08:59
-
Oli Bartsch voted on patch set 10 CR +1 V +1 02/07/25, 09:09
-
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 -
Oli Bartsch voted on patch set 10 CR 0 V 0 02/07/25, 10:26
-
Benni Mack commented on patch set 10 02/07/25, 10:31
PageRecordProvider.php:343looks like we're actually replacing searchConstraint instead of doing this additive? -
Stefan Bürk uploaded patch set 11
CR +1V +102/07/25, 10:51 -
Stefan Bürk commented on patch set 11 02/07/25, 10:52
PageRecordProvider.php:343ReplyGood 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. -
Stefan Bürk commented on patch set 11 02/07/25, 10:55
DatabaseRecordProvider.php:380ReplyReesolving 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:149ReplyMinimized / reduced it only to PostgreSQL. Resolving this discussion for now, as this is required to have the same behaviour like MySQL/MariaDB/SQLite. -
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
-
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) -
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). -
Benni Mack commented on patch set 11 02/07/25, 11:01
Reply
yes -
Christian Kuhn voted on patch set 11 CR +1 V +1 02/07/25, 11:02
-
Anja Leichsenring voted on patch set 11 CR +1 V +1 02/07/25, 11:04
-
Stefan Bürk voted on patch set 11 CR +2 V +2 02/07/25, 11:26
-
Stefan Bürk merged patch set 12 02/07/25, 11:26
ExpressionBuilderTest.php:149