[FLINK-18164] null <> 'str' should be true

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Invalid
• Component/s: Table SQL / API
• Assignee: Unassigned
• Reporter: Benchao Li
• Created: 05/Jun/20 16:58
• Updated: 04/Jul/20 08:40
• Resolved: 06/Jun/20 03:46

Description

In ISO SQL standard, boolean is three-value type, including true, false, unknown.

And in Flink SQL, we also go with the SQL standard, and the unknown will be null. For example:
 

SELECT null <> null  -- null
SELECT null <> 'hello'  -- null
SELECT null = null  -- null
SELECT null <> 'hello'  -- null

 
In where clause, we evaluate `null` boolean to `false` by default. For example:

... WHERE null <> null  -- evaluate to false, there is no output
... null <> 'hello'  -- evaluate to false, there is no output
SELECT IF(null = null, 0, 1)  -- 1
SELECT IF(null <> 'hello', 0, 1) -- 1

To make null comparable to others, use can use IS [NOT] DISTINCT FROM. For example:

SELECT null IS NOT DISTINCT FROM null  -- true
SELECT null IS DISTINCT FROM 'hello'  -- true

 

~~~~~~~_{original description}~~~~~~~

Currently, if we compare null with other literals, the result will always be false.
It's because the code gen always gives a default value (false) for the result. And I think it's a bug if `null <> 'str'` is false.

It's reported from user-zh: http://apache-flink.147419.n8.nabble.com/flink-sql-null-false-td3640.html
CC jark ykt836