[multistage] support additional SQL feature tracker

[walterddr]

Many SQL features are not yet supported in multistage engine, because one of the two followings:

• stage plan node in pinot-query-planner hasn't been implemented yet.
• operator in pinot-query-runtime hasn't been implemented yet

Some of the existing SQL features missing that prevents us from fully enabling the IntegrationTestSet

• enable partial hard-coded test set: [multistage] test using BaseClusterIntegrationTestSet #9412
  • CAST with type alias like LONG for BIGINT is not supported
  • MV column support, instead standard ARRAY/LIST SQL type will be
• enable partial generated test set ([multistage] generated test for v2 engine #9452)

Some already supported by Pinot currently but not on the new engine.

• IN filter clause ([multistage] Query Fails When Using IN Clause With String Args #9345) ([multistage] Support IN and NOT-IN Clauses #9374)
• RANGE filter clause ([multistage] adding support for range predicate #9445)
• ORDER BY ([multisage] support ORDER BY #9279)
• HAVING ([multistage] support HAVING clause #9274)
• DISTINCT ([multistage][bugfix] fix group-by without agg call plan failure #9383)
• CAST ([multistage] add cast function #9384)
• LIMIT <start>, <count> ([multi stage] add tests for LIMIT/OFFSET in QueryRunnerTest #9496)
• LIKE ([multistage] support like and regex like #9654)
• DISTINCT Aggregation
• UNION/INTERSECT
• AGG with FILTER

Some of the features are removing limitations of current engine

• support function invoker for non-standard SQL keywords like DATE_TRUNC (requires rewrite of FunctionRegistry to record not just # of arguments but a DataType[] as function signature, also might as well use function catalog in calcite) ([multistage] add calcite function catalog #9375)
• support inequality join condition as post-join filter ([multistage] do not pushdown inequality join rules #9328)
• count(*) / count(col) / sum(1) are equivalent (without null support), but the rewrite rules are not clear ([multistage] fix literal aggregate function not working issue #9331)
• support more complex join condition expressions, like
  • a JOIN b USING(col) ([multistage][feature] Support a join b using(col) #9373)
  • a JOIN b ON b.col = a.col (ordering matters as currently the ref indexing is assuming left then right) ([multistage][bug-fix] Fix the join condition order different from join table order #9350)
• support LEFT JOIN ([multistage] add LEFT JOIN #9446)
  • support NULL value ([multistage] support NULL in data blocks #9427)
• support small RIGHT table with inequality conditions. ([multistage] support inequality JOIN #9448)
• support SEMI JOIN [multistage] support SEMI join #9367
• support NULL-safe operators

Some of the additional items to support but not yet

• UNNEST (related Support FLATTEN (unnest), KVGEN type functions #7881)
• LATERAL
• OVER aggregation (related: [multistage] Support Window Functions #7213)
• MATCH_REGOGNIZE

Larger Design initiatives

• resolve Scalar/Transform/Predicate function mismatches #9658
• Function registry with strong type revisit function registry #9641
• Function registry for other types like UDAF and UDTF revisit function registry #9641

[61yao]

a Join b Using(col) is already supported. I'll just send out a test.