Full Join表现为Inner Join问题排查（多连接条件无WHERE子句）

在Spark on Hive环境执行多条件FULL JOIN查询，预期获取两表所有匹配及不匹配的数据组合，但实际发现Table2中含"Unknown"的行被过滤，查询效果等同于Inner Join。

背景说明：category1、category2、category3字段在元数据缺失时会被硬编码为"Unknown"，该值为普通文本字符串，FULL JOIN应保留Table2中的这类行。已排查同类问题常见的WHERE子句过滤原因，但本次查询无WHERE子句，需排查其他可能原因。

查询语句如下：

SELECT a.date AS date,
       a.group AS group,
       a.category1,
       a.category2,
       a.category3,
       COALESCE(a.metric1,0) AS signups,
       COALESCE(b.metric2,0) AS gnc 
FROM table1 a
FULL JOIN table2 b 
       ON a.date = b.date
      AND a.group= b.group
      AND LOWER(a.category1) = LOWER(b.category1)
      AND LOWER(a.category2) = LOWER(b.category2)
      AND LOWER(a.category3) = LOWER(b.category3) 

表结构及示例数据：

Table 1 #

Table 2 #

排查方向 #

• 验证"Unknown"的实际值：确认Table2中的"Unknown"是真实字符串，而非NULL被可视化显示为"Unknown"。如果是NULL，LOWER(b.category1)会返回NULL，而NULL与任何值比较都不相等，导致这些行无法匹配也无法被保留。可执行SELECT * FROM table2 WHERE category1 IS NULL验证。
• 检查SELECT子句的字段引用：当前SELECT的category字段均来自左表a，当右表b无匹配行时，这些字段会显示为NULL，可能被误判为行未保留。修改SELECT语句，改用COALESCE(a.category1, b.category1) AS category1这类方式合并两表字段，再查看结果是否包含Table2的"Unknown"行。
• 核对大小写转换后的匹配逻辑：确认Table2中"Unknown"转小写为"unknown"后，Table1对应日期、group下是否存在同值的category数据。即使Table1无匹配，FULL JOIN也应保留右表行，但需检查结果中是否存在a字段为NULL、b字段为"Unknown"的记录，避免误判。
• 对比字段数据类型：检查两表的date、group、category字段数据类型是否完全一致（比如date字段一个是字符串、一个是日期类型），隐式类型转换可能导致JOIN条件不成立，进而影响FULL JOIN的结果保留。可通过DESCRIBE table1和DESCRIBE table2对比字段类型。
• 排查Spark优化器干扰：Spark的成本优化器（CBO）可能对FULL JOIN做了不合理的优化，尝试关闭相关优化规则，比如执行SET spark.sql.optimizer.excludedRules=org.apache.spark.sql.catalyst.optimizer.PushPredicateThroughJoin后重新运行查询，观察结果变化。

内容的提问来源于stack exchange，提问作者Ashwin Shankar