[jira] [Created] (FLINK-31830) Coalesce on nested fields with different nullabilities will get wrong plan

["lincoln lee (Jira)" <ji...@apache.org>]

lincoln lee created FLINK-31830:
-----------------------------------

             Summary: Coalesce on nested fields with different nullabilities will get wrong plan
                 Key: FLINK-31830
                 URL: https://issues.apache.org/jira/browse/FLINK-31830
             Project: Flink
          Issue Type: Bug
          Components: Table SQL / Planner
    Affects Versions: 1.14.6
            Reporter: lincoln lee


A test case similar to FLINK-31829, only changes the nullable field `a.np` to not null, will get a wrong plan in 1.14.x (reported from the community user):

{code}
  @Test
  def testCoalesceOnNestedColumns(): Unit = {
    val tEnv = util.tableEnv
    val tableDescriptor = TableDescriptor.forConnector("datagen")
        .schema(Schema.newBuilder
            .column("id", DataTypes.INT.notNull)
            .column("a", DataTypes.ROW(DataTypes.FIELD("np", DataTypes.INT.notNull())).nullable)
            .build)
        .build
    tEnv.createTemporaryTable("t1", tableDescriptor)
    tEnv.createTemporaryTable("t2", tableDescriptor)
    val res = tEnv.executeSql("EXPLAIN SELECT a.id, COALESCE(a.a.np, b.a.np) c1, IFNULL(a.a.np, b.a.np) c2 FROM t1 a left JOIN t2 b ON a.id=b.id where a.a is null or a.a.np is null")
    res.print()
}  

== Abstract Syntax Tree ==
LogicalProject(id=[$0], c1=[CAST($1.np):INTEGER], c2=[IFNULL($1.np, $3.np)])
+- LogicalFilter(condition=[OR(IS NULL($1), IS NULL(CAST($1.np):INTEGER))])
   +- LogicalJoin(condition=[=($0, $2)], joinType=[left])
      :- LogicalTableScan(table=[[default_catalog, default_database, t1]])
      +- LogicalTableScan(table=[[default_catalog, default_database, t2]])
{code}

the top project in the ast is wrong:  `LogicalProject(id=[$0], c1=[CAST($1.np):INTEGER], c2=[IFNULL($1.np, $3.np)])`, the `c1=[CAST($1.np):INTEGER]` relate to `COALESCE(a.a.np, b.a.np) c1` is incorrect,
but this works fine when using sql ddl to create tables
{code}
  @Test
  def testCoalesceOnNestedColumns2(): Unit = {
    val tEnv = util.tableEnv
    tEnv.executeSql(
      s"""
         |create temporary table t1 (
         |  id int not null,
         |  a row<np int not null>
         |) with (
         | 'connector' = 'datagen'
         |)
         |""".stripMargin)
    tEnv.executeSql(
      s"""
         |create temporary table t2 (
         |  id int not null,
         |  a row<np int not null>
         |) with (
         | 'connector' = 'datagen'
         |)
         |""".stripMargin)
    val res = tEnv.executeSql(
      "EXPLAIN SELECT a.id, COALESCE(a.a.np, b.a.np) c1, IFNULL(a.a.np, b.a.np) c2 FROM t1 a left JOIN t2 b ON a.id=b.id where a.a is null or a.a.np is null")
    res.print()
  }
{code}
from 1.15, the coalesce will be a new builtin function, and the ast looks correct in version 1.15+, while before 1.15 it was rewritten as `case when`






--
This message was sent by Atlassian Jira
(v8.20.10#820010)