MariaDB子查询无法引用父表：同查询MySQL可正常执行

原因 #

MariaDB和MySQL在嵌套子查询的作用域解析规则上存在差异：

• MySQL允许深层嵌套的子查询直接引用外层查询的表别名；
• 但MariaDB的作用域解析更严格：当子查询中出现与外层同名的表/别名时，会优先使用当前子查询内的对象，且不支持跨过多层级引用外层表字段，导致原查询中最内层子查询无法识别Album.ArtistId。

解决办法 #

方法1：简化查询结构，直接关联聚合 #

去掉冗余的嵌套子查询，通过JOIN关联表后直接生成JSON结果：

with Artist(ArtistId, Name) as (
    select 1 as ArtistId, 'AC/DC' as Name
    union all
    select 2 as ArtistId, 'Accept' as Name
), Album(AlbumId, Title, ArtistId) as (
    select 1 as AlbumId, 'For Those About To Rock We Salute You' as Title, 1 as ArtistId
    union all
    select 2 as AlbumId, 'Balls to the Wall' as Title, 2 as ArtistId
)
select
    json_object(
        'Artist',
        json_arrayagg(json_object('Name', a.Name))
    ) as data
from Album al
join Artist a on al.ArtistId = a.ArtistId
group by al.AlbumId, al.Title, al.ArtistId;

方法2：使用LATERAL JOIN传递外层字段（MariaDB 10.2+支持） #

通过LATERAL JOIN将外层Album的字段传递给子查询，彻底避免作用域冲突：

with Artist(ArtistId, Name) as (
    select 1 as ArtistId, 'AC/DC' as Name
    union all
    select 2 as ArtistId, 'Accept' as Name
), Album(AlbumId, Title, ArtistId) as (
    select 1 as AlbumId, 'For Those About To Rock We Salute You' as Title, 1 as ArtistId
    union all
    select 2 as AlbumId, 'Balls to the Wall' as Title, 2 as ArtistId
)
select
    json_object('Artist', j.artist_json) as data
from Album al
join lateral (
    select json_arrayagg(json_object('Name', Name)) as artist_json
    from Artist a
    where a.ArtistId = al.ArtistId
) j on true;

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