jOOQ
jOOQ copied to clipboard
Column not found if stored client side computed column references another column that isn't part of a DML statement
With a configuration like this:
<forcedType>
<priority>-1</priority>
<generator>ctx -> org.jooq.impl.DSL.when(X.isNotNull(), ctx.table().X_VERSION.plus(1)).else_(ctx.table().X_VERSION)</generator>
<includeExpression>(?i:T_COMP_CLIENT_SIDE_DEP\.X_VERSION)</includeExpression>
</forcedType>
<forcedType>
<priority>-1</priority>
<generator>ctx -> org.jooq.impl.DSL.when(X.isNotNull(), org.jooq.impl.DSL.currentTimestamp()).else_(ctx.table().X_MODIFIED_AT)</generator>
<includeExpression>(?i:T_COMP_CLIENT_SIDE_DEP\.X_MODIFIED_AT)</includeExpression>
</forcedType>
The expected behaviour is for X_VERSION
to be incremented, whenever X
is touched, and for X_MODIFIED_AT
to be set to the current timestamp whenever X
is touched.
This doesn't currently work when X
is omitted from an INSERT
statement, e.g.
assertEquals(1,
create().insertInto(t)
.columns(t.ID)
.values(1)
.execute());
The above produces this SQL:
insert into T_COMP_CLIENT_SIDE_DEP (ID, X_VERSION, X_MODIFIED_AT)
select
ID,
case
when X is not null then (X_VERSION + 1)
else X_VERSION
end,
case
when X is not null then current_timestamp
else X_MODIFIED_AT
end
from (
select 1
) t (ID)
With this error:
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Feld "X" nicht gefunden
Column "X" not found; SQL statement:
insert into T_COMP_CLIENT_SIDE_DEP (ID, X_VERSION, X_MODIFIED_AT) select ID, case when X is not null then (X_VERSION + cast(? as int)) else X_VERSION end, case when X is not null then current_timestamp else X_MODIFIED_AT end from (select cast(? as int)) t (ID) [42122-214]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:502)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
at org.h2.message.DbException.get(DbException.java:223)
at org.h2.message.DbException.get(DbException.java:199)
at org.h2.expression.ExpressionColumn.getColumnException(ExpressionColumn.java:244)
at org.h2.expression.ExpressionColumn.optimizeOther(ExpressionColumn.java:226)
at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:213)
at org.h2.expression.condition.SimplePredicate.optimize(SimplePredicate.java:43)
at org.h2.expression.condition.NullPredicate.optimize(NullPredicate.java:48)
at org.h2.expression.SearchedCase.optimize(SearchedCase.java:46)
at org.h2.command.query.Select.prepareExpressions(Select.java:1170)
at org.h2.command.query.Query.prepare(Query.java:218)
at org.h2.command.dml.Insert.doPrepare(Insert.java:310)
at org.h2.command.dml.DataChangeStatement.prepare(DataChangeStatement.java:37)
at org.h2.command.Parser.prepareCommand(Parser.java:575)
at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:631)
at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:554)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:92)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
at org.jooq.tools.jdbc.DefaultConnection.prepareStatement(DefaultConnection.java:95)
... 35 more
Instead, we should produce the following SQL:
insert into T_COMP_CLIENT_SIDE_DEP (ID, X_VERSION, X_MODIFIED_AT)
select
ID,
case
when X is not null then (X_VERSION + 1)
else X_VERSION
end,
case
when X is not null then current_timestamp
else X_MODIFIED_AT
end
from (
select 1, null, null, null
) t (ID, X, X_VERSION, X_MODIFIED_AT)
... where we use the DEFAULT
expression, or NULL
as a default for those fields that users don't set explicitly.
Workaround
It works like this, however:
assertEquals(1,
create().insertInto(t)
.columns(t.ID, t.X, t.X_VERSION, t.X_MODIFIED_AT)
.values(1, null, null, null)
.execute());