I keep patching state in a small SQLite file and run it with sqlite3 :memory: < main.sql. I want hosts last patched before 2026-10-01 that have no active maintenance hold. Active means until >= '2026-11-02'.

By my count that is 5 hosts. The overdue count alone is 6, and only db-la-01 has a live hold. The query below returns nothing at all. Exit status 0, no warning.

SELECT hostname, env, last_patched
FROM hosts
WHERE last_patched < '2026-10-01'
  AND id NOT IN (
    SELECT host_id FROM maint_holds WHERE until >= '2026-11-02'
  )
ORDER BY last_patched;

The count version prints this:

candidates
----------
         0

What I checked:

  • The inventory and holds listings look right.
  • The subquery run by itself returns the rows I expect.
  • until is TEXT, but every value is ISO 8601 and the format is consistent.
  • Swapping >= for > changed nothing.

The active holds listing, for reference:

id  host_id  hostname_hint    until
--  -------  -------------  ----------
 1        3  db-la-01       2026-11-15
 3           db-la-02       2026-12-01

The second hold is for db-la-02, which isn't in hosts yet. I haven't resolved the hostname, so host_id is empty. That's a half-finished feature, and I don't see why it would affect a query about hosts that do exist.

So why does the filter remove every row instead of just db-la-01? I suspected the text comparison, but the dates are fine. What am I missing?