I took the prn3 patch attached to
DERBY-4433, which ensures that generated/identity columns are added in a ProjectRestrictNode on top of the source result set if the source is a SetOperatorNode, and moved the PRN generation from SetOperatorNode to ResultSetNode so that it applies to all kinds of result sets (except table constructors, which need special handling). See the attached always_prn patch.
With that patch, the
DERBY-3 repro does no longer produce gaps in the identity values, so the approach looks promising. I also tried it in combination with the DERBY-4 patch (derby-4_dhw), but then it unfortunately produced an assert error:
ij> create table t3(id int generated always as identity, i int);
0 rows inserted/updated/deleted
ij> insert into t3 select * from t1 order by x;
ERROR XJ001: Java exception: 'ASSERT FAILED bindExpressions() is not expected to be called for class org.apache.derby.impl.sql.compile.ProjectRestrictNode: org.apache.derby.shared.common.sanity.AssertFailure'.