| From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | naveenkrishna(dot)s(at)sky(dot)uk |
| Subject: | BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors |
| Date: | 2025-11-17 06:08:30 |
| Message-ID: | 19114-b0d2502c853fe210@postgresql.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 19114
Logged by: Naveen Krishna S
Email address: naveenkrishna(dot)s(at)sky(dot)uk
PostgreSQL version: 14.13
Operating system: Mac OS
Description:
In a table with vector column,
SELECT embedding <=> CAST('[0.01, 0.23, -0.1,..]' as vector)
AS distance
FROM my_table
WHERE TRUE
order by distance desc
LIMIT 100;
is giving 100 records whereas
SELECT embedding <=> CAST('[0.01, 0.23, -0.1,..]' as vector)
AS distance
FROM my_table
WHERE TRUE
order by distance asc
LIMIT 100;
is giving 40 records for a specific embedding and always less than the limit
for any query embedding. Why is this?
I have also noticed if I use NULLS FIRST or COALESCE(distance, 99999999999)
it is returning the requested limit. But when I tried to list the records
with distance as null, there were none.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Thomas Munro | 2025-11-17 07:50:45 | Re: BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors |
| Previous Message | ocean_li_996 | 2025-11-17 01:34:03 | Re: BUG #19109: catalog lookup with the wrong snapshot during logical decoding causes coredump |