Does jooq record use column indexes when fetching data?

Question

I'm investigating an issue where we are seeing strange exceptions related to jooq trying to populate a generated Record class, where it gets data type errors because it uses java.sql.ResultSet::getXXX(int) (column index based) to fetch data.

The part of the stacktrace that I can share looks like:

Caused by: java.sql.SQLDataException: Value 'ABC' is outside of valid range for type java.lang.Byte
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:114)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:92)
    at com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1423)
    at com.mysql.cj.jdbc.result.ResultSetImpl.getByte(ResultSetImpl.java:710)
    at com.zaxxer.hikari.pool.HikariProxyResultSet.getByte(HikariProxyResultSet.java)
    at org.jooq.tools.jdbc.DefaultResultSet.getByte(DefaultResultSet.java:124)
    at org.jooq.tools.jdbc.DefaultResultSet.getByte(DefaultResultSet.java:124)
    at org.jooq.impl.CursorImpl$CursorResultSet.getByte(CursorImpl.java:688)
    at org.jooq.impl.DefaultBinding$DefaultByteBinding.get0(DefaultBinding.java:1783)
    at org.jooq.impl.DefaultBinding$DefaultByteBinding.get0(DefaultBinding.java:1755)
    at org.jooq.impl.DefaultBinding$AbstractBinding.get(DefaultBinding.java:871)
    at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1725)

Which is definitely a column mismatch caused using wrong column index.

The issue comes up because we are using the record on an evolving schema so the underlying table contains columns not available in the record definition.

Note that the actual code that triggers this is:

jooq.insertInto(TABLE)
    .set(TABLE.COL, "ABC")
    .returning(TABLE.asterisk())
    .fetchOne();

What scares me a bit here is, that if it does indeed use column indexes by design, that will make schema evolution somewhat hard (how would you delete a column from a running application).

Long story (sorry), question is: does jooq use column index in records generated by jooq-generator and is there a way to use column names instead?

One thing I have noticed is that when I compare the documentation at https://www.jooq.org/doc/3.14/manual/code-generation/codegen-records/ the shown generated records does not match what the generator actually generates. The documentation show methods like:

// Every column generates a setter and a getter
@Override
public void setId(Integer value) {
    setValue(BOOK.ID, value);
}

But in reality the generated code looks like (taken from jOOQ-examples):

/**
 * Setter for <code>PUBLIC.BOOK.ID</code>.
 */
public void setId(Integer value) {
    set(0, value);
}

Btw we are using jooq 3.14.15.

Answer 1

Ok, this was a local error. What actually caused the issue was that our code was written as:

jooq.insertInto(TABLE)
    .set(TABLE.COL, "ABC")
    .returning(TABLE.asterisk())
    .fetchOne();

And the TABLE.asterisk() is what messes it up (since on the database that contains extra columns it does not return what jooq expects). Fortunately removing it solves the problem so our code now looks like:

jooq.insertInto(TABLE)
    .set(TABLE.COL, "ABC")
    .returning()
    .fetchOne();