Merge conflict Gerrit cannot merge this change. It needs a rebase onto main.

Review main Work in progress Quiet

95162[BUGFIX] Handle empty dates consistently in Extbase

Benjamin Kott CR 0 V 0 Hard +767 −1 updated 10/23/26, 10:23

Commit message

A TCA type=datetime column that is not nullable stores an empty date as
its empty value instead of SQL NULL: 0 in an integer column, and
"0000-00-00", "0000-00-00 00:00:00" or "00:00:00" in a native one. A
nullable native DATE or DATETIME column may still hold "0000-00-00"
from before it was nullable. DateTimeFactory maps all of these back to
null. A query for equals($property, null) however became IS NULL, so it
missed those rows and logicalNot() kept them. MySQL matches a zero date
with IS NULL, but not with IS NOT NULL. And Backend::insertObject()
left a null property out of the insert, so the schema default decided
what was stored: "" in a column declared varchar, where updateObject()
and DataHandler write "0", and an error in a native column, which has
no default.

Typo3DbQueryParser now matches a null date as SQL NULL or that value.
This also finds SQL NULL in a column that the TCA does not make
nullable but ext_tables.sql declares DEFAULT NULL.
ColumnMap::storesNullDateTimeAsEmptyValue() names a column that is not
nullable, and insertObject() writes its empty value like updateObject()
does.

Only the OPERATOR_EQUAL_TO_NULL and OPERATOR_NOT_EQUAL_TO_NULL cases
change. Comparison::getOperator() already turns OPERATOR_EQUAL_TO and
OPERATOR_NOT_EQUAL_TO with a null operand into them, and the parser
gets no other comparison: ComparisonInterface is internal, the final
Comparison is its only implementation, and QueryObjectModelFactory
always creates that. A normalization in the parser would repeat this
for a case that cannot occur.

PostgreSQL cannot hold a zero date and raises an error when a native
DATE or DATETIME column is compared against one, so such a column is
still compared against NULL alone there. MySQL and MariaDB hold it
where NO_ZERO_DATE is off, which is what DataHandler writes into a
native column with nullable=false.

Test this change

Run a command in your clone of the TYPO3 Core. Then try what the change does and vote on Verified in Gerrit. How to review a patch

Check out patch set 3
git fetch https://review.typo3.org/Packages/TYPO3.CMS refs/changes/62/95162/3 && git checkout FETCH_HEAD
Cherry-pick onto main
git fetch https://review.typo3.org/Packages/TYPO3.CMS refs/changes/62/95162/3 && git cherry-pick FETCH_HEAD

Review

  • Code review CR 0 Needs a vote
  • Verified V 0 Needs a vote
    • core-ci +1 CI

Issues 1

Files 11

Review log 910

  1. Benjamin Kott uploaded patch set 1 08/07/26, 06:48

  2. Benjamin Kott uploaded patch set 2 08/07/26, 13:20

  3. Benjamin Kott marked it work in progress 10/16/26, 14:05

  4. Benjamin Kott wrote 10/16/26, 14:05

    Unset private
  5. Benjamin Franzke commented on patch set 2 10/16/26, 15:06

    ColumnMap.php:150 Unresolved

    I think this is is wrong and should not check `this->dateTimeStorageFormat === null` at all. It causes legacy mariadb native dbType DateTime non-nullable fields (with explicit nullable=false) not to be considered. See typo3/sysext/core/Tests/Functional/DataHandling/DataHandler/DatetimeLegacyTest.php for more information.
  6. Benjamin Franzke commented on patch set 2 10/16/26, 15:06

    Typo3DbQueryParser.php:392

    Unneded, normalization already happens in `$comparison->getOperator()`.
  7. Benjamin Kott uploaded patch set 3 10/16/26, 15:32

  8. core-ci voted on patch set 3 V +1 10/16/26, 15:42 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/111374

  9. Markus Klein commented on patch set 3 10/20/26, 15:26

    Not sure about the 0000-00-00 stuff. That's kind of legacy in MySQL and not working with any of "strict" or "NO_ZERO_DATE" SQL mode. For v15 I'd suggest we ensure that zero-date can't happen anymore by enforcing correct TCA/SQL?
  10. Benjamin Franzke commented on patch set 3 10/23/26, 10:23

    Reply Unresolved

    I agree, we should have deprecated it in v14. We "only" ensured that nullable for native datetime fields is enabled by default, but it was still possible to *explicitly* set `nullable=false`. We should disallow that and ship a repeatable upgrade wizard that migrates all (legacy) 0000-00-00 values to `null`. Given the fact that is requires non-strict mysql (or sqlite), do you think such a change would be fine as a breaking change? Anyway, whatever decision we make there: That doesn't help much for this extbase logic though, since we still have **non-nullable** integer based fields, where the topic is larger, since a lot of code presumes they are not nullable (thinking of StartTimeRestriction and EndTimeRestriction and possibly more).

    Typo3DbQueryParser.php:392 Reply

    Done

    Typo3DbQueryParser.php:410 Unresolved

    we do handle two cases now: ``` $query->equals('myDate', null); $query->logicalNot($query->equals('myDate', null)); ``` but the issue also (correctly) showed that greaterThan (and therefore also lessThan) need to be considered as well. I mean, the issue only mentions `$query->greaterThan('myDate', 0)`, but especially for `$query->lessThan('myDate', new \DateTime('now'))` we need to decide how to handle pseudo-null values (0 for type int, legacy 0000-00-00 for native). *It bascially needs to become a less than + != 0 for integer feelds.* Sorry to put this out as a blocker, but I feel it's not right to support `null` mapping to pseudo-null only for a subset of the `QueryInterface` operators.

    DateTimeStorageLegacyTest.php:31 Unresolved

    Since this one is based on logic in typo3/sysext/core/Tests/Functional/DataHandling/DataHandler/DatetimeLegacyTest.php I just noticed I incorrectly excluded sqlite back then (only focused on mysql because it required the custom sql mode setting). Have now adapted that in: > 97058: [TASK] Run the zero date tests on SQLite as well | https://review.typo3.org/c/Packages/TYPO3.CMS/+/97058 We should adapt that in here later on as well.

    DateTimeStorageTestTrait.php:127 Unresolved

    I think we need to clarify why explicit ´null` values are overwritten by random concrete time/date values.