I stumbled upon a problem that my query worked in MySQL but not SQLite. As it turns out, when using SQLite it is not possible to perform date arithmetic on field references, only on actual integers.

This works

$qb = $this->repo->createQueryBuilder('foo');
$qb->join("foo.bar", "bar")
       ->where("CURRENT_TIMESTAMP() BETWEEN
                            DATE_SUB(foo.time, 1, 'hour')
                     AND
                            DATE_SUB(foo.time, 2, 'hour')
                     ");

generating (roughly)

SELECT foo.time
INNER JOIN bar ON foo.bar_id = bar.id
WHERE CURRENT_TIMESTAMP() BETWEEN
DATETIME(foo.time,"-1 HOUR")
AND
DATETIME(p0_.time,"-2 HOUR");

This does not

$qb = $this->repo->createQueryBuilder('foo');
$qb->join("foo.bar", "bar")
       ->where("CURRENT_TIMESTAMP() BETWEEN
                            DATE_SUB(foo.time, bar.start, 'hour')
                     AND
                            DATE_SUB(foo.time, bar.end, 'hour')
                     ");

Because this will get generated

SELECT foo.time
INNER JOIN bar ON foo.bar_id = bar.id
WHERE CURRENT_TIMESTAMP() BETWEEN
DATETIME(foo.time,"-bar.start HOUR")
AND
DATETIME(p0_.time,"-bar.end HOUR");

My fix adds support to reference other fields in the query, so that the first code example works the same way, but the second one uses SQLite concat symbol and generates the following:

SELECT foo.time
INNER JOIN bar ON foo.bar_id = bar.id
WHERE CURRENT_TIMESTAMP() BETWEEN
DATETIME(foo.time,"-" || bar.start || " HOUR")
AND
DATETIME(p0_.time,"-" || bar.end || " HOUR");

I've got only two concerns regarding this:

• Currently functional tests do not check for this case at all, but I added an SQLite platform test. Is this enough?
• I had to modify PHPDocs in AbstractPlatform to reflect that some methods now accept string or integer as $interval for date arithmetics. I know for sure this is the case for MySQL and, given this fix, SQLite, but other platforms may not support it. This brings inconsistency in what different platforms support