Datasette version: 0.65.2
SQLite version: 3.53.1
Plugin: None needed, core Datasette

Description

When filtering a view on a computed column (e.g. SUM(...) AS alias) with an exact match to 0, Datasette returns 0 rows even though matching rows exist.

The filter ?valid_count__exact=0 generates SQL WHERE "valid_count" = :p0 and binds :p0 as the string '0'. Since computed columns in SQLite views have no column affinity (empty type in PRAGMA table_xinfo), SQLite treats the comparison as INTEGER vs TEXT — different storage classes — and the comparison fails.

Reproduction

Schema

CREATE TABLE items(
  id INTEGER PRIMARY KEY,
  category TEXT,
  valid INTEGER
);

INSERT INTO items VALUES (1, 'a', 0);
INSERT INTO items VALUES (2, 'a', 1);
INSERT INTO items VALUES (3, 'a', 0);
INSERT INTO items VALUES (4, 'b', 0);
INSERT INTO items VALUES (5, 'b', 0);

CREATE VIEW summary AS
  SELECT category,
    SUM(CASE WHEN valid THEN 1 ELSE 0 END) AS valid_count,
    SUM(CASE WHEN NOT valid THEN 1 ELSE 0 END) AS invalid_count
  FROM items
  GROUP BY category;

Steps

1. Start Datasette and filter:

   datasette repro.db
   # GET /repro/summary?valid_count__exact=0
   

2. Observe "0 rows where valid_count = 0"

Expected

1 row: b | 0 | 2 (category 'b' has valid_count=0)

Actual

0 rows.

Generated SQL (from /repro/summary.json?valid_count__exact=0)

{
  "query": {
    "sql": "select category, valid_count, invalid_count from summary where \"valid_count\" = :p0  limit 101",
    "params": { "p0": "0" }
  }
}

Root cause

1. PRAGMA table_xinfo(summary) shows valid_count and invalid_count columns have empty type — no NUMERIC affinity.
2. SQLite compares by storage class order: INTEGER < TEXT. 0 = '0' is always FALSE by design — this is not a SQLite bug.
3. Datasette binds filter parameters as TEXT strings ("0"), not as integers (0).
4. Without column affinity, the TEXT-typed parameter is never coerced to INTEGER.
5. For real table columns with declared types (e.g. INTEGER), this works fine because SQLite applies column affinity to the comparison value. For computed columns in views, there is no declared type.

Workaround

Wrap SUM in CAST(... AS INTEGER) to give the column explicit INTEGER affinity:

CREATE VIEW summary AS
  SELECT category,
    CAST(SUM(CASE WHEN valid THEN 1 ELSE 0 END) AS INTEGER) AS valid_count,
    CAST(SUM(CASE WHEN NOT valid THEN 1 ELSE 0 END) AS INTEGER) AS invalid_count
  FROM items
  GROUP BY category;

This makes the filter work because PRAGMA table_xinfo now shows type INTEGER, and SQLite coerces the string parameter for comparison.

Proposed fix

Datasette should detect the column type of view columns (via PRAGMA table_xinfo) and bind filter parameters with the appropriate type (INTEGER/REAL for numeric columns) rather than always binding as TEXT.