[CALCITE-3603] SqlLateralOperator's unparse add additional keyword 'LATERAL' when the inner operator is SqlSnapshot

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.21.0
• Fix Version/s: 1.22.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Danny Chen
• Reporter: Kevin Zhang
• Created: 16/Dec/19 11:26
• Updated: 06/Mar/20 04:35
• Resolved: 18/Dec/19 05:49

Description

When joining with a dimension table using the following 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

the unparsed sql is:

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`

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.

Issue Links

• relates to: CALCITE-3620 Remove implicit lateral operator for temporal table join
• links to: GitHub Pull Request #1667