BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors
Date
Msg-id 19114-b0d2502c853fe210@postgresql.org
Whole thread Raw
Responses Re: BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors
List pgsql-bugs
The following bug has been logged on the website:

Bug reference:      19114
Logged by:          Naveen Krishna S
Email address:      naveenkrishna.s@sky.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.


pgsql-bugs by date:

Previous
From: ocean_li_996
Date:
Subject: Re: BUG #19109: catalog lookup with the wrong snapshot during logical decoding causes coredump
Next
From: Thomas Munro
Date:
Subject: Re: BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors