[17.0-rc][疑似平台] datetime 字段的时间窗过滤返回空集（客服仪表盘全空）

[yinlianghui]

现象

升级 @objectstack/* 到 17.0.0-rc.0 后，客服仪表盘所有 widget 显示 0 / 暂无数据，而数据库中有 38 条工单（30 条未关闭，全部在近 30 天窗口内）。

复现

1. 升级到 17.0.0-rc.0，SQLite（better-sqlite3）驱动，全新种子数据
2. 打开客服仪表盘（dateRange: last_30_days，字段 created_date，类型 datetime）
3. 所有基于 case_metrics 的 widget 返回空

widget 实际 SQL：WHERE is_closed = ? AND created_date >= ? AND created_date <= ?。列内容为 ISO 文本（如 2026-07-26T00:00:00.000Z）。同一查询手工验证：ISO 字符串参数返回 29 行，epoch 毫秒参数返回 0 行 —— 17.0 驱动把 datetime 比较参数转成了 epoch 毫秒，与磁盘上的 ISO 文本比较永假。

date 类型字段（如 close_date）不受影响（销售/高管仪表盘正常）。数据库是 17.0 自己新建的，属于写入端(ISO)/读取端(epoch)不一致（对应 spec 中 SqlDriver.temporalFilterValue 的行为）。

影响

所有 datetime 字段上的 dateRange / {30_days_ago} 类时间窗过滤。升级阻断。

详见分支 upgrade/objectstack-17 的 docs/upgrade-17/test-report.md §3 P1。

[yinlianghui]

源码确认（objectstack@fc156fa4a）：CONFIRMED，平台 bug。写路径存 ISO TEXT、过滤路径按声明类型无条件转 epoch-ms；平台自身 created_at/updated_at 同样中招；分桶路径已有 typeof() 兼容而过滤路径没有。9.10.0 埋雷（#2034），17.0 服务端占位符解析（#3582）引爆。已上报平台：objectstack-ai/objectstack#3912

[yinlianghui]

📎 分支 upgrade/objectstack-17 已推送至上游（仅分支，无 PR，勿合并——阻塞于 17.0 正式版与 objectstack#3912/#3913/#3914）。正文引用的文档现在可直接访问：测试报告 ｜ 测试计划

[yinlianghui]

Two things confirmed while fixing #460, which turns out to be this same defect seen from the 16.1.0 side.

It is the platform, and it is not 17.0-specific. Reproduced on the pinned 16.1.0 with a fresh seed, against the same case_metrics dataset the widgets use, varying only the created_date filter:

filter	rows
none	38
$gte only (2026-05-01)	38
$lte only (2026-07-30)	0
$gte + $lte (the dashboard's own window)	0
$gte + $lte, full ISO with end-of-day	0

So the rule on a datetime column is directional: $lte matches no rows, $gte matches every row (the lower bound is not applied at all — a floor that should exclude older rows doesn't). SELECT typeof(created_date) is text for all 38 rows, and binding those same bounds as strings in raw SQL returns 38.

Worth noting the $gte half separately: it fails silently in the other direction, so a widget with only a lower bound looks fine while actually being unfiltered. service_dashboard's daily_case_volume ($gte: '{30_days_ago}') is currently plotting every case, not the last 30 days — indistinguishable today only because the seed spans exactly 30 days.

Upstream tracking:

• [17.0.0-rc.0] SQLite datetime window filters return empty: filter comparands coerced to epoch-ms while writes store ISO TEXT objectstack#3912 — this exact defect, closed as completed 2026-07-29, fix in the 17.0 train.
• dashboard 的日期区间上界打在 datetime 列上丢失当天数据 —— 默认配置即命中 objectstack#3777 — open, bug/priority:p1, and a separate datetime bug: a bare YYYY-MM-DD $lte upper bound on a datetime column drops every record created after 00:00 that day, by an amount that grows through the day.

That second one matters for the upgrade plan here: taking the 17.0 fix for #3912 does not by itself make it safe to window a datetime field. #3777 has to land too, otherwise a restored date picker silently drops records created earlier the same day. Both are preconditions for re-enabling dateRange on a datetime field.

#546 works around it for service_dashboard on 16.1.0 by removing the dateRange block (the range is ANDed into every widget query, so the $lte half zeroes the whole dashboard at any preset — widening it, which is what #460 originally proposed, changes nothing). It also adds a CI guard that fails if any dashboard windows a datetime field, so this can't be reintroduced silently while the upstream fixes are outstanding. It does not resolve this issue — the platform-wide impact and the upgrade path stay open here.

---

Generated by Claude Code