[FLINK-19670] Query on a view of temporal join throws ParseException

• Type: Bug
• Status: Open
• Priority: Not a Priority
• Resolution: Unresolved
• Affects Version/s: 1.11.1
• Component/s: Table SQL / API
• Labels: auto-deprioritized-major auto-deprioritized-minor
• Assignee: Unassigned
• Reporter: simenliuxing
• Created: 16/Oct/20 07:35
• Updated: 24/Aug/23 22:35

Description

When I run a sql task with flink1.11.1 and blink planner, the following syntax error appears, sql is as follows:

CREATE TABLE orders (
  order_id INT,
  product_id INT,
  proctime AS PROCTIME()
) WITH (
  'connector' = 'kafka',
  'topic' = 'test',
  'properties.bootstrap.servers' = 'localhost:9092',
  'format' = 'json'
);

create table products(
  pro_id  INT ,
  product_name STRING,
  PRIMARY  KEY (pro_id) NOT ENFORCED
) WITH  (
  'connector'='jdbc',
  'url'='jdbc:mysql://localhost:3306/test',
  'username'='root',
  'password'='root',
  'table-name'='result4'
);

CREATE TABLE orders_info (
  order_id INT,
  pro_id INT,
  product_name STRING
) WITH (
    'connector' = 'print'
);

create view orders_view
as
SELECT
    order_id,
    pro_id,
    product_name
FROM orders
LEFT JOIN products FOR SYSTEM_TIME AS OF orders.proctime
ON orders.product_id = products.pro_id;

INSERT INTO orders_info SELECT * FROM orders_view;

The error is as follows:

Caused by: org.apache.flink.sql.parser.impl.ParseException: Encountered "FOR" at line 3, column 73.
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" ...
    
	at org.apache.flink.sql.parser.impl.FlinkSqlParserImpl.generateParseException(FlinkSqlParserImpl.java:36086)
	at org.apache.flink.sql.parser.impl.FlinkSqlParserImpl.jj_consume_token(FlinkSqlParserImpl.java:35900)
	at org.apache.flink.sql.parser.impl.FlinkSqlParserImpl.SqlStmtEof(FlinkSqlParserImpl.java:3801)
	at org.apache.flink.sql.parser.impl.FlinkSqlParserImpl.parseSqlStmtEof(FlinkSqlParserImpl.java:248)
	at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:161)
	... 25 more

 

Issue Links

• is caused by: CALCITE-4526 SqlSnapshot unparse lost the AS keyword when the table has alias