[FLINK-30966] Flink SQL IF FUNCTION logic error

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.16.0, 1.16.1
• Fix Version/s: 1.18.0, 1.16.3, 1.17.2
• Component/s: Table SQL / Planner
• Labels: pull-request-available
• Assignee: Shuiqiang Chen
• Reporter: 谢波
• Created: 08/Feb/23 13:20
• Updated: 20/Jul/23 04:21
• Resolved: 06/Jun/23 02:54

Description

my data is 

//
{ "before": { "status": "sent" }, "after": { "status": "succeed" }, "op": "u", "ts_ms": 1671926400225, "transaction": null } 

my sql is 

 

CREATE TABLE t
(
    before ROW (

        status varchar (32)

        ),
    after ROW (

        status varchar (32)

        ),
    ts_ms                bigint,
    op                   string,
    kafka_timestamp      timestamp METADATA FROM 'timestamp',
--     @formatter:off
    proctime AS PROCTIME()
--     @formatter:on
) WITH (
    'connector' = 'kafka',
--     'topic' = '',
    'topic' = 'test',
    'properties.bootstrap.servers' = ' ',
    'properties.group.id' = '',
    'format' = 'json',
    'scan.topic-partition-discovery.interval' = '60s',
    'scan.startup.mode' = 'earliest-offset',
    'json.ignore-parse-errors' = 'true'
 );
create table p
(
    status                  STRING ,
    before_status                  STRING ,
    after_status                  STRING ,
    metadata_operation      STRING COMMENT '源记录操作类型',
    dt                      STRING
)WITH (
    'connector' = 'print'
 );
INSERT INTO p
SELECT
       IF(op <> 'd', after.status, before.status),
        before.status,
        after.status,
       op                                         AS metadata_operation,
       DATE_FORMAT(kafka_timestamp, 'yyyy-MM-dd') AS dt
FROM t;

 

 my local env output is 

 

 

+I[null, sent, succeed, u, 2023-02-08] 

 

 my produtionc env output is 

+I[sent, sent, succeed, u, 2023-02-08]  

why?  
This look like a bug.

 

Issue Links

• duplicates: FLINK-30559 May get wrong result for `if` expression if it's string data type
• fixes: FLINK-30018 NPE error in generated StreamExecCalc
• fixes: FLINK-30559 May get wrong result for `if` expression if it's string data type
• is duplicated by: FLINK-30559 May get wrong result for `if` expression if it's string data type
• is duplicated by: FLINK-31653 Using`if` statement for a string subtype of the row type may meet npe in code generated by codegen
• links to: GitHub Pull Request #21927
• links to: GitHub Pull Request #22712
• links to: GitHub Pull Request #22713