incorrect behavior of the SELECT statement when specifying the maximum date [H2]
- 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
Assessment
This issue has not been assessed yet.