[FLINK-16578] join lateral table function with condition fails with exception

• Type: Bug
• Status: Open
• Priority: Not a Priority
• Resolution: Unresolved
• Affects Version/s: 1.10.0
• Component/s: Table SQL / Planner
• Labels: auto-deprioritized-major auto-deprioritized-minor
• Assignee: Unassigned
• Reporter: Shuo Cheng
• Created: 13/Mar/20 03:07
• Updated: 17/Nov/21 22:38

Description

Reproducing:

 

// CorrelateITCase.scala
@Test
def testJoinTableFunction(): Unit = {
  registerFunction("func", new TableFunc2)
  val sql =
    """
      | select
      |   c, s, l
      | from inputT JOIN LATERAL TABLE(func(c)) as T(s, l)
      | on s = c
      |""".stripMargin
  checkResult(sql, Seq())
}

The it case will be failed with exception: "Cannot generate a valid execution plan for the given query".

 Firstly, for the given sql, the logical plan produced after decorrelating is already wrong, which is a  bug introduced by CALCITE-2004, and fixed in CALCITE-3847 (fixed versions 1.23).

Secondly, even after the fix, we may fail in `FlinkCorrelateVariablesValidationProgram`, because after decorrelating, there exists correlate variable in a `LogicalFilter`. we should fix the validation problem.

Issue Links

• Dependent: FLINK-16579 Upgrade Calcite version to 1.26 for Flink SQL