关联子查询的使用限制与常见报错及解决方法-云原生数据仓库AnalyticDB-阿里云

关联子查询是一种特殊的子查询，子查询的执行依赖外部查询的当前行。关联子查询的外部查询每查询一行数据，子查询都需要运行一次，在数据量大的情况下，可能会导致查询性能下降。本文主要介绍关联子查询的用法及常见报错。

前提条件

集群内核版本需为3.1.9.0及以上版本。

说明

请在云原生数据仓库AnalyticDB MySQL控制台集群信息页面的配置信息区域，查看和升级内核版本。

使用限制

关联列的限制

说明

关联列是指子查询中包含的来自外部查询的列。

关联列仅允许出现在子查询的WHERE子句中。

关联子查询不支持跳层关联，即不支持在子查询包含的子查询中出现关联列。例如：

SELECT * FROM ts1 WHERE ts1.a > 
    (SELECT MAX(ts3.c) FROM ts3 WHERE ts3.c IN 
        (SELECT ts2.a FROM ts2 WHERE ts1.b = ts2.b ) );

子查询的限制

子查询不能位于JOIN语句中。
子查询中不能包含任意窗口函数。

子查询不能包含任何的集合操作。例如UNION等。例如：

SELECT * FROM ts1 WHERE ts1.a IN 
    (SELECT ts2.a FROM ts2 WHERE ts1.b = ts2.b UNION SELECT ts3.a FROM ts3 );

IN条件的子查询不能包含LIMIT、GROUP BY、ORDER BY、JOIN。例如：

SELECT * FROM ts1 WHERE ts1.a IN 
    (SELECT ts2.a FROM ts2 WHERE ts1.b = ts2.b LIMIT 10);

SELECT * FROM ts1 WHERE ts1.a IN 
    (SELECT AVG(ts2.a) FROM ts2 WHERE ts1.b = ts2.b GROUP BY ts2.c );

示例

使用关联子查询，查询商品的最低折扣：

SELECT id,  
    (SELECT MIN(discount)
    FROM item
    WHERE goods.id = goods_id )
FROM goods;

您也可以通过JOIN改写上述关联子查询：

SELECT id,t.min_discount
FROM goods
LEFT JOIN 
    (SELECT goods_id,
         MIN(discount) AS min_discount
    FROM item
    GROUP BY  goods_id ) t
    ON t.goods_id = goods.id;

常见报错

Given correlated subquery with correlation: [column_name] is not supported

原因：关联列仅允许出现在WHERE子句中。AnalyticDB for MySQL在执行关联子查询时，会解关联，即将关联子查询变为普通子查询，若关联列出现在子查询的SELECT子句中，会解关联失败，进而导致报错。

以如下代码为例，关联列ts1.a出现在子查询的SELECT子句中，导致关联查询无法解开，出现报错。

SELECT ts1.a,
    (SELECT SUM(ts2.a) + ts1.a
    FROM ts2
    WHERE ts1.b = ts2.b )
FROM ts1;

解决方法：

方法一：在子查询的SELECT子句中去除关联列，改写SQL。

SELECT ts1.a,    
    (SELECT SUM(ts2.a)
    FROM ts2
    WHERE ts1.b = ts2.b ) + ts1.a
FROM ts1;

方法二：通过JOIN改写关联子查询。

SELECT ts1.a,
         tt.suma + ts1.a
FROM ts1
LEFT JOIN 
    (SELECT ts2.b AS b,
         SUM(ts2.a) AS suma
    FROM ts2
    GROUP BY  ts2.b ) tt
    ON ts1.b = tt.b;

Scalar sub-query has returned multiple rows

原因：关联子查询返回结果有多行时，会出现该报错，若返回结果行数等于0或1时，不会报错。

以如下代码为例，外部查询的WHERE子句中是等值条件，且子查询的返回结果为多行，出现报错。

SELECT ts1.a
FROM ts1
WHERE ts1.a = 
    (SELECT ts2.a
    FROM ts2
    WHERE ts1.b = ts2.b );

解决方法：根据SQL语义修改SQL。根据上述查询中的语义，将外部查询的等值条件修改为IN条件。

SELECT ts1.a
FROM ts1
WHERE ts1.a IN 
    (SELECT ts2.a
    FROM ts2
    WHERE ts1.b = ts2.b );