MySQL 窗口函数在select中执行顺序引发的问题

1、问题发现

研发今天发我一个SQL,说窗口函数总是得不到预期结果，我将SQL简化如下：

WITH cte AS
(
  SELECT 8  AS QTY,1049359 AS id union all
  SELECT 20  AS QTY,1049359 AS id union all
  SELECT 10  AS QTY,1049360 AS id 
)
select sum(qty),id,sum(qty) over() as s from CTE
group by id

执行结果如下

可见窗口函数sum(qty) over() as s 得到的结果为18，按照预期窗口函数结果应该是38

那为什么是这个结果呢？

2、问题根因

查看MySQL的官方文档一直是我认为排查问题的最优方案，经过查询官方文档得知，窗口函数在 Select语句中的执行顺序是在WHERE、GROUP BY和HAVING处理之后，窗口执行发生在ORDER BY、LIMIT和SELECT DISTINCT之前

Window functions are permitted only in the select list and ORDER BY clause. Query result rows are determined from the FROM clause, after WHERE, GROUP BY, and HAVING processing, and windowing execution occurs before ORDER BY, LIMIT, and SELECT DISTINCT.

官方说明

因为在group by 执行之后，才执行窗口函数，所以当id相同的两行group by后，就只剩下了第一行的qty 所以这里窗口函数的合计就变成了8+10 = 18.

MySQL在没有开ONLY_FULL_GROUP_BY时，允许select 处引用没有在group by使用的字段。
我们可以来验证它

3、问题验证

打开ONLY_FULL_GROUP_BY

set  @@sql_mode = 'ONLY_FULL_GROUP_BY'

再执行上面的SQL

WITH cte AS
(
  SELECT 8  AS QTY,1049359 AS id union all
  SELECT 20  AS QTY,1049359 AS id union all
  SELECT 10  AS QTY,1049360 AS id 
)
select sum(qty),id,sum(qty) over() as s from CTE
group by id

抛出以下错误

Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘CTE.QTY’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

3、修正写法

再嵌套一层SUM即可

WITH cte AS
(
  SELECT 8  AS QTY,1049359 AS id union all
  SELECT 20  AS QTY,1049359 AS id union all
  SELECT 10  AS QTY,1049360 AS id 
)
select sum(qty),id,sum(sum(qty)) over() as s from CTE
group by id

建议：生产环境开启ONLY_FULL_GROUP_BY