Add Table.select() to enhance InlineDerivedTable

[lukaseder]

The InlineDerivedTable syntax Table.where(Condition) has been implemented for convenience, to quickly create a derived table from a Table and a WHERE clause, possibly inlining the WHERE clause to the surrounding query where appropriate.

Apart from allowing users to use this feature, we're using it internalls when creating parent() or child() relationships from ForeignKey or from TableRecord references:

Result<AuthorRecord> authors = ...
ctx.selectFrom(authors.children(BOOK_AUTHOR_ID)).fetch();

Behind the scenes, that's just:

ctx.selectFrom(BOOK.where(BOOK.AUTHOR_ID.in(authors.stream().map(r -> r.get(AUTHOR.ID)).toList()))).fetch();

For any similar use-case where a Table::where clause is useful in terms of dynamic SQL (i.e. coupling the Table with a Condition), it might be equally useful to also provide:

• select()

Other clauses also seem to be useful, but many of them enforce the derived table, rather than making it inlinable:

• connectBy() and startWith()
• groupBy() and having()
• window() and qualify()
• selectDistinct()
• orderBy()
• offset() and limit() and seek()
• union() and intersect() and except()

Depending on the expressions in the select() clause, the InlineDerivedTable would still have to be enforced as a derived table, rather than remaining inlinable. Such expressions include:

• Aggregate functions
• Window functions
• Whenever the expression is used in JOIN .. ON or JOIN .. USING clauses

The select() clause can be used for this MULTISET convenience feature: #13063.

[lukaseder]

I'm closing this as won't fix. I already have mixed feelings about the InlineDerivedTable feature per se. It's a power user feature for users of dynamic SQL. It works because the WHERE clause doesn't affect the projection. It only produces a view of the same row type as the original table.

This is different with select(), where a different row type is desired. The resulting table has nothing to do with the original one. Columns can be added / removed. So the type of Table.select() would just be Table<?>, which would render the feature useless, compared to e.g. an actual derived table.

There had been other clause suggestions, where this problem wouldn't appear, including:

• qualify()
• orderBy()
• offset() and limit()
• distinct()
• union(), except(), and intersect()

But the utility of any of these is questionable.

The original idea appeared in this feature here: #13069, which has been rejected in the meantime.

I'm closing this as won't fix.