Databricks SQL执行NOT EXISTS关联子查询报错SparkUnsupportedOperationException

执行包含NOT EXISTS运算符的关联子查询时触发以下错误：

Error SparkUnsupportedOperationException: [INTERNAL_ERROR] Cannot generate code for expression: outer

原SQL语句如下：

SELECT in_cs.COMM_ID AS CUSTOMER_SERVICE_EPIC_ID, 
   Data.CUR_VALUE_DATETIME AS VALUE_INSTANT,
FROM hive_metastore.RAW_CLARITY.SMRTDTA_ELEM_DATA Data
INNER JOIN hive_metastore.RAW_CLARITY.SMRTDTA_ELEM_VALUE Value
ON Data.HLV_ID = Value.HLV_ID
INNER JOIN hive_metastore.RAW_CLARITY.CLARITY_CONCEPT SmartDataElement
ON Data.ELEMENT_ID = SmartDataElement.CONCEPT_ID
INNER JOIN hive_metastore.RAW_CLARITY.CUST_SERVICE in_cs
ON Data.RECORD_ID_NUMERIC = in_cs.COMM_ID AND NOT EXISTS
  ( SELECT 1 FROM hive_metastore.RAW_CLARITY.CUST_SERVICE AS cs
    LEFT JOIN  hive_metastore.RAW_CLARITY.CAL_REFERENCE_CRM AS crc
    ON cs.COMM_ID = crc.REF_CRM_ID
    LEFT JOIN hive_metastore.RAW_CLARITY.CAL_COMM_TRACKING AS cct
    ON crc.COMM_ID = cct.COMM_ID
    WHERE cct.COMM_ID IS NULL AND in_cs.COMM_ID = cs.COMM_ID)

问题原因 #

Databricks SQL基于的Spark优化器，在处理关联子查询中嵌套多表LEFT JOIN+NOT EXISTS的组合逻辑时，无法正确解析外层表引用（in_cs.COMM_ID）的关联关系，导致代码生成环节出错。

解决方法：改写为LEFT JOIN + IS NULL形式 #

Spark对LEFT JOIN结合IS NULL的写法支持更稳定，将原NOT EXISTS逻辑转换为此形式即可解决问题：

SELECT in_cs.COMM_ID AS CUSTOMER_SERVICE_EPIC_ID, 
       Data.CUR_VALUE_DATETIME AS VALUE_INSTANT
FROM hive_metastore.RAW_CLARITY.SMRTDTA_ELEM_DATA Data
INNER JOIN hive_metastore.RAW_CLARITY.SMRTDTA_ELEM_VALUE Value
  ON Data.HLV_ID = Value.HLV_ID
INNER JOIN hive_metastore.RAW_CLARITY.CLARITY_CONCEPT SmartDataElement
  ON Data.ELEMENT_ID = SmartDataElement.CONCEPT_ID
INNER JOIN hive_metastore.RAW_CLARITY.CUST_SERVICE in_cs
  ON Data.RECORD_ID_NUMERIC = in_cs.COMM_ID
-- 提取原NOT EXISTS中的筛选逻辑为独立子查询
LEFT JOIN (
  SELECT cs.COMM_ID
  FROM hive_metastore.RAW_CLARITY.CUST_SERVICE AS cs
  LEFT JOIN hive_metastore.RAW_CLARITY.CAL_REFERENCE_CRM AS crc
    ON cs.COMM_ID = crc.REF_CRM_ID
  LEFT JOIN hive_metastore.RAW_CLARITY.CAL_COMM_TRACKING AS cct
    ON crc.COMM_ID = cct.COMM_ID
  WHERE cct.COMM_ID IS NULL
) filter_cs
  ON in_cs.COMM_ID = filter_cs.COMM_ID
-- 通过IS NULL实现原NOT EXISTS的排除逻辑
WHERE filter_cs.COMM_ID IS NULL

改写思路 #

1. 把原NOT EXISTS子查询中的筛选逻辑（找出cct.COMM_ID IS NULL的COMM_ID）提取为独立的子查询filter_cs；
2. 将外层表in_cs与filter_cs做LEFT JOIN；
3. 最后通过filter_cs.COMM_ID IS NULL筛选出不在filter_cs中的记录，完全等价于原NOT EXISTS的逻辑。

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