Hello,

i’m an open-source contributor to Turso, and I came across this while updating the SQLite version we use.

I may very well be misunderstanding the expected behavior here, on SQLite 3.53.0 I found a query whose result seems different from what I would have expected. I also noticed that the result changes when optimization is disabled, which made me think it might be worth asking about.

If this is simply my misunderstanding, I’d really appreciate any correction.

CREATE TABLE t2(id INT, data INT);
CREATE TABLE t3(amount INT);

INSERT INTO t2 VALUES (1,0),(2,0);
INSERT INTO t3 VALUES (1),(1);

SELECT COUNT(*) AS matched
FROM t2
WHERE EXISTS (SELECT 1 FROM t3 WHERE t3.amount > t2.data);

SELECT COUNT(*) AS rows_out
FROM (
  SELECT id
  FROM t2
  WHERE EXISTS (SELECT 1 FROM t3 WHERE t3.amount > t2.data)
  LIMIT 2 OFFSET 3
);

What I expected

• For the first query, both rows in t2 seem to match, so I get: matched = 2
• For the second query, since only 2 rows match but the offset is 3, I expected the subquery to return no rows, so I expected: rows_out = 0

What I see on 3.53.0

• rows_out = 1

Additional observation

if i disable optimization

.testctrl optimizations 0x40000000
SELECT COUNT(*) AS rows_out_noopt
FROM (
  SELECT id
  FROM t2
  WHERE EXISTS (SELECT 1 FROM t3 WHERE t3.amount > t2.data)
  LIMIT 2 OFFSET 3
);

then i get

• rows_out_noopt = 0

So with that optimization disabled, the result matches what I expected.

Because of that difference, I wanted to ask whether this is expected behavior and I am misunderstanding the query semantics, or whether this may be a bug.

git bisect

I bisected between version-3.50.4 (good) and version-3.51.0 (bad). First bad commit is:

aa54d7a0ca03a4df516f25e66ff3c4801be07a7b Merge in the exists-to-join optimization that has been modified to relax the requirement of having an indexed join constraint. (2025-07-02)

Thanks.