incorrect behavior of the SELECT statement when specifying the maximum date [H2]

[SchweinchenFuntik]

if you use LocalDate.MAX then the h2 database query is executed only in the raw query, and the Exposed query does not work. posgres - error

package sample

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.javatime.CurrentTimestamp
import org.jetbrains.exposed.sql.javatime.date
import org.jetbrains.exposed.sql.statements.StatementType
import org.jetbrains.exposed.sql.transactions.transaction
import java.math.BigDecimal
import java.time.LocalDate
import java.time.LocalDateTime

object MyTable : Table() {
    val date = date("date").defaultExpression(CurrentTimestamp())
    val value = decimal("value", 10, 3)
}

fun main() {
    // val db = pgDB()
    val db = h2DB()
    transaction(db) {
        SchemaUtils.create(MyTable)
        insertVales()
        addLogger(StdOutSqlLogger)
        val from = LocalDate.of(2022, 4, 1)
        val to = LocalDate.MAX.withDayOfMonth(1) // no select
        //val to = Postgres.maxTimestamp.toLocalDate().withDayOfMonth(1) // ok
        // not work
        MyTable.select { MyTable.date.between(from, to) }.orderBy(MyTable.date, SortOrder.DESC)
            .forEach(::println)

        // work
        exec("SELECT public.my.date, public.my.value FROM public.my WHERE public.my.DATE BETWEEN '2022-04-01' AND '2775-09-01' ORDER BY public.my.DATE DESC",explicitStatementType = StatementType.SELECT) {
            while (it.next()) {
                val d = it.getDate("DATE")
                val v = it.getBigDecimal("VALUE")
                println("$d=$v")
            }
        }
    }
}

private fun insertVales() {
    val seed = LocalDate.of(2022, 4, 1)
    val bg = BigDecimal("100")
    val values = listOf(seed to bg, seed.plusMonths(1) to bg, seed.plusMonths(2) to bg)
    for ((d, v) in values) MyTable.insert {
        it[date] = d
        it[value] = v
    }
}

private fun h2DB(): Database {
    val url = "jdbc:h2:mem:regular;DB_CLOSE_DELAY=-1;"
    return Database.connect(url, "org.h2.Driver", user = "sa")
}

object Postgres {
    val minTimestamp: LocalDateTime = LocalDate.ofEpochDay(-4713).atTime(0, 0)
    val maxTimestamp: LocalDateTime = LocalDate.ofEpochDay(294276).atTime(0, 0)
}

private fun pgDB(): Database {
    val url = "jdbc:postgresql://localhost:5432/expose_reproduce"
    return Database.connect(url, "org.h2.Driver", user = "postgres", password = "")
}

LOG: H2

SQL: SELECT MY."DATE", MY."VALUE" FROM MY WHERE MY."DATE" BETWEEN '2022-04-01' AND '+999999999-12-01' ORDER BY MY."DATE" DESC
2022-06-01=100.000
2022-05-01=100.000
2022-04-01=100.000
SQL: SELECT MY."DATE", MY."VALUE" FROM MY WHERE MY."DATE" BETWEEN '2022-04-01' AND '+999999999-12-01' ORDER BY MY."DATE" DESC

LOG: PG if to = LocalDate.MAX.withDayOfMonth(1)

Exception in thread "main" org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: date out of range: "169104628-11-09 BC +02"

if you use Postgres.maxTimestamp then all queries are successful

[naftalmm]

Dates in generated SQL are represented as Strings in ISO format. DBMSs automatically convert them to respectful (date/datetime/timestamp) type during query execution. But LocalDate.MAX.withDayOfMonth(1) is bigger than the upper limit of date type value in PostgreSQL and other DBMS. And while PostgreSQL is issuing a PSQLException in this case, other DBMSs seem to convert it with overflow, resulting in always false condition.

So, basically, it's a DBMS limitation. How could ORM mitigate it?

[SchweinchenFuntik]

probably not much. Millet behavior of ORM and raw SQL is different. The first gives an empty result, and the second - data.

At the same time, raw SQL was taken from the Exposed log