BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors

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.

Responses

Browse pgsql-bugs by date

  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