同一SQL查询在不同平台执行结果不同的原因排查

执行以下SQL查询时，在PhpMyAdmin和Flyspeed两个平台得到了不同结果：

Select
    untdchem_db2.customer_credits.CustID,
    untdchem_db2.customers.CustCompanyName,
    (To_Days(CurDate()) - To_Days(untdchem_db2.customer_credits.CustCreditDate)) As days_past_due,
    Sum(If(((Select
         days_past_due) Between 1 And 30), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As zero_30,
    Sum(If(((Select
         days_past_due) Between 31 And 40), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As thirty_40,
    Sum(If(((Select
         days_past_due) Between 41 And 50), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As forty_50,
    Sum(If(((Select
         days_past_due) Between 51 And 60), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As fifty_60,
    Sum(If(((Select
         days_past_due) Between 61 And 90), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As sixty_90,
    Sum(If(((Select
         days_past_due) > 90), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As ninety_plus
From
    customer_credits Inner Join
    customers On customer_credits.CustID = customers.CustID
Where
    customer_credits.CustCreditTakenDate Is Null
Group By
    customer_credits.CustID;

• 非标准SQL的解析差异：你在Sum函数里用(Select days_past_due)引用SELECT子句定义的别名，这属于非标准SQL写法。MySQL规范中，SELECT子句的别名不能在同一SELECT的其他表达式（尤其是子查询）中直接引用。PhpMyAdmin和Flyspeed的SQL解析器对这种非标准语法的容错逻辑不同：一个可能错误地将子查询解析为当前行的days_past_due值，另一个可能解析失败导致条件始终不成立（返回0），最终统计结果不同。
• GROUP BY模式差异：MySQL的ONLY_FULL_GROUP_BY严格模式要求，SELECT中的非聚合字段必须出现在GROUP BY子句中。你的SQL里GROUP BY仅指定了customer_credits.CustID，但SELECT包含CustCompanyName和days_past_due（非聚合、非GROUP BY字段）。如果某一工具关闭了ONLY_FULL_GROUP_BY，会随机返回分组内某一行的days_past_due值，不同工具的执行计划不同，取的行不一样，进而影响后续Sum的统计结果。
• 日期/时区设置差异：如果两个工具连接的数据库服务器不是同一个，或者同一服务器但工具的时区配置不同，CurDate()返回的当前日期会有差异，导致days_past_due的计算值不同，最终分组统计的数值自然不同。

为了避免解析差异，直接使用days_past_due的计算式替代子查询引用，同时补全GROUP BY字段以符合严格模式要求：

SELECT
    cc.CustID,
    c.CustCompanyName,
    (TO_DAYS(CURDATE()) - TO_DAYS(cc.CustCreditDate)) AS days_past_due,
    SUM(IF((TO_DAYS(CURDATE()) - TO_DAYS(cc.CustCreditDate)) BETWEEN 1 AND 30, cc.CustCreditTotalAmount, 0)) AS zero_30,
    SUM(IF((TO_DAYS(CURDATE()) - TO_DAYS(cc.CustCreditDate)) BETWEEN 31 AND 40, cc.CustCreditTotalAmount, 0)) AS thirty_40,
    SUM(IF((TO_DAYS(CURDATE()) - TO_DAYS(cc.CustCreditDate)) BETWEEN 41 AND 50, cc.CustCreditTotalAmount, 0)) AS forty_50,
    SUM(IF((TO_DAYS(CURDATE()) - TO_DAYS(cc.CustCreditDate)) BETWEEN 51 AND 60, cc.CustCreditTotalAmount, 0)) AS fifty_60,
    SUM(IF((TO_DAYS(CURDATE()) - TO_DAYS(cc.CustCreditDate)) BETWEEN 61 AND 90, cc.CustCreditTotalAmount, 0)) AS sixty_90,
    SUM(IF((TO_DAYS(CURDATE()) - TO_DAYS(cc.CustCreditDate)) > 90, cc.CustCreditTotalAmount, 0)) AS ninety_plus
FROM
    untdchem_db2.customer_credits cc
INNER JOIN
    untdchem_db2.customers c ON cc.CustID = c.CustID
WHERE
    cc.CustCreditTakenDate IS NULL
GROUP BY
    cc.CustID, c.CustCompanyName;

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