[jira] [Comment Edited] (CALCITE-3603) SqlLateralOperator's unparse add additional keyword 'LATERAL' when the inner operator is SqlSnapshot

["Danny Chen (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3603?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17000635#comment-17000635 ] 

Danny Chen edited comment on CALCITE-3603 at 12/20/19 5:48 AM:
---------------------------------------------------------------

Thanks [~julianhyde] ~ I did a little research standard SQL 2011 and here is what i found about LATERAL keyword and temporal table join:

{code:xml}
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 without the LATERAL, what we need to fix is the scope of the referenced names in the expression.




was (Author: danny0405):
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)