We have a user table we can select from like so...
Result<Record1<PortalUserRecord>> userList = dsl.select(Tables.PORTAL_USER).from(Tables.PORTAL_USER).fetch();
Sometimes this works as expected (returns all users). However ussually it seems to do this...
Caused by: org.jooq.exception.DataAccessException: SQL [select cast (row ("portal_user".*) as "portal_user") as "portal_user" from "portal_user"]; Error while reading field: cast (row ("planglobal"."portal_user".*) as "planglobal"."portal_user"), at JDBC index: 1
at org.jooq_3.17.4.POSTGRES.debug(Unknown Source)
at org.jooq_3.17.4.POSTGRES.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:3307)
at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:678)
at org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1464)
at org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1425)
at org.jooq.impl.CursorImpl.fetchNext(CursorImpl.java:238)
at org.jooq.impl.AbstractCursor.fetch(AbstractCursor.java:177)
at org.jooq.impl.AbstractCursor.fetch(AbstractCursor.java:88)
at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:265)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:341)
at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:290)
at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2837)
at com.sferion.planglobal.backend.admin.UserJooqAdmin.listAll(UserJooqAdmin.java:125)
at com.sferion.planglobal.ui.crud.AbstractJooqCrudView.<init>(AbstractJooqCrudView.java:68)
at com.sferion.planglobal.ui.crud.CompanyDependentJooqCrudView.<init>(CompanyDependentJooqCrudView.java:33)
at com.sferion.planglobal.ui.crud.CompanyDependentJooqCrudView.<init>(CompanyDependentJooqCrudView.java:28)
at com.sferion.planglobal.ui.views.UsersListView.<init>(UsersListView.java:59)
at com.sferion.planglobal.ui.views.UsersListView$$FastClassByGuice$$137473960.GUICE$TRAMPOLINE(<generated>)
at com.sferion.planglobal.ui.views.UsersListView$$FastClassByGuice$$137473960.apply(<generated>)
at com.google.inject.internal.DefaultConstructionProxyFactory$FastClassProxy.newInstance(DefaultConstructionProxyFactory.java:82)
at com.google.inject.internal.ConstructorInjector.provision(ConstructorInjector.java:114)
at com.google.inject.internal.ConstructorInjector.construct(ConstructorInjector.java:91)
at com.google.inject.internal.ConstructorBindingImpl$Factory.get(ConstructorBindingImpl.java:300)
at com.google.inject.internal.InjectorImpl$1.get(InjectorImpl.java:1101)
... 69 more
Caused by: java.sql.SQLException: Error while reading field: cast (row ("planglobal"."portal_user".*) as "planglobal"."portal_user"), at JDBC index: 1
at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1590)
at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.apply(CursorImpl.java:1525)
at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.apply(CursorImpl.java:1484)
at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:144)
at org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1449)
... 89 more
Caused by: java.lang.NumberFormatException: For input string: "2022-10-17 20:57:11.795537"
at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:67)
at java.base/java.lang.Long.parseLong(Long.java:711)
at java.base/java.lang.Long.valueOf(Long.java:1163)
at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgFromString(DefaultBinding.java:3873)
at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgSetValue(DefaultBinding.java:3984)
at org.jooq.impl.DefaultBinding$DefaultRecordBinding.lambda$pgNewRecord$13(DefaultBinding.java:3976)
at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:144)
at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgNewRecord(DefaultBinding.java:3972)
at org.jooq.impl.DefaultBinding$DefaultRecordBinding.get0(DefaultBinding.java:3767)
at org.jooq.impl.DefaultBinding$DefaultRecordBinding.get0(DefaultBinding.java:3687)
at org.jooq.impl.DefaultBinding$InternalBinding.get(DefaultBinding.java:1025)
at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1580)
It is very strange to me that sometimes this query works and sometimes I get an error for the exact query. From what I can tell selecting Tables.PORTAL_USER generates an asterisk query but the column orders are not consistent. This makes me wonder what would cause that? A bug in postgres? The JDBC driver? Something wrong with the latest release of jooq (3.17.4) as of writing.
You're using the new jOOQ 3.17 Table as SelectField feature, which has a native implementation on PostgreSQL, while being emulated elsewhere. E.g. in native PostgreSQL, it's possible to write:
SELECT t FROM t
Producing a nested record from your projection, rather than a flat result set. In your particular case, that's not necessary. You can flatten your result set like this:
Result<PortalUserRecord> userList =
dsl.selectFrom(PORTAL_USER).fetch();
It's simpler, and doesn't run into this bug. It's certainly a bug in jOOQ (another bug caused by the native PG-specific implementation of said Table as SelectField feature. The * (asterisk) is at fault here, although omitting it as in the SQL example above, would expose the same problem. The problem being that this syntax relies on the column order of the database instance, rather than on the column order of the generated code. (This is why jOOQ never generates the * for ordinary queries either, but always lists all columns explicitly). Using this syntax would require a lot of database change management diligence, to make sure all development, test, production database instances expose the exact same database meta data.
After numerous other issues with the native implementation of the feature
I'd say, this should be the last straw to remove the implementation, and emulate the feature also on PostgreSQL:
As a workaround, you should avoid the feature, or ensure the generation schema matches the production schema exactly.