[
https://issues.apache.org/jira/browse/CALCITE-3603?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17000635#comment-17000635
]
Danny Chen commented on CALCITE-3603:
-------------------------------------
Thanks [~julianhyde] ~ I did a little research standard SQL 2011 and here is
what i found about LATERAL keyword and temporal table join:
{code:sql}
SQL-2011 IWD 9075-2:201?(E) 7.6 <table reference>
Syntax Rules
1) If <query system time period specification> QSTPS is specified, then:
a) b)
c)
The table identified by the <table or query name> shall be a system-versioned
table.
If BETWEEN is specified and neither SYMMETRIC nor ASYMMETRIC is specified, then
ASYM- METRIC is implicit.
QSTPS shall not contain a <column reference> or an <SQL parameter reference>.
9) Case:
a) If TR is contained in a <from clause> FC with no intervening <query
expression>, then the scope clause SC of TR is the <select statement: single
row> or innermost <query specification> that contains FC. The scope of a range
variable of TR is the <select list>, <where clause>, <group by clause>, <having
clause>, and <window clause> of SC, together with every <lateral derived table>
that is simply contained in FC and is preceded by TR, and every <collection
derived table> that is simply contained in FC and is preceded by TR, and the
<join condition> of all <joined table>s contained in SC that contain TR. If SC
is the <query specification> that is the <query expression body> of a simple
table query STQ, then the scope of a range variable of TR also includes the
<order by clause> of STQ.
28) A <table primary> is possibly non-deterministic if the simply contained
<table name>, <query name>, <derived table>, <lateral derived table>, <data
change delta table>, or <joined table> is possibly non- deterministic, or if
the <table primary> contains a <query system time period specification> that
does not implicitly or explicitly specify FOR SYSTEM_TIME AS OF
CURRENT_TIMESTAMP.
{code}
So from my side, what i understood is that: yes, there is no direct
relation-ship between LATERAL and temporal table join. So we definitely should
not add an implicitly LATERAL keyword here.
The standard only allows constant(or literal) for <query system time period
specification>, while we support an expression here, so with the LATERAL, what
we need to fix is the scope of the referenced names in the expression.
> SqlLateralOperator's unparse add additional keyword 'LATERAL' when the inner
> operator is SqlSnapshot
> ----------------------------------------------------------------------------------------------------
>
> Key: CALCITE-3603
> URL: https://issues.apache.org/jira/browse/CALCITE-3603
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.21.0
> Reporter: Kevin Zhang
> Assignee: Danny Chen
> Priority: Minor
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> When joining with a dimension table using the following sql
> {code:sql}
> SELECT
> o.amout, o.currency, r.rate, o.amount * r.rate
> FROM
> Orders AS o
> JOIN LatestRates FOR SYSTEM_TIME AS OF o.proctime AS r
> ON r.currency = o.currency
> {code}
> the unparsed sql is:
> {code:sql}
> SELECT `o`.`amout`, `o`.`currency`, `r`.`rate`, `o`.`amount` * `r`.`rate`
> FROM `Orders` AS `o`
> INNER JOIN LATERAL `LatestRates` FOR SYSTEM_TIME AS OF `o`.`proctime` AS `r`
> ON `r`.`currency` = `o`.`currency`
> {code}
> which has a syntax error because an additional "LATERAL" is added after
> "JOIN".
> The problem lies in SqlLateralOperator's unparse method, if the kind of the
> first operand is SqlSnapshot, we should not write out the operator's name.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
