MYSQL 模式中 GROUP BY 不包含所有的非聚合字段时的注意事项

问题现象

实际业务 SQL 执行 select count(1) 和 select tb.* from 两条 SQL 对相同的表做查询结果集不一致，预期 select tb.* from 查询结果为 1 行，实际 select count(1) 的结果也应该是 1 行。

select tb.* from，如下所示。

select count(1) 计划
=====================================================
|ID|OPERATOR             |NAME|EST.ROWS|EST.TIME(us)|
-----------------------------------------------------
|0 |SCALAR GROUP BY      |    |1       |108         |
|1 |└─SUBPLAN SCAN       |t   |4       |108         |
|2 |  └─HASH GROUP BY    |    |4       |108         |
|3 |    └─TABLE FULL SCAN|t1  |42      |96          |
=====================================================
Outputs & filters:
-------------------------------------
  0 - output([T_FUN_COUNT(*)(0x7fa353882010)]), filter(nil), rowset=256
      group(nil), agg_func([T_FUN_COUNT(*)(0x7fa353882010)])
  1 - output(nil), filter(nil), rowset=256
      access(nil)
  2 - output([1]), filter([DATE_FORMAT(t1.payTime(0x7fa35386be30), '%Y-%m')(0x7fa35389d950) >= '2024-10'(0x7fa35389e1c0)], [DATE_FORMAT(t1.payTime(0x7fa35386be30),
       '%Y-%m')(0x7fa35389d950) <= '2024-11'(0x7fa35389eb10)]), rowset=256
      group([t1.recoverParkId(0x7fa353868500)], [t1.escapeParkId(0x7fa353864d20)]), agg_func(nil)
  3 - output([t1.escapeParkId(0x7fa353864d20)], [t1.recoverParkId(0x7fa353868500)], [DATE_FORMAT(t1.payTime(0x7fa35386be30), '%Y-%m')(0x7fa35389d950)]), filter([t1.recoverParkId(0x7fa353868500) 
      != t1.escapeParkId(0x7fa353864d20)(0x7fa35386a5e0)], [t1.state(0x7fa353869a50) = 3(0x7fa353869300)]), rowset=256
      access([t1.escapeParkId(0x7fa353864d20)], [t1.recoverParkId(0x7fa353868500)], [t1.state(0x7fa353869a50)], [t1.payTime(0x7fa35386be30)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false,false], 
      range_key([t1.id(0x7fa353883b00)]), range(MIN ; MAX)always true

SELECT count(1) FROM
  (
    select
      recoverMonth,
      recoverParkName,
      escapeParkName,
      payCount,
      payinAmount,
      startDate,
      endDate
    FROM
      (
        SELECT
          t.recoverMonth,
          t.escapeParkName,
          t.payinAmount,
          t.recoverParkName,
          t.payCount,
          startDate,
          endDate
        FROM
          (
            SELECT
              DATE_FORMAT(t1.payTime, '%Y-%m') AS recoverMonth,
              t2.lot_name AS escapeParkName,
              t3.lot_name AS recoverParkName,
              round(sum(t1.payinAmount) / 100, 2) AS payinAmount,
              count(*) AS payCount,
              DATE_FORMAT(
                LAST_DAY(DATE_SUB(t1.payTime, INTERVAL 1 MONTH)),
                '%Y-%m-%d'
              ) AS startDate,
              DATE_FORMAT(
                LAST_DAY(t1.payTime) - INTERVAL 1 DAY,
                '%Y-%m-%d'
              ) AS endDate
            FROM
              t1
              LEFT JOIN t2 ON t1.escapeParkId = t2.lot_id
              LEFT JOIN t3 ON t1.recoverParkId = t3.lot_id
            WHERE
              t1.state = 3
              AND t1.recoverParkId != t1.escapeParkId
            GROUP BY
              t1.recoverParkId,
              t1.escapeParkId
          ) t
        WHERE 1 = 1  AND recoverMonth between '2024-10' and '2024-11'
       
      ) as a
  ) as tb

关键信息

OceanBase 数据库 MySQL 兼容 MySQL 模式中 GROUP BY 不包含所有的非聚合字段时的注意事项，OceanBase 数据库默认的 sql mode 中是没增加约束查询必须是聚合的列所以执行不报错。

问题原因

主要是 SQL 中有个子查询中的 GROUP BY 写法不标准导致的执行结果不一致，这个查询每一个（recoverParkId, escapeParkId）GROUP BY 分组里，recoverMonth，escapeParkName，recoverParkName 都是返回分组内的某一行数据，SQL 引擎只能保证这三个投影项投出去的都是分组里确实存在的数据，但无法保证具体返回哪一行数据。

举例说明。

CREATE TABLE `student` (
  `sno` varchar(20) NOT NULL,
  `sname` varchar(20) DEFAULT NULL,
  `ssex` varchar(20) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  `sdept` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`sno`)
)

