Add Settings.renderAutoAliasedDerivedTableExpressions to auto alias unnamed field expressions in derived tables

[lukaseder]

This will produce a syntax error:

create().select()
        .from(select(inline(1)))
        .fetch();

Workaround:

create().select()
        .from(select(inline(1).as("a")))
        .fetch();

---

See also:

• array_agg alias seems to be mandatory but missing #7395 A similar case when using array_agg in a subquery
• Add an SPI to generate names for unnamed QueryParts #7977 An SPI that produces names for unnamed query parts
• Incorrectly generated sqlite query? #15543
• Table as SelectField doesn't work from unnamed scalar subquery in PostgreSQL #13988

[lukaseder]

A thorough fix is best implemented when we have immutable queries, i.e. queries where we can no longer add columns to the SELECT clause. Because then, we can assign generated column names deterministically across queries and subqueries, without names ever changing. For instance:

Select<?> select =
create().select()
        .from(create().selectOne());

Field<?> field = select.field(0);

The above field reference would yield a generated field name (e.g. col1), and there is no way this name can ever change, from the above select reference.

[knutwannheden]

This issue also cropped up in #1699 when testing the DB2 support.

[lukaseder]

Looking into this now.

• A good place to decide whether such aliases need to be generated is Alias
  • We'll do this only in the default implementation of toSQLWrapped
  • Only if there are no fieldAliases (which would provide explicit aliases already)
  • Only for Select or DerivedTable
• The implementation is then in SelectQueryImpl, where we already have a mechanism of specifying select aliases

Since this is a delicate change with regression risks, I'm not going to backport the fix.

[lukaseder]

A new marker interface is necessary to denote all the Field types that do not require such auto-aliasing, including e.g.:

• FieldAlias
• TableFieldImpl
• Level
• Rownum
• SQLField (which may already contain an alias, we don't know)
• UDTPathFieldImpl

[lukaseder]

Some extensive test will be necessary for all AbstractField implementations:

• To ensure only those where we want auto-aliasing to apply are affected
• To ensure that we cover all AbstractField implementations in a test (this meta test will fail for future implementations, by default)

[lukaseder]

More candidates:

• Deleting (these are keywords, and thus don't work automatically)
• FalseCondition
• Inserting
• NullCondition
• SQLCondition
• TrueCondition
• Updating

Cases where a wrapper type should delegate to the contained type (similar to SimpleCheckQueryPart)

• Coerce
• ConditionAsField
• FieldCondition
• FieldProxy

In principle, there's also room for making this dialect specific, e.g. PI could be a NamedField in some dialects, but not in others. That might be a bit overengineered for now.

[lukaseder]

Very cool! This query:

ctx.selectFrom(select(T_BOOK.ID, inline(1), trueCondition()).from(T_BOOK))

Now renders this SQL:

select "alias_77757314"."id", "alias_77757314"."1", "alias_77757314".condition
from (
  select
    "public"."t_book"."id",
    1 as "1",
    true as condition
  from "public"."t_book"
) as "alias_77757314"

As can be seen, there are likely many AbstractCondition implementations that will now render the name condition, leading to ambiguities which weren't there before. I.e. this is a slight regression risk in case an RDBMS was able to derive a column name from an expression automatically, or in case anonymous columns are supported. For example in PostgreSQL:

select *
from (
  select
    true,
    false,
    1
) as t

Produces:

|?column?|?column?|?column?|
|--------|--------|--------|
|true    |false   |1       |

It's unclear if this only causes issues with the asterisk, currently, because it's not really possible to dereference any columns from t

[lukaseder]

Another regression risk, our Field.getName() values may conflict with keywords, e.g. this query is now being generated for a test in MySQL:

insert into `test`.`t_author` (`ID`, `LAST_NAME`)
values (
  (
    select *
    from (
      select (max(`test`.`t_author`.`ID`) + 1) as add
      from `test`.`t_author`
    ) as `t`
  ), 
  'X'
)

The error is now:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add from test.t_author) as t), 'X')' at line 1

This works:

insert into `test`.`t_author` (`ID`, `LAST_NAME`)
values (
  (
    select *
    from (
      select (max(`test`.`t_author`.`ID`) + 1) as `add`
      from `test`.`t_author`
    ) as `t`
  ), 
  'X'
);

[lukaseder]

Continuing this, I feel there are many risks of introducing this as a new default behaviour, especially when asterisks are involved, i.e. when name resolution in the RDBMS is not being done.

For example, the following transformations are now made:

-- Before
select * from t, (select 1) u

-- After
select * from t, (select 1 "1") u

But depending on other settings (e.g. quoting of identifiers), "1" may be unquoted, in case of which it's an invalid identifier again, just like add before.

If, however, we introduce this as an opt-in behaviour, guarded by a flag called Settings.autoAliasDerivedTableExpressions, then the feature might be acceptable. The flag could have 3 states:

• NEVER (default, status quo)
• UNNAMED
• ALWAYS

[lukaseder]

The 3 state setting might be useful in the future, when we do this also at the top level to guarantee field names (I think there's already an issue for this).

Implemented in jOOQ 3.20

[lukaseder]

Worth looking into the SPI for auto alias generation as well:

• Add an SPI to generate names for unnamed QueryParts #7977