[FLINK-21162] FLINK SQL IF function semantic incorrect - ASF JIRA

XML

Word

Printable

JSON

I have a job using IF() test condition.

when i use "IF(col = '', 'a', 'b'), WHEN col = '' return a ", it is ok.

when i use "IF(col IS NULL, 'a', 'b'), WHEN col = NULL return a ", it is ok.

when i use "IF(col = '' OR col IS NULL, 'a', 'b'),WHEN col = ''return a", it is ok.

when i use "IF(col = '' OR col IS NULL, 'a', 'b'),WHEN col = NULL return b" ,that's amazing.

The semantic seems like inccorrect.

my table ddl:

CREATE TABLE print(a STRING, b STRING) WITH ( 'connector' = 'print' );

my dml :

INSERT INTO print
SELECT return_flag,
IF(return_flag = '' OR return_flag IS NULL, 'N', 'Y') returnFlag
FROM header;

my test data:

3> +I(,N)
5> +I(X,Y)

1> +I(null,Y)
2> +I(,N)
5> +I(null,Y)
3> +I(,N)

is duplicated by

FLINK-22015 SQL filter containing OR and IS NULL will produce an incorrect result.

is related to

FLINK-22015 SQL filter containing OR and IS NULL will produce an incorrect result.

links to

GitHub Pull Request #14872