[
https://issues.apache.org/jira/browse/CALCITE-5251?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
lincoln lee updated CALCITE-5251:
---------------------------------
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|https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/queries/joins/#event-time-temporal-join],
the snapshot maybe optimized in some cases (to reserve less versions of the
managed versioned-table), e.g.,
{code}
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
{code}
currently, the following node tree has no hint on `LogicalSnapshot`
{code}
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]])
{code}
If snapshot could be extended to support hint, it would make this optimization
possible.
was:Current, `snapshot` does not support hint yet and this blocks hint
propagation to the underlying temporal table.
> Support SQL hint for Snapshot
> -----------------------------
>
> Key: CALCITE-5251
> URL: https://issues.apache.org/jira/browse/CALCITE-5251
> Project: Calcite
> Issue Type: New Feature
> Reporter: lincoln lee
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> 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|https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/queries/joins/#event-time-temporal-join],
> the snapshot maybe optimized in some cases (to reserve less versions of the
> managed versioned-table), e.g.,
> {code}
> 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
> {code}
> currently, the following node tree has no hint on `LogicalSnapshot`
> {code}
> 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]])
> {code}
> If snapshot could be extended to support hint, it would make this
> optimization possible.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
