多条件SQL ORDER BY子句失效排查：首CASE排序被忽略问题

当前使用的排序SQL片段如下：

ORDER BY
  case when (app.pricing 
             AND app.enforce_reporting
             AND (uas.count IS NULL OR uas.count_reported_at IS NULL)
            ) then 1 end DESC,  
  LEFT(sst.name, 3) DESC, 
  uas.updated_at DESC;

其中app.pricing和app.enforce_reporting为布尔类型字段。实际执行时，第一个ORDER BY CASE条件未生效，查询仅按LEFT(sst.name, 3) DESC和uas.updated_at DESC排序。

样本数据 #

AppPricingPlan 记录 #

#<AppPricingPlan id: "b2de59bd03da4c8cb130cd434ce6e1d1", application_id: "f34cdb27341a4e7498604172b677bc38", pricing: true, plan_name: "$2 pricing", enforce_reporting: true>
#<AppPricingPlan id: "2dde59bd03da4c8cb130cd434ce6e1d1", application_id: "f345db27341a4e7498604172b677bc38", pricing: false, plan_name: "$4 pricing", enforce_reporting: false>
#<AppPricingPlan id: "85e4505eea5440fca1d492e96c5f513b", application_id: "d745db27341a4e7498604172b677bc38", pricing: true, plan_name: "$4 pricing", enforce_reporting: true>

UserAppSale 记录 #

#<UserAppSale id: "7e18a8f2c7164042badc35b6835139ef", user_id: "e376d378869e45bc96cf3061a45aj664", application_id: "1c4d242e38e9463d835e27322e6f353c", app_pricing_plan_id: "2b6e59bd03da4c8cb130cd434ce6e1d1", active: true, created_at: "2023-03-16 15:23:34", updated_at: "2023-03-17 12:32:07", count: 22, count_reported_at: "2023-03-17 12:32:07">
#<UserAppSale id: "9e18a8f2c7164042badc35b6835139ef", user_id: "e906d378869e45bc96cf3061a45aj664", application_id: "1c4d242e38e9463d835e27322e6f353c", app_pricing_plan_id: "85e4505eea5440fca1d492e96c5f513b", active: true, created_at: "2022-03-06 15:23:34", updated_at: "2022-03-17 12:32:07", count: 0, count_reported_at: nil>
#<UserAppSale id: "2f18a8f2c7164042badc35b6835139ef", user_id: "e906d378869e45bc96cf3061a45aj664", application_id: "1c4d242e38e9463d835e27322e6f353c", app_pricing_plan_id: "b2de59bd03da4c8cb130cd434ce6e1d1", active: true, created_at: "2023-03-11 05:23:34", updated_at: "2022-03-14 12:32:07", count: 0, count_reported_at: nil>

预期排序结果 #

#<UserAppSale id: "9e18a8f2c7164042badc35b6835139ef"...>
#<UserAppSale id: "2f18a8f2c7164042badc35b6835139ef"...>
#<UserAppSale id: "7e18a8f2c7164042badc35b6835139ef"...>

• 表关联错误：查询中app（AppPricingPlan）与uas（UserAppSale）的关联条件可能存在问题，导致app.pricing和app.enforce_reporting未取到当前uas记录对应的定价计划字段值。比如样本数据中第一条UserAppSale的app_pricing_plan_id为2b6e59bd03da4c8cb130cd434ce6e1d1，但AppPricingPlan中对应的ID是2dde59bd03da4c8cb130cd434ce6e1d1（存在字符差异），这类拼写错误会导致关联失败，app字段为NULL，CASE条件无法触发。
• CASE语句缺少ELSE分支：当前CASE仅在条件满足时返回1，不满足时返回NULL。部分数据库对NULL在DESC排序中的处理是排在非NULL值之后，但如果所有行的CASE结果要么是1要么是NULL，理论上满足条件的行应该靠前；但如果存在其他异常导致CASE结果全为NULL，就会直接进入后续排序条件。建议补充ELSE分支明确权重：

  case when (app.pricing 
             AND app.enforce_reporting
             AND (uas.count IS NULL OR uas.count_reported_at IS NULL)
            ) then 1 else 0 end DESC
  

• 布尔字段兼容性问题：不同数据库对布尔类型的存储和判断逻辑有差异，比如部分数据库将布尔值存为字符串（'t'/'f'）或整数（1/0），直接用app.pricing作为条件可能无法正确识别。可以尝试显式指定判断逻辑，比如app.pricing = true或app.pricing = 1（根据数据库类型调整）。
• sst.name字段干扰：如果LEFT(sst.name,3)的值在满足CASE条件的行中排序优先级覆盖了CASE结果，也会造成CASE条件被忽略的假象。可以单独测试仅保留CASE条件的排序，验证其是否生效。

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