配置单元在NOT in条件下自动筛选NULL

Question

我在AWS中有一个名为table1的配置单元表，只有一列，如下所示：

COL1
(null)
active

正在尝试以下查询

select * 
from table1 
where COL1 NOT IN ('reversed')

Hive只返回一行

COL1
active

为什么它不返回带有(null)的记录？

据我所知，修复方法是如下修改条件。但我想调试这个问题，这样在其他查询上就不会发生这种情况

where coalesce(COL1,"") NOT IN ('reversed')

Answer 1

这是所有RDBMS系统处理null值的方式。

null有一个特殊的含义——类似于not defined

检查时：

COL1 NOT IN ('reversed')

将进行以下比较：

(null) != reversed
active != reversed
...

只有返回true的才会返回：

scala> spark.sql("SELECT 'active' != 'reversed'").show
+-------------------------+
|(NOT (active = reversed))|
+-------------------------+
|                     true|
+-------------------------+

scala> spark.sql("SELECT null != 'reversed'").show
+---------------------------------------+
|(NOT (CAST(NULL AS STRING) = reversed))|
+---------------------------------------+
|                                   null|
+---------------------------------------+

正如您所看到的：(null) != 'reversed'不返回true——这就是为什么您在结果集中看不到它

此外：

scala> spark.sql("SELECT (null) = 'reversed'").show
+---------------------------------+
|(CAST(NULL AS STRING) = reversed)|
+---------------------------------+
|                             null|
+---------------------------------+

因此，我们有IS NULL、IS NOT NULL、COALESCE等方法和函数，使我们能够使用NULL值