TIMESTAMP column predicates with < / <= / BETWEEN silently match zero rows; >= and > work

[mkhairi]

Tested against: origin/main @ 8e84501a (post-v0.3.0 main head, 2026-07-07)

Severity: High — date-range filtering is a core query shape; the wrong
half of the comparison space returns empty with no error, so consumers see
plausible-but-wrong results instead of a failure.

Summary

On a document_strict collection, a WHERE predicate comparing a
TIMESTAMP column against a literal with < or <= (and therefore
BETWEEN, which needs the upper bound) matches zero rows silently,
even when the stored value is clearly inside the bound. The mirrored
predicates >= / > on the same column and data work correctly, and
INTEGER columns compare correctly in both directions — the defect is
specific to TIMESTAMP upper-bound evaluation.

Repro

CREATE COLLECTION tscmp_repro (id TEXT PRIMARY KEY, t TIMESTAMP, n INTEGER) ENGINE = document_strict;
INSERT INTO tscmp_repro (id, t, n) VALUES ('a', '2026-07-02 13:00:00', 5);

SELECT id FROM tscmp_repro WHERE t <= '2026-07-02 14:00:00';   -- (0 rows)  WRONG
SELECT id FROM tscmp_repro WHERE t <  '2026-07-02 14:00:00';   -- (0 rows)  WRONG
SELECT id FROM tscmp_repro WHERE t >= '2026-07-02 12:00:00';   -- 1 row     correct
SELECT id FROM tscmp_repro WHERE t >  '2026-07-02 12:00:00';   -- 1 row     correct
SELECT id FROM tscmp_repro WHERE t BETWEEN '2026-07-02 12:00:00' AND '2026-07-02 14:00:00';
                                                                -- (0 rows)  WRONG
-- control: INTEGER compares work in both directions
SELECT id FROM tscmp_repro WHERE n <= 10;                       -- 1 row
SELECT id FROM tscmp_repro WHERE n <  10;                       -- 1 row

Expected

1. t <= '2026-07-02 14:00:00' matches the row whose t is
   2026-07-02 13:00:00, symmetric with >=.
2. BETWEEN behaves as >= lower AND <= upper.
3. If a TIMESTAMP/literal comparison cannot be evaluated, error loudly
   rather than matching nothing.

Files (best-guess)

• TIMESTAMP literal coercion in predicate planning/evaluation — the
  asymmetry (lower bounds fine, upper bounds empty) smells like the
  string literal being compared uncoerced (lexicographic or
  max-sentinel) on one side of the range only, e.g. in the planner's
  range-predicate conversion for document scans
  (nodedb/src/control/planner/sql_plan_convert/…) or the residual
  predicate application on fetched rows
  (nodedb/src/data/executor/handlers/document/read/fetch.rs).

Operational context

Every "records before X" / "records in window X..Y" query — retention
sweeps, expiry checks, report windows, temporal-validity filters —
returns empty over pgwire. Because the result is an empty set rather
than an error, downstream systems conclude "nothing matches" and
proceed, which is the worst failure mode for retention/expiry logic.