LIMIT on a join between virtual catalog tables returns 0 rows #200

Version / build tested against

origin/main @ 74febcf80

Deployment mode

Origin — single node (local)

Engine(s) involved

Not engine-specific / unsure

Summary

A query joining two virtual pg_catalog tables returns the correct rows — until a LIMIT clause is added, at which point the same query silently returns 0 rows. Real (user) collections joined with LIMIT are unaffected; the failure is specific to the virtual catalog evaluator. Any catalog introspection that pages with LIMIT silently sees an empty catalog.

Steps to reproduce

CREATE COLLECTION wk_probe (id TEXT PRIMARY KEY, name TEXT DEFAULT 'x', qty INT) WITH (engine='document_strict');

SELECT t.oid, t.typname FROM pg_type as t LEFT JOIN pg_range as r ON oid = rngtypid
 WHERE t.typname IN ('int2','text');
--  21 | int2
--  25 | text          (2 rows — correct)

SELECT t.oid, t.typname FROM pg_type as t LEFT JOIN pg_range as r ON oid = rngtypid
 WHERE t.typname IN ('int2','text') LIMIT 5;
-- (0 rows)            <- WRONG: LIMIT 5 on a 2-row result must return both rows

SELECT a.attname FROM pg_attribute a
  LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
 WHERE a.attrelid = 'wk_probe'::regclass LIMIT 5;
-- (0 rows)            <- same shape, different catalog pair; without LIMIT: id/name/qty

-- control: real collections, join + LIMIT — correct
CREATE COLLECTION wk_b (id TEXT PRIMARY KEY, ref TEXT) WITH (engine='document_strict');
INSERT INTO wk_probe (id, name, qty) VALUES ('a','n',1);
INSERT INTO wk_b (id, ref) VALUES ('b1','a');
SELECT w.id AS w_id, b.id AS b_id FROM wk_probe w LEFT JOIN wk_b b ON b.ref = w.id LIMIT 5;
--  a | b1            (1 row — correct)

Expected behavior

LIMIT n truncates a result set to at most n rows; it never empties a non-empty result. Catalog joins behave like collection joins.

Actual behavior

Catalog-to-catalog joins return 0 rows whenever a LIMIT clause is present; the identical query without LIMIT returns the correct rows. Single-table catalog queries with LIMIT are fine, and collection joins with LIMIT are fine.

What actually happened? (check all that are true)

• A workaround exists (rewrite the query, avoid one path, etc.)

Proposed severity

SEV-2 — High: major functionality broken or silently-wrong results; stored data intact

Reproducibility

Always — every attempt

Last known-good version / commit (if a regression)

(blank — catalog joins only recently became usable, so this shape may never have worked with LIMIT)

Environment & logs

Linux x86_64, release build, fresh data directory. Statements succeed silently — no server log lines. Best guess: the virtual-catalog evaluator applies LIMIT before (or instead of) materializing the join output.

Before submitting

• I searched existing issues and this is not a duplicate.
• I reproduced this on a released tag or a current main build (not a stale local branch).
• This is not a security vulnerability (those go to a private advisory).