MariaDB 10.1.30版本MAX子查询无返回结果问题求助

一、带MAX函数的子查询无数据返回的原因 #

1. LEFT JOIN被隐式转为INNER JOIN：原语句中使用LEFT JOIN systeminformation si1后，又在WHERE子句中过滤si1.Id IN (...)。由于LEFT JOIN允许si1.Id为NULL，但NULL IN (...)的结果永远是FALSE，因此这条WHERE条件会自动把LEFT JOIN转换成INNER JOIN，只保留csm_installbase中能匹配到systeminformation记录的行。
2. MAX子查询结果与关联条件不匹配：单独执行子查询SELECT MAX(si3.Id), si3.IB_RecID FROM systeminformation si3 GROUP BY si3.IB_RecID，检查返回的IB_RecID是否存在于csm_installbase表中。如果这些IB_RecID在csm_installbase里没有对应记录，或者这些MAX值对应的systeminformation记录的IB_RecID和csm_installbase.Id不匹配，就会导致没有符合条件的行被返回。
3. 无聚合函数的GROUP BY行为：移除MAX函数后，子查询SELECT si3.Id FROM systeminformation si3 GROUP BY si3.IB_RecID在MariaDB 10.1（默认关闭ONLY_FULL_GROUP_BY）的情况下，会返回每个IB_RecID组中的任意一条si3.Id。恰好这些任意返回的Id对应的IB_RecID在csm_installbase中有匹配，因此能查到2条数据。

二、LIMIT子查询报错的原因 #

MariaDB 10.1版本不支持在IN/ALL/ANY/SOME子查询中使用LIMIT，这是该版本的特性限制。升级到MariaDB 10.2及以上版本后，该限制会被移除。

三、正确的查询写法（针对需求：获取每个csm_installbase对应的最新systeminformation记录的IB_RecID） #

写法1：关联子查询 #

SELECT ib.Id AS 'IB_RecID'
FROM csm_installbase ib
JOIN systeminformation si1 ON si1.IB_RecID = ib.Id
WHERE si1.Id = (SELECT MAX(si3.Id) FROM systeminformation si3 WHERE si3.IB_RecID = ib.Id)

写法2：JOIN聚合结果 #

SELECT ib.Id AS 'IB_RecID'
FROM csm_installbase ib
JOIN (
    SELECT IB_RecID, MAX(Id) AS max_si_id
    FROM systeminformation
    GROUP BY IB_RecID
) si_max ON ib.Id = si_max.IB_RecID
JOIN systeminformation si1 ON si1.IB_RecID = si_max.IB_RecID AND si1.Id = si_max.max_si_id

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