Spark SQL子查询报错：无法解析`o.created_by`，PostgreSQL可正常执行

错误cannot resolve 'o.created_by'的核心原因是Spark SQL的子查询作用域规则和PostgreSQL不同：

• PostgreSQL支持多层嵌套子查询中，内层子查询直接引用外层关联表的字段；
• 但Spark SQL中，多层嵌套的子查询无法直接访问外层非直接父级的表字段（这里最内层的PROJ_USER_DF u无法访问最外层的OBU_VIN_DF o），只能访问直接父级查询的字段。

从错误日志的输入列也能看出，Spark当前只识别到最内层子查询的表u的字段，完全没感知到外层的o表。

把嵌套的子查询结构改写成JOIN方式，让关联关系在同一层级，或者使用Spark支持的LATERAL JOIN（Spark 2.3及以上版本支持）来处理关联子查询。

方案1：重写为JOIN结构（兼容性更好） #

将多层嵌套的子查询逐步展开成JOIN，避免跨层引用：

val SQLDF = spark.sqlContext.sql("""
select m.v_number, o.u_id, m.r_number, m.v_name, p.pr_name
from
    PG_DF_VHM m
    JOIN OBU_VIN_DF o ON m.v_number = o.v_number
    JOIN PROJ_NAME_DF p ON p.pr_id IN (
        SELECT pu.pr_id
        FROM PROJ_USER_DF pu
        JOIN PG_MAP_DF pm ON pu.user_id = pm.user_id
        JOIN PROJ_USER_DF u ON pm.project_user_id = u.project_user_id
        WHERE u.user_id = o.created_by AND pm.user_role_id = 3
        LIMIT 1
    )
""")

方案2：使用LATERAL JOIN（Spark 2.3+） #

LATERAL JOIN允许子查询引用JOIN左侧的表字段，更贴近原PostgreSQL的写法：

val SQLDF = spark.sqlContext.sql("""
select m.v_number, o.u_id, m.r_number, m.v_name, p.pr_name
from
    PG_DF_VHM m
    JOIN OBU_VIN_DF o ON m.v_number = o.v_number
    JOIN PROJ_NAME_DF p ON p.pr_id IN (
        SELECT pu.pr_id
        FROM PROJ_USER_DF pu
        JOIN (
            SELECT pm.user_id
            FROM PG_MAP_DF pm
            JOIN LATERAL (
                SELECT u.project_user_id
                FROM PROJ_USER_DF u
                WHERE u.user_id = o.created_by
            ) u ON pm.project_user_id = u.project_user_id
            WHERE pm.user_role_id = 3
            LIMIT 1
        ) pm ON pu.user_id = pm.user_id
    )
""")

• 尽量避免Spark SQL中多层嵌套的关联子查询，优先用JOIN替代，减少作用域问题；
• 如果必须保留子查询结构，确保内层子查询只引用直接父级的字段，或者使用LATERAL JOIN来突破作用域限制；
• 注意Spark版本对LATERAL JOIN的支持，Spark 2.3及以上才正式支持该语法。

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