Vector top-K results change when LIMIT changes
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 48/100
Research direction
Start by reproducing the SQL example and compare the plans chosen for LIMIT 100 and LIMIT 105; the payload does not name CockroachDB files or tests. Trace how vector search and scan access paths handle NULL vectors in distance-ordered queries. Done when both paths apply the same NULL semantics and increasing LIMIT preserves the smaller result set.
Written by the indexing model from the issue text.
Description
Describe the problem
For a distance-ordered vector query, changing only LIMIT can switch the plan
between vector search and a scan. The vector-search path omits rows whose
vector is NULL, while the scan path returns the NULL-vector row first. The
larger-LIMIT result is therefore not an extension of the smaller-LIMIT result.
In the case below, LIMIT 100 excludes the row with id = 999, while
LIMIT 105 includes it as the first row.
To Reproduce
DROP TABLE IF EXISTS vector_null_limit;
CREATE TABLE vector_null_limit (
id INT PRIMARY KEY,
cat INT,
vec VECTOR(4),
VECTOR INDEX (vec)
);
INSERT INTO vector_null_limit
SELECT g,
g % 3,
(('[' || ((g % 7))::float8::text || ',' ||
((g * 3 % 11))::float8::text || ',' ||
((g * 5 % 13))::float8::text || ',' ||
((g * 7 % 17))::float8::text || ']')::VECTOR(4))
FROM generate_series(1, 200) AS g;
INSERT INTO vector_null_limit VALUES (999, 2, NULL);
SELECT id
FROM vector_null_limit
ORDER BY vec <-> '[2,3,4,5]'
LIMIT 100;
-- First rows: 71, 115, 144, 37, 1
-- The NULL-vector row (id = 999) is absent.
SELECT id
FROM vector_null_limit
ORDER BY vec <-> '[2,3,4,5]'
LIMIT 105;
-- First rows: 999, 71, 115, 144, 37
-- The NULL-vector row (id = 999) is present.
SELECT
(SELECT count(*) FROM (
SELECT id FROM vector_null_limit
ORDER BY vec <-> '[2,3,4,5]'
LIMIT 100
) AS q100 WHERE id = 999) AS null_row_in_limit_100,
(SELECT count(*) FROM (
SELECT id FROM vector_null_limit
ORDER BY vec <-> '[2,3,4,5]'
LIMIT 105
) AS q105 WHERE id = 999) AS null_row_in_limit_105;
-- null_row_in_limit_100 | null_row_in_limit_105
-- ----------------------+-----------------------
-- 0 | 1
Expected behavior
Both access paths should apply the same NULL-vector semantics. Increasing the
LIMIT should preserve the rows returned by the smaller LIMIT and only append
additional rows. Therefore, the two counts above should be equal.
Environment:
- CockroachDB version:
v26.3.0-alpha.00000000-dev-13cb3eb27674b4a981e5c526d04c2387e81efd0b - Server OS: Linux amd64
- Client app:
psql16.15
Jira issue: CRDB-70499
- Dominant language
- Go
- Stars
- 32.6k
- Forks
- 4.2k
- PR merge metrics
- No merged PRs in 30d
Getting set up
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
cockroachdb/cockroach#176245 · 1 comment ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinPossibly taken @1AbdullahButt claimed this 14 days ago. OpenC-bug O-community X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
cockroachdb/cockroach#176228 · 1 comment ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEMay be free again @darshpatel1052 claimed this 18 days ago, and no pull request is open. OpenC-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
cockroachdb/cockroach#176223 · 2 comments ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutPossibly taken @dajiaohuang claimed this 14 days ago. OpenC-bug O-community X-blathers-untriaged
Difficulty 1/5 Under an hour Newbie friendliness 84/100
cockroachdb/cockroach#176210 · 1 comment ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
cockroachdb/cockroach#176150 · 1 comment ·
All issues in cockroachdb/cockroach
Similar issues
-
area/import-export area/projects concern/ux
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
go-vikunja/vikunja#4455 · 1 comment ·
Maintainers usually reply within 2 days
-
Dependabot PRs no longer merge automaticallyPossibly taken @dwisiswant0 claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
projectdiscovery/urlfinder#160 ·
Maintainers usually reply within 1 day
-
database
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
chnm/legal-modernism#450 ·
Maintainers usually reply within 1 day
-
tech-debt
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
Maintainers usually reply within 1 day
-
bug ready-for-triage ready-to-code
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
RedHatInsights/insights-ingress-go#946 · 4 comments ·
Maintainers usually reply within 1 day