Add support for JOIN algorithm hints

[lukaseder]

Customers have often requested better support for JOIN algorithm hints, i.e. hints that force an optimiser to choose nested loop joins, hash joins, merge joins, etc.

The syntax is always vendor specific:

• Add support for SQL Server's JOIN hints #5694
• Add support for CockroachDB JOIN hints #15437

The SQL standard is impartial to such implementation details. The canonical approach would be to support all of these directly as API, such as:

BOOK.rightHashJoin(AUTHOR)

But this would heavy on the new API that would have to be repeated:

• In Table and in SelectJoinStep
• For each join type (including the NATURAL flag)
• For each convenience overload (though, this could be skipped)
• For each dialect specific JOIN algorithm

I.e. we'd get dozens of additional methods for something that is rarely useful.

Other API designs are possible, for example, we could have:

• An enum that can be passed to each JOIN method, e.g. BOOK.rightJoin(AUTHOR, HASH) (this conflicts with the existing method that allows for dynamic join types, i.e. BOOK.join(AUTHOR, RIGHT_JOIN)
  • Pro: Great for dynamic SQL
  • Con: Can't pass additional hint-specific arguments very easily, e.g. what index(es) can be used by a join algorithm
• A postfix method on all JOIN types, e.g. BOOK.rightJoin(AUTHOR).withHash()
  • Pro: Existing T-SQL style hints are already available via Table.with()
  • Pro: Simplest API
  • Con: Reads a bit clumsily
  • Con: Complicates SelectJoinStep API
• A prefix method on all JOIN types, e.g. BOOK.hash().rightJoin(AUTHOR)
  • Pro: Reads almost like T-SQL style hints
  • Con: A new API type is required (return type of hash(), etc.) with again all the JOIN methods repeated, as well as transient implementation type that remembers the table and delays construction of the Join implementation
  • Con: Uses up Table API, which may conflict with future features
  • Con: Complicates SelectJoinStep API
• More methods on Table and SelectJoinStep as suggested above, e.g. BOOK.rightHashJoin(AUTHOR)
  • Pro: Consistent with existing DSL design (we don't have to offer it on all methods, e.g. not on crossJoin() or crossApply())
  • Pro: Consistent with expectations from users from auto-completion
  • Pro: Consistent with the existing straightJoin() method from MySQL
  • Con: Many new methods

Given the benefits of dynamic SQL, there's certainly going to be BOOK.join(AUTHOR, RIGHT_JOIN, HASH), where both are combined

Implementation

Hints are vendor specific. Some hints are probably supported by most vendors who support hints at all, including e.g.:

• HASH
• LOOP
• MERGE

Other hints are vendor specific, such as:

• NO_HASH (this just reduces the possible choices, but doesn't limit it to a specific choice)
• REMOTE

The strategy will be to ignore a hint if we don't know it is supported by a dialect.

Tasks

• Add DSL API
  • Table
  • SelectJoinStep
• Add SelectQuery API
• Add QOM API
• Add implementations
• Add Parser implementation
  • T-SQL style inline hints (e.g. a LEFT LOOP JOIN b)
  • Oracle style comment hints (e.g. /*+USE_NL(a b)*/). Postponed: Support parsing Oracle / YugabyteDB style JOIN hints #15812
• Add documentation page (1 per hint type, to benefit <dialects/> translation)

Supported hint types:

• HASH
• LOOP
• MERGE

Not supported (yet):

• Hints relating to the join order
• Hints on semi/anti joins, cross joins, apply, straight_join
• Hints on NATURAL join (they're possible via the dynamic SQL DSL overload)
• NO_HASH, NO_LOOP, NO_MERGE, i.e. hints excluding a certain algorithm
• INVERTED (to enforce GIN index lookups in CockroachDB)
• REMOTE (to enforce the "driving site" of a nested loop join in SQL Server)

Dialect support:

• CockroachDB: https://www.cockroachlabs.com/docs/stable/sql-grammar#opt_join_hint
  directives-that-can-alter-query-plan
• Oracle: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/influencing-the-optimizer.html#GUID-2C09E9BF-A05B-4F02-8BB6-B858F599CAEA
• SQL Server: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-join?view=sql-server-ver16&redirectedfrom=MSDN
• Sybase ASE: https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20023_1251/html/optimizer/X29335.htm
• YugabyteDB: https://docs.yugabyte.com/preview/explore/query-1-performance/pg-hint-plan/#hints-for-join-methods

Not yet implemented (see #15811)

• HANA
• Informix
• Vertica

Not applicable

• MariaDB has many historic MySQL hints, but not JOIN hints: https://mariadb.com/kb/en/optimizer-hints/
• MySQL: https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html (but support for the HASH_JOIN hint has been removed again in 8.0.20, and I can't seem to use the others to force MySQL to do a hash join...)

[lukaseder]

It could be useful to be able to turn off the generation of hints, especially when translating between dialects:

• Add Settings to turn off the generation of hints #15809

[lukaseder]

Only commercial RDBMS support this feature natively with a T-SQL style syntax. For the only OSS RDBMS where join hints are supported (MySQL), we'll need the commercial QOM traversal API, so this feature is commercial only.

[lukaseder]

Implemented in jOOQ 3.19. Some additional follow-up work includes:

• Correctly merge generated hints with user-provided hints #15813
• Support Oracle / YugabyteDB JOIN hints also in DML #15814