[
https://issues.apache.org/jira/browse/FLINK-37147?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18080243#comment-18080243
]
Qilong Wang commented on FLINK-37147:
-------------------------------------
Calcite found that the LEFT JOIN output (left, null) will definitely be
filtered by the filter. At this time, LEFT JOIN will be optimized to INNER JOIN
and the filter will be pushed down to before the join. If cast "2022-02-30" is
set as date, an error will occur. The solution is to replace CAST with TRY_CAST
> The where condition run time advance caused a program error
> -----------------------------------------------------------
>
> Key: FLINK-37147
> URL: https://issues.apache.org/jira/browse/FLINK-37147
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Affects Versions: 1.17.1
> Environment: {code:java}
> //代码占位符
> StreamExecutionEnvironment env =
> StreamExecutionEnvironment.getExecutionEnvironment();
> env.setRuntimeMode(RuntimeExecutionMode.BATCH).setParallelism(1);
> StreamTableEnvironment tableEnvironment = StreamTableEnvironment.create(env);
> Table taxiOperation = tableEnvironment.fromValues(
> DataTypes.ROW(
> DataTypes.FIELD("id", DataTypes.STRING()),
> DataTypes.FIELD("startTime", DataTypes.STRING()),
> DataTypes.FIELD("amount", DataTypes.DOUBLE())
> ),
> row("1", "2022-02-07", 7.38),
> row("3", "2022-02-08", 2.38),
> row("4", "2022-02-30", 5.38)
> );
> tableEnvironment.createTemporaryView("taxiOperation", taxiOperation);
> Table taxiCompany = tableEnvironment.fromValues(
> DataTypes.ROW(
> DataTypes.FIELD("codeTime", DataTypes.STRING())
> ),
> row("2022-02-07"),
> row("2022-02-08")
> );
> tableEnvironment.createTemporaryView("taxiTimeCode", taxiCompany);
> tableEnvironment.createTemporaryView("t1", tableEnvironment.sqlQuery("select
> tc.*, tto.* from taxiTimeCode tc " +
> "left join taxiOperation tto on tc.codeTime = tto.startTime"));
> tableEnvironment.createTemporaryView("t2", tableEnvironment.sqlQuery("select
> t1.*, cast(t1.startTime as date) as endTime from t1"));
> tableEnvironment.sqlQuery("select * from t2 where endTime <
> current_date").execute().print(); {code}
> Reporter: lixu
> Priority: Major
>
> In the provided code, when querying the t2 table, adding the filtering of the
> date field, the program will report an error; Without adding the filtering of
> the date field, the program runs normally;
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
