Major Description
The "coalesce" function incorrectly asks the index to do "is not null" for the first property:
SELECT a.*
FROM [dam:Asset] AS a
WHERE ((COALESCE(a.[jcr:lastModified], a.[jcr:created]) < cast('2023-05-08T20:51:06.239+03:00' AS date))
OR (COALESCE(a.[jcr:lastModified], a.[jcr:created]) = cast('2023-05-08T20:51:06.239+03:00' AS date)
[dam:Asset] as [asset] /* lucene:fragments-9(/oak:index/fragments-9)
+jcr:lastModified:[-9223372036854775808 TO 9223372036854775807]
*/
This is because the Coalesce implementation uses an incorrect "getPropertyExistence" method. It is implemented as follows, so that it implies the first operand is not null, which is incorrect: the first operand can be null. Even the second operand can be null; just the combination can't be null - but there seems to be no good reason to inform the index to do this.
// this is wrong:
@Override
public PropertyExistenceImpl getPropertyExistence() {
PropertyExistenceImpl pe = operand1.getPropertyExistence();
return pe != null ? pe : operand2.getPropertyExistence();
}