Sequence.nextvals() doesn't work with DSLContext.selectFrom()

[lukaseder]

While this works:

System.out.println(ctx.fetchValues(S_AUTHOR_ID.nextvals(10)));

And produces in H2:

select next value for "PUBLIC"."S_AUTHOR_ID" from system_range(1, 10) generate_series

This doesn't:

System.out.println(ctx.selectFrom(S_AUTHOR_ID.nextvals(10)).fetch());

The query is now:

select "alias_90250067".nextval from (
  select next value for "PUBLIC"."S_AUTHOR_ID" from system_range(1, 10) generate_series
) "alias_90250067"

And raises errors in various dialects, e.g. in H2:

org.jooq.exception.DataAccessException: SQL [select "alias_90250067".nextval from (select next value for "PUBLIC"."S_AUTHOR_ID" from system_range(cast(? as int), cast(? as int)) generate_series) "alias_90250067" -- SQL rendered with a free trial version of jOOQ 3.17.0-SNAPSHOT]; Feld "alias_90250067.NEXTVAL" nicht gefunden
Column "alias_90250067.NEXTVAL" not found; SQL statement:
select "alias_90250067".nextval from (select next value for "PUBLIC"."S_AUTHOR_ID" from system_range(cast(? as int), cast(? as int)) generate_series) "alias_90250067" -- SQL rendered with a free trial version of jOOQ 3.17.0-SNAPSHOT [42122-210]
	at org.jooq_3.17.0-SNAPSHOT.H2.debug(Unknown Source) ~[?:?]
	at org.jooq.impl.Tools.translate(Tools.java:3083) ~[classes/:?]
	at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:670) ~[classes/:?]
	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:354) [classes/:?]
	at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:284) [classes/:?]
	at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2820) [classes/:?]
	at org.jooq.testscripts.JDBC.main(JDBC.java:36) [test-classes/:?]
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Feld "alias_90250067.NEXTVAL" nicht gefunden
Column "alias_90250067.NEXTVAL" not found; SQL statement:
select "alias_90250067".nextval from (select next value for "PUBLIC"."S_AUTHOR_ID" from system_range(cast(? as int), cast(? as int)) generate_series) "alias_90250067" -- SQL rendered with a free trial version of jOOQ 3.17.0-SNAPSHOT [42122-210]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:521) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:496) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.message.DbException.get(DbException.java:227) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.message.DbException.get(DbException.java:203) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.expression.ExpressionColumn.getColumnException(ExpressionColumn.java:248) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.expression.ExpressionColumn.optimizeOther(ExpressionColumn.java:230) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:213) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.command.query.Select.prepare(Select.java:1177) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.command.Parser.prepareCommand(Parser.java:557) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:615) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:553) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:92) ~[h2-2.1.210.jar:2.1.210]
	at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288) ~[h2-2.1.210.jar:2.1.210]
	at org.jooq.impl.ProviderEnabledConnection.prepareStatement(ProviderEnabledConnection.java:109) ~[classes/:?]
	at org.jooq.impl.SettingsEnabledConnection.prepareStatement(SettingsEnabledConnection.java:82) ~[classes/:?]
	at org.jooq.impl.AbstractResultQuery.prepare(AbstractResultQuery.java:210) ~[classes/:?]
	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:307) ~[classes/:?]
	... 3 more
42122
Feld "alias_90250067.NEXTVAL" nicht gefunden
Column "alias_90250067.NEXTVAL" not found; SQL statement:
select "alias_90250067".nextval from (select next value for "PUBLIC"."S_AUTHOR_ID" from system_range(cast(? as int), cast(? as int)) generate_series) "alias_90250067" -- SQL rendered with a free trial version of jOOQ 3.17.0-SNAPSHOT [42122-210]
42S22

[lukaseder]

We'll need to generate an alias for the projected expression, which might break existing code in subtle ways, so I'm not going to backport this fix.

[lukaseder]

Can't use nextval as an alias in Oracle, Db2. Let's just call it n

[lukaseder]

Hmm, no, the reason is that in Oracle, it's not possible to use a sequence from a subquery. So, we cannot fix this in Oracle. Dialects that can't do this include (at least):

• Db2
• HSQLDB
• Oracle
• SQL Server

[lukaseder]

Fixed for dialects that support sequence expressions in subqueries