[FLINK-26361] "Unexpected correlate variable $cor0" when using hive dialect to write a subquery

• Type: Sub-task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Labels: pull-request-available
• Assignee: luoyuxia
• Reporter: luoyuxia
• Created: 25/Feb/22 02:11
• Updated: 01/Jul/22 02:30
• Resolved: 01/Jul/22 02:30

Description

With hive dialect, can be reproduced using such code:

tableEnv.executeSql("CREATE TABLE src (key string, value string)");
tableEnv.executeSql("create view cv1 as \n"
                + "select * \n"
                + "from src b where exists\n"
                + "  (select a.key \n"
                + "  from src a \n"
                + "  where b.value = a.value )");
List<Row> results =
                CollectionUtil.iteratorToList(tableEnv.executeSql("select * from src where src"
                        + ".key in (select key from cv1)").collect());

The plan for such sql is :

LogicalSink(table=[*anonymous_collect$1*], fields=[key, value])
  LogicalProject(key=[$0], value=[$1])
    LogicalFilter(condition=[IN($0, {
LogicalProject(key=[$0])
  LogicalProject(key=[$0], value=[$1])
    LogicalFilter(condition=[EXISTS({
LogicalProject(key=[$0])
  LogicalFilter(condition=[=($cor0.value, $1)])
    LogicalTableScan(table=[[test-catalog, default, src]])
})])
      LogicalTableScan(table=[[test-catalog, default, src]])
})])
      LogicalTableScan(table=[[test-catalog, default, src]])

The node LogicalFilter(condition=[=($cor0.value, $1)]) contains `$cor0`, but miss variablesSet. To fix it, we should pass variablesSet when create LogicalFilter.

Issue Links

• links to: GitHub Pull Request #18920