SQLite: param mapping a DateTime value for use in a WHERE comparison clause involving a DATE (TEXT) column causes incorrect/inconsistent results

[savemetenminutes]

Bug Report

Summary

According to https://www.sqlite.org/datatype3.html#date_and_time_datatype there is no discrete data type for storing DATE or DATETIME values. Instead all values are treated as TEXT for all purposes. When it comes to comparisons, this becomes a problem (see "How to reproduce").

Current behavior

A DATE column value of '2025-05-31' is less than '2025-05-31 00:00:00'

Expected behavior

There are two possibilities to alleviate this issue.

1. DateTimeInterface PHP params should be treated similar to how they are treated for INSERT and UPDATE queries. The time portion is omitted. I'm not sure whether obtaining the actual value would happen in (partial stack trace):

AbstractQuery.php:395, Doctrine\ORM\AbstractQuery->processParameterValue()
Query.php:422, Doctrine\ORM\Query->resolveParameterValue()
Query.php:366, Doctrine\ORM\Query->processParameterMappings()
Query.php:287, Doctrine\ORM\Query->_doExecute()
AbstractQuery.php:935, Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache()
AbstractQuery.php:891, Doctrine\ORM\AbstractQuery->execute()
AbstractQuery.php:689, Doctrine\ORM\AbstractQuery->getResult()
...

...or (partial stack trace again):

DateTimeImmutableType.php:34, Doctrine\DBAL\Types\DateTimeImmutableType->convertToDatabaseValue()
Connection.php:1887, Doctrine\DBAL\Connection->getBindingInfo()
Connection.php:1828, Doctrine\DBAL\Connection->bindParameters()
Connection.php:1102, Doctrine\DBAL\Connection->executeQuery()
FinalizedSelectExecutor.php:27, Doctrine\ORM\Query\Exec\FinalizedSelectExecutor->execute()
Query.php:296, Doctrine\ORM\Query->_doExecute()
AbstractQuery.php:935, Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache()
AbstractQuery.php:891, Doctrine\ORM\AbstractQuery->execute()
AbstractQuery.php:689, Doctrine\ORM\AbstractQuery->getResult()
...

...but obviously the logic would need to be depepndent on the platform. Tested in MySQL and MariaDB, these comparisons work as expected (the DATE value is assumed to refer to time 00:00:00 for purposes of comparison).

2. The second option is to append a '00:00:00' to the DATE column as in:

SELECT
    t0_.id          AS id_0,
    t0_.date_expire AS date_expire_1
FROM
    test_date_expire t0_
WHERE
    t0_.date_expire || ' 00:00:00' = '2025-05-31 00:00:00'

I would assume this is a much less performant approach. OTOH this is consistent with the general SQL behavior (I'm yet to test this in PostgreSQL and SQL Server) where the comparison is performed more accurately and satisfies the conditions
'2025-05-31 00:00:01' > '2025-05-31' (this one would fail using the 1. approach as dropping the time part 00:00:00 would make the two strings equal)
'2025-05-31 00:00:00' = '2025-05-31'

How to reproduce

CREATE TABLE
    test_date_expire
(
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    date_expire DATE NOT NULL
);

INSERT INTO
    test_date_expire (date_expire)
VALUES ('2025-05-31');

SELECT
    t0_.id          AS id_0,
    t0_.date_expire AS date_expire_1
FROM
    test_date_expire t0_
WHERE
    t0_.date_expire = '2025-05-31 00:00:00'

(empty result set)

SELECT
    t0_.id          AS id_0,
    t0_.date_expire AS date_expire_1
FROM
    test_date_expire t0_
WHERE
    t0_.date_expire > '2025-05-31 00:00:00'

(empty result set)

SELECT
    t0_.id          AS id_0,
    t0_.date_expire AS date_expire_1
FROM
    test_date_expire t0_
WHERE
    t0_.date_expire < '2025-05-31 00:00:00'

+----+-------------+
|id_0|date_expire_1|
+----+-------------+
|1   |2025-05-31   |
+----+-------------+