[CALCITE-5251] Support SQL hint for Snapshot

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.32.0
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: lincoln lee
• Created: 29/Aug/22 08:42
• Updated: 10/Sep/22 08:40
• Resolved: 08/Sep/22 06:20

Description

1. Current `snapshot` does not support hint yet and this blocks hint propagation to the underlying temporal table(As query hints, all operators included in the query may have the need to propagate hint, current hint propagation depends on whether the node is hintable).

2. When do a temporal join in Flink, the snapshot maybe optimized in some cases (to reserve less versions of the managed versioned-table), e.g., 

select /*+ snapshot('max-version'='1') */ stream *
from orders o join products_temporal for system_time as of orders.proctime as p
on o.productid = p.productid

currently, the following node tree has no hint on `LogicalSnapshot`

LogicalDelta
  LogicalProject(PROCTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], PRODUCTID0=[$3], NAME=[$4], SUPPLIERID=[$5], SYS_START=[$6], SYS_END=[$7])
    LogicalFilter(condition=[=($1, $3)])
      LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[\{0}])
        LogicalTableScan(table=[[CATALOG, SALES, ORDERS]])
        LogicalSnapshot(period=[$cor0.PROCTIME])
          LogicalTableScan(table=[[CATALOG, SALES, PRODUCTS_TEMPORAL]])

If snapshot could be extended to support hint, it would make this optimization possible.

Issue Links

• is related to: CALCITE-5107 Support SQL hint for Filter, SetOp, Sort, Window, Values
• links to: GitHub Pull Request #2888