Custom null cases are difficult to work with.

[dakriy]

The use case where there is a value in a database that is equivalent to being a null (without the underlying column being null), is not possible with the library from my research. My specific use case is needing to interpret blank strings as a null value. I tired to implement a custom column type to do this, and extended the IColumnType::readObject method. This did not work as the result row ignores nulls returned from that method and instead runs getObject off of the JDBC ResultSet directly (This is in the ResultRow.create companion function). I also tried replicating how the current nullable works, but this did not seem to be possible from outside the library. Is my use case currently possible?

[Tapac]

Can you please share the SQL you'd like to implement and the Exposed code you use?

[dakriy]

It's not quite a one off case as I have thousands of non-nullable columns in a database where I would like to handle a specific case as null in application code (a blank string). I would also like to be able to save null and have it translate to that value in the database. I need to do this across potentially many different column types as booleans and other types are saved as strings, but can in some cases can be nulled with a blank string. I also know if a column can have this "null" value or not. It's basically a null implementation in a database while avoiding builtin database features like the plague.

The SQL would be pretty standard, something like SELECT A.col1, A.col2, A.col, FROM TABLE A WHERE A.col1 = 'hello'. Maybe this is where I am going wrong and I should be thinking about a solution with case statements or something similar. The main down side to this is having to remember to apply them when working with any query that uses these columns.

To generalize this case I was attempting to create custom column types for these. I started out with the string case by creating an extension class off of VarCharColumnType:

class EmptyableStringColumnType(
    colLength: Int,
    collate: String?,
) : VarCharColumnType(colLength, collate) {
    override fun readObject(rs: ResultSet, index: Int): Any? {
        val value = super.readObject(rs, index)
        return if (value is String && value.isBlank()) null
        else value
    }

    override fun valueToDB(value: Any?): Any? {
        return if (value == null) " "
        else super.valueToDB(value)
    }
}

fun Table.emptyableVarChar(
    name: String, length: Int, collate: String? = null
): Column<String?> = registerColumn<String>(
    name, EmptyableStringColumnType(length, collate)
).nullable()

This mostly works. I can save nulls and they get converted to a blank string in the DB and regular strings are returned normally. Although, obviously not null checks would not work as expected and the column isn't actually nullable (I don't care too much about this), so saving an actual null to the database would fail. The problem comes when trying to retrieve a blank string from the database as null. They are returned as a blank strings because of ResultRow.kt:91:

        fun create(rs: ResultSet, fieldsIndex: Map<Expression<*>, Int>): ResultRow {
            return ResultRow(fieldsIndex).apply {
                fieldsIndex.forEach { (field, index) ->
                    val value = (field as? Column<*>)?.columnType?.readObject(rs, index + 1) ?: rs.getObject(index + 1)
                    data[index] = value
                }
            }
        }

When a null is returned from the readObject method, it goes and re-runs the getObject method from the JDBC. This overwrites the null that I wanted with blank string. I also tried solutions using a custom wrapper type on a string, but it feels clunky and would potentially have to create 2 versions for every custom type I have in the database, one for the nullable case and one for the non-nullable case.

Thank you very much for your time!

[spand]

+1

We would like to use something similar for supporting "unknown" values of an enumerationByName column. I am not sure this is all we need to but it seems like a step on the way.

[Tapac]

It's the bug for sure. Thank you @naftalmm for PR.