Improve GENERATE_SERIES emulation by avoiding unnecessary derived table if possible

Some emulations produce derived tables for good reasons, e.g. GENERATE_SERIES creates a derived table with a nested recursive CTE. The derived table helps reduce the scope where the CTE is visible to where it is really needed, allowing for things like cross joining two calls to GENERATE_SERIES

For example, when translating this:

insert into t (a) select * from generate_series(1, 3);

To HSQLDB, we're getting this:

insert into t (a)
select *
from (
  with recursive
    generate_series(generate_series) as (
      select 1
      from (
        select 1 as dual
        from information_schema.system_users
        limit 1
      ) as dual
      union all
      select (generate_series + 1)
      from generate_series
      where generate_series < 3
    )
  select generate_series
  from generate_series
) as generate_series;

However, SQL Server doesn't support nested CTE and requires it to be pushed up to the top level (see #3175, #11586)

The resulting SQL is:

with
  generate_series(generate_series) as (
    select 1
    union all
    select (generate_series + 1)
    from generate_series
    where generate_series < 3
  )
insert into t (a)
select *
from (
  select generate_series
  from generate_series
) generate_series;

Now, there's a weird looking derived table left over, which isn't really necessary anymore. We might as well inline it like this:

with
  generate_series(generate_series) as (
    select 1
    union all
    select (generate_series + 1)
    from generate_series
    where generate_series < 3
  )
insert into t (a)
select *
from generate_series;

So, there are two things to be done here:

[ ] Create a new internal "inlineable derived table" that is inlined whenever it is not strictly needed (e.g. SELECT * FROM t). See also InlineDerivedTable. This is only done internally for derived tables created by jOOQ. User-defined derived tables will not be inlined automatically, though we might offer such a (configurable) transformation in the future.
[ ] Apply this improvement to the GENERATE_SERIES emulation.

jOOQ / jOOQ

Improve GENERATE_SERIES emulation by avoiding unnecessary derived table if possible #11588