Databricks SQL内联表定义无法计算outer表达式报错咨询

运行环境为Azure Databricks 10.4 LTS集群，集成Apache Spark 3.2.1、Scala 2.12，连接MSSQL数据库开展查询操作，关联RetailTransactionSalesTrans_Full（别名rts）、RetailTransactionTable_Full（别名rtt）两张表，目标是获取两个timestamp类型字段的最大时间戳值，初始编写SQL如下：

SELECT 
   rts.DataLakeModified_DateTime, 
   rtt.DataLakeModified_DateTime,
   (
     SELECT MAX(DataLakeModifiedDateTime)
     FROM VALUES(rts.DataLakeModified_DateTime), (rtt.DataLakeModified_DateTime) AS AllDates (DataLakeModifiedDateTime)
   ) AS DataLakeModifiedDateTime
FROM RetailTransactionSalesTrans_Full rts
LEFT JOIN RetailTransactionTable_Full rtt
    ON rtt.TRANSACTIONID = rts.TRANSACTIONID
        AND rtt.CHANNEL = rts.CHANNEL
        AND rtt.STORE = rts.STORE
        AND rtt.TERMINAL = rts.TERMINALID

语句执行时抛出如下分析异常：

Error in SQL statement: AnalysisException: cannot evaluate expression outer(rts.DataLakeModified_DateTime) in inline table definition; line 6 pos 17

Spark SQL的VALUES子句定义内联表时，不支持直接引用外层查询的关联列，上述子查询中向内联表传入外层rts、rtt表字段的写法，违反了该语法限制，触发分析阶段报错。

优先使用Spark内置的GREATEST函数实现需求，该函数作用是返回入参列表中的最大值，原生支持timestamp类型，性能远高于相关子查询写法，修改后SQL如下：

SELECT 
   rts.DataLakeModified_DateTime, 
   rtt.DataLakeModified_DateTime,
   GREATEST(rts.DataLakeModified_DateTime, rtt.DataLakeModified_DateTime) AS DataLakeModifiedDateTime
FROM RetailTransactionSalesTrans_Full rts
LEFT JOIN RetailTransactionTable_Full rtt
    ON rtt.TRANSACTIONID = rts.TRANSACTIONID
        AND rtt.CHANNEL = rts.CHANNEL
        AND rtt.STORE = rts.STORE
        AND rtt.TERMINAL = rts.TERMINALID

兼容空值场景调整 #

如果两个时间戳字段可能存在NULL值，GREATEST默认会返回NULL，需要兼容空值时可搭配COALESCE函数处理，实现单字段为空时取非空字段值的逻辑：

GREATEST(
  COALESCE(rts.DataLakeModified_DateTime, rtt.DataLakeModified_DateTime),
  COALESCE(rtt.DataLakeModified_DateTime, rts.DataLakeModified_DateTime)
) AS DataLakeModifiedDateTime

子查询替代写法（不推荐） #

如果必须使用子查询逻辑，可替换VALUES内联表为UNION ALL拼接的结果集，规避语法限制：

SELECT 
   rts.DataLakeModified_DateTime, 
   rtt.DataLakeModified_DateTime,
   (
     SELECT MAX(DataLakeModifiedDateTime)
     FROM (
       SELECT rts.DataLakeModified_DateTime AS DataLakeModifiedDateTime
       UNION ALL
       SELECT rtt.DataLakeModified_DateTime AS DataLakeModifiedDateTime
     ) AllDates
   ) AS DataLakeModifiedDateTime
FROM RetailTransactionSalesTrans_Full rts
LEFT JOIN RetailTransactionTable_Full rtt
    ON rtt.TRANSACTIONID = rts.TRANSACTIONID
        AND rtt.CHANNEL = rts.CHANNEL
        AND rtt.STORE = rts.STORE
        AND rtt.TERMINAL = rts.TERMINALID

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