Using Transaction.exec changes all referenced columns to be nullable going forward
- Dominant language
- Kotlin
- Stars
- 9.3k
- Forks
- 798
- Avg merge
- 4d 2h
- Merged PRs (30d)
- 26
Description
Feel free to tell me I'm doing it wrong, but when I try to use exposed to generate prepared statements that I execute directly on the transaction, it changes all the column types that I inserted to be nullable for all future inserts. My use case is that I want to support inserts that ignore conflicts, here's a contrived example:
```kotlin
object IdTests : Table() {
val id = long("id").autoIncrement()
val value = varchar("value", 255)
override val primaryKey = PrimaryKey(id)
}
object IdTestService {
fun createSchema() {
transaction {
SchemaUtils.create(IdTests)
}
}
fun insertTest() {
transaction {
val statement1 = InsertStatement(IdTests).apply {
this[IdTests.value] = "test1"
}
this.exec(statement1.prepareSQL(this), statement1.arguments().first())
// INSERT INTO idtests ("value") VALUES ('test1')
val statement2 = InsertStatement(IdTests).apply {
this[IdTests.id] = -2
this[IdTests.value] = "test2"
}
val sql = """
${statement2.prepareSQL(this)}
ON CONFLICT DO NOTHING
RETURNING ${IdTests.id.name}
""".trimIndent()
this.exec(sql, statement2.arguments().first(), StatementType.MULTI)
// INSERT INTO idtests (id, "value") VALUES (-2, 'test2')
val statement3 = InsertStatement(IdTests).apply {
this[IdTests.value] = "test3"
}
this.exec(statement3.prepareSQL(this), statement3.arguments().first())
// INSERT INTO idtests (id, "value") VALUES (NULL, 'test3')
// ERROR: null value in column "id" of relation "idtests" violates not-null constraint
}
}
}
```
Notice the generated SQL for `statement1` and `statement3`. In `statement1`, exposed knows the id column has a default value in the DB and leaves it out of the insert statement, but in `statement3` (and all future insert statements), it will try to insert null because the column type has been modified permanently in the execution of statement2.
This is highly problematic for any column that uses a DB generated default, as all future inserts will try to insert NULL.
I think I've tracked it down to [this recent change](https://github.com/JetBrains/Exposed/pull/1973):
https://github.com/JetBrains/Exposed/blob/3fafec1a31f88b1ea4fda13fdf17c6dc182e5693/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/Transaction.kt#L253-L257
Contributor guide
Assessment
This issue has not been assessed yet.