I am trying to upgrade JOOQ from 3.14.6 to 3.17.5. I am observing that generated SQL across two versions are different

Jooq 3.14.6

SELECT 
  public.payment.request_id 
FROM 
  payment 
WHERE 
  payment.status_expire_at < cast(
    '2007-12-03 10:15:30 00:00' as timestamp(6) with time zone
  )
) 
order by 
  public.payment.status_expire_at ASC LIMIT 10;

In Jooq 3.17.5

SELECT 
  public.payment.request_id 
FROM 
  payment 
WHERE 
  payment.status_expire_at < cast(
    '2007-12-03 10:15:30 00:00' as timestamp(6) with time zone
  )
) 
order by 
  public.payment.status_expire_at ASC FETCH NEXT 10 ROWS ONLY;

Can someone let me know

1. If there are any settings in jooq codegen (version 3.17.5) which will allow me to have the generated SQL same as in 3.14.6?
2. Is there any documentation link which summarizes all changes in generated SQL across Jooq versions from 3.14.6 to 3.175?

DB - Postgres

CodePudding user response：

Older PostgreSQL version support

You might be using an older version of PostgreSQL that didn't support the standard SQL FETCH clause yet? Please refer to the support matrix to see which jOOQ version (and edition) supports which PostgreSQL version. The commercial editions still support a lot of old PostgreSQL versions.

Your specific questions:

If there are any settings in jooq codegen (version 3.17.5) which will allow me to have the generated SQL same as in 3.14.6?

No, there's no configuration for this particular syntax, other than the SQLDialect, which is integration tested against every supported PostgreSQL version.

Is there any documentation link which summarizes all changes in generated SQL across Jooq versions from 3.14.6 to 3.175?

Yes, the release notes or the issue tracker. This particular change was probably introduced in jOOQ 3.15 with PostgreSQL 13 support:

• https://github.com/jOOQ/jOOQ/issues/10645

In particular, the native PostgreSQL FETCH FIRST .. WITH TIES support probably triggered this change.

Possible solutions:

• You can stick with jOOQ 3.14.x, where the jOOQ Open Source Edition (assuming you're using that) did yet support PostgreSQL 13 and this syntax
• You can upgrade to a commercial jOOQ edition, which still supports PostgreSQL 12 and less
• You can upgrade PostgreSQL to something more recent
• You can patch the generated SQL string with a regex in an ExecuteListener: Search FETCH NEXT (\?|\d ) ROWS ONLY and replace by LIMIT $1. Note that while it's OFFSET .. FETCH, it's LIMIT .. OFFSET, so if you want to use this approach, beware of having to invert the order of the bind values as well. This is more of a hack, of course, the other options are much better.