org.postgresql.util.PSQLException: ERROR: column "data" is of type bytea but expression is of type oid

[nlgtuankiet]

SQLDelight Version

2.0.0

Application Operating System

JVM + Postgres

Describe the Bug

user330315 describe the bug very well in his StackOverflow answer: https://stackoverflow.com/a/34128743

Postgres has two different "BLOB" types: bytea, which is essentially what the SQL standard defines as a BLOB. And "large objects" which is more or less a "pointer" to binary storage (it's still stored inside the DB).

The Postgres JDBC has always treated "large objects" as the equivalent to BLOB (which I have never understood) and thus ps.setNull(++index, java.sql.Types.BLOB); makes the driver think you are dealing with a "large object" (aka "oid") column.

To overcome this, use

ps.setNull(++index, Types.OTHER); alternatively you can use:

ps.setObject(++index, null);

The solution is to use Types.OTHER when the bytea value is null.

class JdbcPreparedStatement(
  private val preparedStatement: PreparedStatement,
) : SqlPreparedStatement {
  override fun bindBytes(index: Int, bytes: ByteArray?) {
    if (bytes == null) {
      // preparedStatement.setNull(index + 1, Types.BLOB) <- this line is the root cause
      // use this instead
      preparedStatement.setNull(index + 1, Types.OTHER)
    } else {
      preparedStatement.setBytes(index + 1, bytes)
    }
  }

For now, a quick workaround for sqdelight 2.0.0 and below is to overwrite the implementation of PreparedStatement#setNull

fun DataSource.asJdbcDriverFix() = object : JdbcDriver() {
    override fun getConnection(): Connection {
        return FixedConnection(connection)
    }

    override fun closeConnection(connection: Connection) {
        connection.close()
    }

    override fun addListener(vararg queryKeys: String, listener: Query.Listener) {
        // No-op. JDBC Driver is not set up for observing queries by default.
    }

    override fun removeListener(vararg queryKeys: String, listener: Query.Listener) {
        // No-op. JDBC Driver is not set up for observing queries by default.
    }

    override fun notifyListeners(vararg queryKeys: String) {
        // No-op. JDBC Driver is not set up for observing queries by default.
    }
}

class FixedConnection(private val c: Connection) : Connection by c {
    override fun prepareStatement(sql: String?): PreparedStatement {
        val ps = c.prepareStatement(sql)
        return object : PreparedStatement by ps {
            override fun setNull(parameterIndex: Int, sqlType: Int) {
                if (sqlType == Types.BLOB) {
                    ps.setNull(parameterIndex, Types.OTHER)
                } else {
                    ps.setNull(parameterIndex, sqlType)
                }
            }
        }
    }
}

val datasource = HikariDataSource()
val jdbcDriver = datasource.asJdbcDriverFix()

Stacktrace

org.postgresql.util.PSQLException: ERROR: column "data" is of type bytea but expression is of type oid
  Hint: You will need to rewrite or cast the expression.
  Position: 103
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
    at app.cash.sqldelight.driver.jdbc.JdbcPreparedStatement.execute(JdbcDriver.kt:279)
    at app.cash.sqldelight.driver.jdbc.JdbcDriver.execute(JdbcDriver.kt:133)

[nlgtuankiet]

May I submit a PR for this @AlecKazakova I tested it, work perfectly for null and not null case.

[YektaDev]

Any updates?