Allow hints around the type and order of JOIN operations

[hlcianfagna]

Problem Statement

[Raising feature request for consideration/refinement following a brief discussion with @seut ]

In some cases, the optimizer may come out with a suboptimal plan for performing JOINs for a query.
There may be many reasons for this, but on an exceptional basis, knowing how the data looks like, it would be useful to be able to request a specific JOIN order or force a hash join.

Possible Solutions

Something along the lines of what other systems do:
PostgreSQL allows hinting NestLoop and MergeJoin with pg_hint_plan.
In MySQL order can be forced using the keyword STRAIGHT_JOIN instead of JOIN.
In MSSQL JOIN can be preceded with LOOP (for nested loops), MERGE, or HASH to request a specific mechanism.
In Oracle the optimizer looks for hints within /* */ comments and there are hints such as USE_NL to request nested loops.

Considered Alternatives

Try rewriting queries with subqueries until obtaining the desired plan, results may change when upgrading CrateDB or changing other factors in the environment.

References:
MS SQL Server Hints https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16#force-order

[hlcianfagna]

Relates with #10213
if we consider the workaround suggested in https://community.snowflake.com/s/article/Controlling-Join-Order

[hlcianfagna]

Regarding the option of using special comments as in pg_hint_plan, a discussion in crate/crash#386 pointed out that https://www.postgresql.org/docs/15/sql-syntax-lexical.html#SQL-SYNTAX-COMMENTS reads:

A comment is removed from the input stream before further syntax analysis and is effectively replaced by whitespace.

[matriv]

I think the "plan" is to use special comments as pg_hint_plan or Oracle:
/*+ PARALLEL(employees 3) */, (notice the + sign) so those shouldn't be removed at parser level.