[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

• [x] enable partial hard-coded test set: https://github.com/apache/pinot/pull/9412
  • CAST with type alias like LONG for BIGINT is not supported
  • MV column support, instead standard ARRAY/LIST SQL type will be
• [x] enable partial generated test set (https://github.com/apache/pinot/pull/9452)

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

• [x] IN filter clause (https://github.com/apache/pinot/issues/9345) (https://github.com/apache/pinot/pull/9374/)
• [x] RANGE filter clause (https://github.com/apache/pinot/pull/9445)
• [x] ORDER BY (https://github.com/apache/pinot/pull/9279)
• [x] HAVING (https://github.com/apache/pinot/pull/9274)
• [x] DISTINCT (https://github.com/apache/pinot/pull/9383)
• [x] CAST (https://github.com/apache/pinot/pull/9384)
• [x] LIMIT <start>, <count> (https://github.com/apache/pinot/pull/9496)
• [x] LIKE (https://github.com/apache/pinot/pull/9654)
• [ ] DISTINCT Aggregation
• [ ] UNION/INTERSECT
• [ ] AGG with FILTER

Some of the features are removing limitations of current engine

• [x] 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) (https://github.com/apache/pinot/pull/9375)
• [x] support inequality join condition as post-join filter (https://github.com/apache/pinot/pull/9328)
• [x] count(*) / count(col) / sum(1) are equivalent (without null support), but the rewrite rules are not clear (https://github.com/apache/pinot/pull/9331)
• [x] support more complex join condition expressions, like
  • [x] a JOIN b USING(col) (https://github.com/apache/pinot/pull/9373)
  • [x] a JOIN b ON b.col = a.col (ordering matters as currently the ref indexing is assuming left then right) (https://github.com/apache/pinot/pull/9350)
• [x] support LEFT JOIN (https://github.com/apache/pinot/pull/9446)
  • [x] support NULL value (https://github.com/apache/pinot/pull/9427)
• [x] support small RIGHT table with inequality conditions. (https://github.com/apache/pinot/pull/9448)
• [x] support SEMI JOIN https://github.com/apache/pinot/pull/9367
• [ ] support NULL-safe operators

Some of the additional items to support but not yet

• [ ] UNNEST (related https://github.com/apache/pinot/issues/7881)
• [ ] LATERAL
• [ ] OVER aggregation (related: https://github.com/apache/pinot/issues/7213)
• [ ] MATCH_REGOGNIZE

Larger Design initiatives

• [x] #9658
• [ ] Function registry with strong type #9641
• [ ] Function registry for other types like UDAF and UDTF https://github.com/apache/pinot/issues/9641

[61yao]

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