Flask+SQLAlchemy查询MS SQL Server时GROUP BY报错(8120)

我开发了一个基于MS SQL Server的Flask后端应用，使用SQLAlchemy构建查询而非直接编写pyodbc语句，查询代码如下：

active_with_schools = db.query(
    Registration.id,
    Registration.uuid,
    Registration.last_name,
    Registration.first_name,
    Registration.email,
    Registration.phone,
    Registration.date_added,
    Registration.other_heard_from,
    Registration.other_feedback,
    func.coalesce(
        Registration.date_expired,
        datetime.now(UTC).strftime('%Y-%m-%d %H:%M:%S')
    ),
    func.coalesce(
        Registration.expired_by, ''
    ),
    func.count(School.registration_id)
).join(
    User,
    User.email == Registration.email
).join(
    School,
    School.registration_id == Registration.id,
    isouter=True
).filter(
    func.coalesce(
        Registration.date_expired,
        datetime.now(UTC).strftime('%Y-%m-%d %H:%M:%S')
    ) < datetime.now(UTC).strftime('%Y-%m-%d %H:%M:%S')
).group_by(
    Registration.id,
    Registration.uuid,
    Registration.last_name,
    Registration.first_name,
    Registration.email,
    Registration.phone,
    Registration.date_added,
    Registration.other_heard_from,
    Registration.other_feedback,
    func.coalesce(
        Registration.date_expired,
        datetime.now(UTC).strftime('%Y-%m-%d %H:%M:%S')
    ),
    func.coalesce(
        Registration.expired_by, ''
    )
).having(
    func.count(
        func.coalesce(
            School.registration_id, -1
        )
    ) > 0
).order_by(
    Registration.date_added.desc()
).all()

执行时抛出ProgrammingError异常，错误信息：

(pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Column 'MySchema.registration.date_expired' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (8120) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Column 'MySchema.registration.date_expired' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (8120); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")
[SQL: SELECT [MySchema].registration.id AS [MySchema_registration_id],
            [MySchema].registration.uuid AS [MySchema_registration_uuid],
            [MySchema].registration.last_name AS [MySchema_registration_last_name], 
            [MySchema].registration.first_name AS [MySchema_registration_first_name], 
            [MySchema].registration.email AS [MySchema_registration_email],
            [MySchema].registration.phone AS [MySchema_registration_phone],
            [MySchema].registration.date_added AS [MySchema_registration_date_added], 
            [MySchema].registration.other_heard_from AS [MySchema_registration_other_heard_from], 
            [MySchema].registration.other_feedback AS [MySchema_registration_other_feedback], 
            coalesce([MySchema].registration.date_expired, ?) AS coalesce_1,
            coalesce([MySchema].registration.expired_by, ?) AS coalesce_3,
            count([MySchema].school.registration_id) AS count_1 
FROM [MySchema].registration 
    JOIN [MySchema].[user] 
        ON [MySchema].[user].email = [MySchema].registration.email 
    LEFT OUTER JOIN [MySchema].school 
        ON [MySchema].school.registration_id = [MySchema].registration.id 
WHERE coalesce([MySchema].registration.date_expired, ?) < ? 
GROUP BY [MySchema].registration.id, 
    [MySchema].registration.uuid, 
    [MySchema].registration.last_name, 
    [MySchema].registration.first_name, 
    [MySchema].registration.email, 
    [MySchema].registration.phone, 
    [MySchema].registration.date_added, 
    [MySchema].registration.other_heard_from, 
    [MySchema].registration.other_feedback, 
    coalesce([MySchema].registration.date_expired, ?),
    coalesce([MySchema].registration.expired_by, ?) 
HAVING count(coalesce([MySchema].school.registration_id, ?)) > ? 
ORDER BY [MySchema].registration.date_added DESC]
[parameters: (
    '2024-05-16 16:36:39', 
    '', 
    '2024-05-16 16:36:39', 
    '2024-05-16 16:36:39', 
    '2024-05-16 16:36:39', 
    '', 
    -1, 
    0
)]

但将异常中生成的SQL替换参数值后，在PyCharm数据库控制台能正常执行：

SELECT [MySchema].registration.id                        AS [MySchema_registration_id],
       [MySchema].registration.uuid                      AS [MySchema_registration_uuid],
       [MySchema].registration.last_name                 AS [MySchema_registration_last_name],
       [MySchema].registration.first_name                AS [MySchema_registration_first_name],
       [MySchema].registration.email                     AS [MySchema_registration_email],
       [MySchema].registration.phone                     AS [MySchema_registration_phone],
       [MySchema].registration.date_added                AS [MySchema_registration_date_added],
       [MySchema].registration.other_heard_from          AS [MySchema_registration_other_heard_from],
       [MySchema].registration.other_feedback            AS [MySchema_registration_other_feedback],
       COALESCE([MySchema].registration.date_expired, '2024-05-16 16:36:39') AS coalesce_1,
       COALESCE([MySchema].registration.expired_by, '')   AS coalesce_3,
       COUNT([MySchema].school.registration_id)          AS count_1
    FROM [MySchema].registration
             JOIN [MySchema].[user]
                  ON [MySchema].[user].email = [MySchema].registration.email
             LEFT OUTER JOIN [MySchema].school
                             ON [MySchema].school.registration_id = [MySchema].registration.id
    WHERE COALESCE([MySchema].registration.date_expired, '2024-05-16 16:36:39') < '2024-05-16 16:36:39'
    GROUP BY [MySchema].registration.id, 
             [MySchema].registration.uuid, 
             [MySchema].registration.last_name,
             [MySchema].registration.first_name, 
             [MySchema].registration.email, 
             [MySchema].registration.phone,
             [MySchema].registration.date_added, 
             [MySchema].registration.other_heard_from,
             [MySchema].registration.other_feedback, 
             COALESCE([MySchema].registration.date_expired, '2024-05-16 16:36:39'),
             COALESCE([MySchema].registration.expired_by, '')
    HAVING COUNT(COALESCE([MySchema].school.registration_id, -1)) > 0
    ORDER BY [MySchema].registration.date_added DESC

请问使用SQLAlchemy构建查询时哪里出了问题？

• 问题核心：多次调用datetime.now(UTC).strftime('%Y-%m-%d %H:%M:%S')会生成多个独立的字符串实例，SQLAlchemy会将它们视为不同的参数，导致生成的SQL中，SELECT、WHERE、GROUP BY里的coalesce(date_expired, ?)使用了不同的占位符。SQL Server解析参数化查询时，无法识别这些占位符对应同一个时间值，错误判定date_expired字段未被包含在GROUP BY或聚合函数中，抛出8120错误。而手动替换为字面量时，SQL Server能识别所有coalesce(date_expired, 'xxx')是等价表达式，因此可以正常执行。

• 修复方案：

  1. 提前计算并复用时间变量：
     将当前时间赋值给一个变量，在所有需要的地方复用，确保SQLAlchemy将其作为同一个参数处理：

     current_utc_time = datetime.now(UTC).strftime('%Y-%m-%d %H:%M:%S')
     active_with_schools = db.query(
         Registration.id,
         Registration.uuid,
         Registration.last_name,
         Registration.first_name,
         Registration.email,
         Registration.phone,
         Registration.date_added,
         Registration.other_heard_from,
         Registration.other_feedback,
         func.coalesce(Registration.date_expired, current_utc_time),
         func.coalesce(Registration.expired_by, ''),
         func.count(School.registration_id)
     ).join(User, User.email == Registration.email)\
      .join(School, School.registration_id == Registration.id, isouter=True)\
      .filter(func.coalesce(Registration.date_expired, current_utc_time) < current_utc_time)\
      .group_by(
          Registration.id,
          Registration.uuid,
          Registration.last_name,
          Registration.first_name,
          Registration.email,
          Registration.phone,
          Registration.date_added,
          Registration.other_heard_from,
          Registration.other_feedback,
          func.coalesce(Registration.date_expired, current_utc_time),
          func.coalesce(Registration.expired_by, '')
      )\
      .having(func.count(func.coalesce(School.registration_id, -1)) > 0)\
      .order_by(Registration.date_added.desc())\
      .all()
     

  2. 让数据库端计算当前时间（更推荐）：
     使用SQLAlchemy的func.getutcdate()对应SQL Server的GETUTCDATE()函数，避免在Python端生成字符串，确保表达式在数据库层面完全统一，同时规避参数化带来的问题：

     active_with_schools = db.query(
         Registration.id,
         Registration.uuid,
         Registration.last_name,
         Registration.first_name,
         Registration.email,
         Registration.phone,
         Registration.date_added,
         Registration.other_heard_from,
         Registration.other_feedback,
         func.coalesce(Registration.date_expired, func.getutcdate()),
         func.coalesce(Registration.expired_by, ''),
         func.count(School.registration_id)
     ).join(User, User.email == Registration.email)\
      .join(School, School.registration_id == Registration.id, isouter=True)\
      .filter(func.coalesce(Registration.date_expired, func.getutcdate()) < func.getutcdate())\
      .group_by(
          Registration.id,
          Registration.uuid,
          Registration.last_name,
          Registration.first_name,
          Registration.email,
          Registration.phone,
          Registration.date_added,
          Registration.other_heard_from,
          Registration.other_feedback,
          func.coalesce(Registration.date_expired, func.getutcdate()),
          func.coalesce(Registration.expired_by, '')
      )\
      .having(func.count(func.coalesce(School.registration_id, -1)) > 0)\
      .order_by(Registration.date_added.desc())\
      .all()
     

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