[
https://issues.apache.org/jira/browse/FLINK-20307?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17237854#comment-17237854
]
Danny Chen edited comment on FLINK-20307 at 11/24/20, 4:25 AM:
---------------------------------------------------------------
Based on the SQL standard 2011 7.6 table reference, the table primary syntax is:
{code:xml}
<table primary> ::=
<table or query name> [ <query system time period specification> ]
[ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
{code}
So the query should be re-written as following:
{code:sql}
SELECT p.name, p.qty * r.rate AS price, p.`tstamp`
FROM Products p
JOIN versioned_rates FOR SYSTEM_TIME AS OF p.`tstamp` AS r -- AS should be
here, the AS keyword is optional
ON p.currency = r.currency
{code}
was (Author: danny0405):
Based on the SQL standard 2011 7.6 table reference, the table primary syntax is:
{code:xml}
<table primary> ::=
<table or query name> [ <query system time period specification> ]
[ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
{code}
So the query should be re-written as following:
{code:sql}
SELECT p.name, p.qty * r.rate AS price, p.`tstamp`
FROM Products p
JOIN versioned_rates FOR SYSTEM_TIME AS OF p.`tstamp` AS r -- AS should be here
~
ON p.currency = r.currency
{code}
> Cannot alias versioned table in a join
> --------------------------------------
>
> Key: FLINK-20307
> URL: https://issues.apache.org/jira/browse/FLINK-20307
> Project: Flink
> Issue Type: Improvement
> Components: Table SQL / Planner
> Reporter: Dawid Wysakowicz
> Assignee: Danny Chen
> Priority: Critical
> Fix For: 1.12.0
>
>
> A query like:
> {code}
> SELECT p.name, p.qty * r.rate AS price, p.`tstamp` FROM Products p JOIN
> versioned_rates r FOR SYSTEM_TIME AS OF p.`tstamp` ON p.currency = r.currency;
> {code}
> fails with:
> {code}
> [ERROR] Could not execute SQL statement. Reason:
> org.apache.flink.sql.parser.impl.ParseException: Encountered "FOR" at line 1,
> column 108.
> Was expecting one of:
> <EOF>
> "EXCEPT" ...
> "FETCH" ...
> "GROUP" ...
> "HAVING" ...
> "INTERSECT" ...
> "LIMIT" ...
> "OFFSET" ...
> "ON" ...
> "ORDER" ...
> "MINUS" ...
> "TABLESAMPLE" ...
> "UNION" ...
> "USING" ...
> "WHERE" ...
> "WINDOW" ...
> "(" ...
> "NATURAL" ...
> "JOIN" ...
> "INNER" ...
> "LEFT" ...
> "RIGHT" ...
> "FULL" ...
> "CROSS" ...
> "," ...
> "OUTER" ...
> {code}
> When I do not alias the {{versioned_rates}} table everything works as
> expected. Therefore query like just runs:
> {code}
> SELECT p.name, p.qty * versioned_rates.rate AS price, p.`tstamp` FROM
> Products p JOIN versioned_rates FOR SYSTEM_TIME AS OF p.`tstamp` ON
> p.currency = versioned_rates.currency;
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
