optReference column should allow update { it[column] = nullableValue }

[vlsi]

See Users#cityId

object Cities : Table() {
    val id = integer("id").autoIncrement() // Column<Int>
    val name = varchar("name", 50) // Column<String>

    override val primaryKey = PrimaryKey(id, name = "PK_Cities_ID")
}

object Users : Table() {
    val id = varchar("id", 10) // Column<String>
    val name = varchar("name", length = 50) // Column<String>
    val cityId = optReference("city_id", Cities.id) // Column<Int?>

    override val primaryKey = PrimaryKey(id, name = "PK_User_ID") // name is optional here
}

Expected:

Users.insert {
    it[cityId] = null // should work, but it does not
    it[cityId] = 42 // should work, but it does not
}

Actual: code does not compile.

I suspect the issue is with UpdateBuilder#setWithEntityIdValue:

Exposed/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/statements/UpdateBuilder.kt

Lines 33 to 38 in d592472

	@JvmName("setWithEntityIdValue")
	operator fun <S : Comparable<S>, ID : EntityID<S>, E : S?> set(column: Column<ID>, value: E) {
	require(!values.containsKey(column)) { "$column is already initialized" }
	column.columnType.validateValueBeforeUpdate(value)
	values[column] = value
	}

The type of optReference(...) is Column<EntityID<...>?> which is not compatible with ID : EntityID<E>.

It looks like the workaround is to permit Column<ID?>.
For instance,

operator fun <S : Comparable<S>, ID : EntityID<S>?, E : S?> set(column: Column<ID>, value: E)

Unfortunately, that still allows users to store null to non-nullable columns (however, even the current code allows them to do so).

[vlsi]

Workaround is

@Suppress("UNCHECKED_CAST")
operator fun <T, S : Comparable<S>, ID : EntityID<S>?, E : S?> UpdateBuilder<T>.set(column: Column<ID>, value: E) =
    // see https://github.com/JetBrains/Exposed/issues/1275
    set(column as Column<EntityID<S>>, value)

[vlsi]

A sad case is

Table2.update {
    it[table1] = null // table1 is optional reference
}

both overloads set(Column<S>, S) and set(Column<EntityID<S>?>, S?) suit :-/

[MiaYi]

Is there any update on this? or a temporary solution to update optional reference with a simple id value?

[vlsi]

I tried Exposed 0.37.3, and now it forbids nullable value for nullable foreign key fields.

For instance:

val optionalInt: Int? = 42
Users.insert {
    it[cityId] = optionalInt // should work, however fails with Type mismatch: inferred type is String? but TypeVariable(S) was expected
}

[vlsi]

@Tapac , I'm inclined the issue should be reopened as Exposed still does not allow it[column] = nullableValue syntax.

[StefanLiebig]

Is there a workaround to achieve that?

[vlsi]

@StefanHUB , my current workarounds (exposed 0.37.3) are

fun<ID: Comparable<ID>, T : Entity<ID>> EntityClass<ID, T>.testCache(id: ID) =
    testCache(EntityID(id, table))

@Suppress("UNCHECKED_CAST")
operator fun <T, S : Comparable<S>, ID : EntityID<S>?, E : S?> UpdateBuilder<T>.set(column: Column<ID>, value: E) =
    // see https://github.com/JetBrains/Exposed/issues/1275
    set(column as Column<S?>, value)

[StefanLiebig]

@vlsi Thanks. This works.

[vlsi]

My previous WA included a bug: it allowed to assign nullable value to a non-nullable column.

The better WA is as follows:

@Suppress("UNCHECKED_CAST")
operator fun <T, S : Comparable<S>, ID : EntityID<S>> UpdateBuilder<T>.set(column: Column<ID?>, value: S?) =
    // see https://github.com/JetBrains/Exposed/issues/1275
    set(column as Column<S?>, value)

The same pattern works for "custom casts".

For instance, my database uses numeric(20) keys, however, it is convenient to use Longs in tests (since most of the time, the keys fit in Long).

So I created the following:

@JvmName("setEntityIdLong")
operator fun <T, ID: EntityID<BigDecimal>> UpdateBuilder<T>.set(column: Column<ID>, value: Long) =
    set(column, BigDecimal(value))

@Suppress("UNCHECKED_CAST")
@JvmName("setEntityIdLongNullable")
operator fun <T, ID: EntityID<BigDecimal>> UpdateBuilder<T>.set(column: Column<ID?>, value: Long?) =
    // see https://github.com/JetBrains/Exposed/issues/1275
    set(column as Column<BigDecimal?>, value?.let { BigDecimal(it) })

It enables "automatic" cast to BigDecimal in expressions like

Objects.merge {
    it[id] = 9164178420001991212 // id is decimal(...) column (which maps to BigDecimal)
}