JetBrains / JetBrains/Exposed

Using Transaction.exec changes all referenced columns to be nullable going forward

Open
#2,142 2 comments 0 reactions 1 assignee Claimed by @bog-walk View on GitHub
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

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.