Description

When a compound SELECT with UNION and LIMIT (without ORDER BY) is used as a subquery inside a WHERE IN clause, the LIMIT is silently ignored, returning more rows than expected.

Minimal Reproduction

CREATE TABLE t(x INTEGER);
INSERT INTO t VALUES (1),(2),(3),(4),(5);

-- BUG: LIMIT 2 ignored inside WHERE IN subquery
SELECT * FROM t WHERE x IN (SELECT 1 UNION SELECT 2 UNION SELECT 3 LIMIT 2);
-- Turso: 1, 2, 3  (3 rows — LIMIT ignored)
-- SQLite: 1, 2     (2 rows — LIMIT applied correctly)

Run with: echo "..." | cargo run --bin tursodb

What Works

Top-level compound SELECT with LIMIT works correctly:

SELECT * FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 LIMIT 2);
-- Turso: 1, 2 ✅
-- SQLite: 1, 2 ✅

UNION ALL + LIMIT in subquery also works:

SELECT * FROM t WHERE x IN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 LIMIT 2);
-- Turso: 1, 2 ✅
-- SQLite: 1, 2 ✅

UNION + ORDER BY + LIMIT in subquery also works:

SELECT * FROM t WHERE x IN (SELECT 1 UNION SELECT 2 UNION SELECT 3 ORDER BY 1 DESC LIMIT 2);
-- Turso: 3, 2 ✅
-- SQLite: 3, 2 ✅

Affected Pattern

The bug requires ALL of these conditions:

• UNION (not UNION ALL) — the dedup variant
• LIMIT without ORDER BY
• Used as a subquery inside WHERE IN

Impact

Correctness bug — queries silently return wrong results. All features involved (UNION, LIMIT, WHERE IN) are enabled by default with no experimental flags.

Likely Root Cause

When a compound SELECT with LIMIT is used as a subquery in IN(), the LIMIT clause is dropped during subquery compilation. The top-level compound SELECT path handles LIMIT correctly, as does the UNION ALL subquery path. The interaction between the UNION deduplication and LIMIT in the subquery context appears to be the gap.