JetBrains / JetBrains/Exposed

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

Open
#1,459 2 comments 0 reactions 0 assignees View on GitHub
Dominant language
Kotlin
Stars
9.3k
Forks
798
Avg merge
4d 2h
Merged PRs (30d)
26

Description

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

```kotlin
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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.