Swift GRDB中部分SQLite查询失效，与inloop表现一致的原因解析

问题背景 #

开发基于Swift 5的iOS应用，使用GRDB库操作SQLite数据库。在Mac上用inloop和DB Browser for SQLite工具调试时发现：部分SQL查询在DB Browser中可正常返回结果，但在inloop中无报错也无结果，GRDB中同样无法正常工作。已找到替代查询语句可在GRDB中运行，推测问题出在strftime函数的使用上。

失效查询语句 #

select
  sum(e.amount) as Total,
  strftime("%Y%m%d", entrydate / 1000, 'unixepoch') as day,
  count(c.cid) as trxcount,
  group_concat(
    DISTINCT(
      c.first_name || ' ' || ifnull(c.middle_name, '') || ' ' || ifnull(c.last_name, '')
    )
  ) as parties
from
  LedgerEntry e
  inner join Customer c on e.customer_id = c.cid
where
  e.entrydate >= 1655271792800
  and e.entrydate <= 1655271792868
  and e.type = 0
group by
  day
order by
  day asc

GRDB实现代码 #

import Foundation
import GRDB

class SBC_DailyBalanceByType : Codable, FetchableRecord, PersistableRecord {
    var total : Double?
    var day : Int?
    var trxcount : Int?
    var parties : String?
}

class SB_DailyBalanceByType : NSObject {
    static let shared = SB_DailyBalanceByType()
    static var arrData = [SBC_DailyBalanceByType]()
    
    func GetBal(type: Int, startDate:Int, endDate:Int, completion: @escaping ([SBC_DailyBalanceByType]?) -> ()) {
        do {
            let dbQueue = try DatabaseQueue(path: SBGlobal.filePath)
            
            let query = "select sum(e.amount) as Total,strftime(\"%Y%m%d\", entrydate / 1000, 'unixepoch') as day,count(c.cid) as trxcount,group_concat(DISTINCT(c.first_name || ' ' || ifnull(c.middle_name, '') || ' ' || ifnull(c.last_name, ''))) as parties from LedgerEntry e inner join Customer c on e.customer_id = c.cid where e.entrydate >= \(1655271792800) and e.entrydate <= \(1655271792868) and e.type = \(0) group by day order by day asc"

            let players: [SBC_DailyBalanceByType] = try dbQueue.read { db in
                try SBC_DailyBalanceByType.fetchAll(db, sql: query)
            }
            
            SB_DailyBalanceByType.arrData.append(contentsOf: players)
            print(players)
        } catch {
            print(error)
        }
        completion(SB_DailyBalanceByType.arrData)
    }
}

替代可行查询语句 #

SELECT
  SUM(e.amount) AS Total,
  SUBSTR(DATE('1970-01-01', '+' || (e.entrydate / 1000) || ' SECONDS'), 1, 10) AS day,
  COUNT(c.cid) AS trxcount,
  GROUP_CONCAT(
    COALESCE(c.first_name, '') || ' ' || COALESCE(c.middle_name, '') || ' ' || COALESCE(c.last_name, '')
  ) AS parties
FROM
  LedgerEntry e
  INNER JOIN Customer c ON e.customer_id = c.cid
WHERE
  e.entrydate BETWEEN 1655271792800
  AND 1655271792868
  AND e.type = 0
GROUP BY
  day
ORDER BY
  day ASC;

核心原因分析 #

1. SQLite版本差异导致strftime行为不一致 #

DB Browser for SQLite通常搭载较新版本的SQLite，这类版本允许将整数类型的秒级时间戳配合unixepoch修饰符传入strftime函数，能正确解析并生成日期字符串。而GRDB依赖的iOS系统内置SQLite版本（或inloop使用的版本）相对较旧，旧版本要求strftime配合unixepoch时的时间参数必须是浮点类型，整数类型的参数无法被正确识别，导致strftime("%Y%m%d", ...)返回NULL。最终group by day基于NULL分组，要么无符合条件的结果，要么结果无法正常映射到数据模型。

2. 整数除法的类型处理差异 #

不同SQLite环境对整数除法的结果类型处理不同：部分环境会自动将entrydate / 1000的整数结果转为浮点类型，而另一些环境保持整数类型。strftime对浮点类型的时间参数兼容性更好，整数类型在旧版本中容易出现解析失败的问题。

3. GROUP_CONCAT(DISTINCT)的语法兼容性问题 #

原查询中group_concat(DISTINCT(...))的括号写法不符合SQLite标准语法（正确写法为group_concat(DISTINCT 表达式)），虽然新版本SQLite能兼容该写法，但旧版本可能无法识别，进而导致查询无结果或失败。

替代方案有效的原因 #

替代查询通过DATE('1970-01-01', '+' || (e.entrydate / 1000) || ' SECONDS')的方式生成日期，这种字符串拼接的时间偏移指令在新旧SQLite版本中兼容性都很好，能稳定计算出正确日期；同时使用标准SQL函数COALESCE代替ifnull、去掉DISTINCT的多余括号，进一步提升了语法兼容性。

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