MariaDB子查询访问外部列c.id在10.1正常10.2报1054错误如何解决

该报错是MariaDB 10.2版本对JOIN解析逻辑进行SQL标准合规修正导致的：

• 你的SQL混用了逗号分隔的隐式连接和显式LEFT JOIN，SQL标准规定显式JOIN的优先级高于隐式逗号连接
• 执行到LEFT JOIN关联rp表的子查询时，c（contracts）表还没有被纳入当前连接上下文，因此子查询无法识别外部列c.id
• 10.1及更早版本没有严格遵循该优先级规则，解析逻辑较为宽松，因此可以正常执行

方案1：将所有隐式连接改为显式INNER JOIN（推荐，符合SQL标准写法） #

统一用显式JOIN声明连接关系，保证c表在LEFT JOIN执行前已经被纳入上下文，修改后代码如下：

SELECT 
    p.id AS pid, 
    p.name AS pname, 
    c.id AS cid, 
    c.contract_code AS contr_code, 
    c.type AS ctype, 
    e.id AS eid, 
    e.state AS estate, 
    e.pay_code AS epay_code, 
    COUNT(z.id) AS damages, 
    rp.id AS resp_id, 
    rp.name AS resp_name, 
    rp.surname AS resp_surname, 
    IF(CURDATE() BETWEEN DATE_ADD(e.starts_pay_date, INTERVAL 23 DAY) AND DATE_ADD(e.starts_pay_date, INTERVAL 1 MONTH) AND e.state = 1, 1, 0) AS ending 
FROM `contracts` AS c
INNER JOIN `customers` AS p ON c.customer_id = p.id
INNER JOIN `cash` AS e ON e.contract_id = c.id
LEFT JOIN `damage` z ON e.contract_id = z.contract_id AND z.state = 1 
LEFT JOIN `customers` rp ON rp.id = ( 
    SELECT MAX(p1.id) resp 
    FROM `customers` AS p1, 
        `contract_responsible` AS r 
    WHERE c.id = r.contract_id AND p1.id = r.resp_id ) 
WHERE e.starts_pay_date <= '2021-09-08 23:59:59'
GROUP BY e.id 
ORDER BY e.inserted_date DESC 
LIMIT 50 OFFSET 0

方案2：给隐式连接的表加括号强制提升优先级 #

如果不想调整原有连接写法，可以用括号把所有逗号连接的表包裹，强制让这部分先执行，c.id就能被LEFT JOIN的子查询识别：

SELECT 
    p.id AS pid, 
    p.name AS pname, 
    c.id AS cid, 
    c.contract_code AS contr_code, 
    c.type AS ctype, 
    e.id AS eid, 
    e.state AS estate, 
    e.pay_code AS epay_code, 
    COUNT(z.id) AS damages, 
    rp.id AS resp_id, 
    rp.name AS resp_name, 
    rp.surname AS resp_surname, 
    IF(CURDATE() BETWEEN DATE_ADD(e.starts_pay_date, INTERVAL 23 DAY) AND DATE_ADD(e.starts_pay_date, INTERVAL 1 MONTH) AND e.state = 1, 1, 0) AS ending 
FROM (
    `contracts` AS c,
    `customers` AS p,
    `cash` AS e
)
LEFT JOIN `damage` z ON e.contract_id = z.contract_id AND z.state = 1 
LEFT JOIN `customers` rp ON rp.id = ( 
    SELECT MAX(p1.id) resp 
    FROM `customers` AS p1, 
        `contract_responsible` AS r 
    WHERE c.id = r.contract_id AND p1.id = r.resp_id ) 
WHERE e.starts_pay_date <= '2021-09-08 23:59:59' AND c.customer_id = p.id AND e.contract_id = c.id 
GROUP BY e.id 
ORDER BY e.inserted_date DESC 
LIMIT 50 OFFSET 0

MariaDB 10.2之后默认开启了ONLY_FULL_GROUP_BY模式，你当前写法GROUP BY仅指定了e.id，需要确认其他非聚合列均与e.id唯一对应，避免后续出现GROUP BY相关报错。

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