Partial index causes rows with NULL to be invisible in unrelated multi-column WHERE queries

[masanork]

Description

When a partial index with WHERE column IS NOT NULL exists on a table, rows where that column is NULL become invisible to multi-column WHERE queries — even when the query does not reference the NULL column at all.

Reproduction

CREATE TABLE test_table (
    id         TEXT PRIMARY KEY,
    task_id    TEXT NOT NULL,
    status     TEXT NOT NULL,
    deadline   INTEGER          -- nullable
);

-- This partial index triggers the bug
CREATE INDEX idx_status_deadline
    ON test_table(status, deadline)
    WHERE deadline IS NOT NULL;

CREATE INDEX idx_task_id
    ON test_table(task_id);

-- Insert a row with deadline = NULL
INSERT INTO test_table (id, task_id, status, deadline)
VALUES ('row-1', 'task-A', 'active', NULL);

Expected

SELECT count(*) FROM test_table WHERE task_id = 'task-A';              -- 1 ✓
SELECT count(*) FROM test_table WHERE status = 'active';               -- 1 ✓
SELECT count(*) FROM test_table WHERE task_id = 'task-A' AND status = 'active';  -- 1 (expected)

Actual

SELECT count(*) FROM test_table WHERE task_id = 'task-A';              -- 1 ✓
SELECT count(*) FROM test_table WHERE status = 'active';               -- 1 ✓
SELECT count(*) FROM test_table WHERE task_id = 'task-A' AND status = 'active';  -- 0 ✗ BUG

Each column matches individually (count = 1), but the combined AND returns 0. The UPDATE ... WHERE task_id = ? AND status = ? also affects 0 rows.

Additional observations

• Rows inserted without NULL in the indexed column are not affected
• The bug occurs with raw SQL literals (no parameter binding involved)
• Named parameters and positional parameters both reproduce the same behavior
• UPDATE ... SET status = 'completed' (unconditional, no WHERE) succeeds and affects 1 row — confirming the row exists
• Dropping the partial index immediately fixes the issue; replacing with a regular composite index (no WHERE clause) also works

Workaround

Replace the partial index with a regular composite index:

DROP INDEX IF EXISTS idx_status_deadline;
CREATE INDEX idx_status_deadline ON test_table(status, deadline);  -- no WHERE clause

Environment

• turso crate: 0.6.0-pre.15 (Rust SDK)
• Platform: macOS (Darwin 25.4.0), aarch64
• Rust: 1.94.0
• Storage: in-memory (:memory:) — reproducible without file-based DB