For some reason sqlite prefers an index using larger than operand, whereas it could also be used by comparing on equality.

Using SQLite version 3.45.0 2023-12-14 22:01:55

DROP TABLE IF EXISTS x;
CREATE TABLE x(i INTEGER, j INTEGER, k INTEGER);
CREATE INDEX idx_x_ij ON x(i, j);

EXPLAIN QUERY PLAN SELECT * FROM x a JOIN x b ON b.i = a.i AND b.j = a.k WHERE a.k > 0;

Returns:

QUERY PLAN
|--SCAN a
`--SEARCH b USING INDEX idx_x_ij (i=? AND j>?)

Expected:

QUERY PLAN
|--SCAN a
`--SEARCH b USING INDEX idx_x_ij (i=? AND j=?)