下面是 OceanBase 数据库 MySQL 模式和原生 MYSQL 的对比，OceanBase 数据库默认的 sql mode 中是没增加约束查询必须是聚合的列所以执行不报错。

• OceanBase 数据库 MySQL 模式。

  -- OceanBase 数据库 MySQL 模式
  -- 非严格模式
  OceanBase(root@test)> SELECT sname, sdept, MAX(sage) FROM student GROUP BY sname;
  Empty set (0.16 sec)
  
  OceanBase(root@test)> explain SELECT sname, sdept, MAX(sage) FROM student GROUP BY sname;
  +---------------------------------------------------------------------------------------------------+
  | Query Plan                                                                                        |
  +---------------------------------------------------------------------------------------------------+
  | ====================================================                                              |
  | |ID|OPERATOR         |NAME   |EST.ROWS|EST.TIME(us)|                                              |
  | ----------------------------------------------------                                              |
  | |0 |HASH GROUP BY    |       |1       |5           |                                              |
  | |1 |└─TABLE FULL SCAN|student|1       |4           |                                              |
  | ====================================================                                              |
  | Outputs & filters:                                                                                |
  | -------------------------------------                                                             |
  |   0 - output([student.sname], [student.sdept], [T_FUN_MAX(student.sage)]), filter(nil), rowset=16 |
  |       group([student.sname]), agg_func([T_FUN_MAX(student.sage)])                                 |
  |   1 - output([student.sname], [student.sdept], [student.sage]), filter(nil), rowset=16            |
  |       access([student.sname], [student.sdept], [student.sage]), partitions(p0)                    |
  |       is_index_back=false, is_global_index=false,                                                 |
  |       range_key([student.sno]), range(MIN ; MAX)always true                                       |
  +---------------------------------------------------------------------------------------------------+
  14 rows in set (0.01 sec)
  
  OceanBase(root@test)> show variables like "%sql_mode%";
  +---------------+-------------------------------------------------------+
  | Variable_name | Value                                                 |
  +---------------+-------------------------------------------------------+
  | sql_mode      | STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER |
  +---------------+-------------------------------------------------------+
  1 row in set (0.02 sec)
  
  -- 增加约束 ONLY_FULL_GROUP_BY  该设置则约束查询必须是聚合的列
  OceanBase(root@test)> set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER';
  Query OK, 0 rows affected (0.00 sec)
  
  OceanBase(root@test)> SELECT sname, sdept, MAX(sage) FROM student GROUP BY sname;
  ERROR 1055 (42000): nonaggregated column 'test.s.sno' is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  
  -- 去掉 sdept 列，投影列中就不存在非聚合的列，所以正常执行
  OceanBase(root@test)> SELECT sname, MAX(sage) FROM student GROUP BY sname;
  Empty set (0.00 sec)
  

• 原生 MySQL。

   # 1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.s.sno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    mysql> SELECT sname, sdept, MAX(sage) FROM student GROUP BY sname;
    ERROR 1055 (42000): Expression
  
    mysql> show variables like "%sql_mode%";
    +---------------+-----------------------------------------------------------------------------------------------------------------------+
    | Variable_name | Value                                                                                                                 |
    +---------------+-----------------------------------------------------------------------------------------------------------------------+
    | sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
    +---------------+-----------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.02 sec)
  
    -- ONLY_FULL_GROUP_BY  该设置则约束查询必须是聚合的列
    mysql> set  sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
    Query OK, 0 rows affected (0.00 sec)
  
    -- 非严格模式所以不报错
    mysql> SELECT sname, sdept, MAX(sage) FROM student GROUP BY sname;
    Empty set (0.00 sec)
  

问题的风险及影响

执行结果不稳定。

影响租户

影响 OceanBase 数据库中的 SYS 租户和 MySQL 租户，对于 Oracle 租户无影响。

适用版本

OceanBase 数据库所有版本。

解决方法

推荐正常写法 GROUP BY 不包含所有的非聚合字段时需要注意。

-- 增加约束 ONLY_FULL_GROUP_BY  该设置则约束查询必须是聚合的列
OceanBase(root@test)>set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER';
Query OK, 0 rows affected (0.00 sec)

OceanBase(root@test)> SELECT sname, sdept, MAX(sage) FROM student GROUP BY sname;
ERROR 1055 (42000): nonaggregated column 'test.s.sno' is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

-- 去掉 sdept 列，投影列中就不存在非聚合的列，所以正常执行
OceanBase(root@test)> SELECT sname, MAX(sage) FROM student GROUP BY sname;
Empty set (0.00 sec)

规避方式

推荐正常写法 GROUP BY 不包含所有的非聚合字段时需要注意。

详情参见：MySQL Handling of GROUP BY。